Practical 8: Normalizing to Third Normal Form

Ravi Deshmukh is gone.

INSERT INTO bad_loan VALUES
 (3, 2, 'Ravi Deshmukh', 'Thane', 3, 'MySQL Reference', 'Vikram Vaswani', 'India', '2025-06-10');
UPDATE bad_loan SET member_city = 'Mumbai' WHERE member_id = 1;

Functional dependency, which is the language of the rest

X determines Y, written X to Y, means: knowing X tells you Y, and two rows with the same X must have the same Y.

In the bad table:

Those four lines are the analysis, and the three normal forms are three rules about them. Write them down before touching the tables; the rest is mechanical.

Two more terms, both asked by name:

A prime attribute is one that is part of some candidate key. Everything else is non-prime.

A partial dependency is a non-prime attribute that depends on only part of a composite key. A transitive dependency is a non-prime attribute that depends on another non-prime attribute.

First normal form

A table is in 1NF when every value is atomic: one value per column per row, and no repeating groups.

The bad table is already in 1NF, and it is worth seeing what would not be:

Two books in one cell. Nothing can be asked of it: "who borrowed Database Systems" needs a string search, the count of books needs the commas counted, and a title containing a comma breaks it.

The other failure of 1NF is a repeating group: book1, book2, book3 as three columns, which is the same mistake as phone1, phone2, phone3 in [Practical 8: Turning the ER Model Into Tables].

The fix in both cases is a row per value, which is exactly what the loan table already is.

Second normal form

A table is in 2NF when it is in 1NF and no non-prime attribute depends on only part of a composite key.

This form has something to say only when the primary key is composite. If the key is a single column, nothing can depend on part of it, and a 1NF table with a single column key is automatically in 2NF.

So take a table whose key really is composite: the loan keyed by the book and the member.

CREATE TABLE bad_loan2 (
    book_id     INT,
    member_id   INT,
    book_title  VARCHAR(20),
    member_name VARCHAR(16),
    issued_on   DATE,
    PRIMARY KEY (book_id, member_id)
);

INSERT INTO bad_loan2 VALUES
 (1, 1, 'The C Language',   'Asha Kulkarni', '2025-06-02'),
 (2, 1, 'Database Systems', 'Asha Kulkarni', '2025-06-02'),
 (3, 2, 'MySQL Reference',  'Ravi Deshmukh', '2025-06-10');

SELECT * FROM bad_loan2;

+---------+-----------+------------------+---------------+------------+
| book_id | member_id | book_title       | member_name   | issued_on  |
+---------+-----------+------------------+---------------+------------+
|       1 |         1 | The C Language   | Asha Kulkarni | 2025-06-02 |
|       2 |         1 | Database Systems | Asha Kulkarni | 2025-06-02 |
|       3 |         2 | MySQL Reference  | Ravi Deshmukh | 2025-06-10 |
+---------+-----------+------------------+---------------+------------+

The key is (book_id, member_id). Now look at the dependencies:

• book_title depends on book_id alone, which is part of the key.
• member_name depends on member_id alone, which is the other part.
• issued_on depends on the whole key, as it should.

Two partial dependencies, so the table is in 1NF and not in 2NF.

The fix is to move each partially dependent attribute to a table keyed by the part it actually depends on.

CREATE TABLE n2_book   (book_id INT PRIMARY KEY, book_title VARCHAR(20));
CREATE TABLE n2_member (member_id INT PRIMARY KEY, member_name VARCHAR(16));
CREATE TABLE n2_loan   (book_id INT, member_id INT, issued_on DATE,
                        PRIMARY KEY (book_id, member_id));

INSERT INTO n2_book   SELECT DISTINCT book_id, book_title   FROM bad_loan2;
INSERT INTO n2_member SELECT DISTINCT member_id, member_name FROM bad_loan2;
INSERT INTO n2_loan   SELECT book_id, member_id, issued_on   FROM bad_loan2;

SELECT b.book_title, m.member_name, l.issued_on
FROM n2_loan l
JOIN n2_book b   ON b.book_id   = l.book_id
JOIN n2_member m ON m.member_id = l.member_id
ORDER BY l.book_id;

+------------------+---------------+------------+
| book_title       | member_name   | issued_on  |
+------------------+---------------+------------+
| The C Language   | Asha Kulkarni | 2025-06-02 |
| Database Systems | Asha Kulkarni | 2025-06-02 |
| MySQL Reference  | Ravi Deshmukh | 2025-06-10 |
+------------------+---------------+------------+

The same three facts, and Asha Kulkarni's name is now stored once instead of twice. Nothing was lost: the join puts the original rows back exactly, which is what makes it a decomposition rather than a deletion.

Third normal form

A table is in 3NF when it is in 2NF and no non-prime attribute depends on another non-prime attribute.

That is the transitive dependency, and the library has one:

CREATE TABLE bad_book (
    book_id        INT PRIMARY KEY,
    book_title     VARCHAR(20),
    author_name    VARCHAR(18),
    author_country VARCHAR(10)
);

INSERT INTO bad_book VALUES
 (1, 'The C Language',    'Dennis Ritchie', 'USA'),
 (4, 'Data Structures',   'Behrouz Forouzan', 'Iran'),
 (5, 'Computer Networks', 'Behrouz Forouzan', 'Iran');

SELECT * FROM bad_book;

