CC-2141
Database Systems
Department of Computer Science
School of Systems & Technology - SST
CASE STUDY
In this unit you will learn to practice on database normalization for case:
➢Library management system
2
Library management system
▪ Un-normalized Database:
▪ Let's say we have a table which stores information about books borrowed by library members:
MemberID Member Name Member Address Book ID Book Title Author Date Borrowed
2024-04-01
101 John Abraham 96-Brooklyn Bridge 231 Harry Potter J.K. Rowling 2024-05-10
2024-07-15
102 Jane Claude 23-River Road Street 331 Lord of the Rings K.R.R. Tolkien 2024-04-02
101 John Abraham 96-Brooklyn Bridge 332 Lord of the Rings K.R.R. Tolkien 2024-04-03
101 John Abraham 96-Brooklyn Bridge 255 Java by Example Dietal & Dietal 2024-04-05
2024-04-10
102 Jane Claude 23-River Road Street 525 Cloud Computing Dr. Zee
2024-05-25
102 Jane Claude 23-River Road Street 256 Java by Example Dietal & Dietal 2024-04-15
3
UNF to 1NF
▪ Resolve multivalued cells (Each cell must have atomic value)
▪ Remove repeating groups in rows / columns
▪ Establish a primary key.
4
Conversion of UNF to 1NF
Resolve multivalued cells
MemberID Member Name Member Address Book ID Book Title Author Date Borrowed
101 John Abraham 96-Brooklyn Bridge 231 Harry Potter J.K. Rowling 2024-04-01
101 John Abraham 96-Brooklyn Bridge 231 Harry Potter J.K. Rowling 2024-05-10
101 John Abraham 96-Brooklyn Bridge 231 Harry Potter J.K. Rowling 2024-07-15
102 Jane Claude 23-River Road Street 331 Lord of the Rings K.R.R. Tolkien 2024-04-02
101 John Abraham 96-Brooklyn Bridge 332 Lord of the Rings K.R.R. Tolkien 2024-04-03
101 John Abraham 96-Brooklyn Bridge 255 Java by Example Dietal & Dietal 2024-04-05
102 Jane Claude 23-River Road Street 525 Cloud Computing Dr. Zee 2024-04-10
102 Jane Claude 23-River Road Street 525 Cloud Computing Dr. Zee 2024-05-25
102 Jane Claude 23-River Road Street 256 Java by Example Dietal & Dietal 2024-04-15
5
Conversion of UNF to 1NF
(Remove / Segregate repeating groups )
• (Like member address can change) It is an anomaly to change it in multiple rows.
• So, segregate Members table from Books data.
• Rename books table to Books Borrow and establish a primary key
Members Books Borrow
MemberID Member Name Member Address Borrow ID Book ID Book Title Author MemberID Date Borrowed
1 231 Harry Potter J.K. Rowling 101 2024-04-01
101 John Abraham 96-Brooklyn Bridge
2 231 Harry Potter J.K. Rowling 101 2024-05-10
102 Jane Claude 23-River Road Street 3 231 Harry Potter J.K. Rowling 101 2024-07-15
4 331 Lord of the Rings K.R.R. Tolkien 102 2024-04-02
5 332 Lord of the Rings K.R.R. Tolkien 101 2024-04-03
6 255 Java by Example Dietal & Dietal 101 2024-04-05
7 525 Cloud Computing Dr. Zee 102 2024-04-10
8 525 Cloud Computing Dr. Zee 102 2024-05-25
9 256 Java by Example Dietal & Dietal 102 2024-04-15
6
Conversion of 1NF to 2NF
Remove partial dependencies
Remove further repetition of data
• Book Title is partially dependent on Borrow ID and is also a repeating data.
• So, create a separate books table to solve partial dependency and save space.
Members
Books Borrow
MemberID Member Name Member Address
Borrow ID Book ID MemberID Date Borrowed
101 John Abraham 96-Brooklyn Bridge
1 231 101 2024-04-01
102 Jane Claude 23-River Road Street
2 231 101 2024-05-10
3 231 101 2024-07-15
Books 4 331 102 2024-04-02
Book ID Book Title Author
5 332 101 2024-04-03
231 Harry Potter J.K. Rowling
331 Lord of the Rings K.R.R. Tolkien 6 255 101 2024-04-05
332 Lord of the Rings K.R.R. Tolkien 7 525 102 2024-04-10
255 Java by Example Dietal & Dietal
8 525 102 2024-05-25
525 Cloud Computing Dr. Zee
256 Java by Example Dietal & Dietal 9 256 102 2024-04-15
7
Conversion of 2NF to 3NF
Remove transitive dependencies
Remove further repetition of data
• Author is transitively (indirectly) dependent on Book ID and is also a repeating data.
• So, create a separate Authors table to remove transitive dependency and save space.
Members Books Borrow
MemberID Member Name Member Address Borrow ID Book ID MemberID Date Borrowed
101 John Abraham 96-Brooklyn Bridge 1 231 101 2024-04-01
102 Jane Claude 23-River Road Street 2 231 101 2024-05-10
3 231 101 2024-07-15
4 331 102 2024-04-02
5 332 101 2024-04-03
6 255 101 2024-04-05
Books
7 525 102 2024-04-10
Book ID Book Title AuthorID
Authors 8 525 102 2024-05-25
231 Harry Potter 1
AuthorID Author
9 256 102 2024-04-15
331 Lord of the Rings 2
1 J.K. Rowling
332 Lord of the Rings 2
2 K.R.R. Tolkien
255 Java by Example 3
525 Cloud Computing 4
3 Dietal & Dietal • Normalized Database with 4 tables
4 Dr. Zee
256 Java by Example 3
8
Thankyou
Any Queries?