Order Fulfillment with PostgreSQL

Design a normalized PostgreSQL database and publish SQL analytics for orders.

Introduction

30 Second Summary

Order records can look trustworthy until the same product carries two prices. One hidden conflict can make every report harder to defend.

In this project, you will apply normalization to turn a flawed order-intake table into a PostgreSQL database that enforces business rules. You will publish its analytical evidence in a public GitHub repository.

What You'll Build

Picture opening your public repository to show an ER diagram backed by reports that answer real order questions.

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

  • A diagnostic query that reveals conflicting prices for SKU-101 before you redesign the data.
  • A five-table normalized core that rejects invalid data through database-enforced business rules.
  • A public portfolio repository where anyone can rerun the SQL behind your resume claims.
  • Secret Mission: Build a carrier SLA report that uses a filtered aggregate to count late deliveries.

Are there any prerequisites?

You do not need any project tools installed before you begin. The guide covers PostgreSQL 18.6 plus pgAdmin 4 on Windows.

It also covers creating a GitHub account if you need one.

Before We Start

Before any hands-on work begins, this checkpoint locks in the business purpose of tracking customers, products, orders, order items, and fulfillment events. A clear purpose keeps normalization, data integrity, reliable reporting, and portfolio evidence tied to the same business need.

Set Up PostgreSQL and GitHub

Reliable fulfillment reporting needs a database engine that you can run repeatedly. Your local PostgreSQL server gives every later SQL script a consistent place to run.

pgAdmin 4 gives you a visual workspace for creating the database. GitHub turns the finished files into public evidence of your SQL skills.

In this step, get ready to:
  • Install PostgreSQL 18.6 with pgAdmin 4 on Windows.
  • Create the order_fulfillment database and confirm the server version.
  • Prepare a local project folder and a public GitHub repository.
Install PostgreSQL and pgAdmin

The official Windows installer bundles the PostgreSQL server with pgAdmin. Selecting major version 18 gives you the current PostgreSQL 18.6 release used throughout this project.

What gets installed?

  • The PostgreSQL server stores your tables and executes your SQL.
  • pgAdmin provides the graphical tools for creating databases and viewing query results.
  • StackBuilder adds optional packages. This project does not require those packages.
  • Choose the tab that matches the PostgreSQL installation currently available on your Windows computer.

✔️ PostgreSQL 18.6 and pgAdmin 4 are installed

Your database tools are already at the required version. That gives you a head start on creating the project database.

ⓧ I have an older version

The database superuser password is a sensitive local credential. The public repository must never contain it.

  • Open the official PostgreSQL Windows download page.
  • Choose the Interactive installer by EDB.
  • Select PostgreSQL 18 in the installer.
  • Keep The PostgreSQL server selected.
  • Keep pgAdmin selected.

The component list should now include the server and pgAdmin. Those are the two tools this project depends on.

  • Store the database superuser password in your password manager.
  • Skip StackBuilder when the installer offers optional packages.
  • Finish the PostgreSQL installation.

Windows now has the PostgreSQL 18 server and the bundled pgAdmin application available.

ⓧ PostgreSQL is not installed

The installer asks you to create a database superuser password. This credential stays on your computer and out of your public repository.

  • Open the official PostgreSQL Windows download page.
  • Choose the Interactive installer by EDB.
  • Select PostgreSQL 18 in the installer.
  • Keep The PostgreSQL server selected.
  • Keep pgAdmin selected.

The component list should now include the database server and its graphical administration tool.

  • Store the database superuser password in your password manager.
  • Skip StackBuilder when the installer offers optional packages.
  • Finish the PostgreSQL installation.

PostgreSQL and pgAdmin are now available from Windows search.

Missing a database component?

  • Return to the official Windows download page if you used a different installer.
  • Run the installer again if pgAdmin is missing from Windows search.
  • Keep the pgAdmin component selected during the second installation attempt.

Still stuck? Help me troubleshoot my PostgreSQL and pgAdmin installation on Windows.

Create and verify the local database

A PostgreSQL server can hold several databases. Creating order_fulfillment isolates this project's tables and queries from anything else on your server.

  • Press the Windows key to open Windows search.
  • Type pgAdmin 4 into Windows search.
  • Press Enter to start pgAdmin 4.
  • Connect to the local PostgreSQL 18 server with the superuser password you stored.

The pgAdmin object browser now shows your connected local server. This confirms that the administration tool can reach PostgreSQL.

  • Open the Database dialog from the database collection's context menu.
  • Enter order_fulfillment in the Database field.
  • Click Save.

The object browser now lists order_fulfillment. That confirms the project database exists on your local server.

  • Select order_fulfillment in the object browser.
  • Open Query Tool from the Tools menu.

Before you run the check, which PostgreSQL version do you expect the connected server to report?

  • Confirm the connected server version by executing this query:
SELECT version();

What does the version query do?

  • The SELECT statement asks the connected server to return one value.
  • The version() function reads the server's own version information.
  • The result proves that Query Tool is connected to the intended PostgreSQL server.

You should see Data Output begin with PostgreSQL 18.6. That's the local database engine confirmed and ready for your SQL scripts.

Seeing a different version?

  • Confirm that Query Tool is connected to order_fulfillment on the PostgreSQL 18 server.
  • Return to the installation tab if the result starts with an older PostgreSQL version.
  • Reconnect with the database superuser password if pgAdmin cannot reach the local server.

Need help? Help me diagnose my PostgreSQL version or pgAdmin connection.

Prepare the local and public workspaces

Your local folder will hold the SQL scripts and erd.png. A matching repository name keeps the published evidence easy to identify.

  • Use Windows search to open the Windows file browser.
  • Select Desktop as the folder location.
  • Create a new folder named postgresql-order-fulfillment with the new-folder control.

You should see postgresql-order-fulfillment on your Desktop. This folder is ready to receive the SQL scripts and ER diagram from later steps.

  • Create a GitHub account from the official signup page if you do not already have one.
  • Sign in to GitHub.

Your signed-in account can now create the public repository that holds the portfolio evidence.

  • Click New repository.
  • Enter postgresql-order-fulfillment as the repository name.

The creation form now identifies the repository with the same name as your local folder.

  • Select Public.
  • Enable Add README.

The repository is now configured for public viewing with an initial documentation file.

  • Click Create repository.

You should see the public postgresql-order-fulfillment repository page with its README displayed. Your portfolio destination is live and ready for the project files.

Repository setup not matching?

  • Confirm that you are signed in to the intended GitHub account.
  • Check that the repository name is exactly postgresql-order-fulfillment.
  • Confirm that Public and Add README are selected before creating the repository.

Need another pair of eyes? Help me fix my GitHub repository setup.

Use this final check to compare your setup with the required project state.

✔️ Awesome, I've got everything!

Your local database, project folder, and public repository now form the complete workspace for the SQL project.

ⓧ I'd like to double check the full code

Confirm these three setup artifacts before continuing.

  • Local project folder: Local folder prepared to store SQL scripts and erd.png.
  • order_fulfillment database: Created locally on the PostgreSQL 18.6 server and selected in pgAdmin 4 Query Tool.
  • postgresql-order-fulfillment GitHub repository: Public GitHub repository created with an initial README.

Your local engine and public evidence space are ready. Next, you'll load the flat intake data and reveal the pricing conflict that normalization needs to solve.

Expose the Flat-Table Problem

Your PostgreSQL workspace is ready. Your public GitHub repository can hold the evidence later.

A flat intake table stores customer facts beside product facts on every order line. Repetition creates room for conflicting values.

This step preserves the flawed source data. A diagnostic query then makes the hidden price conflict visible.

In this step, get ready to:
  • Create the raw intake table with its eight source rows.
  • Inspect the repeated customer facts and product facts.
  • Run a SKU diagnostic that exposes conflicting prices.
Create and load the raw intake table

The raw table acts as a snapshot of the original order intake. It deliberately keeps order facts beside customer facts and product facts.

What is a flat intake table?

A flat intake table places every fact needed for one record into a single row. Shared facts appear again when an order has another product or a product appears in another order.

  • Return to the Query Tool for the order_fulfillment database.
  • Add the staging-table definition by pasting this code into the empty SQL editor:
CREATE TABLE raw_order_intake (
    source_row_id integer PRIMARY KEY,
    order_id integer NOT NULL,
    order_date date NOT NULL,
    customer_email text NOT NULL,
    customer_name text NOT NULL,
    region text NOT NULL,
    sku text NOT NULL,
    product_name text NOT NULL,
    category text NOT NULL,
    quantity integer NOT NULL,
    unit_price numeric(10, 2) NOT NULL,
    order_status text NOT NULL
);

What does this table define?

  • The source_row_id column gives every imported row a stable identity.
  • The primary key prevents two source rows from using the same identifier.
  • The NOT NULL rules require every source row to include each flat-order field.
  • The customer columns and product columns remain mixed with the order columns. This structure preserves the source problem for diagnosis.
  • Highlight only the CREATE TABLE statement.
  • Click Execute script in the Query Tool toolbar.

