Normalisation and Normal Forms – Database Design (Senior 4 ICT) | Ganzaa

Database Design

Lessons

Senior 4 · ICT

Database Design

5 lessons

Lesson 5 of 5

Normalisation and Normal Forms

25 min

A mark sheet that fights back

A teacher keeps the term's marks in one big table: every row repeats the student's name and class and the subject's name and teacher. It looks complete. Then a typing mistake in a teacher's name has to be corrected in four rows, and one is missed; a new subject cannot be entered until somebody has a mark in it; and when a student leaves, the only record that Physics is taught by J. Habimana leaves with him. These problems are not bad luck. They come from the design of the table, and a systematic method called normalisation removes them.

Definition

Normalisation: the process of organising the attributes of a database into relations so that each fact is stored once, by removing redundancy and the anomalies it causes. It splits large relations into smaller ones linked by keys.

Anomaly: a problem caused by redundancy. An update anomaly: one fact stored in several rows must be changed in all of them. An insertion anomaly: a fact cannot be recorded without an unrelated fact. A deletion anomaly: deleting one fact destroys another.

Functional dependency, written $A \rightarrow B$ : the value of A determines the value of B; whenever two rows have the same A, they have the same B. Example: StudentID → StudentName.

Normal form: a set of conditions a relation meets. Each normal form includes the ones before it: 1NF, then 2NF, then 3NF.

Unnormalised form (UNF)

Here is the teacher's data as first written, one row per student, with lists inside cells (all names and marks are illustrative; each subject is taught by one teacher):

StudentID	StudentName	Class	Subjects	Teachers	Marks
1001	Aline Uwase	S4A	MAT Mathematics, ICT ICT	T01 J. Habimana, T02 C. Mukamana	72, 85
1002	Eric Mugisha	S4B	MAT Mathematics, PHY Physics	T01 J. Habimana, T01 J. Habimana	64, 58
1003	Grace Ingabire	S4A	ICT ICT, MAT Mathematics	T02 C. Mukamana, T01 J. Habimana	91, 77

This is not a relation: cells hold several values (a repeating group), so a query such as "all Physics marks" means picking lists apart.

First normal form (1NF)

Rule: every cell holds a single, atomic value, there are no repeating groups, and each row is identified by a primary key.

Method: give each value of the repeating group its own row and copy the other values into it; choose a key. One student now has several rows, so StudentID alone is not unique, but StudentID together with SubjectCode is: the key is composite.

StudentID	SubjectCode	StudentName	Class	SubjectName	TeacherID	TeacherName	Mark
1001	MAT	Aline Uwase	S4A	Mathematics	T01	J. Habimana	72
1001	ICT	Aline Uwase	S4A	ICT	T02	C. Mukamana	85
1002	MAT	Eric Mugisha	S4B	Mathematics	T01	J. Habimana	64
1002	PHY	Eric Mugisha	S4B	Physics	T01	J. Habimana	58
1003	ICT	Grace Ingabire	S4A	ICT	T02	C. Mukamana	91
1003	MAT	Grace Ingabire	S4A	Mathematics	T01	J. Habimana	77

The table is in 1NF, but look at the redundancy: "J. Habimana" appears 4 times, "Aline Uwase" twice, "Mathematics" 3 times. All three anomalies are present.

Finding the dependencies

Normalisation beyond 1NF is guided by the functional dependencies, which come from the school's rules: a student has one name and one class; a subject has one name and one teacher; a teacher has one name; a mark belongs to one student in one subject.

A full dependency needs the whole key: (StudentID, SubjectCode) → Mark.
A partial dependency needs only part of a composite key: StudentID → StudentName, Class and SubjectCode → SubjectName, TeacherID.
A transitive dependency runs through a non-key attribute: SubjectCode → TeacherID → TeacherName.

Second normal form (2NF)

Rule: the relation is in 1NF and every non-key attribute depends on the whole primary key: there are no partial dependencies. (A relation whose key is a single attribute is automatically in 2NF once it is in 1NF.)

Method: move each group of partially dependent attributes into a new relation, together with the part of the key it depends on. That part becomes the new relation's primary key and stays behind as a foreign key.

Student(StudentID, StudentName, Class)
Subject(SubjectCode, SubjectName, TeacherID, TeacherName)
Result(StudentID*, SubjectCode*, Mark)

Each student's name is now stored once, and a subject can be added before anyone has a mark in it. But Subject still repeats the teacher's name for every subject she teaches.

Third normal form (3NF)

Rule: the relation is in 2NF and no non-key attribute depends on another non-key attribute: there are no transitive dependencies.

Method: move the transitively dependent attributes into a new relation whose key is the attribute they depend on; leave that attribute behind as a foreign key.

