Repair a Messy Retail Database

Repair a flat retail sales log with SQL Server and build reliable reports.

Introduction

30 Second Summary

A sales log can look tidy until one customer's email appears in several rows. Change one copy, and nobody can tell which address is correct.

In this project, you will repair a small retail sales database that repeats customer and product details. You'll use SQL Server 2025 Express LocalDB plus SQL Server Management Studio 22 to turn that unreliable log into a protected relational model.

What You'll Build

You'll run your finished Transact-SQL reports to reveal every product purchase plus a customer spending ranking led by Maya Chen at 155.00.

By the end of this project, you'll have:

  • A flat sales log you can query before exposing an update anomaly with conflicting email values.
  • A normalized four-table model that gives every customer one dependable record.
  • Sales reports that reveal every purchased product plus a customer spending ranking.
  • Secret Mission: Package the customer summary as a reusable database view. Then query shoppers who spent at least 100.00 in highest-to-lowest order.

Are there any prerequisites?

A Windows 10 or later x64 PC with administrator access and internet access is required. The system drive also needs at least 6 GB of free space.

Before We Start

Before the hands-on work begins, lock in what you are building and why it matters. You will turn a repeated retail sales log into RetailRepairLab, a normalized database with reliable relationships, constraints, and reports.

Set Up LocalDB and SSMS

Your RetailRepairLab plan now needs a database engine that can run its future T-SQL scripts. You also need a query editor that shows the databases you create.

You will install SQL Server Express LocalDB, version 2025, as the local engine. You will use SQL Server Management Studio, version 22, to connect to that engine.

In this step, get ready to:
  • Prepare Windows for the database installers.
  • Install SQL Server 2025 Express LocalDB and SQL Server Management Studio 22.
  • Verify the LocalDB instance and connect to it from SSMS.
Check your PC and install LocalDB

LocalDB provides a real SQL Server engine with automatic instance management. This keeps your attention on relational modeling instead of server administration.

This installation changes software on your PC. The engine remains local and uses disk space without creating a paid cloud resource.

  • Confirm that Windows is updated.
  • Confirm that the PC runs Windows 10 or later.
  • Confirm that the processor architecture is x64.
  • Confirm that at least 6 GB is free on the system drive.
  • Confirm that you can run an installer as an administrator.

The LocalDB command gives you a quick check for an existing installation. At this point, it may show instance details or report that the tool is unavailable.

  • Press the Windows key to open Windows Search.
  • Type Command Prompt.
  • Press Enter to open Command Prompt.
  • Check the automatic LocalDB instance by running this command:
sqllocaldb info MSSQLLocalDB

What does this command check?

The sqllocaldb info MSSQLLocalDB command requests information about the automatic LocalDB instance named MSSQLLocalDB.

An existing installation reports details such as its version, owner, and state. An unavailable command means LocalDB still needs to be installed.

  • Choose the tab that matches the result in Command Prompt.

✔️ I see the required LocalDB version

You are ahead of the setup. The output confirms that the required LocalDB engine is already available for your Windows account.

ⓧ I see an older LocalDB version

Your existing LocalDB installation is older than the version used in this project. Install SQL Server 2025 Express LocalDB before continuing.

  • Open the Microsoft SQL Server downloads page.
  • Download the SQL Server 2025 Express media.
  • Run SQL Server Setup as an administrator.
  • Select LocalDB on the Feature Selection/Shared Features page.
  • Complete SQL Server Setup.

Setup installs the automatic MSSQLLocalDB instance for the verification later in this step.

ⓧ Command not found

The missing command confirms that SQL Server Express LocalDB is not installed. The Express download includes LocalDB as an installation feature.

  • Open the Microsoft SQL Server downloads page.
  • Download the SQL Server 2025 Express media.
  • Run SQL Server Setup as an administrator.
  • Select LocalDB on the Feature Selection/Shared Features page.
  • Complete SQL Server Setup.

Setup installs the automatic MSSQLLocalDB instance for your Windows account.

Setup does not finish?

  • Confirm that SQL Server Setup is running with administrator permissions.
  • Confirm that the system drive still has at least 6 GB of free space.
  • Check your internet connection before downloading the Express media again.
  • Help me troubleshoot my SQL Server Express LocalDB installation.
Install and open SSMS

SSMS gives you Object Explorer for navigating databases and a query editor for running scripts. Version 22 installs through Visual Studio Installer using the vs_SSMS.exe bootstrapper.

  • Press the Windows key to open Windows Search.
  • Type SQL Server Management Studio.
  • Choose the tab that matches the search result.

✔️ SSMS is ready

Windows Search shows SQL Server Management Studio 22. Your query editor is already installed.

  • Select SQL Server Management Studio 22 from the search results.

SSMS opens with Object Explorer available for a database connection.

ⓧ I see an older SSMS version

The installed SSMS version is older than the version used in this project. Install SSMS 22 before connecting to LocalDB.

  • Open the official SSMS installation page.
  • Download the bootstrapper named vs_SSMS.exe.
  • Run vs_SSMS.exe as an administrator.
  • Select Install in Visual Studio Installer.
  • Press the Windows key after installation completes.
  • Type SQL Server Management Studio 22.
  • Press Enter to open SSMS.

Optional workloads are not required for this project. SSMS opens with the tools needed to connect and run queries.

ⓧ SSMS is not installed

No SSMS result means the query editor still needs to be installed. The current installer uses a small bootstrapper that launches Visual Studio Installer.

  • Open the official SSMS installation page.
  • Download the bootstrapper named vs_SSMS.exe.
  • Run vs_SSMS.exe as an administrator.
  • Select Install in Visual Studio Installer.
  • Press the Windows key after installation completes.
  • Type SQL Server Management Studio 22.
  • Press Enter to open SSMS.

Optional workloads are not required for this project. SSMS opens with Object Explorer ready for your connection.

