Project Wrap-Up and Extension Challenges

Objectives

By the end of this lesson, you should be able to:

• Describe the complete Fernwood Supply Co. database, end to end
• Extend an existing schema with a new table and relationship on your own
• Identify which module’s concepts a new requirement calls for

💡 Why this matters: A real database is never really “finished,” new requirements show up, new reports get requested, new tables get added to an already-live schema. This lesson closes out the guided part of the project and hands the next steps to you.

⚠️ A note on verification: the two example queries in this lesson were run against a real, live PostgreSQL 18 database, using the completed project schema and data from the previous lessons.

What Was Built

Over this module, a complete relational database was designed from a plain-language brief (Lesson 1), built as seven related tables with real constraints (Lesson 2), populated and maintained with realistic data changes (Lesson 3), queried for real business answers using joins, aggregation, CTEs, and subqueries (Lesson 4), and finally packaged with a view, sped up with indexes, and protected with a transaction (Lesson 5). Every relationship shape from Module 9 appears in this schema: one-to-many (categories to products, customers to orders), one-to-one (employees to employee_profiles), self-referencing (employees.manager_id), and many-to-many (orders and products, through order_items).

Two More Example Reports

Two additional questions this schema can already answer, without any new tables:

SELECT c.city, ROUND(AVG(order_totals.order_total), 2) AS avg_order_value
FROM customers c
JOIN (
  SELECT o.id, o.customer_id, SUM(oi.quantity * oi.unit_price) AS order_total
  FROM orders o JOIN order_items oi ON o.id = oi.order_id
  WHERE o.status = 'completed'
  GROUP BY o.id, o.customer_id
) AS order_totals ON order_totals.customer_id = c.id
GROUP BY c.city
ORDER BY c.city;

   city   | avg_order_value
-----------+-----------------
 Austin   |           89.99
 Denver   |           82.87

SELECT c.name, COUNT(o.id) AS completed_orders
FROM customers c
JOIN orders o ON o.customer_id = c.id AND o.status = 'completed'
GROUP BY c.name
HAVING COUNT(o.id) > 1;

     name       | completed_orders
-----------------+-------------------
 Riley Nguyen   |                 2

The first report uses a FROM-clause subquery (Module 11) to compute per-order totals before averaging them by city, the second uses HAVING (Module 8) to isolate repeat customers, both built entirely from tools already covered.

Extension Challenges

These challenges are intentionally open-ended, no verified solution is provided, working through the design decisions yourself is the point.

Add product reviews. Customers should be able to leave a rating (1 to 5) and a comment on products they’ve purchased. Design the table, decide its relationships to customers and products, and consider whether a CHECK constraint (Module 5) belongs on the rating column.

Add shipping addresses. A customer might have multiple saved addresses, and each order should record which address it shipped to. Decide whether this is one-to-many or many-to-many, and where the foreign key belongs.

Add a discount system. Some orders qualify for a percentage discount. Decide whether this belongs as a column on orders, a separate discounts table, or something else, and write a report showing total revenue with and without discounts applied.

Write a churn report. Using order_date, identify customers who placed a completed order more than 60 days ago but have placed none since (hint: this is a correlated subquery or NOT EXISTS, similar to Lesson 4’s Report 5, combined with a date comparison).

Add inventory tracking. Instead of a simple in_stock boolean, track an actual quantity_on_hand per product, and write a transaction (Module 13) that decreases it correctly whenever an order is placed, refusing the order if there isn’t enough stock (hint: a CHECK constraint preventing negative stock, combined with ROLLBACK on failure, mirrors this module’s account balance example directly).

Try It

1. Pick one extension challenge above and sketch its schema change the way Lesson 1 sketched the original project, entities and relationships, before writing any SQL.
2. Implement that sketch as real CREATE TABLE statements, verified against a live database.
3. Write one report that uses your new table, combining it with at least one table from the original schema.
4. Reflect, in your own words, on which module’s concepts felt most directly useful while working through this capstone, and why.

Recap

This project combined every module in the course: database and PostgreSQL foundations, SQL basics, table creation, CRUD operations, altering tables, querying data, built-in functions, grouping and aggregation, relationships, joins, advanced queries, database objects and performance, and transactions, into one coherent, working database. The extension challenges are the natural next step, the same design-then-build process from Lesson 1 through Lesson 5, applied to genuinely new requirements.

This is the final lesson of SQL Fundamentals with PostgreSQL.