IB Computer Science Topic A3.2.6: Constructing a Database Normalized to 3NF for Real-world Scenarios Notes

Normalizing a Real Scenario to 3NF: A Method You Can Repeat

1. This statement asks you to Construct a normalized design, so the work here is applying the normal forms and their dependency rules rather than defining them again.
2. The scenario always arrives as one flat table or a description of a paper form, and your job is to end up with a set of tables and keys.
3. The method below works on any scenario, and the two worked cases show it applied to different shapes of problem.

The Procedure, Step by Step

1. Write out the unnormalized table
   1. List every attribute the scenario mentions, with two or three sample rows, because the repeated values in the sample data are what reveal the dependencies.
2. Split any cell holding more than one value
   1. Move the repeating group into its own entity and give it a foreign key back to the original, which reaches 1NF.
3. Write down the primary key you are left with
   1. Use the natural composite key at this stage rather than inventing an ID, because a composite key is what makes a partial dependency visible.
4. Test every non-key attribute against the whole key
   1. Ask "do I need all of the key to know this value?" and move out anything that answers no, which reaches 2NF.
5. Test every non-key attribute against the other non-key attributes
   1. Ask "is this value really determined by another ordinary column?" and move out anything that answers yes, which reaches 3NF.
6. Re-check every table you created along the way
   1. A table produced at the 2NF step can itself contain a transitive dependency, and that is the step most often skipped.

Worked Scenario: A Hospital Appointment System

A clinic records appointments on a single spreadsheet, and each row holds the patient, the doctor, the doctor's department and the tests ordered.

Unnormalized Form

First Normal Form: Take the Repeating Tests Out

1. TestsOrdered holds a list, so it is not atomic and it becomes its own entity.
2. An appointment is identified naturally by which patient came at which date and time, so that triple is the key of what remains.
3. Appointment (PK PatientID + ApptDate + ApptTime)

AppointmentTest (PK PatientID + ApptDate + ApptTime + TestName, all three date and patient columns FK)

Second Normal Form: The Patient Data Depends on Part of the Key

1. PatientName and PatientPhone are determined by PatientID alone, so they depend on part of the composite key.
2. The evidence is visible in the sample rows: Maya Okafor's phone number is written twice.
3. Patient (PK PatientID)

Appointment (PK PatientID + ApptDate + ApptTime, FK PatientID)

Third Normal Form: Two Transitive Dependencies, One Inside the Other

1. DoctorName, Specialism, DeptName and DeptHead are all determined by DoctorID, and DoctorID is not part of the key, so this is a transitive dependency.
2. Moving them into a Doctor entity leaves a second transitive dependency inside that new table, because DeptHead is determined by DeptName rather than by DoctorID.
3. Both have to be removed, which is why step six of the procedure matters.
4. Department (PK DeptID)

Doctor (PK DoctorID, FK DeptID)

Appointment (PK PatientID + ApptDate + ApptTime, FK PatientID, FK DoctorID)

The Finished 3NF Design

Second Scenario: A Library Lending System

1. The library's flat loan sheet holds MemberName, MemberEmail, BookTitle, AuthorName, ISBN, CopyBarcode, DateBorrowed and DateDue.
2. The modelling decision that carries this scenario is the difference between a title and a physical copy.
3. A loan is of one copy, and the library may hold six copies of the same ISBN, so a design that lends a title cannot say which book is on the shelf.
4. AuthorName is determined by ISBN and not by the loan, so it is a transitive dependency and moves out at 3NF.

A book with two authors would make Book to Author many-to-many, which needs a linking table rather than a single AuthorID column.

Other Scenarios and the Entities They Usually Need

The same three questions produce these entity sets, and each of these scenarios is named in the syllabus.

1. The e-commerce case is worth reading twice, because keeping the price paid on the order line is a deliberate, justified exception rather than a normalization error.
2. A historical price is a different fact from the current price, and choosing to store it is closer to a denormalization decision than to a dependency you have missed.