+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
Part 483 of 541

๐Ÿ“˜ Database Design: Normalization

Master database design: normalization in Python with practical examples, best practices, and real-world applications ๐Ÿš€

๐Ÿš€Intermediate
25 min read

Prerequisites

  • Basic understanding of programming concepts ๐Ÿ“
  • Python installation (3.8+) ๐Ÿ
  • VS Code or preferred IDE ๐Ÿ’ป

What you'll learn

  • Understand the concept fundamentals ๐ŸŽฏ
  • Apply the concept in real projects ๐Ÿ—๏ธ
  • Debug common issues ๐Ÿ›
  • Write clean, Pythonic code โœจ

๐ŸŽฏ Introduction

Welcome to this exciting tutorial on database normalization! ๐ŸŽ‰ In this guide, weโ€™ll explore how to design efficient, scalable databases that wonโ€™t give you headaches later.

Youโ€™ll discover how normalization can transform your messy data into a well-organized system. Whether youโ€™re building an e-commerce platform ๐Ÿ›’, a social media app ๐Ÿ“ฑ, or a library management system ๐Ÿ“š, understanding normalization is essential for creating databases that grow gracefully with your application.

By the end of this tutorial, youโ€™ll feel confident designing normalized databases in your own projects! Letโ€™s dive in! ๐ŸŠโ€โ™‚๏ธ

๐Ÿ“š Understanding Database Normalization

๐Ÿค” What is Database Normalization?

Database normalization is like organizing your closet ๐Ÿ—„๏ธ. Instead of throwing all your clothes in one big pile, you separate them into categories: shirts in one drawer, pants in another, and socks in their own space. This makes finding what you need much easier!

In database terms, normalization is the process of organizing data to minimize redundancy and improve data integrity. This means you can:

  • โœจ Eliminate duplicate data
  • ๐Ÿš€ Update information in one place
  • ๐Ÿ›ก๏ธ Maintain data consistency
  • ๐Ÿ’พ Save storage space

๐Ÿ’ก Why Use Normalization?

Hereโ€™s why developers love normalized databases:

  1. Data Integrity ๐Ÿ”’: No conflicting information
  2. Efficient Updates ๐Ÿ’ป: Change data once, update everywhere
  3. Storage Optimization ๐Ÿ“Š: Less duplicate data means less space
  4. Query Performance ๐Ÿ”ง: Well-structured data queries faster

Real-world example: Imagine an online bookstore ๐Ÿ“š. Without normalization, youโ€™d store the authorโ€™s full details with every book. If the author changes their email, youโ€™d have to update it in hundreds of places!

๐Ÿ”ง Basic Normalization Forms

๐Ÿ“ First Normal Form (1NF)

Letโ€™s start with the basics - 1NF requires:

  • Each column contains atomic (indivisible) values
  • Each column contains values of a single type
  • Each column has a unique name
  • Order doesnโ€™t matter
# ๐Ÿ‘‹ Hello, Normalization!
import sqlite3
import pandas as pd

# โŒ Not in 1NF - multiple values in one field
bad_table = {
    'student_id': [1, 2],
    'name': ['Alice', 'Bob'],
    'courses': ['Math,Physics,Chemistry', 'English,History']  # ๐Ÿ’ฅ Multiple values!
}

# โœ… In 1NF - atomic values
good_table_students = {
    'student_id': [1, 1, 1, 2, 2],
    'name': ['Alice', 'Alice', 'Alice', 'Bob', 'Bob'],
    'course': ['Math', 'Physics', 'Chemistry', 'English', 'History']
}

# ๐ŸŽจ Creating tables in Python
conn = sqlite3.connect(':memory:')
cursor = conn.cursor()

# Create normalized table
cursor.execute('''
    CREATE TABLE student_courses (
        student_id INTEGER,
        student_name TEXT,
        course TEXT
    )
''')

print("โœ… Table in 1NF created successfully!")

๐ŸŽฏ Second Normal Form (2NF)

2NF builds on 1NF and requires:

  • Be in 1NF
  • All non-key attributes depend on the entire primary key
# ๐Ÿ—๏ธ Moving to 2NF
# โŒ Not in 2NF - student_name depends only on student_id
cursor.execute('''
    CREATE TABLE bad_enrollments (
        student_id INTEGER,
        course_id INTEGER,
        student_name TEXT,  -- ๐Ÿ’ฅ Depends only on student_id!
        course_name TEXT,   -- ๐Ÿ’ฅ Depends only on course_id!
        grade TEXT,
        PRIMARY KEY (student_id, course_id)
    )
''')

