ITECH1006 Assignment 2: Database Schema Design and SQL Queries
VerifiedAdded on 2020/05/08
|15
|4119
|238
Homework Assignment
AI Summary
This document presents a complete solution for a database management systems assignment (ITECH1006). The solution begins with the design of a relational schema, detailing the attributes, datatypes, and keys for various entities such as GENRE, SUPPLIER, ACTOR, INVENTORY, and others. Following the schema design, the document provides SQL CREATE statements to build the database, including table definitions with primary and foreign key constraints. Subsequently, INSERT statements are included to populate the tables with sample data. The assignment then proceeds to demonstrate a series of SQL queries to retrieve and manipulate the data, covering SELECT statements with JOINs, filtering, aggregation, and data modification (UPDATE, DELETE, INSERT) operations. The SQL queries cover a range of tasks, from simple data retrieval to more complex operations involving multiple tables, demonstrating a thorough understanding of SQL syntax and database management principles. This assignment solution provides a robust foundation for learning database concepts.
Assignment 2
ITECH 1006 – Database Management Systems
Student ID –
Student Name -
Date –
ITECH 1006 – Database Management Systems
Student ID –
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
FidgenreID INTEGER PRIMARY KEY
FidgenreName TEXT(50)
FidDescription TEXT(250)
2. SUPPLIER
Attribute Datetype Key
FidsupplierID INTEGER PRIMARY KEY
FidsupplierName TEXT(50)
FidContact TEXT(50)
FidPhone TEXT(15)
FidEmail TEXT(50)
FidAddress TEXT(50)
FidSuburb TEXT(50)
FidPostcode TEXT(5)
FidCountry TEXT(50)
FidComments TEXT(250)
3. ACTOR
Attribute Datetype Key
FidactorID INTEGER PRIMARY KEY
FidfirstName TEXT(50)
FidlastName TEXT(50)
FidPlaceOfBirth TEXT(100)
FidDateOfBirth DATE
FidDateOfDeath DATE
4. INVENTORY
Attribute Datetype Key
FidinventoryID INTEGER PRIMARY KEY
Fidtitle TEXT(250)
FidunitPrice DECIMAL(5,2)
Fidmarkup DECIMAL(5,2)
Fiddiscount DECIMAL(5,2)
FidqtyInStock INTEGER
FidgenreID INTEGER FOREIGN KEY
FidsupplierID INTEGER FOREIGN KEY
Task 1
Relational Schema
1. GENRE
Attribute Datetype Key
FidgenreID INTEGER PRIMARY KEY
FidgenreName TEXT(50)
FidDescription TEXT(250)
2. SUPPLIER
Attribute Datetype Key
FidsupplierID INTEGER PRIMARY KEY
FidsupplierName TEXT(50)
FidContact TEXT(50)
FidPhone TEXT(15)
FidEmail TEXT(50)
FidAddress TEXT(50)
FidSuburb TEXT(50)
FidPostcode TEXT(5)
FidCountry TEXT(50)
FidComments TEXT(250)
3. ACTOR
Attribute Datetype Key
FidactorID INTEGER PRIMARY KEY
FidfirstName TEXT(50)
FidlastName TEXT(50)
FidPlaceOfBirth TEXT(100)
FidDateOfBirth DATE
FidDateOfDeath DATE
4. INVENTORY
Attribute Datetype Key
FidinventoryID INTEGER PRIMARY KEY
Fidtitle TEXT(250)
FidunitPrice DECIMAL(5,2)
Fidmarkup DECIMAL(5,2)
Fiddiscount DECIMAL(5,2)
FidqtyInStock INTEGER
FidgenreID INTEGER FOREIGN KEY
FidsupplierID INTEGER FOREIGN KEY
5. MANUFACTURER
Attribute Datetype Key
FidmanufacturerID INTEGER PRIMARY KEY
FidmanufacturerName TEXT(50)
FidPhone TEXT(15)
FidEmail TEXT(50)
FidAddress TEXT(50)
FidSuburb TEXT(50)
FidPostcode TEXT(5)
FidCountry TEXT(50)
6. DIRECTOR
Attribute Datetype Key
FiddirectorID INTEGER PRIMARY KEY
FidfirstName TEXT(50)
FidlastName TEXT(50)
FidPlaceOfBirth TEXT(100)
FidDateOfBirth DATE
FidDateOfDeath DATE
7. COLLECTION
Attribute Datetype Key
FidcollectionID INTEGER PRIMARY KEY
FidcollectionName TEXT(50)
FidDescription TEXT(250)
FiddateReleased DATE
8. COLLECTIBLE
Attribute Datetype Key
FidinventoryID INTEGER PRIMARY KEY/FOREIGN
KEY
FidmadeYear INTEGER
FidmanufacturerID INTEGER FOREIGN KEY
9. COLLECTION_MEMBER
Attribute Datetype Key
FidcollectionID INTEGER PRIMARY KEY/FOREIGN
KEY
FidinventoryID INTEGER PRIMARY KEY/FOREIGN
KEY
Attribute Datetype Key
FidmanufacturerID INTEGER PRIMARY KEY
FidmanufacturerName TEXT(50)
FidPhone TEXT(15)
FidEmail TEXT(50)
FidAddress TEXT(50)
FidSuburb TEXT(50)
FidPostcode TEXT(5)
FidCountry TEXT(50)
6. DIRECTOR
Attribute Datetype Key
FiddirectorID INTEGER PRIMARY KEY
FidfirstName TEXT(50)
FidlastName TEXT(50)
FidPlaceOfBirth TEXT(100)
FidDateOfBirth DATE
FidDateOfDeath DATE
7. COLLECTION
Attribute Datetype Key
FidcollectionID INTEGER PRIMARY KEY
FidcollectionName TEXT(50)
FidDescription TEXT(250)
FiddateReleased DATE
8. COLLECTIBLE
Attribute Datetype Key
FidinventoryID INTEGER PRIMARY KEY/FOREIGN
KEY
FidmadeYear INTEGER
FidmanufacturerID INTEGER FOREIGN KEY
9. COLLECTION_MEMBER
Attribute Datetype Key
FidcollectionID INTEGER PRIMARY KEY/FOREIGN
KEY
FidinventoryID INTEGER PRIMARY KEY/FOREIGN
KEY
⊘ This is a preview!⊘
Do you want full access?
Subscribe today to unlock all pages.
Trusted by 1+ million students worldwide
10. MOVIE
Attribute Datetype Key
FidinventoryID INTEGER PRIMARY KEY/FOREIGN
KEY
FidreleaseYear INTEGER
Fidclassification TEXT(5)
FidstarRating INTEGER
FiddirectorID INTEGER FOREIGN KEY
11. CAST
Attribute Datetype Key
FidactorID INTEGER PRIMARY KEY/FOREIGN
KEY
FidinventID INTEGER PRIMARY KEY/FOREIGN
KEY
Fidrole TEXT(50)
12. CUSTOMER
Attribute Datetype Key
FidcustomerID INTEGER PRIMARY KEY
FidfirstName TEXT(50)
FidlastName TEXT(50)
FidPhone TEXT(15)
FidEmail TEXT(50)
FidAddress TEXT(100)
FidPostcode INTEGER
FidloyaltyDiscount DECIMAL(5,2)
13. RECEIPT
Attribute Datetype Key
FidreceiptID INTEGER PRIMARY KEY
FidcustomerID INTEGER FOREIGN KEY
FidreceiptDate DATE
14. PURCHASE
Attribute Datetype Key
FidreceiptID INTEGER PRIMARY KEY
FidinventoryID INTEGER FOREIGN KEY
Fidquantity INTEGER
FidamountPaid DECIMAL(5,2)
(Adrienne Watt n.d.)
(teach-ict.com n.d.)
Attribute Datetype Key
FidinventoryID INTEGER PRIMARY KEY/FOREIGN
KEY
FidreleaseYear INTEGER
Fidclassification TEXT(5)
FidstarRating INTEGER
FiddirectorID INTEGER FOREIGN KEY
11. CAST
Attribute Datetype Key
FidactorID INTEGER PRIMARY KEY/FOREIGN
KEY
FidinventID INTEGER PRIMARY KEY/FOREIGN
KEY
Fidrole TEXT(50)
12. CUSTOMER
Attribute Datetype Key
FidcustomerID INTEGER PRIMARY KEY
FidfirstName TEXT(50)
FidlastName TEXT(50)
FidPhone TEXT(15)
FidEmail TEXT(50)
FidAddress TEXT(100)
FidPostcode INTEGER
FidloyaltyDiscount DECIMAL(5,2)
13. RECEIPT
Attribute Datetype Key
FidreceiptID INTEGER PRIMARY KEY
FidcustomerID INTEGER FOREIGN KEY
FidreceiptDate DATE
14. PURCHASE
Attribute Datetype Key
FidreceiptID INTEGER PRIMARY KEY
FidinventoryID INTEGER FOREIGN KEY
Fidquantity INTEGER
FidamountPaid DECIMAL(5,2)
(Adrienne Watt n.d.)
(teach-ict.com n.d.)
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
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 (
FidgenreID int NOT NULL AUTO_INCREMENT,
FidgenreName TEXT(50) NOT NULL,
FidDescription TEXT(250) NULL,
PRIMARY KEY (FidgenreID)
);
DROP TABLE IF EXISTS SUPPLIER;
CREATE TABLE SUPPLIER (
FidsupplierID int NOT NULL AUTO_INCREMENT,
FidsupplierName TEXT(50) NOT NULL,
FidContactName TEXT(50) NOT NULL,
FidPhone TEXT(15) NOT NULL,
FidEmail TEXT(50) NOT NULL,
FidAddress TEXT(50) NOT NULL,
FidSuburb TEXT(50) NOT NULL,
FidPostcode TEXT(5) NOT NULL,
FidCountry TEXT(50) NOT NULL,
FidComments TEXT(250) NULL,
PRIMARY KEY (FidsupplierID)
);
DROP TABLE IF EXISTS ACTOR;
CREATE TABLE ACTOR (
FidactorID int NOT NULL AUTO_INCREMENT,
FidFirstName TEXT(50) NOT NULL,
FidLastName TEXT(50) NOT NULL,
FidPlaceOfBirth TEXT(100) NOT NULL,
FidDateOfBirth Date NOT NULL,
FidDateOfDeath Date NULL,
PRIMARY KEY (FidactorID)
);
DROP TABLE IF EXISTS INVENTORY;
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 (
FidgenreID int NOT NULL AUTO_INCREMENT,
FidgenreName TEXT(50) NOT NULL,
FidDescription TEXT(250) NULL,
PRIMARY KEY (FidgenreID)
);
DROP TABLE IF EXISTS SUPPLIER;
CREATE TABLE SUPPLIER (
FidsupplierID int NOT NULL AUTO_INCREMENT,
FidsupplierName TEXT(50) NOT NULL,
FidContactName TEXT(50) NOT NULL,
FidPhone TEXT(15) NOT NULL,
FidEmail TEXT(50) NOT NULL,
FidAddress TEXT(50) NOT NULL,
FidSuburb TEXT(50) NOT NULL,
FidPostcode TEXT(5) NOT NULL,
FidCountry TEXT(50) NOT NULL,
FidComments TEXT(250) NULL,
PRIMARY KEY (FidsupplierID)
);
DROP TABLE IF EXISTS ACTOR;
CREATE TABLE ACTOR (
FidactorID int NOT NULL AUTO_INCREMENT,
FidFirstName TEXT(50) NOT NULL,
FidLastName TEXT(50) NOT NULL,
FidPlaceOfBirth TEXT(100) NOT NULL,
FidDateOfBirth Date NOT NULL,
FidDateOfDeath Date NULL,
PRIMARY KEY (FidactorID)
);
DROP TABLE IF EXISTS INVENTORY;
CREATE TABLE INVENTORY (
FidinventoryID int NOT NULL AUTO_INCREMENT,
Fidtitle TEXT(250) NOT NULL,
FidunitPrice decimal(5,2) NOT NULL,
Fidmarkup decimal(5,2) NOT NULL,
Fiddiscount decimal(5,2) NOT NULL,
FidqtyInStock int NOT NULL,
FidgenreID int NOT NULL,
FidsupplierID int NULL,
PRIMARY KEY (FidinventoryID),
FOREIGN KEY (FidgenreID)
REFERENCES GENRE(FidgenreID),
FOREIGN KEY (FidsupplierID)
REFERENCES SUPPLIER(FidsupplierID)
);
DROP TABLE IF EXISTS MANUFACTURER;
CREATE TABLE MANUFACTURER (
FidmanufacturerID int NOT NULL AUTO_INCREMENT,
FidmanufacturerName TEXT(50) NOT NULL,
FidPhone TEXT(15) NOT NULL,
FidEmail TEXT(50) NOT NULL,
FidAddress TEXT(100) NOT NULL,
FidSuburb TEXT(50) NOT NULL,
FidPostcode int NOT NULL,
FidCountry TEXT(50) NULL,
PRIMARY KEY (FidmanufacturerID)
);
DROP TABLE IF EXISTS DIRECTOR;
CREATE TABLE DIRECTOR (
FiddirectorID int NOT NULL AUTO_INCREMENT,
FidFirstName TEXT(50) NOT NULL,
FidLastName TEXT(50) NOT NULL,
FidPlaceOfBirth TEXT(100) NOT NULL,
FidDateOfBirth Date NOT NULL,
FidDateOfDeath Date NULL,
PRIMARY KEY (FiddirectorID)
);
DROP TABLE IF EXISTS COLLECTION;
CREATE TABLE COLLECTION (
FidcollectionID int NOT NULL AUTO_INCREMENT,
FidcollectionName TEXT(50) NOT NULL,
FidDescription TEXT(250) NOT NULL,
FiddateReleased Date NOT NULL,
FidinventoryID int NOT NULL AUTO_INCREMENT,
Fidtitle TEXT(250) NOT NULL,
FidunitPrice decimal(5,2) NOT NULL,
Fidmarkup decimal(5,2) NOT NULL,
Fiddiscount decimal(5,2) NOT NULL,
FidqtyInStock int NOT NULL,
FidgenreID int NOT NULL,
FidsupplierID int NULL,
PRIMARY KEY (FidinventoryID),
FOREIGN KEY (FidgenreID)
REFERENCES GENRE(FidgenreID),
FOREIGN KEY (FidsupplierID)
REFERENCES SUPPLIER(FidsupplierID)
);
DROP TABLE IF EXISTS MANUFACTURER;
CREATE TABLE MANUFACTURER (
FidmanufacturerID int NOT NULL AUTO_INCREMENT,
FidmanufacturerName TEXT(50) NOT NULL,
FidPhone TEXT(15) NOT NULL,
FidEmail TEXT(50) NOT NULL,
FidAddress TEXT(100) NOT NULL,
FidSuburb TEXT(50) NOT NULL,
FidPostcode int NOT NULL,
FidCountry TEXT(50) NULL,
PRIMARY KEY (FidmanufacturerID)
);
DROP TABLE IF EXISTS DIRECTOR;
CREATE TABLE DIRECTOR (
FiddirectorID int NOT NULL AUTO_INCREMENT,
FidFirstName TEXT(50) NOT NULL,
FidLastName TEXT(50) NOT NULL,
FidPlaceOfBirth TEXT(100) NOT NULL,
FidDateOfBirth Date NOT NULL,
FidDateOfDeath Date NULL,
PRIMARY KEY (FiddirectorID)
);
DROP TABLE IF EXISTS COLLECTION;
CREATE TABLE COLLECTION (
FidcollectionID int NOT NULL AUTO_INCREMENT,
FidcollectionName TEXT(50) NOT NULL,
FidDescription TEXT(250) NOT NULL,
FiddateReleased Date NOT NULL,
⊘ This is a preview!⊘
Do you want full access?
Subscribe today to unlock all pages.
Trusted by 1+ million students worldwide
PRIMARY KEY (FidcollectionID)
);
DROP TABLE IF EXISTS COLLECTIBLE;
CREATE TABLE COLLECTIBLE (
FidinventoryID int NOT NULL,
FidmadeYear int NOT NULL,
FidmanufacturerID int NOT NULL,
PRIMARY KEY (FidinventoryID),
FOREIGN KEY (FidinventoryID)
REFERENCES INVENTORY(FidinventoryID),
FOREIGN KEY (FidmanufacturerID)
REFERENCES MANUFACTURER(FidmanufacturerID)
);
DROP TABLE IF EXISTS COLLECTION_MEMBER;
CREATE TABLE COLLECTION_MEMBER (
FidinventoryID int NOT NULL,
FidcollectionID int NOT NULL,
PRIMARY KEY (FidinventoryID, FidcollectionID),
FOREIGN KEY (FidinventoryID)
REFERENCES INVENTORY(FidinventoryID),
FOREIGN KEY (FidcollectionID)
REFERENCES COLLECTION(FidcollectionID)
);
DROP TABLE IF EXISTS MOVIE;
CREATE TABLE MOVIE (
FidinventoryID int NOT NULL,
FidreleaseYear int NOT NULL,
Fidclassification TEXT(5) NOT NULL,
FidstarRating int NOT NULL,
FiddirectorID int NOT NULL,
PRIMARY KEY (FidinventoryID),
FOREIGN KEY (FidinventoryID)
REFERENCES INVENTORY(FidinventoryID),
FOREIGN KEY (FiddirectorID)
REFERENCES DIRECTOR(FiddirectorID)
);
DROP TABLE IF EXISTS CAST;
CREATE TABLE CAST (
FidactorID int NOT NULL,
FidinventID int NOT NULL,
Fidrole TEXT(50) NOT NULL,
PRIMARY KEY (FidactorID,FidinventID),
);
DROP TABLE IF EXISTS COLLECTIBLE;
CREATE TABLE COLLECTIBLE (
FidinventoryID int NOT NULL,
FidmadeYear int NOT NULL,
FidmanufacturerID int NOT NULL,
PRIMARY KEY (FidinventoryID),
FOREIGN KEY (FidinventoryID)
REFERENCES INVENTORY(FidinventoryID),
FOREIGN KEY (FidmanufacturerID)
REFERENCES MANUFACTURER(FidmanufacturerID)
);
DROP TABLE IF EXISTS COLLECTION_MEMBER;
CREATE TABLE COLLECTION_MEMBER (
FidinventoryID int NOT NULL,
FidcollectionID int NOT NULL,
PRIMARY KEY (FidinventoryID, FidcollectionID),
FOREIGN KEY (FidinventoryID)
REFERENCES INVENTORY(FidinventoryID),
FOREIGN KEY (FidcollectionID)
REFERENCES COLLECTION(FidcollectionID)
);
DROP TABLE IF EXISTS MOVIE;
CREATE TABLE MOVIE (
FidinventoryID int NOT NULL,
FidreleaseYear int NOT NULL,
Fidclassification TEXT(5) NOT NULL,
FidstarRating int NOT NULL,
FiddirectorID int NOT NULL,
PRIMARY KEY (FidinventoryID),
FOREIGN KEY (FidinventoryID)
REFERENCES INVENTORY(FidinventoryID),
FOREIGN KEY (FiddirectorID)
REFERENCES DIRECTOR(FiddirectorID)
);
DROP TABLE IF EXISTS CAST;
CREATE TABLE CAST (
FidactorID int NOT NULL,
FidinventID int NOT NULL,
Fidrole TEXT(50) NOT NULL,
PRIMARY KEY (FidactorID,FidinventID),
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
FOREIGN KEY (FidinventID)
REFERENCES MOVIE(FidinventoryID),
FOREIGN KEY (FidactorID)
REFERENCES ACTOR(FidactorID)
);
DROP TABLE IF EXISTS CUSTOMER;
CREATE TABLE CUSTOMER (
FidcustomerID int NOT NULL AUTO_INCREMENT,
FidFirstName TEXT(50) NOT NULL,
FidLastName TEXT(50) NOT NULL,
FidPhone TEXT(15) NOT NULL,
FidEmail TEXT(50) NOT NULL,
FidAddress TEXT(100) NOT NULL,
FidSuburb TEXT(50) NOT NULL,
FidPostcode int NOT NULL,
FidloyaltyDiscount decimal(5,2) NULL,
PRIMARY KEY (FidcustomerID)
);
DROP TABLE IF EXISTS RECEIPT;
CREATE TABLE RECEIPT (
FidreceiptID int NOT NULL AUTO_INCREMENT,
FidcustomerID int NOT NULL,
FidreceiptDate date NOT NULL,
PRIMARY KEY (FidreceiptID),
FOREIGN KEY (FidcustomerID)
REFERENCES CUSTOMER(FidcustomerID)
);
DROP TABLE IF EXISTS PURCHASE;
CREATE TABLE PURCHASE (
FidreceiptID int NOT NULL,
FidinventoryID int NOT NULL,
Fidquantity int NOT NULL,
FidamountPaid decimal(5,2) NOT NULL,
PRIMARY KEY (FidreceiptID),
FOREIGN KEY (FidreceiptID)
REFERENCES RECEIPT(FidreceiptID),
FOREIGN KEY (FidinventoryID)
REFERENCES INVENTORY(FidinventoryID)
);
REFERENCES MOVIE(FidinventoryID),
FOREIGN KEY (FidactorID)
REFERENCES ACTOR(FidactorID)
);
DROP TABLE IF EXISTS CUSTOMER;
CREATE TABLE CUSTOMER (
FidcustomerID int NOT NULL AUTO_INCREMENT,
FidFirstName TEXT(50) NOT NULL,
FidLastName TEXT(50) NOT NULL,
FidPhone TEXT(15) NOT NULL,
FidEmail TEXT(50) NOT NULL,
FidAddress TEXT(100) NOT NULL,
FidSuburb TEXT(50) NOT NULL,
FidPostcode int NOT NULL,
FidloyaltyDiscount decimal(5,2) NULL,
PRIMARY KEY (FidcustomerID)
);
DROP TABLE IF EXISTS RECEIPT;
CREATE TABLE RECEIPT (
FidreceiptID int NOT NULL AUTO_INCREMENT,
FidcustomerID int NOT NULL,
FidreceiptDate date NOT NULL,
PRIMARY KEY (FidreceiptID),
FOREIGN KEY (FidcustomerID)
REFERENCES CUSTOMER(FidcustomerID)
);
DROP TABLE IF EXISTS PURCHASE;
CREATE TABLE PURCHASE (
FidreceiptID int NOT NULL,
FidinventoryID int NOT NULL,
Fidquantity int NOT NULL,
FidamountPaid decimal(5,2) NOT NULL,
PRIMARY KEY (FidreceiptID),
FOREIGN KEY (FidreceiptID)
REFERENCES RECEIPT(FidreceiptID),
FOREIGN KEY (FidinventoryID)
REFERENCES INVENTORY(FidinventoryID)
);
Task 3
Insert Statements
use Startstruck_Entertainment_studentid;
INSERT INTO GENRE (FidgenreName, FidDescription) 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 (FidsupplierName, FidContactName, FidPhone,
FidEmail, FidAddress, FidSuburb, FidPostcode, FidCountry, FidComments) 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","GlenFid",2167,"Australia","Christmas orders by November 15"),
("Movie Magic","Sandor ButterFid","+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 (FidFirstName, FidLastName, FidPlaceOfBirth, FidDateOfBirth,
FidDateOfDeath) 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 (FidFirstName, FidLastName, FidPlaceOfBirth, FidDateOfBirth)
values
Insert Statements
use Startstruck_Entertainment_studentid;
INSERT INTO GENRE (FidgenreName, FidDescription) 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 (FidsupplierName, FidContactName, FidPhone,
FidEmail, FidAddress, FidSuburb, FidPostcode, FidCountry, FidComments) 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","GlenFid",2167,"Australia","Christmas orders by November 15"),
("Movie Magic","Sandor ButterFid","+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 (FidFirstName, FidLastName, FidPlaceOfBirth, FidDateOfBirth,
FidDateOfDeath) 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 (FidFirstName, FidLastName, FidPlaceOfBirth, FidDateOfBirth)
values
⊘ This is a preview!⊘
Do you want full access?
Subscribe today to unlock all pages.
Trusted by 1+ million students worldwide
("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 (FidfirstName, FidlastName, FidPlaceOfBirth,
FidDateOfBirth) 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 (FidfirstName, FidlastName, FidPlaceOfBirth,
FidDateOfBirth, FidDateOfDeath) 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 (FidmanufacturerName, FidPhone, FidEmail,
FidAddress, FidSuburb, FidPostcode, FidCountry) 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"),
("Modelworks", "+1456123789", "info@modelworks.com","12 Danshui
Street","Zhongzheng", 1020, "Taiwan"),
%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 (FidfirstName, FidlastName, FidPlaceOfBirth,
FidDateOfBirth) 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 (FidfirstName, FidlastName, FidPlaceOfBirth,
FidDateOfBirth, FidDateOfDeath) 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 (FidmanufacturerName, FidPhone, FidEmail,
FidAddress, FidSuburb, FidPostcode, FidCountry) 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"),
("Modelworks", "+1456123789", "info@modelworks.com","12 Danshui
Street","Zhongzheng", 1020, "Taiwan"),
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
("Rockstar", "+1451586604", "info@rockstarfigures.com","40 Grand
Drive","Chicago", 1999, "USA");
insert into CUSTOMER (FidfirstName, FidlastName, FidPhone, FidEmail,
FidAddress, FidSuburb, FidPostcode) 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 (FidfirstName, FidlastName, FidPhone, FidEmail,
FidAddress, FidSuburb, FidPostcode, FidloyaltyDiscount ) 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 (Fidtitle, FidunitPrice, Fidmarkup, Fiddiscount,
FidqtyInStock, FidgenreID, FidsupplierID) 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 (FidinventoryID,FidreleaseYear, Fidclassification, FidstarRating,
FiddirectorID) values
(4,1958,"M",4,7 ),
(5,2011,"M",4,8 ),
(6,2006,"MA",3,2 );
insert into COLLECTIBLE (FidinventoryID, FidmadeYear, FidmanufacturerID)
values
(1,2016,1),
Drive","Chicago", 1999, "USA");
insert into CUSTOMER (FidfirstName, FidlastName, FidPhone, FidEmail,
FidAddress, FidSuburb, FidPostcode) 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 (FidfirstName, FidlastName, FidPhone, FidEmail,
FidAddress, FidSuburb, FidPostcode, FidloyaltyDiscount ) 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 (Fidtitle, FidunitPrice, Fidmarkup, Fiddiscount,
FidqtyInStock, FidgenreID, FidsupplierID) 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 (FidinventoryID,FidreleaseYear, Fidclassification, FidstarRating,
FiddirectorID) values
(4,1958,"M",4,7 ),
(5,2011,"M",4,8 ),
(6,2006,"MA",3,2 );
insert into COLLECTIBLE (FidinventoryID, FidmadeYear, FidmanufacturerID)
values
(1,2016,1),
(2,2011,1),
(3,2006,2),
(7,2016,2),
(8,1993,4),
(9,2013,4);
insert into COLLECTION (FidcollectionName, FidDescription, FiddateReleased)
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 (FidactorID, FidinventID, Fidrole) 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 */
(3,2006,2),
(7,2016,2),
(8,1993,4),
(9,2013,4);
insert into COLLECTION (FidcollectionName, FidDescription, FiddateReleased)
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 (FidactorID, FidinventID, Fidrole) 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 */
⊘ 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.