Capstone Lab: Full Database Design, Implementation, and Documentation

Capstone Lab: Full Database Design, Implementation, and Documentation

This capstone lab is your opportunity to demonstrate every skill developed in this course. Working independently, you will take a business scenario from a blank slate to a fully documented, production-quality PostgreSQL database. Read this page carefully before you open any tool.

Understanding the Assignment Phases

The lab has three phases that build on each other. Do not skip ahead — instructors grade each phase, and errors made early multiply into larger problems later.

Phase 1 – Requirements and Design. Read the scenario brief completely before writing a single line of SQL. Identify the entities (people, places, things, events) and the relationships between them. Write a short requirements document listing what data the system must store and what questions it must answer. Then draw a normalized ER diagram in draw.io, labeling primary keys, foreign keys, cardinality (one-to-many, many-to-many), and any weak entities. Check that your design reaches at least Third Normal Form (3NF): eliminate repeating groups, remove partial dependencies, and remove transitive dependencies. Have a peer or the instructor review the diagram before you write DDL.

Phase 2 – Implementation and Queries. Translate your ER diagram into a DDL script executed in psql or pgAdmin against PostgreSQL 16. Every table must have a primary key constraint. Foreign keys, NOT NULL, UNIQUE, and CHECK constraints must appear wherever the requirements document calls for them. Create at least one view that simplifies a common query and at least one function (PL/pgSQL or SQL-language) that encapsulates reusable logic, such as calculating totals or formatting output.

Populate the database with at least 200 rows of realistic seed data. You may write INSERT statements by hand or use a Python seed script — either approach is acceptable. Realistic means plausible names, dates, and amounts; avoid placeholder text such as "test1" or "aaa."

Next, answer the ten business questions in the scenario brief. Annotate each query with a comment block explaining what business question it answers and why you structured it that way. At least one query must use a Common Table Expression (CTE) and at least one must use an aggregation with a HAVING clause. Test every query to confirm it returns correct results before submission.

Phase 3 – Documentation and Defense. Write a two-page technical documentation report using the provided template. Cover these five areas: (1) schema design decisions — why you chose your table structure; (2) normalization rationale — which normal forms you applied and how; (3) index choices with EXPLAIN ANALYZE evidence — paste trimmed output showing the index being used; (4) role and privilege design — which database roles you created, what permissions they hold, and why; (5) backup procedure — the pg_dump command and schedule you would use in a production environment.

Finally, present your work in a 10-minute live defense. Spend roughly two minutes on the ER diagram, three minutes walking through key queries on screen, and five minutes on documentation and questions from the panel. Be ready to run any query live and explain any design choice.

Using EXPLAIN ANALYZE Safely

Running EXPLAIN ANALYZE executes the query against real data, which matters on large tables. In a production environment, never run EXPLAIN ANALYZE on a write operation without a transaction rollback wrapper. In this lab your database is local and contains seed data only, so the risk is low, but form the right habit now: wrap EXPLAIN ANALYZE on INSERT, UPDATE, or DELETE in BEGIN / ROLLBACK so no data changes are committed accidentally.

Backup with pg_dump

Your documentation must describe a backup procedure. The standard tool is pg_dump, which produces a consistent snapshot of a single database without locking other users out. A plain-text dump can be restored with psql; a custom-format dump uses pg_restore. Store dump files in a location separate from the database server — a dump on the same disk as the data does not protect you from disk failure. For your documentation, describe the format flag you would use, the target file path, and how often you would schedule the backup.

Rubric Overview

The rubric awards points in five areas: DDL correctness (25%), query correctness (25%), normalization and ER diagram (20%), documentation quality (15%), and oral defense (15%). The passing threshold is 80% of total rubric points, and every query must return correct results when the instructor runs your script on a fresh database. That last condition means your DDL script must be idempotent-friendly — use DROP TABLE IF EXISTS or CREATE TABLE IF NOT EXISTS so the instructor can run it cleanly.

Professional Habits That Matter

Comment your DDL. Every table should have a COMMENT ON TABLE statement or at least inline comments explaining non-obvious columns. Use consistent naming conventions (snake_case is the PostgreSQL community standard). Keep your Git repository organized with separate files for DDL, seed data, queries, and the function/view definitions. These habits are not graded here, but employers will notice them in your portfolio.