ITECH1006 Assignment 2: Database Schema, Queries, and Implementation
VerifiedAdded on 2020/04/01
|15
|4127
|27
Homework Assignment
AI Summary
This assignment solution for ITECH1006, a Database Management Systems course, presents a detailed relational schema for an entertainment database, including tables for genres, suppliers, actors, inventory, manufacturers, directors, collections, collectibles, collection members, movies, cast, customers, receipts, and purchases. It includes the data types and primary/foreign keys for each attribute. The solution further provides SQL create statements to build the database and insert statements to populate the tables with sample data. Moreover, the assignment offers SQL queries to retrieve and manipulate data, demonstrating various SQL operations like ordering, joining tables, calculating age, and performing updates and deletions, covering a wide range of database management concepts. References to database design fundamentals are also included.
Assignment 2
ITECH 1006 – Database Management Systems
Student ID – 30335301
Student Name -
Date –
Module Tutor -
ITECH 1006 – Database Management Systems
Student ID – 30335301
Student Name -
Date –
Module Tutor -
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
FieldgenreID INTEGER PRIMARY KEY
FieldgenreName NCHAR(50)
FieldDescription NCHAR(250)
2. SUPPLIER
Attribute Datetype Key
FieldsupplierID INTEGER PRIMARY KEY
FieldsupplierName NCHAR(50)
FieldContact NCHAR(50)
FieldPhone NCHAR(15)
FieldEmail NCHAR(50)
FieldAddress NCHAR(50)
FieldSuburb NCHAR(50)
FieldPostcode NCHAR(5)
FieldCountry NCHAR(50)
FieldComments NCHAR(250)
3. ACTOR
Attribute Datetype Key
FieldactorID INTEGER PRIMARY KEY
FieldfirstName NCHAR(50)
FieldlastName NCHAR(50)
FieldPlaceOfBirth NCHAR(100)
FieldDateOfBirth DATE
FieldDateOfDeath DATE
4. INVENTORY
Attribute Datetype Key
FieldinventoryID INTEGER PRIMARY KEY
Fieldtitle NCHAR(250)
FieldunitPrice DECIMAL(5,2)
Fieldmarkup DECIMAL(5,2)
Fielddiscount DECIMAL(5,2)
FieldqtyInStock INTEGER
FieldgenreID INTEGER FOREIGN KEY
FieldsupplierID INTEGER FOREIGN KEY
Task 1
Relational Schema
1. GENRE
Attribute Datetype Key
FieldgenreID INTEGER PRIMARY KEY
FieldgenreName NCHAR(50)
FieldDescription NCHAR(250)
2. SUPPLIER
Attribute Datetype Key
FieldsupplierID INTEGER PRIMARY KEY
FieldsupplierName NCHAR(50)
FieldContact NCHAR(50)
FieldPhone NCHAR(15)
FieldEmail NCHAR(50)
FieldAddress NCHAR(50)
FieldSuburb NCHAR(50)
FieldPostcode NCHAR(5)
FieldCountry NCHAR(50)
FieldComments NCHAR(250)
3. ACTOR
Attribute Datetype Key
FieldactorID INTEGER PRIMARY KEY
FieldfirstName NCHAR(50)
FieldlastName NCHAR(50)
FieldPlaceOfBirth NCHAR(100)
FieldDateOfBirth DATE
FieldDateOfDeath DATE
4. INVENTORY
Attribute Datetype Key
FieldinventoryID INTEGER PRIMARY KEY
Fieldtitle NCHAR(250)
FieldunitPrice DECIMAL(5,2)
Fieldmarkup DECIMAL(5,2)
Fielddiscount DECIMAL(5,2)
FieldqtyInStock INTEGER
FieldgenreID INTEGER FOREIGN KEY
FieldsupplierID INTEGER FOREIGN KEY
5. MANUFACTURER
Attribute Datetype Key
FieldmanufacturerID INTEGER PRIMARY KEY
FieldmanufacturerName NCHAR(50)
FieldPhone NCHAR(15)
FieldEmail NCHAR(50)
FieldAddress NCHAR(50)
FieldSuburb NCHAR(50)
FieldPostcode NCHAR(5)
FieldCountry NCHAR(50)
6. DIRECTOR
Attribute Datetype Key
FielddirectorID INTEGER PRIMARY KEY
FieldfirstName NCHAR(50)
FieldlastName NCHAR(50)
FieldPlaceOfBirth NCHAR(100)
FieldDateOfBirth DATE
FieldDateOfDeath DATE
7. COLLECTION
Attribute Datetype Key
FieldcollectionID INTEGER PRIMARY KEY
FieldcollectionName NCHAR(50)
FieldDescription NCHAR(250)
FielddateReleased DATE
8. COLLECTIBLE
Attribute Datetype Key
FieldinventoryID INTEGER PRIMARY KEY/FOREIGN
KEY
FieldmadeYear INTEGER
FieldmanufacturerID INTEGER FOREIGN KEY
9. COLLECTION_MEMBER
Attribute Datetype Key
FieldcollectionID INTEGER PRIMARY KEY/FOREIGN
KEY
FieldinventoryID INTEGER PRIMARY KEY/FOREIGN
KEY
10. MOVIE
Attribute Datetype Key
Attribute Datetype Key
FieldmanufacturerID INTEGER PRIMARY KEY
FieldmanufacturerName NCHAR(50)
FieldPhone NCHAR(15)
FieldEmail NCHAR(50)
FieldAddress NCHAR(50)
FieldSuburb NCHAR(50)
FieldPostcode NCHAR(5)
FieldCountry NCHAR(50)
6. DIRECTOR
Attribute Datetype Key
FielddirectorID INTEGER PRIMARY KEY
FieldfirstName NCHAR(50)
FieldlastName NCHAR(50)
FieldPlaceOfBirth NCHAR(100)
FieldDateOfBirth DATE
FieldDateOfDeath DATE
7. COLLECTION
Attribute Datetype Key
FieldcollectionID INTEGER PRIMARY KEY
FieldcollectionName NCHAR(50)
FieldDescription NCHAR(250)
FielddateReleased DATE
8. COLLECTIBLE
Attribute Datetype Key
FieldinventoryID INTEGER PRIMARY KEY/FOREIGN
KEY
FieldmadeYear INTEGER
FieldmanufacturerID INTEGER FOREIGN KEY
9. COLLECTION_MEMBER
Attribute Datetype Key
FieldcollectionID INTEGER PRIMARY KEY/FOREIGN
KEY
FieldinventoryID 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
FieldinventoryID INTEGER PRIMARY KEY/FOREIGN
KEY
FieldreleaseYear INTEGER
Fieldclassification NCHAR(5)
FieldstarRating INTEGER
FielddirectorID INTEGER FOREIGN KEY
11. CAST
Attribute Datetype Key
FieldactorID INTEGER PRIMARY KEY/FOREIGN
KEY
FieldinventID INTEGER PRIMARY KEY/FOREIGN
KEY
Fieldrole NCHAR(50)
12. CUSTOMER
Attribute Datetype Key
FieldcustomerID INTEGER PRIMARY KEY
FieldfirstName NCHAR(50)
FieldlastName NCHAR(50)
FieldPhone NCHAR(15)
FieldEmail NCHAR(50)
FieldAddress NCHAR(100)
FieldPostcode INTEGER
FieldloyaltyDiscount DECIMAL(5,2)
13. RECEIPT
Attribute Datetype Key
FieldreceiptID INTEGER PRIMARY KEY
FieldcustomerID INTEGER FOREIGN KEY
FieldreceiptDate DATE
14. PURCHASE
Attribute Datetype Key
FieldreceiptID INTEGER PRIMARY KEY
FieldinventoryID INTEGER FOREIGN KEY
Fieldquantity INTEGER
FieldamountPaid DECIMAL(5,2)
(P. Litwin n.d.)
Task 2
KEY
FieldreleaseYear INTEGER
Fieldclassification NCHAR(5)
FieldstarRating INTEGER
FielddirectorID INTEGER FOREIGN KEY
11. CAST
Attribute Datetype Key
FieldactorID INTEGER PRIMARY KEY/FOREIGN
KEY
FieldinventID INTEGER PRIMARY KEY/FOREIGN
KEY
Fieldrole NCHAR(50)
12. CUSTOMER
Attribute Datetype Key
FieldcustomerID INTEGER PRIMARY KEY
FieldfirstName NCHAR(50)
FieldlastName NCHAR(50)
FieldPhone NCHAR(15)
FieldEmail NCHAR(50)
FieldAddress NCHAR(100)
FieldPostcode INTEGER
FieldloyaltyDiscount DECIMAL(5,2)
13. RECEIPT
Attribute Datetype Key
FieldreceiptID INTEGER PRIMARY KEY
FieldcustomerID INTEGER FOREIGN KEY
FieldreceiptDate DATE
14. PURCHASE
Attribute Datetype Key
FieldreceiptID INTEGER PRIMARY KEY
FieldinventoryID INTEGER FOREIGN KEY
Fieldquantity INTEGER
FieldamountPaid DECIMAL(5,2)
(P. Litwin n.d.)
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_30335301;
create database Startstruck_Entertainment_30335301;
use Startstruck_Entertainment_30335301;
DROP TABLE IF EXISTS GENRE;
CREATE TABLE GENRE (
FieldgenreID int NOT NULL AUTO_INCREMENT,
FieldgenreName nchar(50) NOT NULL,
FieldDescription nchar(250) NULL,
PRIMARY KEY (FieldgenreID)
);
DROP TABLE IF EXISTS SUPPLIER;
CREATE TABLE SUPPLIER (
FieldsupplierID int NOT NULL AUTO_INCREMENT,
FieldsupplierName nchar(50) NOT NULL,
FieldContactName nchar(50) NOT NULL,
FieldPhone nchar(15) NOT NULL,
FieldEmail nchar(50) NOT NULL,
FieldAddress nchar(50) NOT NULL,
FieldSuburb nchar(50) NOT NULL,
FieldPostcode nchar(5) NOT NULL,
FieldCountry nchar(50) NOT NULL,
FieldComments nchar(250) NULL,
PRIMARY KEY (FieldsupplierID)
);
DROP TABLE IF EXISTS ACTOR;
CREATE TABLE ACTOR (
FieldactorID int NOT NULL AUTO_INCREMENT,
FieldFirstName nchar(50) NOT NULL,
FieldLastName nchar(50) NOT NULL,
FieldPlaceOfBirth nchar(100) NOT NULL,
FieldDateOfBirth Date NOT NULL,
FieldDateOfDeath Date NULL,
PRIMARY KEY (FieldactorID)
);
DROP TABLE IF EXISTS INVENTORY;
CREATE TABLE INVENTORY (
FieldinventoryID int NOT NULL AUTO_INCREMENT,
DROP Database IF EXISTS Startstruck_Entertainment_30335301;
create database Startstruck_Entertainment_30335301;
use Startstruck_Entertainment_30335301;
DROP TABLE IF EXISTS GENRE;
CREATE TABLE GENRE (
FieldgenreID int NOT NULL AUTO_INCREMENT,
FieldgenreName nchar(50) NOT NULL,
FieldDescription nchar(250) NULL,
PRIMARY KEY (FieldgenreID)
);
DROP TABLE IF EXISTS SUPPLIER;
CREATE TABLE SUPPLIER (
FieldsupplierID int NOT NULL AUTO_INCREMENT,
FieldsupplierName nchar(50) NOT NULL,
FieldContactName nchar(50) NOT NULL,
FieldPhone nchar(15) NOT NULL,
FieldEmail nchar(50) NOT NULL,
FieldAddress nchar(50) NOT NULL,
FieldSuburb nchar(50) NOT NULL,
FieldPostcode nchar(5) NOT NULL,
FieldCountry nchar(50) NOT NULL,
FieldComments nchar(250) NULL,
PRIMARY KEY (FieldsupplierID)
);
DROP TABLE IF EXISTS ACTOR;
CREATE TABLE ACTOR (
FieldactorID int NOT NULL AUTO_INCREMENT,
FieldFirstName nchar(50) NOT NULL,
FieldLastName nchar(50) NOT NULL,
FieldPlaceOfBirth nchar(100) NOT NULL,
FieldDateOfBirth Date NOT NULL,
FieldDateOfDeath Date NULL,
PRIMARY KEY (FieldactorID)
);
DROP TABLE IF EXISTS INVENTORY;
CREATE TABLE INVENTORY (
FieldinventoryID int NOT NULL AUTO_INCREMENT,
Fieldtitle nchar(250) NOT NULL,
FieldunitPrice decimal(5,2) NOT NULL,
Fieldmarkup decimal(5,2) NOT NULL,
Fielddiscount decimal(5,2) NOT NULL,
FieldqtyInStock int NOT NULL,
FieldgenreID int NOT NULL,
FieldsupplierID int NULL,
PRIMARY KEY (FieldinventoryID),
FOREIGN KEY (FieldgenreID)
REFERENCES GENRE(FieldgenreID),
FOREIGN KEY (FieldsupplierID)
REFERENCES SUPPLIER(FieldsupplierID)
);
DROP TABLE IF EXISTS MANUFACTURER;
CREATE TABLE MANUFACTURER (
FieldmanufacturerID int NOT NULL AUTO_INCREMENT,
FieldmanufacturerName nchar(50) NOT NULL,
FieldPhone nchar(15) NOT NULL,
FieldEmail nchar(50) NOT NULL,
FieldAddress nchar(100) NOT NULL,
FieldSuburb nchar(50) NOT NULL,
FieldPostcode int NOT NULL,
FieldCountry nchar(50) NULL,
PRIMARY KEY (FieldmanufacturerID)
);
DROP TABLE IF EXISTS DIRECTOR;
CREATE TABLE DIRECTOR (
FielddirectorID int NOT NULL AUTO_INCREMENT,
FieldFirstName nchar(50) NOT NULL,
FieldLastName nchar(50) NOT NULL,
FieldPlaceOfBirth nchar(100) NOT NULL,
FieldDateOfBirth Date NOT NULL,
FieldDateOfDeath Date NULL,
PRIMARY KEY (FielddirectorID)
);
DROP TABLE IF EXISTS COLLECTION;
CREATE TABLE COLLECTION (
FieldcollectionID int NOT NULL AUTO_INCREMENT,
FieldcollectionName nchar(50) NOT NULL,
FieldDescription nchar(250) NOT NULL,
FielddateReleased Date NOT NULL,
PRIMARY KEY (FieldcollectionID)
);
FieldunitPrice decimal(5,2) NOT NULL,
Fieldmarkup decimal(5,2) NOT NULL,
Fielddiscount decimal(5,2) NOT NULL,
FieldqtyInStock int NOT NULL,
FieldgenreID int NOT NULL,
FieldsupplierID int NULL,
PRIMARY KEY (FieldinventoryID),
FOREIGN KEY (FieldgenreID)
REFERENCES GENRE(FieldgenreID),
FOREIGN KEY (FieldsupplierID)
REFERENCES SUPPLIER(FieldsupplierID)
);
DROP TABLE IF EXISTS MANUFACTURER;
CREATE TABLE MANUFACTURER (
FieldmanufacturerID int NOT NULL AUTO_INCREMENT,
FieldmanufacturerName nchar(50) NOT NULL,
FieldPhone nchar(15) NOT NULL,
FieldEmail nchar(50) NOT NULL,
FieldAddress nchar(100) NOT NULL,
FieldSuburb nchar(50) NOT NULL,
FieldPostcode int NOT NULL,
FieldCountry nchar(50) NULL,
PRIMARY KEY (FieldmanufacturerID)
);
DROP TABLE IF EXISTS DIRECTOR;
CREATE TABLE DIRECTOR (
FielddirectorID int NOT NULL AUTO_INCREMENT,
FieldFirstName nchar(50) NOT NULL,
FieldLastName nchar(50) NOT NULL,
FieldPlaceOfBirth nchar(100) NOT NULL,
FieldDateOfBirth Date NOT NULL,
FieldDateOfDeath Date NULL,
PRIMARY KEY (FielddirectorID)
);
DROP TABLE IF EXISTS COLLECTION;
CREATE TABLE COLLECTION (
FieldcollectionID int NOT NULL AUTO_INCREMENT,
FieldcollectionName nchar(50) NOT NULL,
FieldDescription nchar(250) NOT NULL,
FielddateReleased Date NOT NULL,
PRIMARY KEY (FieldcollectionID)
);
⊘ 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 (
FieldinventoryID int NOT NULL,
FieldmadeYear int NOT NULL,
FieldmanufacturerID int NOT NULL,
PRIMARY KEY (FieldinventoryID),
FOREIGN KEY (FieldinventoryID)
REFERENCES INVENTORY(FieldinventoryID),
FOREIGN KEY (FieldmanufacturerID)
REFERENCES MANUFACTURER(FieldmanufacturerID)
);
DROP TABLE IF EXISTS COLLECTION_MEMBER;
CREATE TABLE COLLECTION_MEMBER (
FieldinventoryID int NOT NULL,
FieldcollectionID int NOT NULL,
PRIMARY KEY (FieldinventoryID, FieldcollectionID),
FOREIGN KEY (FieldinventoryID)
REFERENCES INVENTORY(FieldinventoryID),
FOREIGN KEY (FieldcollectionID)
REFERENCES COLLECTION(FieldcollectionID)
);
DROP TABLE IF EXISTS MOVIE;
CREATE TABLE MOVIE (
FieldinventoryID int NOT NULL,
FieldreleaseYear int NOT NULL,
Fieldclassification nchar(5) NOT NULL,
FieldstarRating int NOT NULL,
FielddirectorID int NOT NULL,
PRIMARY KEY (FieldinventoryID),
FOREIGN KEY (FieldinventoryID)
REFERENCES INVENTORY(FieldinventoryID),
FOREIGN KEY (FielddirectorID)
REFERENCES DIRECTOR(FielddirectorID)
);
DROP TABLE IF EXISTS CAST;
CREATE TABLE CAST (
FieldactorID int NOT NULL,
FieldinventID int NOT NULL,
Fieldrole nchar(50) NOT NULL,
PRIMARY KEY (FieldactorID,FieldinventID),
FOREIGN KEY (FieldinventID)
REFERENCES MOVIE(FieldinventoryID),
FOREIGN KEY (FieldactorID)
CREATE TABLE COLLECTIBLE (
FieldinventoryID int NOT NULL,
FieldmadeYear int NOT NULL,
FieldmanufacturerID int NOT NULL,
PRIMARY KEY (FieldinventoryID),
FOREIGN KEY (FieldinventoryID)
REFERENCES INVENTORY(FieldinventoryID),
FOREIGN KEY (FieldmanufacturerID)
REFERENCES MANUFACTURER(FieldmanufacturerID)
);
DROP TABLE IF EXISTS COLLECTION_MEMBER;
CREATE TABLE COLLECTION_MEMBER (
FieldinventoryID int NOT NULL,
FieldcollectionID int NOT NULL,
PRIMARY KEY (FieldinventoryID, FieldcollectionID),
FOREIGN KEY (FieldinventoryID)
REFERENCES INVENTORY(FieldinventoryID),
FOREIGN KEY (FieldcollectionID)
REFERENCES COLLECTION(FieldcollectionID)
);
DROP TABLE IF EXISTS MOVIE;
CREATE TABLE MOVIE (
FieldinventoryID int NOT NULL,
FieldreleaseYear int NOT NULL,
Fieldclassification nchar(5) NOT NULL,
FieldstarRating int NOT NULL,
FielddirectorID int NOT NULL,
PRIMARY KEY (FieldinventoryID),
FOREIGN KEY (FieldinventoryID)
REFERENCES INVENTORY(FieldinventoryID),
FOREIGN KEY (FielddirectorID)
REFERENCES DIRECTOR(FielddirectorID)
);
DROP TABLE IF EXISTS CAST;
CREATE TABLE CAST (
FieldactorID int NOT NULL,
FieldinventID int NOT NULL,
Fieldrole nchar(50) NOT NULL,
PRIMARY KEY (FieldactorID,FieldinventID),
FOREIGN KEY (FieldinventID)
REFERENCES MOVIE(FieldinventoryID),
FOREIGN KEY (FieldactorID)
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
REFERENCES ACTOR(FieldactorID)
);
DROP TABLE IF EXISTS CUSTOMER;
CREATE TABLE CUSTOMER (
FieldcustomerID int NOT NULL AUTO_INCREMENT,
FieldFirstName nchar(50) NOT NULL,
FieldLastName nchar(50) NOT NULL,
FieldPhone nchar(15) NOT NULL,
FieldEmail nchar(50) NOT NULL,
FieldAddress nchar(100) NOT NULL,
FieldSuburb nchar(50) NOT NULL,
FieldPostcode int NOT NULL,
FieldloyaltyDiscount decimal(5,2) NULL,
PRIMARY KEY (FieldcustomerID)
);
DROP TABLE IF EXISTS RECEIPT;
CREATE TABLE RECEIPT (
FieldreceiptID int NOT NULL AUTO_INCREMENT,
FieldcustomerID int NOT NULL,
FieldreceiptDate date NOT NULL,
PRIMARY KEY (FieldreceiptID),
FOREIGN KEY (FieldcustomerID)
REFERENCES CUSTOMER(FieldcustomerID)
);
DROP TABLE IF EXISTS PURCHASE;
CREATE TABLE PURCHASE (
FieldreceiptID int NOT NULL,
FieldinventoryID int NOT NULL,
Fieldquantity int NOT NULL,
FieldamountPaid decimal(5,2) NOT NULL,
PRIMARY KEY (FieldreceiptID),
FOREIGN KEY (FieldreceiptID)
REFERENCES RECEIPT(FieldreceiptID),
FOREIGN KEY (FieldinventoryID)
REFERENCES INVENTORY(FieldinventoryID)
);
);
DROP TABLE IF EXISTS CUSTOMER;
CREATE TABLE CUSTOMER (
FieldcustomerID int NOT NULL AUTO_INCREMENT,
FieldFirstName nchar(50) NOT NULL,
FieldLastName nchar(50) NOT NULL,
FieldPhone nchar(15) NOT NULL,
FieldEmail nchar(50) NOT NULL,
FieldAddress nchar(100) NOT NULL,
FieldSuburb nchar(50) NOT NULL,
FieldPostcode int NOT NULL,
FieldloyaltyDiscount decimal(5,2) NULL,
PRIMARY KEY (FieldcustomerID)
);
DROP TABLE IF EXISTS RECEIPT;
CREATE TABLE RECEIPT (
FieldreceiptID int NOT NULL AUTO_INCREMENT,
FieldcustomerID int NOT NULL,
FieldreceiptDate date NOT NULL,
PRIMARY KEY (FieldreceiptID),
FOREIGN KEY (FieldcustomerID)
REFERENCES CUSTOMER(FieldcustomerID)
);
DROP TABLE IF EXISTS PURCHASE;
CREATE TABLE PURCHASE (
FieldreceiptID int NOT NULL,
FieldinventoryID int NOT NULL,
Fieldquantity int NOT NULL,
FieldamountPaid decimal(5,2) NOT NULL,
PRIMARY KEY (FieldreceiptID),
FOREIGN KEY (FieldreceiptID)
REFERENCES RECEIPT(FieldreceiptID),
FOREIGN KEY (FieldinventoryID)
REFERENCES INVENTORY(FieldinventoryID)
);
Task 3
Insert Statements
use Startstruck_Entertainment_30335301;
INSERT INTO GENRE (FieldgenreName, FieldDescription) 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 (FieldsupplierName, FieldContactName, FieldPhone,
FieldEmail, FieldAddress, FieldSuburb, FieldPostcode, FieldCountry,
FieldComments) 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","Glenfield",2167,"Australia","Christmas orders by November 15"),
("Movie Magic","Sandor Butterfield","+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 (FieldFirstName, FieldLastName, FieldPlaceOfBirth,
FieldDateOfBirth, FieldDateOfDeath) 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_30335301;
INSERT INTO GENRE (FieldgenreName, FieldDescription) 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 (FieldsupplierName, FieldContactName, FieldPhone,
FieldEmail, FieldAddress, FieldSuburb, FieldPostcode, FieldCountry,
FieldComments) 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","Glenfield",2167,"Australia","Christmas orders by November 15"),
("Movie Magic","Sandor Butterfield","+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 (FieldFirstName, FieldLastName, FieldPlaceOfBirth,
FieldDateOfBirth, FieldDateOfDeath) 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 (FieldFirstName, FieldLastName, FieldPlaceOfBirth,
FieldDateOfBirth) 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 (FieldfirstName, FieldlastName, FieldPlaceOfBirth,
FieldDateOfBirth) 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 (FieldfirstName, FieldlastName, FieldPlaceOfBirth,
FieldDateOfBirth, FieldDateOfDeath) 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 (FieldmanufacturerName, FieldPhone, FieldEmail,
FieldAddress, FieldSuburb, FieldPostcode, FieldCountry) 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"),
FieldDateOfBirth) 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 (FieldfirstName, FieldlastName, FieldPlaceOfBirth,
FieldDateOfBirth) 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 (FieldfirstName, FieldlastName, FieldPlaceOfBirth,
FieldDateOfBirth, FieldDateOfDeath) 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 (FieldmanufacturerName, FieldPhone, FieldEmail,
FieldAddress, FieldSuburb, FieldPostcode, FieldCountry) 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 (FieldfirstName, FieldlastName, FieldPhone, FieldEmail,
FieldAddress, FieldSuburb, FieldPostcode) 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 (FieldfirstName, FieldlastName, FieldPhone, FieldEmail,
FieldAddress, FieldSuburb, FieldPostcode, FieldloyaltyDiscount ) 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 (Fieldtitle, FieldunitPrice, Fieldmarkup, Fielddiscount,
FieldqtyInStock, FieldgenreID, FieldsupplierID) 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 (FieldinventoryID,FieldreleaseYear, Fieldclassification,
FieldstarRating, FielddirectorID) 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 (FieldfirstName, FieldlastName, FieldPhone, FieldEmail,
FieldAddress, FieldSuburb, FieldPostcode) 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 (FieldfirstName, FieldlastName, FieldPhone, FieldEmail,
FieldAddress, FieldSuburb, FieldPostcode, FieldloyaltyDiscount ) 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 (Fieldtitle, FieldunitPrice, Fieldmarkup, Fielddiscount,
FieldqtyInStock, FieldgenreID, FieldsupplierID) 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 (FieldinventoryID,FieldreleaseYear, Fieldclassification,
FieldstarRating, FielddirectorID) values
(4,1958,"M",4,7 ),
(5,2011,"M",4,8 ),
(6,2006,"MA",3,2 );
insert into COLLECTIBLE (FieldinventoryID, FieldmadeYear, FieldmanufacturerID)
values
(1,2016,1),
(2,2011,1),
(3,2006,2),
(7,2016,2),
(8,1993,4),
(9,2013,4);
insert into COLLECTION (FieldcollectionName, FieldDescription,
FielddateReleased) 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 (FieldactorID, FieldinventID, Fieldrole) 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");
values
(1,2016,1),
(2,2011,1),
(3,2006,2),
(7,2016,2),
(8,1993,4),
(9,2013,4);
insert into COLLECTION (FieldcollectionName, FieldDescription,
FielddateReleased) 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 (FieldactorID, FieldinventID, Fieldrole) 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.