4.10.3 - Database normalisation to third normal form

Repeated facts and database anomalies

Normalisation organises relations so that facts are stored with the keys that determine them. It reduces unnecessary duplication and the inconsistencies that duplication can cause. It does not mean removing every repeated value: foreign keys must repeat when many records refer to the same entity.

Suppose a college stores a student's name, course title and tutor name on every enrolment row. If twenty students attend the same course, the course's title and tutor details are copied twenty times. This is data redundancy.

• An update anomaly occurs if changing a fact requires several updates and the copies disagree when only some are changed. Renaming a tutor on one enrolment but not the others gives contradictory names for the same tutor.
• An insertion anomaly occurs if a fact cannot be recorded independently. A new course may not fit a table whose key requires a student enrolment, even though its course and tutor are already known.
• A deletion anomaly occurs if deleting one fact accidentally loses another. Removing the last enrolment on a course could also remove the only stored course title and tutor details.

Separate STUDENT, COURSE, TUTOR and ENROLMENT relations can store those facts independently. Normalisation improves consistency and maintainability; it is not a guarantee that all data are correct, and joining several tables may be necessary for a report.

What determines an attribute

A functional dependency is a rule that one set of attribute values determines another attribute's value. Write $X \rightarrow Y$ to mean that every valid record with the same X value must have the same Y value. X is the determinant. This is a rule of the data requirements, not a coincidence in a small sample.

For this college, each student has one recorded name, each course has one title and one tutor, each tutor has one recorded name, and each student can enrol on a course once with one grade. Therefore:

• StudentID determines StudentName.
• CourseID determines CourseTitle and TutorID.
• TutorID determines TutorName.
• (StudentID, CourseID) determines Grade.

The pair (StudentID, CourseID) uniquely identifies an enrolment. StudentID alone cannot determine Grade: the same student may have different grades on different courses. CourseID alone cannot determine Grade: different students on that course may receive different grades. Grade has a full dependency on the combined key.

StudentName depends on only StudentID, a proper subset of that combined key. This is a partial dependency. CourseID determines TutorID and TutorID determines TutorName, so CourseID determines TutorName through TutorID: a transitive dependency.

A non-key attribute here means an attribute that is not part of any candidate key. In the worked relations below, the stated primary key is also the only candidate key. If other candidate keys exist, the normal-form conditions must be checked against them too; choosing a different primary key does not make an underlying dependency disappear.

First normal form removes repeating groups

A relation is in first normal form (1NF) when every cell holds a single atomic value and there are no repeating groups of attributes. Atomic means one value appropriate to the intended domain: a date can be one value even though it has day, month and year. A cell containing a list of courses is not one course value.

An unnormalised student record might contain CourseID values “C1, C2” and matching lists of titles and grades. Alternatively, it might have Course1, Grade1, Course2 and Grade2 columns. Both make the number of courses part of the record's structure and make queries/updates awkward.

Create one row per enrolment and repeat the student identifier where needed:

The primary key is (StudentID, CourseID) under the one-enrolment-per-pair rule. All cells hold one value and the repeating group is removed. The table is now in 1NF, but the course title and tutor name still repeat. Atomic cells alone do not establish 2NF or 3NF.

Second normal form removes partial dependencies

A relation is in second normal form (2NF) if it is in 1NF and every non-key attribute depends on the whole of each candidate key, rather than on a proper subset. For a relation whose only candidate key contains one attribute, a partial-key dependency cannot occur, so 1NF already satisfies this part of the 2NF condition.

In the 1NF enrolment table, StudentName depends only on StudentID. CourseTitle, TutorID and TutorName depend only on CourseID. Move those attributes into relations identified by their respective determinants:

• STUDENT(StudentID [PK], StudentName)
• COURSE(CourseID [PK], CourseTitle, TutorID, TutorName)
• ENROLMENT(StudentID [PK, FK], CourseID [PK, FK], Grade)

ENROLMENT.StudentID references STUDENT; ENROLMENT.CourseID references COURSE. Grade remains in ENROLMENT because both parts of the key are needed to determine it. Keep the key attributes in the link relation: removing them would lose which student earned which grade on which course.

Each relation is now in 2NF under the stated candidate-key assumptions. COURSE still has CourseID determining TutorName through TutorID. Removing partial dependencies has not yet removed that transitive dependency.

Third normal form removes transitive dependencies

For these relations, third normal form (3NF) means 2NF with no non-key attribute depending on the key through another non-key attribute. A non-key fact should describe its relation's key directly, rather than another non-key fact stored alongside it.

COURSE contains CourseID determining TutorID, and TutorID determining TutorName. Separate the tutor fact:

• STUDENT(StudentID [PK], StudentName)
• TUTOR(TutorID [PK], TutorName)
• COURSE(CourseID [PK], CourseTitle, TutorID [FK])
• ENROLMENT(StudentID [PK, FK], CourseID [PK, FK], Grade)

COURSE.TutorID references TUTOR.TutorID. TutorID stays in COURSE to record which tutor teaches the course. TutorName is stored once in TUTOR. A foreign key is not itself a violation of 3NF: COURSE no longer also stores the tutor name that depends on it.

Check the complete design. STUDENT's name depends on StudentID; TUTOR's name depends on TutorID; COURSE's title and tutor identifier depend on CourseID; ENROLMENT's grade depends on the whole student–course key. The relations have atomic cells, no partial dependencies and no relevant transitive non-key dependencies under the stated rules.

The original records can be reconstructed by joining on matching keys. S1/C1 still has Grade A, but its student name, course title and tutor name are obtained from their own relations. This lossless reconstruction matters: a decomposition that loses associations is not a successful normalisation.