ER Diagram Case Study

Build a complete ER diagram for BookVault's database system, translating business requirements into entity relationships and creating the final SQL schema.

BookVault Business Requirements

Every successful database starts with understanding the business. BookVault operates as an online bookstore where customers browse books, place orders, and receive their purchases.

Here's what BookVault needs to track. Customers create accounts with personal information like name, email, and location. They can place multiple orders over time. Each order contains one or more books, and customers can order different quantities of each book.

Books have authors who write them. Some books have multiple authors, and authors can write multiple books. Books also belong to categories like Fiction, Science, or Business. Categories can have subcategories - Romance falls under Fiction, for example.

BookVault tracks inventory levels, pricing, and order fulfillment status. When customers place orders, the system needs to record what was ordered, when, and for how much.

Identifying Entities and Attributes

From these requirements, we identify six core entities. Each represents something important that BookVault needs to store information about.

We also need categories for organizing books and order_items to handle the many-to-many relationship between orders and books.

Each entity needs attributes - the specific pieces of information we store. Customers need names and email addresses. Books need titles and prices. Orders need dates and status information.

Complete ER Diagram

Here's the complete ER diagram for BookVault. Each box represents an entity with its attributes listed inside. Lines show relationships between entities.

Notice how order_items sits between orders and books. This junction entity resolves the many-to-many relationship - one order can contain many books, and one book can appear in many orders.

Relationship Analysis

Let's analyze each relationship and its cardinality. Understanding these connections drives the entire database structure.

Books connect to authors in a one-to-many relationship. For simplicity, we assume each book has one primary author. Books also belong to one category, though categories can have subcategories through the parent_category_id self-referencing foreign key.

Creating the SQL Schema

Now we translate our ER diagram into SQL. We start with entities that don't depend on others, then add tables with foreign keys.

-- BookVault: Independent entities first
-- These tables have no foreign key dependencies

CREATE TABLE authors (
    author_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    nationality VARCHAR(50)
);

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    city VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Table 'authors' created successfully
Table 'customers' created successfully

-- BookVault: Categories with self-referencing relationship
-- parent_category_id allows subcategories

CREATE TABLE categories (
    category_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    parent_category_id INTEGER,
    FOREIGN KEY (parent_category_id) REFERENCES categories(category_id)
);

-- BookVault: Books depend on authors and categories
CREATE TABLE books (
    book_id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    author_id INTEGER NOT NULL,
    category_id INTEGER NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock_qty INTEGER DEFAULT 0,
    published_year INTEGER,
    FOREIGN KEY (author_id) REFERENCES authors(author_id),
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
);

Table 'categories' created successfully
Table 'books' created successfully

Categories can reference themselves for subcategories. Fiction might be a main category, while Romance has Fiction as its parent. Books must have both an author and category - these foreign keys are marked NOT NULL.

-- BookVault: Orders depend on customers
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) DEFAULT 'pending',
    total_amount DECIMAL(10,2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- BookVault: Order items resolve many-to-many relationship
-- This junction table connects orders and books
CREATE TABLE order_items (
    order_item_id SERIAL PRIMARY KEY,
    order_id INTEGER NOT NULL,
    book_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL DEFAULT 1,
    unit_price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (book_id) REFERENCES books(book_id),
    UNIQUE(order_id, book_id)  -- Prevent duplicate book in same order
);

Table 'orders' created successfully
Table 'order_items' created successfully

Testing the Schema

Let's add sample data to verify our design works. A good ER diagram should handle realistic business scenarios without issues.

-- BookVault: Sample data to test our schema
-- Insert authors first (no dependencies)
INSERT INTO authors (first_name, last_name, nationality) VALUES
('Stephen', 'King', 'American'),
('Agatha', 'Christie', 'British'),
('Haruki', 'Murakami', 'Japanese');

-- Insert categories with hierarchy
INSERT INTO categories (name, parent_category_id) VALUES
('Fiction', NULL),          -- Main category
('Mystery', NULL),          -- Main category  
('Horror', 1),              -- Subcategory of Fiction
('Detective', 2);           -- Subcategory of Mystery

-- Insert customers
INSERT INTO customers (first_name, last_name, email, city) VALUES
('John', 'Smith', 'john@example.com', 'New York'),
('Sarah', 'Davis', 'sarah@example.com', 'London');

3 rows inserted into 'authors'
4 rows inserted into 'categories'  
2 rows inserted into 'customers'

-- BookVault: Complete order scenario
-- Books with proper foreign key references
INSERT INTO books (title, author_id, category_id, price, stock_qty, published_year) VALUES
('The Shining', 1, 3, 15.99, 50, 1977),
('Murder on the Orient Express', 2, 4, 12.99, 30, 1934),
('Norwegian Wood', 3, 1, 18.50, 25, 1987);

-- Customer places an order
INSERT INTO orders (customer_id, total_amount, status) VALUES
(1, 28.98, 'confirmed');

-- Order contains two books with different quantities  
INSERT INTO order_items (order_id, book_id, quantity, unit_price) VALUES
(1, 1, 1, 15.99),  -- One copy of The Shining
(1, 2, 1, 12.99);  -- One copy of Murder on the Orient Express

3 rows inserted into 'books'
1 row inserted into 'orders'
2 rows inserted into 'order_items'

Our schema works perfectly. The foreign keys maintain data integrity - you can't create an order for a non-existent customer or add a book that doesn't exist to an order. The junction table cleanly handles the many-to-many relationship while storing important details like quantity and unit price.

Common Design Patterns

BookVault demonstrates several key database design patterns. These appear in most real-world systems, from Netflix's content catalog to Uber's ride tracking.

The self-referencing foreign key in categories creates hierarchical data. Amazon uses this pattern for product categories. Spotify organizes playlists this way. The pattern works whenever you need parent-child relationships within the same entity type.

Status fields like order.status track business processes. Orders flow from 'pending' to 'confirmed' to 'shipped' to 'delivered'. This creates an audit trail and enables business reporting. Stripe does this for payments, Airbnb for bookings.

Schema Evolution

Real databases evolve. BookVault might add reviews, wishlists, or discounts later. Good ER design anticipates change without over-engineering upfront.

Want to add book reviews? Create a reviews table with foreign keys to customers and books. Need discount codes? Add a promotions table and reference it in orders. The existing schema structure supports these additions cleanly.

But don't add features you don't need yet. YAGNI (You Aren't Gonna Need It) applies to database design. Start with core business requirements. Extend when business needs become clear.

Quiz