Database Normalization and Implementation for COIT20247

COIT2024
7
Database
Design
and
Developm
ent
May 22
201
9
Student ID:
Student Name:
Module Tutor:

Database Normalization
3NF Relations
Customer (CustomerID, CustomerName, Street, City, PostCode, CustomerContactNum)
Primary Key CustomerID
Employee (EmployeeID, EmployeeName, EmployeeStartDate)
Primary Key EmployeeID
Parcel (ParcelID, ParcelWeightInGrams, FromStreet, FromCity, FromPostCode, ToStreet,
ToCity, ToPostCode, DateCollected, ParcelValue, Charges, Status, CustomerID, EmployeeID)
Primary Key ParcelID
CustomerID references Customer (CustomerID)
EmployeeID references Employee (EmployeeID)
Delivered (ParcelID, DeliveredDate)
Primary Key ParcelID
ParcelID references Parcel (ParcelID)
Returned (ParcelID, ReturnedDate)
Primary Key ParcelID
ParcelID references Parcel (ParcelID)
Lost (ParcelID, LostEntryDate)
Primary Key ParcelID
ParcelID references Parcel (ParcelID)
Claim (ClaimID, ClaimLodgeDate, ClaimSettledAmount, ClaimPaidDate, ParcelID)
Primary Key ClaimID
ParcelID references Lost (ParcelID)
Invoice (InvoiceID, InvoiceAmount, InvoiceDate, ParcelID)
Primary Key InvoiceID
ParcelID references Parcel (ParcelID)

Functional Dependency
Customer Table
CustomerName  CustomerID
Street  CustomerID
City  CustomerID
PostCode  CustomerID
CustomerContactNum  CustomerID
Invoice Table
InvoiceAmount  InvoiceID
InvoiceDate  InvoiceID
ParcelID  InvoiceID