The Brief

What changes here. Every step so far told you what to build. This tells you what must be true when you are finished.

The project. Design a database for a small archive or collection of your choosing, load real data into it, index it, and answer a set of questions with queries you can explain.

Pick a collection you find genuinely interesting. A library, a discography, a set of public records, the data you collected in the APIs & Datasets course. That last one is the strongest option: you already have real, messy, cleaned data with its provenance recorded, and putting it into a proper schema is the natural next step.

The acceptance criteria. Every one is checkable by another person from your repository.

1. At least four tables, including one many-to-many relationship with its own join table.

2. Every table has a primary key, and every relationship a foreign key.

3. Constraints are used: NOT NULL, UNIQUE and at least one CHECK, each justified in a comment.

4. A comment above each table says what one row of it represents.

5. Real data is loaded, at least a few hundred rows, from a source you name.

6. Eight queries answering stated questions, each with the question in a comment.

7. At least one uses a LEFT JOIN because the absence is part of the answer.

8. At least one uses GROUP BY with HAVING.

9. At least one uses a CTE, and reads better for it.

10. Query plans are recorded before and after any index, with the ones that changed nothing removed.

11. Every headline number is computed a second way and the two agree.

12. A README explains the schema, the source of the data, and one design decision you would make differently.

✦ The sentence to be able to write

One row of this table is one ______. If you cannot complete that for every table in your schema, the design is not finished. It is the single fastest way to find a table doing two jobs.