Database Management System

COVER PAGE

Contents
Q1................................................................................................................................................................3
Exer 1:......................................................................................................................................................3
Exer 2:......................................................................................................................................................3
Exer 3:......................................................................................................................................................5
Q2................................................................................................................................................................6
Exer 1..................................................................................................................................................6
Exer 2.......................................................................................................................................................8
Exer 3.......................................................................................................................................................9
Exer 4.....................................................................................................................................................10
Exer 5.....................................................................................................................................................11
Question 3.................................................................................................................................................12
Exer 1: Dependency diagram.................................................................................................................12
Exer 2: removing partial dependencies.................................................................................................13
Exer 3: removing transitive dependency...............................................................................................13
Exercise 4: ERD......................................................................................................................................14
Q4..............................................................................................................................................................15
Exer 1.....................................................................................................................................................15
Exer 2: SQL Code....................................................................................................................................15

Q1
Exer 1:
Product (productNumber, name, price, category, description, organizationCode)
PK (productNumber)
FK (organisationCode) references company (organizationCode)
Company (organizationCode, name, address, tel)
PK (organizationCode)
Customer (customerID, name, address, phoneNumber)
PK (customerID)
Purchase (PurchaseID, date, customerID)
PK (purchaseID)
FK (customerID) references customer (CustomerID)
Purchase_line (purchaseID, productID)
PK (purchaseID, productID, quantity)
FK (purchaseID) references Purchase (purchaseID)
FK (productID) references product (productID).
Exer 2:
ERD