Question

FedEx wants to develop a company-wide information system. Shipped items are the heart of the FedEx product tracking information system. Shipped items can be characterized by item number, weight, dimensions, insurance amount, destination city, and final delivery date. Shipped items are received into the FedEx system at a single retail center. There are multiple retail centers in big cities such as LA, NY, etc. Each of the retail centers has one of the four types: FedEx office, FedEx Ship Center, Office Depot, and FedEx Authorized Ship Center. Shipped items make their way to their destination via one or more standard UPS transportation events (ex. Flights, ships, truck deliveries). These transportation events are characterized by schedule, a type (e.g. flight, truck, ship), and a delivery route.

1. Please create an Entity-Relationship diagram that captures this information about the FedEx system.
- Please identify primary keys, foreign keys, and cardinality constraints.
- Please solve many-to-many relationship problems if you have any here.
- If you have any assumptions, please identify those assumptions (User-defined constraints).
2. After you finish your ERD, please create ER Data Model with ten records in each of the all entities (Be careful, some of the information has been provided already in the text – e.g. retail center type: FedEx office, FedEx Ship Center, Office Depot, and FedEx Authorized Ship Center).
3. Create Relational Schema for this ERD.

Answer 1

Entity-Relationship Diagram (ERD):
The ERD for the FedEx system can have the following entities and relationships:

- Entity: Shipped Items (Primary Key: Item Number)
Characteristics: Weight, Dimensions, Insurance Amount, Destination City, Final Delivery Date
Cardinality: One-to-Many relationship with Retail Center through Received At

- Entity: Retail Center (Primary Key: Center ID)
Characteristics: Center Type (FedEx Office, FedEx Ship Center, Office Depot, FedEx Authorized Ship Center)
Cardinality: One-to-Many relationship with Shipped Items through Received At, Many-to-One relationship with Transportation Event through Departed From

- Entity: Transportation Event (Primary Key: Event ID)
Characteristics: Schedule, Type (Flight, Truck, Ship), Delivery Route
Cardinality: Many-to-Many relationship with Shipped Items through Transported By, Many-to-One relationship with Retail Center through Departed From

Assumptions:
- Each shipped item can only be received at one retail center.
- Each retail center can be of only one center type.
- Each transportation event can transport multiple shipped items.

ER Data Model:
- Shipped Items (Item Number, Weight, Dimensions, Insurance Amount, Destination City, Final Delivery Date, Received At)
- Retail Center (Center ID, Center Type)
- Transportation Event (Event ID, Schedule, Type, Delivery Route, Departed From)
*Note: foreign keys are identified as attribute names in parentheses next to the table name.

Relational Schema:
- Shipped Items(ItemNumber, Weight, Dimensions, InsuranceAmount, DestinationCity, FinalDeliveryDate, RetailCenterID)
- Retail Center(CenterID, CenterType)
- Transportation Event(EventID, Schedule, Type, DeliveryRoute, DepartedFromCenterID)
- Transported By(EventID, ItemNumber)
*Note: Primary Keys are underlined.