You should see a successful execution status with no result rows. The database now contains the empty raw_order_intake table.

Table creation did not succeed?

  • Confirm the Query Tool is still connected to order_fulfillment.
  • Check that the statement ends with a closing parenthesis followed by a semi-colon.
  • Avoid executing the creation statement again if a previous attempt already created the table.

Still stuck? Help me diagnose why the raw_order_intake table was not created.

  • Place the cursor below the table definition.
  • Add the eight source rows by pasting this code:
INSERT INTO raw_order_intake (
    source_row_id,
    order_id,
    order_date,
    customer_email,
    customer_name,
    region,
    sku,
    product_name,
    category,
    quantity,
    unit_price,
    order_status
) VALUES
    (1, 1001, '2026-01-05', 'maya.chen@example.com', 'Maya Chen', 'North', 'SKU-101', 'Ergonomic Mouse', 'Accessories', 2, 29.99, 'delivered'),
    (2, 1001, '2026-01-05', 'maya.chen@example.com', 'Maya Chen', 'North', 'SKU-201', 'Laptop Stand', 'Office', 1, 59.99, 'delivered'),
    (3, 1002, '2026-01-08', 'liam.patel@example.com', 'Liam Patel', 'South', 'SKU-101', 'Ergonomic Mouse', 'Accessories', 1, 31.99, 'delivered'),
    (4, 1002, '2026-01-08', 'liam.patel@example.com', 'Liam Patel', 'South', 'SKU-301', 'HD Webcam', 'Video', 1, 89.00, 'delivered'),
    (5, 1003, '2026-01-12', 'sofia.rivera@example.com', 'Sofia Rivera', 'West', 'SKU-302', 'Noise-Cancelling Headset', 'Audio', 1, 119.00, 'shipped'),
    (6, 1004, '2026-01-16', 'noah.williams@example.com', 'Noah Williams', 'East', 'SKU-202', 'Desk Lamp', 'Office', 2, 44.50, 'cancelled'),
    (7, 1005, '2026-02-02', 'emma.johnson@example.com', 'Emma Johnson', 'North', 'SKU-201', 'Laptop Stand', 'Office', 1, 59.99, 'delivered'),
    (8, 1005, '2026-02-02', 'emma.johnson@example.com', 'Emma Johnson', 'North', 'SKU-202', 'Desk Lamp', 'Office', 1, 44.50, 'delivered');

What do these rows represent?

  • Each tuple represents one product line from an incoming order.
  • Orders with multiple products occupy multiple rows.
  • Customer details repeat for every product line in the same order.
  • Product details repeat whenever the same SKU appears in another order.
  • Highlight only the INSERT INTO statement.
  • Click Execute script.

You should see a successful execution status confirming that eight rows were inserted. The raw source data is now stored in PostgreSQL.

  • Click Save As in the Query Tool toolbar.
  • Choose the local project folder from earlier.
  • Enter 00_raw_order_intake.sql as the file name.
  • Click Save.

The Query Tool tab should now show 00_raw_order_intake.sql. Your first portfolio script now has a local home.

Rows did not load?

  • Confirm the table-creation statement completed before the insert statement.
  • Check that all eight value rows remain inside the same VALUES clause.
  • Use the data view in the next substep if a previous attempt reports a primary-key conflict. Eight existing rows mean the insert already succeeded.

Need another pair of eyes? Help me find why the eight raw order rows were not inserted.

Inspect the repeated source facts

The raw rows are easier to compare in the pgAdmin 4 data grid. Viewing all eight rows reveals how often stable customer facts and product facts are copied.

  • Return to the Browser tree in pgAdmin.
  • Expand Schemas.
  • Expand public beneath Schemas.
  • Expand Tables beneath public.
  • Right-click raw_order_intake.
  • Select View/Edit Data.
  • Select All Rows.

The data grid should contain eight rows. Each row includes order details beside customer details and product details.

  • Compare source rows 1 and 2 across the customer columns.
  • Compare source rows 2 and 7 across the product columns.
  • Compare source rows 6 and 8 across the product columns.

Where is the repetition?

  • Source rows 1 and 2 repeat the customer name Maya Chen.
  • Those rows also repeat maya.chen@example.com and the North region.
  • Source rows 2 and 7 repeat the product name Laptop Stand.
  • Source rows 6 and 8 repeat the product name Desk Lamp.

Why is repetition risky?

Every repeated copy becomes another place where a stable fact can change independently. Reports become unreliable when those copies disagree.

Expose the conflicting SKU price

Aggregate functions can compare every recorded price within each SKU group. A group survives the final filter only when its minimum price differs from its maximum price.

  • Switch back to the 00_raw_order_intake.sql Query Tool tab.
  • Place the cursor below the insert statement.
  • Add the diagnostic query by pasting this code:
SELECT
    sku,
    MIN(unit_price) AS minimum_price,
    MAX(unit_price) AS maximum_price
FROM raw_order_intake
GROUP BY sku
HAVING MIN(unit_price) <> MAX(unit_price)
ORDER BY sku;

How does the diagnostic work?

  • The GROUP BY sku clause places every row for the same SKU into one group.
  • The MIN(unit_price) expression finds the lowest recorded price in each group.
  • The MAX(unit_price) expression finds the highest recorded price in each group.
  • The HAVING clause keeps groups whose two prices disagree.

Before you run the diagnostic, which SKU do you expect the filter to keep?

  • Highlight only the final SELECT query.
  • Click Execute script.

You found the hidden contradiction. The Data Output grid shows one row for SKU-101.

Its minimum price is 29.99. Its maximum price is 31.99.

What does the result prove?

The same product identity carries two source prices. Normalization gives stable product facts a dedicated home while order lines preserve their transaction values.

  • Click Save in the Query Tool toolbar.
  • Confirm the editor tab still shows 00_raw_order_intake.sql.

No diagnostic row?

  • Confirm the table contains all eight supplied rows.
  • Check the two SKU-101 rows in the insert statement.
  • Confirm their prices are 29.99 and 31.99.

Still seeing an empty result? Help me troubleshoot the SKU price diagnostic.

✔️ Awesome, I've got everything!

Your saved script now creates the raw table, inserts eight rows, and exposes the conflicting SKU price.

ⓧ I'd like to double check the full code

The complete 00_raw_order_intake.sql file should match this reference.

CREATE TABLE raw_order_intake (
    source_row_id integer PRIMARY KEY,
    order_id integer NOT NULL,
    order_date date NOT NULL,
    customer_email text NOT NULL,
    customer_name text NOT NULL,
    region text NOT NULL,
    sku text NOT NULL,
    product_name text NOT NULL,
    category text NOT NULL,
    quantity integer NOT NULL,
    unit_price numeric(10, 2) NOT NULL,
    order_status text NOT NULL
);

INSERT INTO raw_order_intake (
    source_row_id,
    order_id,
    order_date,
    customer_email,
    customer_name,
    region,
    sku,
    product_name,
    category,
    quantity,
    unit_price,
    order_status
) VALUES
    (1, 1001, '2026-01-05', 'maya.chen@example.com', 'Maya Chen', 'North', 'SKU-101', 'Ergonomic Mouse', 'Accessories', 2, 29.99, 'delivered'),
    (2, 1001, '2026-01-05', 'maya.chen@example.com', 'Maya Chen', 'North', 'SKU-201', 'Laptop Stand', 'Office', 1, 59.99, 'delivered'),
    (3, 1002, '2026-01-08', 'liam.patel@example.com', 'Liam Patel', 'South', 'SKU-101', 'Ergonomic Mouse', 'Accessories', 1, 31.99, 'delivered'),
    (4, 1002, '2026-01-08', 'liam.patel@example.com', 'Liam Patel', 'South', 'SKU-301', 'HD Webcam', 'Video', 1, 89.00, 'delivered'),
    (5, 1003, '2026-01-12', 'sofia.rivera@example.com', 'Sofia Rivera', 'West', 'SKU-302', 'Noise-Cancelling Headset', 'Audio', 1, 119.00, 'shipped'),
    (6, 1004, '2026-01-16', 'noah.williams@example.com', 'Noah Williams', 'East', 'SKU-202', 'Desk Lamp', 'Office', 2, 44.50, 'cancelled'),
    (7, 1005, '2026-02-02', 'emma.johnson@example.com', 'Emma Johnson', 'North', 'SKU-201', 'Laptop Stand', 'Office', 1, 59.99, 'delivered'),
    (8, 1005, '2026-02-02', 'emma.johnson@example.com', 'Emma Johnson', 'North', 'SKU-202', 'Desk Lamp', 'Office', 1, 44.50, 'delivered');

SELECT
    sku,
    MIN(unit_price) AS minimum_price,
    MAX(unit_price) AS maximum_price
