Normalise to 3NF

What is a 3NF Database?

A Third Normal Form (3NF) database is a relational database structure that eliminates redundancy, partial dependencies, and transitive dependencies.

It ensures efficient data storage and integrity by properly structuring tables.

Steps to Normalise a Database to 3NF

  • Step 1: 1NF - Ensure Atomicity (All attributes contain atomic values, no repeating groups).
  • Step 2: 2NF - Remove Partial Dependencies (Every non-key attribute depends on the entire primary key).
  • Step 3: 3NF - Remove Transitive Dependencies (Non-key attributes must not depend on other non-key attributes).

Real-World Examples

Example: Library Management System

Before we make a start, let's think about the real-life version of what this means:

  • Someone can go to a library and take out 1 or more books
  • Each time this happens, we'll call it a transaction
  • Over time, a borrower can have many transactions
  • A transaction can consist of borrowing 1 book, 2 books, or more

So, as a description of relations, we have:

  • Each borrower can make many transactions
  • Each transaction is made by 1 borrower
  • Each transaction may consist of borrowing many books
  • Also, an author can write many books
  • For simplicity, a book is written by one author

Unnormalised Table (UNF):

This is our starting point:

Loans(TransactionID, BookTitle1, AuthorName1, BookTitle2, AuthorName2, MemberName, BorrowDate)

Sample Data:

TransactionIDBookTitle1AuthorName1BookTitle2AuthorName2MemberNameBorrowDate
1 The Hobbit J.R.R. Tolkien 1984 George Orwell Amira Khan 2024-05-12
2 Dracula Bram Stoker Frankenstein Mary Shelley Liam Evans 2024-05-13
3 Jane Eyre Charlotte Brontë - - Amira Khan 2024-05-14

In this current form, it comes with certain limitations, and here is a selection:

  • 2 books, maximum per transaction
  • If you take 1 book out, memory is still allocated in the 'gap'
  • If you want to query who has borrowed a particular book, you need to search 2 columns

1NF: Remove Repeating Groups

The clear repeating attributes are those about BookTitle and AuthorName. This means our first step results in:

Loans(TransactionID, BookTitle, AuthorName, MemberName, BorrowDate)

So our sample data now looks like this:

TransactionIDBookTitleAuthorNameMemberNameBorrowDate
1 The Hobbit J.R.R. Tolkien Amira Khan 2024-05-12
1 1984 George Orwell Amira Khan 2024-05-12
2 Dracula Bram Stoker Liam Evans 2024-05-13
2 Frankenstein Mary Shelley Liam Evans 2024-05-13
3 Jane Eyre Charlotte Brontë Amira Khan 2024-05-14

In the original, unnormalised table, TransactionID referred to a row that could consist of up to 2 books being loaned. This means that a loan is not about a single book, but about a loan transaction. Therefore, the revised Loans table must identify which book(s) were loaned in that transaction.

As a result, TransactionID and BookTitle become a composite key. Why? Because TransactionID is not unique to a row as a loan (transaction) might consist of more than 1 book. But, together, TransactionID and BookTitle do provide a unique reference to a record.

So we get this:

Loans(TransactionID, BookTitle, AuthorName, MemberName, BorrowDate)

We leave 1NF with a composite key. It should be noted that BookTitle is not an ideal candidate for a Primary Key, but we'll come to that next.

2NF: Remove Partial Dependencies

Above, The Loans table refers to all the parts of each transaction by using multiple rows, 1 for each loan of a book (using TransactionID and BookTitle) in the same transaction.

Now let's look for any partial dependencies.

  • AuthorName: This has a dependency on the book, not the transaction
  • MemberName: This has a dependency on the transaction, not the book (they could be any books)
  • BorrowDate: This has a dependency on the transaction, not the book
  • BookTitle: This is interesting as it currently forms part of the composite key. However, the BookTitle does not depend on the transaction: it is the thing being loaned

So we need to create some new tables.

Remember to leave an ID for each attribute behind in Loans, as this ensures we maintain relationships in each transaction.

Books(BookID, BookTitle, AuthorID)
Authors(AuthorID, AuthorName)
Members(MemberID, MemberName)
Loans(TransactionID, MemberID, BookID, AuthorID, BorrowDate)

This leaves us in a sticky situation as TransactionID will be unique for each record, but a transaction might consist of more than 1 book being loaned.

Remember we said:

  • MemberName: This has a dependency on the transaction, not the book
  • BorrowDate: This has a dependency on the transaction, not the book

Well, this is very important and very subtle. At the beginning of this example we said, A transaction can consist of borrowing 1 book, 2 books, or more. So, for any given transaction, there might be many books being loaned.

Which gives us:

