Challenge and hand off the schema
See this course’s project requirements
A four-table equipment-loan schema with stable keys, relationship definitions, integrity rules and checked edge cases.
Keep your working files in your own notebook or software. Save a private description, checklist, and work link in My Proof.
What to include
- Four entity definitions and base row counts.
- Stable primary keys and foreign keys.
- Relationship sketch and trace.
- Dependency/redundancy example.
- Integrity rules, three edge cases and limits.
Review and revise
Use these criteria to check your work and choose what to improve.
- Objects and events remain distinct.
- Keys reconstruct every required relationship.
- Facts are stored with their proper owner.
- Constraints and case decisions match the stated policy.
Want more background?
The idea in everyday life
Design a small fictional equipment-loan system before writing retrieval queries.
A good model states both what it can represent and which business rules need explicit enforcement.
Challenge and hand off the schema — guided practice
Test the complete design with historical and simultaneous-loan cases.
Work in design.md or on paper; no database installation is required. Fictional equipment library: types T1=Camera, replacement units 200; T2=Tripod, replacement units 40. Items I1 and I2 have type T1; I3 has type T2. Borrowers B1=River and B2=Sky are aliases. Loans: L1, I1, B1, returned=1; L2, I1, B2, returned=0; L3, I3, B1, returned=0. Rule: returned is 0 or 1; each item may have at most one active (returned=0) loan. Keep the base rows available throughout all six lessons.
1. Apply the active rule
Try L4,I1,B1,0 against the base rows.
You should see: Reject: L2 already records an active loan of I1.
A one-to-many historical relationship does not enforce simultaneous availability.
2. Complete the old loan
In a copy, mark L2 returned=1, then reconsider L4.
You should see: L4 is now allowed by the one-active-loan rule.
The same proposal changes validity when relevant state changes.
3. Check an independent item
Try L5,I2,B2,0 against the original base.
You should see: Allowed: I2 has no active loan.
The rule is per item, not per borrower or type.
4. Package
Complete design.md with four tables, keys, relationships, value/reference rules and these three test decisions. Optional schema.sql includes a partial unique index for active loans.
You should see: The project includes structure plus behavior evidence.
State that real circulation systems need dates, history and operational policies beyond this model.
What to finish
A four-table schema packet, checked cases and annotated optional SQLite example.
Watch for this
Claiming foreign keys alone enforce the one-active-loan rule.
How can I tell I understand it?
Complete the changed case, explain your result, and pass the three application checks.
Quick knowledge check
Answer all 3 questions. Get at least 2 right and the lesson completes automatically.
You’ll get an explanation for each answer. Signed in? Your results save to your account.
Pass the check. We’ll save the progress.
Answer all 3 questions above and get at least 2 right. You can review the explanations and try again.
Go to the knowledge checkOptional course overview video · same video as Lesson 1
Database Design Course - Learn how to design and plan a database for beginners
From freeCodeCamp.org. Watch here or open it on YouTube .
Optional course overview. Follow the written steps below at your own pace.Tools, templates, and lesson sources
Tools and templates
Tools open in a new tab so you can keep your place here.
Sources and further reading
Explore the references behind this lesson.
Source & access notes · checked 2026-09-12
Opened and inspected on 2026-09-12: Entities, keys and relationship multiplicities.
Access: Free public web page; no paid enrollment required.
Reuse: CC BY 4.0 except credited exceptions; independent tables and explanations.
Source & access notes · checked 2026-09-12
Opened and inspected on 2026-09-12: Determinants and dependencies.
Access: Free public web page; no paid enrollment required.
Reuse: CC BY 4.0 except noted material; independent examples.
Source & access notes · checked 2026-09-12
Opened and inspected on 2026-09-12: Redundancy and decomposition.
Access: Free public web page; no paid enrollment required.
Reuse: CC BY 4.0 except noted material; independent examples.
Report a problem with this lesson
Tell us what went wrong. Please leave out personal information and private project contents.