# Week 09 – Task 1: PostgreSQL Relational Model, SQL and Normalization

This note explains the SQL concepts used in the Week 09 e-commerce database exercise. The goal is not just to write queries that work, but to understand why the tables are designed this way and what each query is doing.

The examples use a small e-commerce application with customers, products, orders and order items.

1. What is a relational database?

A relational database stores information in tables. Each table represents one type of thing, and each row represents one record.

For this exercise, we use four main tables:

Instead of putting every detail into one huge table, we split information into related tables. This makes data easier to maintain and avoids repeating the same information unnecessarily.

2. Primary keys and foreign keys

Primary key (PK)

A primary key uniquely identifies each row in a table. It must be unique and cannot be NULL.

CREATE TABLE users (
    user_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    full_name VARCHAR(120) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE
);

Here, user_id identifies each customer. Two users cannot have the same user_id.

Foreign key (FK)

A foreign key connects one table to another. It ensures that a row refers to a record that actually exists.

CREATE TABLE orders (
    order_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(user_id)
);

The user_id in orders points to users.user_id. That means an order cannot be created for a customer who does not exist.

Other constraints used in the schema

• NOT NULL – the column must have a value.
• UNIQUE – duplicate values are not allowed.
• CHECK – values must satisfy a condition.
• DEFAULT – PostgreSQL supplies a value when one is not provided.

Example:

unit_price NUMERIC(12, 2) NOT NULL CHECK (unit_price >= 0)

This makes sure a product cannot have a negative price.

3. Understanding table relationships

Relationships describe how records in different tables are connected.

One-to-one (1:1)

One row in Table A is related to one row in Table B.

Example: one user has one profile-settings record. A UNIQUE foreign key can help enforce this relationship.

One-to-many (1:N)

One row in Table A can be related to many rows in Table B.

Example: one customer can place many orders, but each order belongs to one customer.

users (1) -------- (many) orders

The foreign key is placed on the many side: orders.user_id.

Many-to-many (N:N)

Many rows in Table A can be related to many rows in Table B.

Example: one order can contain many products, and one product can appear in many orders.

We handle this using a junction table called order_items:

orders (1) ---- (many) order_items (many) ---- (1) products

CREATE TABLE order_items (
    order_id BIGINT NOT NULL REFERENCES orders(order_id),
    product_id BIGINT NOT NULL REFERENCES products(product_id),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price NUMERIC(12, 2) NOT NULL CHECK (unit_price >= 0),
    PRIMARY KEY (order_id, product_id)
);

The composite primary key (order_id, product_id) prevents the same product from appearing in two separate lines for the same order. If the customer wants more units, the quantity is increased.

Notice that unit_price is stored here as well. It represents the price charged when the order was placed. If the product's current price changes later, the old order should still show the original price.

4. SQL joins – getting data from related tables

A join combines rows from multiple tables using a related column.

INNER JOIN

Returns only rows that have a match in both tables.

For example, show orders along with the customer who placed each order:

SELECT
    o.order_id,
    u.full_name,
    o.order_date,
    o.status
FROM orders AS o
INNER JOIN users AS u
    ON u.user_id = o.user_id;

If a customer has not placed an order, that customer will not appear in this result.

LEFT JOIN

Returns every row from the left table, plus matching rows from the right table. If there is no match, the right-side columns contain NULL.

SELECT
    u.user_id,
    u.full_name,
    o.order_id
FROM users AS u
LEFT JOIN orders AS o
    ON o.user_id = u.user_id;

This is useful when we want to include customers who have never placed an order.

RIGHT JOIN

Returns every row from the right table and matching rows from the left table.

SELECT
    u.full_name,
    o.order_id
FROM users AS u
RIGHT JOIN orders AS o
    ON o.user_id = u.user_id;

In practice, a RIGHT JOIN can usually be rewritten as a LEFT JOIN by swapping the table order.

FULL OUTER JOIN

Returns matching rows and also keeps unmatched rows from both sides.

SELECT
    p.product_id,
    p.product_name,
    oi.order_id
FROM products AS p
FULL OUTER JOIN order_items AS oi
    ON oi.product_id = p.product_id;

This can be useful for data checks, because it can reveal records that do not have a match on either side.

5. Aggregations – calculating totals

Aggregate functions calculate a result from multiple rows.

Common examples:

• COUNT() – counts rows.
• SUM() – adds values.
• AVG() – calculates an average.
• MIN() – finds the smallest value.
• MAX() – finds the largest value.

For example, calculate the total value of every order:

SELECT
    o.order_id,
    SUM(oi.quantity * oi.unit_price) AS order_total
FROM orders AS o
JOIN order_items AS oi
    ON oi.order_id = o.order_id
GROUP BY o.order_id
ORDER BY o.order_id;

We multiply quantity by unit price to get each line's value, then add the lines belonging to the same order.

GROUP BY

GROUP BY puts rows with the same value into groups. Aggregate functions then calculate one result per group.

HAVING

HAVING filters groups after aggregation. It is different from WHERE, which filters individual rows before grouping.

