Why normalization eliminates data anomalies in DBMS Theory - See It in Action

Understanding Why Normalization Eliminates Data Anomalies

📖 Scenario: You are managing a small library database that stores information about books, authors, and publishers. Initially, all information is stored in one big table.

🎯 Goal: Learn how to organize data by splitting the big table into smaller related tables to avoid problems like duplicate data and inconsistent updates.

📋 What You'll Learn

Create a table called Library with columns BookID, BookTitle, AuthorName, and PublisherName with sample data

Create a variable called duplicate_check to identify repeated author names

Split the Library table into two tables: Books and Authors to remove repeated author names

Add a foreign key AuthorID in Books to link to Authors and complete the normalized structure

💡 Why This Matters

🌍 Real World

Databases in libraries, stores, and companies use normalization to keep data clean and avoid mistakes.

💼 Career

Database administrators and developers must understand normalization to design reliable and efficient databases.

1

Create the initial Library table with repeated data

Create a dictionary called Library with these exact entries: 1: {'BookTitle': 'Python Basics', 'AuthorName': 'Alice', 'PublisherName': 'TechBooks'}, 2: {'BookTitle': 'Advanced Python', 'AuthorName': 'Alice', 'PublisherName': 'TechBooks'}, 3: {'BookTitle': 'Data Science', 'AuthorName': 'Bob', 'PublisherName': 'DataPub'}

DBMS Theory

# Create the Library dictionary with book details
# Your code here

Need a hint?

Use a dictionary with keys as book IDs and values as another dictionary with book details.

2

Identify repeated author names

Create a list called duplicate_check that contains all AuthorName values from the Library dictionary

DBMS Theory

Library = {
    1: {'BookTitle': 'Python Basics', 'AuthorName': 'Alice', 'PublisherName': 'TechBooks'},
    2: {'BookTitle': 'Advanced Python', 'AuthorName': 'Alice', 'PublisherName': 'TechBooks'},
    3: {'BookTitle': 'Data Science', 'AuthorName': 'Bob', 'PublisherName': 'DataPub'}
}
# Create duplicate_check list with all AuthorName values
# Your code here

Need a hint?

Use a list comprehension to collect all author names from the Library dictionary.

3

Split Library into Books and Authors tables

Create two dictionaries: Authors with keys as author IDs and values as author names, and Books with keys as book IDs and values as dictionaries containing BookTitle and AuthorID linking to Authors. Use 1 for Alice and 2 for Bob as author IDs.

DBMS Theory

Library = {
    1: {'BookTitle': 'Python Basics', 'AuthorName': 'Alice', 'PublisherName': 'TechBooks'},
    2: {'BookTitle': 'Advanced Python', 'AuthorName': 'Alice', 'PublisherName': 'TechBooks'},
    3: {'BookTitle': 'Data Science', 'AuthorName': 'Bob', 'PublisherName': 'DataPub'}
}
duplicate_check = [Library[book]['AuthorName'] for book in Library]
# Create Authors and Books dictionaries to remove repeated author names
# Your code here

Need a hint?

Assign unique IDs to authors and link books to authors by these IDs.

4

Add Publisher table and link to Books

Create a dictionary called Publishers with keys as publisher IDs and values as publisher names: 1: 'TechBooks', 2: 'DataPub'. Then add a PublisherID key to each book in Books linking to the correct publisher ID.

DBMS Theory

Library = {
    1: {'BookTitle': 'Python Basics', 'AuthorName': 'Alice', 'PublisherName': 'TechBooks'},
    2: {'BookTitle': 'Advanced Python', 'AuthorName': 'Alice', 'PublisherName': 'TechBooks'},
    3: {'BookTitle': 'Data Science', 'AuthorName': 'Bob', 'PublisherName': 'DataPub'}
}
duplicate_check = [Library[book]['AuthorName'] for book in Library]
Authors = {
    1: 'Alice',
    2: 'Bob'
}
Books = {
    1: {'BookTitle': 'Python Basics', 'AuthorID': 1},
    2: {'BookTitle': 'Advanced Python', 'AuthorID': 1},
    3: {'BookTitle': 'Data Science', 'AuthorID': 2}
}
# Create Publishers dictionary and add PublisherID to Books
# Your code here

Need a hint?

Assign unique IDs to publishers and link each book to its publisher by ID.