That is the desktop tooling sorted. Your local engine and query editor are ready to meet each other.

SSMS does not open?

  • Confirm that Visual Studio Installer finished the SSMS installation.
  • Run vs_SSMS.exe with administrator permissions if the installation stopped early.
  • Check your internet connection before restarting the installer.
  • Help me troubleshoot my SSMS installation.
Verify LocalDB and connect SSMS

The final check proves that Windows can find the automatic LocalDB instance. Connecting through Object Explorer proves that SSMS can use it.

  • Return to Command Prompt from your earlier LocalDB check.
  • Predict which state the automatic instance reports before you verify it.
  • Verify the MSSQLLocalDB instance by running this command:
sqllocaldb info MSSQLLocalDB

What confirms LocalDB is available?

The output identifies the MSSQLLocalDB instance. It also reports details including its version, owner, and state.

This result proves that the LocalDB command-line utility can find the automatic instance assigned to your Windows account.

You should see instance details for MSSQLLocalDB. Your local database engine is now installed and responding to its verification command.

  • Return to SSMS.
  • Select Connect in Object Explorer.
  • Select Database Engine.
  • Enter (localdb)\MSSQLLocalDB in the Server name field.
  • Select Windows Authentication in the Authentication field.
  • Select Connect.

Object Explorer shows the connected (localdb)\MSSQLLocalDB instance. This proves that SSMS can reach your local database engine.

First connection timed out?

The automatic LocalDB instance can take a few seconds to start during its first connection. Keep the same server name and authentication setting.

  • Wait a few seconds.
  • Select Connect again.
  • Help me troubleshoot my LocalDB connection in SSMS.
  • Select New Query in the SSMS toolbar.

You should see a blank query window connected to (localdb)\MSSQLLocalDB. That window is ready for the retail database script in the next step.

✔️ Awesome, I've got everything!

Your LocalDB instance responds in Command Prompt. SSMS is open with the connected instance visible in Object Explorer and a blank query window ready.

ⓧ I'd like to double check the full code

SQL Server 2025 Express LocalDB instance installed and verified with sqllocaldb info MSSQLLocalDB.

Installed, open, and connected to (localdb)\MSSQLLocalDB using Windows Authentication.

Your local SQL Server environment is ready. Next up, you will build the six-row flat sales log that exposes the database design problem.

Build the Flat Sales Log

Your LocalDB engine is connected through SQL Server Management Studio. You now need a database that you can populate and query yourself.

A single sales table produces a useful result quickly. Repeating each customer's details across purchases creates the weakness you will expose next.

In this step, get ready to:
  • Create the RetailRepairLab database with a flat sales table.
  • Populate dbo.SalesLog with six sample sales rows.
  • Inspect the rows for repeated customer and product facts.
Create the database and flat table

A database gives the retail lab its own container. The first Transact-SQL batches reset that container before making it active.

The setup intentionally removes any earlier RetailRepairLab database. Your new lab contains no work to lose at this point.

  • Save the New Query window from earlier as 01-flat-sales-log.sql using the query window's save control.
  • Add the database setup batches to 01-flat-sales-log.sql by pasting this code into the empty editor:
USE master;
GO

DROP DATABASE IF EXISTS RetailRepairLab;
GO

CREATE DATABASE RetailRepairLab;
GO

USE RetailRepairLab;
GO

What Do These Batches Do?

  • The opening batch switches execution to the system database so the lab can be reset safely.
  • The next batch removes an earlier copy of RetailRepairLab when one exists.
  • The creation batch builds a clean RetailRepairLab database.
  • Each GO separates one batch from the next in the SSMS query editor.
  • The final batch makes RetailRepairLab active for the table statements that follow.
  • Save 01-flat-sales-log.sql.
  • Run the current script by selecting Execute in the query toolbar.

You should see confirmation that every batch completed without an error. This proves that RetailRepairLab now exists.

Database Creation Failed?

  • Confirm that the query window is still connected to (localdb)\MSSQLLocalDB.
  • Close any other query window that is actively using RetailRepairLab if the database cannot be reset.
  • Check that every GO appears on its own line.
  • Ask for help with the failed database batches.

The database is ready for its first table. dbo.SalesLog stores one complete sales line in each row.

  • Append the table definition below the existing database setup in 01-flat-sales-log.sql by pasting this code:
CREATE TABLE dbo.SalesLog
(
    SalesLogID INT IDENTITY(1, 1) PRIMARY KEY,
    OrderNumber NVARCHAR(20) NOT NULL,
    OrderDate DATE NOT NULL,
    CustomerName NVARCHAR(100) NOT NULL,
    CustomerEmail NVARCHAR(255) NOT NULL,
    ProductName NVARCHAR(100) NOT NULL,
    Quantity INT NOT NULL CHECK (Quantity > 0),
    UnitPrice DECIMAL(10, 2) NOT NULL CHECK (UnitPrice >= 0)
);
GO

How Is the Sales Log Structured?

  • The primary key gives every sales row a unique SalesLogID.
  • The identity setting starts that ID at 1 before increasing it for each inserted row.
  • The order columns record which sale occurred.
  • The customer columns repeat the buyer's details inside each purchased product row.
  • The product columns record the item plus its quantity and price.
  • The value constraints require a positive quantity plus a non-negative price.
  • Save 01-flat-sales-log.sql.
  • Run the updated script by selecting Execute in the query toolbar.

The output area should confirm that the table batches completed without errors. Your empty dbo.SalesLog table is ready for data.

Table Creation Failed?

  • Run the entire script from its first line so the database is reset before the table is created.
  • Check that the closing parenthesis appears before the final batch separator.
  • Compare each column name with the table definition above.
  • Ask for help with the SalesLog table definition.
Insert the sample sales rows

Each sample row represents one product within an order. Orders with multiple products therefore repeat their order details across several rows.

  • Append the six sample rows below the table definition in 01-flat-sales-log.sql by pasting this code:
