ITECH 1006 Database Assignment 2

This assignment for ITECH 1006: Database Management Systems consists of three main tasks. Task 1 involves designing a relational schema for a database, detailing attributes and datatypes for tables such as GENRE, SUPPLIER, ACTOR, INVENTORY, MANUFACTURER, DIRECTOR, COLLECTION, COLLECTIBLE, COLLECTION_MEMBER, MOVIE, CAST, CUSTOMER, RECEIPT, and PURCHASE. Task 2 focuses on creating the database and tables using SQL `CREATE TABLE` statements, including primary and foreign keys. Task 3 involves inserting data into the tables using SQL `INSERT INTO` statements. Finally, Task 4 presents a series of SQL queries (`SELECT`, `UPDATE`, `DELETE`) to retrieve, modify, and delete data from the database, demonstrating various SQL functionalities and joins. The assignment includes references to resources used for relational database design.

Assignment 2
ITECH 1006 – Database Management Systems
Student ID –
Date -
Module Tutor -

Report on Database Implementation
Task 1
Relational Schema
1. GENRE
Attribute Datetype Key
genreID INT PK
genreName TEXT(50)
Description TEXT(250)
2. SUPPLIER
Attribute Datetype Key
supplierID INT PK
supplierName TEXT(50)
Contact TEXT(50)
Phone TEXT(15)
Email TEXT(50)
Address TEXT(50)
Suburb TEXT(50)
Postcode TEXT(5)
Country TEXT(50)
Comments TEXT(250)
3. ACTOR
Attribute Datetype Key
actorID INT PK
firstName TEXT(50)
lastName TEXT(50)
PlaceOfBirth TEXT(100)
DateOfBirth DATE
DateOfDeath DATE
4. INVENTORY
Attribute Datetype Key
inventoryID INT PK
title TEXT(250)
unitPrice DECIMAL(5,2)
markup DECIMAL(5,2)
discount DECIMAL(5,2)
qtyInStock INT
genreID INT FK
supplierID INT FK

5. MANUFACTURER
Attribute Datetype Key
manufacturerID INT PK
manufacturerName TEXT(50)
Phone TEXT(15)
Email TEXT(50)
Address TEXT(50)
Suburb TEXT(50)
Postcode TEXT(5)
Country TEXT(50)
6. DIRECTOR
Attribute Datetype Key
directorID INT PK
firstName TEXT(50)
lastName TEXT(50)
PlaceOfBirth TEXT(100)
DateOfBirth DATE
DateOfDeath DATE
7. COLLECTION
Attribute Datetype Key
collectionID INT PK
collectionName TEXT(50)
Description TEXT(250)
dateReleased DATE
8. COLLECTIBLE
Attribute Datetype Key
inventoryID INT PK/FK
madeYear INT
manufacturerID INT FK
9. COLLECTION_MEMBER
Attribute Datetype Key
collectionID INT PK/FK
inventoryID INT PK/FK
10. MOVIE
Attribute Datetype Key
inventoryID INT PK/FK
releaseYear INT
classification TEXT(5)
starRating INT
directorID INT FK

11. CAST
Attribute Datetype Key
actorID INT PK/FK
inventID INT PK/FK
role TEXT(50)
12. CUSTOMER
Attribute Datetype Key
customerID INT PK
firstName TEXT(50)
lastName TEXT(50)
Phone TEXT(15)
Email TEXT(50)
Address TEXT(100)
Postcode INT
loyaltyDiscount DECIMAL(5,2)
13. RECEIPT
Attribute Datetype Key
receiptID INT PK
customerID INT FK
receiptDate DATE
14. PURCHASE
Attribute Datetype Key
receiptID INT PK
inventoryID INT FK
quantity INT
amountPaid DECIMAL(5,2)
(Paul Litwin n.d)
( Tutorialspoint 2016)

Task 2
Create Statements
DROP Database IF EXISTS Startstruck_Entertainment_StudentID;
create database Startstruck_Entertainment_StudentID;
use Startstruck_Entertainment_StudentID;
DROP TABLE IF EXISTS GENRE;
CREATE TABLE GENRE (
genreID int NOT NULL AUTO_INCREMENT,
genreName varchar(50) NOT NULL,
Description varchar(250) NULL,
PRIMARY KEY (genreID)
);
DROP TABLE IF EXISTS SUPPLIER;
CREATE TABLE SUPPLIER (
supplierID int NOT NULL AUTO_INCREMENT,
supplierName varchar(50) NOT NULL,
ContactName varchar(50) NOT NULL,
Phone varchar(15) NOT NULL,
Email varchar(50) NOT NULL,
Address varchar(50) NOT NULL,
Suburb varchar(50) NOT NULL,
Postcode varchar(5) NOT NULL,
Country varchar(50) NOT NULL,
Comments varchar(250) NULL,
PRIMARY KEY (supplierID)
);
DROP TABLE IF EXISTS ACTOR;
CREATE TABLE ACTOR (
actorID int NOT NULL AUTO_INCREMENT,
FirstName varchar(50) NOT NULL,
LastName varchar(50) NOT NULL,
PlaceOfBirth varchar(100) NOT NULL,
DateOfBirth Date NOT NULL,
DateOfDeath Date NULL,
PRIMARY KEY (actorID)
);

