Normalisation to 3NF

Overview

Recall questions on normalisation and its three normal forms, repeating groups and atomic values, partial and transitive dependencies, converting tables to 2NF and 3NF, derived fields, and the benefits and costs of normalising.

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 is normalisation in databases?

   Model answer

   Normalisation is the process of organising the fields and tables of a relational database so that redundancy is removed and the data is held in a structure that avoids anomalies. It is done in stages, each called a normal form, with each stage removing a particular kind of problem, and it typically results in data being split across more, smaller tables linked by keys.

2. 2

   A student confuses normalisation in databases with normalisation of floating point numbers. What is the difference?

   Model answer

   They are unrelated processes that happen to share a word. Database normalisation restructures tables to remove redundancy and dependency problems. Floating point normalisation adjusts a number's mantissa and exponent to a standard form so that the greatest possible precision is retained for the bits available. Neither has any bearing on the other.

3. 3

   State the rule for first normal form.

   Model answer

   A table is in first normal form if it contains no repeating groups and every field holds a single atomic value, with each record uniquely identifiable by a primary key. In other words, no cell may hold a list, and no set of columns may repeat the same kind of data across several columns.

4. 4

   This table is not in 1NF: Student(StudentID, Name, Subject1, Subject2, Subject3). Explain what is wrong and give the corrected design.

   Model answer

   Subject1, Subject2 and Subject3 are a repeating group: the same kind of data spread over several columns. It wastes space for students taking one subject, it caps every student at three subjects, and searching for everyone taking Physics means checking three separate columns. The fix is to remove the repeating group into its own table with a row per subject taken: Student(StudentID, Name) and StudentSubject(StudentID, Subject), where StudentID and Subject together form the primary key of the second table and StudentID is a foreign key to the first.

5. 5

   A field called Address holds '14 Mill Lane, Bristol, BS1 4AA'. Why does this breach 1NF and what should be done?

   Model answer

   The value is not atomic: three separate items of data, street, town and postcode, are packed into one field. That makes it impossible to search or sort reliably on town or postcode alone, and validation of the postcode cannot be applied. It should be split into separate Street, Town and Postcode fields, each holding a single value.

6. 6

   State the rule for second normal form.

   Model answer

   A table is in second normal form if it is already in first normal form and contains no partial dependencies, meaning no non-key attribute depends on only part of a composite primary key rather than on the whole of it. A table in 1NF whose primary key is a single field is automatically in 2NF, because there is no part of the key to be partially dependent on.

Other ways to work this

The same content, four other ways.

What is inside Levo

Six ways to work the same material.