Database Management Systems Project: ER Diagrams and SQL Queries

This database project focuses on the design and implementation of a database system for Startstruck Entertainment. The project begins with the creation of database tables using SQL commands, followed by an Entity-Relationship (ER) diagram illustrating the relationships between different entities such as manufacturers, collections, inventory, actors, directors, and customers. The project includes insert statements to populate the tables with data, and a relational schema that defines the structure of the database. Furthermore, the project provides various SQL query statements to retrieve and manipulate data from the database, covering a range of operations like selecting, joining, filtering, updating, and deleting data. The queries demonstrate how to extract specific information, such as supplier details, movie information, actor details, and inventory data, as well as how to perform updates and deletions. The project concludes with a list of references used in the development of the database system.

database management systems

Table of Contents
Table Creation............................................................................................................................2
ER Diagram..............................................................................................................................11
Insert Statement........................................................................................................................12
Relational schema....................................................................................................................19
Query statement.......................................................................................................................22
References................................................................................................................................28
1

Table Creation
Table creation SQL command is shown below.
-- MySQL Workbench Forward Engineering
SET @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` DEFAULT
CHARACTER 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

`SupplierID` INT NULL,
`tblPurchase_PurchaseID` INT NOT NULL,
`tblPurchase_tblReceipt_ReceiptID` INT NOT NULL,
PRIMARY KEY (`InventoryID`, `tblPurchase_PurchaseID`,
`tblPurchase_tblReceipt_ReceiptID`),
INDEX `fk_tblInventory_tblPurchase1_idx` (`tblPurchase_PurchaseID` ASC,
`tblPurchase_tblReceipt_ReceiptID` ASC),
CONSTRAINT `fk_tblInventory_tblPurchase1`
FOREIGN KEY (`tblPurchase_PurchaseID` , `tblPurchase_tblReceipt_ReceiptID`)
REFERENCES `Startstruck_Entertainment`.`tblPurchase` (`PurchaseID` ,
`tblReceipt_ReceiptID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `Startstruck_Entertainment`.`tblSupplier`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Startstruck_Entertainment`.`tblSupplier` (
`SupplierID` INT NOT NULL,
`Name` VARCHAR(45) NULL,
`ContactName` VARCHAR(45) NULL,
`Phone` VARCHAR(45) NULL,
`Email` VARCHAR(45) NULL,
`Address` VARCHAR(45) NULL,
`Suburb` VARCHAR(45) NULL,
`Postcode` VARCHAR(45) NULL,
`Country` VARCHAR(45) NULL,
`Comments` VARCHAR(45) NULL,
`tblInventory_InventoryID` INT NOT NULL,
PRIMARY KEY (`SupplierID`, `tblInventory_InventoryID`),
INDEX `fk_tblSupplier_tblInventory1_idx` (`tblInventory_InventoryID` ASC),
CONSTRAINT `fk_tblSupplier_tblInventory1`
6

FOREIGN KEY (`tblInventory_InventoryID`)
REFERENCES `Startstruck_Entertainment`.`tblInventory` (`InventoryID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `Startstruck_Entertainment`.`tblGenre`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Startstruck_Entertainment`.`tblGenre` (
`GenreID` INT NOT NULL,
`Name` VARCHAR(45) NULL,
`Description` VARCHAR(45) NULL,
`tblInventory_InventoryID` INT NOT NULL,
PRIMARY KEY (`GenreID`, `tblInventory_InventoryID`),
INDEX `fk_tblGenre_tblInventory1_idx` (`tblInventory_InventoryID` ASC),
CONSTRAINT `fk_tblGenre_tblInventory1`
FOREIGN KEY (`tblInventory_InventoryID`)
REFERENCES `Startstruck_Entertainment`.`tblInventory` (`InventoryID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `Startstruck_Entertainment`.`tblCast`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Startstruck_Entertainment`.`tblCast` (
`CastID` INT NOT NULL,
`ActorID` INT NULL,
`InventoryID` INT NULL,
`Role` VARCHAR(45) NULL,
PRIMARY KEY (`CastID`))
7

ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `Startstruck_Entertainment`.`tblActor`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Startstruck_Entertainment`.`tblActor` (
`ActorID` INT NOT NULL,
`FirstName` VARCHAR(45) NULL,
`LastName` VARCHAR(45) NULL,
`PlaceOfBirth` VARCHAR(45) NULL,
`DateOfBirth` VARCHAR(45) NULL,
`DateOfDeath` VARCHAR(45) NULL,
`tblCast_CastID` INT NOT NULL,
PRIMARY KEY (`ActorID`, `tblCast_CastID`),
INDEX `fk_tblActor_tblCast1_idx` (`tblCast_CastID` ASC),
CONSTRAINT `fk_tblActor_tblCast1`
FOREIGN KEY (`tblCast_CastID`)
REFERENCES `Startstruck_Entertainment`.`tblCast` (`CastID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `Startstruck_Entertainment`.`tblDirector`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Startstruck_Entertainment`.`tblDirector` (
`DirectorID` INT NOT NULL,
`FirstName` VARCHAR(45) NULL,
`LasName` VARCHAR(45) NULL,
`PlaceOfBirth` DATE NULL,
`DateOfBirth` DATE NULL,
`DateOfDeath` DATE NULL,
8

PRIMARY KEY (`DirectorID`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `Startstruck_Entertainment`.`tblMovie`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Startstruck_Entertainment`.`tblMovie` (
`MovieID` INT NOT NULL,
`ReleaseYear` YEAR NULL,
`Classfication` VARCHAR(45) NULL,
`StarRating` VARCHAR(45) NULL,
`tblCast_CastID` INT NOT NULL,
`tblDirector_DirectorID` INT NOT NULL,
`tblInventory_InventoryID` INT NOT NULL,
`tblInventory_tblPurchase_PurchaseID` INT NOT NULL,
`tblInventory_tblPurchase_tblReceipt_ReceiptID` INT NOT NULL,
PRIMARY KEY (`MovieID`, `tblCast_CastID`, `tblDirector_DirectorID`,
`tblInventory_InventoryID`, `tblInventory_tblPurchase_PurchaseID`,
`tblInventory_tblPurchase_tblReceipt_ReceiptID`),
INDEX `fk_tblMovie_tblCast1_idx` (`tblCast_CastID` ASC),
INDEX `fk_tblMovie_tblDirector1_idx` (`tblDirector_DirectorID` ASC),
INDEX `fk_tblMovie_tblInventory1_idx` (`tblInventory_InventoryID` ASC,
`tblInventory_tblPurchase_PurchaseID` ASC,
`tblInventory_tblPurchase_tblReceipt_ReceiptID` ASC),
CONSTRAINT `fk_tblMovie_tblCast1`
FOREIGN KEY (`tblCast_CastID`)
REFERENCES `Startstruck_Entertainment`.`tblCast` (`CastID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_tblMovie_tblDirector1`
FOREIGN KEY (`tblDirector_DirectorID`)
REFERENCES `Startstruck_Entertainment`.`tblDirector` (`DirectorID`)
ON DELETE NO ACTION
9

ON UPDATE NO ACTION,
CONSTRAINT `fk_tblMovie_tblInventory1`
FOREIGN KEY (`tblInventory_InventoryID` , `tblInventory_tblPurchase_PurchaseID` ,
`tblInventory_tblPurchase_tblReceipt_ReceiptID`)
REFERENCES `Startstruck_Entertainment`.`tblInventory` (`InventoryID` ,
`tblPurchase_PurchaseID` , `tblPurchase_tblReceipt_ReceiptID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `Startstruck_Entertainment`.`tblCustomer`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Startstruck_Entertainment`.`tblCustomer` (
`CustomerID` INT NOT NULL,
`FirstName` VARCHAR(45) NULL,
`LastName` VARCHAR(45) NULL,
`Phone` VARCHAR(45) NULL,
`Email` VARCHAR(45) NULL,
`address` VARCHAR(45) NULL,
`Suburb` VARCHAR(45) NULL,
`PostCode` VARCHAR(45) NULL,
`LoyaltyDiscount` VARCHAR(45) NULL,
`tblReceipt_ReceiptID` INT NOT NULL,
PRIMARY KEY (`CustomerID`, `tblReceipt_ReceiptID`),
INDEX `fk_tblCustomer_tblReceipt1_idx` (`tblReceipt_ReceiptID` ASC),
CONSTRAINT `fk_tblCustomer_tblReceipt1`
FOREIGN KEY (`tblReceipt_ReceiptID`)
REFERENCES `Startstruck_Entertainment`.`tblReceipt` (`ReceiptID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
10

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
ER Diagram
The Entity Relationship Diagram is shown below.
The Above ER diagram contains the following entities. These are,
 Supplier
 Purchase
 Actor
11