ITECH1006 Assignment 2: Database Schema, Queries, and Implementation

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 -

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

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

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

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,

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)
);

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)

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)
);

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","[e-mail]","94 Lancaster
St","Collingwood",3066,"Australia",""),
("Mega Entertainment","James
McGrath","[phone]","[e-mail]","32 Easy
St","Perth",6140,"Australia",""),
("Crazy Collectibles","Ernie
Miller","0448789987","[e-mail]","3 North
Road","Auckland",1111,"New Zealand",""),
("Rare Films","Samantha Stevens","0751672924","rarefilms.com.au","9 Newcastle
Ave","Cabramatta",2166,"Australia",""),
("Premiere Entertainment","Winston
Jones","0657168342","premiereentertainment.com","1548 Hume
Hwy","Glenfield",2167,"Australia","Christmas orders by November 15"),
("Movie Magic","Sandor Butterfield","[phone]","sales@movie-
magic.co.uk","88 Bond Parade","London",9911,"UK","Recalculate VAT"),
("VIC Entertainment","Rupert
Ballinger","0498528417","vicentertainment.gov.au","62
BluebirdCrt","Collingwood",3066,"Australia",""),
("Samson Films","Delilah Jones","[phone]","samsonfilms.com.au","27
Fortitude Dr","Brisbane",4001,"Australia","Minimum order $100");
insert into ACTOR (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 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", "[phone]", "[e-mail]","9 Rue de Chat
Noir","Paris", 7519,"France"),
("Movie Figure Smithy", "[phone]", "[e-mail]","4 Beach Road
East","Wan Chai", 7654, "Hong Kong"),

("Modelworks", "[phone]", "[e-mail]","12 Danshui
Street","Zhongzheng", 1020, "Taiwan"),
("Rockstar", "[phone]", "[e-mail]","40 Grand
Drive","Chicago", 1999, "USA");
insert into CUSTOMER (FieldfirstName, FieldlastName, FieldPhone, FieldEmail,
FieldAddress, FieldSuburb, FieldPostcode) values
("Brigg","John","53159510","[e-mail]","1 Bells
Road","Napoleons","3352"),
("Summers","Joe","45613208","[e-mail]","52 Main
St","Maryborough","3465"),
("Winter","Sam","12356713","[e-mail]","2156 Skipton
Rd","Sebastopol","3356"),
("Fall","Sally","15626543","[e-mail]","65 Commercial
St","Clunes","3370"),
("Spencer","Troy","040853185","[e-mail]","65Crest
Ave","Ballarat","3353"),
("Jones","Tommy","51657896","[e-mail]","1254 Sturt
St","Ballarat","3353");
insert into CUSTOMER (FieldfirstName, FieldlastName, FieldPhone, FieldEmail,
FieldAddress, FieldSuburb, FieldPostcode, FieldloyaltyDiscount ) values
("Spring","Mary","13265413","[e-mail]","98 Neil
St","Beaufort","3373",0.12),
("Jones","Pat","049513572","[e-mail]","46 Wattle
Ave","Wendouree","3355",0.1),
("Matthews","Doreen","21356467","[e-mail]","7 Hepburn Springs
Blvd","Daylesford","3460",0.15),
("McKenzie","Barry","32165489","[e-mail]","47 Gillies
St","Ballarat","3350",0.19);
insert into INVENTORY (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");

Task 4
SQL Queries
use Startstruck_Entertainment_30335301;
/* Query a */
select * from Supplier order by FieldSuburb;
/* Query b */
Select Fieldtitle, FieldsupplierName, FieldEmail, FieldPhone, FieldSuburb from
Inventory inner join Movie
on Inventory. FieldinventoryID= Movie. FieldinventoryID
inner join Supplier on Inventory. FieldsupplierID= Supplier. FieldsupplierID;
/* Query c */
select CONCAT( FieldfirstName," " , FieldlastName) as 'ActorName',
YEAR(now()) - YEAR( FieldDateOfBirth) - (DATE_FORMAT(now(), '%m%d') <
DATE_FORMAT( FieldDateOfBirth, '%m%d')) as ActorAge
from Actor where FieldDateOfDeath is null;
/* Query d */
Select Inventory. Fieldtitle, FieldgenreName from Inventory inner join Genre
on Inventory. FieldgenreID= Genre. FieldgenreID order by Fieldtitle desc;
/* Query e */
select Fieldtitle, FieldunitPrice, Fieldmarkup, FieldqtyInStock, FieldsupplierName,
FieldmanufacturerName
from COLLECTIBLE inner join inventory on COLLECTIBLE. FieldinventoryID=
inventory. FieldinventoryID
inner join SUPPLIER on SUPPLIER. FieldsupplierID= inventory. FieldsupplierID
inner join manufacturer on manufacturer. FieldmanufacturerID= COLLECTIBLE.
FieldmanufacturerID
order by Fieldtitle;
/* Query f */
Select FieldgenreName from Genre where FieldgenreID not in (select
FieldgenreID from Inventory);
/* Query g */
SET SQL_SAFE_UPDATES=0;
delete from customer where FieldPostcode=3353;
/* Query h */

SET SQL_SAFE_UPDATES=0;
update Inventory set Fieldtitle="Wookie Figure – live size" where
Fieldtitle="Wookie Figure";
/* Query i */
select inventory. Fieldtitle, CONCAT( ACTOR. FieldfirstName," " , ACTOR.
FieldlastName) as 'ActorName', CONCAT( DIRECTOR. FieldfirstName," " ,
DIRECTOR. FieldlastName) as 'DirectorName'
from MOVIE inner join inventory on MOVIE. FieldinventoryID= inventory.
FieldinventoryID
inner join DIRECTOR on MOVIE. FielddirectorID= DIRECTOR. FielddirectorID
inner join CAST on MOVIE. FieldinventoryID= CAST. FieldinventID
inner join ACTOR on CAST. FieldactorID= ACTOR. FieldactorID;
/* Query j */
SET autocommit=0;
START TRANSACTION;
insert into DIRECTOR ( FielddirectorID, FieldfirstName, FieldlastName,
FieldPlaceOfBirth, FieldDateOfBirth) values
(9, "Steven", "Soderberghin", "Atlanta", STR_TO_DATE("01-14-1963","%m-%d-
%Y"));
insert into INVENTORY ( FieldinventoryID, Fieldtitle, FieldunitPrice,
Fieldmarkup, Fielddiscount, FieldqtyInStock, FieldgenreID) values
(10, "Solaris", 18,35,10,27,4);
insert into MOVIE ( FieldinventoryID, FieldreleaseYear, Fieldclassification,
FieldstarRating, FielddirectorID) values
(10,2002,'M',4,9);
COMMIT;
/* Query k */
select Fieldtitle, FieldqtyInStock, ( FieldunitPrice + Fieldmarkup) as 'SalePrice'
from inventory;
/* Query l */
Select Actor. FieldfirstName, Actor. FieldlastName, Fieldtitle, FieldgenreName
from Actor inner join Cast on Actor. FieldActorID= Cast. FieldActorID
inner join Inventory on Inventory. FieldinventoryID= Cast. FieldinventID
inner join Genre on Inventory. FieldgenreID= Genre. FieldgenreID order by Actor.
FieldlastName desc;
(H. Bdr 2013)

References
P. Litwin (n.d.), Fundamentals of Relational Database Design, Retrieved from
http://www.deeptraining.com/litwin/dbdesign/
FundamentalsOfRelationalDatabaseDesign.aspx.
H. Bdr (2013), SQL Database, Retrieved from
http://harkachalise.blogspot.in/2013/03/simple-and-composite-attributes.html.