Database Management System Assignment

This database management system assignment focuses on designing and querying relational databases. It includes tasks like defining tables (Customer, Address), performing CRUD operations, retrieving specific data using SQL queries, and manipulating inventory information. The assignment also covers topics such as joining tables, foreign keys, and subqueries.

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