Show understanding of the normalisation process

8.1 Database Concepts

What is a Database?

Think of a database as a super‑organized library where every book (record) is stored in a specific place (table) and can be found quickly using a catalogue (index). 📚

Why Normalise?

Just like a library sorts books by genre, author, and title to avoid confusion, normalisation removes redundancy and inconsistency from tables. It makes updates, deletions, and searches faster and safer. 🧩

Exam Tip: Remember that 1NF requires atomic values, 2NF removes partial dependencies, and 3NF eliminates transitive dependencies. Write the key steps in order when asked to normalise a table.

The Normalisation Process

  1. 1NF (First Normal Form) – Ensure each field contains only atomic values and each record is unique. Example: No lists or multiple values in a single column.
  2. 2NF (Second Normal Form) – Remove partial dependencies on a composite key. Every non‑key attribute must depend on the whole primary key.
  3. 3NF (Third Normal Form) – Eliminate transitive dependencies. Non‑key attributes should depend only on the primary key.
  4. BCNF (Boyce‑Codd Normal Form) – A stricter version of 3NF where every determinant is a candidate key.

Example: Normalising a Student Course Table

Suppose we have the following table:

StudentID StudentName Course Instructor
S001 Alice Math Prof. Smith
S002 Bob Math Prof. Smith
S001 Alice Physics Dr. Lee

Issues:

  • Duplicate student names for each course (redundancy).
  • Instructor information repeats for the same course.

After normalisation:

StudentID StudentName
S001 Alice
S002 Bob
CourseID CourseName Instructor
C001 Math Prof. Smith
C002 Physics Dr. Lee
StudentID CourseID
S001 C001
S001 C002
S002 C001
Exam Tip: When normalising, always identify the primary key first. Then check for partial and transitive dependencies. Use the functional dependency notation: $A \rightarrow B$ means A determines B. If you see $A \rightarrow B$ and $B \rightarrow C$, you need to split to avoid transitive dependency.

Quick Recap

  • 1NF: Atomic values, unique rows.
  • 2NF: No partial dependencies on composite keys.
  • 3NF: No transitive dependencies.
  • BCNF: Every determinant is a candidate key.

Remember: Normalisation is like cleaning up a messy room—each item has its own spot, so you can find and replace things without breaking anything else. Good luck with your exams! 🚀

Revision

Log in to practice.

2 views 0 suggestions