Database theory asks how records, relationships, queries and transactions can represent trustworthy information. A query returning rows does not necessarily answer the reader’s question, and an index improving speed does not repair a flawed join or a broken identity rule. The discipline connects mathematical relations with data integrity and safe change.
This guide explains keys, relational algebra, SQL duplicates and NULLs, joins, functional dependencies, normalisation, transaction isolation, recovery and data provenance. Original examples show why a correct-looking calculation can count a person twice or show an obsolete result after correction.
Choose a route through the data
Relations, keys and query meaning · Chapters 1–8 · Start here
SQL logic, dependencies and normalisation · Chapters 9–17 · Start here
Transactions, isolation and optimisation · Chapters 18–25 · Start here
Recovery, lineage and trustworthy use · Chapters 26–30 · Start here
Full chapter index · Worked data cases · Database repair cases · Connected Gold Standard guides
Complete chapter index
Relations, keys and query meaning
- 1. Database theory begins with the meaning of a record
- 2. How database theory differs from data structures
- 3. The relational model treats data as mathematical relations
- 4. Schemas define structure but also require a domain story
- 5. Candidate keys and primary keys have precise jobs
- 6. Foreign keys preserve references under defined rules
- 7. Relational algebra makes query operations explicit
- 8. Relational calculus describes results declaratively
SQL logic, dependencies and normalisation
- 9. SQL query results need duplicate-aware interpretation
- 10. NULL is not an ordinary numeric value
- 11. Three-valued logic changes filtering
- 12. Joins are relationships, not just matching text
- 13. Join multiplicity can inflate aggregate results
- 14. Functional dependencies express deterministic relationships
- 15. Normalisation reduces some update anomalies
- 16. Lossless decomposition protects reconstruction
- 17. Dependency preservation affects constraint enforcement
Transactions, isolation and optimisation
- 18. Transactions group related changes into a logical unit
- 19. Isolation controls which concurrent histories are permitted
- 20. Serializability is an outcome of transaction histories
- 21. Write skew illustrates a subtle concurrency anomaly
- 22. MVCC gives readers different views of time
- 23. Query optimisation preserves meaning while changing execution
- 24. Indexes change access cost, not logical truth
- 25. Database recovery needs a durable record of intent
Recovery, lineage and trustworthy use
- 26. Distributed databases multiply the agreement problem
- 27. Provenance explains where an answer came from
- 28. Temporal data distinguish events, validity and recording
- 29. Privacy and integrity must govern database access
- 30. A practical study path and frequently asked questions
Data correctness has several distinct layers
| Layer | Core question | Frequent mistake |
|---|---|---|
| Entity identity | Which real-world unit does a row represent? | Using a person’s display name as a unique key |
| Query logic | Which records satisfy the question? | Unwanted duplicates or missing cases |
| Integrity | Which combinations are permitted? | Broken references and update anomalies |
| Transactions | Can concurrent work violate an invariant? | Unsafe read-then-write operations |
| History and provenance | Where did this result come from? | Overwritten corrections without audit context |
1. Database theory begins with the meaning of a record
Database theory studies formal models of stored data, queries, constraints and transactions. A database is not merely a collection of files or rows. It represents claims about entities, relationships and changes over time, so correctness involves what those claims mean and which combinations of them are allowed.
A student database, for example, may record enrolment, course assignment and assessment attempts. Before optimising queries, identify whether a row means a person, an attempt or a class membership. Joining tables with ambiguous identity can produce perfectly executable but false reports. The gold standard is a valid data model plus trustworthy operations and recoverable history.
2. How database theory differs from data structures
A data structure determines how values are stored and accessed, such as hash maps, trees and arrays. Database theory adds questions about relations, logical query meaning, integrity constraints, update anomalies and concurrent transactions. A B-tree index can accelerate lookups but does not itself define what a student ID means or whether two rows represent the same learner.
The Data Structures root addresses in-memory and indexed representations. This guide begins with relational semantics, then uses physical structures as one part of implementing a valid query and transaction contract.
3. The relational model treats data as mathematical relations
In the classical relational model, a relation is a set of tuples over named attributes, with a schema describing attribute types and intended meanings. Mathematical sets do not contain duplicate identical tuples. Real SQL tables often use bag or multiset behaviour by default, allowing duplicate rows unless constraints or DISTINCT are applied.
State which semantics are being discussed. A relational-algebra theorem about sets cannot be applied blindly to every SQL query involving duplicate rows, nulls and ordering. Good explanations connect the formal relation to a concrete schema while acknowledging where implementation-level SQL differs from the original mathematics.
4. Schemas define structure but also require a domain story
A schema lists attributes, types and relationships. For enrolment, one might use Learner(learner_id, legal_name) and Enrolment(learner_id, course_id, start_date), instead of repeating the learner’s name in every course record. The split reflects distinct entities and processes.
Data types cannot substitute for identity rules. A field named learner_id must be created and governed so it actually identifies the intended person across authorised systems. Document keys, units, time zones and permissible values. A syntactically valid schema can still make a false assumption about reality.
5. Candidate keys and primary keys have precise jobs
A candidate key is a minimal set of attributes that uniquely identifies a tuple under the intended constraints. A primary key is one chosen identifier among possible candidates in a relational schema or DBMS design. Real SQL systems typically require primary-key values to be unique and non-null.
A display name is rarely a safe person identifier because people can share names or change them. Using an automatically assigned numeric key may solve some database identity issues, but records still need validated linkage to real people. A unique constraint protects stored values; it does not establish correct real-world identity on its own.
6. Foreign keys preserve references under defined rules
A foreign key restricts values in one table to match referenced keys in another table, subject to DBMS null and constraint semantics. An Enrolment row referencing a missing learner can violate referential integrity. Constraints help prevent orphaned records or inappropriate deletes.
Choose what happens when a referenced record is removed or updated: reject, cascade or set a reference to an appropriate missing value. Cascading deletion of a student could erase required assessment history if applied thoughtlessly. The correct rule follows retention, safeguarding and application requirements, not whatever default is easiest to implement.
7. Relational algebra makes query operations explicit
Selection filters tuples by a predicate; projection keeps chosen attributes; joins combine compatible tuples; union and difference operate on relations under appropriate schema conditions. These building blocks allow a query to be understood as a mathematical expression and support reasoning about equivalent transformations.
An eligibility query may select active enrolments, join to programmes and project learner identifiers. Changing the join predicate can create unrelated pairings even when the SQL executes. Describe the business meaning and candidate matches before treating the resulting table as evidence.
8. Relational calculus describes results declaratively
Relational calculus uses logical formulas to define which tuples satisfy a condition, without requiring a particular step-by-step physical evaluation plan. This declarative perspective separates the meaning of a query from the algorithm a database engine chooses to execute it.
For example, a request for learners enrolled in at least one Mathematics course can be written as an existence condition over enrolment and course records. The engine may use indexes or joins to compute the answer. A query’s mathematical result should not depend on an arbitrary row-access order when the semantics do not specify one.
9. SQL query results need duplicate-aware interpretation
SQL uses SELECT, FROM, WHERE, GROUP BY and other constructs to express queries, but default duplicate semantics differ from set-based relational algebra. Selecting a course name from an enrolment table may return the same course many times because several learners are enrolled.
If the reader needs distinct courses, use suitable distinct semantics. If the reader needs number of enrolments, keep duplicates that represent different legitimate records. Automatically adding DISTINCT can conceal erroneous joins or erase the multiplicities needed for analysis. Define the unit of counting first.
10. NULL is not an ordinary numeric value
SQL NULL generally represents a missing or unknown value in a column, depending on application convention, and comparisons involving NULL follow SQL’s three-valued logic. The expression NULL=0 is not true, and NULL=NULL is generally unknown rather than true in standard SQL equality semantics.
A missing exam score is not automatically a zero, absence from the programme or refusal to participate. Track meaningful missing-data states and use appropriate predicates such as IS NULL when intended. Arithmetic and filtering decisions can change when unknown values enter a query, so test them explicitly.
11. Three-valued logic changes filtering
In standard SQL, predicates can evaluate to true, false or unknown. A WHERE clause retains rows for which the condition is true, not rows where it is false or unknown. A seemingly reasonable comparison can therefore omit records with NULL values without the author realising it.
For instance, WHERE score<>0 does not include rows whose score is NULL, even if the aim was to show all cases without a recorded zero. Document whether missing scores belong in the report. The correct logic depends on the decision and cannot be guessed by substituting the ordinary two-valued truth table.
12. Joins are relationships, not just matching text
An inner join combines rows satisfying a predicate, often key equality. An outer join can preserve unmatched rows with missing values on one side under the SQL semantics. The choice changes which people or events appear in a report.
A LEFT JOIN from all learners to their assessments can retain pupils with no recorded attempt; an INNER JOIN can silently remove them. When reporting progress, this is a substantive difference. Identify whether the target population includes learners who have not submitted work before choosing the join.
13. Join multiplicity can inflate aggregate results
When each learner has several enrolments and several assessment attempts, joining those tables directly by learner ID can produce many combinations of records. Summing attempts or fees after such a join may double-count data. Each row is mathematically valid under the join rule even though the resulting aggregate is misleading.
Aggregate at the appropriate unit before joining, add appropriate predicates or use correlated logic where suitable. Always compare expected row counts and identify one-to-one versus one-to-many relationships. Query correctness includes the meaning of the result, not only whether it runs without error.
14. Functional dependencies express deterministic relationships
A functional dependency X→Y means that within each valid relation instance, tuples agreeing on X also agree on Y. If course_id uniquely determines an official course title at a given schema level, course_id→course_title may be intended. Such dependencies help identify keys and potential redundancy.
Functional dependencies are data-model rules, not discoveries based only on a coincidental sample of rows. Today no two pupils may share a phone number, but that observation does not establish a permanent person-to-phone dependency. Validate constraints with domain experts and distinguish time-varying facts from stable identifiers.
15. Normalisation reduces some update anomalies
Normalisation decomposes relations to reduce redundancy while preserving intended information under specific dependencies. First normal form, second normal form, third normal form and Boyce–Codd normal form address different structural conditions, and their interpretations are tied to a stated relational framework.
If a table repeats an instructor’s office number for every student, changing that number requires many updates and creates inconsistency risk. Moving instructor contact details to a separate keyed table can reduce duplication. But fragmentation adds joins and requires careful lossless decomposition. Normalisation is design reasoning, not the automatic best physical layout for every workload.
16. Lossless decomposition protects reconstruction
A decomposition is lossless with respect to relevant dependencies when joining the decomposed relations reconstructs exactly the intended original relation, without losing tuples or creating spurious combinations. Decomposition must be justified by shared keys and constraints.
For example, splitting a relation into name-course and name-teacher when names are not unique can produce false combinations after a join. Identifying the correct keys is essential. A decomposition that looks tidy on a diagram may still destroy the relationships necessary to answer a real query.
17. Dependency preservation affects constraint enforcement
Some normalisation choices preserve the ability to enforce the original functional dependencies within individual decomposed relations; others require checking relationships across tables. BCNF decomposition can improve certain anomalies but may not preserve every dependency in an easily local form.
Choose among legitimate designs based on integrity, update patterns and query requirements. A schema that cannot enforce a vital constraint without complicated triggers or expensive cross-table checks may need reconsideration. The most normalised-looking schema is not automatically the easiest to operate correctly.
18. Transactions group related changes into a logical unit
A transaction groups operations that should be treated according to an atomic, consistent unit of work. The familiar ACID properties refer to atomicity, consistency, isolation and durability, though exact guarantees depend on the DBMS and workload.
A course-registration transaction might reserve a seat, create an enrolment row and update a capacity counter. If one part fails, the application needs a defined outcome rather than a half-completed registration. Database transactions provide mechanisms, but the programmer must still write the correct business rules and choose appropriate isolation.
19. Isolation controls which concurrent histories are permitted
Concurrent transactions can observe or modify data in interleaving orders. Isolation levels determine which phenomena are allowed in a particular DBMS. Read committed and serializable represent different guarantees, and names like repeatable read can have implementation-specific behaviours beyond minimum SQL-standard requirements.
The PostgreSQL documentation describes serializable isolation as allowing committed concurrent transactions to have an effect equivalent to some serial order, with possible serialization failures requiring retries. Do not assume the same details from the isolation-level name across every database product. Study the actual engine semantics.
20. Serializability is an outcome of transaction histories
Serializability means a concurrent history has an effect equivalent to some serial execution of transactions under an appropriate formal definition. Conflict serializability can be analysed with precedence graphs in simplified models. A cycle may reveal that a particular conflict history cannot be rearranged into an equivalent serial order.
The theorem concerns the modeled reads, writes and transactions. A business process that starts an external payment outside the database may break user-visible atomicity even if all database transactions are serializable. Draw the full workflow and identify boundaries that require separate coordination.
21. Write skew illustrates a subtle concurrency anomaly
Imagine two doctors are on call, with a rule that at least one must remain. Each transaction reads that two are on call and independently turns one person off, changing different rows. Under some snapshot-isolation implementations both transactions can commit, leaving nobody on call.
Row-level update conflicts alone may not detect this write skew. A suitable serializable isolation level, locking strategy or enforced invariant can protect the rule under the chosen DBMS. The key lesson is that each transaction’s individually reasonable checks may fail when both rely on an outdated shared condition.
22. MVCC gives readers different views of time
Multi-version concurrency control retains versions of records so readers and writers can often proceed without simple in-place blocking. Transactions may see a snapshot according to their isolation level, while updates create newer versions. This can improve concurrency but introduces visibility and cleanup rules.
A report run under one snapshot may legitimately exclude a correction committed moments later. The question is whether the report’s timestamp and consistency contract are appropriate. MVCC is not a guarantee that every client immediately sees the latest value or that all possible transaction anomalies disappear.
23. Query optimisation preserves meaning while changing execution
A database query planner can choose join orders, indexes, scans and aggregation strategies intended to produce equivalent results under the query’s semantics. It estimates costs using statistics about data distribution, storage and available resources.
Reordering an inner join under appropriate conditions can be safe, but outer joins, NULLs, volatile functions and bag semantics can limit transformations. Query optimisation is not a licence to change which records belong to the result. A fast query returning the wrong population is worse than a slower correct query.
24. Indexes change access cost, not logical truth
A B-tree index supports ordered lookups and ranges under its implementation; a hash index can support certain key-equality operations. Both require storage and maintenance. An index can make a correct query faster but cannot fix the use of the wrong identifier or a missing join condition.
Benchmark on the read-write workload, not just one SELECT statement. Index maintenance affects inserts, deletions and updates; index size affects storage and caching. Match the physical representation to the actual query while preserving integrity constraints and meaningful results.
25. Database recovery needs a durable record of intent
Write-ahead logging and related recovery techniques help engines reconstruct committed changes and undo or ignore incomplete work after a crash, under their storage and durability assumptions. Checkpoints reduce the amount of work required during recovery.
A successful COMMIT acknowledgment should have a documented durability meaning, but hardware failures, backups and operational procedures still matter. Test crash recovery with realistic scenarios rather than treating a database’s ACID label as proof that no data can ever be lost under any circumstance.
26. Distributed databases multiply the agreement problem
Replicating data across servers changes which updates have become visible and durable where. Distributed transactions and replication protocols need explicit conflict, agreement and failure semantics. A single-node transaction argument is not sufficient for operations that span several independent services.
The Distributed Algorithms guide covers coordination and consensus. Database theory adds the meaning of queries, integrity and transaction histories. A set of replicas agreeing on an incorrectly merged student record still stores an incorrect claim.
27. Provenance explains where an answer came from
Data provenance traces which source records, transformations or processes contributed to an output. In relational settings, provenance models may annotate query results to represent their derivations. At a practical level, a school report should identify assessment version, correction date, source and aggregation rules.
Provenance does not automatically establish that source records are true or authorised. It enables investigation of mistakes and propagation of corrections. A number without lineage can be impossible to audit, even if the query itself was mathematically well formed.
28. Temporal data distinguish events, validity and recording
A fact may be valid during one period but entered into the system later. Temporal database concepts distinguish dimensions such as valid time and transaction or system time under particular models. Correcting a historical enrolment date should not necessarily erase the fact that an earlier report used a previous value.
Specify which time axis a query uses. ‘Active on 1 October’ differs from ‘recorded as active on 1 October’ if corrections arrived later. Temporal precision matters for eligibility, finance and examinations. Database designs should protect appropriate auditability without retaining personal information without lawful purpose.
29. Privacy and integrity must govern database access
Constraints, access control, encryption, retention rules and audit logs protect different aspects of information. A primary key protects uniqueness of stored values; it does not authorise every staff member to see them. A query returning a correct result may still disclose sensitive data to an unauthorised user.
Use role and purpose-based access appropriate to the setting, review data minimisation and document corrections. Do not use the same personal identifier indiscriminately across services merely because joins become easier. Database theory serves people when rigorous integrity accompanies legitimate use.
30. A practical study path and frequently asked questions
Does a primary key prove two rows concern different real people? No. Does SQL automatically remove duplicate rows? No. Is NULL equivalent to zero? No. Can two transactions both read the same value and still conflict? Yes. Does serializable isolation ensure every external side effect occurs once? Not by itself.
Build small Learner, Course and Enrolment tables with explicit keys. Write a join and inspect duplicates and missing records. Add constraints, then simulate two concurrent registrations for one remaining seat. At advanced level study functional dependencies, serializability, provenance and query equivalence. The enduring skill is connecting logical data meaning to correct operations.
Twenty original relational and transaction investigations
1. An inner join silently loses students without scores
A Learner table contains 100 students; the Assessment table contains results for 80. An inner join reports only 80 learners, potentially misrepresenting programme coverage. A suitable left join can preserve students without assessments. The correct choice depends on the intended report population, not personal preference for one SQL keyword.
2. Three enrolments and four attempts multiply rows
A learner has three course-enrolment rows and four attempt rows. Joining both on learner_id alone creates twelve combinations, not seven independent events. Summing marks from the joined result can overcount. Join on appropriately specific keys or aggregate at the intended grain before combining.
3. A NULL score is not a failed examination
A student has no recorded score because the assessment has not been taken. A query replaces NULL with zero and reports failure. That creates an unsupported statement. Distinguish absence, nonparticipation, invalid attempts and actual earned marks, then decide how each contributes to a report.
4. Names are not suitable unique keys
Two pupils share a display name. A database uses name as its primary key and rejects the second pupil or incorrectly merges their results. A genuinely unique governed identifier is necessary, but still must be linked to the right person through appropriate verification.
5. An update anomaly changes only some rows
An instructor’s office is stored in every enrolment row. Staff update half those rows after an office move. Different pupils now receive contradictory information. Separating instructor details into a keyed relation can reduce this redundancy, if the decomposition preserves the intended relationships.
6. A decomposition makes spurious combinations
A table represents which instructor teaches which course for which learner. Splitting it only by learner name and course name can lose the specific association when names repeat. Joining the pieces back can create relationships never present in the original. Verify losslessness under suitable keys and dependencies.
7. A finite dataset suggests a false dependency
Every learner currently has a unique home telephone number. An analyst infers phone→learner_id as a permanent functional dependency. Families may share a number, and the apparent rule can fail. Schema dependencies require domain justification, not merely inspection of one sample.
8. SQL duplicates differ from mathematical set tuples
A SQL query selects a course title from enrolments and returns Mathematics twenty times. In a set-based relational projection it appears only once. Neither result is automatically a mistake; they answer different multiplicity questions. Use DISTINCT only when unique values are intended.
9. A NOT IN query behaves unexpectedly with NULL
A query uses NOT IN against a subquery that can return NULL. SQL’s unknown logic may prevent rows being returned as the programmer expects. Verify the engine semantics and consider an appropriately correlated NOT EXISTS when modelling absence. Test empty and NULL-containing cases.
10. An outer join filter accidentally becomes an inner join
A LEFT JOIN preserves learners with no results, but a WHERE condition on the right table’s score removes NULL-extended rows. The displayed population then excludes exactly the learners the author tried to keep. Move or reformulate predicates according to the intended semantics.
11. A registration check suffers write skew
Two transactions each see one remaining seat and both create registrations without a suitable constraint or locking strategy. Individually their checks seemed valid, but together they violate capacity. Define a database invariant and test concurrent histories rather than relying on sequential happy-path cases.
12. Serializable transactions can require retries
An application uses an isolation mode that detects an unsafe concurrent schedule and aborts one transaction. A programmer treats every such abort as a permanent customer failure. Implement a bounded, safe whole-transaction retry policy and consider idempotency of external effects. The DBMS warning is part of preserving correctness, not evidence the engine is defective.
13. A sequence value is not a gapless record of success
A system generates sequential IDs and assumes any missing number proves a deleted official result. Many database sequences can advance even when a transaction later rolls back. A gap may be expected behaviour. Use a separate audited process for any legally required gapless numbering.
14. A wrong query is made faster by an index
A school report joins two tables on class name instead of class identifier. A new index improves performance dramatically, but students from different cohorts with the same name are still combined. Physical optimisation cannot correct a logically invalid predicate.
15. A stale replica displays the old grade
A teacher corrects an assessment on one server; a parent reads a replica that has not yet received the update. The value is stale under the system’s chosen consistency behaviour. Define when a corrected result becomes official and what readers should see in the meantime.
16. An audit trail loses the original assessment version
A teacher changes one mark after a recheck but the database overwrites the old value and deletes its associated assessment version. The final score may be correct, yet later reviewers cannot reconstruct why a report changed. Record appropriate versioned lineage and retention controls.
17. A parent receives a record they are not authorised to view
A correctly written SQL query retrieves the requested student profile, but the requester has no valid access authority for that student. Relational query correctness cannot substitute for access control, consent or lawful processing. The system must enforce the legitimate access decision before revealing data.
18. A temporal query mixes recorded time and valid time
A pupil was enrolled on 1 September, but a correction was entered in October. A report asking ‘who was enrolled on 15 September’ must specify whether it means the best current historical truth or what was recorded on that date. The answers can differ.
19. A backup restores data but not external actions
After recovery, the database contains an order record, but the external payment or notification service acted at a different point in time. Restoring rows alone cannot establish end-to-end consistency. Reconcile side effects using authorised records and request identities.
20. A school database project needs a meaningful handover
Learners build a small relational system and test keys, joins and two transactions. A national registry would require extensive privacy, resilience, professional review, identity governance and operational controls. Transfer the reasoning about constraints and transactions, not the classroom design’s implied readiness.
Explore the eduKate information ecosystem
Mathematical and information foundations
Systems and governance
Database research and primary technical documentation
- CMU 15-445 — Database Systems, Fall 2026
- Carnegie Mellon — Database Systems Course Description
- PostgreSQL — Constraints and Keys
- PostgreSQL — Transaction Isolation
All names, database records and numerical examples here are invented teaching cases. Real database deployments require privacy and access controls, tested migrations, resilience plans and competent review of operational and legal requirements.
Sixteen additional database correctness and changed-context cases
These hypothetical investigations show how a database can satisfy its technical constraints while returning an incorrect, stale or unauthorised result. Identify the intended entity, transaction or historical meaning before applying a repair.
1. A duplicate school name creates a false cross-school match
Two distinct schools use the same display name. A reporting query joins learner records to campuses by name rather than a governed campus identifier. Pupils from both schools can now appear under each other’s programmes. Every joined row may satisfy the written predicate while the predicate itself misrepresents the intended identity. Define stable keys and correct relationships before optimising SQL. Verify surprising increases in row counts as possible semantic defects, not necessarily legitimate growth in enrolment.
2. A report loses all students who were absent
A school evaluates assessment results only for learners with submitted attempts, then describes the mean as representing the full cohort. Pupils absent on assessment day disappear from the joined dataset. The report must name the respondent or participant population and identify missing outcomes separately. The correct SQL may use a left join and an explicit denominator, but the research question should determine the choice. Database semantics and educational measurement meet when a numerical summary is used for institutional conclusions.
3. An average changes when a join multiplies rows
A course record is joined with several assessment attempts and several attendance entries per learner. Each attempt may appear once for every attendance record, so summing marks or calculating an average gives some pupils artificial extra weight. Aggregate the appropriate events before the many-to-many join, or use a query formulation preserving intended multiplicities. Check that row counts and key uniqueness match the data model. A query plan can run fast and return a precisely calculated but substantively wrong result.
4. A primary key guarantees uniqueness, not truth
A learner table uses a unique generated integer primary key, so two rows cannot share that number. An import mistakenly creates two different IDs for the same student. Both rows satisfy the database constraint, yet the person is duplicated. Identity reconciliation requires authorised evidence and governance outside the primary-key definition. When building record-linkage workflows, protect audit history and avoid casually merging people who share names or addresses. Data integrity is stronger when its formal and real-world aspects remain explicit.
5. One missing value has four possible meanings
An assessment field contains NULL for an unsubmitted exam, an excused absence, a corrupted import and an unresolved appeal. These are different conditions even if a single nullable column stores them. Define a status variable and appropriate constraints so the reason can be represented without inventing a numerical mark. Reports should show which records are pending or not applicable, not convert every unknown into a zero or erase rows silently. A nullable type is not a complete missing-data policy.
6. A case-sensitive identifier changes query results
A course code is imported as ‘MATH101’ in one system and ‘math101’ in another. A join using a case-sensitive comparison fails to match them; an indiscriminate case-folding rule may also merge codes that legitimately differ in another context. Define the identifier’s canonical form and validation process rather than guessing. Record the original source when necessary for auditability. Correct string comparison depends on domain meaning, collation and normalisation, not only syntax.
7. A cascade delete removes evidence that must be retained
A foreign-key relationship is configured to delete all related assessment records when a learner account is deleted. The programme may still have a legitimate obligation to retain some anonymised or archived evidence for a defined period, or conversely must lawfully remove personal information. Deletion rules should follow authorised retention and privacy decisions. Review the entire relationship graph and backups before using CASCADE as a convenience. Database referential integrity does not decide public policy about data preservation.
8. A grade correction races with a publication job
A teacher corrects a mark while an automated report is being assembled across several tables. The job reads the updated total but an earlier version of the question-level responses, creating an internally inconsistent report. Choose a transaction and isolation strategy that provides an appropriate coherent view, or version and reconcile the report explicitly. A stable snapshot may be sufficient for one purpose, while an official final report may require a particular confirmed cut-off. Document which version was published and when.
9. Two transactions preserve different rows but break one rule
A clinic’s fictional staffing table requires at least one professional on duty. Two transactions each read that two professionals are present, then each changes a different row to off duty. Under certain snapshot-isolation histories, both commits can leave no one on duty. A conflict that spans a predicate is not always detected by simple row-level update protection. Use a suitable serializable strategy or explicitly enforced invariant and test the concurrent schedule. Application correctness is about the final combined state.
10. A serializable abort triggers an accidental duplicate side effect
A transaction sends an email before committing, then receives a serialization failure and rolls back. On retry, the email is sent again, though the database operation may eventually commit only once. Keep side effects within properly coordinated mechanisms, such as an appropriate transactional outbox and idempotent consumers, or a documented reconciliation design. Serializability governs the database history, not automatically the history of independent mail or payment services. Trace the complete user-visible workflow.
11. A query planner chooses a poor estimate
A database planner estimates that a status value is rare, so it chooses an index-based plan. After an operational change, the value becomes common and many random fetches slow the query. The logical result can remain correct while performance deteriorates. Update statistics and inspect the actual plan and data distribution. Query optimisation estimates costs under imperfect knowledge; a query’s meaning should remain invariant even when its execution method changes. Monitor correctness and performance separately.
12. A time query reports two defensible answers
An enrolment is effective from 1 September but entered on 4 September. A manager asks who was enrolled on 2 September. A valid-time query includes the enrolment; a transaction-time query of what the database knew on 2 September does not. Both can be useful for different investigations. Specify whether the reader wants current best knowledge of history or the historical recorded view. Good temporal design preserves the distinction and handles later corrections explicitly.
13. An official identifier is represented as a number
A course identifier ‘00127’ is imported as an integer, losing leading zeros. A later export produces ‘127’, which fails to match another system’s canonical string identifier. Identifiers are labels, not necessarily quantities. Define their data types, padding and validation rules, and avoid numeric operations unless they have genuine meaning. Database design becomes more reliable when it distinguishes a code from the amount it might superficially resemble.
14. A durable database still requires a backup restoration test
A service uses transactional logging and receives successful commit acknowledgements. Operators assume that backups are complete and restoration will always work. A recovery drill reveals that one necessary external encryption key was not retained under the appropriate secure procedure, so the backup cannot be restored. Durability and disaster recovery span database, keys, infrastructure and operations. Test actual restoration into a controlled environment and verify record integrity, rather than equating enabled backup jobs with recoverability.
15. A provenance graph records the wrong source version
A dashboard states that a performance figure came from the Mathematics assessment, but it does not record whether it used the original scoring rubric or a corrected version. When a discrepancy appears, the source reference is too vague to reproduce the calculation. Record assessment identifier, version, relevant correction events and transformation rules with appropriate privacy safeguards. Provenance should let authorised reviewers reconstruct the result. A list of links without versioned meaning may offer an appearance of transparency while leaving the decisive question unanswered.
16. Transfer a class database exercise into a public service cautiously
Students build a small catalogue with primary keys, foreign keys, joins and a basic transaction. These principles transfer into larger systems, but a real public service must additionally handle identity verification, concurrent users, privacy law, audit trails, migrations, service recovery and legitimate deletion. The classroom database’s correct result for a tiny invented example is educational evidence of understanding, not certification that the design is ready for public records. Carry over the reasoning and verify the expanded requirements afresh.
Return to The Gold Standard, the Education and Learning Transfers into Life foundation and How X Works.