Database System
CS-408
Submitted to : Mam Raheela
Naseem
Group No :
Muhammad Hussain Bari 23-ag-
10292 Syed Taimoor Ahsan 23-ag-
10335
Muhammad Saim Naeem 23-ag-
10302
Muhammad Rehan 23-ag-10300
Relational Tables Based on ERD
Library Management System
Library Table :
Attribute Data Type Description Constraint
Library_ID Integer Unique ID of the Primary Key
library (PK)
Name Varchar Name of the library Not Null
Address Varchar Address of the -
library
Contact_Numb Varchar Contact number -
er
Book Table :
Data
Attribute Type Description Constraint
Book_ID Integer Unique ID of the book Primary Key (PK)
Title Varchar Title of the book Not Null
Author Varchar Author of the book -
Genre Varchar Genre of the book -
Status Varchar Availability status Check constraint
(Available/Borrowed)
Library to which the
Library_ID Integer book belongs Foreign Key (FK)
Member Table :
Data
Attribute Type Description Constraint
Primary
Member_ID Integer Unique ID of the member Key
(PK)
Name Varchar Name of the member Not Null
Address Varchar Address of the member -
Contact_Numbe Varchar Member's contact number -
r
Email Varchar Email address -
Date when the
Membership_Da Date member joined -
te
Librarian Table :
Data
Attribute Type Description Constraint
Librarian_ID Integer Unique ID of the Primary Key (PK)
librarian
Name Varchar Name of the librarian -
Librarian's contact
Contact_Numb Varchar number -
er
Email Varchar Email address -
Library_ID Integer Associated library Foreign Key (FK)
Borrowing Table :
Data
Attribute Type Description Constraint
Primary
Borrowing_I Integer Unique ID of the borrowing Key
D (PK)
Member_ID Integer Member who borrowed the Foreign Key
book (FK)
Book_ID Integer Book that was borrowed Foreign Key
(FK)
Date when the book was
Borrow_Dat Date borrowed -
e
Due_Date Date Due date for return -
Return Table :
Attribute Data Type Description Constraint
Return_ID Integer Unique ID of the return Primary Key (PK)
Borrowing_I Integer Associated borrowing Foreign Key (FK)
D record
Return_Dat Date Date of return -
e
Fine_ID Integer Fine associated (if any) Foreign
Key(nullable)
Fine Table :
Data
Attribute Type Description Constraint
Fine_ID Integer Unique ID of the fine Primary Key (PK)
Member_ID Integer Member who is fined Foreign Key (FK)
Amount Decimal Fine amount -
Paid_Status Varchar Whether paid or Check (Paid/Unpaid)
unpaid
Normalization of Tables
Let’s now add formality and structure to the normalization section with well-
presented tables showing the transformation from:
Unnormalized Form (UNF)
First Normal Form (1NF)
Second Normal Form (2NF)
Third Normal Form (3NF)
We’ll base this on borrow-return-fine data, which is the best candidate for
demonstrating normalization.
Normalization Example: Borrowing, Return & Fine
1. Unnormalized Form (UNF)
UNF has multi-valued attributes and data redundancy.
Retur
Borrowing Member Borrow Due_ n_Dat Fine_Amoun
_ID _Name Book_Title _Date Date t Fine_Status
e
401 Ali Khan Python 101 2024-03- 2024- 2024- 200 Unpaid
01 03-15 03-20
402 Sana Clean Code 2024-03- 2024- 2024- 0 Paid
Raza 05 03-20 03-18
2. First Normal Form (1NF)
Remove repeating groups and ensure atomic values.
But it still has partial and transitive dependencies.
Borrowin Member_ Book_Titl Borrow Due_D Return_ Fine_Am
g_ID Name e _Date ate Date ount Fine_Status
2024-03- 2024- 2024-03-
401 Ali Khan Python 200 Unpaid
01 03-15 20
101
2024-03- 2024- 2024-03-
402 Sana Clean 0 Paid
05 03-20 18
Raza Code
3. Second Normal Form (2NF)
Remove partial dependencies by separating repeating entities.
Borrowing Table :
Borrowing_ID Member_ID Book_ID Borrow_Date Due_Date
401 201 101 2024-03-01 2024-03-15
402 202 102 2024-03-05 2024-03-20
Return Table :
Return_ID Borrowing_ID Return_Date Fine_ID
501 401 2024-03-20 601
502 402 2024-03-18 N/A
Fine Table :
Fine_ID Amount Paid_Status
601 200 Unpaid
But there’s still a transitive dependency in the Fine table:
Paid_Status depends on Fine_ID, not on Member_ID.
4. Third Normal Form (3NF)
Remove transitive dependencies.
We now move Member_ID to the Fine table to show who paid it.
Final Fine Table (3NF)
Fine_ID Member_ID Amount Paid_Status
601 201 200 Unpaid
Now everything depends directly on the primary key of each table —
all are in 3NF.
Conclusion
In this documentation, we explored the design and normalization of a Library
Management System database, covering essential aspects from entity
identification to relational schema creation and normalization up to Third
Normal Form (3NF).
Through the normalization process, we ensured that all data in the system is
logically organized, redundancy is minimized, and data integrity is
maintained. By separating composite information into distinct, well-structured
tables—such as Borrowing, Return, and Fine—we eliminated partial and transitive
dependencies that could compromise data consistency.
Normalization not only enhances the efficiency of data storage and retrieval but
also ensures that future updates, deletions, and insertions can be performed
without anomalies. With clearly defined primary keys, foreign keys, and
constraints, this structured relational model forms a solid foundation for
implementing the database in any RDBMS.
This approach makes the Library Management System scalable, reliable, and
easier to maintain—ensuring a robust and optimized backend for managing all
library operations effectively.
