Download Database Design and Normalization: A Case Study of UPS Company and more Study Guides, Projects, Research Management Information Systems in PDF only on Docsity!
1. Create an Entity Relationship diagram that captures this information about the UPS company. Be certain to indicate identifiers and cardinality constraints.
The "Shipped Items" entity represents the shipped items, with "ItemNumber" as the primary key (PK). It has a one-to-many (1..) relationship with the "Retail Centers" entity, indicated by the line with crow's foot at the "Shipped Items" side. The "Retail Centers" entity represents the retail centers, with "UniqueID" as the primary key (PK). The "Transportation Events" entity represents the transportation events, with "ScheduleNumber" as the primary key (PK). There is a one-to-many (1..) relationship between "Retail Centers" and "Transportation Events," indicated by the line with crow's foot at the "Transportation Events" side.
Shipped Items Retail Centers
Transportation Events
2. Normalize all the relations extracted from the above Entity Relationship diagram in Part 1.
First Normal Form (1NF):
- Shipped Items (ItemNumber, Weight, Dimensions, InsuranceAmount, Destination, FinalDeliveryDate)
- Retail Centers (UniqueID, Type, Address)
- Transportation Events (ScheduleNumber, Type, DeliveryRoute)
Second Normal Form (2NF):
No partial dependencies exist in the relations, so they already satisfy 2NF.
Third Normal Form (3NF):
No transitive dependencies exist in the relations, so they already satisfy 3NF.
The normalized relations are as follows:
Shipped Items (ItemNumber, Weight, Dimensions, InsuranceAmount, Destination, FinalDeliveryDate) Retail Centers (UniqueID, Type, Address) Transportation Events (ScheduleNumber, Type, DeliveryRoute)
- Save the "Retail Centers" table.
- Create the "Transportation Events" table with the following fields: ScheduleNumber (Primary Key): Text data type. Type: Text data type. DeliveryRoute: Text data type.
- Save the "Transportation Events" table.
- Close the table design view.
- Click on the "Database Tools" tab and select "Database Relationships."
- Drag and drop the "ItemNumber" field from the "Shipped Items" table to the "UniqueID" field in the "Retail Centers" table to create a relationship between them.
- Drag and drop the "UniqueID" field from the "Retail Centers" table to the "ScheduleNumber" field in the "Transportation Events" table to create a relationship between them.
- Set the relationship options, such as referential integrity and cascade update/delete, according to your requirements.
- Save the database.
