Database System of International Parcel Service (IPS)
Verified
Added on Β 2022/11/17
|9
|573
|288
AI Summary
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.
Contribute Materials
Your contribution can guide someoneβs learning journey. Share your
documents today.
Student ID: Student Name: Date:
Secure Best Marks with AI Grader
Need help grading? Try our AI Grader for instant feedback on your assignments.
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 KeyFlCustomerID TabItem(FlitemNumber, Flweight, Fldimensions, FlinsuranceAmount, Fldestination, FlfinalDeliveryDate, Flinstructions) Primary KeyFlitemNumber TabRetailCentre(FlretailCentreID, Fltype, Fladdress) Primary KeyFlretailCentreID TabEmployee(FlemployeeID, FlemployeeName, Fladdress, Flphone, Flemail, FlretailCentreID) Primary KeyFlemployeeID FlretailCentreID references Tabretail_Centre (FlretailCentreID) TabReceipt(FlitemNumber, FlreceivedDate, Flcharges, FlretailCentreID, FlemployeeID) Primary KeyFlitemNumber FlretailCentreID references Tabretail_Centre (FlretailCentreID) FlemployeeID references TabEmployee (FlemployeeID) FlitemNumber references TabItem (FlitemNumber) TabTransportation(FlscheduleNumber, FlcustomerID, Fltype, FldeliveryRoute, FlitemNumber) Primary KeyFlscheduleNumber 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)
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
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-
Secure Best Marks with AI Grader
Need help grading? Try our AI Grader for instant feedback on your assignments.
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]