Database Management System Assignment
Added on 2020-04-01
29 Pages3646 Words65 Views
|
|
|
DATABASE MANAGEMENT SYSTEMS
Table of ContentsTable Creation............................................................................................................................2ER Diagram..............................................................................................................................11Insert Statement........................................................................................................................12Relational schema....................................................................................................................19Query statement.......................................................................................................................22References................................................................................................................................281
Table CreationTable creation SQL command is shown below.-- MySQL Workbench Forward EngineeringSET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;SET@OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS,FOREIGN_KEY_CHECKS=0;SET@OLD_SQL_MODE=@@SQL_MODE,SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';-- ------------------------------------------------------- Schema Startstruck_Entertainment-- ------------------------------------------------------- ------------------------------------------------------- Schema Startstruck_Entertainment-- -----------------------------------------------------CREATE SCHEMA IF NOT EXISTS `Startstruck_Entertainment` DEFAULTCHARACTER SET utf8 ;USE `Startstruck_Entertainment` ;-- ------------------------------------------------------- Table `Startstruck_Entertainment`.`tblManufacturer`-- -----------------------------------------------------CREATE TABLE IF NOT EXISTS `Startstruck_Entertainment`.`tblManufacturer` ( `ManufacturerID` INT NOT NULL, `Name` VARCHAR(45) NULL, `Phone` VARCHAR(45) NULL, `Email` VARCHAR(45) NULL, `address` VARCHAR(45) NULL, `Suburb` VARCHAR(45) NULL, `Postcode` VARCHAR(45) NULL,2
`Country` VARCHAR(45) NULL, PRIMARY KEY (`ManufacturerID`))ENGINE = InnoDB;-- ------------------------------------------------------- Table `Startstruck_Entertainment`.`tblCollect`-- -----------------------------------------------------CREATE TABLE IF NOT EXISTS `Startstruck_Entertainment`.`tblCollect` ( `CollectID` INT NOT NULL, `MadeOfYear` DATE NULL, `Manufacturer` VARCHAR(45) NULL, `tblManufacturer_ManufacturerID` INT NOT NULL, PRIMARY KEY (`CollectID`, `tblManufacturer_ManufacturerID`), INDEX `fk_tblCollect_tblManufacturer1_idx` (`tblManufacturer_ManufacturerID` ASC), CONSTRAINT `fk_tblCollect_tblManufacturer1` FOREIGN KEY (`tblManufacturer_ManufacturerID`) REFERENCES `Startstruck_Entertainment`.`tblManufacturer` (`ManufacturerID`) ON DELETE NO ACTION ON UPDATE NO ACTION)ENGINE = InnoDB;-- ------------------------------------------------------- Table `Startstruck_Entertainment`.`tblCollectionMember`-- -----------------------------------------------------CREATE TABLE IF NOT EXISTS `Startstruck_Entertainment`.`tblCollectionMember` ( `CollectionID` INT NOT NULL, `InventoryID` INT NULL, `tblCollect_CollectID` INT NOT NULL, PRIMARY KEY (`CollectionID`, `tblCollect_CollectID`), INDEX `fk_tblCollectionMember_tblCollect1_idx` (`tblCollect_CollectID` ASC), CONSTRAINT `fk_tblCollectionMember_tblCollect1` FOREIGN KEY (`tblCollect_CollectID`)3
REFERENCES `Startstruck_Entertainment`.`tblCollect` (`CollectID`) ON DELETE NO ACTION ON UPDATE NO ACTION)ENGINE = InnoDB;-- ------------------------------------------------------- Table `Startstruck_Entertainment`.`tblCollection`-- -----------------------------------------------------CREATE TABLE IF NOT EXISTS `Startstruck_Entertainment`.`tblCollection` ( `CollectionID` INT NOT NULL, `Name` VARCHAR(45) NULL, `Description` VARCHAR(45) NULL, `DateReleased` VARCHAR(45) NULL, `tblCollectionMember_CollectionID` INT NOT NULL, PRIMARY KEY (`CollectionID`, `tblCollectionMember_CollectionID`), INDEX `fk_tblCollection_tblCollectionMember_idx` (`tblCollectionMember_CollectionID`ASC), CONSTRAINT `fk_tblCollection_tblCollectionMember` FOREIGN KEY (`tblCollectionMember_CollectionID`) REFERENCES `Startstruck_Entertainment`.`tblCollectionMember` (`CollectionID`) ON DELETE NO ACTION ON UPDATE NO ACTION)ENGINE = InnoDB;-- ------------------------------------------------------- Table `Startstruck_Entertainment`.`tblReceipt`-- -----------------------------------------------------CREATE TABLE IF NOT EXISTS `Startstruck_Entertainment`.`tblReceipt` ( `ReceiptID` INT NOT NULL, `Date` DATE NULL, PRIMARY KEY (`ReceiptID`))ENGINE = InnoDB;4
-- ------------------------------------------------------- Table `Startstruck_Entertainment`.`tblPurchase`-- -----------------------------------------------------CREATE TABLE IF NOT EXISTS `Startstruck_Entertainment`.`tblPurchase` ( `PurchaseID` INT NOT NULL, `Quantity` VARCHAR(45) NULL, `AmountPaid` VARCHAR(45) NULL, `tblReceipt_ReceiptID` INT NOT NULL, PRIMARY KEY (`PurchaseID`, `tblReceipt_ReceiptID`), INDEX `fk_tblPurchase_tblReceipt1_idx` (`tblReceipt_ReceiptID` ASC), CONSTRAINT `fk_tblPurchase_tblReceipt1` FOREIGN KEY (`tblReceipt_ReceiptID`) REFERENCES `Startstruck_Entertainment`.`tblReceipt` (`ReceiptID`) ON DELETE NO ACTION ON UPDATE NO ACTION)ENGINE = InnoDB;-- ------------------------------------------------------- Table `Startstruck_Entertainment`.`tblInventory`-- -----------------------------------------------------CREATE TABLE IF NOT EXISTS `Startstruck_Entertainment`.`tblInventory` ( `InventoryID` INT NOT NULL, `Title` VARCHAR(45) NULL, `UnitPrice` VARCHAR(45) NULL, `Markup` VARCHAR(45) NULL, `Discount` VARCHAR(45) NULL, `QtyInStock` VARCHAR(45) NULL, `GenreID` INT NULL, `PurchaseID` INT NULL, `MovieID` INT NULL, `CollectID` INT NULL,5
End of preview
Want to access all the pages? Upload your documents or become a member.
Related Documents