Experiment 2
Title: Data Modeling Exercises 2
Submitted By: Ponugumatla Bhavish
URN: 2023-B-04072004
Semester: III
Program: BCA
Course: Fundamentals of SQL and Data Modeling for the
Web
Let's take the Library Management System ER Model that we created earlier and apply the
normalization process step by step. The aim is to eliminate redundancies and ensure that the
database design adheres to First Normal Form (1NF), Second Normal Form (2NF), and Third Normal
Form (3NF).
Original ER Model Recap:
● Entities:
o Member: MemberID, Name, Email, PhoneNumber, MembershipStartDate
o Book: BookID, Title, Author, Genre, PublishedYear, ISBN, AvailabilityStatus,
PublisherID
o BorrowingTransaction: TransactionID, BorrowDate, DueDate, ReturnDate,
FineAmount, MemberID, BookID, StaffID
o Staff: StaffID, Name, Position, ContactInfo
o Publisher: PublisherID, Name, Address, ContactInfo
Step 1: First Normal Form (1NF)
Definition of 1NF:
● Eliminate repeating groups.
● Ensure that all attributes contain atomic values (no sets, lists, or multi-valued attributes).
Current Issues in 1NF:
● We have no repeating groups or non-atomic values in our original model. Each attribute
already holds atomic values, and all fields are uniquely identified.
Action:
● No change required because the model already satisfies 1NF.
Thus, the Member, Book, BorrowingTransaction, Staff, and Publisher entities are in 1NF.
Step 2: Second Normal Form (2NF)
Definition of 2NF:
● The model must already be in 1NF.
● No partial dependencies: No non-key attribute should depend on a part of the primary key
in a composite key.
Identifying Partial Dependencies:
● BorrowingTransaction has a composite key formed by TransactionID, MemberID, and
BookID.
o In this case, attributes like BorrowDate, DueDate, ReturnDate, and FineAmount
depend only on TransactionID and not on MemberID or BookID.
o Therefore, MemberID and BookID do not need to be in the BorrowingTransaction
table for these attributes.
Action:
● Remove partial dependency by creating separate tables.
Updated Entities in 2NF:
1. Member:
o MemberID (PK)
o Name
o Email
o PhoneNumber
o MembershipStartDate
2. Book:
o BookID (PK)
o Title
o Author
o Genre
o PublishedYear
o ISBN
o AvailabilityStatus
o PublisherID (FK)
3. BorrowingTransaction:
o TransactionID (PK)
o BorrowDate
o DueDate
o ReturnDate
o FineAmount
o MemberID (FK)
o StaffID (FK)
4. Staff:
o StaffID (PK)
o Name
o Position
o ContactInfo
5. Publisher:
o PublisherID (PK)
o Name
o Address
o ContactInfo
Step 3: Third Normal Form (3NF)
Definition of 3NF:
● The model must already be in 2NF.
● No transitive dependencies: Non-key attributes should not depend on other non-key
attributes.
Identifying Transitive Dependencies:
● In the Staff table, the ContactInfo attribute depends on StaffID, but it could also be
considered a non-key attribute that is functionally dependent on the StaffName attribute
(transitive dependency).
● In the Publisher table, ContactInfo depends on PublisherID, but it may also be considered a
non-key attribute dependent on PublisherName.
Action:
● Separate out transitive dependencies into their own tables.
Updated Entities in 3NF:
1. Member:
o MemberID (PK)
o Name
o Email
o PhoneNumber
o MembershipStartDate
2. Book:
o BookID (PK)
o Title
o Author
o Genre
o PublishedYear
o ISBN
o AvailabilityStatus
o PublisherID (FK)
3. BorrowingTransaction:
o TransactionID (PK)
o BorrowDate
o DueDate
o ReturnDate
o FineAmount
o MemberID (FK)
o StaffID (FK)
4. Staff:
o StaffID (PK)
o Name
o Position
o StaffContactInfo:
▪ StaffID (PK)
▪ ContactInfo (Now separated)
5. Publisher:
o PublisherID (PK)
o Name
o PublisherContactInfo:
▪ PublisherID (PK)
▪ ContactInfo (Now separated)
Summary of Normalization Process
● 1NF: The original model is already in 1NF because all attributes contain atomic values.
● 2NF: We removed partial dependencies by splitting BorrowingTransaction into a normalized
structure with TransactionID as the primary key and removing unnecessary dependencies.
● 3NF: We removed transitive dependencies by creating new tables for StaffContactInfo and
PublisherContactInfo, isolating contact information from their respective main tables.