INSERT INTO dbo.SalesLog
(
    OrderNumber,
    OrderDate,
    CustomerName,
    CustomerEmail,
    ProductName,
    Quantity,
    UnitPrice
)
VALUES
    (N'SO-1001', '2026-01-05', N'Maya Chen', N'maya.old@example.com', N'Laptop Stand', 1, 45.00),
    (N'SO-1001', '2026-01-05', N'Maya Chen', N'maya.old@example.com', N'Wireless Mouse', 2, 25.00),
    (N'SO-1002', '2026-01-06', N'Jordan Lee', N'jordan@example.com', N'Laptop Stand', 1, 45.00),
    (N'SO-1003', '2026-01-07', N'Maya Chen', N'maya.old@example.com', N'USB-C Hub', 1, 60.00),
    (N'SO-1004', '2026-01-08', N'Sam Rivera', N'sam@example.com', N'Wireless Mouse', 3, 25.00),
    (N'SO-1004', '2026-01-08', N'Sam Rivera', N'sam@example.com', N'USB-C Hub', 1, 60.00);
GO

What Does This Insert Create?

  • The six value lists become six separate rows in dbo.SalesLog.
  • The two SO-1001 rows represent two products in Maya Chen's first order.
  • The two SO-1004 rows represent two products in Sam Rivera's order.
  • SQL Server generates each SalesLogID automatically.
  • Save 01-flat-sales-log.sql.
  • Run the updated script by selecting Execute in the query toolbar.

You should see a row-count confirmation for six inserted rows. The flat sales log now contains its complete sample dataset.

Six Rows Were Not Inserted?

  • Run the entire script so the database is recreated before the insert batch begins.
  • Check that every sample row is separated by a comma except the final row.
  • Confirm that the final value list ends with a closing parenthesis.
  • Ask for help with the six-row insert.
Inspect the repeated facts

The table works as a sales log because every row contains enough information to describe one purchase line. The result grid also reveals how often the same customer and product facts are stored again.

  • Append the detail query plus the row-count query to the bottom of 01-flat-sales-log.sql by pasting this code:
SELECT SalesLogID,
       OrderNumber,
       OrderDate,
       CustomerName,
       CustomerEmail,
       ProductName,
       Quantity,
       UnitPrice
FROM dbo.SalesLog
ORDER BY SalesLogID;

SELECT COUNT(*) AS SalesRowCount
FROM dbo.SalesLog;
GO

What Do These Queries Show?

  • The first query returns every sales row in generated ID order.
  • Its columns let you compare customer details with product details across different purchases.
  • The second query counts every row in dbo.SalesLog.
  • The SalesRowCount alias gives that count a clear heading in the result grid.
  • Save 01-flat-sales-log.sql.

Before you run the finished script, which customer names do you expect to appear more than once?

  • Run the complete script by selecting Execute in the query toolbar.

That worked. The first Results grid shows six sales rows.

The second Results grid shows SalesRowCount with the value 6.

  • Scan the first Results grid for repeated values in CustomerName and CustomerEmail.
  • Compare the repeated ProductName values with their UnitPrice values.

You should find Maya Chen in three rows plus Sam Rivera in two rows. Laptop Stand, Wireless Mouse, and USB-C Hub each appear twice.

Not Seeing Six Rows?

  • Run the entire file so the database reset occurs before the table and rows are recreated.
  • Confirm that all six value lists remain inside the insert batch.
  • Check that the row-count query targets dbo.SalesLog.
  • Ask for help with the final Results grids.

✔️ Awesome, I've got everything!

Your 01-flat-sales-log.sql file now contains the full flat-sales script. Running it rebuilds the lab and returns both result grids.

ⓧ I'd like to double check the full code

Here is the full 01-flat-sales-log.sql file for comparison.

USE master;
GO

DROP DATABASE IF EXISTS RetailRepairLab;
GO

CREATE DATABASE RetailRepairLab;
GO

USE RetailRepairLab;
GO

CREATE TABLE dbo.SalesLog
(
    SalesLogID INT IDENTITY(1, 1) PRIMARY KEY,
    OrderNumber NVARCHAR(20) NOT NULL,
    OrderDate DATE NOT NULL,
    CustomerName NVARCHAR(100) NOT NULL,
    CustomerEmail NVARCHAR(255) NOT NULL,
    ProductName NVARCHAR(100) NOT NULL,
    Quantity INT NOT NULL CHECK (Quantity > 0),
    UnitPrice DECIMAL(10, 2) NOT NULL CHECK (UnitPrice >= 0)
);
GO

INSERT INTO dbo.SalesLog
(
    OrderNumber,
    OrderDate,
    CustomerName,
    CustomerEmail,
    ProductName,
    Quantity,
    UnitPrice
)
VALUES
    (N'SO-1001', '2026-01-05', N'Maya Chen', N'maya.old@example.com', N'Laptop Stand', 1, 45.00),
    (N'SO-1001', '2026-01-05', N'Maya Chen', N'maya.old@example.com', N'Wireless Mouse', 2, 25.00),
    (N'SO-1002', '2026-01-06', N'Jordan Lee', N'jordan@example.com', N'Laptop Stand', 1, 45.00),
    (N'SO-1003', '2026-01-07', N'Maya Chen', N'maya.old@example.com', N'USB-C Hub', 1, 60.00),
    (N'SO-1004', '2026-01-08', N'Sam Rivera', N'sam@example.com', N'Wireless Mouse', 3, 25.00),
    (N'SO-1004', '2026-01-08', N'Sam Rivera', N'sam@example.com', N'USB-C Hub', 1, 60.00);
GO

SELECT SalesLogID,
       OrderNumber,
       OrderDate,
       CustomerName,
       CustomerEmail,
       ProductName,
       Quantity,
       UnitPrice
FROM dbo.SalesLog
ORDER BY SalesLogID;