FROM raw_order_intake
GROUP BY sku
HAVING MIN(unit_price) <> MAX(unit_price)
ORDER BY sku;

The raw data now proves why the current shape cannot support dependable reporting. Next, you will separate those repeated facts into a normalized relational core.

Build the Normalized Core

Your last diagnostic exposed one SKU with two source prices. That conflict shows how repeated product facts make reporting unreliable.

A normalized model gives each stable fact one home. You will build five linked tables for customers, products, orders, order items, plus fulfillments.

PostgreSQL will enforce those links through named constraints. An ER diagram will make the finished structure visible.

In this step, get ready to:
  • Define five normalized tables with named keys plus business rules.
  • Load the canonical customers, products, orders, order items, plus fulfillment records.
  • Export an ER diagram that shows the five tables plus their relationships.
Create the five-table schema

Each table owns one kind of business fact. Primary keys identify records while foreign keys connect those records across tables.

  • Replace the current Query Tool editor contents with the customer table by pasting this code:
CREATE TABLE customers (
    customer_id integer CONSTRAINT customers_pk PRIMARY KEY,
    email text NOT NULL CONSTRAINT customers_email_unique UNIQUE,
    full_name text NOT NULL,
    region text NOT NULL,
    created_on date NOT NULL,
    CONSTRAINT customers_region_valid
        CHECK (region IN ('North', 'South', 'East', 'West'))
);

How does the customer table protect its data?

  • The customers_pk constraint gives every customer a unique identifier.
  • The customers_email_unique constraint prevents one email address from representing multiple customer records.
  • The customers_region_valid constraint limits regions to the four supported values.
  • Use Save As to store the script as 01_schema.sql in the local project folder.
  • Highlight the complete CREATE TABLE customers statement.
  • Click Execute script in the Query Tool toolbar.

The output pane confirms that the statement completed. The database now has a dedicated home for customer identity plus region data.

Customer table not created?

  • Confirm that Query Tool is connected to the order_fulfillment database.
  • Check that the closing parenthesis appears immediately before the final semicolon.

Still stuck? Help me troubleshoot the customers table statement.

  • Append the product table to 01_schema.sql by pasting this code after the customer table:
CREATE TABLE products (
    product_id integer CONSTRAINT products_pk PRIMARY KEY,
    sku text NOT NULL CONSTRAINT products_sku_unique UNIQUE,
    product_name text NOT NULL,
    category text NOT NULL,
    unit_price numeric(10, 2) NOT NULL,
    active boolean NOT NULL DEFAULT true,
    CONSTRAINT products_price_positive CHECK (unit_price > 0),
    CONSTRAINT products_category_valid
        CHECK (category IN ('Accessories', 'Office', 'Video', 'Audio'))
);

Why keep products separate?

  • The products_sku_unique constraint gives each catalog SKU one product record.
  • The products_price_positive constraint rejects zero-priced or negative-priced catalog entries.
  • The products_category_valid constraint keeps category values within the supported business domain.
  • The active column defaults to true for new catalog records.
  • Save 01_schema.sql.
  • Highlight only the CREATE TABLE products statement.
  • Click Execute script.

The output pane confirms that the product table was created. The canonical price for each SKU now has one catalog location.

Product table not created?

  • Confirm that every category value is enclosed in single quotes.
  • Check that numeric(10, 2) includes both precision values.

Need another pair of eyes? Help me find the problem in the products table statement.

  • Append the order table to 01_schema.sql by pasting this code after the product table:
CREATE TABLE orders (
    order_id integer CONSTRAINT orders_pk PRIMARY KEY,
    customer_id integer NOT NULL,
    order_date date NOT NULL,
    status text NOT NULL,
    shipping_fee numeric(10, 2) NOT NULL,
    CONSTRAINT orders_customer_fk
        FOREIGN KEY (customer_id) REFERENCES customers (customer_id),
    CONSTRAINT orders_status_valid
        CHECK (status IN ('pending', 'paid', 'shipped', 'delivered', 'cancelled')),
    CONSTRAINT orders_shipping_fee_nonnegative CHECK (shipping_fee >= 0)
);

How do orders connect to customers?

  • The orders_customer_fk constraint requires every order to reference an existing customer.
  • The orders_status_valid constraint limits each order to a supported lifecycle state.
  • The orders_shipping_fee_nonnegative constraint allows free shipping while rejecting negative fees.
  • Save 01_schema.sql.
  • Highlight only the CREATE TABLE orders statement.
  • Click Execute script.

The output pane confirms that the order table was created. Orders can now reference customers without repeating customer details.

Order table not created?

  • Confirm that the customers table exists before executing this statement.
  • Check that the foreign key references customers (customer_id) exactly.

If the relationship still fails, help me debug the orders foreign key.

  • Append the order-item table to 01_schema.sql by pasting this code after the order table:
CREATE TABLE order_items (
    order_id integer NOT NULL,
    product_id integer NOT NULL,
    quantity integer NOT NULL,
    unit_price numeric(10, 2) NOT NULL,
    CONSTRAINT order_items_pk PRIMARY KEY (order_id, product_id),
    CONSTRAINT order_items_order_fk
        FOREIGN KEY (order_id) REFERENCES orders (order_id) ON DELETE CASCADE,
    CONSTRAINT order_items_product_fk
        FOREIGN KEY (product_id) REFERENCES products (product_id),
    CONSTRAINT order_items_quantity_positive CHECK (quantity > 0),
    CONSTRAINT order_items_price_positive CHECK (unit_price > 0)
);

Why does each order item store a price?

The unit_price column preserves the price charged during the sale. The current catalog price remains in products.unit_price.

  • The composite order_items_pk constraint prevents the same product from appearing twice within one order.
  • The two foreign keys connect each line to an existing order plus an existing product.
  • The quantity constraint rejects zero or negative quantities.
  • Save 01_schema.sql.
  • Highlight only the CREATE TABLE order_items statement.
  • Click Execute script.

The output pane confirms that the order-item table was created. The model can now represent products sold within each order.

Order-item table not created?

  • Confirm that both orders plus products exist before executing this statement.
  • Check that the composite primary key contains order_id plus product_id.

For focused help, ask me to inspect the order_items constraints.

  • Append the fulfillment table to 01_schema.sql by pasting this code after the order-item table:
CREATE TABLE fulfillments (
    fulfillment_id integer CONSTRAINT fulfillments_pk PRIMARY KEY,
    order_id integer NOT NULL CONSTRAINT fulfillments_order_unique UNIQUE,
    shipped_date date NOT NULL,
    delivered_date date,
    carrier text NOT NULL,
    CONSTRAINT fulfillments_order_fk
        FOREIGN KEY (order_id) REFERENCES orders (order_id) ON DELETE CASCADE,
    CONSTRAINT fulfillments_dates_valid
        CHECK (delivered_date IS NULL OR delivered_date >= shipped_date)
);

How does fulfillment stay consistent?

  • The fulfillments_order_unique constraint allows one fulfillment record per order.
  • The fulfillments_order_fk constraint ties every fulfillment to an existing order.
  • The fulfillments_dates_valid constraint permits an undelivered shipment with a null delivery date.
  • The same date constraint prevents delivery from preceding shipment.
  • Save 01_schema.sql.
  • Highlight only the CREATE TABLE fulfillments statement.
  • Click Execute script.

The output pane confirms that the fulfillment table was created. Your normalized core now contains all five linked tables.

Fulfillment table not created?

  • Confirm that the orders table exists before executing this statement.
  • Check that the date rule contains delivered_date IS NULL before the alternative date comparison.

If the final table still fails, help me review the fulfillments statement.

✔️ Awesome, I've got everything!

Your five normalized tables are created. Make sure 01_schema.sql is saved in the local project folder.

ⓧ I'd like to double check the full code

Compare your saved script with this complete 01_schema.sql reference.

CREATE TABLE customers (
    customer_id integer CONSTRAINT customers_pk PRIMARY KEY,
    email text NOT NULL CONSTRAINT customers_email_unique UNIQUE,
    full_name text NOT NULL,
    region text NOT NULL,
    created_on date NOT NULL,
    CONSTRAINT customers_region_valid
        CHECK (region IN ('North', 'South', 'East', 'West'))
);

CREATE TABLE products (
    product_id integer CONSTRAINT products_pk PRIMARY KEY,
    sku text NOT NULL CONSTRAINT products_sku_unique UNIQUE,
    product_name text NOT NULL,
    category text NOT NULL,
    unit_price numeric(10, 2) NOT NULL,
    active boolean NOT NULL DEFAULT true,
    CONSTRAINT products_price_positive CHECK (unit_price > 0),
    CONSTRAINT products_category_valid
        CHECK (category IN ('Accessories', 'Office', 'Video', 'Audio'))
);

