1 Library
1. mock table:
Borrow Borro Studen Studen Studen Boo Book Due Remar Rema
ID w t t t kID Title Date k rk
Date ID Name Phone ID Descr
iption
B001 2026- S101 Anish 98111 BK Database 2026- R01 Late
05-10 Sharm 11111 01, System, 05-14 Retur
a BK0 Operating n
2 System
B002 2026- S102 Riya 98222 BK Computer 2026- R02 No
05-11 Thapa 22222 03, Network, 05-17 Fine
BK0 Java
4 Programmi
ng
B003 2026- S103 Suma 98333 BK Web 2026- R03 Dam
05-12 n KC 33333 05, Developme 05-18 aged
BK0 nt, Data Cover
6 Mining
The mock table contains repeating group this creates
data redundancy
insertion anomaly
update anomaly
deletion anomaly
Thus, normalization is required.
Relationships Before UNF
Student (1:M) Borrow
Borrow (M:M) Book
Borrow (1:M) Remark
2. LibraryBorrowing_UNF
(BorrowID, BorrowDate,StudentID, StudentName, Section,{BookID, BookTitle, Author,
DueDate},{RemarkID, Remark})
The relation contains repeating groups:
{BookID, BookTitle, Author}
{RemarkID, Remark}
3. First Normal Form (1NF)
Condition
Remove repeating groups
Ensure atomic values
Introduce composite keys where required
Borrow-1
(BorrowID, BorrowDate, StudentID, StudentName, Section)
here in borrow book there is repeating so making new table for that,
Borrow_Book-1
(BorrowID*, BookID, BookTitle, Author, DueDate)
again, in remarks is repeating group so making new table for that,
Borrow_Remark-1
(BorrowID*, RemarkID, Remark)
All repeating groups are removed and atomic values are achieved.
4. Second Normal Form (2NF)
Condition
Remove partial dependency
Non-key attributes must fully depend on the whole composite key
Checking Borrow_Book-1
BookID → BookTitle, Author
Partial dependency exists.
FullFunctional Dependency
BorrowID,BookIDDueDate
Thus:
Borrow_Book-2
(BorrowID*, BookID*, DueDate)
Book-2
(BookID, BookTitle, Author)
Checking Borrow_Remark-1
Borrow_Remark-1
(RemarkID, BorrowID*, Remark)
Functional Dependency:
RemarkID → Remark
No partial dependency exists because there is no composite primary key.
Thus relation remains same in 2NF.
Borrow-2
(BorrowID, BorrowDate, StudentID, StudentName, Section)
No composite key exists, so relation remains same.
5. Third Normal Form (3NF)
Condition
Remove transitive dependency
Non-key attributes depend only on primary key
Checking Borrow-2
BorrowID → StudentID
StudentID → StudentName, Class
Transitive dependency exists.
Thus:
Borrow-3
(BorrowID, BorrowDate, StudentID*)
Student-3
(StudentID, StudentName, Section)
Final 3NF Relations
Corrected Final 3NF
Student
(StudentID, StudentName,Section)
Book
(BookID, BookTitle, Author)
Borrow
(BorrowID, BorrowDate, StudentID*)
BorrowBook
(BorrowID*, BookID*, DueDate)
BorrowRemark
(RemarkID, BorrowID*, Remark)
2 Doctor
1. Unnormalized Form (UNF)
HospitalAppointment_UNF
(AppointmentID, AppointmentDate,
PatientID, PatientName, PatientPhone,
{DoctorID, DoctorName, Specialization},
{RemarkID, RemarkDescription, RemarkType})
The relation contains repeating groups:
{DoctorID, DoctorName, Specialization}
{RemarkID, RemarkDescription, RemarkType}
2. First Normal Form (1NF)
Condition
Remove repeating groups
Ensure atomic values
Introduce composite keys where required
Appointment-1
(AppointmentID, AppointmentDate, PatientID, PatientName, PatientPhone)
Appointment_Doctor-1
(AppointmentID*, DoctorID, DoctorName, Specialization)
Composite Key:
(AppointmentID, DoctorID)
Appointment_Remark-1
(AppointmentID*, RemarkID, RemarkDescription, RemarkType)
Composite Key:
(AppointmentID, RemarkID)
3. Second Normal Form (2NF)
Condition
Remove partial dependency
Non-key attributes must fully depend on whole composite key
Checking Appointment_Doctor-1
DoctorID → DoctorName, Specialization
Partial dependency exists.
Thus:
Appointment_Doctor-2
(AppointmentID*, DoctorID*)
Doctor-2
(DoctorID, DoctorName, Specialization)
Checking Appointment_Remark-1
RemarkID → RemarkDescription, RemarkType
Partial dependency exists.
Thus:
Appointment_Remark-2
(AppointmentID*, RemarkID*)
Remark-2
(RemarkID, RemarkDescription, RemarkType)
Checking Appointment-1
AppointmentID → PatientID, PatientName, PatientPhone, AppointmentDate
No composite key exists.
Thus relation remains same.
Appointment-2
(AppointmentID, AppointmentDate, PatientID, PatientName, PatientPhone)
4. Third Normal Form (3NF)
Condition
Remove transitive dependency
Non-key attributes depend only on primary key
Checking Appointment-2
AppointmentID → PatientID
PatientID → PatientName, PatientPhone
Transitive dependency exists.
Thus:
Appointment-3
(AppointmentID, AppointmentDate, PatientID*)
Patient-3
(PatientID, PatientName, PatientPhone)
Refining Remark Relationship
Appointment and Remark have One-to-Many relationship.
Thus bridge relation is unnecessary.
Therefore:
Remark-3
(RemarkID, AppointmentID*, RemarkDescription, RemarkType)
Final 3NF Relations
Patient
(PatientID, PatientName, PatientPhone)
Doctor
(DoctorID, DoctorName, Specialization)
Appointment
(AppointmentID, AppointmentDate, PatientID*)
Appointment_Doctor
(AppointmentID*, DoctorID*)
Remark
(RemarkID, AppointmentID*, RemarkDescription, RemarkType)