Achieve Third Normal Form - Exercise

Self-Assessment Rubric: 18 Points

For each of the three scenarios, award up to 6 points: 1 for the key and relevant dependencies, 1 for a justified classification, 2 for appropriate final relations and keys, 1 for references or calculation placement, and 1 for explaining preservation of information. Equivalent designs earn credit when they preserve the stated rules.

Compare your answer with the model solution after submitting. The website does not assign points automatically.

Scenario and Prerequisites

Stories on CD maintains a media catalog, internal reports, and sales orders. Review achieving 3NF before starting. Use all candidate keys and the stated dependencies, not assumptions inferred from attribute names. PK means primary key and FK means foreign key; the relations below are schema notation, not executable SQL.

Three Tasks

For each scenario, state its key and dependencies, assess its normalization problem, propose 3NF relations with keys and references, and explain how the original information is recovered. No dependencies other than those stated or implied below are assumed.

1. Music Catalog

MusicCD(MusicID, Title, Artist, GenreName, GenreContact)

MusicID is the only candidate key. Each release has one display artist and one genre. GenreName uniquely identifies a genre in this simplified catalog and determines one current GenreContact. Many releases share a genre; neither artist nor contact is unique. Every value is scalar, and all values are required.

2. Report Authors and Departments

Report(ReportID, Title, Author1, Author2,
       DepartmentName, DepartmentContact)

ReportID identifies a report. Author1 and Author2 are legacy slots for the same author list, not distinct roles. A report can have any number of authors, an author can contribute to many reports, and author order is not significant. Names need not be unique, so use AuthorID to identify people when converting these slots. Each report belongs to one department. DepartmentName uniquely identifies a department in this example and determines its current contact.

Explain why fixed author slots are unsuitable for the required relationship. Acknowledge that scalar Author1 and Author2 columns alone do not prove a formal 1NF violation; assess the repeating-list design and department dependency separately. Do not invent missing author identities from names alone.

3. Order-Line Calculation

OrderItem(OrderNo, ItemNo, Quantity, Discount,
          SellingPrice, LineCost, Shipped)

The only candidate key is (OrderNo, ItemNo): an item occurs at most once in an order. Quantity, Discount, SellingPrice, and Shipped describe the complete order-item pair and do not depend on either key component alone. SellingPrice is the agreed unit price for that sale, not the current catalog price. Discount is an amount per unit. Quantity is positive; 0 <= Discount <= SellingPrice. Values are required and use exact arithmetic for this exercise.

LineCost is always calculated as Quantity * (SellingPrice - Discount). The same quantity, discount, and price can occur on different order lines. Identify the resulting dependency and explain whether LineCost should be stored in the base relation or calculated when queried. Preserve the agreed sale price.

Enter your reasoning and proposed relations, then select Submit to compare your work with the model answer. This is self-assessment; no automatic grade is calculated.