SELECT COUNT(*) AS SalesRowCount
FROM dbo.SalesLog;
GO

Your flat sales log now works as a complete one-table system. Next, you will test what happens when one copy of a repeated customer fact changes.

Trigger the Update Anomaly

Your six-row sales log is queryable. The flat design looks useful at first.

Now you'll use a targeted Transact-SQL update to test one repeated customer fact. The test asks whether the database can still identify one trusted email afterward.

In this step, get ready to:
  • Create a targeted script that changes Maya Chen's email on one sales row.
  • Run a diagnostic query across every Maya Chen row.
Update one copy of Maya Chen's email

Each row in dbo.SalesLog stores sale facts beside customer facts. The WHERE clause lets you target one product row with precision.

  • In SQL Server Management Studio, create a blank query editor tab.
  • Save the blank query as 02-update-anomaly.sql.
  • Target Maya Chen's Laptop Stand row by pasting this code:
USE RetailRepairLab;
GO

UPDATE dbo.SalesLog
SET CustomerEmail = N'maya.new@example.com'
WHERE OrderNumber = N'SO-1001'
      AND ProductName = N'Laptop Stand';
GO

What does this update do?

  • The USE RetailRepairLab batch sets the database context for the script.
  • The UPDATE dbo.SalesLog statement changes the stored customer email.
  • The WHERE clause narrows the change to the SO-1001 Laptop Stand row.
  • Save 02-update-anomaly.sql.
  • Run the script by selecting Execute.

Good progress. SSMS reports that one row was affected.

Did the update affect a different number of rows?

  • Confirm that the query editor is connected to RetailRepairLab.
  • Match the filter values to SO-1001 for the order number plus Laptop Stand for the product name.
  • Help me troubleshoot the targeted SalesLog update.
Compare Maya Chen's email rows

The update completed successfully. A diagnostic SELECT now gathers every stored copy of Maya Chen's email.

  • Add the diagnostic query below the existing update by pasting this code:
SELECT SalesLogID,
       OrderNumber,
       CustomerName,
       CustomerEmail
FROM dbo.SalesLog
WHERE CustomerName = N'Maya Chen'
ORDER BY SalesLogID;
GO

What does this query check?

  • The WHERE CustomerName = N'Maya Chen' condition isolates Maya Chen's sales rows.
  • The ORDER BY SalesLogID clause keeps the rows in their original identity order.
  • The selected columns connect each email value to its sales row.
  • Save 02-update-anomaly.sql.

Before you run this check, pause to predict whether you will see one email value or several.

  • Highlight the final SELECT statement.
  • Run the highlighted query by selecting Execute.

You'll see three rows for Maya Chen. The Laptop Stand row contains maya.new@example.com.

The Wireless Mouse row contains maya.old@example.com. The USB-C Hub row contains the same old value.

What is an update anomaly?

This contradiction is the intended shortfall. An update anomaly occurs when one real-world fact is copied across several rows.

The dbo.SalesLog table now holds two email values for Maya Chen. The database has no authoritative email value for that customer.

Not seeing three Maya Chen rows?

  • Confirm that you highlighted the final SELECT statement before selecting Execute.
  • Check that the update filter still uses SO-1001 for OrderNumber plus Laptop Stand for ProductName.
  • Help me find the missing Maya Chen rows.

✔️ Awesome, I've got everything!

You have captured the broken state in 02-update-anomaly.sql. The script now demonstrates the conflicting customer fact.

ⓧ I'd like to double check the full code

  • Compare 02-update-anomaly.sql with the complete script below.
USE RetailRepairLab;
GO

UPDATE dbo.SalesLog
SET CustomerEmail = N'maya.new@example.com'
WHERE OrderNumber = N'SO-1001'
      AND ProductName = N'Laptop Stand';
GO

SELECT SalesLogID,
       OrderNumber,
       CustomerName,
       CustomerEmail
FROM dbo.SalesLog
WHERE CustomerName = N'Maya Chen'
ORDER BY SalesLogID;
GO

How is the script structured?

  • The first batch sets RetailRepairLab as the database context.
  • The second batch updates one matching sales row.
  • The final batch returns every Maya Chen row for comparison.

You've exposed the exact weakness hiding in the flat sales log. Next, you'll replace repeated facts with a normalized relational model that protects each relationship.

Normalize the Retail Database

The anomaly from the last step left Maya Chen with two email addresses. The flat table repeats each fact, so no row can serve as the authoritative customer record.

Normalization separates each kind of fact into a dedicated table. You will use Transact-SQL to connect those tables while SQL Server constraints protect the relationships.

In this step, get ready to:
  • Define four normalized tables.
  • Load consistent customer, product, order, and line-item data.
  • Verify one authoritative email value for each customer.
Define the normalized tables

Each table stores one kind of fact. This structure gives customers, products, and orders a single home while preserving the original conflicting dbo.SalesLog for comparison.

  • Switch back to SQL Server Management Studio from earlier.
  • Select New Query in the toolbar.

You should see a blank query editor connected to the LocalDB instance from earlier.

  • Press Ctrl+S to open the save dialog.

The save dialog asks where to store your new query file.

  • Enter 03-normalized-model.sql as the file name.
  • Select Save.

The editor tab now shows 03-normalized-model.sql as its name.

How does the model protect data?

A primary key gives each record a stable identity. A composite primary key uses two columns to identify each order-product combination.

A foreign key requires a matching parent record. A check constraint rejects values outside an allowed rule.

Selecting only the newest block can feel fiddly. This approach lets you confirm each table before moving to the next dependency.

  • Create the customer table by pasting this into the blank 03-normalized-model.sql editor:
USE RetailRepairLab;
GO

CREATE TABLE dbo.Customers
(
    CustomerID INT PRIMARY KEY,
    CustomerName NVARCHAR(100) NOT NULL,
    CustomerEmail NVARCHAR(255) NOT NULL UNIQUE
);
GO

