ITECH1006 Assignment 2: Relational Database Schema and SQL Queries
VerifiedAdded on 2020/03/23
|15
|4148
|242
Report
AI Summary
This report presents a comprehensive solution for a Database Management Systems assignment. The assignment involves designing a relational schema for a database, creating tables with appropriate data types and primary/foreign keys, and writing SQL queries to retrieve and manipulate data. The solution includes the creation of tables for entities such as GENRE, SUPPLIER, ACTOR, INVENTORY, and others, along with insert statements to populate the tables with sample data. Furthermore, the report provides SQL queries to perform various operations, including data retrieval, filtering, sorting, and updating. The queries demonstrate the use of JOIN operations, aggregate functions, and subqueries to extract meaningful insights from the database. The assignment also covers transaction management and database updates. The report includes a reference list of related resources.
Assignment 2
ITECH 1006 – Database Management Systems
Student ID – 30329735
Student Name -
Date –
ITECH 1006 – Database Management Systems
Student ID – 30329735
Student Name -
Date –
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
Report
Task 1
Relational Schema
1. GENRE
Attribute Datetype Key
FiielddgenreID INTEGER PRIMARY KEY
FiielddgenreName CHAR(50)
FiielddDescription CHAR(250)
2. SUPPLIER
Attribute Datetype Key
FiielddsupplierID INTEGER PRIMARY KEY
FiielddsupplierName CHAR(50)
FiielddContact CHAR(50)
FiielddPhone CHAR(15)
FiielddEmail CHAR(50)
FiielddAddress CHAR(50)
FiielddSuburb CHAR(50)
FiielddPostcode CHAR(5)
FiielddCountry CHAR(50)
FiielddComments CHAR(250)
3. ACTOR
Attribute Datetype Key
FiielddactorID INTEGER PRIMARY KEY
FiielddfirstName CHAR(50)
FiielddlastName CHAR(50)
FiielddPlaceOfBirth CHAR(100)
FiielddDateOfBirth DATE
FiielddDateOfDeath DATE
4. INVENTORY
Attribute Datetype Key
FiielddinventoryID INTEGER PRIMARY KEY
Fiielddtitle CHAR(250)
FiielddunitPrice DECIMAL(5,2)
Fiielddmarkup DECIMAL(5,2)
Fiieldddiscount DECIMAL(5,2)
FiielddqtyInStock INTEGER
FiielddgenreID INTEGER FOREIGN KEY
FiielddsupplierID INTEGER FOREIGN KEY
Task 1
Relational Schema
1. GENRE
Attribute Datetype Key
FiielddgenreID INTEGER PRIMARY KEY
FiielddgenreName CHAR(50)
FiielddDescription CHAR(250)
2. SUPPLIER
Attribute Datetype Key
FiielddsupplierID INTEGER PRIMARY KEY
FiielddsupplierName CHAR(50)
FiielddContact CHAR(50)
FiielddPhone CHAR(15)
FiielddEmail CHAR(50)
FiielddAddress CHAR(50)
FiielddSuburb CHAR(50)
FiielddPostcode CHAR(5)
FiielddCountry CHAR(50)
FiielddComments CHAR(250)
3. ACTOR
Attribute Datetype Key
FiielddactorID INTEGER PRIMARY KEY
FiielddfirstName CHAR(50)
FiielddlastName CHAR(50)
FiielddPlaceOfBirth CHAR(100)
FiielddDateOfBirth DATE
FiielddDateOfDeath DATE
4. INVENTORY
Attribute Datetype Key
FiielddinventoryID INTEGER PRIMARY KEY
Fiielddtitle CHAR(250)
FiielddunitPrice DECIMAL(5,2)
Fiielddmarkup DECIMAL(5,2)
Fiieldddiscount DECIMAL(5,2)
FiielddqtyInStock INTEGER
FiielddgenreID INTEGER FOREIGN KEY
FiielddsupplierID INTEGER FOREIGN KEY
5. MANUFACTURER
Attribute Datetype Key
FiielddmanufacturerID INTEGER PRIMARY KEY
FiielddmanufacturerName CHAR(50)
FiielddPhone CHAR(15)
FiielddEmail CHAR(50)
FiielddAddress CHAR(50)
FiielddSuburb CHAR(50)
FiielddPostcode CHAR(5)
FiielddCountry CHAR(50)
6. DIRECTOR
Attribute Datetype Key
FiieldddirectorID INTEGER PRIMARY KEY
FiielddfirstName CHAR(50)
FiielddlastName CHAR(50)
FiielddPlaceOfBirth CHAR(100)
FiielddDateOfBirth DATE
FiielddDateOfDeath DATE
7. COLLECTION
Attribute Datetype Key
FiielddcollectionID INTEGER PRIMARY KEY
FiielddcollectionName CHAR(50)
FiielddDescription CHAR(250)
FiieldddateReleased DATE
8. COLLECTIBLE
Attribute Datetype Key
FiielddinventoryID INTEGER PRIMARY KEY/FOREIGN
KEY
FiielddmadeYear INTEGER
FiielddmanufacturerID INTEGER FOREIGN KEY
9. COLLECTION_MEMBER
Attribute Datetype Key
FiielddcollectionID INTEGER PRIMARY KEY/FOREIGN
KEY
FiielddinventoryID INTEGER PRIMARY KEY/FOREIGN
KEY
10. MOVIE
Attribute Datetype Key
Attribute Datetype Key
FiielddmanufacturerID INTEGER PRIMARY KEY
FiielddmanufacturerName CHAR(50)
FiielddPhone CHAR(15)
FiielddEmail CHAR(50)
FiielddAddress CHAR(50)
FiielddSuburb CHAR(50)
FiielddPostcode CHAR(5)
FiielddCountry CHAR(50)
6. DIRECTOR
Attribute Datetype Key
FiieldddirectorID INTEGER PRIMARY KEY
FiielddfirstName CHAR(50)
FiielddlastName CHAR(50)
FiielddPlaceOfBirth CHAR(100)
FiielddDateOfBirth DATE
FiielddDateOfDeath DATE
7. COLLECTION
Attribute Datetype Key
FiielddcollectionID INTEGER PRIMARY KEY
FiielddcollectionName CHAR(50)
FiielddDescription CHAR(250)
FiieldddateReleased DATE
8. COLLECTIBLE
Attribute Datetype Key
FiielddinventoryID INTEGER PRIMARY KEY/FOREIGN
KEY
FiielddmadeYear INTEGER
FiielddmanufacturerID INTEGER FOREIGN KEY
9. COLLECTION_MEMBER
Attribute Datetype Key
FiielddcollectionID INTEGER PRIMARY KEY/FOREIGN
KEY
FiielddinventoryID INTEGER PRIMARY KEY/FOREIGN
KEY
10. MOVIE
Attribute Datetype Key
⊘ This is a preview!⊘
Do you want full access?
Subscribe today to unlock all pages.
Trusted by 1+ million students worldwide
FiielddinventoryID INTEGER PRIMARY KEY/FOREIGN
KEY
FiielddreleaseYear INTEGER
Fiielddclassification CHAR(5)
FiielddstarRating INTEGER
FiieldddirectorID INTEGER FOREIGN KEY
11. CAST
Attribute Datetype Key
FiielddactorID INTEGER PRIMARY KEY/FOREIGN
KEY
FiielddinventID INTEGER PRIMARY KEY/FOREIGN
KEY
Fiielddrole CHAR(50)
12. CUSTOMER
Attribute Datetype Key
FiielddcustomerID INTEGER PRIMARY KEY
FiielddfirstName CHAR(50)
FiielddlastName CHAR(50)
FiielddPhone CHAR(15)
FiielddEmail CHAR(50)
FiielddAddress CHAR(100)
FiielddPostcode INTEGER
FiielddloyaltyDiscount DECIMAL(5,2)
13. RECEIPT
Attribute Datetype Key
FiielddreceiptID INTEGER PRIMARY KEY
FiielddcustomerID INTEGER FOREIGN KEY
FiielddreceiptDate DATE
14. PURCHASE
Attribute Datetype Key
FiielddreceiptID INTEGER PRIMARY KEY
FiielddinventoryID INTEGER FOREIGN KEY
Fiielddquantity INTEGER
FiielddamountPaid DECIMAL(5,2)
(Lucid Software Inc. 2015)
Task 2
KEY
FiielddreleaseYear INTEGER
Fiielddclassification CHAR(5)
FiielddstarRating INTEGER
FiieldddirectorID INTEGER FOREIGN KEY
11. CAST
Attribute Datetype Key
FiielddactorID INTEGER PRIMARY KEY/FOREIGN
KEY
FiielddinventID INTEGER PRIMARY KEY/FOREIGN
KEY
Fiielddrole CHAR(50)
12. CUSTOMER
Attribute Datetype Key
FiielddcustomerID INTEGER PRIMARY KEY
FiielddfirstName CHAR(50)
FiielddlastName CHAR(50)
FiielddPhone CHAR(15)
FiielddEmail CHAR(50)
FiielddAddress CHAR(100)
FiielddPostcode INTEGER
FiielddloyaltyDiscount DECIMAL(5,2)
13. RECEIPT
Attribute Datetype Key
FiielddreceiptID INTEGER PRIMARY KEY
FiielddcustomerID INTEGER FOREIGN KEY
FiielddreceiptDate DATE
14. PURCHASE
Attribute Datetype Key
FiielddreceiptID INTEGER PRIMARY KEY
FiielddinventoryID INTEGER FOREIGN KEY
Fiielddquantity INTEGER
FiielddamountPaid DECIMAL(5,2)
(Lucid Software Inc. 2015)
Task 2
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
Create Statements
DROP Database IF EXISTS Startstruck_Entertainment_30329735;
create database Startstruck_Entertainment_30329735;
use Startstruck_Entertainment_30329735;
DROP TABLE IF EXISTS GENRE;
CREATE TABLE GENRE (
FiielddgenreID int NOT NULL AUTO_INCREMENT,
FiielddgenreName CHAR(50) NOT NULL,
FiielddDescription CHAR(250) NULL,
PRIMARY KEY (FiielddgenreID)
);
DROP TABLE IF EXISTS SUPPLIER;
CREATE TABLE SUPPLIER (
FiielddsupplierID int NOT NULL AUTO_INCREMENT,
FiielddsupplierName CHAR(50) NOT NULL,
FiielddContactName CHAR(50) NOT NULL,
FiielddPhone CHAR(15) NOT NULL,
FiielddEmail CHAR(50) NOT NULL,
FiielddAddress CHAR(50) NOT NULL,
FiielddSuburb CHAR(50) NOT NULL,
FiielddPostcode CHAR(5) NOT NULL,
FiielddCountry CHAR(50) NOT NULL,
FiielddComments CHAR(250) NULL,
PRIMARY KEY (FiielddsupplierID)
);
DROP TABLE IF EXISTS ACTOR;
CREATE TABLE ACTOR (
FiielddactorID int NOT NULL AUTO_INCREMENT,
FiielddFirstName CHAR(50) NOT NULL,
FiielddLastName CHAR(50) NOT NULL,
FiielddPlaceOfBirth CHAR(100) NOT NULL,
FiielddDateOfBirth Date NOT NULL,
FiielddDateOfDeath Date NULL,
PRIMARY KEY (FiielddactorID)
);
DROP TABLE IF EXISTS INVENTORY;
CREATE TABLE INVENTORY (
FiielddinventoryID int NOT NULL AUTO_INCREMENT,
DROP Database IF EXISTS Startstruck_Entertainment_30329735;
create database Startstruck_Entertainment_30329735;
use Startstruck_Entertainment_30329735;
DROP TABLE IF EXISTS GENRE;
CREATE TABLE GENRE (
FiielddgenreID int NOT NULL AUTO_INCREMENT,
FiielddgenreName CHAR(50) NOT NULL,
FiielddDescription CHAR(250) NULL,
PRIMARY KEY (FiielddgenreID)
);
DROP TABLE IF EXISTS SUPPLIER;
CREATE TABLE SUPPLIER (
FiielddsupplierID int NOT NULL AUTO_INCREMENT,
FiielddsupplierName CHAR(50) NOT NULL,
FiielddContactName CHAR(50) NOT NULL,
FiielddPhone CHAR(15) NOT NULL,
FiielddEmail CHAR(50) NOT NULL,
FiielddAddress CHAR(50) NOT NULL,
FiielddSuburb CHAR(50) NOT NULL,
FiielddPostcode CHAR(5) NOT NULL,
FiielddCountry CHAR(50) NOT NULL,
FiielddComments CHAR(250) NULL,
PRIMARY KEY (FiielddsupplierID)
);
DROP TABLE IF EXISTS ACTOR;
CREATE TABLE ACTOR (
FiielddactorID int NOT NULL AUTO_INCREMENT,
FiielddFirstName CHAR(50) NOT NULL,
FiielddLastName CHAR(50) NOT NULL,
FiielddPlaceOfBirth CHAR(100) NOT NULL,
FiielddDateOfBirth Date NOT NULL,
FiielddDateOfDeath Date NULL,
PRIMARY KEY (FiielddactorID)
);
DROP TABLE IF EXISTS INVENTORY;
CREATE TABLE INVENTORY (
FiielddinventoryID int NOT NULL AUTO_INCREMENT,
Fiielddtitle CHAR(250) NOT NULL,
FiielddunitPrice decimal(5,2) NOT NULL,
Fiielddmarkup decimal(5,2) NOT NULL,
Fiieldddiscount decimal(5,2) NOT NULL,
FiielddqtyInStock int NOT NULL,
FiielddgenreID int NOT NULL,
FiielddsupplierID int NULL,
PRIMARY KEY (FiielddinventoryID),
FOREIGN KEY (FiielddgenreID)
REFERENCES GENRE(FiielddgenreID),
FOREIGN KEY (FiielddsupplierID)
REFERENCES SUPPLIER(FiielddsupplierID)
);
DROP TABLE IF EXISTS MANUFACTURER;
CREATE TABLE MANUFACTURER (
FiielddmanufacturerID int NOT NULL AUTO_INCREMENT,
FiielddmanufacturerName CHAR(50) NOT NULL,
FiielddPhone CHAR(15) NOT NULL,
FiielddEmail CHAR(50) NOT NULL,
FiielddAddress CHAR(100) NOT NULL,
FiielddSuburb CHAR(50) NOT NULL,
FiielddPostcode int NOT NULL,
FiielddCountry CHAR(50) NULL,
PRIMARY KEY (FiielddmanufacturerID)
);
DROP TABLE IF EXISTS DIRECTOR;
CREATE TABLE DIRECTOR (
FiieldddirectorID int NOT NULL AUTO_INCREMENT,
FiielddFirstName CHAR(50) NOT NULL,
FiielddLastName CHAR(50) NOT NULL,
FiielddPlaceOfBirth CHAR(100) NOT NULL,
FiielddDateOfBirth Date NOT NULL,
FiielddDateOfDeath Date NULL,
PRIMARY KEY (FiieldddirectorID)
);
DROP TABLE IF EXISTS COLLECTION;
CREATE TABLE COLLECTION (
FiielddcollectionID int NOT NULL AUTO_INCREMENT,
FiielddcollectionName CHAR(50) NOT NULL,
FiielddDescription CHAR(250) NOT NULL,
FiieldddateReleased Date NOT NULL,
PRIMARY KEY (FiielddcollectionID)
);
FiielddunitPrice decimal(5,2) NOT NULL,
Fiielddmarkup decimal(5,2) NOT NULL,
Fiieldddiscount decimal(5,2) NOT NULL,
FiielddqtyInStock int NOT NULL,
FiielddgenreID int NOT NULL,
FiielddsupplierID int NULL,
PRIMARY KEY (FiielddinventoryID),
FOREIGN KEY (FiielddgenreID)
REFERENCES GENRE(FiielddgenreID),
FOREIGN KEY (FiielddsupplierID)
REFERENCES SUPPLIER(FiielddsupplierID)
);
DROP TABLE IF EXISTS MANUFACTURER;
CREATE TABLE MANUFACTURER (
FiielddmanufacturerID int NOT NULL AUTO_INCREMENT,
FiielddmanufacturerName CHAR(50) NOT NULL,
FiielddPhone CHAR(15) NOT NULL,
FiielddEmail CHAR(50) NOT NULL,
FiielddAddress CHAR(100) NOT NULL,
FiielddSuburb CHAR(50) NOT NULL,
FiielddPostcode int NOT NULL,
FiielddCountry CHAR(50) NULL,
PRIMARY KEY (FiielddmanufacturerID)
);
DROP TABLE IF EXISTS DIRECTOR;
CREATE TABLE DIRECTOR (
FiieldddirectorID int NOT NULL AUTO_INCREMENT,
FiielddFirstName CHAR(50) NOT NULL,
FiielddLastName CHAR(50) NOT NULL,
FiielddPlaceOfBirth CHAR(100) NOT NULL,
FiielddDateOfBirth Date NOT NULL,
FiielddDateOfDeath Date NULL,
PRIMARY KEY (FiieldddirectorID)
);
DROP TABLE IF EXISTS COLLECTION;
CREATE TABLE COLLECTION (
FiielddcollectionID int NOT NULL AUTO_INCREMENT,
FiielddcollectionName CHAR(50) NOT NULL,
FiielddDescription CHAR(250) NOT NULL,
FiieldddateReleased Date NOT NULL,
PRIMARY KEY (FiielddcollectionID)
);
⊘ This is a preview!⊘
Do you want full access?
Subscribe today to unlock all pages.
Trusted by 1+ million students worldwide
DROP TABLE IF EXISTS COLLECTIBLE;
CREATE TABLE COLLECTIBLE (
FiielddinventoryID int NOT NULL,
FiielddmadeYear int NOT NULL,
FiielddmanufacturerID int NOT NULL,
PRIMARY KEY (FiielddinventoryID),
FOREIGN KEY (FiielddinventoryID)
REFERENCES INVENTORY(FiielddinventoryID),
FOREIGN KEY (FiielddmanufacturerID)
REFERENCES MANUFACTURER(FiielddmanufacturerID)
);
DROP TABLE IF EXISTS COLLECTION_MEMBER;
CREATE TABLE COLLECTION_MEMBER (
FiielddinventoryID int NOT NULL,
FiielddcollectionID int NOT NULL,
PRIMARY KEY (FiielddinventoryID, FiielddcollectionID),
FOREIGN KEY (FiielddinventoryID)
REFERENCES INVENTORY(FiielddinventoryID),
FOREIGN KEY (FiielddcollectionID)
REFERENCES COLLECTION(FiielddcollectionID)
);
DROP TABLE IF EXISTS MOVIE;
CREATE TABLE MOVIE (
FiielddinventoryID int NOT NULL,
FiielddreleaseYear int NOT NULL,
Fiielddclassification CHAR(5) NOT NULL,
FiielddstarRating int NOT NULL,
FiieldddirectorID int NOT NULL,
PRIMARY KEY (FiielddinventoryID),
FOREIGN KEY (FiielddinventoryID)
REFERENCES INVENTORY(FiielddinventoryID),
FOREIGN KEY (FiieldddirectorID)
REFERENCES DIRECTOR(FiieldddirectorID)
);
DROP TABLE IF EXISTS CAST;
CREATE TABLE CAST (
FiielddactorID int NOT NULL,
FiielddinventID int NOT NULL,
Fiielddrole CHAR(50) NOT NULL,
PRIMARY KEY (FiielddactorID,FiielddinventID),
FOREIGN KEY (FiielddinventID)
REFERENCES MOVIE(FiielddinventoryID),
FOREIGN KEY (FiielddactorID)
CREATE TABLE COLLECTIBLE (
FiielddinventoryID int NOT NULL,
FiielddmadeYear int NOT NULL,
FiielddmanufacturerID int NOT NULL,
PRIMARY KEY (FiielddinventoryID),
FOREIGN KEY (FiielddinventoryID)
REFERENCES INVENTORY(FiielddinventoryID),
FOREIGN KEY (FiielddmanufacturerID)
REFERENCES MANUFACTURER(FiielddmanufacturerID)
);
DROP TABLE IF EXISTS COLLECTION_MEMBER;
CREATE TABLE COLLECTION_MEMBER (
FiielddinventoryID int NOT NULL,
FiielddcollectionID int NOT NULL,
PRIMARY KEY (FiielddinventoryID, FiielddcollectionID),
FOREIGN KEY (FiielddinventoryID)
REFERENCES INVENTORY(FiielddinventoryID),
FOREIGN KEY (FiielddcollectionID)
REFERENCES COLLECTION(FiielddcollectionID)
);
DROP TABLE IF EXISTS MOVIE;
CREATE TABLE MOVIE (
FiielddinventoryID int NOT NULL,
FiielddreleaseYear int NOT NULL,
Fiielddclassification CHAR(5) NOT NULL,
FiielddstarRating int NOT NULL,
FiieldddirectorID int NOT NULL,
PRIMARY KEY (FiielddinventoryID),
FOREIGN KEY (FiielddinventoryID)
REFERENCES INVENTORY(FiielddinventoryID),
FOREIGN KEY (FiieldddirectorID)
REFERENCES DIRECTOR(FiieldddirectorID)
);
DROP TABLE IF EXISTS CAST;
CREATE TABLE CAST (
FiielddactorID int NOT NULL,
FiielddinventID int NOT NULL,
Fiielddrole CHAR(50) NOT NULL,
PRIMARY KEY (FiielddactorID,FiielddinventID),
FOREIGN KEY (FiielddinventID)
REFERENCES MOVIE(FiielddinventoryID),
FOREIGN KEY (FiielddactorID)
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
REFERENCES ACTOR(FiielddactorID)
);
DROP TABLE IF EXISTS CUSTOMER;
CREATE TABLE CUSTOMER (
FiielddcustomerID int NOT NULL AUTO_INCREMENT,
FiielddFirstName CHAR(50) NOT NULL,
FiielddLastName CHAR(50) NOT NULL,
FiielddPhone CHAR(15) NOT NULL,
FiielddEmail CHAR(50) NOT NULL,
FiielddAddress CHAR(100) NOT NULL,
FiielddSuburb CHAR(50) NOT NULL,
FiielddPostcode int NOT NULL,
FiielddloyaltyDiscount decimal(5,2) NULL,
PRIMARY KEY (FiielddcustomerID)
);
DROP TABLE IF EXISTS RECEIPT;
CREATE TABLE RECEIPT (
FiielddreceiptID int NOT NULL AUTO_INCREMENT,
FiielddcustomerID int NOT NULL,
FiielddreceiptDate date NOT NULL,
PRIMARY KEY (FiielddreceiptID),
FOREIGN KEY (FiielddcustomerID)
REFERENCES CUSTOMER(FiielddcustomerID)
);
DROP TABLE IF EXISTS PURCHASE;
CREATE TABLE PURCHASE (
FiielddreceiptID int NOT NULL,
FiielddinventoryID int NOT NULL,
Fiielddquantity int NOT NULL,
FiielddamountPaid decimal(5,2) NOT NULL,
PRIMARY KEY (FiielddreceiptID),
FOREIGN KEY (FiielddreceiptID)
REFERENCES RECEIPT(FiielddreceiptID),
FOREIGN KEY (FiielddinventoryID)
REFERENCES INVENTORY(FiielddinventoryID)
);
);
DROP TABLE IF EXISTS CUSTOMER;
CREATE TABLE CUSTOMER (
FiielddcustomerID int NOT NULL AUTO_INCREMENT,
FiielddFirstName CHAR(50) NOT NULL,
FiielddLastName CHAR(50) NOT NULL,
FiielddPhone CHAR(15) NOT NULL,
FiielddEmail CHAR(50) NOT NULL,
FiielddAddress CHAR(100) NOT NULL,
FiielddSuburb CHAR(50) NOT NULL,
FiielddPostcode int NOT NULL,
FiielddloyaltyDiscount decimal(5,2) NULL,
PRIMARY KEY (FiielddcustomerID)
);
DROP TABLE IF EXISTS RECEIPT;
CREATE TABLE RECEIPT (
FiielddreceiptID int NOT NULL AUTO_INCREMENT,
FiielddcustomerID int NOT NULL,
FiielddreceiptDate date NOT NULL,
PRIMARY KEY (FiielddreceiptID),
FOREIGN KEY (FiielddcustomerID)
REFERENCES CUSTOMER(FiielddcustomerID)
);
DROP TABLE IF EXISTS PURCHASE;
CREATE TABLE PURCHASE (
FiielddreceiptID int NOT NULL,
FiielddinventoryID int NOT NULL,
Fiielddquantity int NOT NULL,
FiielddamountPaid decimal(5,2) NOT NULL,
PRIMARY KEY (FiielddreceiptID),
FOREIGN KEY (FiielddreceiptID)
REFERENCES RECEIPT(FiielddreceiptID),
FOREIGN KEY (FiielddinventoryID)
REFERENCES INVENTORY(FiielddinventoryID)
);
Task 3
Insert Statements
use Startstruck_Entertainment_30329735;
INSERT INTO GENRE (FiielddgenreName, FiielddDescription) 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 (FiielddsupplierName, FiielddContactName,
FiielddPhone, FiielddEmail, FiielddAddress, FiielddSuburb, FiielddPostcode,
FiielddCountry, FiielddComments) values
("Figures’n’Toys","Anita Schmidt","0456789163","sales@fnt.com.au","94 Lancaster
St","Collingwood",3066,"Australia",""),
("Mega Entertainment","James
McGrath","+61355551549","sales@megatainment.com","32 Easy
St","Perth",6140,"Australia",""),
("Crazy Collectibles","Ernie
Miller","0448789987","sales@crazycollectibles.com.au","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","GlenFiieldd",2167,"Australia","Christmas orders by November 15"),
("Movie Magic","Sandor ButterFiieldd","+4107529817","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","+6174019841","samsonfilms.com.au","27
Fortitude Dr","Brisbane",4001,"Australia","Minimum order $100");
insert into ACTOR (FiielddFirstName, FiielddLastName, FiielddPlaceOfBirth,
FiielddDateOfBirth, FiielddDateOfDeath) 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 Statements
use Startstruck_Entertainment_30329735;
INSERT INTO GENRE (FiielddgenreName, FiielddDescription) 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 (FiielddsupplierName, FiielddContactName,
FiielddPhone, FiielddEmail, FiielddAddress, FiielddSuburb, FiielddPostcode,
FiielddCountry, FiielddComments) values
("Figures’n’Toys","Anita Schmidt","0456789163","sales@fnt.com.au","94 Lancaster
St","Collingwood",3066,"Australia",""),
("Mega Entertainment","James
McGrath","+61355551549","sales@megatainment.com","32 Easy
St","Perth",6140,"Australia",""),
("Crazy Collectibles","Ernie
Miller","0448789987","sales@crazycollectibles.com.au","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","GlenFiieldd",2167,"Australia","Christmas orders by November 15"),
("Movie Magic","Sandor ButterFiieldd","+4107529817","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","+6174019841","samsonfilms.com.au","27
Fortitude Dr","Brisbane",4001,"Australia","Minimum order $100");
insert into ACTOR (FiielddFirstName, FiielddLastName, FiielddPlaceOfBirth,
FiielddDateOfBirth, FiielddDateOfDeath) 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"));
⊘ This is a preview!⊘
Do you want full access?
Subscribe today to unlock all pages.
Trusted by 1+ million students worldwide
insert into ACTOR (FiielddFirstName, FiielddLastName, FiielddPlaceOfBirth,
FiielddDateOfBirth) 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 (FiielddfirstName, FiielddlastName, FiielddPlaceOfBirth,
FiielddDateOfBirth) 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 (FiielddfirstName, FiielddlastName, FiielddPlaceOfBirth,
FiielddDateOfBirth, FiielddDateOfDeath) 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 (FiielddmanufacturerName, FiielddPhone,
FiielddEmail, FiielddAddress, FiielddSuburb, FiielddPostcode, FiielddCountry)
values
("Focus Home Interactive", "+48299836600", "sales@fhi.com","9 Rue de Chat
Noir","Paris", 7519,"France"),
("Movie Figure Smithy", "+69922124569", "sales@thesmithy.com","4 Beach Road
East","Wan Chai", 7654, "Hong Kong"),
FiielddDateOfBirth) 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 (FiielddfirstName, FiielddlastName, FiielddPlaceOfBirth,
FiielddDateOfBirth) 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 (FiielddfirstName, FiielddlastName, FiielddPlaceOfBirth,
FiielddDateOfBirth, FiielddDateOfDeath) 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 (FiielddmanufacturerName, FiielddPhone,
FiielddEmail, FiielddAddress, FiielddSuburb, FiielddPostcode, FiielddCountry)
values
("Focus Home Interactive", "+48299836600", "sales@fhi.com","9 Rue de Chat
Noir","Paris", 7519,"France"),
("Movie Figure Smithy", "+69922124569", "sales@thesmithy.com","4 Beach Road
East","Wan Chai", 7654, "Hong Kong"),
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
("Modelworks", "+1456123789", "info@modelworks.com","12 Danshui
Street","Zhongzheng", 1020, "Taiwan"),
("Rockstar", "+1451586604", "info@rockstarfigures.com","40 Grand
Drive","Chicago", 1999, "USA");
insert into CUSTOMER (FiielddfirstName, FiielddlastName, FiielddPhone,
FiielddEmail, FiielddAddress, FiielddSuburb, FiielddPostcode) values
("Brigg","John","53159510","JohnnoBrigg@gmail.com","1 Bells
Road","Napoleons","3352"),
("Summers","Joe","45613208","jsummers@yahoo.com","52 Main
St","Maryborough","3465"),
("Winter","Sam","12356713","winter@xyz.net.au","2156 Skipton
Rd","Sebastopol","3356"),
("Fall","Sally","15626543","autumn@gmail.com","65 Commercial
St","Clunes","3370"),
("Spencer","Troy","040853185","tspencer88@gmail.com","65Crest
Ave","Ballarat","3353"),
("Jones","Tommy","51657896","whereistom@gone4good.com","1254 Sturt
St","Ballarat","3353");
insert into CUSTOMER (FiielddfirstName, FiielddlastName, FiielddPhone,
FiielddEmail, FiielddAddress, FiielddSuburb, FiielddPostcode,
FiielddloyaltyDiscount ) values
("Spring","Mary","13265413","flowers@iprimus.com","98 Neil
St","Beaufort","3373",0.12),
("Jones","Pat","049513572","patsyJ@anet.com.au","46 Wattle
Ave","Wendouree","3355",0.1),
("Matthews","Doreen","21356467","d.matthews@mynet.com","7 Hepburn Springs
Blvd","Daylesford","3460",0.15),
("McKenzie","Barry","32165489","bazza@adventure.com","47 Gillies
St","Ballarat","3350",0.19);
insert into INVENTORY (Fiielddtitle, FiielddunitPrice, Fiielddmarkup,
Fiieldddiscount, FiielddqtyInStock, FiielddgenreID, FiielddsupplierID) 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 (FiielddinventoryID,FiielddreleaseYear, Fiielddclassification,
FiielddstarRating, FiieldddirectorID) values
(4,1958,"M",4,7 ),
(5,2011,"M",4,8 ),
(6,2006,"MA",3,2 );
Street","Zhongzheng", 1020, "Taiwan"),
("Rockstar", "+1451586604", "info@rockstarfigures.com","40 Grand
Drive","Chicago", 1999, "USA");
insert into CUSTOMER (FiielddfirstName, FiielddlastName, FiielddPhone,
FiielddEmail, FiielddAddress, FiielddSuburb, FiielddPostcode) values
("Brigg","John","53159510","JohnnoBrigg@gmail.com","1 Bells
Road","Napoleons","3352"),
("Summers","Joe","45613208","jsummers@yahoo.com","52 Main
St","Maryborough","3465"),
("Winter","Sam","12356713","winter@xyz.net.au","2156 Skipton
Rd","Sebastopol","3356"),
("Fall","Sally","15626543","autumn@gmail.com","65 Commercial
St","Clunes","3370"),
("Spencer","Troy","040853185","tspencer88@gmail.com","65Crest
Ave","Ballarat","3353"),
("Jones","Tommy","51657896","whereistom@gone4good.com","1254 Sturt
St","Ballarat","3353");
insert into CUSTOMER (FiielddfirstName, FiielddlastName, FiielddPhone,
FiielddEmail, FiielddAddress, FiielddSuburb, FiielddPostcode,
FiielddloyaltyDiscount ) values
("Spring","Mary","13265413","flowers@iprimus.com","98 Neil
St","Beaufort","3373",0.12),
("Jones","Pat","049513572","patsyJ@anet.com.au","46 Wattle
Ave","Wendouree","3355",0.1),
("Matthews","Doreen","21356467","d.matthews@mynet.com","7 Hepburn Springs
Blvd","Daylesford","3460",0.15),
("McKenzie","Barry","32165489","bazza@adventure.com","47 Gillies
St","Ballarat","3350",0.19);
insert into INVENTORY (Fiielddtitle, FiielddunitPrice, Fiielddmarkup,
Fiieldddiscount, FiielddqtyInStock, FiielddgenreID, FiielddsupplierID) 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 (FiielddinventoryID,FiielddreleaseYear, Fiielddclassification,
FiielddstarRating, FiieldddirectorID) values
(4,1958,"M",4,7 ),
(5,2011,"M",4,8 ),
(6,2006,"MA",3,2 );
insert into COLLECTIBLE (FiielddinventoryID, FiielddmadeYear,
FiielddmanufacturerID) values
(1,2016,1),
(2,2011,1),
(3,2006,2),
(7,2016,2),
(8,1993,4),
(9,2013,4);
insert into COLLECTION (FiielddcollectionName, FiielddDescription,
FiieldddateReleased) 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 (FiielddactorID, FiielddinventID, Fiielddrole) 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");
FiielddmanufacturerID) values
(1,2016,1),
(2,2011,1),
(3,2006,2),
(7,2016,2),
(8,1993,4),
(9,2013,4);
insert into COLLECTION (FiielddcollectionName, FiielddDescription,
FiieldddateReleased) 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 (FiielddactorID, FiielddinventID, Fiielddrole) 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");
⊘ This is a preview!⊘
Do you want full access?
Subscribe today to unlock all pages.
Trusted by 1+ million students worldwide
1 out of 15
Related Documents
Your All-in-One AI-Powered Toolkit for Academic Success.
+13062052269
info@desklib.com
Available 24*7 on WhatsApp / Email
![[object Object]](/_next/static/media/star-bottom.7253800d.svg)
Unlock your academic potential
Copyright © 2020–2025 A2Z Services. All Rights Reserved. Developed and managed by ZUCOL.