Database design and normalisation techniques

Overview

Recall questions on why databases are normalised, the conditions for first, second and third normal form, functional dependency, and normalising a given table.

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?

   Model answer

   Normalisation is the process of organising the attributes of a database into tables so that each item of data is stored once, in the table where it belongs, and so that the structure supports the required queries without redundancy. It is carried out in stages, each removing a particular kind of dependency, and it proceeds by splitting tables and introducing keys until the relations satisfy the conditions of the normal form being aimed at, which at this level is third normal form.

2. 2

   Understand why databases are normalised. Give the reasons.

   Model answer

   To remove redundancy, so that no item of data is stored more than once. This saves storage, but far more importantly it removes the possibility of the copies disagreeing, so the data stays consistent. It removes update anomalies, where changing a value means altering many rows and missing one leaves the database contradicting itself. It removes insertion anomalies, where a fact cannot be recorded because some unrelated data is not yet known. It removes deletion anomalies, where deleting one record accidentally destroys the only copy of an unrelated fact. Updates are also faster, because one row is written rather than many, and the resulting structure is more flexible: new queries and new tables can usually be added without restructuring what is already there.

3. 3

   A single table holds StudentID, StudentName, TutorID, TutorName and TutorRoom, with one row per student. Identify the update, insertion and deletion anomalies it suffers.

   Model answer

   Update anomaly: a tutor's room is repeated in the row of every student they tutor, so a room change means editing many rows, and any row missed leaves two different rooms recorded for the same tutor. Insertion anomaly: a newly appointed tutor cannot be recorded at all until they have at least one student, because there is no row to put them in without a StudentID. Deletion anomaly: if a tutor's last student leaves and that row is deleted, all record of the tutor, their name and their room disappears with it. All three arise from the same cause — tutor data being stored in a table whose subject is students.

4. 4

   What conditions must a relation satisfy to be in first normal form?

   Model answer

   It must contain no repeating groups, and every attribute value must be atomic — a single indivisible item in every cell. In practice this means no attribute holds a list, and no set of attributes such as Item1, Item2, Item3 repeats the same idea across several columns. Repeating groups are removed by taking them out into a separate relation, whose primary key includes the key of the original relation.

5. 5

   What must be true for a relation to be in second normal form?

   Model answer

   It must already be in first normal form, and it must contain no partial dependencies — no non-key attribute may depend on only part of a composite primary key; every non-key attribute must depend on the whole of the primary key. The question only arises where the primary key is composite, because a single-attribute key has no parts, so a relation in first normal form with a single-attribute primary key is automatically in second normal form.

6. 6

   What must be true for a relation to be in third normal form?

   Model answer

   It must already be in second normal form, and it must contain no transitive dependencies — no non-key attribute may depend on another non-key attribute. Every non-key attribute must be determined by the primary key directly, and not by way of some other ordinary attribute.

Other ways to work this

The same content, four other ways.

What is inside Levo

Six ways to work the same material.