DROP TABLE IF EXISTS INVENTORY;
CREATE TABLE INVENTORY (
inventoryID int NOT NULL AUTO_INCREMENT,
title varchar(250) NOT NULL,
unitPrice decimal(5,2) NOT NULL,
markup decimal(5,2) NOT NULL,
discount decimal(5,2) NOT NULL,
qtyInStock int NOT NULL,
genreID int NOT NULL,
supplierID int NULL,
PRIMARY KEY (inventoryID),
FOREIGN KEY (genreID)
REFERENCES GENRE(genreID),
FOREIGN KEY (supplierID)
REFERENCES SUPPLIER(supplierID)
);
DROP TABLE IF EXISTS MANUFACTURER;
CREATE TABLE MANUFACTURER (
manufacturerID int NOT NULL AUTO_INCREMENT,
manufacturerName varchar(50) NOT NULL,
Phone varchar(15) NOT NULL,
Email varchar(50) NOT NULL,
Address varchar(100) NOT NULL,
Suburb varchar(50) NOT NULL,
Postcode int NOT NULL,
Country varchar(50) NULL,
PRIMARY KEY (manufacturerID)
);
DROP TABLE IF EXISTS DIRECTOR;
CREATE TABLE DIRECTOR (
directorID int NOT NULL AUTO_INCREMENT,
FirstName varchar(50) NOT NULL,
LastName varchar(50) NOT NULL,
PlaceOfBirth varchar(100) NOT NULL,
DateOfBirth Date NOT NULL,
DateOfDeath Date NULL,
PRIMARY KEY (directorID)
);

DROP TABLE IF EXISTS COLLECTION;
CREATE TABLE COLLECTION (
collectionID int NOT NULL AUTO_INCREMENT,
collectionName varchar(50) NOT NULL,
Description varchar(250) NOT NULL,
dateReleased Date NOT NULL,
PRIMARY KEY (collectionID)
);
DROP TABLE IF EXISTS COLLECTIBLE;
CREATE TABLE COLLECTIBLE (
inventoryID int NOT NULL,
madeYear int NOT NULL,
manufacturerID int NOT NULL,
PRIMARY KEY (inventoryID),
FOREIGN KEY (inventoryID)
REFERENCES INVENTORY(inventoryID),
FOREIGN KEY (manufacturerID)
REFERENCES MANUFACTURER(manufacturerID)
);
DROP TABLE IF EXISTS COLLECTION_MEMBER;
CREATE TABLE COLLECTION_MEMBER (
inventoryID int NOT NULL,
collectionID int NOT NULL,
PRIMARY KEY (inventoryID, collectionID),
FOREIGN KEY (inventoryID)
REFERENCES INVENTORY(inventoryID),
FOREIGN KEY (collectionID)
REFERENCES COLLECTION(collectionID)
);
DROP TABLE IF EXISTS MOVIE;
CREATE TABLE MOVIE (
inventoryID int NOT NULL,
releaseYear int NOT NULL,
classification varchar(5) NOT NULL,
starRating int NOT NULL,
directorID int NOT NULL,
PRIMARY KEY (inventoryID),
FOREIGN KEY (inventoryID)
REFERENCES INVENTORY(inventoryID),
FOREIGN KEY (directorID)
REFERENCES DIRECTOR(directorID)
);

