B01DBFN212 Database Project: IPS Database Design and Implementation

This report presents the design and implementation of a database for International Parcel Service (IPS). It begins with an introduction outlining the project's scope, which includes both the logical and physical design of the database. An ER diagram illustrates the entities (Customer, Transportation, RetailCentre, Employee, Item, and Receipt) and their relationships. The report then details the database design, showing relations in normalized form, including table structures for Customers, Items, RetailCentres, Employees, Receipts, and Transportation, with primary and foreign keys defined. Functional dependencies within the tables are also analyzed. The implementation is demonstrated using MS Access, with examples of form design and data insertion. The report concludes with several SQL queries, showcasing how to retrieve and manipulate data from the database, including queries using SELECT, JOIN, and GROUP BY clauses. References for functional dependencies are also included.

[Database fundamentals]
2019
Student ID:
Student Name:

Report
Introduction
The report is showing the logical and physical design of International Parcel Service (IPS). The
ER diagram is showing the important entities and relationships among them. The database design
is being shown by ER diagram while the database is implemented in the MS Access database.

1. ER Diagram
The ER diagram is showing the logical design of the International Parcel Service (IPS) system. There
are six entities in the system. There is one-to-many relationship between Customer and
Transportation entity. There is one-to-many relationship between RetailCentre and Employee
entity. There is one-to-many relationship between Item and Transportation entity. There is one-
to-many relationship between Item and Receipt entity. There is one-to-many relationship

between Employee and Receipt entity. There is one-to-many relationship between RetailCentre
and Receipt entity.
2. Relations in Normalized Form
TabbCustomer (FFcustomerID, FFCustomerName, FFaddress, FFphone, FFemail)
Primary Key FFCustomerID
TabbItem (FFitemNumber, FFweight, FFdimensions, FFinsuranceAmount, FFdestination,
FFfinalDeliveryDate, FFinstructions)
Primary Key FFitemNumber
TabbRetailCentre (FFretailCentreID, FFtype, FFaddress)
Primary Key FFretailCentreID
TabbEmployee (FFemployeeID, FFemployeeName, FFaddress, FFphone, FFemail,
FFretailCentreID)
Primary Key FFemployeeID
FFretailCentreID references TabbretailCentre (FFretailCentreID)
TabbReceipt (FFitemNumber, FFreceivedDate, FFcharges, FFretailCentreID, FFemployeeID)
Primary Key FFitemNumber
FFretailCentreID references TabbretailCentre (FFretailCentreID)
FFemployeeID references TabbEmployee (FFemployeeID)
FFitemNumber references TabbItem (FFitemNumber)
TabbTransportation (FFscheduleNumber, FFcustomerID, FFtype, FFdeliveryRoute,
FFitemNumber)
Primary Key FFscheduleNumber
FFcustomerID references TabbCustomer (FFcustomerID)

FFitemNumber references TabbItem (FFitemNumber)
Functional Dependency
There is not any repeated group in the employee table.
Every field depends upon the primary key EmployeeID and EmployeeID is unique key storing
only id of the employee. It is not composite key. Therefore there is no partial dependency.
All fields do not depend upon any other field except Primary key. Therefore, there is no
transitive dependency in the table.
These are the rules of third normalization and the table Employee is satisfying the above rules.
EmployeeName  EmployeeID
Address  EmployeeID
Phone  EmployeeID
Email  EmployeeID
RetailCentreID  EmployeeID
(Opentextbc.ca (n.d.)
(Tutorialspoint.com 2019)

3. Database Implementation
4. Item Form

5. Data Insertion
Customers Table
RetailCentres Table
Receipts Table
Items Table

Employees Table
Transportations Table
6. Queries
Query 1
SELECT * FROM Transportations WHERE type='Flight';
Query 2
SELECT type, count(scheduleNumber) AS NumberOfTransportations FROM Transportations
GROUP BY type;

Query 3
SELECT Transportations.scheduleNumber, Transportations.type, Transportations.deliveryRoute,
Customers.customerName, Transportations.itemNumber FROM Customers INNER JOIN
Transportations ON Customers.customerID=Transportations.customerID;
7. Report

Conclusion
The report is summarizing the complete database system of International Parcel Service (IPS).
The database queries, report and form are created in the database and all have shown in the
report. The report is showing the showing the way to create select query, inner join query and the
function query using group by clause.
References
Opentextbc.ca (n.d.). Chapter 11 Functional Dependencies. Online. Available:
https://opentextbc.ca/dbdesign01/chapter/chapter-11-functional-dependencies/. [Accessed: 25-
May-2019]
Tutorialspoint.com (2019). What is Functional Dependency. Online. Available:
https://www.tutorialspoint.com/Functional-dependency-in-DBMS. [Accessed: 25-May-2019]