Database Fundamentals: IPS Database Design and Implementation

Database
Fundamentals
Student ID:
Student Name:
5/25/2019

Report
Introduction
The report is describing the database system of International Parcel Service (IPS). The complete
database design and database implementation in MS Access is being shown in the report. The
database design is being shown by Entity Relationship Diagram which is showing all entities and
relationships among that entities.
1. ER Diagram

The ER diagram is showing the entities and relationship among them. Following rules are
followed in the above ER Diagram-
- A customer and the item may be involved in any number of transportations.
- Similarly an employee and the item may belong to any number of receipts.
- A retail centre may contain any number of employees.
- A retail centre will generate any number of receipts.
2. Third Normal Form Relations
TabCustomers (FllcustomerID, FllCustomerName, Flladdress, Fllphone, Fllemail)
Primary Key FllCustomerID
TabItems (FllitemNumber, Fllweight, Flldimensions, FllinsuranceAmount, Flldestination,
FllfinalDeliveryDate, Fllinstructions)
Primary Key FllitemNumber
TabRetailCentres (FllretailCentreID, Flltype, Flladdress)
Primary Key FllretailCentreID
TabEmployees (FllemployeeID, FllemployeeName, Flladdress, Fllphone, Fllemail,
FllretailCentreID)
Primary Key FllemployeeID
FllretailCentreID references TabretailCentres (FllretailCentreID)
TabReceipts (FllitemNumber, FllreceivedDate, Fllcharges, FllretailCentreID, FllemployeeID)
Primary Key FllitemNumber
FllretailCentreID references TabretailCentres (FllretailCentreID)
FllemployeeID references TabEmployees (FllemployeeID)
FllitemNumber references TabItems (FllitemNumber)
TabTransportations (FllscheduleNumber, FllcustomerID, Flltype, FlldeliveryRoute,
FllitemNumber)
Primary Key FllscheduleNumber
FllcustomerID references TabCustomers (FllcustomerID)
FllitemNumber references TabItems (FllitemNumber)