Achieve Second Normal Form - Exercise

Objective: Use functional dependencies to decompose a relation into 2NF without losing its facts.

Stories on CD: purchasing from distributors

Stories on CD needs to record the CDs it purchases. This exercise uses a simplified purchasing model, distinct from the earlier customer-order scenario. An order may contain several different CDs, and a CD may appear on several orders. A CD appears at most once in any one order, so one row represents one order/CD pairing.

For this exercise only, each CD has one designated distributor. All values are single-valued, required, and drawn from their declared domains. No identifiers or combinations other than the stated keys are guaranteed unique.

PurchaseDetail(CDNo, Title, Price, CategoryNo, CategoryName,
               DistID, OrderNo, OrderDate, OrderCost, Received)
Distributor(DistID, DistName)

Attribute meanings and rules

CDNo identifies a catalog item with one current Title, Price, CategoryNo, and DistID. Price is the current catalog price, not a historical price paid on an order.
CategoryNo determines one CategoryName. Different categories may have the same display name.
OrderNo identifies a purchase order with one OrderDate and one recorded OrderCost. OrderCost is the total for the whole order, not a per-CD amount.
Received records whether that particular CD line has been received. Different lines in the same order may have different statuses.
DistID identifies one distributor name. Distributor names are not assumed unique.

Tasks

State whether PurchaseDetail is in 1NF. Identify its candidate key and explain why neither component alone is sufficient.
Write the functional dependencies, distinguishing partial dependencies from the full-key dependency and the category dependency.
Decompose the design into 2NF. Show each relation, primary key, and foreign key. Account for every original attribute and explain how to reconstruct PurchaseDetail.

Hints

Expect four relations in total, including the existing Distributor relation. Keep the order/CD pairing and its Received value together. A repeated foreign-key value is legitimate; repeating an order's total on every line is a different issue.

Stop at 2NF for this exercise. The category dependency provides a useful preview of why 2NF may still need further normalization.

Use the three-task checklist on the result page for self-assessment. No automatic grade is calculated.

Submit your response

Type or paste your answer, then select Submit to compare it with the model answer. This is self-assessment; submitting does not automatically grade your work.