Books(BookID, BookTitle, AuthorID)
Authors(AuthorID, AuthorName)
Members(MemberID, MemberName)
Loans(LoanID, TransactionID, BookID)
Transactions(TransactionID, MemberID, BorrowDate)

This is what our original sample data looks like now:

Books
BookID BookTitle AuthorID
B001The HobbitA001
B0021984A002
B003DraculaA003
B004FrankensteinA004
B005Jane EyreA005
Authors
AuthorIDAuthorName
A001J.R.R. Tolkien
A002George Orwell
A003Bram Stoker
A004Mary Shelley
A005Charlotte Brontë
Members
MemberIDMemberName
M101Amira Khan
M102Liam Evans
Loans
LoanIDTransactionIDBookID
L001T001B001
L002T001B002
L003T002B003
L004T002B004
L005T003B005
Transactions
TransactionIDMemberIDBorrowDate
T001M1012024-05-12
T002M1022024-05-13
T003M1012024-05-14

3NF: Remove Transitive Dependencies

We are now looking for non-key attributes that are dependent on other non-key attributes. So far, we have:

Books(BookID, BookTitle, AuthorID)
Authors(AuthorID, AuthorName)
Members(MemberID, MemberName)
Loans(LoanID, TransactionID, BookID)
Transactions(TransactionID, MemberID, BorrowDate)

In this case, it appears we have no such dependencies, so we're done.

Our tables are now organised in such a way that:

  • A Transaction (which can consist of multiple loans) is uniquely identified (PK) and linked to the Member (FK)
  • A Loan - 1 book - is linked to both the Transaction and the Book
  • Multiple Loans can exist per Transaction (unique LoanID, same TransactionID)
  • Each Book is linked to its Author (FK)

We have normalised a database to 3rd Normal Form.

Example: Hospital Management System

Before we look at the tables, let's think about the real-life version of this system:

  • A hospital has many patients
  • Patients may book one or more appointments
  • Each appointment is with one doctor
  • Doctors can see many patients over time
  • Each patient can have multiple medical records

So, as a description of relations, we have:

  • Each patient can have many appointments
  • Each appointment is for one patient
  • Each appointment is with one doctor
  • Each doctor can have many appointments
  • Each patient can have many medical records

Which gives:

Patients(PatientID, Name, DOB, Address)
Doctors(DoctorID, Name, Specialty)
Appointments(AppointmentID, PatientID, DoctorID, AppointmentDate)
MedicalRecords(RecordID, PatientID, Diagnosis, Prescription)

Example: E-Commerce Platform

Before we look at the structure, let's think about the real-life version of this system:

  • Customers can place orders on the website
  • Each order can include one or more products
  • Each product belongs to a category and has a price
  • Over time, a customer can place multiple orders

So, as a description of relations, we have:

  • Each customer can place many orders
  • Each order is placed by one customer
  • Each order can contain many products
  • Each product can appear in many orders (via OrderDetails)

Which gives:

Customers(CustomerID, Name, Email, Address)
Products(ProductID, Name, Category, Price)
Orders(OrderID, CustomerID, OrderDate)
OrderDetails(OrderDetailID, OrderID, ProductID, Quantity)

Example: School Management System

Before we look at the tables, let’s reflect on the school environment:

  • Students are enrolled into classes
  • Each class is taught by a teacher
  • Teachers may teach more than one class
  • Teachers may teach more than one subject
  • Subjects can be taught by multiple teachers

So, as a description of relations, we have:

  • Each student can be enrolled in many classes
  • Each class can have many students
  • Each class is taught by one teacher
  • Each teacher can teach many classes
  • Each teacher can teach many subjects
  • Each subject can be taught by many teachers

Which gives:

Students(StudentID, Name, DOB, Class)
Teachers(TeacherID, Name)
Subjects(SubjectID, SubjectName)
TeacherSubjects(TeacherID, SubjectID)
Classes(ClassID, ClassName, TeacherID)
Enrollments(EnrollmentID, StudentID, ClassID)

Why Normalise a Database to 3NF?

  • Reduces Data Redundancy: Avoids unnecessary duplication of data.
  • Eliminates Update Anomalies: Prevents errors when modifying data.
  • Ensures Data Integrity: Maintains accuracy and consistency.
  • Improves Performance: Optimises queries and data storage.

 Key Takeaways

  • A 3NF database eliminates redundancy and dependency issues.
  • Normalisation involves 1NF (Atomicity), 2NF (No Partial Dependencies), and 3NF (No Transitive Dependencies).
  • Real-world applications include library management, hospital management, e-commerce, school management, and employee management.
  • Well-structured databases ensure data integrity, efficiency, and scalability.