Database Fundamentals Project: Design and Implementation of IPS System

This report details the design and implementation of a relational database for the International Parcel Service (IPS) using MS Access. The project begins with the database design, including an ER diagram and normalization to the third normal form, followed by the implementation in MS Access. The report includes the creation of tables, queries (demonstrating SQL functionality), and a concluding report. The database is designed to manage various aspects of the IPS system, such as customer information, item details, retail centers, employee data, receipts, and transportation schedules. The queries demonstrate data retrieval and manipulation capabilities within the database. The report concludes by summarizing the features implemented in the MS Access database management system for the International Parcel Service (IPS). References to database normalization are included.

Student ID:
Student Name:

Report
Introduction
The report is showing the database design and development of International Parcel Service (IPS)
system. First the database design is developed and after that the database is implemented in MS
Access. All tables, queries and report are developed in MS Access. MS Access database
management system is very effective and appropriate for the International Parcel Service (IPS)
system because all required features are available in it.
ER Diagram

Third Normal Form Relations
TCustomer (FcustomerID, FCustomerName, Faddress, Fphone, Femail)
Primary Key FCustomerID
TItem (FitemNumber, Fweight, Fdimensions, FinsuranceAmount, Fdestination,
FfinalDeliveryDate, Finstructions)
Primary Key FitemNumber
TRetailCentre (FretailCentreID, Ftype, Faddress)
Primary Key FretailCentreID
TEmployee (FemployeeID, FemployeeName, Faddress, Fphone, Femail, FretailCentreID)
Primary Key FemployeeID
FretailCentreID references TretailCentre (FretailCentreID)
TReceipt (FitemNumber, FreceivedDate, Fcharges, FretailCentreID, FemployeeID)
Primary Key FitemNumber
FretailCentreID references TretailCentre (FretailCentreID)
FemployeeID references TEmployee (FemployeeID)
FitemNumber references TItem (FitemNumber)
TTransportation (FscheduleNumber, FcustomerID, Ftype, FdeliveryRoute, FitemNumber)
Primary Key FscheduleNumber
FcustomerID references TCustomer (FcustomerID)
FitemNumber references TItem (FitemNumber)

Functional Dependency in Receipt Table
Every field depends upon the primary key- ItemNumber and there is no any other primary or
composite key. Therefore, there is no partial dependency.
All fields depend upon the primary key- ItemNumber only. They do not depend upon any other
field. Therefore, there is no transitive dependency.
ReceivedDate  ItemNumber
Charges  ItemNumber
RetailCentreID  ItemNumber
EmployeeID  ItemNumber
Therefore the table Receipt is in 3 NF.
(Dimitri Fontaine 2019)
(Rouse M. n.d.)

Database Implementation
Item Form

Queries
Query 1
SELECT TItem.itemNumber, TTransportation.deliveryRoute, TReceipt.receivedDate,
TItem.finalDeliveryDate FROM (TItem INNER JOIN TTransportation ON
TItem.itemNumber=TTransportation.itemNumber) INNER JOIN TReceipt ON
TTransportation.itemNumber=TReceipt.itemNumber;
Query 2
SELECT TRetailCentre.retailCentreID, TRetailCentre.type, sum(TReceipt.charges) AS
TotalCharges FROM TRetailCentre INNER JOIN TReceipt ON
TRetailCentre.retailCentreID=TReceipt.retailCentreID GROUP BY
TRetailCentre.retailCentreID, TRetailCentre.type;
Query 3
SELECT * FROM TTransportation WHERE Type='Truck';

Report
Conclusion
The report is showing the all features which are implemented in MS Access database
management system for International Parcel Service (IPS). Database design and database
implementation both are being described in the report. The report is giving full detail about IPS
system. After studying the report, a user can easily understand the whole system of IPS.

References
Rouse M. (n.d.). Database Normalization. Online. Available:
https://searchsqlserver.techtarget.com/definition/normalization. [Accessed: 26-May-2019]
Dimitri Fontaine (2019). Database Normalization and Primary Keys. Online. Available:
https://tapoueh.org/blog/2018/03/database-normalization-and-primary-keys/. [Accessed: 26-
May-2019]