What does the customer table protect?

  • The database context places the new table inside RetailRepairLab.
  • CustomerID identifies each customer record.
  • CustomerEmail requires one non-null value that is unique across customers.
  • GO ends each batch so SSMS can execute the next section separately.
  • Save 03-normalized-model.sql.
  • Highlight the code from USE RetailRepairLab through the first GO after dbo.Customers.
  • Select Execute in the query toolbar.

The selected batches should finish without an error. This confirms that dbo.Customers now exists with one place for each email address.

Customer table creation failed?

  • Confirm that the first line uses RetailRepairLab as the database context.
  • Run this table block only once because a second creation attempt finds the existing table.
  • Help me troubleshoot the customer table creation.
  • Add the product table by pasting this at the bottom of 03-normalized-model.sql:
CREATE TABLE dbo.Products
(
    ProductID INT PRIMARY KEY,
    ProductName NVARCHAR(100) NOT NULL UNIQUE,
    CurrentPrice DECIMAL(10, 2) NOT NULL,
    CONSTRAINT CK_Products_CurrentPrice CHECK (CurrentPrice >= 0)
);
GO

What does the product table protect?

  • ProductID gives each product a stable identifier.
  • ProductName prevents duplicate product names through its uniqueness rule.
  • CK_Products_CurrentPrice prevents a price below zero.
  • Save 03-normalized-model.sql.
  • Highlight only the CREATE TABLE dbo.Products block.
  • Select Execute in the query toolbar.

The selected batch should finish without an error. The database now has one protected record for each product.

Product table creation failed?

  • Confirm that the highlighted selection includes the complete dbo.Products definition.
  • Check that CK_Products_CurrentPrice contains matching parentheses.
  • Help me troubleshoot the product table creation.
  • Add the order table by pasting this at the bottom of 03-normalized-model.sql:
CREATE TABLE dbo.Orders
(
    OrderID INT PRIMARY KEY,
    OrderNumber NVARCHAR(20) NOT NULL UNIQUE,
    OrderDate DATE NOT NULL,
    CustomerID INT NOT NULL,
    CONSTRAINT FK_Orders_Customers
        FOREIGN KEY (CustomerID) REFERENCES dbo.Customers(CustomerID)
);
GO

How do orders connect to customers?

  • OrderID identifies one order.
  • OrderNumber prevents the same business order number from appearing twice.
  • FK_Orders_Customers requires every order to reference an existing customer.
  • Save 03-normalized-model.sql.
  • Highlight only the CREATE TABLE dbo.Orders block.
  • Select Execute in the query toolbar.

The selected batch should finish without an error. Each future order must now point to a valid customer record.

Order table creation failed?

  • Confirm that dbo.Customers was created before this table.
  • Check that both references use CustomerID with identical capitalization.
  • Help me troubleshoot the order table creation.
  • Add the order-item table by pasting this at the bottom of 03-normalized-model.sql:
CREATE TABLE dbo.OrderItems
(
    OrderID INT NOT NULL,
    ProductID INT NOT NULL,
    Quantity INT NOT NULL,
    UnitPrice DECIMAL(10, 2) NOT NULL,
    CONSTRAINT PK_OrderItems PRIMARY KEY (OrderID, ProductID),
    CONSTRAINT FK_OrderItems_Orders
        FOREIGN KEY (OrderID) REFERENCES dbo.Orders(OrderID),
    CONSTRAINT FK_OrderItems_Products
        FOREIGN KEY (ProductID) REFERENCES dbo.Products(ProductID),
    CONSTRAINT CK_OrderItems_Quantity CHECK (Quantity > 0),
    CONSTRAINT CK_OrderItems_UnitPrice CHECK (UnitPrice >= 0)
);
GO

Why use a composite key?

  • PK_OrderItems combines OrderID with ProductID to identify one product line within an order.
  • The two foreign keys require matching order records and product records.
  • The quantity check requires a value above zero.
  • The unit-price check allows zero while rejecting negative values.
  • Save 03-normalized-model.sql.
  • Highlight only the CREATE TABLE dbo.OrderItems block.
  • Select Execute in the query toolbar.

The selected batch should finish without an error. Your four-table relational structure is now ready for consistent data.

Order-item table creation failed?

  • Confirm that dbo.Orders exists before creating dbo.OrderItems.
  • Confirm that dbo.Products exists before creating dbo.OrderItems.
  • Help me troubleshoot the order-item table creation.
Load consistent retail data

Deterministic IDs make every relationship easy to trace. The cleaned customer data keeps maya.new@example.com as Maya Chen's single authoritative email value.

  • Add the cleaned customer rows by pasting this at the bottom of 03-normalized-model.sql:
INSERT INTO dbo.Customers
    (CustomerID, CustomerName, CustomerEmail)
VALUES
    (1, N'Maya Chen', N'maya.new@example.com'),
    (2, N'Jordan Lee', N'jordan@example.com'),
    (3, N'Sam Rivera', N'sam@example.com');
GO

What does this customer data establish?

  • Maya Chen receives CustomerID value 1.
  • Jordan Lee receives CustomerID value 2.
  • Sam Rivera receives CustomerID value 3.
  • Save 03-normalized-model.sql.
  • Highlight only the INSERT INTO dbo.Customers block.
  • Select Execute in the query toolbar.

The execution result should report three affected rows. Each customer now has one normalized record.

Customer rows were not inserted?

  • Confirm that dbo.Customers is empty before executing this insert block.
  • Check that each customer ID is unique.
  • Help me troubleshoot the customer insert.
  • Add the product rows by pasting this at the bottom of 03-normalized-model.sql:
INSERT INTO dbo.Products
    (ProductID, ProductName, CurrentPrice)
VALUES
    (1, N'Laptop Stand', 45.00),
    (2, N'Wireless Mouse', 25.00),
    (3, N'USB-C Hub', 60.00);
GO

