Normalization of Tables: From 2NF to 3NF
The second normal form (2NF) ensures that each non-key attribute of a table is fully dependent on the primary key. However, it is still possible for a table to have transitive dependencies, where non-key attributes depend on other non-key attributes. To address this issue, we can use the third normal form (3NF) to further normalize the database.
The third normal form (3NF) requires that all non-key attributes in a table be independent of each other, and that each non-key attribute depends only on the primary key. This helps eliminate data redundancy and improves data consistency and maintainability.
Let's consider an example of a fictional database for an online bookstore. Suppose we have a table called "Books" that contains information about books, including their title, author, publisher, and the department they belong to:
| ISBN | Title | Author | Publisher | Department |
| 978-0316067928 | The Hunger Games | Suzanne Collins | Scholastic | Fiction |
| 978-0316015844 | Catching Fire | Suzanne Collins | Scholastic | Fiction |
| 978-0547928227 | The Hobbit | J.R.R. Tolkien | Houghton Mifflin | Fiction |
| 978-0590353427 | Harry Potter and the Philosopher's Stone | J.K. Rowling | Bloomsbury | Fiction |
This table is in 2NF because each non-key attribute depends fully on the primary key (ISBN). However, we can see that there is a transitive dependency between the "Department" column and the "Title" column, because the department of a book is determined by the title of the book, which is determined by the ISBN.
To normalize this table to 3NF, we need to split it into two tables. The first table, "Books", will contain information about each book and its ISBN, author, and publisher:
| ISBN | Title | Author | Publisher |
| 978-0316067928 | The Hunger Games | Suzanne Collins | Scholastic |
| 978-0316015844 | Catching Fire | Suzanne Collins | Scholastic |
| 978-0547928227 | The Hobbit | J.R.R. Tolkien | Houghton Mifflin |
| 978-0590353427 | Harry Potter and the Philosopher's Stone | J.K. Rowling | Bloomsbury |
The second table, "Departments", will contain information about each department and the books it contains:
| Department | ISBN |
| Fiction | 978-0316067928 |
| Fiction | 978-0316015844 |
| Fiction | 978-0547928227 |
| Fiction | 978-0590353427 |
In this normalized database, each non-key attribute (title, author, publisher, and department) depends only on the primary key or the combination of the primary key and a single non-key attribute. There are no transitive dependencies, which improves data consistency and maintainability.