Design a small relational schema with keys and constraints, load fixtures and prove that invalid relationships are rejected.
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 |
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.
Draw members, events and registrations with their relationships.
Entity and relationship sketch
Record your result and evidence here or in a separate file.
A member name appears as a member attribute, not as the registration key.
Ask which entity owns each fact.
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.
Write schema.sql with three tables, primary keys, foreign keys and a positive-capacity check.
Executable schema
Record your result and evidence here or in a separate file.
PRAGMA foreign_keys reports 1 and duplicate registration pairs are rejected.
Test enforcement through an attempted invalid insert, not just by reading the SQL.
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.
Load the supplied members and events, then query registration counts per event.
Fixtures and verification queries
Record your result and evidence here or in a separate file.
Event 10 has 2 registrations and event 20 has 1.
Count the original registration rows before trusting an aggregate.
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.
Run three invalid-insert tests and one valid new registration.
Constraint test report
Record your result and evidence here or in a separate file.
All three invalid cases fail and the valid case succeeds.
Confirm the test was actually committed or rolled back as intended.
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.
Write a schema README and an overbooking limitation note.
Design rationale
Record your result and evidence here or in a separate file.
The README explicitly distinguishes referential integrity from capacity enforcement.
Remove claims that the current schema does not actually enforce.
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.
Foreign keys are enabled and invalid-insert evidence is retained; capacity enforcement is not falsely claimed.
| Criterion | Points | 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 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.
This is a local schema exercise, not a concurrent booking service or a deployed database.