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

 Customer
 Genre
 Director
 Cast
 Collectible
 Inventory
 Movie
 Manufacturer
 Collection Member
Insert Statement
To insert the data into the table by insert statement.
The Actor table data is shown below.
12

The Collection table data is shown below.
13

The Customer table data is shown below.
14

The Director table data is shown below.
15

The Genre table data is shown below.
16

The Inventory table data is shown below.
The Manufaturer table data is shown below.
17

The Movie table data is shown below.
18

The Supplier table data is shown below.
Relational schema
Supplier table
Supplier ID INT Primary Key
Name VARCHAR (45) -
Phone VARCHAR (45) -
Email VARCHAR (45) -
Address VARCHAR (45) -
Suburb VARCHAR (45) -
Postcode VARCHAR (45) -
Country VARCHAR (45) -
Comments VARCHAR (45) -
19

Genre Table
Genre ID INT Primary Key
name VARCHAR (45) -
description VARCHAR (45) -
Collectible Table
ID INT Primary Key
year VARCHAR (45) -
manufacturer VARCHAR (45) -
member of collection VARCHAR (45) -
Collection Table
Collection ID INT Primary Key
Name VARCHAR (45) -
Version VARCHAR (45) --
Year VARCHAR (45)
Manufacturer ID INT Foreign Key
Movie Table
Movie ID INT Primary Key
Year VARCHAR (45) -
classification VARCHAR (45) -
stars VARCHAR (45) -
director VARCHAR (45) -
Actor ID INT Foreign Key
Director ID INT Foreign Key
Actor Table
Actor ID INT Primary Key
Surname VARCHAR (45) -
Name VARCHAR (45) -
Place of birth VARCHAR (45) -
Date of birth VARCHAR (45) -
20

Date of Death VARCHAR (45) -
Director Table
Director ID INT Primary Key
Surname VARCHAR (45) -
Name VARCHAR (45) -
Place of birth VARCHAR (45) -
Date of birth VARCHAR (45) -
Date of Death VARCHAR (45) -
Inventory Table
Inventory ID INT Primary Key
title VARCHAR (45) -
genre VARCHAR (45) -
Unit Price VARCHAR (45) -
mark-up VARCHAR (45) -
discount VARCHAR (45) -
Quantity in Stock VARCHAR (45) -
supplier ID INT Foreign Key
Genre ID INT Foreign Key
Purchase ID INT Foreign Key
Movie ID INT Foreign Key
Manufacturer Table
Manufacturer ID INT Primary Key
Name VARCHAR (45) -
Phone VARCHAR (45) -
Email VARCHAR (45) -
Address VARCHAR (45) -
Suburb VARCHAR (45) -
Post Code VARCHAR (45) -
Country VARCHAR (45) -
21

Customer Table
Customer ID INT Primary Key
Surname VARCHAR (45) -
Name VARCHAR (45) -
Phone VARCHAR (45) -
Email VARCHAR (45) -
Address VARCHAR (45) -
Suburb VARCHAR (45) -
Postcode VARCHAR (45) -
Purchase ID INT Foreign Key
Query statement
Query – 1
Here listing the supplier details and sorted the suburb in ascending order. It is shown in
below.
22

Query – 2
Here listing the movie items and their suppliers name, email, phone number and suburb. It is
shown in below.
Query – 3
Here listing the Actor details. It is shown below.
Query – 4
Here listing the genre details. It is shown below.
23

Query – 5
Here listing the Inventory items details. It is shown below.
Query – 6
Here listing the genres details that have no inventory item. It is shown below.
24

Query – 7
Here delete the all the customers from the postcode 3353. It is shown in below.
Query – 8
Here changes the inventory item name form Wookie figure to Wookie figure-live size. It is
shown in below.
25

Query – 9
Here list the movie titles with their cast and director. It is shown in below (Pallaw, 2010).
Query – 10
Here insert the movie title Solaris. It is shown in below.
26

Query – 11
Here lists the inventory with title, quantity and sales price. It is shown in below.
Query – 12
Here lists the actor first and last name. It is shown below (Bhatia and Bansal, n.d.).
27

References
Bhatia, A. and Bansal, V. (n.d.). Database management system.
Pallaw, V. (2010). Database management systems. Darya Ganj, New Delhi: Asian Books
Private Ltd.
28