Working a full normalisation through

Overview

Complete worked examples taken from a business document to third normal form, showing the key at each stage and naming the dependency removed, with the common mistakes made when normalising by eye.

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

   What should be shown at each stage when normalisation is presented?

   Model answer

   Each stage shows the attributes as they stand at that point, the key of each group underlined, and a statement of what was removed and why: the repeating group, the partial dependency or the transitive dependency.

2. 2

   Normalise to third normal form a cinema booking slip holding booking number, date, customer number, customer name, and for each seat a seat number, row and price band.

   Model answer

   UNF has the repeating seat group. 1NF splits into Booking keyed on BookingNo with the date, CustomerNo and CustomerName, and BookingSeat keyed on BookingNo and SeatNumber with the row and price band. 2NF moves row and price band, which depend on SeatNumber alone, to a Seat table. 3NF moves CustomerName, which depends on CustomerNo, to a Customer table.

3. 3

   Normalise to third normal form a hospital appointment card holding appointment number, patient number, patient name, consultant code, consultant name and clinic.

   Model answer

   There is no repeating group, so it is already in 1NF, and the key is a single attribute, so it is in 2NF. PatientName depends on PatientNo and ConsultantName and Clinic depend on ConsultantCode, both transitive, so Patient and Consultant tables are created and Appointment keeps both codes as foreign keys.

4. 4

   Normalise to third normal form a training record holding course code, course title, and for each attendee a staff number, staff name and department.

   Model answer

   1NF splits into Course keyed on CourseCode with the title, and Attendance keyed on CourseCode and StaffNo. 2NF moves StaffName and Department, which depend on StaffNo alone, to a Staff table. 3NF is then already satisfied unless Department determines something further, such as a department name held separately.

5. 5

   How do you know when the normalisation is finished?

   Model answer

   The normalisation is finished when every attribute in every table can be shown to depend on that table's whole key and on nothing else, which is checked attribute by attribute rather than judged by how the tables look.

6. 6

   What is the most common mistake when normalising by eye?

   Model answer

   Splitting tables because they look large rather than because a dependency requires it. Normalisation is a test on dependencies, so every split must be justified by naming the dependency it removes.

Other ways to work this

The same content, four other ways.

What is inside Levo

Six ways to work the same material.