Path 67 · The relation workshop · Intermediate
Put each fact in the right place.
A guided, practical route through sql database design.
What you will be able to do
Design a small relational schema with keys and constraints, load fixtures and prove that invalid relationships are rejected.
Plan about 10h 35m in total. Reading and practice are editorial estimates; video runtimes come from public provider metadata. Your pace will vary.
Table of Contents
Before you begin
Check your starting point
What you need
Basic SELECT queries and a local SQLite interpreter or Python 3 sqlite3 module. No hosted database is required.
The selected public provider lessons are free to view. The original case can be completed with paper, a text editor or freely available local tools. Paid exams, certificates, subscriptions and optional provider software are not required for this project.
Completing this Coursya path does not award a provider certificate. Any provider assessment, account requirement or credential must be checked separately on its official page.
Three readiness questions
Is a name a reliable unique key?
No. Different people can share a name.
Where does a many-to-many relationship belong?
In a junction table with the relevant keys.
Are foreign keys useful if enforcement is off?
The declarations alone do not enforce the case in SQLite.
Use a workable pace
At 5 hours per week, allow about 3 weeks. Start with Stage 1, continue in order and reserve 2h 30m for the capstone. If a gate fails, use its repair step before advancing. At another weekly budget, divide 635 estimated minutes by your available weekly minutes and round up.
Keep a small evidence folder. A checked box means you reviewed your output; it is not a certificate or proof that a provider lesson was watched.
The working case
One registration joins two facts
A fictional reading club needs members, events and registrations. One member may join many events; one event may have many members. A registration must refer to an existing member and event, and the same pair cannot be registered twice.
| member_id | member | event_id | event |
|---|---|---|---|
| 1 | Ada | 10 | Repair basics |
| 2 | Ben | 10 | Repair basics |
| 1 | Ada | 20 | Bookbinding |

