Study guide: Design Consistent Relational Records
Design parentsA,B and children with required parent IDs. Evaluate a child pointing to C, an incomplete 3-token transfer and a query with a user-supplied value.
Ideas to explain in your own words
- A primary key uniquely identifies rows under a database’s declared constraints. A display name is often not unique, so it may be unsuitable as the key. The model supplies a stable item ID and explicitly enforces uniqueness. Repeated values in other fields do not necessarily violate a key.
- A foreign-key reference can link a child record to a parent under specified constraints. In the supplied model every child must reference an existing parent. Deleting a parent may need a defined reject, cascade or replacement policy; this model rejects missing references instead of guessing.
- A transaction groups operations so the defined change can commit together or be rolled back. In our atomic transfer model, debiting A without crediting B is unacceptable. Transaction boundaries and error handling preserve the stated invariant. No real financial transfer occurs in this lesson.
- Parameterized database queries bind data values separately from SQL structure. Concatenating an untrusted value into SQL text can let it be interpreted as part of the command. A parameter is a value, not a table or column name. This lesson compares safe paper plans without executing untrusted requests.
Useful vocabulary
- primary key
- An enforced unique row identifier.
- foreign key
- A reference to a related row under stated constraints.
- transaction
- Operations committed together or rolled back.
Watch your reasoning
- Two items can share a display name while having different valid identifiers.
- A parent-looking code is not evidence that a corresponding parent record exists.
- Two successful-looking partial steps are not enough if their combined invariant is broken.
- Escaping by guesswork is not a substitute for documented parameter binding.
A worked review example
Rows item_id=J/name=Map and item_id=K/name=Map have J≠K, with both required IDs present, so they satisfy the declared primary-key rule. Their repeated non-key name is allowed because only item_id is unique in this contract. Add another item_id=J row: now J repeats and the key rule fails. Removing a required item_id would instead create a missing-key failure. Apply the actual declared constraint rather than demanding that every displayed value be unique. User value is passed through a placeholder binding. Which query-plan property is described? The supported answer is Bound data parameter. Bind the supplied input as a data parameter and keep allowed query structure fixed. Escaping by guesswork is not a substitute for documented parameter binding. The supplied facts, rather than an assumed real-world detail, determine this paper case. Compare the recorded action or value with the stated rule before choosing an answer.
Make a practice plan
- Make a four-row summary, one row for each lesson, with its computing idea and one worked decision.
- Explain one tempting mistake using the actual supplied example.
- Complete the new unit review before consulting its explanations.
- Try the transfer task on paper and compare your evidence against the model response.