State and check integrity rules
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 schema’s structure and constraints work together to reject impossible references and values.
State and check integrity rules — guided practice
Translate requirements into rules for permitted records.
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. List required references
State Item.type_id must exist in Type; Loan.item_id and borrower_id must exist in their tables.
You should see: Every link can be followed to a real practice row.
Foreign-key rules prevent orphaned records.
2. List value rules
State returned is 0 or 1, replacement_units is a nonnegative whole number, and required IDs cannot be blank.
You should see: returned=2 and cost=-1 are invalid.
A number’s type alone does not establish its allowed meaning.
3. Check two proposed rows
Evaluate L4,I2,B2,0 and L5,I9,B2,0.
You should see: L4 is permitted; L5 references absent I9 and is rejected.
Concrete examples expose vague rules.
4. Inspect optional implementation
Read schema.sql. PRAGMA foreign_keys=ON enables SQLite reference checks. The replacement_units CHECK requires integer storage and a nonnegative value; ordinary SQLite INTEGER affinity alone is not a type guarantee. Paper reasoning is sufficient.
You should see: The implementation visibly corresponds to the written rules.
Some database checks need explicit activation or implementation.
What to finish
An integrity-rule list with accepted and rejected rows.
Watch for this
Confusing a well-formed-looking ID with an ID that actually exists.
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: 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.