Build a Complete SQL Server Database From Scratch: A Capstone Project Walkthrough

Everything from the fundamentals track comes together here — no new syntax, just applying what you already know (data types, DDL/DML, queries, aggregates, joins, and constraints) to a realistic, slightly underspecified brief, the way a real task at work actually arrives. This lesson gives you the brief, the schema skeleton, and the design decisions to wrestle with — not the finished answer. Building it yourself, including getting some parts wrong first, is the actual point.

The Brief: BookNook

Design and build a database for a small online bookstore. It needs to track books, authors, customers, and orders.

• Author — name, country
• Book — title, price, publish_year, foreign key to Author
• Customer — name, unique email
• CustomerOrder — customer_id (FK), order_date, status
• OrderItem — the junction table connecting orders to books, since an order can contain many books and a book can appear in many orders

The Schema, Visualized

OrderItem is the piece most beginners miss on their first attempt — a many-to-many relationship (Book ↔ Order) always resolves through a junction table like this, never a direct link between the two. This is exactly the Enrollment pattern from Chapter 5, applied to a new domain.

A Skeleton to Start From — You Fill In the Constraints

Deliberately incomplete: the columns are given, but the exact PK/FK/CHECK/DEFAULT choices are yours to decide and justify, based on everything Chapters 2 and 6 covered.

CREATE TABLE dbo.Author (
    author_id   INT IDENTITY(1,1) PRIMARY KEY,
    full_name   NVARCHAR(100) NOT NULL,
    country     NVARCHAR(50)  NOT NULL
);

CREATE TABLE dbo.Book (
    book_id       INT IDENTITY(1,1) PRIMARY KEY,
    title         NVARCHAR(200) NOT NULL,
    author_id     INT NOT NULL REFERENCES dbo.Author(author_id),
    price         DECIMAL(8,2)  NOT NULL, -- what CHECK belongs here?
    publish_year  INT NOT NULL
);

CREATE TABLE dbo.Customer (
    customer_id  INT IDENTITY(1,1) PRIMARY KEY,
    full_name    NVARCHAR(100) NOT NULL,
    email        NVARCHAR(100) NOT NULL -- what constraint makes this genuinely unique?
);

CREATE TABLE dbo.CustomerOrder (
    order_id     INT IDENTITY(1,1) PRIMARY KEY,
    customer_id  INT NOT NULL REFERENCES dbo.Customer(customer_id),
    order_date   DATE NOT NULL, -- what DEFAULT saves you typing this every time?
    status       NVARCHAR(20) NOT NULL -- what DEFAULT status makes sense for a brand-new order?
);

CREATE TABLE dbo.OrderItem (
    order_id    INT NOT NULL REFERENCES dbo.CustomerOrder(order_id),
    book_id     INT NOT NULL REFERENCES dbo.Book(book_id),
    quantity    INT NOT NULL, -- what CHECK prevents a nonsensical quantity?
    unit_price  DECIMAL(8,2) NOT NULL,
    PRIMARY KEY (order_id, book_id) -- why a composite key here, specifically?
);

A Real Design Decision You’ll Have to Make

Should OrderItem.unit_price duplicate Book.price, or should you just JOIN to Book for the price at query time? Prices change over time — what should an order from six months ago show, today’s price or the price actually paid at purchase? This is a genuine, common denormalization decision (echoing Chapter 6’s normalization lesson) — not a mistake to avoid. The right answer here is almost certainly to duplicate it: a historical order should show what was actually paid, not today’s price. Storing it directly on OrderItem is deliberate denormalization for a good reason, exactly the kind of exception the normalization lesson told you to expect.

What Your Submission Needs

1. All five CREATE TABLE statements with appropriate PK/FK/CHECK/DEFAULT constraints — fill in every blank left above, with a one-line comment justifying each constraint choice
2. Realistic sample data — at least 4 authors, 8 books, 5 customers, 6 orders, 10 order items
3. A query showing each customer’s total spend across all orders (needs JOIN + GROUP BY + SUM)
4. A query showing the best-selling book by total quantity ordered (needs JOIN + GROUP BY + SUM + ORDER BY + TOP)
5. A query showing authors who’ve never had a book ordered — careful with the LEFT JOIN + WHERE trap from Chapter 5

Self-Check Before You Consider It Done

Stretch Goal: Deploy It for Real

Everything above works identically on your local install — but try creating this exact database on Azure SQL Database or AWS RDS (Chapter 0) instead of locally. All the same CREATE TABLE and INSERT statements work unchanged; only how you connect changes.