Teacher(TeacherID, TeacherName)
Subject(SubjectCode, SubjectName, TeacherID*)

With Student and Result unchanged, the design is in 3NF: four relations, and every fact is stored exactly once.

William Kent summed up 3NF in one sentence: every non-key attribute must provide a fact about the key, the whole key, and nothing but the key. "The key" is 1NF (rows are identified by a key), "the whole key" is 2NF, "nothing but the key" is 3NF. A stricter form, Boyce–Codd normal form (BCNF, 1974), deals with rare cases that have several overlapping candidate keys.

sql

PRAGMA foreign_keys = ON;

-- The marks data in third normal form: each fact stored once
CREATE TABLE Teacher (
  TeacherID   TEXT NOT NULL PRIMARY KEY,
  TeacherName TEXT NOT NULL
);
CREATE TABLE Subject (
  SubjectCode TEXT NOT NULL PRIMARY KEY,
  SubjectName TEXT NOT NULL,
  TeacherID   TEXT NOT NULL REFERENCES Teacher(TeacherID)
);
CREATE TABLE Student (
  StudentID   INTEGER PRIMARY KEY,
  StudentName TEXT NOT NULL,
  Class       TEXT NOT NULL
);
CREATE TABLE Result (
  StudentID   INTEGER NOT NULL REFERENCES Student(StudentID),
  SubjectCode TEXT    NOT NULL REFERENCES Subject(SubjectCode),
  Mark        INTEGER CHECK (Mark BETWEEN 0 AND 100),
  PRIMARY KEY (StudentID, SubjectCode)
);

INSERT INTO Teacher VALUES ('T01', 'J. Habimna'), ('T02', 'C. Mukamana');   -- a typing mistake
INSERT INTO Subject VALUES ('MAT', 'Mathematics', 'T01'), ('PHY', 'Physics', 'T01'), ('ICT', 'ICT', 'T02');
INSERT INTO Student VALUES (1001, 'Aline Uwase', 'S4A'), (1002, 'Eric Mugisha', 'S4B'), (1003, 'Grace Ingabire', 'S4A');
INSERT INTO Result VALUES
  (1001, 'MAT', 72), (1001, 'ICT', 85),
  (1002, 'MAT', 64), (1002, 'PHY', 58),
  (1003, 'ICT', 91), (1003, 'MAT', 77);

-- Correct the teacher's name: ONE row to update, not four
UPDATE Teacher SET TeacherName = 'J. Habimana' WHERE TeacherID = 'T01';

-- Joins rebuild the original mark sheet whenever it is needed
SELECT st.StudentID, st.StudentName, st.Class, su.SubjectName, t.TeacherName, r.Mark
FROM Result AS r
JOIN Student AS st ON r.StudentID = st.StudentID
JOIN Subject AS su ON r.SubjectCode = su.SubjectCode
JOIN Teacher AS t  ON su.TeacherID = t.TeacherID
ORDER BY st.StudentID, su.SubjectName;

-- A new subject can be added before anyone has a mark in it
INSERT INTO Subject VALUES ('CHE', 'Chemistry', 'T02');
SELECT * FROM Subject;

Code Explanation

Press Run. A typing mistake in the teacher's name is corrected with one UPDATE of one row, and every mark row shows the corrected name, because the name is stored once. The four-table JOIN rebuilds the original mark sheet whenever it is needed, and Chemistry is added to Subject before anyone has a mark in it: the insertion anomaly has gone. The CHECK constraint is a domain rule: a mark must be between 0 and 100.

Worked example 1: name the anomaly

All three refer to the 1NF table above.

"J. Habimana's name is corrected in three rows but not the fourth." An update anomaly: one fact was stored four times.
"Chemistry, taught by C. Mukamana, starts next term, but it cannot be entered." An insertion anomaly: a row needs a StudentID as part of its key, and no student has a Chemistry mark yet.
"Eric leaves and his two rows are deleted; now nothing records that Physics exists or who teaches it." A deletion anomaly: the only Physics row belonged to Eric.

Worked example 2: normalise a library loans table

Loan(StudentID, ISBN, DateBorrowed, StudentName, Class, Title, AuthorID, AuthorName, DueDate), in 1NF. Rules: a student has one name and class; a book (ISBN) has one title and, in this school's records, one main author; an author has one name; the due date is set per loan.

Dependencies. StudentID → StudentName, Class (partial). ISBN → Title, AuthorID (partial). AuthorID → AuthorName (transitive, through ISBN). (StudentID, ISBN, DateBorrowed) → DueDate (full).
2NF, removing partial dependencies: Student(StudentID, StudentName, Class); Book(ISBN, Title, AuthorID, AuthorName); Loan(StudentID*, ISBN*, DateBorrowed, DueDate).
3NF, removing the transitive dependency from Book: Author(AuthorID, AuthorName); Book(ISBN, Title, AuthorID*).

