Domain: Library Management System
The library management system tracks books, authors, borrowers, and borrow transactions.
Initial Unnormalized Data (UNF):
| TransactionID | BorrowerName | BorrowerAddress | BorrowedBooks | TransactionDate |
|---|---|---|---|---|
| 1 | Sarah Johnson | 123 Elm St, NY | “Book1:Author1”, “Book2:Author2” | 2023-12-01 |
Full Library Management System Sample Data
Unnormalized Data (UNF):
| TransactionID | BorrowerName | BorrowerAddress | BorrowedBooks | TransactionDate |
|---|---|---|---|---|
| 1 | Sarah Johnson | 123 Elm St, NY | Book1:Author1, Book2:Author2 | 2023-12-01 |
| 2 | Mark Spencer | 456 Pine Rd, CA | Book3:Author1 | 2023-12-02 |
| 3 | Sarah Johnson | 123 Elm St, NY | Book4:Author3, Book5:Author2 | 2023-12-03 |
Goal: Normalize the data to 3NF
1NF: Remove multivalued attributes by creating individual rows for each book in the borrowed books column.
| TransactionID | BorrowerName | BorrowerAddress | BookTitle | AuthorName | TransactionDate |
|---|---|---|---|---|---|
| 1 | Sarah Johnson | 123 Elm St, NY | Book1 | Author1 | 2023-12-01 |
| 1 | Sarah Johnson | 123 Elm St, NY | Book2 | Author2 | 2023-12-01 |
| 2 | Mark Spencer | 456 Pine Rd, CA | Book3 | Author1 | 2023-12-02 |
| 3 | Sarah Johnson | 123 Elm St, NY | Book4 | Author3 | 2023-12-03 |
| 3 | Sarah Johnson | 123 Elm St, NY | Book5 | Author2 | 2023-12-03 |
erDiagram
Transaction {
int TransactionID PK
string BorrowerName
string BorrowerAddress
string BookTitle
string AuthorName
string TransactionDate
}2NF: Eliminate partial dependencies by separating Borrowers, Books, and Transactions into different tables.
Borrower Table:
| BorrowerID | BorrowerName | BorrowerAddress |
|---|---|---|
| 1 | Sarah Johnson | 123 Elm St, NY |
| 2 | Mark Spencer | 456 Pine Rd, CA |
Book Table:
| BookID | BookTitle | AuthorName |
|---|---|---|
| 1 | Book1 | Author1 |
| 2 | Book2 | Author2 |
| 3 | Book3 | Author1 |
| 4 | Book4 | Author3 |
| 5 | Book5 | Author2 |
Transaction Table:
| TransactionID | BorrowerID | TransactionDate |
|---|---|---|
| 1 | 1 | 2023-12-01 |
| 2 | 2 | 2023-12-02 |
| 3 | 1 | 2023-12-03 |
TransactionDetails Table:
| TransactionDetailID | TransactionID | BookID |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 2 | 3 |
| 4 | 3 | 4 |
| 5 | 3 | 5 |
erDiagram
Borrower {
int BorrowerID PK
string BorrowerName
string BorrowerAddress
}
Book {
int BookID PK
string BookTitle
string AuthorName
}
Transaction {
int TransactionID PK
int BorrowerID FK
string TransactionDate
}
TransactionDetails {
int TransactionDetailID PK
int TransactionID FK
int BookID FK
}
3NF: Eliminate transitive dependencies by creating a separate Author table.
Author Table:
| AuthorID | AuthorName |
|---|---|
| 1 | Author1 |
| 2 | Author2 |
| 3 | Author3 |
Book Table (Updated):
| BookID | BookTitle | AuthorID |
|---|---|---|
| 1 | Book1 | 1 |
| 2 | Book2 | 2 |
| 3 | Book3 | 1 |
| 4 | Book4 | 3 |
| 5 | Book5 | 2 |
erDiagram
Borrower {
int BorrowerID PK
string BorrowerName
string BorrowerAddress
}
Author {
int AuthorID PK
string AuthorName
}
Book {
int BookID PK
string BookTitle
int AuthorID FK
}
Transaction {
int TransactionID PK
int BorrowerID FK
string TransactionDate
}
TransactionDetails {
int TransactionDetailID PK
int TransactionID FK
int BookID FK
}
Book ||--o{ Author : "is written by"
Borrower ||--o{ Transaction : "borrows"
Transaction ||--o{ TransactionDetails : "contains"
Book ||--o{ TransactionDetails : "Book"
Summary
The data has been normalized from an unstructured format (UNF) to fully normalized (3NF), ensuring minimal redundancy, integrity, and consistency. This exercise can be applied to any domain requiring structured, relational data storage.