Multiple choiceQuestion 1 · Recall
Loading…
By the end you can explain why a database is normalised, test a table against First Normal Form (1NF) and Second Normal Form (2NF), and split a table that fails either test.
Normalisation is the process of organising tables so that each fact is stored once, in the table where it belongs.
It is not tidiness for its own sake. A badly organised table causes three anomalies — problems that appear when you insert, update or delete data.
| Anomaly | What happens in a badly designed table |
|---|---|
| Insertion | You cannot add a course until at least one student enrols on it, because the course details only exist inside a student row. |
| Update | A course title is repeated for every enrolled student, so changing it means changing many rows. Miss one and the table contradicts itself. |
| Deletion | Deleting the last student on a course erases the course details too, because they were stored only in that student's row. |
Normalisation removes the repetition that creates these anomalies. It happens in stages called normal forms, and each stage assumes the previous one is satisfied.
First Normal Form (1NF)
A table is in 1NF when every field contains a single atomic value — no lists, no repeating groups, and no multiple values in one field — and every record can be uniquely identified by a primary key.
Worked example: From a repeating group to 1NF
A school stores enrolments like this:
| StudentID | StudentName | Courses |
|---|---|---|
| S01 | Amina Khan | CS101, MA102 |
| S02 | Ben Osei | CS101 |
Explain why this is not in 1NF, and convert it.
Second Normal Form (2NF)
A table is in 2NF when it is in 1NF and every non-key field depends on the whole primary key, not just part of it. A field that depends on only part of a composite key is a partial dependency.
2NF only causes trouble when the primary key is composite (made of more than one field). If the primary key is a single field, a 1NF table is automatically in 2NF, because there is no "part of the key" for a field to depend on.
Suppose the enrolment table also stored the course title:
| StudentID | CourseCode | StudentName | CourseTitle |
|---|---|---|---|
| S01 | CS101 | Amina Khan | Introduction to Programming |
| S01 | MA102 | Amina Khan | Calculus |
| S02 | CS101 | Ben Osei | Introduction to Programming |
The primary key is (StudentID, CourseCode). Now ask what each non-key field depends on.
StudentName depends on StudentID alone. Amina's name does not change when she takes a different course.CourseTitle depends on CourseCode alone. The title does not change when a different student enrols.Worked example: Splitting into 2NF
Normalise the enrolment table above into 2NF.
Think of each table as a room, and each fact as belonging in exactly one room. A student's name lives in the student room. A course title lives in the course room. The enrolment room holds only the links between them.
When a fact is stored in the wrong room, you have to update it in every copy. Normalisation is the rule that every fact gets one home, reachable through its key.
A table is in 1NF if it has a primary key.
A primary key is necessary but not sufficient. A field holding a list, such as "CS101, MA102", still breaks 1NF even if the table has a key.
Every table needs fixing to reach 2NF.
A table whose primary key is a single field is already in 2NF once it is in 1NF. Partial dependency needs a composite key to exist.
Normalising always means more tables, which is always worse.
It usually means more tables, but each is simpler and each fact is stored once. The cost is extra joins; the benefit is data that cannot contradict itself.
Splitting a table loses data.
Student(StudentID, StudentName) and so on, not just a sentence saying "separate the students".The split keeps every fact and adds keys to reconnect them. A join reconstructs the original view. What is removed is the duplication, not the information.