# โœ… In 2NF - separate tables
cursor.execute('''
    CREATE TABLE students (
        student_id INTEGER PRIMARY KEY,
        student_name TEXT
    )
''')

cursor.execute('''
    CREATE TABLE courses (
        course_id INTEGER PRIMARY KEY,
        course_name TEXT
    )
''')

cursor.execute('''
    CREATE TABLE enrollments (
        student_id INTEGER,
        course_id INTEGER,
        grade TEXT,
        PRIMARY KEY (student_id, course_id),
        FOREIGN KEY (student_id) REFERENCES students(student_id),
        FOREIGN KEY (course_id) REFERENCES courses(course_id)
    )
''')

print("๐ŸŽ‰ Database normalized to 2NF!")

๐Ÿš€ Third Normal Form (3NF)

3NF requires:

  • Be in 2NF
  • No transitive dependencies (non-key attributes depend only on the primary key)
# ๐ŸŽจ Achieving 3NF
# โŒ Not in 3NF - city depends on zip_code, not on student_id
cursor.execute('''
    CREATE TABLE bad_students (
        student_id INTEGER PRIMARY KEY,
        name TEXT,
        zip_code TEXT,
        city TEXT,      -- ๐Ÿ’ฅ Depends on zip_code!
        state TEXT      -- ๐Ÿ’ฅ Also depends on zip_code!
    )
''')

# โœ… In 3NF - separate location data
cursor.execute('''
    CREATE TABLE zip_codes (
        zip_code TEXT PRIMARY KEY,
        city TEXT,
        state TEXT
    )
''')

cursor.execute('''
    CREATE TABLE students_3nf (
        student_id INTEGER PRIMARY KEY,
        name TEXT,
        zip_code TEXT,
        FOREIGN KEY (zip_code) REFERENCES zip_codes(zip_code)
    )
''')

print("โœจ Achieved 3NF - no transitive dependencies!")

๐Ÿ’ก Practical Examples

๐Ÿ›’ Example 1: E-Commerce Database

Letโ€™s design a normalized e-commerce database:

# ๐Ÿ›๏ธ E-Commerce Database Design
def create_ecommerce_db():
    conn = sqlite3.connect('ecommerce.db')
    cursor = conn.cursor()
    
    # ๐Ÿ‘ค Customers table
    cursor.execute('''
        CREATE TABLE customers (
            customer_id INTEGER PRIMARY KEY AUTOINCREMENT,
            email TEXT UNIQUE NOT NULL,
            name TEXT NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ''')
    
    # ๐Ÿ“ฆ Products table
    cursor.execute('''
        CREATE TABLE products (
            product_id INTEGER PRIMARY KEY AUTOINCREMENT,
            sku TEXT UNIQUE NOT NULL,
            name TEXT NOT NULL,
            price DECIMAL(10,2) NOT NULL,
            stock_quantity INTEGER DEFAULT 0
        )
    ''')
    
    # ๐Ÿ›’ Orders table
    cursor.execute('''
        CREATE TABLE orders (
            order_id INTEGER PRIMARY KEY AUTOINCREMENT,
            customer_id INTEGER NOT NULL,
            order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            total_amount DECIMAL(10,2),
            status TEXT DEFAULT 'pending',
            FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        )
    ''')
    
    # ๐Ÿ“‹ Order items table (junction table)
    cursor.execute('''
        CREATE TABLE order_items (
            order_id INTEGER,
            product_id INTEGER,
            quantity INTEGER NOT NULL,
            unit_price DECIMAL(10,2) NOT NULL,
            PRIMARY KEY (order_id, product_id),
            FOREIGN KEY (order_id) REFERENCES orders(order_id),
            FOREIGN KEY (product_id) REFERENCES products(product_id)
        )
    ''')
    
    print("๐Ÿ›’ E-commerce database created with proper normalization!")
    
    # ๐ŸŽฎ Let's add some sample data
    cursor.execute("INSERT INTO customers (email, name) VALUES (?, ?)", 
                   ('[email protected]', 'Alice Johnson'))
    cursor.execute("INSERT INTO products (sku, name, price, stock_quantity) VALUES (?, ?, ?, ?)", 
                   ('BOOK-001', 'Python Mastery ๐Ÿ“˜', 29.99, 100))
    
    conn.commit()
    return conn

