Coursya Editorial

Free SQL Database Design Learning Path

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

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.

5 stagesWith an output and review gate
0mSelected video runtime
2h 5mEstimated assigned reading
8h 30mEstimated practice + project

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.

Original synthetic teaching data. These are not results from a real organization.
member_id member event_id event
1 Ada 10 Repair basics
2 Ben 10 Repair basics
1 Ada 20 Bookbinding
One registration joins two facts. Members: 2; Registrations: 3; Events: 2. Unique pair: member_id + event_id.
Unique pair: member_id + event_id. Open the full-size teaching diagram.

The table and explanations preserve the essential case information when the diagram is unavailable.

Your route

Five outputs, then a final handoff

  1. 01Identify entities and factsEntity and relationship sketch
    About 1h 45m including assigned learning and practice.
  2. 02Declare keys and constraintsExecutable schema
    About 2h 15m including assigned learning and practice.
  3. 03Load and inspect fixturesFixtures and verification queries
    About 1h 45m including assigned learning and practice.
  4. 04Exercise failure casesConstraint test report
    About 1h 20m including assigned learning and practice.
  5. 05Explain a limitationDesign rationale
    About 1h including assigned learning and practice.
  6. 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

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

01

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.

02

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.

03

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.

04

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.

05

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.

Created by Coursya. Original case and guided practice. Sources checked 13 October 2026.

Learning content remains readable without JavaScript. Use the printable workbook if browser progress saving is unavailable.

Share this article

Questions & discussion

Share a useful question or correction. Comments appear after moderation. Please avoid personal or sensitive information.

Add to the discussion

Your email address will not be published. Required fields are marked *