What does this product data establish?

  • Each product receives a stable ID for later relationships.
  • CurrentPrice stores the current product price.
  • The uniqueness rule ensures each product name appears once.
  • Save 03-normalized-model.sql.
  • Highlight only the INSERT INTO dbo.Products block.
  • Select Execute in the query toolbar.

The execution result should report three affected rows. The product records are ready for order-item references.

Product rows were not inserted?

  • Confirm that the product IDs are 1, 2, and 3.
  • Check that every current price is zero or higher.
  • Help me troubleshoot the product insert.
  • Add the order rows by pasting this at the bottom of 03-normalized-model.sql:
INSERT INTO dbo.Orders
    (OrderID, OrderNumber, OrderDate, CustomerID)
VALUES
    (1, N'SO-1001', '2026-01-05', 1),
    (2, N'SO-1002', '2026-01-06', 2),
    (3, N'SO-1003', '2026-01-07', 1),
    (4, N'SO-1004', '2026-01-08', 3);
GO

How do these orders use customer IDs?

  • SO-1001 references Maya Chen through customer ID 1.
  • SO-1003 also references Maya Chen through the same customer ID.
  • The remaining orders reference Jordan Lee and Sam Rivera through their own IDs.
  • Save 03-normalized-model.sql.
  • Highlight only the INSERT INTO dbo.Orders block.
  • Select Execute in the query toolbar.

The execution result should report four affected rows. Maya Chen's two orders now reference one customer record.

Order rows were not inserted?

  • Confirm that the customer rows were inserted before these orders.
  • Check that every CustomerID value matches an existing customer.
  • Help me troubleshoot the order insert.
  • Add the six line items by pasting this at the bottom of 03-normalized-model.sql:
INSERT INTO dbo.OrderItems
    (OrderID, ProductID, Quantity, UnitPrice)
VALUES
    (1, 1, 1, 45.00),
    (1, 2, 2, 25.00),
    (2, 1, 1, 45.00),
    (3, 3, 1, 60.00),
    (4, 2, 3, 25.00),
    (4, 3, 1, 60.00);
GO

What do the line items represent?

  • Each row links one order to one purchased product.
  • Quantity records how many units were purchased.
  • UnitPrice preserves the price charged for that order line.
  • The composite key prevents the same product from appearing twice within one order.
  • Save 03-normalized-model.sql.
  • Highlight only the INSERT INTO dbo.OrderItems block.
  • Select Execute in the query toolbar.

The execution result should report six affected rows. All four orders now connect to their purchased products.

Line items were not inserted?

  • Confirm that the order rows were inserted before the line items.
  • Confirm that the product rows were inserted before the line items.
  • Help me troubleshoot the order-item insert.
Verify one customer record per person

The final query groups orders under their customer records. A single grouped row for Maya Chen proves that her email no longer repeats across order lines.

  • Add the customer and order verification query by pasting this at the bottom of 03-normalized-model.sql:
SELECT c.CustomerName,
       c.CustomerEmail,
       COUNT(o.OrderID) AS OrderCount
FROM dbo.Customers AS c
LEFT JOIN dbo.Orders AS o
    ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerName,
         c.CustomerEmail
ORDER BY c.CustomerName;
GO

What does the verification query prove?

  • LEFT JOIN keeps every customer in the result even if that customer has no orders.
  • COUNT(o.OrderID) counts the orders connected to each customer.
  • GROUP BY produces one result row for each customer-email combination.
  • ORDER BY arranges the result by customer name.
  • Save 03-normalized-model.sql.

Before you run the check, do you expect Maya Chen to appear once or three times in the normalized result?

  • Highlight only the final SELECT query.
  • Select Execute in the query toolbar.

You should see Jordan Lee with 1 order. You should see Maya Chen with 2 orders.

You should see Sam Rivera with 1 order. Maya Chen appears once with maya.new@example.com.

That is the contradiction resolved. The normalized model now has one authoritative customer record for Maya Chen.

  • Switch back to 02-update-anomaly.sql from earlier.
  • Compare its three conflicting Maya Chen rows with the single normalized customer row.

The flat table tied Maya's email to individual sales rows. The normalized model stores that email once in dbo.Customers.

Verification result looks different?

  • Confirm that all four insert blocks completed before running the verification query.
  • Check that Maya Chen uses customer ID 1 in both matching order rows.
  • Help me troubleshoot the normalized verification result.

✔️ Awesome, I've got everything!

Your saved 03-normalized-model.sql file now defines the full normalized model with its data and verification query.

ⓧ I'd like to double check the full code

Compare your saved 03-normalized-model.sql file with this complete version.

USE RetailRepairLab;
GO

CREATE TABLE dbo.Customers
(
    CustomerID INT PRIMARY KEY,
    CustomerName NVARCHAR(100) NOT NULL,
    CustomerEmail NVARCHAR(255) NOT NULL UNIQUE
);
GO

CREATE TABLE dbo.Products
(
    ProductID INT PRIMARY KEY,
    ProductName NVARCHAR(100) NOT NULL UNIQUE,
    CurrentPrice DECIMAL(10, 2) NOT NULL,
    CONSTRAINT CK_Products_CurrentPrice CHECK (CurrentPrice >= 0)
);
GO

CREATE TABLE dbo.Orders
(
    OrderID INT PRIMARY KEY,
    OrderNumber NVARCHAR(20) NOT NULL UNIQUE,
    OrderDate DATE NOT NULL,
    CustomerID INT NOT NULL,
    CONSTRAINT FK_Orders_Customers
        FOREIGN KEY (CustomerID) REFERENCES dbo.Customers(CustomerID)
);
GO