CREATE TABLE orders (
    order_id integer CONSTRAINT orders_pk PRIMARY KEY,
    customer_id integer NOT NULL,
    order_date date NOT NULL,
    status text NOT NULL,
    shipping_fee numeric(10, 2) NOT NULL,
    CONSTRAINT orders_customer_fk
        FOREIGN KEY (customer_id) REFERENCES customers (customer_id),
    CONSTRAINT orders_status_valid
        CHECK (status IN ('pending', 'paid', 'shipped', 'delivered', 'cancelled')),
    CONSTRAINT orders_shipping_fee_nonnegative CHECK (shipping_fee >= 0)
);

CREATE TABLE order_items (
    order_id integer NOT NULL,
    product_id integer NOT NULL,
    quantity integer NOT NULL,
    unit_price numeric(10, 2) NOT NULL,
    CONSTRAINT order_items_pk PRIMARY KEY (order_id, product_id),
    CONSTRAINT order_items_order_fk
        FOREIGN KEY (order_id) REFERENCES orders (order_id) ON DELETE CASCADE,
    CONSTRAINT order_items_product_fk
        FOREIGN KEY (product_id) REFERENCES products (product_id),
    CONSTRAINT order_items_quantity_positive CHECK (quantity > 0),
    CONSTRAINT order_items_price_positive CHECK (unit_price > 0)
);

CREATE TABLE fulfillments (
    fulfillment_id integer CONSTRAINT fulfillments_pk PRIMARY KEY,
    order_id integer NOT NULL CONSTRAINT fulfillments_order_unique UNIQUE,
    shipped_date date NOT NULL,
    delivered_date date,
    carrier text NOT NULL,
    CONSTRAINT fulfillments_order_fk
        FOREIGN KEY (order_id) REFERENCES orders (order_id) ON DELETE CASCADE,
    CONSTRAINT fulfillments_dates_valid
        CHECK (delivered_date IS NULL OR delivered_date >= shipped_date)
);
Load the canonical data

The schema defines what the database accepts. The seed data gives the model enough linked records to prove every relationship works.

  • Replace the Query Tool editor contents with the customer seed data by pasting this code:
INSERT INTO customers (
    customer_id,
    email,
    full_name,
    region,
    created_on
) VALUES
    (1, 'maya.chen@example.com', 'Maya Chen', 'North', '2025-11-12'),
    (2, 'liam.patel@example.com', 'Liam Patel', 'South', '2025-11-18'),
    (3, 'sofia.rivera@example.com', 'Sofia Rivera', 'West', '2025-12-01'),
    (4, 'noah.williams@example.com', 'Noah Williams', 'East', '2025-12-06'),
    (5, 'emma.johnson@example.com', 'Emma Johnson', 'North', '2025-12-12'),
    (6, 'oliver.brown@example.com', 'Oliver Brown', 'South', '2025-12-19');

What does this data establish?

  • Each customer receives one stable customer_id.
  • The six records cover every region permitted by the customer constraint.
  • These identifiers become the parent records for the order data.
  • Use Save As to store the script as 02_seed.sql in the local project folder.
  • Highlight the complete customer INSERT statement.
  • Click Execute script.

The output pane confirms that six customer rows were inserted. The parent records for every order now exist.

Customer rows not inserted?

  • Confirm that the customers table was created before running the insert.
  • Check every row for five values in the same order as the listed columns.

If the insert still fails, help me compare the customer rows with the schema.

  • Append the product seed data to 02_seed.sql by pasting this code after the customer insert:
INSERT INTO products (
    product_id,
    sku,
    product_name,
    category,
    unit_price,
    active
) VALUES
    (101, 'SKU-101', 'Ergonomic Mouse', 'Accessories', 29.99, true),
    (102, 'SKU-102', 'Mechanical Keyboard', 'Accessories', 79.00, true),
    (201, 'SKU-201', 'Laptop Stand', 'Office', 59.99, true),
    (202, 'SKU-202', 'Desk Lamp', 'Office', 44.50, true),
    (301, 'SKU-301', 'HD Webcam', 'Video', 89.00, true),
    (302, 'SKU-302', 'Noise-Cancelling Headset', 'Audio', 119.00, true);

Where did the conflicting price go?

The catalog now stores one canonical price for SKU-101. Its product price is 29.99.

Historical sale prices belong in the order-item records. This separation removes the conflicting repeated source fact.

  • Save 02_seed.sql.
  • Highlight only the product INSERT statement.
  • Click Execute script.

The output pane confirms that six product rows were inserted. Each SKU now has one catalog record.

Product rows not inserted?

  • Confirm that every category matches one of the values permitted by products_category_valid.
  • Check that each SKU appears only once in the product insert.

For help with a rejected row, ask me to match the product data to its constraints.

  • Append the order seed data to 02_seed.sql by pasting this code after the product insert:
INSERT INTO orders (
    order_id,
    customer_id,
    order_date,
    status,
    shipping_fee
) VALUES
    (1001, 1, '2026-01-05', 'delivered', 5.00),
    (1002, 2, '2026-01-08', 'delivered', 7.50),
    (1003, 3, '2026-01-12', 'shipped', 6.00),
    (1004, 4, '2026-01-16', 'cancelled', 0.00),
    (1005, 5, '2026-02-02', 'delivered', 5.00),
    (1006, 6, '2026-02-05', 'paid', 7.00),
    (1007, 1, '2026-02-11', 'delivered', 5.00),
    (1008, 2, '2026-02-18', 'shipped', 7.50),
    (1009, 3, '2026-03-03', 'delivered', 6.00),
    (1010, 4, '2026-03-07', 'paid', 6.00);

How do these orders use the model?

  • Every customer_id points to one of the six existing customer records.
  • The statuses cover paid, shipped, delivered, plus cancelled stages.
  • The shipping fees include a valid zero value for the cancelled order.
  • Save 02_seed.sql.
  • Highlight only the order INSERT statement.
  • Click Execute script.

The output pane confirms that ten order rows were inserted. Each order now points back to a valid customer.

Order rows not inserted?

  • Confirm that all six customer rows were inserted before running this statement.
  • Check every status against the values permitted by orders_status_valid.

If a relationship is rejected, help me trace the order back to its customer.

  • Append the order-item seed data to 02_seed.sql by pasting this code after the order insert:
INSERT INTO order_items (
    order_id,
    product_id,
    quantity,
    unit_price
) VALUES
    (1001, 101, 2, 29.99),
    (1001, 201, 1, 59.99),
    (1002, 301, 1, 89.00),
    (1002, 102, 1, 79.00),
    (1003, 302, 1, 119.00),
    (1003, 101, 1, 29.99),
    (1004, 202, 2, 44.50),
    (1005, 201, 1, 59.99),
    (1005, 202, 1, 44.50),
    (1006, 102, 1, 79.00),
    (1006, 302, 1, 119.00),
    (1007, 301, 1, 89.00),
    (1007, 101, 1, 29.99),
    (1008, 201, 2, 59.99),
    (1009, 302, 1, 119.00),
    (1009, 202, 2, 44.50),
    (1010, 101, 3, 29.99),
    (1010, 102, 1, 79.00);

What do the order items represent?

  • Each row connects one order to one product.
  • The quantity column records how many units were sold.
  • The unit_price column preserves the sale price for that transaction.
  • The 18 rows create the many-to-many bridge between orders plus products.
  • Save 02_seed.sql.
  • Highlight only the order-item INSERT statement.
  • Click Execute script.

The output pane confirms that 18 order-item rows were inserted. The database now knows which products belong to each order.

Order-item rows not inserted?

  • Confirm that the ten orders plus six products were inserted first.
  • Check that no pair of order_id plus product_id appears twice.

If the bridge data fails, help me identify the rejected order-item row.

  • Append the fulfillment seed data to 02_seed.sql by pasting this code after the order-item insert:
INSERT INTO fulfillments (
    fulfillment_id,
    order_id,
    shipped_date,
    delivered_date,
    carrier
) VALUES
    (1, 1001, '2026-01-06', '2026-01-09', 'ParcelPro'),
    (2, 1002, '2026-01-09', '2026-01-13', 'ShipFast'),
    (3, 1003, '2026-01-13', NULL, 'ParcelPro'),
    (4, 1005, '2026-02-03', '2026-02-06', 'ParcelPro'),
    (5, 1007, '2026-02-12', '2026-02-15', 'ShipFast'),
    (6, 1008, '2026-02-19', NULL, 'ShipFast'),
    (7, 1009, '2026-03-04', '2026-03-09', 'ParcelPro');

How do the fulfillment rows show progress?

  • Delivered orders have both shipment plus delivery dates.
  • Orders still in transit use NULL for delivered_date.
  • Each order appears at most once because the order reference is unique.
  • Save 02_seed.sql.
  • Highlight only the fulfillment INSERT statement.
  • Click Execute script.

The output pane confirms that seven fulfillment rows were inserted. The model now distinguishes delivered orders from shipments still in transit.

