Diagram Relationships, Participation and Keys - Exercise

Objective: Complete four Crow’s Foot relationships and identify the primary and foreign keys.

Stories on CD business rules

A customer may have zero or many orders; each order belongs to exactly one customer.
This diagram includes draft orders: an order may have zero or many lines, and every line belongs to exactly one order. Submitted orders must have at least one line, a separate lifecycle rule.
Every line identifies exactly one CD title; a CD title may occur on zero or many lines.
Every CD title has exactly one distributor in this simplified supply model; a distributor may currently supply zero or many titles.
At most one line for a given CD title is allowed in an order. LINE ITEM's primary key is the pair (OrderNo, CDNo).

The retained diagram uses SellingPrice and Shipped as line attributes. These do not change the keys or relationship counts requested here.

Five entity boxes with attributes: CUSTOMER, ORDER, LINE ITEM, CD and DISTRIBUTOR. — Five entities for Stories on CD. CustID, OrderNo, CDNo and DistID identify the parent entities.

Read each endpoint as the number of instances at that end related to one instance at the opposite end. A circle means minimum zero, a bar means minimum one, and a crow's foot means maximum many. Double bars mean exactly one. Participation and identifying relationships are different: the latter concern key composition.

Construct A: left zero-many, right exactly-one. B: left one-many, right zero-one. C: left zero-many, right zero-one. D: left zero-one, right zero-many. E: left exactly-one, right zero-many. — Candidate constructs A–E, to be placed left to right.

Four relationships

CUSTOMER:ORDER
ORDER:LINE ITEM
LINE ITEM:CD
CD:DISTRIBUTOR

For each pair in the displayed order, select A–E without reversing the construct, write the rule in both directions using minimum/maximum counts, and identify all PK and FK attributes in each entity. For LINE ITEM, show the complete composite key and both foreign-key targets. An equivalent diagram plus clear text is acceptable.

VENDOR:PRODUCT — construct E
One VENDOR has 0..many PRODUCT instances.
One PRODUCT has 1..1 VENDOR.
VENDOR: VendID = PK
PRODUCT: ProdID = PK; VendID = FK to VENDOR.VendID

The example assumes optional vendor participation and mandatory product participation. When naming participation, say whose instances must participate; do not infer it just from an entity's location on the page.

Self-assessment: 60 points

For each of four relationships, award up to 5 points for the correct construct and consistent bidirectional counts, 5 for the first entity’s complete key annotations, and 5 for the second entity’s complete key annotations. Total: 60 points. CPK/FK means an attribute is a component of the composite primary key and also a foreign key; it is not a complete primary key by itself. Compare your work with the model answer; this page does not automatically grade free text or award course credit.

Submit your response

Enter your answer, then select Submit to view it alongside the model answer.

Your answers