+---------+-------------------+------------------+----------------+
| book_id | book_title        | author_name      | author_country |
+---------+-------------------+------------------+----------------+
|       1 | The C Language    | Dennis Ritchie   | USA            |
|       4 | Data Structures   | Behrouz Forouzan | Iran           |
|       5 | Computer Networks | Behrouz Forouzan | Iran           |
+---------+-------------------+------------------+----------------+

book_id determines author_name, and author_name determines author_country. So author_country depends on the key through another non-key column, which is what transitive means: book id to author name to author country.

Iran is written twice, and the update anomaly follows immediately:

UPDATE bad_book SET author_country = 'Canada' WHERE book_id = 4;

SELECT author_name, author_country FROM bad_book WHERE author_name = 'Behrouz Forouzan';

+------------------+----------------+
| author_name      | author_country |
+------------------+----------------+
| Behrouz Forouzan | Canada         |
| Behrouz Forouzan | Iran           |
+------------------+----------------+

One author, two countries.

The fix is to move the transitively dependent attribute to a table keyed by what it really depends on.

CREATE TABLE n3_author (author_name VARCHAR(18) PRIMARY KEY,
                        author_country VARCHAR(10));
CREATE TABLE n3_book   (book_id INT PRIMARY KEY, book_title VARCHAR(20),
                        author_name VARCHAR(18),
                        FOREIGN KEY (author_name) REFERENCES n3_author(author_name));

INSERT INTO n3_author VALUES ('Dennis Ritchie', 'USA'), ('Behrouz Forouzan', 'Iran');
INSERT INTO n3_book VALUES (1, 'The C Language', 'Dennis Ritchie'),
                           (4, 'Data Structures', 'Behrouz Forouzan'),
                           (5, 'Computer Networks', 'Behrouz Forouzan');

UPDATE n3_author SET author_country = 'Canada' WHERE author_name = 'Behrouz Forouzan';

SELECT b.book_title, a.author_name, a.author_country
FROM n3_book b JOIN n3_author a ON a.author_name = b.author_name
ORDER BY b.book_id;

+-------------------+------------------+----------------+
| book_title        | author_name      | author_country |
+-------------------+------------------+----------------+
| The C Language    | Dennis Ritchie   | USA            |
| Data Structures   | Behrouz Forouzan | Canada         |
| Computer Networks | Behrouz Forouzan | Canada         |
+-------------------+------------------+----------------+

One update, both books right. That is what normalization buys, and it is the paragraph to write in the conclusion.

In the real library database this is why AUTHOR is a table of its own, exactly as [Practical 1: The ER Diagram: Entities, Attributes and Keys] argued from the other direction. A well drawn ER diagram usually produces tables that are already in 3NF, which is a good thing to be able to say.

The three forms, on one line each

The mnemonic examiners like: the key, the whole key, and nothing but the key. 1NF is about the data being atomic; 2NF says every non-key attribute depends on the whole key; 3NF says it depends on nothing but the key.

Beyond 3NF there is Boyce Codd normal form, and then 4NF and 5NF. MU's syllabus says up to third normal form, so they are outside this practical; knowing that they exist and that BCNF is a stricter 3NF is enough.

What beginners get wrong

Thinking 2NF applies to a table with a single column key. It cannot fail there.

Confusing partial with transitive. Partial is on part of the key; transitive is through a non-key column.

Decomposing so far that the original rows cannot be rebuilt. A decomposition must be lossless: the join must give back exactly what was there.

Normalizing without writing the dependencies down. The dependencies are the working; the tables follow from them.

Believing normalization is always right. It costs joins, and a reporting database is often deliberately denormalized for speed. Say so in a viva: it shows judgment.

Forgetting to add the foreign keys after decomposing. Without them the pieces can drift apart, and the anomaly comes back by another road.

Quick revision

• Normalization puts each fact in one place, to stop insertion, update and deletion anomalies.
• X to Y means knowing X tells you Y.
• A prime attribute is part of a candidate key; anything else is non-prime.
• 1NF: atomic values, no repeating groups.
• 2NF: 1NF and no non-prime attribute depending on part of a composite key.
• 3NF: 2NF and no non-prime attribute depending on another non-prime attribute.
• The key, the whole key, and nothing but the key.
• A decomposition must be lossless: the join must rebuild the original rows.
• A good ER design usually lands in 3NF already.

What goes in your journal

Aim, the unnormalized table with its rows, and the list of functional dependencies. Then three steps, each with the tables before and after and one sentence naming the dependency being removed.

Include at least one anomaly actually performed: the UPDATE that leaves one member in two cities is the best of them, because the result is visible in two rows of one printed table. Then the same update after normalization, touching one row and fixing everything. Those two results are the practical's whole argument.

Test yourself

1. What are the three anomalies normalization prevents? Insertion, update and deletion anomalies.

2. What does 1NF require? That every value is atomic, with one value per column per row and no repeating groups of columns.

3. What is a partial dependency, and which form removes it? A non-prime attribute depending on only part of a composite primary key. 2NF removes it.

4. What is a transitive dependency, and which form removes it? A non-prime attribute depending on another non-prime attribute rather than on the key directly. 3NF removes it.

5. Why can a table with a single column primary key not violate 2NF? Because there is no part of the key for an attribute to depend on; any dependency on the key is a dependency on the whole key.

6. What does lossless decomposition mean? That joining the decomposed tables back together returns exactly the original rows, with nothing lost and nothing invented.

7. Give the mnemonic for the first three normal forms. The key, the whole key, and nothing but the key.