Fulfillment rows not inserted?

  • Confirm that each referenced order exists in the orders table.
  • Check that every non-null delivery date falls on or after its shipment date.

For help with the date rule, ask me to inspect the fulfillment rows.

  • Append the final count query to 02_seed.sql by pasting this code after the fulfillment insert:
SELECT
    (SELECT COUNT(*) FROM customers) AS customers,
    (SELECT COUNT(*) FROM products) AS products,
    (SELECT COUNT(*) FROM orders) AS orders,
    (SELECT COUNT(*) FROM order_items) AS order_items,
    (SELECT COUNT(*) FROM fulfillments) AS fulfillments;

What does the count query prove?

Each scalar subquery counts one normalized table. A single result row makes missing seed records easy to spot.

  • Save 02_seed.sql.

Before you execute the count query, which five totals do you expect to see?

  • Highlight only the final SELECT query.
  • Click Execute script.

Data Output shows 6 customers, 6 products, 10 orders, 18 order items, plus 7 fulfillments.

That is the core data load complete. Every normalized table now contains the exact records needed for the later integrity tests plus analytics.

Counts do not match?

  • Compare the lower count with the insert result for that same table.
  • Execute only the missing table's INSERT statement after confirming that it did not already succeed.

If the totals still differ, help me trace the count mismatch through 02_seed.sql.

✔️ Awesome, I've got everything!

Your normalized core contains all five expected row totals. Make sure 02_seed.sql is saved in the local project folder.

ⓧ I'd like to double check the full code

Compare your saved script with this complete 02_seed.sql reference.

INSERT INTO customers (
    customer_id,
    email,
    full_name,
    region,
    created_on
) VALUES
    (1, 'maya.chen@example.com', 'Maya Chen', 'North', '2025-11-12'),
    (2, 'liam.patel@example.com', 'Liam Patel', 'South', '2025-11-18'),
    (3, 'sofia.rivera@example.com', 'Sofia Rivera', 'West', '2025-12-01'),
    (4, 'noah.williams@example.com', 'Noah Williams', 'East', '2025-12-06'),
    (5, 'emma.johnson@example.com', 'Emma Johnson', 'North', '2025-12-12'),
    (6, 'oliver.brown@example.com', 'Oliver Brown', 'South', '2025-12-19');

INSERT INTO products (
    product_id,
    sku,
    product_name,
    category,
    unit_price,
    active
) VALUES
    (101, 'SKU-101', 'Ergonomic Mouse', 'Accessories', 29.99, true),
    (102, 'SKU-102', 'Mechanical Keyboard', 'Accessories', 79.00, true),
    (201, 'SKU-201', 'Laptop Stand', 'Office', 59.99, true),
    (202, 'SKU-202', 'Desk Lamp', 'Office', 44.50, true),
    (301, 'SKU-301', 'HD Webcam', 'Video', 89.00, true),
    (302, 'SKU-302', 'Noise-Cancelling Headset', 'Audio', 119.00, true);

INSERT INTO orders (
    order_id,
    customer_id,
    order_date,
    status,
    shipping_fee
) VALUES
    (1001, 1, '2026-01-05', 'delivered', 5.00),
    (1002, 2, '2026-01-08', 'delivered', 7.50),
    (1003, 3, '2026-01-12', 'shipped', 6.00),
    (1004, 4, '2026-01-16', 'cancelled', 0.00),
    (1005, 5, '2026-02-02', 'delivered', 5.00),
    (1006, 6, '2026-02-05', 'paid', 7.00),
    (1007, 1, '2026-02-11', 'delivered', 5.00),
    (1008, 2, '2026-02-18', 'shipped', 7.50),
    (1009, 3, '2026-03-03', 'delivered', 6.00),
    (1010, 4, '2026-03-07', 'paid', 6.00);

INSERT INTO order_items (
    order_id,
    product_id,
    quantity,
    unit_price
) VALUES
    (1001, 101, 2, 29.99),
    (1001, 201, 1, 59.99),
    (1002, 301, 1, 89.00),
    (1002, 102, 1, 79.00),
    (1003, 302, 1, 119.00),
    (1003, 101, 1, 29.99),
    (1004, 202, 2, 44.50),
    (1005, 201, 1, 59.99),
    (1005, 202, 1, 44.50),
    (1006, 102, 1, 79.00),
    (1006, 302, 1, 119.00),
    (1007, 301, 1, 89.00),
    (1007, 101, 1, 29.99),
    (1008, 201, 2, 59.99),
    (1009, 302, 1, 119.00),
    (1009, 202, 2, 44.50),
    (1010, 101, 3, 29.99),
    (1010, 102, 1, 79.00);

INSERT INTO fulfillments (
    fulfillment_id,
    order_id,
    shipped_date,
    delivered_date,
    carrier
) VALUES
    (1, 1001, '2026-01-06', '2026-01-09', 'ParcelPro'),
    (2, 1002, '2026-01-09', '2026-01-13', 'ShipFast'),
    (3, 1003, '2026-01-13', NULL, 'ParcelPro'),
    (4, 1005, '2026-02-03', '2026-02-06', 'ParcelPro'),
    (5, 1007, '2026-02-12', '2026-02-15', 'ShipFast'),
    (6, 1008, '2026-02-19', NULL, 'ShipFast'),
    (7, 1009, '2026-03-04', '2026-03-09', 'ParcelPro');

SELECT
    (SELECT COUNT(*) FROM customers) AS customers,
    (SELECT COUNT(*) FROM products) AS products,
    (SELECT COUNT(*) FROM orders) AS orders,
    (SELECT COUNT(*) FROM order_items) AS order_items,
    (SELECT COUNT(*) FROM fulfillments) AS fulfillments;
Export the ER diagram

The row counts prove that data exists. The diagram proves how the five tables relate to one another.

  • Open ERD Tool in pgAdmin 4.
  • Drag customers from the object explorer onto the diagram.
  • Drag products onto the diagram.
  • Drag orders onto the diagram.
  • Drag order_items onto the diagram.
  • Drag fulfillments onto the diagram.
  • Use the auto-align control to arrange the five tables.

What should the relationships show?

  • Customers connect to orders through customer_id.
  • Orders connect to order items through order_id.
  • Products connect to order items through product_id.
  • Orders connect to fulfillments through order_id.

Before you inspect the finished diagram, which two tables do you expect to connect through order_items?

  • Inspect the relationship lines shown on the diagram.

You should see order_items linking orders to products. You should also see orders linking customers to fulfillments.

  • Click Download image in ERD Tool.
  • Save the image as erd.png in the local project folder.

The downloaded erd.png shows all five normalized tables plus their primary-key and foreign-key relationships.

Missing a table or relationship?

  • Confirm that all five normalized tables exist in the order_fulfillment database.
  • Check that the diagram uses tables from the same database where you executed 01_schema.sql.
  • Add any missing table from the object explorer before downloading the image again.

If a relationship line is missing, help me match the ER diagram to 01_schema.sql.

Your database now has five populated tables, enforced relationships, plus a visible ER diagram. Next, you will make those constraints prove themselves by rejecting deliberately invalid data.

Prove Data Integrity

Your normalized tables now hold the seeded order data. The ER diagram maps the five-table relationships.

A diagram suggests data integrity at design time. Deliberate invalid writes in pgAdmin prove that PostgreSQL enforces those rules.

In this step, get ready to:
  • Create an integrity test script containing three deliberate invalid inserts.
  • Execute each invalid insert separately to reveal its named constraint.
  • Confirm the affected table counts remain unchanged.
Test the positive-quantity rule

A CHECK constraint evaluates a rule before PostgreSQL accepts a row. This negative test uses quantity 0 while keeping the referenced order and product valid.

  • Replace the SQL editor content with the first negative test by pasting:
-- Highlight and run only one INSERT at a time.
-- Each statement is expected to fail and leave the database unchanged.

-- Expected: order_items_quantity_positive
INSERT INTO order_items (
    order_id,
    product_id,
    quantity,
    unit_price
) VALUES (1001, 302, 0, 119.00);

What Does This Test Prove?

  • The opening comments establish that every insert runs separately. Each insert is expected to fail.
  • Order 1001 already exists. Product 302 already exists.
  • Quantity 0 isolates the rule enforced by order_items_quantity_positive.

This statement is a deliberate negative test of the quantity rule.

Before you execute it, which named constraint do you expect PostgreSQL to report?

  • Highlight only the zero-quantity INSERT.
  • Click Execute script in Query Tool.

The Messages tab names order_items_quantity_positive. No order item was added.

That expected failure proves the quantity rule protects order_items at write time.

  • Use Save As in Query Tool to save 03_integrity_tests.sql inside your local project folder from earlier.