CREATE TABLE dbo.OrderItems
(
    OrderID INT NOT NULL,
    ProductID INT NOT NULL,
    Quantity INT NOT NULL,
    UnitPrice DECIMAL(10, 2) NOT NULL,
    CONSTRAINT PK_OrderItems PRIMARY KEY (OrderID, ProductID),
    CONSTRAINT FK_OrderItems_Orders
        FOREIGN KEY (OrderID) REFERENCES dbo.Orders(OrderID),
    CONSTRAINT FK_OrderItems_Products
        FOREIGN KEY (ProductID) REFERENCES dbo.Products(ProductID),
    CONSTRAINT CK_OrderItems_Quantity CHECK (Quantity > 0),
    CONSTRAINT CK_OrderItems_UnitPrice CHECK (UnitPrice >= 0)
);
GO

INSERT INTO dbo.Customers
    (CustomerID, CustomerName, CustomerEmail)
VALUES
    (1, N'Maya Chen', N'maya.new@example.com'),
    (2, N'Jordan Lee', N'jordan@example.com'),
    (3, N'Sam Rivera', N'sam@example.com');
GO

INSERT INTO dbo.Products
    (ProductID, ProductName, CurrentPrice)
VALUES
    (1, N'Laptop Stand', 45.00),
    (2, N'Wireless Mouse', 25.00),
    (3, N'USB-C Hub', 60.00);
GO

INSERT INTO dbo.Orders
    (OrderID, OrderNumber, OrderDate, CustomerID)
VALUES
    (1, N'SO-1001', '2026-01-05', 1),
    (2, N'SO-1002', '2026-01-06', 2),
    (3, N'SO-1003', '2026-01-07', 1),
    (4, N'SO-1004', '2026-01-08', 3);
GO

INSERT INTO dbo.OrderItems
    (OrderID, ProductID, Quantity, UnitPrice)
VALUES
    (1, 1, 1, 45.00),
    (1, 2, 2, 25.00),
    (2, 1, 1, 45.00),
    (3, 3, 1, 60.00),
    (4, 2, 3, 25.00),
    (4, 3, 1, 60.00);
GO

SELECT c.CustomerName,
       c.CustomerEmail,
       COUNT(o.OrderID) AS OrderCount
FROM dbo.Customers AS c
LEFT JOIN dbo.Orders AS o
    ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerName,
         c.CustomerEmail
ORDER BY c.CustomerName;
GO

Your retail data now has reliable identities, protected relationships, and one home for each fact. Next up, you will join these tables to produce order details and customer spending totals.

Query the Clean Model

The normalized relational model now keeps every retail fact in a dependable home. That structure protects the data from the contradictions you saw in the flat log.

Useful sales answers now sit across separate tables. You will use Transact-SQL joins to reconstruct each purchase.

You will then use grouped aggregation to rank customers by spending.

In this step, get ready to:
  • Reconstruct each purchased product as an order-detail row.
  • Build a customer-spend summary.
  • Verify both reports against the expected sales totals.
Build the order-detail report

The normalized design stores order details in separate tables. A join follows shared key values to rebuild one product purchase per row.

Multiplying quantity by unit price adds the value of each purchased product to the report.

  • Return to SQL Server Management Studio from earlier.
  • Create a blank query tab with the query editor's new-query control.

You now have an empty editor connected to the LocalDB instance from earlier.

  • Use the query editor's save control to name the blank script 04-sales-reports.sql.

The filename at the top of the editor now reads 04-sales-reports.sql.

  • Paste this first report into 04-sales-reports.sql:
USE RetailRepairLab;
GO

SELECT o.OrderNumber,
       o.OrderDate,
       c.CustomerName,
       p.ProductName,
       oi.Quantity,
       oi.UnitPrice,
       oi.Quantity * oi.UnitPrice AS LineTotal
FROM dbo.Orders AS o
INNER JOIN dbo.Customers AS c
    ON o.CustomerID = c.CustomerID
INNER JOIN dbo.OrderItems AS oi
    ON o.OrderID = oi.OrderID
INNER JOIN dbo.Products AS p
    ON oi.ProductID = p.ProductID
ORDER BY o.OrderDate,
         o.OrderNumber,
         p.ProductName;
GO

What does this query do?

  • The USE RetailRepairLab statement selects the database that contains your normalized tables.
  • Each GO marks the end of a batch in the SSMS query editor.
  • The three INNER JOIN clauses follow the relationships between all four tables.
  • The aliases o, c, oi, and p keep the column references concise.
  • The expression oi.Quantity * oi.UnitPrice calculates the historic total for each line item.
  • The ORDER BY clause places the purchases in a consistent sequence.
  • Save 04-sales-reports.sql.

Before you execute this first report, predict the number of rows produced by six line items.

  • Select Execute in the query editor toolbar.

The first result grid contains six rows. Each row shows one purchased product with its calculated LineTotal.

For order SO-1001, the Laptop Stand line shows 45.00. The Wireless Mouse line shows 50.00.

Seeing missing rows or blank columns?

  • Confirm that the script begins with USE RetailRepairLab so the query uses the correct database.
  • Check that every ON clause matches the corresponding ID columns from the normalized model.
  • Compare the table aliases with the aliases used in the SELECT list.
  • Help me troubleshoot my four-table order-detail query.
Summarize customer spending

The line-item join creates one row per purchased product. An order containing multiple products therefore appears more than once before the rows are grouped.

A distinct order count protects the summary from counting those repeated order IDs as separate orders.

  • Place your cursor below the final GO in 04-sales-reports.sql.
  • Paste this customer-spend query at the bottom of the file:
SELECT c.CustomerName,
       COUNT(DISTINCT o.OrderID) AS OrderCount,
       SUM(oi.Quantity * oi.UnitPrice) AS TotalSpent
FROM dbo.Customers AS c
INNER JOIN dbo.Orders AS o
    ON c.CustomerID = o.CustomerID
INNER JOIN dbo.OrderItems AS oi
    ON o.OrderID = oi.OrderID
GROUP BY c.CustomerName
ORDER BY TotalSpent DESC;
GO

