SQL Database Design
Project workbook ยท Path 67

Design a small relational schema with keys and constraints, load fixtures and prove that invalid relationships are rejected.

The case

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_idmemberevent_idevent
1Ada10Repair basics
2Ben10Repair basics
1Ada20Bookbinding
STAGE 1

Identify entities and facts

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.

Your task

Draw members, events and registrations with their relationships.

Required output

Entity and relationship sketch

Record your result and evidence here or in a separate file.

Review gate

A member name appears as a member attribute, not as the registration key.

If the gate is not met

Ask which entity owns each fact.

STAGE 2

Declare keys and constraints

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.

Your task

Write schema.sql with three tables, primary keys, foreign keys and a positive-capacity check.

Required output

Executable schema

Record your result and evidence here or in a separate file.

Review gate

PRAGMA foreign_keys reports 1 and duplicate registration pairs are rejected.

If the gate is not met

Test enforcement through an attempted invalid insert, not just by reading the SQL.

STAGE 3

Load and inspect fixtures

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.

Your task

Load the supplied members and events, then query registration counts per event.

Required output

Fixtures and verification queries

Record your result and evidence here or in a separate file.

Review gate

Event 10 has 2 registrations and event 20 has 1.

If the gate is not met

Count the original registration rows before trusting an aggregate.

STAGE 4

Exercise failure cases

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.

Your task

Run three invalid-insert tests and one valid new registration.

Required output

Constraint test report

Record your result and evidence here or in a separate file.

Review gate

All three invalid cases fail and the valid case succeeds.

If the gate is not met

Confirm the test was actually committed or rolled back as intended.

STAGE 5

Explain a limitation

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.

Your task

Write a schema README and an overbooking limitation note.

Required output

Design rationale

Record your result and evidence here or in a separate file.

Review gate

The README explicitly distinguishes referential integrity from capacity enforcement.

If the gate is not met

Remove claims that the current schema does not actually enforce.

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.

Critical condition

Foreign keys are enabled and invalid-insert evidence is retained; capacity enforcement is not falsely claimed.

CriterionPointsEvidence
Evidence and correctness30Calculations, source use and results match the supplied case; limitations are honest.
Required artifacts30All five stage outputs and the capstone handoff are present and inspectable.
Reasoning and decisions20Choices, assumptions, alternatives and the case-specific critical condition are explained.
Reproducibility10A reviewer can follow the stated steps and distinguish expected from observed results.
Clear communication10Labels, 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.

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.

Boundaries

This is a local schema exercise, not a concurrent booking service or a deployed database.