Did the Insert Succeed?

  • Confirm order_fulfillment is still the selected database.
  • Compare the order_items definition with 01_schema.sql from earlier. Confirm order_items_quantity_positive is present.
  • Stop before continuing if PostgreSQL accepted the invalid row.

Need help restoring the expected state? Help me diagnose why the zero-quantity order item was accepted.

Test relationship and uniqueness rules

A foreign key keeps every order tied to an existing customer. The missing-customer test isolates that relationship.

The next two tests change one protected value at a time. This makes each failure traceable to one named rule.

  • Append the missing-customer test below the first insert by pasting:
-- Expected: orders_customer_fk
INSERT INTO orders (
    order_id,
    customer_id,
    order_date,
    status,
    shipping_fee
) VALUES (1999, 999, '2026-03-10', 'paid', 5.00);

What Does This Test Prove?

  • Order 1999 is new. Its other values fit the declared order rules.
  • Customer 999 does not exist in customers.
  • The test isolates the relationship enforced by orders_customer_fk.

Before you execute it, which relationship should prevent order 1999 from entering the database?

  • Highlight only the missing-customer INSERT.
  • Click Execute script in Query Tool.

The Messages tab names orders_customer_fk. Order 1999 was rejected.

  • Click Save in Query Tool.

Seeing the Wrong Constraint?

  • Highlight only the insert that references customer 999.
  • Confirm customer 999 is absent from the seeded customers table.
  • Check 01_schema.sql for the named orders_customer_fk constraint.

Still seeing a different result? Help me troubleshoot the missing-customer integrity test.

A unique constraint prevents two customer records from claiming the same email address. The final invalid insert reuses an email already owned by customer 1.

  • Append the duplicate-email test below the missing-customer insert by pasting:
-- Expected: customers_email_unique
INSERT INTO customers (
    customer_id,
    email,
    full_name,
    region,
    created_on
) VALUES (99, 'maya.chen@example.com', 'Duplicate Maya', 'West', '2026-03-10');

What Does This Test Prove?

  • Customer ID 99 is new. The remaining values satisfy the required customer fields.
  • The email maya.chen@example.com already belongs to Maya Chen.
  • The test isolates the email rule enforced by customers_email_unique.

Before you execute it, which field do you expect PostgreSQL to identify as conflicting?

  • Highlight only the duplicate-email INSERT.
  • Click Execute script in Query Tool.

The Messages tab names customers_email_unique. Customer 99 was rejected.

  • Click Save in Query Tool.

Was the Duplicate Accepted?

  • Confirm the selected statement contains maya.chen@example.com.
  • Compare the customer definition with 01_schema.sql from earlier. Confirm customers_email_unique is present.
  • Stop before continuing if PostgreSQL accepted customer 99.

Need help removing an unexpected duplicate? Help me restore the customers table to its seeded state.

Confirm the tables stayed unchanged

Expected errors become convincing evidence when the protected table counts remain unchanged. A final count query checks all three affected tables together.

  • Append the final count query below the three negative tests by pasting:
-- Run this query after the three separate negative tests.
SELECT
    (SELECT COUNT(*) FROM order_items) AS order_items,
    (SELECT COUNT(*) FROM orders) AS orders,
    (SELECT COUNT(*) FROM customers) AS customers;

What Does This Query Prove?

  • Each scalar subquery counts one table targeted by an invalid insert.
  • Unchanged totals prove that none of the rejected rows entered the database.
  • The aliases label each result so the evidence is easy to read in Data Output.

Before you run the query, do you expect any count to be higher than its seeded total?

  • Highlight only the final SELECT query.
  • Click Execute script in Query Tool.

The Data Output row shows 18 order items. It also shows 10 orders and 6 customers.

That completes the evidence chain. Three invalid writes failed while every seeded total stayed intact.

  • Click Save in Query Tool.

Do the Counts Look Different?

  • Confirm the displayed columns are order_items, orders, and customers.
  • Confirm you executed each invalid insert separately.
  • Stop before the next step if any total is above its seeded value.

Need help finding an unexpected row? Help me compare my integrity-test counts with the seeded database state.

Your saved script should keep the three negative tests in execution order. The unchanged-count query belongs at the end.

✔️ Awesome, I've got everything!

Your 03_integrity_tests.sql file is saved. The database still contains its original seeded rows.

ⓧ I'd like to double check the full code

  • Compare your saved 03_integrity_tests.sql file with this complete reference.
-- Highlight and run only one INSERT at a time.
-- Each statement is expected to fail and leave the database unchanged.

-- Expected: order_items_quantity_positive
INSERT INTO order_items (
    order_id,
    product_id,
    quantity,
    unit_price
) VALUES (1001, 302, 0, 119.00);

-- Expected: orders_customer_fk
INSERT INTO orders (
    order_id,
    customer_id,
    order_date,
    status,
    shipping_fee
) VALUES (1999, 999, '2026-03-10', 'paid', 5.00);

-- Expected: customers_email_unique
INSERT INTO customers (
    customer_id,
    email,
    full_name,
    region,
    created_on
) VALUES (99, 'maya.chen@example.com', 'Duplicate Maya', 'West', '2026-03-10');

-- Run this query after the three separate negative tests.
SELECT
    (SELECT COUNT(*) FROM order_items) AS order_items,
    (SELECT COUNT(*) FROM orders) AS orders,
    (SELECT COUNT(*) FROM customers) AS customers;

How Is This File Organized?

Each expected constraint name sits directly above its invalid insert. The final query verifies that all three protected tables retained their seeded totals.

Your schema now has proof behind its design. Next up, you will use the protected data to build the analytics portfolio.

Publish the Analytics Portfolio

Your normalized PostgreSQL database has already rejected invalid writes. The next test is whether its clean structure can answer useful business questions.

Useful analysis turns the model into evidence. A public GitHub repository makes that evidence inspectable by anyone reviewing your work.

In this step, get ready to:
  • Build the three-query analytical pack.
  • Create a reusable order fulfillment reporting view.
  • Publish the complete project with evidence-based resume bullets.
Build the analytical query pack

The normalized tables separate customers, products, orders, line items, and fulfillment events. Joins bring those facts together when a business question needs them.

Why build an analytical query pack?

A query pack makes each claimed SQL skill runnable. Its outputs show how the schema supports revenue analysis, product comparisons, regional rankings, and cumulative performance.

  • Switch back to the pgAdmin 4 Query Tool connected to the order_fulfillment database.
  • Replace the current editor contents with the category revenue query below:
-- Revenue by category, excluding cancelled orders.
SELECT
    p.category,
    SUM(oi.quantity * oi.unit_price) AS product_revenue
FROM orders AS o
JOIN order_items AS oi
    ON oi.order_id = o.order_id
JOIN products AS p
    ON p.product_id = oi.product_id
WHERE o.status <> 'cancelled'
GROUP BY p.category
ORDER BY product_revenue DESC;

What does this query do?

  • The two joins connect each order to its line items and product category.
  • The status filter removes cancelled orders from the revenue calculation.
  • The grouped SUM() calculates product revenue for each category.
  • Execute the category revenue query with Execute script.
  • Save the editor contents as 04_analytics.sql in your local project folder with Save As.

Data Output shows four categories. Accessories leads with 446.93 in product revenue.

Revenue query not returning four rows?

  • Confirm that Query Tool is connected to the order_fulfillment database.
  • Check that the join aliases match o, oi, and p.
  • Confirm that the cancelled status filter remains in the query.

Still stuck? Help me diagnose my category revenue query.

A correlated subquery can compare each product with an aggregate from its own category. The outer alias determines which category the inner query averages.

  • Place the cursor after the category revenue query in 04_analytics.sql.
  • Press Enter twice to leave one blank line.
  • Append the correlated category-price query below:
-- Products priced above the average for their own category.
SELECT
    p.product_id,
    p.sku,
    p.product_name,
    p.category,
    p.unit_price
FROM products AS p
WHERE p.unit_price > (
    SELECT AVG(p2.unit_price)
    FROM products AS p2
    WHERE p2.category = p.category
)
ORDER BY p.category, p.unit_price DESC;

How does the correlation work?

  • The outer query examines one product through alias p.
  • The inner query calculates the category average through alias p2.
  • The condition linking p2.category to p.category recalculates the average for the current outer product.
  • Highlight only the newly appended query.
  • Execute the selection with Execute script.
  • Save 04_analytics.sql.

Data Output contains two rows. They are the Mechanical Keyboard and the Laptop Stand.

Seeing the wrong product comparison?

  • Confirm that the inner query uses alias p2.
  • Check that the correlation compares p2.category with p.category.
  • Use the greater-than operator so products equal to their category average stay out of the result.

Need another pair of eyes? Help me trace my correlated subquery.

The final analysis uses a common table expression to calculate one total per order. Two window functions then analyze those totals without collapsing the result rows.

The CTE and final selection form one SQL statement. The two snippets below are executed together after both are in the editor.

  • Place the cursor after the correlated subquery.
  • Press Enter twice to leave one blank line.
  • Append the order-total CTE below:
-- Order value, regional rank, and running regional revenue.
WITH order_totals AS (
    SELECT
        o.order_id,
        o.customer_id,
        o.order_date,
        SUM(oi.quantity * oi.unit_price) + o.shipping_fee AS order_value
    FROM orders AS o
    JOIN order_items AS oi
        ON oi.order_id = o.order_id
    WHERE o.status <> 'cancelled'
    GROUP BY
        o.order_id,
        o.customer_id,
        o.order_date,
        o.shipping_fee
)

What does the CTE calculate?

The order_totals CTE creates one row per non-cancelled order. Each order_value includes product revenue plus the order shipping fee.

The closing selection below consumes this CTE. Together, the snippets remain one statement.

  • Append the final selection directly below the closing parenthesis:
SELECT
    ot.order_id,
    c.full_name,
    c.region,
    ot.order_date,
    ot.order_value,
    RANK() OVER (
        PARTITION BY c.region
        ORDER BY ot.order_value DESC
    ) AS regional_order_rank,
    SUM(ot.order_value) OVER (
        PARTITION BY c.region
        ORDER BY ot.order_date, ot.order_id
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_region_revenue
FROM order_totals AS ot
JOIN customers AS c
    ON c.customer_id = ot.customer_id
ORDER BY c.region, ot.order_date, ot.order_id;

How do the windows analyze each region?

  • The customer join supplies the name and region for every order total.
  • The RANK() window orders each region by order value.
  • The running SUM() accumulates regional revenue in order-date sequence.

Before you execute the complete statement, how many rows do you expect after the cancelled order is excluded?

  • Highlight the complete CTE statement from its comment through the final semicolon.
  • Execute the selection with Execute script.
  • Save 04_analytics.sql.

Data Output shows nine non-cancelled orders. Each row includes regional_order_rank plus running_region_revenue.

Window results look incomplete?

  • Confirm that both window expressions include PARTITION BY c.region.
  • Check that the running total orders by ot.order_date followed by ot.order_id.
  • Confirm that the CTE excludes cancelled orders.

Still seeing unexpected rankings? Help me debug the CTE and window functions.

✔️ Awesome, I've got everything!

Your 04_analytics.sql file now contains all three analytical queries. Make sure the file is saved in your local project folder.

ⓧ I'd like to double check the full code

Compare your saved file with this complete analytical query pack.

-- Revenue by category, excluding cancelled orders.
SELECT
    p.category,
    SUM(oi.quantity * oi.unit_price) AS product_revenue
FROM orders AS o
JOIN order_items AS oi
    ON oi.order_id = o.order_id
JOIN products AS p
    ON p.product_id = oi.product_id
WHERE o.status <> 'cancelled'
GROUP BY p.category
ORDER BY product_revenue DESC;

-- Products priced above the average for their own category.
SELECT
    p.product_id,
    p.sku,
    p.product_name,
    p.category,
    p.unit_price
FROM products AS p
WHERE p.unit_price > (
    SELECT AVG(p2.unit_price)
    FROM products AS p2
    WHERE p2.category = p.category
)
ORDER BY p.category, p.unit_price DESC;

-- Order value, regional rank, and running regional revenue.
WITH order_totals AS (
    SELECT
        o.order_id,
        o.customer_id,
        o.order_date,
        SUM(oi.quantity * oi.unit_price) + o.shipping_fee AS order_value
    FROM orders AS o
    JOIN order_items AS oi
        ON oi.order_id = o.order_id
    WHERE o.status <> 'cancelled'
    GROUP BY
        o.order_id,
        o.customer_id,
        o.order_date,
        o.shipping_fee
)
SELECT
    ot.order_id,
    c.full_name,
    c.region,
    ot.order_date,
    ot.order_value,
    RANK() OVER (
        PARTITION BY c.region
        ORDER BY ot.order_value DESC
    ) AS regional_order_rank,
    SUM(ot.order_value) OVER (
        PARTITION BY c.region
        ORDER BY ot.order_date, ot.order_id
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_region_revenue
FROM order_totals AS ot
JOIN customers AS c
    ON c.customer_id = ot.customer_id
ORDER BY c.region, ot.order_date, ot.order_id;
Create the reporting view

A view gives repeated reporting logic a stable name. Consumers can query one object instead of rebuilding the joins and calculations each time.

  • Clear the saved analytical queries from the Query Tool editor.
  • Copy the reporting view definition below into the empty editor:
CREATE OR REPLACE VIEW order_fulfillment_summary AS
SELECT
    o.order_id,
    c.full_name AS customer_name,
    c.region,
    o.order_date,
    o.status,
    SUM(oi.quantity * oi.unit_price) + o.shipping_fee AS order_total,
    f.carrier,
    f.shipped_date,
    f.delivered_date,
    f.delivered_date - f.shipped_date AS delivery_days
FROM orders AS o
JOIN customers AS c
    ON c.customer_id = o.customer_id
JOIN order_items AS oi
    ON oi.order_id = o.order_id
LEFT JOIN fulfillments AS f
    ON f.order_id = o.order_id
GROUP BY
    o.order_id,
    c.full_name,
    c.region,
    o.order_date,
    o.status,
    o.shipping_fee,
    f.carrier,
    f.shipped_date,
    f.delivered_date;

What does this view contain?

  • The joins combine order headers with customer details and line items.
  • The left join keeps orders that do not have a fulfillment record.
  • The grouped calculation produces one order_total per order.
  • Subtracting the shipped date from the delivered date produces delivery_days for completed deliveries.
  • Execute the view definition with Execute script.
  • Confirm that the Messages tab reports a successful view operation.

The database now has a reusable reporting layer named order_fulfillment_summary.

  • Place the cursor after the view definition.
  • Press Enter twice to leave one blank line.
  • Append the view query below:
SELECT *
FROM order_fulfillment_summary
ORDER BY order_id;

Why query the view immediately?

The final query verifies that the view is readable. Ordering by order_id also makes the ten seeded orders easy to inspect.

Before you execute the query, do you expect orders without fulfillment records to remain visible?

  • Highlight only the final view query.
  • Execute the selection with Execute script.
  • Save the complete editor contents as 05_reporting_view.sql in your local project folder with Save As.

Data Output returns ten rows. You can see order totals, carrier details, and delivery durations in one result.

Orders without a fulfillment record remain present because the view uses a left join. Their fulfillment fields contain null values.

View query not returning ten rows?

  • Confirm that the fulfillment relationship uses LEFT JOIN.
  • Check that every non-aggregated selected column appears in the grouping list.
  • Confirm that the final query reads from order_fulfillment_summary.

Need help finding the mismatch? Help me troubleshoot the reporting view.

✔️ Awesome, I've got everything!

Your 05_reporting_view.sql file creates the view and queries all ten rows.

ⓧ I'd like to double check the full code

Compare your saved file with this complete reporting view script.

CREATE OR REPLACE VIEW order_fulfillment_summary AS
SELECT
    o.order_id,
    c.full_name AS customer_name,
    c.region,
    o.order_date,
    o.status,
    SUM(oi.quantity * oi.unit_price) + o.shipping_fee AS order_total,
    f.carrier,
    f.shipped_date,
    f.delivered_date,
    f.delivered_date - f.shipped_date AS delivery_days
FROM orders AS o
JOIN customers AS c
    ON c.customer_id = o.customer_id
JOIN order_items AS oi
    ON oi.order_id = o.order_id
LEFT JOIN fulfillments AS f
    ON f.order_id = o.order_id
GROUP BY
    o.order_id,
    c.full_name,
    c.region,
    o.order_date,
    o.status,
    o.shipping_fee,
    f.carrier,
    f.shipped_date,
    f.delivered_date;

SELECT *
FROM order_fulfillment_summary
ORDER BY order_id;
Publish the portfolio evidence

A portfolio claim becomes stronger when its proof is easy to inspect. Your repository will connect each resume bullet to runnable SQL, a visible schema, and documented outputs.

  • Return to the public postgresql-order-fulfillment repository from earlier.
  • Open README.md from the repository file list.
  • Select the pencil icon in the upper-right corner of the file view.
  • Replace the starter README contents with the first section below:
# PostgreSQL Order Fulfillment Intelligence

This project turns a flawed flat order-intake dataset into a normalized PostgreSQL database for customer orders and fulfillment reporting.

## Business problem

Repeated customer and product attributes make a flat intake table vulnerable to inconsistent facts. The diagnostic script demonstrates this with conflicting prices for the same SKU. The normalized model separates stable entities from order events and preserves the sale price on each order item as a historical transaction fact.

## Normalized core

- `customers`: customer identity and region
- `products`: product catalog and current list price
- `orders`: order header and lifecycle status
- `order_items`: many-to-many bridge between orders and products, including quantity and sale price
- `fulfillments`: one fulfillment record per shipped order

The repository includes `erd.png`, exported from pgAdmin 4, to show the primary-key and foreign-key relationships.

What does this section establish?

The opening connects the technical model to the inconsistent source-data problem. The table list explains where stable facts and transaction facts belong.

  • Place the cursor after the ER diagram paragraph.
  • Press Enter twice to leave one blank line.
  • Append the demonstrated SQL, run order, and resume bullets below:
## Demonstrated SQL

- Primary keys, foreign keys, `NOT NULL`, `UNIQUE`, and `CHECK` constraints
- Multi-table inner and left joins
- Correlated scalar subquery
- Aggregate functions with grouping
- Common table expression
- `RANK()` and running `SUM()` window calculations
- Reusable reporting view
- Deliberate negative tests for data integrity

## Run order

1. `00_raw_order_intake.sql`
2. `01_schema.sql`
3. `02_seed.sql`
4. Run each statement in `03_integrity_tests.sql` separately
5. `04_analytics.sql`
6. `05_reporting_view.sql`

Run the scripts in the `order_fulfillment` database with pgAdmin Query Tool.

## Resume bullets

- Designed and implemented a normalized five-table PostgreSQL order-fulfillment database with primary and foreign keys, required columns, and named `UNIQUE` and `CHECK` constraints; validated integrity through deliberate negative tests.
- Built an analytical SQL portfolio using multi-table joins, a correlated subquery, CTEs, aggregate functions, ranking and running-total window functions, and a reusable fulfillment reporting view.

Why are these resume bullets defensible?

Every named capability appears in a repository file. A reviewer can run the scripts to inspect the schema, reproduce the rejected writes, and view the analytical outputs.

The bullets stay within the work you completed. They avoid claims about triggers, stored procedures, indexes, or execution-plan tuning.

  • Select Preview above the README editor.
  • Confirm that the headings, lists, run order, and resume bullets render clearly.
  • Select Commit changes....
  • Enter Document project evidence in the Commit message field.
  • Keep the current branch selected.
  • Select Commit changes.

Your repository front page now explains the business problem and links each resume claim to concrete project evidence.

  • Open the committed README.md file.
  • Select Raw in the upper-right corner.
  • Press Ctrl+S to save the raw file.
  • Choose the local project folder in the save dialog.
  • Set the filename to README.md.
  • Confirm the save.

✔️ Awesome, I've got everything!

Your local project folder now contains the same README.md shown on GitHub.

ⓧ I'd like to double check the full code

Compare the local and published README with this complete file.

# PostgreSQL Order Fulfillment Intelligence

This project turns a flawed flat order-intake dataset into a normalized PostgreSQL database for customer orders and fulfillment reporting.

## Business problem

Repeated customer and product attributes make a flat intake table vulnerable to inconsistent facts. The diagnostic script demonstrates this with conflicting prices for the same SKU. The normalized model separates stable entities from order events and preserves the sale price on each order item as a historical transaction fact.

## Normalized core

- `customers`: customer identity and region
- `products`: product catalog and current list price
- `orders`: order header and lifecycle status
- `order_items`: many-to-many bridge between orders and products, including quantity and sale price
- `fulfillments`: one fulfillment record per shipped order

The repository includes `erd.png`, exported from pgAdmin 4, to show the primary-key and foreign-key relationships.

## Demonstrated SQL

- Primary keys, foreign keys, `NOT NULL`, `UNIQUE`, and `CHECK` constraints
- Multi-table inner and left joins
- Correlated scalar subquery
- Aggregate functions with grouping
- Common table expression
- `RANK()` and running `SUM()` window calculations
- Reusable reporting view
- Deliberate negative tests for data integrity

## Run order

1. `00_raw_order_intake.sql`
2. `01_schema.sql`
3. `02_seed.sql`
4. Run each statement in `03_integrity_tests.sql` separately
5. `04_analytics.sql`
6. `05_reporting_view.sql`

Run the scripts in the `order_fulfillment` database with pgAdmin Query Tool.

## Resume bullets

- Designed and implemented a normalized five-table PostgreSQL order-fulfillment database with primary and foreign keys, required columns, and named `UNIQUE` and `CHECK` constraints; validated integrity through deliberate negative tests.
- Built an analytical SQL portfolio using multi-table joins, a correlated subquery, CTEs, aggregate functions, ranking and running-total window functions, and a reusable fulfillment reporting view.
  • Return to the main page of postgresql-order-fulfillment.
  • Select Add file.
  • Select Upload files.
  • Drag 00_raw_order_intake.sql, 01_schema.sql, 02_seed.sql, 03_integrity_tests.sql, 04_analytics.sql, 05_reporting_view.sql, and erd.png into the upload area.
  • Wait until every filename appears in the upload list.
  • Keep the current branch selected.
  • Select Commit changes to publish the uploaded files.

Upload missing a file?

  • Confirm that all six SQL files are in the same local project folder.
  • Check that the ER diagram filename is exactly erd.png.
  • Upload any missing file through the same Add file flow.

Need help comparing the local folder with GitHub? Help me find the missing portfolio file.

Before you check the repository front page, which eight filenames do you expect to see?

  • Return to the repository front page.
  • Confirm that README.md is visible.
  • Confirm that all six baseline SQL files are visible.
  • Confirm that erd.png is visible.

You should see eight portfolio files in total. The rendered README should show the business problem, demonstrated SQL, run order, and two resume bullets.

That is the portfolio evidence assembled. Your public repository now supports each database design and analytical SQL claim with code that another person can inspect and rerun.

Secret mission

Add a Carrier SLA Report

The reporting view already records delivery duration for every fulfilled order. Build a carrier SLA report that counts deliveries above a three-day target by carrier. Publish the successful result as portfolio evidence.

Clean Up Your Resources

Clean Up Your Resources

None of these resources incurs an ongoing charge. Choose whether to keep everything for your portfolio, pause your work, or delete it entirely.

Resources you used:

  • One local PostgreSQL database named order_fulfillment, available through pgAdmin 4.
  • One local project folder containing seven SQL scripts, README.md, and erd.png.
  • One public GitHub repository named postgresql-order-fulfillment containing the complete portfolio project.

Keep everything running

No action is needed. Choose this option if you want to keep demonstrating the database or improving the portfolio project.

  • Keep the order_fulfillment database available in pgAdmin 4.
  • Keep the local project folder with your SQL scripts and ER diagram.
  • Keep the public postgresql-order-fulfillment repository available for sharing.

Pause - I'll come back to this later

Close your active project windows while leaving every database object and file unchanged. You can resume later without rebuilding anything.

  • Close the Query Tool tab connected to order_fulfillment.
  • Close pgAdmin 4.
  • Close the browser tab showing the postgresql-order-fulfillment repository.
  • Leave the local project folder unchanged.
  • Leave the public repository unchanged.

Delete - I don't want to use this again

Deletion is permanent. This option removes your local database plus the files that support your public portfolio evidence.

Delete the local database:

  • Return to pgAdmin 4 from earlier.
  • Close the Query Tool tab connected to order_fulfillment.
  • Expand your local server in the Browser tree.
  • Expand the databases collection.
  • Right-click order_fulfillment.
  • Choose the database deletion action from the context menu.
  • Confirm the deletion when pgAdmin asks for approval.
  • Refresh the databases collection.

You should no longer see order_fulfillment in the Browser tree.

Delete the local project folder:

  • Press the Windows key to open search.
  • Type File Explorer.
  • Press Enter.
  • Return to the location containing the local project folder from earlier.
  • Select the local project folder.
  • Press Shift+Delete.
  • Confirm permanent deletion.

You should no longer see the local project folder in its previous location.

Delete the public GitHub repository:

  • Return to the postgresql-order-fulfillment repository from earlier.
  • Open the repository settings.
  • Scroll to the section containing destructive repository actions.
  • Start the action that permanently deletes the repository.
  • Enter postgresql-order-fulfillment when GitHub asks you to confirm the repository name.
  • Complete the final deletion confirmation.
  • Return to your GitHub repository list.

You should no longer see postgresql-order-fulfillment in your repository list.

Nice Work!

Nice Work!

You did it! You applied normalization to turn flawed intake data into a PostgreSQL database backed by trustworthy portfolio evidence.

You've learned how to:

  • Designed a five-table relational core for customers, products, orders, order items, and fulfillments. Named constraints visibly reject invalid quantities, missing customers, and duplicate emails.
  • Built an analytical SQL portfolio that calculates category revenue. A correlated subquery finds products priced above their category average. Window functions rank orders and calculate running regional revenue.
  • Published a reproducible portfolio in GitHub. The public repository pairs runnable scripts with an ER diagram. Its README ties each resume bullet to implemented evidence.
  • Completed the Secret Mission by adding a carrier SLA report. A filtered aggregate identifies deliveries that exceed the three-day target for each carrier.

Ready to quiz yourself?