DROP TABLE IF EXISTS CAST;
CREATE TABLE CAST (
actorID int NOT NULL,
inventID int NOT NULL,
role varchar(50) NOT NULL,
PRIMARY KEY (actorID,inventID),
FOREIGN KEY (inventID)
REFERENCES MOVIE(inventoryID),
FOREIGN KEY (actorID)
REFERENCES ACTOR(actorID)
);
DROP TABLE IF EXISTS CUSTOMER;
CREATE TABLE CUSTOMER (
customerID int NOT NULL AUTO_INCREMENT,
FirstName varchar(50) NOT NULL,
LastName varchar(50) NOT NULL,
Phone varchar(15) NOT NULL,
Email varchar(50) NOT NULL,
Address varchar(100) NOT NULL,
Suburb varchar(50) NOT NULL,
Postcode int NOT NULL,
loyaltyDiscount decimal(5,2) NULL,
PRIMARY KEY (customerID)
);
DROP TABLE IF EXISTS RECEIPT;
CREATE TABLE RECEIPT (
receiptID int NOT NULL AUTO_INCREMENT,
customerID int NOT NULL,
receiptDate date NOT NULL,
PRIMARY KEY (receiptID),
FOREIGN KEY (customerID)
REFERENCES CUSTOMER(customerID)
);
DROP TABLE IF EXISTS PURCHASE;
CREATE TABLE PURCHASE (
receiptID int NOT NULL,
inventoryID int NOT NULL,
quantity int NOT NULL,
amountPaid decimal(5,2) NOT NULL,
PRIMARY KEY (receiptID),
FOREIGN KEY (receiptID)
REFERENCES RECEIPT(receiptID),

FOREIGN KEY (inventoryID)
REFERENCES INVENTORY(inventoryID)
);
Task 3
Insert Statements
use Startstruck_Entertainment_StudentID;
INSERT INTO GENRE (genreName, Description) VALUES
("Comedy",""),
("Sports", "Fictional and sports documentaries"),
("Children","All age groups to 18"),
("SciFi",""),
("Crime",""),
("Documentary","Factual accounts including science and history"),
("Fiction","Any other fiction"),
("Fantasy",""),
("Adventure",""),
("Action","");
INSERT INTO SUPPLIER (supplierName, ContactName, Phone, Email, Address,
Suburb, Postcode, Country, Comments) values
("Figures’n’Toys","Anita Schmidt","0456789163","[e-mail]","94 Lancaster
St","Collingwood",3066,"Australia",""),
("Mega Entertainment","James
McGrath","[phone]","[e-mail]","32 Easy
St","Perth",6140,"Australia",""),
("Crazy Collectibles","Ernie
Miller","0448789987","[e-mail]","3 North
Road","Auckland",1111,"New Zealand",""),
("Rare Films","Samantha Stevens","0751672924","rarefilms.com.au","9 Newcastle
Ave","Cabramatta",2166,"Australia",""),
("Premiere Entertainment","Winston
Jones","0657168342","premiereentertainment.com","1548 Hume
Hwy","Glenfield",2167,"Australia","Christmas orders by November 15"),
("Movie Magic","Sandor Butterfield","[phone]","sales@movie-
magic.co.uk","88 Bond Parade","London",9911,"UK","Recalculate VAT"),
("VIC Entertainment","Rupert
Ballinger","0498528417","vicentertainment.gov.au","62
BluebirdCrt","Collingwood",3066,"Australia",""),
("Samson Films","Delilah Jones","[phone]","samsonfilms.com.au","27
Fortitude Dr","Brisbane",4001,"Australia","Minimum order $100");
insert into ACTOR (FirstName, LastName, PlaceOfBirth, DateOfBirth, DateOfDeath)
values
("Christopher","Lee","Belgravia London (UK)",STR_TO_DATE("05-27-
1922","%m-%d-%Y"),STR_TO_DATE("06-07-2015","%m-%d-%Y")),

("Marilyn","Monroe","Los Angeles California (USA)",STR_TO_DATE("06-01-
1926","%m-%d-%Y"),STR_TO_DATE("08-05-1962","%m-%d-%Y"));
insert into ACTOR (FirstName, LastName, PlaceOfBirth, DateOfBirth) values
("Chris","Hemsworth","Melbourne Vic (Aus)",STR_TO_DATE("08-11-1983","%m-
%d-%Y")),
("Angelina","Jolie","Los Angeles, Californaia (USA)",STR_TO_DATE("06-04-
1975","%m-%d-%Y")),
("Meryl","Streep","Summit New Jersey (USA)",STR_TO_DATE("06-22-
1949","%m-%d-%Y")),
("Charlize","Theron","Benoni Gauteng (Sth Africa)",STR_TO_DATE("08-07-
1975","%m-%d-%Y")),
("Drew","Barrymore","Culver City California (USA)",STR_TO_DATE("02-22-
1975","%m-%d-%Y")),
("Lucy","Liu","Jackson Heights New York (USA)",STR_TO_DATE("12-02-
1968","%m-%d-%Y")),
("David","Tennant","Bathgate (UK)",STR_TO_DATE("04-18-1971","%m-%d-
%Y")),
("Dwayne (The Rock)","Johnson","Hayward California
(USA)",STR_TO_DATE("05-02-1972","%m-%d-%Y")),
("Robert (Jnr)","Downey","Manhattan, New York (USA)",STR_TO_DATE("04-04-
1966","%m-%d-%Y"));
insert into DIRECTOR (firstName, lastName, PlaceOfBirth, DateOfBirth) values
("Angelina","Jolie","Los Angeles California (USA)",STR_TO_DATE("06-04-
1975","%m-%d-%Y")),
("Drew","Barrymore","Culver City, California (USA)",STR_TO_DATE("02-22-
1975","%m-%d-%Y")),
("Steven","Spielberg","Cincinnati, Ohio (USA)",STR_TO_DATE("12-18-
1946","%m-%d-%Y")),
("David","Heyman","London (UK)",STR_TO_DATE("07-26-1961","%m-%d-
%Y")),
("Quinton","Tarantino","Knoxville Tennessee (USA)",STR_TO_DATE("03-27-
1963","%m-%d-%Y")),
("Kenneth","Branagh","Belfast (UK)",STR_TO_DATE("12-10-1960","%m-%d-
%Y"));
insert into DIRECTOR (firstName, lastName, PlaceOfBirth, DateOfBirth,
DateOfDeath) values
("Alfred","Hitchcock","London (UK)",STR_TO_DATE("08-13-1899","%m-%d-
%Y"), STR_TO_DATE("04-29-1980","%m-%d-%Y")),
("Terence","Fisher","Maida Vale, London (UK)",STR_TO_DATE("02-23-
1904","%m-%d-%Y"), STR_TO_DATE("06-18-1980","%m-%d-%Y"));
insert into MANUFACTURER (manufacturerName, Phone, Email, Address, Suburb,
Postcode, Country) values
("Focus Home Interactive", "[phone]", "[e-mail]","9 Rue de Chat
Noir","Paris", 7519,"France"),
("Movie Figure Smithy", "[phone]", "[e-mail]","4 Beach Road
East","Wan Chai", 7654, "Hong Kong"),

("Modelworks", "[phone]", "[e-mail]","12 Danshui
Street","Zhongzheng", 1020, "Taiwan"),
("Rockstar", "[phone]", "[e-mail]","40 Grand
Drive","Chicago", 1999, "USA");
insert into CUSTOMER (firstName, lastName, Phone, Email, Address, Suburb,
Postcode) values
("Brigg","John","53159510","[e-mail]","1 Bells
Road","Napoleons","3352"),
("Summers","Joe","45613208","[e-mail]","52 Main
St","Maryborough","3465"),
("Winter","Sam","12356713","[e-mail]","2156 Skipton
Rd","Sebastopol","3356"),
("Fall","Sally","15626543","[e-mail]","65 Commercial
St","Clunes","3370"),
("Spencer","Troy","040853185","[e-mail]","65Crest
Ave","Ballarat","3353"),
("Jones","Tommy","51657896","[e-mail]","1254 Sturt
St","Ballarat","3353");
insert into CUSTOMER (firstName, lastName, Phone, Email, Address, Suburb,
Postcode, loyaltyDiscount ) values
("Spring","Mary","13265413","[e-mail]","98 Neil
St","Beaufort","3373",0.12),
("Jones","Pat","049513572","[e-mail]","46 Wattle
Ave","Wendouree","3355",0.1),
("Matthews","Doreen","21356467","[e-mail]","7 Hepburn Springs
Blvd","Daylesford","3460",0.15),
("McKenzie","Barry","32165489","[e-mail]","47 Gillies
St","Ballarat","3350",0.19);
insert into INVENTORY (title, unitPrice, markup, discount, qtyInStock, genreID,
supplierID) values
("R2D2", 27.00,160,0,2,4,3),
("Han Solo Figure", 10.00,170,0,12,4,3),
("Millenium Falcon Model", 128.00,140,0,7,4,2),
("Syberia", 10.00,110,5,2,4,4),
("Devil Wears Prada", 10.00,130,5,14,1,8),
("Great Expectations", 10.00,180,5,6,7,2),
("Wookie Figure", 418.00,130,0,1,4,2),
("Dekker Figure", 22.00,210,0,6,4,1),
("USS Enterprise Model", 110.00,120,10,2,4,4);
insert into MOVIE (inventoryID,releaseYear, classification, starRating, directorID)
values
(4,1958,"M",4,7 ),
(5,2011,"M",4,8 ),
(6,2006,"MA",3,2 );
insert into COLLECTIBLE (inventoryID,madeYear, manufacturerID) values

(1,2016,1),
(2,2011,1),
(3,2006,2),
(7,2016,2),
(8,1993,4),
(9,2013,4);
insert into COLLECTION (collectionName, Description, dateReleased) values
("Star Wars Droids", "6 piece Star Wars droids collection",STR_TO_DATE("07-01-
2015","%m-%d-%Y")),
("Star WarsMega Collection", "8 piece Star Wars collection of characters from death
star",STR_TO_DATE("06-15-2008","%m-%d-%Y")),
("Alien", "3 piece Alien collection",STR_TO_DATE("09-30-1999","%m-%d-%Y")),
("Startrek", "10 part super collection",STR_TO_DATE("12-24-2001","%m-%d-
%Y")),
("Spaceships collection", "15 part spaceship collection from a variety of
movies",STR_TO_DATE("12-24-2001","%m-%d-%Y")),
("Bladerunner", "5 character original Bladerunner collection",STR_TO_DATE("12-
15-1990","%m-%d-%Y"));
insert into CAST (actorID, inventID, role) values
(1,4, "Hero"),
(2,4, "Heroine"),
(3,4, "Comedian"),
(4,5,"Comedian"),
(1,5,"Hero"),
(5,5,"Heroine"),
(6,5,"Villain"),
(7,6, "Hero"),
(8,6, "Heroine"),
(9,6, "Comedian");
Task 4
SQL Queries
use Startstruck_Entertainment_StudentID;
/* Query a */
select * from Supplier order by Suburb;
/* Query b */
Select title, supplierName, Email, Phone, Suburb from Inventory inner join Movie on
Inventory.inventoryID=Movie.inventoryID
inner join Supplier on Inventory.supplierID=Supplier.supplierID;

/* Query c */
select CONCAT(firstName," " ,lastName) as 'Actor Name',
YEAR(now()) - YEAR(DateOfBirth) - (DATE_FORMAT(now(), '%m%d') <
DATE_FORMAT(DateOfBirth, '%m%d')) as Age
from Actor where DateOfDeath is null;
/* Query d */
Select Inventory.title, genreName from Inventory inner join Genre
on Inventory.genreID=Genre.genreID order by title desc;
/* Query e */
select title, unitPrice, markup, qtyInStock, supplierName, manufacturerName
from COLLECTIBLE inner join inventory on
COLLECTIBLE.inventoryID=inventory.inventoryID
inner join SUPPLIER on SUPPLIER.supplierID=inventory.supplierID
inner join manufacturer on
manufacturer.manufacturerID=COLLECTIBLE.manufacturerID
order by title;
/* Query f */
Select genreName from Genre where genreID not in (select genreID from Inventory);
/* Query g */
SET SQL_SAFE_UPDATES=0;
delete from customer where Postcode=3353;
/* Query h */
SET SQL_SAFE_UPDATES=0;
update Inventory set title="Wookie Figure – live size" where title="Wookie Figure";
/* Query i */
select inventory.title, CONCAT(ACTOR.firstName," " ,ACTOR.lastName) as 'Actor
Name', CONCAT(DIRECTOR.firstName," " ,DIRECTOR.lastName) as 'Director
Name'
from MOVIE inner join inventory on MOVIE.inventoryID=inventory.inventoryID
inner join DIRECTOR on MOVIE.directorID=DIRECTOR.directorID
inner join CAST on MOVIE.inventoryID=CAST.inventID
inner join ACTOR on CAST.actorID=ACTOR.actorID;

/* Query j */
SET autocommit=0;
START TRANSACTION;
insert into DIRECTOR (directorID, firstName, lastName, PlaceOfBirth, DateOfBirth)
values
(9, "Steven", "Soderberghin", "Atlanta", STR_TO_DATE("01-14-1963","%m-%d-
%Y"));
insert into INVENTORY (inventoryID, title, unitPrice, markup, discount, qtyInStock,
genreID) values
(10, "Solaris", 18,35,10,27,4);
insert into MOVIE (inventoryID,releaseYear, classification, starRating, directorID)
values
(10,2002,'M',4,9);
COMMIT;
/* Query k */
select title, qtyInStock, (unitPrice + markup) as 'Sale Price' from inventory;
/* Query l */
Select Actor.firstName, Actor.lastName, title, genreName
from Actor inner join Cast on Actor.ActorID=Cast.ActorID
inner join Inventory on Inventory.inventoryID=Cast.inventID
inner join Genre on Inventory.genreID=Genre.genreID order by Actor.lastName desc;
References
P. Litwin (n.d.), Fundamentals of Relational Database Design, Retrieved from
http://www.deeptraining.com/litwin/dbdesign/
FundamentalsOfRelationalDatabaseDesign.aspx
Tutorialspoint (2016), Relational Data Model, Retrieved from
http://www.tutorialspoint.com/dbms/relational_data_model.htm>