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]