|
Attribute |
Datetype |
Key |
|
genreID |
INT |
PK |
|
Attribute |
Datetype |
|
supplierID |
INT |
|
supplierName |
TEXT(50) |
|
Contact |
TEXT(50) |
|
Phone |
TEXT(15) |
|
|
TEXT(50) |
|
Address |
TEXT(50) |
|
Suburb |
TEXT(50) |
|
Postcode |
TEXT(5) |
|
Country |
TEXT(50) |
|
Comments |
TEXT(250) |
|
Attribute |
Datetype |
|
actorID |
INT |
|
firstName |
TEXT(50) |
|
lastName |
TEXT(50) |
|
PlaceOfBirth |
TEXT(100) |
|
DateOfBirth |
DATE |
|
DateOfDeath |
DATE |
|
Attribute |
Datetype |
|
inventoryID |
INT |
|
title |
TEXT(250) |
|
unitPrice |
DECIMAL(5,2) |
|
markup |
DECIMAL(5,2) |
|
discount |
DECIMAL(5,2) |
|
qtyInStock |
INT |
|
genreID |
INT |
|
supplierID |
INT |
|
Attribute |
Datetype |
Key |
|
receiptID |
INT |
PK |
|
customerID |
INT |
FK |
|
Attribute |
Datetype |
|
receiptID |
INT |
|
inventoryID |
INT |
|
quantity |
INT |
|
amountPaid |
DECIMAL(5,2) |
DROP Database IF EXISTS Startstruck_Entertainment_StudentID;
create database Startstruck_Entertainment_StudentID;
use Startstruck_Entertainment_StudentID;
DROP TABLE IF EXISTS GENRE;
CREATE TABLE GENRE (
genreID int NOT NULL AUTO_INCREMENT,
genreName varchar(50) NOT NULL,
Description varchar(250) NULL,
PRIMARY KEY (genreID)
DROP TABLE IF EXISTS SUPPLIER;
CREATE TABLE SUPPLIER (
supplierID int NOT NULL AUTO_INCREMENT,
supplierName varchar(50) NOT NULL,
ContactName varchar(50) NOT NULL,
Phone varchar(15) NOT NULL,
Email varchar(50) NOT NULL,
Address varchar(50) NOT NULL,
Suburb varchar(50) NOT NULL,
Postcode varchar(5) NOT NULL,
Country varchar(50) NOT NULL,
Comments varchar(250) NULL,
PRIMARY KEY (supplierID)
DROP TABLE IF EXISTS ACTOR;
CREATE TABLE ACTOR (
actorID int NOT NULL AUTO_INCREMENT,
FirstName varchar(50) NOT NULL,
LastName varchar(50) NOT NULL,
PlaceOfBirth varchar(100) NOT NULL,
DateOfBirth Date NOT NULL,
DateOfDeath Date NULL,
PRIMARY KEY (actorID)
DROP TABLE IF EXISTS INVENTORY;
CREATE TABLE INVENTORY (
inventoryID int NOT NULL AUTO_INCREMENT,
title varchar(250) NOT NULL,
unitPrice decimal(5,2) NOT NULL,
markup decimal(5,2) NOT NULL,
discount decimal(5,2) NOT NULL,
qtyInStock int NOT NULL,
genreID int NOT NULL,
supplierID int NULL,
PRIMARY KEY (inventoryID),
FOREIGN KEY (genreID)
REFERENCES GENRE(genreID),
FOREIGN KEY (supplierID)
REFERENCES SUPPLIER(supplierID)
DROP TABLE IF EXISTS MANUFACTURER;
CREATE TABLE MANUFACTURER (
manufacturerID int NOT NULL AUTO_INCREMENT,
manufacturerName varchar(50) NOT NULL,
Phone varchar(15) NOT NULL,
Email varchar(50) NOT NULL,
Address varchar(100) NOT NULL,
Suburb varchar(50) NOT NULL,
Postcode int NOT NULL,
Country varchar(50) NULL,
PRIMARY KEY (manufacturerID)
DROP TABLE IF EXISTS DIRECTOR
CREATE TABLE DIRECTOR (
directorID int NOT NULL AUTO_INCREMENT,
FirstName varchar(50) NOT NULL,
LastName varchar(50) NOT NULL,
PlaceOfBirth varchar(100) NOT NULL,
DateOfBirth Date NOT NULL,
DateOfDeath Date NULL,
PRIMARY KEY (directorID)
DROP TABLE IF EXISTS COLLECTION;
CREATE TABLE COLLECTION (
collectionID int NOT NULL AUTO_INCREMENT,
collectionName varchar(50) NOT NULL,
Description varchar(250) NOT NULL,
dateReleased Date NOT NULL,
PRIMARY KEY (collectionID)
DROP TABLE IF EXISTS COLLECTIBLE;
CREATE TABLE COLLECTIBLE (
inventoryID int NOT NULL,
madeYear int NOT NULL,
manufacturerID int NOT NULL,
PRIMARY KEY (inventoryID),
FOREIGN KEY (inventoryID)
REFERENCES INVENTORY(inventoryID),
FOREIGN KEY (manufacturerID)
REFERENCES MANUFACTURER(manufacturerID)
DROP TABLE IF EXISTS COLLECTION_MEMBER;
CREATE TABLE COLLECTION_MEMBER (
inventoryID int NOT NULL,
collectionID int NOT NULL,
PRIMARY KEY (inventoryID, collectionID),
FOREIGN KEY (inventoryID)
REFERENCES INVENTORY(inventoryID),
FOREIGN KEY (collectionID)
REFERENCES COLLECTION(collectionID)
DROP TABLE IF EXISTS MOVIE;
CREATE TABLE MOVIE (
inventoryID int NOT NULL,
releaseYear int NOT NULL,
classification varchar(5) NOT NULL,
starRating int NOT NULL,
directorID int NOT NULL,
PRIMARY KEY (inventoryID),
FOREIGN KEY (inventoryID)
REFERENCES INVENTORY(inventoryID),
FOREIGN KEY (directorID)
REFERENCES DIRECTOR(directorID)
DROP TABLE IF EXISTS CAST;
CREATE TABLE CAST (
actorID int NOT NULL,
inventID int NOT NULL,
role varchar(50) NOT NULL,
PRIMARY KEY (actorID,inventID),
FOREIGN KEY (inventID)
REFERENCES MOVIE(inventoryID),
FOREIGN KEY (actorID)
REFERENCES ACTOR(actorID)
DROP TABLE IF EXISTS CUSTOMER;
CREATE TABLE CUSTOMER (
customerID int NOT NULL AUTO_INCREMENT,
FirstName varchar(50) NOT NULL,
LastName varchar(50) NOT NULL,
Phone varchar(15) NOT NULL,
Email varchar(50) NOT NULL,
Address varchar(100) NOT NULL,
Suburb varchar(50) NOT NULL,
Postcode int NOT NULL,
loyaltyDiscount decimal(5,2) NULL,
PRIMARY KEY (customerID)
DROP TABLE IF EXISTS RECEIPT;
CREATE TABLE RECEIPT (
receiptID int NOT NULL AUTO_INCREMENT,
customerID int NOT NULL,
receiptDate date NOT NULL,
PRIMARY KEY (receiptID),
FOREIGN KEY (customerID)
REFERENCES CUSTOMER(customerID)
DROP TABLE IF EXISTS PURCHASE;
CREATE TABLE PURCHASE (
receiptID int NOT NULL,
inventoryID int NOT NULL,
quantity int NOT NULL,
amountPaid decimal(5,2) NOT NULL,
PRIMARY KEY (receiptID),
FOREIGN KEY (receiptID)
REFERENCES RECEIPT(receiptID),
FOREIGN KEY (inventoryID)
REFERENCES INVENTORY(inventoryID)
INSERT INTO GENRE (genreName, Description) VALUES
(“Comedy”,””),
(“Sports”, “Fictional and sports documentaries”),
(“Children”,”All age groups to 18″),
(“SciFi”,””),
(“Crime”,””),
(“Documentary”,”Factual accounts including science and history”),
(“Fiction”,”Any other fiction”),
(“Fantasy”,””),
(“Adventure”,””),
(“Action”,””);
INSERT INTO SUPPLIER (supplierName, ContactName, Phone, Email, Address, Suburb, Postcode, Country, Comments) values
(“Figures’n’Toys”,”Anita Schmidt”,”0456789163″,”[email protected]“,”94 Lancaster St”,”Collingwood”,3066,”Australia”,””),
(“Mega Entertainment”,”James McGrath”,”+61355551549″,”[email protected]“,”32 Easy St”,”Perth”,6140,”Australia”,””),
(“Crazy Collectibles”,”Ernie Miller”,”0448789987″,”[email protected]“,”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″,”[email protected]“,”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 (FirstName, LastName, PlaceOfBirth, DateOfBirth, DateOfDeath) values
(“Christopher”,”Lee”,”Belgravia London (UK)”,STR_TO_DATE(“05-27-1922″,”%m-%d-%Y”),STR_TO_DATE(“06-07-2015″,”%m-%d-%Y”)),
(“Marilyn”,”Monroe”,”Los Angeles California (USA)”,STR_TO_DATE(“06-01-1926″,”%m-%d-%Y”),STR_TO_DATE(“08-05-1962″,”%m-%d-%Y”))
insert into ACTOR (FirstName, LastName, PlaceOfBirth, DateOfBirth) values
(“Chris”,”Hemsworth”,”Melbourne Vic (Aus)”,STR_TO_DATE(“08-11-1983″,”%m-%d-%Y”)),
(“Angelina”,”Jolie”,”Los Angeles, Californaia (USA)”,STR_TO_DATE(“06-04-1975″,”%m-%d-%Y”)),
(“Meryl”,”Streep”,”Summit New Jersey (USA)”,STR_TO_DATE(“06-22-1949″,”%m-%d-%Y”)),
(“Charlize”,”Theron”,”Benoni Gauteng (Sth Africa)”,STR_TO_DATE(“08-07-1975″,”%m-%d-%Y”)),
(“Drew”,”Barrymore”,”Culver City California (USA)”,STR_TO_DATE(“02-22-1975″,”%m-%d-%Y”)),
(“Lucy”,”Liu”,”Jackson Heights New York (USA)”,STR_TO_DATE(“12-02-1968″,”%m-%d-%Y”)),
(“David”,”Tennant”,”Bathgate (UK)”,STR_TO_DATE(“04-18-1971″,”%m-%d-%Y”)),
(“Dwayne (The Rock)”,”Johnson”,”Hayward California (USA)”,STR_TO_DATE(“05-02-1972″,”%m-%d-%Y”)),
(“Robert (Jnr)”,”Downey”,”Manhattan, New York (USA)”,STR_TO_DATE(“04-04-1966″,”%m-%d-%Y”));
insert into DIRECTOR (firstName, lastName, PlaceOfBirth, DateOfBirth) values
(“Angelina”,”Jolie”,”Los Angeles California (USA)”,STR_TO_DATE(“06-04-1975″,”%m-%d-%Y”)),
(“Drew”,”Barrymore”,”Culver City, California (USA)”,STR_TO_DATE(“02-22-1975″,”%m-%d-%Y”)),
(“Steven”,”Spielberg”,”Cincinnati, Ohio (USA)”,STR_TO_DATE(“12-18-1946″,”%m-%d-%Y”)),
(“David”,”Heyman”,”London (UK)”,STR_TO_DATE(“07-26-1961″,”%m-%d-%Y”)),
(“Quinton”,”Tarantino”,”Knoxville Tennessee (USA)”,STR_TO_DATE(“03-27-1963″,”%m-%d-%Y”)),
(“Kenneth”,”Branagh”,”Belfast (UK)”,STR_TO_DATE(“12-10-1960″,”%m-%d-%Y”));
insert into DIRECTOR (firstName, lastName, PlaceOfBirth, DateOfBirth, DateOfDeath) values
(“Alfred”,”Hitchcock”,”London (UK)”,STR_TO_DATE(“08-13-1899″,”%m-%d-%Y”), STR_TO_DATE(“04-29-1980″,”%m-%d-%Y”)),
(“Terence”,”Fisher”,”Maida Vale, London (UK)”,STR_TO_DATE(“02-23-1904″,”%m-%d-%Y”), STR_TO_DATE(“06-18-1980″,”%m-%d-%Y”));
insert into MANUFACTURER (manufacturerName, Phone, Email, Address, Suburb, Postcode, Country) values
(“Focus Home Interactive”, “+48299836600”, “[email protected]“,”9 Rue de Chat Noir”,”Paris”, 7519,”France”),
(“Movie Figure Smithy”, “+69922124569”, “[email protected]“,”4 Beach Road East”,”Wan Chai”, 7654, “Hong Kong”),
(“Modelworks”, “+1456123789”, “[email protected]“,”12 Danshui Street”,”Zhongzheng”, 1020, “Taiwan”),
(“Rockstar”, “+1451586604”, “[email protected]“,”40 Grand Drive”,”Chicago”, 1999, “USA”);
insert into CUSTOMER (firstName, lastName, Phone, Email, Address, Suburb, Postcode) values
(“Brigg”,”John”,”53159510″,”[email protected]“,”1 Bells Road”,”Napoleons”,”3352″),
(“Summers”,”Joe”,”45613208″,”[email protected]“,”52 Main St”,”Maryborough”,”3465″),
(“Winter”,”Sam”,”12356713″,”[email protected]“,”2156 Skipton Rd”,”Sebastopol”,”3356″),
(“Fall”,”Sally”,”15626543″,”[email protected]“,”65 Commercial St”,”Clunes”,”3370″),
(“Spencer”,”Troy”,”040853185″,”[email protected]“,”65Crest Ave”,”Ballarat”,”3353″),
(“Jones”,”Tommy”,”51657896″,”[email protected]“,”1254 Sturt St”,”Ballarat”,”3353″);
insert into CUSTOMER (firstName, lastName, Phone, Email, Address, Suburb, Postcode, loyaltyDiscount ) values
(“Spring”,”Mary”,”13265413″,”[email protected]“,”98 Neil St”,”Beaufort”,”3373″,0.12),
(“Jones”,”Pat”,”049513572″,”[email protected]“,”46 Wattle Ave”,”Wendouree”,”3355″,0.1),
(“Matthews”,”Doreen”,”21356467″,”[email protected]“,”7 Hepburn Springs Blvd”,”Daylesford”,”3460″,0.15),
(“McKenzie”,”Barry”,”32165489″,”[email protected]“,”47 Gillies St”,”Ballarat”,”3350″,0.19);
insert into INVENTORY (title, unitPrice, markup, discount, qtyInStock, genreID, supplierID) values
(“R2D2”, 27.00,160,0,2,4,3),
(“Han Solo Figure”, 10.00,170,0,12,4,3),
(“Millenium Falcon Model”, 128.00,140,0,7,4,2),
(“Syberia”, 10.00,110,5,2,4,4),
(“Devil Wears Prada”, 10.00,130,5,14,1,8),
(“Great Expectations”, 10.00,180,5,6,7,2),
(“Wookie Figure”, 418.00,130,0,1,4,2),
(“Dekker Figure”, 22.00,210,0,6,4,1),
(“USS Enterprise Model”, 110.00,120,10,2,4,4);
insert into MOVIE (inventoryID,releaseYear, classification, starRating, directorID) values
(4,1958,”M”,4,7 ),
(5,2011,”M”,4,8 ),
(6,2006,”MA”,3,2 );
insert into COLLECTIBLE (inventoryID,madeYear, manufacturerID) values
(1,2016,1),
(2,2011,1),
(3,2006,2),
(7,2016,2),
(8,1993,4),
(9,2013,4);
insert into COLLECTION (collectionName, Description, dateReleased) values
(“Star Wars Droids“, “6 piece Star Wars droids collection”,STR_TO_DATE(“07-01-2015″,”%m-%d-%Y”)),
(“Star WarsMega Collection“, “8 piece Star Wars collection of characters from death star”,STR_TO_DATE(“06-15-2008″,”%m-%d-%Y”)),
(“Alien”, “3 piece Alien collection“,STR_TO_DATE(“09-30-1999″,”%m-%d-%Y”)),
(“Startrek”, “10 part super collection”,STR_TO_DATE(“12-24-2001″,”%m-%d-%Y”)),
(“Spaceships collection”, “15 part spaceship collection from a variety of movies”,STR_TO_DATE(“12-24-2001″,”%m-%d-%Y”)),
(“Bladerunner”, “5 character original Bladerunner collection”,STR_TO_DATE(“12-15-1990″,”%m-%d-%Y”));
insert into CAST (actorID, inventID, role) values
(1,4, “Hero”),
(2,4, “Heroine”),
(3,4, “Comedian”),
(4,5,”Comedian”),
(1,5,”Hero”),
(5,5,”Heroine”),
(6,5,”Villain”),
(7,6, “Hero”),
(8,6, “Heroine”),
(9,6, “Comedian”);
SQL Queries
use Startstruck_Entertainment_StudentID;
Select title, supplierName, Email, Phone, Suburb from Inventory inner join Movie on Inventory.inventoryID=Movie.inventoryID
inner join Supplier on Inventory.supplierID=Supplier.supplierID;
/* Query c */
select CONCAT(firstName,” ” ,lastName) as ‘Actor Name’,
YEAR(now()) – YEAR(DateOfBirth) – (DATE_FORMAT(now(), ‘%m%d’) < DATE_FORMAT(DateOfBirth, ‘%m%d’)) as Age
from Actor where DateOfDeath is null;
Select Inventory.title, genreName from Inventory inner join Genre
on Inventory.genreID=Genre.genreID order by title desc;
select title, unitPrice, markup, qtyInStock, supplierName, manufacturerName
from COLLECTIBLE inner join inventory on COLLECTIBLE.inventoryID=inventory.inventoryID
inner join SUPPLIER on SUPPLIER.supplierID=inventory.supplierID
inner join manufacturer on manufacturer.manufacturerID=COLLECTIBLE.manufacturerID
order by title;
Select genreName from Genre where genreID not in (select genreID from Inventory);
/* Query g */
SET SQL_SAFE_UPDATES=0;
delete from customer where Postcode=3353;
/* Query h */
SET SQL_SAFE_UPDATES=0;
update Inventory set title=”Wookie Figure – live size” where title=”Wookie Figure”;
/* Query i */
select inventory.title, CONCAT(ACTOR.firstName,” ” ,ACTOR.lastName) as ‘Actor Name’, CONCAT(DIRECTOR.firstName,” ” ,DIRECTOR.lastName) as ‘Director Name’
from MOVIE inner join inventory on MOVIE.inventoryID=inventory.inventoryID
inner join DIRECTOR on MOVIE.directorID=DIRECTOR.directorID
inner join CAST on MOVIE.inventoryID=CAST.inventID
inner join ACTOR on CAST.actorID=ACTOR.actorID;
SET autocommit=0;
START TRANSACTION;
insert into DIRECTOR (directorID, firstName, lastName, PlaceOfBirth, DateOfBirth) values
(9, “Steven”, “Soderberghin”, “Atlanta”, STR_TO_DATE(“01-14-1963″,”%m-%d-%Y”));
insert into INVENTORY (inventoryID, title, unitPrice, markup, discount, qtyInStock, genreID) values
(10, “Solaris”, 18,35,10,27,4);
insert into MOVIE (inventoryID,releaseYear, classification, starRating, directorID) values
(10,2002,’M’,4,9);
COMMIT;
/* Query k */
select title, qtyInStock, (unitPrice + markup) as ‘Sale Price’ from inventory;
Select Actor.firstName, Actor.lastName, title, genreName
from Actor inner join Cast on Actor.ActorID=Cast.ActorID
inner join Inventory on Inventory.inventoryID=Cast.inventID
inner join Genre on Inventory.genreID=Genre.genreID order by Actor.lastName desc;
References
https://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx
Tutorialspoint (2016), Relational Data Model, Retrieved from
https://www.tutorialspoint.com/dbms/relational_data_model.htm>
Essay Writing Service Features
Our Experience
No matter how complex your assignment is, we can find the right professional for your specific task. Contact Essay is an essay writing company that hires only the smartest minds to help you with your projects. Our expertise allows us to provide students with high-quality academic writing, editing & proofreading services.
Free Features
Free revision policy
$10Free bibliography & reference
$8Free title page
$8Free formatting
$8How Our Essay Writing Service Works
First, you will need to complete an order form. It's not difficult but, in case there is anything you find not to be clear, you may always call us so that we can guide you through it. On the order form, you will need to include some basic information concerning your order: subject, topic, number of pages, etc. We also encourage our clients to upload any relevant information or sources that will help.
Complete the order form
Once we have all the information and instructions that we need, we select the most suitable writer for your assignment. While everything seems to be clear, the writer, who has complete knowledge of the subject, may need clarification from you. It is at that point that you would receive a call or email from us.
Writer’s assignment
As soon as the writer has finished, it will be delivered both to the website and to your email address so that you will not miss it. If your deadline is close at hand, we will place a call to you to make sure that you receive the paper on time.
Completing the order and download