# Create and test the database
conn = create_ecommerce_db()

๐ŸŽฎ Example 2: Game Leaderboard System

Letโ€™s create a normalized game leaderboard:

# ๐Ÿ† Game Leaderboard Database
class GameDatabase:
    def __init__(self):
        self.conn = sqlite3.connect('game_leaderboard.db')
        self.cursor = self.conn.cursor()
        self.setup_tables()
    
    def setup_tables(self):
        # ๐ŸŽฎ Players table
        self.cursor.execute('''
            CREATE TABLE IF NOT EXISTS players (
                player_id INTEGER PRIMARY KEY AUTOINCREMENT,
                username TEXT UNIQUE NOT NULL,
                email TEXT UNIQUE NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')
        
        # ๐ŸŽฏ Games table
        self.cursor.execute('''
            CREATE TABLE IF NOT EXISTS games (
                game_id INTEGER PRIMARY KEY AUTOINCREMENT,
                game_name TEXT UNIQUE NOT NULL,
                max_score INTEGER DEFAULT 0,
                difficulty_levels TEXT NOT NULL
            )
        ''')
        
        # ๐Ÿ† Achievements table
        self.cursor.execute('''
            CREATE TABLE IF NOT EXISTS achievements (
                achievement_id INTEGER PRIMARY KEY AUTOINCREMENT,
                game_id INTEGER,
                name TEXT NOT NULL,
                description TEXT,
                points INTEGER DEFAULT 0,
                emoji TEXT,
                FOREIGN KEY (game_id) REFERENCES games(game_id)
            )
        ''')
        
        # ๐Ÿ“Š Scores table
        self.cursor.execute('''
            CREATE TABLE IF NOT EXISTS scores (
                score_id INTEGER PRIMARY KEY AUTOINCREMENT,
                player_id INTEGER,
                game_id INTEGER,
                score INTEGER NOT NULL,
                level INTEGER DEFAULT 1,
                played_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (player_id) REFERENCES players(player_id),
                FOREIGN KEY (game_id) REFERENCES games(game_id)
            )
        ''')
        
        # ๐ŸŒŸ Player achievements (junction table)
        self.cursor.execute('''
            CREATE TABLE IF NOT EXISTS player_achievements (
                player_id INTEGER,
                achievement_id INTEGER,
                earned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (player_id, achievement_id),
                FOREIGN KEY (player_id) REFERENCES players(player_id),
                FOREIGN KEY (achievement_id) REFERENCES achievements(achievement_id)
            )
        ''')
        
        self.conn.commit()
        print("๐ŸŽฎ Game database normalized and ready!")
    
    def add_player(self, username, email):
        try:
            self.cursor.execute(
                "INSERT INTO players (username, email) VALUES (?, ?)",
                (username, email)
            )
            self.conn.commit()
            print(f"โœจ Player {username} joined the game!")
            return self.cursor.lastrowid
        except sqlite3.IntegrityError:
            print(f"โš ๏ธ Player {username} already exists!")
            return None
    
    def record_score(self, player_id, game_id, score, level=1):
        self.cursor.execute(
            "INSERT INTO scores (player_id, game_id, score, level) VALUES (?, ?, ?, ?)",
            (player_id, game_id, score, level)
        )
        self.conn.commit()
        print(f"๐ŸŽฏ Score recorded: {score} points!")
        
        # Check for achievements
        self.check_achievements(player_id, game_id, score)
    
    def check_achievements(self, player_id, game_id, score):
        # ๐Ÿ† Simple achievement check
        if score >= 1000:
            print("๐ŸŽ‰ Achievement unlocked: Score Master! ๐Ÿ†")
    
    def get_leaderboard(self, game_id, limit=10):
        query = '''
            SELECT p.username, MAX(s.score) as high_score
            FROM scores s
            JOIN players p ON s.player_id = p.player_id
            WHERE s.game_id = ?
            GROUP BY p.player_id
            ORDER BY high_score DESC
            LIMIT ?
        '''
        
        results = self.cursor.execute(query, (game_id, limit)).fetchall()
        
        print("\n๐Ÿ† LEADERBOARD ๐Ÿ†")
        for i, (username, score) in enumerate(results, 1):
            emoji = "๐Ÿฅ‡" if i == 1 else "๐Ÿฅˆ" if i == 2 else "๐Ÿฅ‰" if i == 3 else "๐ŸŽฎ"
            print(f"{emoji} {i}. {username}: {score} points")

# ๐ŸŽฎ Test the game database
game_db = GameDatabase()

# Add some players
player1 = game_db.add_player("SpeedyGamer", "[email protected]")
player2 = game_db.add_player("ProPlayer", "[email protected]")

# Add a game
game_db.cursor.execute(
    "INSERT INTO games (game_name, max_score, difficulty_levels) VALUES (?, ?, ?)",
    ("Space Invaders ๐Ÿš€", 999999, "Easy,Medium,Hard,Insane")
)
game_id = game_db.cursor.lastrowid

# Record some scores
if player1:
    game_db.record_score(player1, game_id, 1250, 3)
if player2:
    game_db.record_score(player2, game_id, 980, 2)

# Show leaderboard
game_db.get_leaderboard(game_id)

๐Ÿš€ Advanced Normalization Concepts

๐Ÿง™โ€โ™‚๏ธ Boyce-Codd Normal Form (BCNF)

When youโ€™re ready to level up, BCNF is stricter than 3NF:

# ๐ŸŽฏ BCNF Example
# Consider a university course scheduling system

# โŒ Not in BCNF - professor determines department
cursor.execute('''
    CREATE TABLE bad_course_assignments (
        course_id INTEGER,
        professor_id INTEGER,
        department TEXT,  -- ๐Ÿ’ฅ Depends on professor_id!
        room_number TEXT,
        PRIMARY KEY (course_id, professor_id)
    )
''')

# โœ… In BCNF - separate professor information
cursor.execute('''
    CREATE TABLE professors (
        professor_id INTEGER PRIMARY KEY,
        professor_name TEXT,
        department TEXT
    )
''')

cursor.execute('''
    CREATE TABLE course_assignments (
        course_id INTEGER,
        professor_id INTEGER,
        room_number TEXT,
        PRIMARY KEY (course_id),
        FOREIGN KEY (professor_id) REFERENCES professors(professor_id)
    )
''')

print("โœจ Achieved BCNF - every determinant is a candidate key!")

๐Ÿ—๏ธ Denormalization: When to Break the Rules

Sometimes, for performance, you might intentionally denormalize:

# ๐Ÿš€ Strategic denormalization for performance
class PerformanceOptimizedDB:
    def __init__(self):
        self.conn = sqlite3.connect(':memory:')
        self.cursor = self.conn.cursor()
    
    def create_normalized_schema(self):
        # ๐Ÿ˜ด Fully normalized - might be slow for complex queries
        self.cursor.execute('''
            CREATE TABLE users (
                user_id INTEGER PRIMARY KEY,
                username TEXT
            )
        ''')
        
        self.cursor.execute('''
            CREATE TABLE posts (
                post_id INTEGER PRIMARY KEY,
                user_id INTEGER,
                content TEXT,
                created_at TIMESTAMP,
                FOREIGN KEY (user_id) REFERENCES users(user_id)
            )
        ''')
        
        self.cursor.execute('''
            CREATE TABLE likes (
                user_id INTEGER,
                post_id INTEGER,
                PRIMARY KEY (user_id, post_id)
            )
        ''')
    
    def create_denormalized_schema(self):
        # ๐Ÿš€ Denormalized for read performance
        self.cursor.execute('''
            CREATE TABLE posts_denormalized (
                post_id INTEGER PRIMARY KEY,
                user_id INTEGER,
                username TEXT,      -- ๐ŸŽฏ Denormalized!
                content TEXT,
                created_at TIMESTAMP,
                like_count INTEGER DEFAULT 0  -- ๐ŸŽฏ Denormalized!
            )
        ''')
        
        # ๐Ÿ“Š Use triggers to maintain consistency
        self.cursor.execute('''
            CREATE TRIGGER update_like_count
            AFTER INSERT ON likes
            BEGIN
                UPDATE posts_denormalized 
                SET like_count = like_count + 1
                WHERE post_id = NEW.post_id;
            END
        ''')
        
        print("๐Ÿš€ Denormalized for blazing fast reads!")

# Example usage
perf_db = PerformanceOptimizedDB()
perf_db.create_denormalized_schema()

โš ๏ธ Common Pitfalls and Solutions

๐Ÿ˜ฑ Pitfall 1: Over-Normalization

# โŒ Too normalized - splitting natural attributes
cursor.execute('''
    CREATE TABLE bad_persons (
        person_id INTEGER PRIMARY KEY,
        first_name_id INTEGER,  -- ๐Ÿ’ฅ Over-normalized!
        last_name_id INTEGER,   -- ๐Ÿ’ฅ This is too much!
        FOREIGN KEY (first_name_id) REFERENCES first_names(id),
        FOREIGN KEY (last_name_id) REFERENCES last_names(id)
    )
''')

# โœ… Properly normalized - keep related data together
cursor.execute('''
    CREATE TABLE persons (
        person_id INTEGER PRIMARY KEY,
        first_name TEXT,
        last_name TEXT,
        email TEXT UNIQUE
    )
''')

print("โœ… Keep it simple - don't over-engineer!")

๐Ÿคฏ Pitfall 2: Update Anomalies

# โŒ Update anomaly risk - redundant data
def demonstrate_update_anomaly():
    # Create problematic table
    cursor.execute('''
        CREATE TABLE bad_employee_projects (
            emp_id INTEGER,
            emp_name TEXT,
            emp_dept TEXT,  -- ๐Ÿ’ฅ Repeated for each project!
            project_id INTEGER,
            project_name TEXT
        )
    ''')
    
    # Insert data
    cursor.execute('''
        INSERT INTO bad_employee_projects VALUES 
        (1, 'Alice', 'Engineering', 101, 'Website'),
        (1, 'Alice', 'Engineering', 102, 'Mobile App'),
        (1, 'Alice', 'Enginering', 103, 'API')  -- ๐Ÿ˜ฑ Typo creates inconsistency!
    ''')
    
    print("โš ๏ธ Update anomaly detected - inconsistent department names!")

# โœ… Solution: Normalize properly
def fix_with_normalization():
    cursor.execute('''
        CREATE TABLE employees (
            emp_id INTEGER PRIMARY KEY,
            emp_name TEXT,
            emp_dept TEXT
        )
    ''')
    
    cursor.execute('''
        CREATE TABLE employee_projects (
            emp_id INTEGER,
            project_id INTEGER,
            PRIMARY KEY (emp_id, project_id),
            FOREIGN KEY (emp_id) REFERENCES employees(emp_id)
        )
    ''')
    
    print("โœ… Fixed - update department in one place only!")

๐Ÿ› ๏ธ Best Practices

  1. ๐ŸŽฏ Start with 3NF: Itโ€™s usually sufficient for most applications
  2. ๐Ÿ“Š Analyze Your Queries: Denormalize only when performance requires it
  3. ๐Ÿ›ก๏ธ Use Constraints: Foreign keys, unique constraints, and checks
  4. ๐Ÿ“ Document Everything: Explain why you made certain design decisions
  5. โœจ Keep It Simple: Donโ€™t over-engineer your schema
# ๐Ÿ† Best practices example
def create_best_practice_schema():
    cursor.execute('''
        -- ๐Ÿ“ Document your tables!
        CREATE TABLE customers (
            customer_id INTEGER PRIMARY KEY AUTOINCREMENT,
            email TEXT UNIQUE NOT NULL,  -- ๐Ÿ›ก๏ธ Constraints ensure data quality
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            
            CHECK (email LIKE '%@%')  -- โœ… Basic email validation
        )
    ''')
    
    cursor.execute('''
        CREATE INDEX idx_customer_email  -- ๐Ÿš€ Index for performance
        ON customers(email)
    ''')
    
    print("๐ŸŽฏ Schema follows best practices!")

create_best_practice_schema()

๐Ÿงช Hands-On Exercise

๐ŸŽฏ Challenge: Design a Library Management System

Create a normalized database for a library that tracks:

๐Ÿ“‹ Requirements:

  • โœ… Books with title, ISBN, publication year
  • ๐Ÿท๏ธ Authors (books can have multiple authors)
  • ๐Ÿ‘ค Members who can borrow books
  • ๐Ÿ“… Loan records with due dates
  • ๐ŸŽจ Book categories and genres

๐Ÿš€ Bonus Points:

  • Add a reservation system
  • Track late fees
  • Implement book ratings

๐Ÿ’ก Solution

๐Ÿ” Click to see solution
# ๐ŸŽฏ Library Management System - Normalized Design!
class LibraryDatabase:
    def __init__(self):
        self.conn = sqlite3.connect('library.db')
        self.cursor = self.conn.cursor()
        self.create_schema()
    
    def create_schema(self):
        # ๐Ÿ“š Books table
        self.cursor.execute('''
            CREATE TABLE books (
                book_id INTEGER PRIMARY KEY AUTOINCREMENT,
                isbn TEXT UNIQUE NOT NULL,
                title TEXT NOT NULL,
                publication_year INTEGER,
                total_copies INTEGER DEFAULT 1,
                available_copies INTEGER DEFAULT 1
            )
        ''')
        
        # ๐Ÿ‘ค Authors table
        self.cursor.execute('''
            CREATE TABLE authors (
                author_id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                birth_year INTEGER
            )
        ''')
        
        # ๐Ÿ”— Book-Author junction table (many-to-many)
        self.cursor.execute('''
            CREATE TABLE book_authors (
                book_id INTEGER,
                author_id INTEGER,
                PRIMARY KEY (book_id, author_id),
                FOREIGN KEY (book_id) REFERENCES books(book_id),
                FOREIGN KEY (author_id) REFERENCES authors(author_id)
            )
        ''')
        
        # ๐Ÿท๏ธ Categories table
        self.cursor.execute('''
            CREATE TABLE categories (
                category_id INTEGER PRIMARY KEY AUTOINCREMENT,
                category_name TEXT UNIQUE NOT NULL,
                emoji TEXT DEFAULT '๐Ÿ“–'
            )
        ''')
        
        # ๐Ÿ”— Book-Category junction table
        self.cursor.execute('''
            CREATE TABLE book_categories (
                book_id INTEGER,
                category_id INTEGER,
                PRIMARY KEY (book_id, category_id),
                FOREIGN KEY (book_id) REFERENCES books(book_id),
                FOREIGN KEY (category_id) REFERENCES categories(category_id)
            )
        ''')
        
        # ๐Ÿ‘ฅ Members table
        self.cursor.execute('''
            CREATE TABLE members (
                member_id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                email TEXT UNIQUE NOT NULL,
                phone TEXT,
                join_date DATE DEFAULT CURRENT_DATE,
                membership_status TEXT DEFAULT 'active'
            )
        ''')
        
        # ๐Ÿ“‹ Loans table
        self.cursor.execute('''
            CREATE TABLE loans (
                loan_id INTEGER PRIMARY KEY AUTOINCREMENT,
                book_id INTEGER NOT NULL,
                member_id INTEGER NOT NULL,
                loan_date DATE DEFAULT CURRENT_DATE,
                due_date DATE NOT NULL,
                return_date DATE,
                late_fee DECIMAL(5,2) DEFAULT 0,
                FOREIGN KEY (book_id) REFERENCES books(book_id),
                FOREIGN KEY (member_id) REFERENCES members(member_id)
            )
        ''')
        
        # ๐ŸŒŸ Ratings table
        self.cursor.execute('''
            CREATE TABLE ratings (
                member_id INTEGER,
                book_id INTEGER,
                rating INTEGER CHECK (rating >= 1 AND rating <= 5),
                review TEXT,
                rating_date DATE DEFAULT CURRENT_DATE,
                PRIMARY KEY (member_id, book_id),
                FOREIGN KEY (member_id) REFERENCES members(member_id),
                FOREIGN KEY (book_id) REFERENCES books(book_id)
            )
        ''')
        
        # ๐Ÿ“… Reservations table
        self.cursor.execute('''
            CREATE TABLE reservations (
                reservation_id INTEGER PRIMARY KEY AUTOINCREMENT,
                book_id INTEGER NOT NULL,
                member_id INTEGER NOT NULL,
                reservation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                status TEXT DEFAULT 'pending',
                FOREIGN KEY (book_id) REFERENCES books(book_id),
                FOREIGN KEY (member_id) REFERENCES members(member_id)
            )
        ''')
        
        # ๐Ÿš€ Create indexes for performance
        self.cursor.execute('CREATE INDEX idx_loans_member ON loans(member_id)')
        self.cursor.execute('CREATE INDEX idx_loans_due_date ON loans(due_date)')
        self.cursor.execute('CREATE INDEX idx_books_isbn ON books(isbn)')
        
        self.conn.commit()
        print("๐Ÿ“š Library database created successfully!")
    
    def add_book(self, isbn, title, year, authors, categories):
        # Add book
        self.cursor.execute(
            "INSERT INTO books (isbn, title, publication_year) VALUES (?, ?, ?)",
            (isbn, title, year)
        )
        book_id = self.cursor.lastrowid
        
        # Add authors
        for author_name in authors:
            self.cursor.execute(
                "INSERT OR IGNORE INTO authors (name) VALUES (?)",
                (author_name,)
            )
            self.cursor.execute(
                "SELECT author_id FROM authors WHERE name = ?",
                (author_name,)
            )
            author_id = self.cursor.fetchone()[0]
            
            self.cursor.execute(
                "INSERT INTO book_authors VALUES (?, ?)",
                (book_id, author_id)
            )
        
        # Add categories
        for category in categories:
            self.cursor.execute(
                "INSERT OR IGNORE INTO categories (category_name) VALUES (?)",
                (category,)
            )
            self.cursor.execute(
                "SELECT category_id FROM categories WHERE category_name = ?",
                (category,)
            )
            cat_id = self.cursor.fetchone()[0]
            
            self.cursor.execute(
                "INSERT INTO book_categories VALUES (?, ?)",
                (book_id, cat_id)
            )
        
        self.conn.commit()
        print(f"โœ… Added book: {title} ๐Ÿ“š")
    
    def borrow_book(self, member_id, isbn):
        # Check availability
        self.cursor.execute(
            "SELECT book_id, available_copies FROM books WHERE isbn = ?",
            (isbn,)
        )
        result = self.cursor.fetchone()
        
        if result and result[1] > 0:
            book_id, available = result
            
            # Create loan
            import datetime
            due_date = datetime.date.today() + datetime.timedelta(days=14)
            
            self.cursor.execute(
                "INSERT INTO loans (book_id, member_id, due_date) VALUES (?, ?, ?)",
                (book_id, member_id, due_date)
            )
            
            # Update availability
            self.cursor.execute(
                "UPDATE books SET available_copies = available_copies - 1 WHERE book_id = ?",
                (book_id,)
            )
            
            self.conn.commit()
            print(f"๐Ÿ“– Book borrowed! Due date: {due_date}")
        else:
            print("๐Ÿ˜ข Book not available")

# ๐ŸŽฎ Test the library system!
library = LibraryDatabase()

# Add some books
library.add_book(
    "978-0134685991", 
    "Effective Python", 
    2019,
    ["Brett Slatkin"],
    ["Programming", "Python"]
)

library.add_book(
    "978-1491946008",
    "Fluent Python",
    2015,
    ["Luciano Ramalho"],
    ["Programming", "Python", "Advanced"]
)

# Add a member
library.cursor.execute(
    "INSERT INTO members (name, email) VALUES (?, ?)",
    ("Alice Reader", "[email protected]")
)
member_id = library.cursor.lastrowid

# Borrow a book
library.borrow_book(member_id, "978-0134685991")

๐ŸŽ“ Key Takeaways

Youโ€™ve learned so much! Hereโ€™s what you can now do:

  • โœ… Understand normalization forms (1NF, 2NF, 3NF, BCNF) ๐Ÿ’ช
  • โœ… Design efficient database schemas that scale ๐Ÿ›ก๏ธ
  • โœ… Avoid common normalization pitfalls ๐ŸŽฏ
  • โœ… Know when to denormalize for performance ๐Ÿ›
  • โœ… Build real-world database applications with Python! ๐Ÿš€

Remember: Good database design is like building a strong foundation for a house. Take the time to get it right, and your application will thank you later! ๐Ÿค

๐Ÿค Next Steps

Congratulations! ๐ŸŽ‰ Youโ€™ve mastered database normalization!

Hereโ€™s what to do next:

  1. ๐Ÿ’ป Practice with the library management exercise
  2. ๐Ÿ—๏ธ Design a database for your own project idea
  3. ๐Ÿ“š Move on to our next tutorial: Database Connections and DB-API 2.0
  4. ๐ŸŒŸ Share your database designs with the community!

Remember: Every database expert started by learning normalization. Keep practicing, keep designing, and most importantly, have fun building awesome databases! ๐Ÿš€


Happy database designing! ๐ŸŽ‰๐Ÿš€โœจ