The table and explanations preserve the essential case information when the diagram is unavailable.
Your route
Five outputs, then a final handoff
- 01Identify entities and factsEntity and relationship sketch
About 1h 45m including assigned learning and practice. - 02Declare keys and constraintsExecutable schema
About 2h 15m including assigned learning and practice. - 03Load and inspect fixturesFixtures and verification queries
About 1h 45m including assigned learning and practice. - 04Exercise failure casesConstraint test report
About 1h 20m including assigned learning and practice. - 05Explain a limitationDesign rationale
About 1h including assigned learning and practice. - FINALCapstone reviewCombine the evidence into one inspectable handoff.
About 2h 30m.
0 of 6 outputs checked. JavaScript adds browser-only progress saving.
The complete lesson and all downloads remain available. Use the printable project workbook to track your progress.
Use the minimum useful source material
The courses behind this route
Harvard
CS50’s Introduction to Databases with SQL
The existing CS50 SQL course has verified public sections on Relating, Designing and Writing; these are the focused reading assignments.
Selected material only; this path does not claim completion of the entire provider course.
Course publication and selected public lesson metadata were checked on 13 October 2026. This is not a claim of full video playback, provider-course completion or permanent availability. Recheck the linked source if a lesson changes.
Learn, make, inspect
The five-stage workshop
Identify entities and facts
Video 0m · reading budget 40m · original practice about 1h 5m.
Separate facts about a member, facts about an event and facts about joining an event. Repeating names in every registration makes changes harder to control. Start with the questions the club needs to answer, then choose stable identifiers. The exercise uses synthetic identities and contains no real personal data.
Assigned reading
CS50’s Introduction to Databases with SQL: Relating: entities, relationships and keys.
Use the named provider navigation sections. Editorial reading budget for the named public material; not a verified completion duration
Do the work
Draw members, events and registrations with their relationships.
Save this output
Entity and relationship sketch
Pass the review gate
A member name appears as a member attribute, not as the registration key.
If you need another attempt
Ask which entity owns each fact.
Declare keys and constraints
Video 0m · reading budget 50m · original practice about 1h 25m.
A primary key identifies a row. A foreign key connects it to an existing parent. A composite key can prevent duplicate member-event pairs. Constraints make invalid states harder to store, but they must match the domain rule. Enable SQLite foreign-key enforcement for every connection used in this exercise.
Assigned reading
CS50’s Introduction to Databases with SQL: Designing: schemas, data types and constraints.
Use the named provider navigation sections. Editorial reading budget for the named public material; not a verified completion duration
Do the work
Write schema.sql with three tables, primary keys, foreign keys and a positive-capacity check.
Save this output
Executable schema
Pass the review gate
PRAGMA foreign_keys reports 1 and duplicate registration pairs are rejected.
If you need another attempt
Test enforcement through an attempted invalid insert, not just by reading the SQL.
Load and inspect fixtures
Video 0m · reading budget 35m · original practice about 1h 10m.
Load parent rows before dependent registrations. Keep fixtures small enough to inspect manually and use a transaction for related changes. A join should explain its keys; an accidental many-to-many join can inflate counts while still returning plausible rows.
Assigned reading
CS50’s Introduction to Databases with SQL: Writing: inserting and updating data.
Use the named provider navigation sections. Editorial reading budget for the named public material; not a verified completion duration
Do the work
Load the supplied members and events, then query registration counts per event.
Save this output
Fixtures and verification queries
Pass the review gate
Event 10 has 2 registrations and event 20 has 1.
If you need another attempt
Count the original registration rows before trusting an aggregate.
Exercise failure cases
Video 0m · reading budget 0m · original practice about 1h 20m.
Schema tests should include states that must be refused. Try a missing member, a duplicate pair and a negative capacity. Handle expected failures in a separate test transaction so a demonstration does not corrupt the baseline fixtures. Record the rejected rule, not only the database error text.
Original application stage. Use your preceding outputs and the supplied case; there is no additional required provider lesson.
Do the work
Run three invalid-insert tests and one valid new registration.
Save this output
Constraint test report
Pass the review gate
All three invalid cases fail and the valid case succeeds.
If you need another attempt
Confirm the test was actually committed or rolled back as intended.
Explain a limitation
Video 0m · reading budget 0m · original practice about 1h .
Not every business rule is enforced by a simple column check. Event capacity across many registrations needs transaction-aware application logic or another suitable design. Do not claim that a positive capacity column prevents overbooking. State the boundary and outline how a real implementation would serialize competing reservations.
Original application stage. Use your preceding outputs and the supplied case; there is no additional required provider lesson.
Do the work
Write a schema README and an overbooking limitation note.
Save this output
Design rationale
Pass the review gate
The README explicitly distinguishes referential integrity from capacity enforcement.
If you need another attempt
Remove claims that the current schema does not actually enforce.
Make the result inspectable
Your final project
Deliver the relationship sketch, schema, fixtures, queries, failure-case checks and limitation note. A reviewer should recreate the database locally from an empty file and obtain the same counts.
The ZIP includes synthetic input data, a project workbook, worked reference, evidence log, scoring rubric and case-specific materials. Code projects include source and clearly stated local execution requirements.
Use one consistent rubric
| Criterion | Maximum | Evidence |
|---|---|---|
| Evidence and correctness | 30 | Calculations, source use and results match the supplied case; limitations are honest. |
| Required artifacts | 30 | All five stage outputs and the capstone handoff are present and inspectable. |
| Reasoning and decisions | 20 | Choices, assumptions, alternatives and the case-specific critical condition are explained. |
| Reproducibility | 10 | A reviewer can follow the stated steps and distinguish expected from observed results. |
| Clear communication | 10 | Labels, units, structure and evidence references make the work easy to review. |
Score at least 80/100, supply every required artifact, and satisfy the path-specific critical condition. Rework missing evidence before marking complete.
The critical condition
Foreign keys are enabled and invalid-insert evidence is retained; capacity enforcement is not falsely claimed.
Compare with a worked reference
The junction table has three rows: (1,10), (2,10) and (1,20). Grouping by event produces counts 2 and 1. A composite primary key prevents another (1,10), while foreign keys prevent a registration for a missing member. Neither rule alone checks remaining event capacity.
Know the boundary
This is a local schema exercise, not a concurrent booking service or a deployed database.
Questions before you finish
Do I need to finish every source course?
No. This path names selected lessons or reading sections. Complete the full provider course only if it supports your wider goal. The path does not claim equivalence to that course.
How do I know when to advance?
Use the output and review gate in each stage. If evidence is missing or a result conflicts with the case, follow the repair step and retry before advancing.
Can I do the project without paid software?
The selected public provider lessons are free to view. The original case can be completed with paper, a text editor or freely available local tools. Paid exams, certificates, subscriptions and optional provider software are not required for this project.
What does my checklist prove?
It records your own review in this browser only. It does not verify viewing, skill, a provider credential or a real-world deployment.
What if a lesson or tool changes?
Use the linked course and provider page to inspect current availability. Record the change and revisit the relevant assignment; do not invent a replacement lesson or claim an unavailable activity was completed.
Questions & discussion
Share a useful question or correction. Comments appear after moderation. Please avoid personal or sensitive information.