Restructuring data into third normal form

Overview

Recall questions on the key, the whole key and nothing but the key, worked repairs of a partial and a transitive dependency, removing a repeating group, resolving a many-to-many with a link table, derived values, and why the work is done on paper first.

What you need to know

Answer each one before you reveal it.

Questions from this subtopic

Each one with the model answer underneath.

1. 1

   State the mantra summarising third normal form.

   Model answer

   Every non-key attribute depends on the key, the whole key, and nothing but the key.

2. 2

   A table holds OrderID, ProductID, Quantity, ProductName and ProductPrice, keyed on OrderID and ProductID. Which normal form does it fail?

   Model answer

   Second, because ProductName and ProductPrice depend on ProductID alone, which is only part of the key.

3. 3

   A table holds StudentID, StudentName, TutorID and TutorRoom, keyed on StudentID. Which normal form does it fail?

   Model answer

   Third, because TutorRoom depends on TutorID, which is not the key.

4. 4

   How is a repeating group removed to reach first normal form?

   Model answer

   Move the repeating attributes into a separate table whose key includes the original key plus whatever distinguishes the repeats.

5. 5

   A library table lists BookID and up to three BorrowerIDs in one row. Put it into first normal form.

   Model answer

   Replace the three columns with a Loan table holding one row per loan, keyed on BookID and BorrowerID or on a loan number.

6. 6

   Why must a many-to-many relationship be resolved before normalisation is complete?

   Model answer

   Neither table can hold the other's key without repeating, so a link table with both foreign keys as its composite key is required.

Other ways to work this

The same content, four other ways.

What is inside Levo

Six ways to work the same material.