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.

What Is a Relational Database? RDBMS Concepts Explained for Developers

Before writing a single SELECT statement, you need the mental model. Get this right and everything else in SQL Server clicks into place much faster — most beginner confusion later in this course traces back to a shaky version of what’s covered here.

Tables, Rows, and Columns

A relational database stores data in tables — grids of rows and columns, like a spreadsheet, except every row follows the same strict structure and tables can reference each other. SQL Server is a Relational Database Management System (RDBMS): the software that stores, protects, and lets you query that data.

• Table — a named collection of rows with the same columns (e.g. Customer)
• Row (record, or formally a tuple) — one entity: one customer, one order
• Column (field/attribute) — one property every row has: name, email
• Schema — the overall structure: which tables exist, their columns, types, and the rules connecting them

Why “Relational,” Specifically — The Math Behind the Name

The term comes from set theory: a table is mathematically a relation — a set of tuples (rows). This isn’t just trivia; it explains real SQL Server behavior. A true mathematical set has no inherent order and no duplicate members — which is exactly why a SQL table has no guaranteed row order unless you explicitly add ORDER BY, and why operations like UNION (versus UNION ALL) exist specifically to remove duplicates, echoing set semantics. Beginners who assume “the rows come back in the order I inserted them” get bitten by this constantly — SQL Server makes no such promise, ever.

How Tables Relate to Each Other

The “relational” part means tables relate to each other through shared values, not through nested/embedded structure like you’d see in a document database (MongoDB) or a spreadsheet with merged tabs. A customer_id in the Order table points back to a row in Customer — that’s a foreign key reference, formalized fully in Chapter 5. This is fundamentally different from how a NoSQL document store would model the same data (embedding the customer’s info directly inside every order document, duplicated across orders) — the relational approach trades some query complexity (you must JOIN to see combined data) for zero duplication and guaranteed consistency.

The Three Classic Relationship Shapes

You’ll build a real one-to-many relationship yourself starting in Chapter 5, and the full JOIN toolkit for querying across them right after.

OLTP vs OLAP, in More Than One Paragraph

SQL Server is usually used two ways:

• OLTP (Online Transaction Processing) — lots of small, fast reads/writes, like an e-commerce checkout or a support-ticket system. Schema is normalized (Chapter 6) to avoid duplicate/inconsistent data, since data changes constantly.
• OLAP (Online Analytical Processing) — fewer, much heavier queries that aggregate huge amounts of history for reporting and dashboards. Schemas here are often deliberately denormalized for read speed, since the data is mostly historical and rarely changes.

Beginners almost always start with OLTP-style querying and design, which is the foundation either way — you can’t design a good reporting schema until you understand why the transactional schema it’s summarizing looks the way it does.