Result: Student, Book, Author and Loan, the same shape as the library ERD of Lessons 3 and 4. Good ER modelling and normalisation usually arrive at the same design from two directions.

Worked example 3: is it in 3NF?

Patient(PatientID, FullName, DoctorID, DoctorName). The key is a single attribute, so there can be no partial dependency: the relation is in 2NF. But PatientID → DoctorID → DoctorName is transitive, so it is not in 3NF. Fix: Doctor(DoctorID, DoctorName) and Patient(PatientID, FullName, DoctorID*).

How far to normalise?

For almost every school, business and government database, 3NF is the target: it removes the anomalies that cause real errors. Normalisation has a cost too: answers need joins across several tables, which take more work to write and to run. Large reporting systems sometimes denormalise on purpose, keeping a copy of a total or a name to make reading faster, and accept the extra care needed to keep the copies in step. That is a deliberate trade-off made by an expert, never an excuse for a careless design.

Decomplexify: Learn Database Normalization, 1NF, 2NF, 3NF, 4NF, 5NF. Watch the first three normal forms built up one rule at a time with clear tables; the fourth and fifth forms go beyond this unit.

Warning

Dependencies come from the rules, not from the sample data. Six rows may happen to show each teacher with one subject; only the school can say whether that is a rule.
2NF is about composite keys. A relation with a single-attribute key in 1NF is already in 2NF; check it for transitive dependencies next.
Splitting must not lose information. Always keep a foreign key in the original relation, so a join can rebuild the original data.

Tip

"The key, the whole key, and nothing but the key." For each non-key attribute ask: Does it depend on a key (1NF)? On the whole key (2NF)? On nothing but the key (3NF)?

✦

Practice

Define update, insertion and deletion anomalies, with an example of each from a single table Sale(SaleID, Date, CustomerName, CustomerPhone, ProductName, Price).
Put this into 1NF and give its key: Club(ClubName, Patron, Members), where Members holds a list of student names.
State the functional dependencies in Enrolment(StudentID, CourseCode, StudentName, CourseTitle, Grade), and classify each as full or partial.
Normalise the relation in question 3 to 3NF.
Employee(EmpID, Name, DeptCode, DeptName, DeptLocation): in which normal form is it? Normalise it to 3NF.
Explain why a relation in 1NF whose primary key is a single attribute is always in 2NF.
A developer says, "Normalisation is useless: now I need four joins to print a mark sheet." Evaluate this claim.

Show answersHide answers

Update: a customer's phone stored in every sale must be changed in each row. Insertion: a new product cannot be recorded until it is sold. Deletion: deleting the only sale of a product loses its name and price.
One row per club member: ClubMember(ClubName, StudentName, Patron). (Normalising further would move Patron into Club(ClubName, Patron).)
StudentID → StudentName (partial); CourseCode → CourseTitle (partial); (StudentID, CourseCode) → Grade (full).
Student(StudentID, StudentName), Course(CourseCode, CourseTitle), Enrolment(StudentID*, CourseCode*, Grade). There are no transitive dependencies left, so this is 3NF.
2NF (single-attribute key, so no partial dependency), but not 3NF: EmpID → DeptCode → DeptName, DeptLocation is transitive. 3NF: Department(DeptCode, DeptName, DeptLocation) and Employee(EmpID, Name, DeptCode*).
A partial dependency means depending on part of the key; a key of one attribute has no smaller part, so no partial dependency can exist.
Partly true, mostly wrong. Joins are extra work, but they are what the DBMS is built for, and a saved query writes them once. In return, every fact is stored once, so the data cannot contradict itself, and new subjects or teachers can be added freely. For a reporting system a deliberate, controlled denormalisation may be justified; for the school's main records, 3NF is the right choice.

Quick check

Match each problem to the anomaly it shows.

Tap one on the left, then its partner on the right.

Key points

Redundancy causes update, insertion and deletion anomalies; normalisation removes them by splitting relations so each fact is stored once, linked by foreign keys.
Functional dependencies (A → B) guide the process: full, partial (on part of a composite key) and transitive (through a non-key attribute).
1NF: atomic values, no repeating groups, a key. 2NF: no partial dependencies. 3NF: no transitive dependencies: "the key, the whole key, and nothing but the key".
3NF is the usual target; joins rebuild the combined view, and any denormalisation must be a deliberate trade-off.

1A mark sheet that fights back

2Unnormalised form (UNF)

3First normal form (1NF)

4Finding the dependencies

5Second normal form (2NF)

6Third normal form (3NF)

7How far to normalise?