Database design and normalisation techniques revision notes

Purpose of normalisation

Normalisation is the process of organising database relations so that each fact is stored in an appropriate place and unnecessary duplication is reduced. A database is normalised to improve its design and to reduce the risk that the same fact will have to be changed in several different records.

Functional dependencies

A functional dependency exists when one attribute, or a group of attributes, determines another attribute. For example, StudentID determines StudentName: a particular student identifier should identify one student name. A key is an attribute, or combination of attributes, that identifies a record. A composite key contains more than one attribute.

Third normal form

A relation in third normal form (3NF) has the properties required by the preceding normalisation stages and has no non-key attribute that depends on another non-key attribute. In other words, every non-key attribute should depend on the key, the whole key, and nothing but the key. This prevents a non-key attribute from determining another non-key attribute.

Worked reasoning

Suppose a relation contains:

StudentID, StudentName, CourseID, CourseName, Grade

If an enrolment is identified by the composite key (StudentID, CourseID), Grade depends on the whole key. However, StudentName depends only on StudentID, and CourseName depends only on CourseID. These are not dependencies on the whole composite key. A suitable decomposition is:

• Student(StudentID, StudentName)
• Course(CourseID, CourseName)
• Enrolment(StudentID, CourseID, Grade)

The relations separate student facts, course facts and enrolment facts. The identifiers connect the relations, while each non-key attribute is stored with the key that determines it.

Common errors

• Assuming that a relation is in 3NF merely because it has a primary key.
• Leaving an attribute that depends on only part of a composite key in the same relation.
• Leaving a dependency between two non-key attributes.
• Splitting relations without retaining the attributes needed to identify and connect their records.
• Saying only that normalisation removes duplication, without explaining that it organises dependencies and reduces repeated facts.