Loading...
Normalisation reorganises relations using dependencies to reduce update anomalies. Begin with the assumptions that determine which attributes depend on which keys.
What you'll be able to do
A functional dependency X→Y means an X value determines a Y value under the domain rules. It is not merely a pattern that happens to appear in a small dataset. For the example, StudentID determines StudentName; CourseID determines CourseTitle and TeacherID; TeacherID determines TeacherName.
The combined StudentID,CourseID determines Score, assuming one score per enrolment. Duplicating student and teacher details in every enrolment creates anomalies: a name update can be inconsistent, a deletion can remove the only stored course information, and a new course may be difficult to insert without an enrolment.
First normal form removes repeating groups and uses individual attribute values under the chosen domain. Instead of Course1,Course2 fields, store distinct enrolment rows. Establish a key that uniquely identifies each row.
For a composite enrolment key, second normal form removes non-key attributes depending on only part of that key. Put StudentName in Student and CourseTitle/TeacherID/TeacherName in Course rather than repeating them for each student/course pair. Score remains with Enrolment because it depends on the whole pairing.
In the example Course relation, CourseID determines TeacherID and TeacherID determines TeacherName. Move TeacherName into Teacher so the course stores the reference rather than repeating the teacher description. This removes the stated transitive non-key dependency and produces a third-normal-form design under the assumptions.
A suitable result is Student(StudentID,StudentName), Teacher(TeacherID,TeacherName), Course(CourseID,CourseTitle,TeacherID) and Enrolment(StudentID,CourseID,Score). Preserve the keys and relationship links so required information can be reconstructed by joins. Normalisation reduces defined anomalies; it does not automatically minimise the cost of every query.
| Dependency problem | Move information to |
|---|---|
| StudentID → StudentName | Student |
| CourseID → CourseTitle, TeacherID | Course |
| TeacherID → TeacherName | Teacher |
| StudentID,CourseID → Score | Enrolment |
Attempt the task before opening its worked response. Record each change of state, explain the rule used, and check the result against the original requirements. Code examples labelled Python 3 use that language’s conventions; pseudocode states its own assumptions.
Common mistakes to avoid
Key takeaways
Test yourself
Answer a few questions, then review anything you missed.
Start with the quiz above to check the key ideas. You can also choose another topic from the practice library.