Database Fundamentals Report: Design and Implementation of IPS System

This report details the database system for International Parcel Service (IPS), showcasing complete database design and implementation using MS Access. The report presents an Entity Relationship (ER) Diagram illustrating entities and their relationships, adhering to database normalization principles. It covers the creation of tables like Customers, Items, RetailCentres, Employees, Receipts, and Transportations, with primary and foreign key designations. The report emphasizes Third Normal Form (3NF) relations, functional dependencies, and the absence of repeated groups or partial and transitive dependencies. Database implementation is demonstrated through forms, input data, and SQL queries to retrieve and manipulate data. The report includes example queries, such as retrieving data from joined tables and calculating total charges, and concludes by summarizing the key concepts covered, like database queries, reports, normalization, and SQL functions. References to online resources for database concepts are also provided.

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)

Functional Dependency
Weight  ItemNumber
Dimensions  ItemNumber
InsuranceAmount  ItemNumber
Destination  ItemNumber
FinalDeliveryDate  ItemNumber
Instructions  ItemNumber
1. The table has primary key Item Number and there is no repeated group in the table.
2. All fields depend upon the primary key only and there is no partial dependency.
3. No field depend upon any other field and there is no transitive dependency
These mentioned rules are the rules of 3 NF and therefore the table Item is in 3 NF.
(The Crazy Programmer 2019)
(Study.com 2019)

3. Database Implementation
4. Item Form

5. Input Data
Customer Table
RetailStore Table
Receipt Table
Item Table

Employee Table
Transportation Table
6. Queries
Query 1
SELECT TablReceipt.itemNumber, TablReceipt.receivedDate, TablReceipt.charges,
TablRetailCentre.type, TablRetailCentre.address FROM TablRetailCentre INNER JOIN
TablReceipt ON TablRetailCentre.retailCentreID=TablReceipt.retailCentreID;

Query 2
SELECT itemNumber, receivedDate, charges, retailCentreID, employeeID FROM TablReceipt;
Query 3
SELECT TablRetailCentre.retailCentreID, sum(TablReceipt.charges) AS TotalCharges FROM
TablRetailCentre INNER JOIN TablReceipt ON
TablRetailCentre.retailCentreID=TablReceipt.retailCentreID GROUP BY
TablRetailCentre.retailCentreID;

7. Report
Conclusion
The assignment is giving complete knowledge about database design and implementation. All
features of database implementation are being shown in the report like –
- Database queries
- Database report
- Database forms
- Normalization
- Inner joins
- Group by clause
- SQL functions

References
Study.com (2019). What is Normal Form in DBMS? - Types & Examples. Online. Available:
https://study.com/academy/lesson/what-is-normal-form-in-dbms-types-examples.html.
[Accessed: 25-May-2019]
The Crazy Programmer (2019). Normalization in DBMS – 1NF, 2NF, 3NF and BCNF. Online.
Available: https://www.thecrazyprogrammer.com/2017/06/normalization-in-dbms.html.
[Accessed: 25-May-2019]