ITECH 1006 Assignment on Database Management Systems

Assignment 2
ITECH 1006 – Database Management Systems
Student ID – 30335301
Student Name -
Date –
Module Tutor -

Report
Task 1Relational 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.)