Mini Project

-- Junction table for Books-Authors many-to-many relationship
-- Eliminates redundancy and maintains data integrity
CREATE TABLE book_authors (
    book_id INT,
    author_id INT,
    author_order SMALLINT DEFAULT 1,
    PRIMARY KEY (book_id, author_id),
    FOREIGN KEY (book_id) REFERENCES books(book_id),
    FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

-- Create BookVault database schema
-- Start with customers - the foundation of our business
CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    city VARCHAR(50),
    phone VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Categories table with self-referencing hierarchy
-- Supports parent/child category structure (Fiction -> Mystery)
CREATE TABLE categories (
    category_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    parent_category_id INT,
    description TEXT,
    FOREIGN KEY (parent_category_id) REFERENCES categories(category_id)
);

-- Books table - the heart of our inventory system
-- Includes business-critical fields like stock and pricing
CREATE TABLE books (
    book_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    isbn VARCHAR(13) UNIQUE,
    category_id INT NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock_qty INT DEFAULT 0,
    published_year YEAR,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(category_id),
    INDEX idx_title (title),
    INDEX idx_category (category_id),
    INDEX idx_stock (stock_qty)
);

-- Create composite index for common query patterns
-- Customers often search: "Show me books in Fiction under $20"
CREATE INDEX idx_category_price ON books(category_id, price);

-- Orders are frequently queried by customer and date
CREATE INDEX idx_customer_date ON orders(customer_id, order_date);

-- Email lookups need to be lightning fast for login
CREATE UNIQUE INDEX idx_customer_email ON customers(email);

-- Test query: Find all mystery books under $25
-- This query hits our composite index perfectly
EXPLAIN SELECT b.title, b.price, c.name as category
FROM books b
JOIN categories c ON b.category_id = c.category_id
WHERE c.name = 'Mystery' AND b.price < 25.00
ORDER BY b.price;

-- Reviews table with rating and helpful votes
-- Supports Amazon-style review system
CREATE TABLE reviews (
    review_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    book_id INT NOT NULL,
    rating TINYINT CHECK (rating BETWEEN 1 AND 5),
    review_text TEXT,
    helpful_votes INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    FOREIGN KEY (book_id) REFERENCES books(book_id),
    UNIQUE KEY unique_customer_book (customer_id, book_id)
);

-- Analytics view for business intelligence
-- Calculates average ratings and review counts
CREATE VIEW book_analytics AS
SELECT 
    b.book_id,
    b.title,
    b.price,
    b.stock_qty,
    COALESCE(AVG(r.rating), 0) as avg_rating,
    COUNT(r.review_id) as review_count,
    SUM(oi.quantity) as total_sold
FROM books b
LEFT JOIN reviews r ON b.book_id = r.book_id
LEFT JOIN order_items oi ON b.book_id = oi.book_id
GROUP BY b.book_id, b.title, b.price, b.stock_qty;

-- Insert sample data to test relationships
-- Real data reveals design flaws quickly
INSERT INTO customers (first_name, last_name, email, city) VALUES
('John', 'Doe', 'john@example.com', 'New York'),
('Jane', 'Smith', 'jane@example.com', 'Chicago');

INSERT INTO categories (name, description) VALUES
('Fiction', 'Fictional literature'),
('Mystery', 'Mystery and thriller books'),
('Programming', 'Computer programming guides');

INSERT INTO authors (first_name, last_name, nationality) VALUES
('Agatha', 'Christie', 'British'),
('Robert', 'Martin', 'American');