SELECT
    u.user_id,
    u.full_name,
    SUM(oi.quantity * oi.unit_price) AS total_spend
FROM users AS u
JOIN orders AS o ON o.user_id = u.user_id
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.status <> 'cancelled'
GROUP BY u.user_id, u.full_name
HAVING SUM(oi.quantity * oi.unit_price) >= 4000
ORDER BY total_spend DESC;

This query finds customers whose non-cancelled orders add up to at least 4,000.

6. Subqueries

A subquery is a query inside another query. It is useful when the outer query needs a value calculated by another query.

Example: find products priced above the average product price.

SELECT
    product_id,
    product_name,
    unit_price
FROM products
WHERE unit_price > (
    SELECT AVG(unit_price)
    FROM products
);

The inner query calculates the average price. The outer query returns products whose price is higher than that average.

Correlated subquery

A correlated subquery uses a value from the outer query. It is evaluated in relation to each row being considered.

Example: find each customer's most recent order.

SELECT
    u.user_id,
    u.full_name,
    o.order_id,
    o.order_date
FROM users AS u
JOIN orders AS o ON o.user_id = u.user_id
WHERE o.order_date = (
    SELECT MAX(o2.order_date)
    FROM orders AS o2
    WHERE o2.user_id = u.user_id
);

The inner query uses u.user_id from the outer query, so it finds the latest order for that particular customer.

7. Normalization – keeping data organized

Normalization is the process of organizing tables to reduce unnecessary duplication and prevent data problems.

First Normal Form (1NF)

A table is in 1NF when each column contains a single value and there are no repeating groups.

Bad design:

Better design: store each product as a separate row in order_items.

Second Normal Form (2NF)

A table must first satisfy 1NF. Also, every non-key column must depend on the whole primary key, not just part of it.

In order_items, the primary key is (order_id, product_id). The quantity and unit_price describe that product in that specific order, so they depend on both parts of the key.

Third Normal Form (3NF)

A table must first satisfy 2NF. Non-key columns should depend on the key, the whole key, and nothing but the key – not on another non-key column.

For example, customer name and email belong in users, not repeated in every orders row. Product names belong in products, not repeated in every order item.

Why does this matter? If a customer's email changes, we update it in one place instead of searching through every order.

8. A quick look at the sample schema

The exercise creates the following tables:

CREATE TABLE users (
    user_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    full_name VARCHAR(120) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products (
    product_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    product_name VARCHAR(160) NOT NULL,
    sku VARCHAR(64) NOT NULL UNIQUE,
    unit_price NUMERIC(12, 2) NOT NULL CHECK (unit_price >= 0),
    active BOOLEAN NOT NULL DEFAULT TRUE
);

CREATE TABLE orders (
    order_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(user_id) ON DELETE RESTRICT,
    order_date TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) NOT NULL DEFAULT 'pending'
        CHECK (status IN ('pending', 'paid', 'shipped', 'delivered', 'cancelled'))
);

CREATE TABLE order_items (
    order_id BIGINT NOT NULL REFERENCES orders(order_id) ON DELETE CASCADE,
    product_id BIGINT NOT NULL REFERENCES products(product_id) ON DELETE RESTRICT,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price NUMERIC(12, 2) NOT NULL CHECK (unit_price >= 0),
    PRIMARY KEY (order_id, product_id)
);

A few details worth noticing:

• GENERATED BY DEFAULT AS IDENTITY lets PostgreSQL generate IDs automatically, while still allowing explicit IDs in seed data.
• TIMESTAMPTZ stores timestamps with time-zone awareness.
• ON DELETE RESTRICT prevents deleting a customer or product while related records still depend on it.
• ON DELETE CASCADE removes order-item rows when their parent order is deleted.
• The status CHECK constraint allows only the listed order states.

9. Indexes in this exercise

An index helps PostgreSQL find rows without always scanning the entire table. It is similar to the index at the back of a textbook.

The schema creates these indexes:

CREATE INDEX idx_orders_user_date
    ON orders(user_id, order_date DESC);

CREATE INDEX idx_order_items_product
    ON order_items(product_id);

The first index can help when looking up a customer's orders in date order. The second can help find order items for a particular product.

Indexes are not free: they use storage and need to be updated when rows are inserted, updated or deleted. Add them for real query patterns, not automatically on every column.

10. Running the exercise

Use a practice database because the SQL setup script drops and recreates the dedicated ecommerce schema.

createdb ecommerce_db
psql -d ecommerce_db -f week-09/Task-01/ecommerce-schema.sql

You can also connect first and run individual queries:

psql -d ecommerce_db

Then paste a query from the SQL file.

To inspect the tables in psql:

\dt ecommerce.*

To check the sample users:

SELECT * FROM ecommerce.users;

11. Quick revision

Final takeaway

The main idea of Task 1 is to model real-world data properly. Customers, products, orders and order lines each have their own tables. Primary keys identify records, foreign keys connect them, constraints protect data quality, joins bring related information together, and normalization reduces unnecessary repetition.

Once the relationships make sense, the SQL queries become much easier to understand.