Database System of International Parcel Service (IPS)

This report explains the logical and physical design of the database system of International Parcel Service (IPS) developed in MS Access. It includes ER diagram, relational schema, functional dependency, implementation details, and queries.

Student ID:
Student Name:
Date:

Report
Introduction
The report is explaining the database system of International Parcel Service (IPS). The logical
design and physical design both are explained in the report. The report is showing the database
report, database form and all developed queries. The logical design is developed by the ER
diagram and physical design is implemented into the MS Access database.
ER Diagram

The ER diagram is showing the database in pictorial form. Each entity and its attributes are being
shown in the report. The ER diagram is made in the crow’s foot notation. The tables in the ER
diagram are in the third normalized form.
Relational Schema inThird Normal Form Relations
TabCustomer (FlcustomerID, FlCustomerName, Fladdress, Flphone, Flemail)
Primary Key FlCustomerID
TabItem (FlitemNumber, Flweight, Fldimensions, FlinsuranceAmount, Fldestination,
FlfinalDeliveryDate, Flinstructions)
Primary Key FlitemNumber
TabRetailCentre (FlretailCentreID, Fltype, Fladdress)
Primary Key FlretailCentreID
TabEmployee (FlemployeeID, FlemployeeName, Fladdress, Flphone, Flemail,
FlretailCentreID)
Primary Key FlemployeeID
FlretailCentreID references Tabretail_Centre (FlretailCentreID)
TabReceipt (FlitemNumber, FlreceivedDate, Flcharges, FlretailCentreID, FlemployeeID)
Primary Key FlitemNumber
FlretailCentreID references Tabretail_Centre (FlretailCentreID)
FlemployeeID references TabEmployee (FlemployeeID)
FlitemNumber references TabItem (FlitemNumber)
TabTransportation (FlscheduleNumber, FlcustomerID, Fltype, FldeliveryRoute,
FlitemNumber)
Primary Key FlscheduleNumber
FlcustomerID references TabCustomer (FlcustomerID)
FlitemNumber references TabItem (FlitemNumber)

Functional Dependency in Retail Centre Table/ Dependency Diagram
Type  RetailCentreID
Address  RetailCentreID
- The table has no repeated group.
- All fields depend upon the primary key only. The primary key is single primary key. So,
there is no partial dependency in the table.
- There is no transitive dependency because all fields depend upon the primary key only.
The table is satisfying all the above mentioned rules of third normalization. Therefore, the retail
table is in third normal form.
All the tables are satisfying the same normalization rule in the database.
There is not any partial or transitive dependency in the above table. All tables are following the
same third normalization rules.
(Guru99 2016)

Database Implementation
(Access Programmers n.d.)
Item Form

Queries
Query 1
SELECT * FROM TabEmployee WHERE RetailCentreID=1001;
Query 2
SELECT RetailCentreID, Count(employeeID) AS NumberOfEmployees FROM TabEmployee
GROUP BY RetailCentreID;
Query 3
SELECT TabEmployee.retailCentreID, TabRetailCentre.address, TabEmployee.employeeID,
TabEmployee.employeeName, TabEmployee.address, TabEmployee.phone, TabEmployee.email
FROM TabRetailCentre INNER JOIN TabEmployee ON
TabRetailCentre.retailCentreID=TabEmployee.retailCentreID;

Report
The report is based upon the query 3 as shown below-

Conclusion
The report is fulfilling all the requirements of the IPS database system which is developed in MS
Access database. MS Access database is the complete package in itself because it gives the
features to make queries, forms and reports. The report is the complete justification of all the
tasks completed for the IPS database system.
References
Guru99 (2016). Learn Database Normalization with the help of a case study. Online. Available:
http://www.guru99.com/database-normalization.html. [Accessed: 22-May-2019]
Access Programmers (n.d.). Normalizing the Table Design. Online. Available:
https://www.access-programmers.com/normalizing-the-table-design.aspx. [Accessed: 22-May-
2019]