CODEWITHSUNDEEP
databaseerd
Yeezus
Yeezus

Reputation: 402

Create an ERD Diagram from a case study

The case study is:

Maps systems is a company that specialises in shipping and intends to have up-to-date information on the processing and current location of each shipped item. To do this, Maps systems relies on a company-wide information system. Shipped items are the core of maps product tracking information system. Shipped items are
characterised by item number (unique), weight, dimensions, insurance
amount, destination, and final delivery date. Shipped items are received into the Maps system at a single retail center. Retail centers are characterised by their type, uniqueID, and address. Shipped items make their way to their destination via one or more standard Maps transportation events (i.e., flights, truck deliveries).These transportation events are characterised by a unique scheduleNumber, a type (e.g., flight, truck), and a deliveryRoute.

What I managed to complete:

shipped_items                         retail_centre     
item_number (Pk)                      retail_id (PK)          
weight                      N:1       retail_type            
dimensions              ---------     address                
insurance_amount
destination
final_delivery_date                         |
        |                                   |
        |                                   |
        |                                   |
        |                                   |
        |                                   |
        |              transport_event      |
        |              schedule_id          |
         ------------  transport_type-------
      N:M              address              1:N

However they lecture say we aren't allowed to have many to many relationships and thats when I begin to stumble. I thought about adding an order_item attribute with the entities item_number, schedule_id, and address. Please Help!

Upvotes: -1

Views: 1231

Answers (1)

Nelson Achelengwa
Nelson Achelengwa

Reputation: 1

Nice try. First of all I think there shouldn't be any relationship between transport_event and retail_centre according to the business case. You can convert a many-to-many(N:M) relationship to two one-to-many (1:N) by creating a bridge entity between the two. This bridge entity say "path" for example will inherit the primary keys of the participating entities(shipping_item,transportation_event) in the relationship as foreign keys.

You can have path(pathID,item_number,scheduleNumber,origin,destination)

The italicized attributes are the foreign keys. So path will be the many side of both relationships as it can combine any two shipped items and transportation events more than once.

Hope it helps.

Upvotes: 0

Related Questions