Model Answer – Library Normalization (1NF → 3NF)
This solution walks through First, Second and Third Normal Forms for the given library table, explains anomalies
removed at each step, and presents the final schema.
Unnormalized / Mixed Table (Given)
BookID Title Author AuthorPhone StudentID StudentName BorrowDate
1 DBMS Concepts Navathe 98765 101 Amit 2024-08-01
2 SQL Fundamentals James Martin 87654 102 Sneha 2024-08-03
1 DBMS Concepts Navathe 98765 103 Raj 2024-08-05
Observations: Mixes book data, author data, student data and borrowing transactions in a single table, causing
duplication and update/delete anomalies.
Step 1 – First Normal Form (1NF)
Rule: Each cell holds a single value; rows are uniquely identified. Action: keep atomic values and separate the
repeating transactional data into a Borrow table.
Borrow (Transactions) – in 1NF:
BookID StudentID BorrowDate
1 101 2024-08-01
2 102 2024-08-03
1 103 2024-08-05
Why this helps: Transaction rows are now distinct and atomic; we’ve isolated many-to-many activity (students
borrow books) from static details.
Step 2 – Second Normal Form (2NF)
Rule: Must be in 1NF; every non-key attribute depends on the whole key (no partial dependency). Action: move
attributes that depend only on BookID or StudentID into their own tables.
Books – in 2NF:
BookID Title Author
1 DBMS Concepts Navathe
2 SQL Fundamentals James Martin
Students – in 2NF:
StudentID StudentName
101 Amit
102 Sneha
103 Raj
Borrow – (composite key BookID + StudentID + BorrowDate):
BookID StudentID BorrowDate
1 101 2024-08-01
2 102 2024-08-03
1 103 2024-08-05
Why this helps: Title/Author depend only on BookID; StudentName depends only on StudentID. We removed partial
dependencies from the borrowing table.
Step 3 – Third Normal Form (3NF)
Rule: Must be in 2NF; no transitive dependency (no non-key attribute depends on another non-key attribute).
Action: separate AuthorPhone to an Authors table so it depends on Author, not via Book.
Authors – in 3NF:
Author AuthorPhone
Navathe 98765
James Martin 87654
Books – refined (Author is a foreign key to [Link]):
BookID Title Author
1 DBMS Concepts Navathe
2 SQL Fundamentals James Martin
Why this helps: If an author’s phone changes, update one row in Authors only; avoids duplication and update
anomalies.
Final Schema (3NF)
Table Columns Primary Key Notes
Books BookID, Title, Author BookID Author → FK to [Link]
Authors Author, AuthorPhone Author One row per author
Students StudentID, StudentName StudentID One row per student
Borrow BookID, StudentID, BorrowDate (BookID, StudentID, BorrowDate)
Each borrowing event
Optional integrity rules: (i) [Link] references [Link]; (ii) [Link] references
[Link]; (iii) [Link] references [Link].
Optional SQL DDL (3NF Schema)
CREATE TABLE Authors (
Author VARCHAR(100) PRIMARY KEY,
AuthorPhone VARCHAR(20)
);
CREATE TABLE Books (
BookID INT PRIMARY KEY,
Title VARCHAR(200) NOT NULL,
Author VARCHAR(100) NOT NULL,
FOREIGN KEY (Author) REFERENCES Authors(Author)
);
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
StudentName VARCHAR(100) NOT NULL
);
CREATE TABLE Borrow (
BookID INT NOT NULL,
StudentID INT NOT NULL,
BorrowDate DATE NOT NULL,
PRIMARY KEY (BookID, StudentID, BorrowDate),
FOREIGN KEY (BookID) REFERENCES Books(BookID),
FOREIGN KEY (StudentID) REFERENCES Students(StudentID)
);