3

I've been looking at examples of ER diagrams to better understand them. I came across an ER diagram that I am not sure is correct.

Here is the question/specification:

UPS prides itself on having up-to-date information on the processing and current location of each shipped item. To do this, UPS relies on a company-wide information system. Shipped items are the heart of the UPS product tracking information system. Shipped items can be characterized by item number (unique), weight, dimensions, insurance amount, destination, and final delivery date. Shipped items are received into the UPS system at a single retail center. Retail centers are characterized by their type, uniqueID, and address. Shipped items make their way to their destination via one or more standard UPS transportation events (i.e., flights, truck deliveries). These transportation events are characterized by a unique scheduleNumber, a type (e.g., flight, truck), and a deliveryRoute.

Entities: RetailCenter, ShippedItems, Transportation Event
Relationships: ReceivedFrom(RetailCenter,ShippedItems), ShippedVia(ShippedItems,TransportationEvent)

Here is the diagram: er diagram

My questions is that shouldn't there be a ternary relationship between the three entities? My thought process is that a shipped item takes a transportation event to reach a specific retail center. Doesn't this diagram say that a shipped item is received by a retail center and that a shipped item takes a transportation event?

philipxy
  • 14,867
  • 6
  • 39
  • 83
name
  • 419
  • 3
  • 14

1 Answers1

1

My thought process is that a shipped item takes a transportation event to reach a specific retail center.

You seem to be misreading the spec. An item is brought to a UPS retail center and is then shipped to a destination. But let's consider the ternary relationship that a shipped item takes a transportation event to reach a specific destination.

That is one of many conceivable relationships on those three entities.

Doesn't this diagram say that a shipped item is received by a retail center and that a shipped item takes a transportation event?

Yes, it does. But the ternary relationship is expressible in terms of these diagram binary relationships. (And not vice-versa.)

Every table--base variable or query result--holds rows that participate in some particular relationship. We can characterize the relationship by a predicate--a statement template parameterized by attributes.

A table holds the rows whose values for attributes makes a true statement from its predicate. The predicate of a base variable is given by the DBA.

-- shipped item ItemNumber is received by retail center UniqueId
SELECT * FROM ReceivedFrom
-- shipped item ItemNumber takes transportation event ScheduleNumber
SELECT * FROM ShippedVia

The predicate of a query expression is built from its operators and arguments. Eg the predicate of the NATURAL JOIN of two tables is the AND of the tables' predicates.

-- shipped item ItemNumber is received by retail center UniqueId
       and takes transportation event ScheduleNumber
SELECT * FROM ReceivedFrom NATURAL JOIN ShippedVia

Of course, your particular conception of the ternary relationship might not be this exact query/table. But a practical UPS database would have tables for the fundamental relationships in terms of which any relevant relationship can be expressed.

(Normalization splits predicates of the form "... AND ..." into separate predicates for the "..."s when that is possible and helpful; the original table is given back by the JOIN of the components.)

philipxy
  • 14,867
  • 6
  • 39
  • 83