How does the summary work?

  • The COUNT(DISTINCT o.OrderID) expression counts each order once when that order contains multiple products.
  • The SUM(oi.Quantity * oi.UnitPrice) expression adds every line total for each customer.
  • The GROUP BY c.CustomerName clause produces one summary row per customer.
  • The ORDER BY TotalSpent DESC clause ranks the highest-spending customer first.
  • Save 04-sales-reports.sql.

Before you execute the complete script, predict which customer appears first in the spending summary.

  • Select Execute in the query editor toolbar.

You will see two result grids after the script runs.

The customer summary ranks Maya Chen first at 155.00.

Sam Rivera follows at 135.00. Jordan Lee appears last at 45.00.

That is the clean model paying off. Your protected tables now answer a customer-spending question without duplicating the report data.

Seeing incorrect totals?

  • Check that dbo.OrderItems appears once in the summary query.
  • Confirm that the order count uses COUNT(DISTINCT o.OrderID).
  • Confirm that the spending total multiplies oi.Quantity by oi.UnitPrice before applying SUM.
  • Help me troubleshoot my customer-spend totals.

✔️ Awesome, I've got everything!

The two reports match the target results. Your 04-sales-reports.sql file is complete.

ⓧ I'd like to double check the full code

The complete 04-sales-reports.sql file is below for comparison.

USE RetailRepairLab;
GO

SELECT o.OrderNumber,
       o.OrderDate,
       c.CustomerName,
       p.ProductName,
       oi.Quantity,
       oi.UnitPrice,
       oi.Quantity * oi.UnitPrice AS LineTotal
FROM dbo.Orders AS o
INNER JOIN dbo.Customers AS c
    ON o.CustomerID = c.CustomerID
INNER JOIN dbo.OrderItems AS oi
    ON o.OrderID = oi.OrderID
INNER JOIN dbo.Products AS p
    ON oi.ProductID = p.ProductID
ORDER BY o.OrderDate,
         o.OrderNumber,
         p.ProductName;
GO

SELECT c.CustomerName,
       COUNT(DISTINCT o.OrderID) AS OrderCount,
       SUM(oi.Quantity * oi.UnitPrice) AS TotalSpent
FROM dbo.Customers AS c
INNER JOIN dbo.Orders AS o
    ON c.CustomerID = o.CustomerID
INNER JOIN dbo.OrderItems AS oi
    ON o.OrderID = oi.OrderID
GROUP BY c.CustomerName
ORDER BY TotalSpent DESC;
GO

Secret mission

Package the Report as a View

Turn your customer-spend report into a reusable database view. Query that view with a spending threshold to reveal your highest-value customers.

Clean Up Your Resources

Clean Up Your Resources

Your lab runs entirely on your Windows PC through Microsoft SQL Server Express LocalDB. It uses local disk space and has no ongoing cost.

Choose whether to keep your database available, pause its local process, or delete the lab entirely.

Resources you used:

  • The RetailRepairLab database containing five user tables plus the dbo.vw_CustomerSalesSummary view.

Keep everything running

No action needed. Choose this if you want to keep practising with the normalized model or writing new reports.

  • Leave RetailRepairLab in place for future practice.
  • Return to SQL Server Management Studio when you want to query the model again.

Pause - I'll come back to this later

Shut down the running LocalDB process to free memory. Your database remains on disk for your return.

  • Press the Windows key to open Windows search.
  • Enter Command Prompt in the search box.
  • Press Enter to open Command Prompt.
  • Stop the automatic LocalDB instance by running this command:
sqllocaldb stop MSSQLLocalDB

What Does This Command Do?

  • The sqllocaldb command sends the stop request to MSSQLLocalDB.
  • The RetailRepairLab database files remain on your local disk.
  • Confirm the instance state by running this command:
sqllocaldb info MSSQLLocalDB

What Should You See?

The instance details should report a stopped state. Your database remains available for the next connection.

  • Return to SSMS when you are ready to resume.
  • Reconnect to (localdb)\MSSQLLocalDB with Windows Authentication.

LocalDB starts the automatic instance when you reconnect.

Delete - I don't want to use this again

Remove the lab database when you want a clean slate. The installed tools remain available for future projects.

This Deletion Is Permanent

This deletion permanently removes the lab database. Your SQL script files remain untouched.

  • Return to the SSMS window connected to (localdb)\MSSQLLocalDB.
  • Use the new-query control in the SSMS toolbar to create a blank query.
  • Prepare the database deletion by pasting this script into the blank query:
USE master;
GO

DROP DATABASE IF EXISTS RetailRepairLab;
GO

What Does This Script Do?

  • The first batch switches the connection to master.
  • GO separates the context change from database deletion.
  • DROP DATABASE IF EXISTS removes RetailRepairLab when it exists.
  • Run the deletion by selecting Execute in the query editor.
  • Use the refresh control for the database list in Object Explorer.

You should no longer see RetailRepairLab in Object Explorer. The lab is now fully removed from LocalDB.

Database Still Listed?

  • Close any query window that still uses RetailRepairLab.
  • Run the deletion script again after closing those windows.

Help me delete a LocalDB database that is still in use.

Nice Work!

Nice Work!

That retail database is officially repaired! You transformed a contradictory sales log into a reliable relational model using SQL Server Express LocalDB through queries in SQL Server Management Studio.

You've learned how to:

  • Use Transact-SQL to expose an update anomaly in dbo.SalesLog. The conflicting Maya Chen rows made the flat model's unreliable source of truth visible.
  • Build a four-table relational model from customer, product, order, and line-item facts. Protect that model with primary keys, foreign keys, uniqueness rules, and value checks.
  • Reconstruct an order-detail report with multi-table joins and calculated line totals. Rank customers with grouped aggregation while counting each order once.
  • Secret Mission: Package the customer summary as the reusable dbo.vw_CustomerSalesSummary view. Query it independently to return Maya Chen and Sam Rivera in descending spending order.

Ready to quiz yourself?