Database Management System Project: International Parcel Service

Desklib provides solved assignments and past papers to help students succeed.

Contents
Introduction................................................................................................................................2
1. ERD........................................................................................................................................3
2. Dependency Diagram.............................................................................................................4
3. Database using Ms Access.....................................................................................................4
4. ITEMS form...........................................................................................................................6
5. Data in the tables....................................................................................................................7
Conclusion................................................................................................................................12
References................................................................................................................................13
List of Figures
Figure 1: ERD............................................................................................................................3
Figure 2: Dependency Diagram.................................................................................................4
Figure 2: Customer table............................................................................................................4
Figure 3: Employee table...........................................................................................................5
Figure 4: RetailCentre table.......................................................................................................5
Figure 5: Receipt table...............................................................................................................5
Figure 6: ShippingItems table....................................................................................................5
Figure 7: Transportation table....................................................................................................6
Figure 8: ShippingItems Form...................................................................................................6
Figure 9: Customer data.............................................................................................................7
Figure 10: Employee data..........................................................................................................7
Figure 11: Rc data......................................................................................................................7
Figure 12: Receipt data..............................................................................................................7
Figure 13: ShippingItems data...................................................................................................8
Figure 14: Transportation data...................................................................................................8
Figure 3: Query 1.......................................................................................................................9
Figure 4: Output.........................................................................................................................9
Figure 5: Query 2.......................................................................................................................9
Figure 6: Output.......................................................................................................................10
Figure 7: Query 3.....................................................................................................................10
Figure 8: Output.......................................................................................................................10
Figure 9: Report.......................................................................................................................11

Introduction
The report is based on the database management system of the scenario that is given and is
based on the company that works for the international parcel services that work for shipping
the parcel internationally. This report consists of the entity relationship diagram, dependency
diagram and database is created on Ms. Access. Along with these, there are various forms
also that are created and attached in this report.

1. ERD
Figure 1: ERD
This is the entity relationship diagram of the given case study in which various entities, as
well as attributes, are present and these entities are interconnected with each other with many
relationships. The tables that are present are Customer, ShippingItems, RetailCentre,
Employee, Transportation, and Receipt. These entities have various constraints like primary
as well as foreign keys.
 Customer table has four different attributes in which the CustomeId is the primary key
and this table is connected with ShippingItems as well as Receipt entity.
 Receipt table that has three different attributes and is connected with the Customer table.
 Table ShippingItems has eight different attributes in which the ItemId is the primary key
and CustomerId and Schedule Number is the foreign key present in this particular entity.
 Transportation is another table that has three different attributes out of which
ScheduleNumber is the primary key. This table is connected with ShippingItems.
 RetailCentre table stores the details of the center and has the primary key RC_Id and
composed of overall four different attributes. This table is connected with ShippingItems
as well as Employees.
 The last table is Employees that has five different attributes in which employee_id is the
primary key and RC_id is the foreign key as it is taking reference from RetailCentre table.
This table has only one relationship with RetailCentre table.

2. Dependency Diagram
Figure 2: Dependency Diagram
The above-shown diagram is the dependency diagram and that is based on the ERD created
above. The entire ERD is completely normalized and present in the third normal form.
3. Database using Ms. Access
Here is the database of the given scenario. The database stores the details of every table that
is created and screenshots are given here:
Table Customer:
Figure 3: Customer table

Table Employee:
Figure 4: Employee table
Table RetailCentre:
Figure 5: RetailCentre table
Table Receipt:
Figure 6: Receipt table
Table ShippingItems:
Figure 7: ShippingItems table

Table Transportation:
Figure 8: Transportation table
All these were the screenshots of the database that is created and displayed with the help of
the screenshots.
4. ITEMS form
A form is created by using Ms. Access and the screenshot is provided here:
Figure 9: ShippingItems Form
Data in each table is given in the form of screenshot further:

5. Data in the tables
Customer:
Figure 10: Customer data
Employee:
Figure 11: Employee data
RetailCentre:
Figure 12: Rc data
Receipt:
Figure 13: Receipt data

ShippingItems:
Figure 14: ShippingItems data
Transportation:
Figure 15: Transportation data

6. Queries
Select Query
Figure 16: Query 1
Figure 17: Output
In the above-mentioned screenshots, the data of the table customer is extracted out where the
named starts with the letter D.
Join Query
Figure 18: Query 2

Figure 19: Output
Here, the join query between the tables named as Employee and RetailCentre is shown in
order to extract the data from both the tables at a single time.
Numeric Query
Figure 20: Query 3
Figure 21: Output
In the above-shown screenshots, the numeric query is applied to fetch the aggregated data.

7. Report
Figure 22: Report
The above-created report is based on Query 2 where the join query is implemented between
two of the tables named as Employee and the Retail Centre.

Conclusion
The conclusion of the above report is that the database on Ms. Access is created successfully
and the ER diagrams, as well as the dependency diagram, are also created successfully.
Moreover, the screenshots of the work that is performed are attached along with the file with
the description so as to provide a better understanding of the task that is performed.