INFO20003 Chap.3 Relational Model and Schema Mapping
Relational Model and Schema Mapping
The relational model stores facts as tuples described by attributes and controlled by constraints. This chapter distinguishes superkeys, candidate keys, a chosen primary key and foreign keys, then connects entity integrity, referential integrity and domains to legal database states.
Worked mapping decisions show why a dependent object can need a composite identity and why a convenient surrogate key does not replace the business uniqueness rule. A relation schema names a set of attributes and their domains; a relation instance is the current set of tuples satisfying that schema and its constraints.
Tuple order has no logical significance, attribute names supply roles, and duplicate tuples do not occur under the mathematical set model. SQL may expose bags and displayed ordering, but those implementation behaviours should not be confused with the logical definition. Keys are established through uniqueness and minimality.
Every candidate key uniquely identifies tuples and contains no removable attribute; any superset is a superkey but not a candidate key. Selecting one candidate as primary does not cancel the others, which remain alternate uniqueness rules. A surrogate identifier can simplify references, yet a separate constraint is still required when a meaningful attribute combination must be unique.
Entity integrity prevents a primary-key component from being null because an unidentified tuple cannot fulfil the relation's assertion. Referential integrity requires each non-null foreign-key value to match a candidate key in the referenced relation. Domains and CHECK constraints restrict individual values or row-local combinations.
Deletion actions state policy rather than housekeeping: restriction protects dependent history, cascading removes facts whose existence depends on the parent, and setting null is valid only for an optional reference. Mapping preserves the semantics discovered conceptually. A composite foreign key must reference the complete candidate key with compatible components.
Associative relations carry the participant keys and relationship attributes. Dependent entities combine the owner's identity with their partial identifier unless the design introduces a surrogate while retaining natural uniqueness. Test every schema by constructing legal boundary rows and attempted violations.
Ask which duplicate, orphan, null or deletion the DBMS must reject, and identify rules that require assertions, triggers or transaction logic because basic keys cannot express them. This legal-state method makes constraints observable and prevents notation alone from standing in for reasoning.
What this chapter covers
- 01
Relations and tuples
- 02
Attribute domains
- 03
Superkeys
- 04
Candidate keys
- 05
Primary keys
- 06
Foreign keys
- 07
Entity integrity
- 08
Referential integrity
Worked example · free
Identify a physical copy
- 1branch_code identifies the owning branch and references Branch.
- 1isbn identifies the title and references Book.
- 1copy_no distinguishes copies of one title within one branch.
- 1The composite candidate key is branch_code, isbn and copy_no.
Key terms
- Relation
- A set of tuples sharing a heading of named attributes and domains.
- Superkey
- Any attribute set that uniquely identifies tuples, whether minimal or not.
- Candidate key
- A minimal superkey for which no proper subset remains unique.
- Primary key
- The candidate key selected as the main tuple identifier.
- Referential integrity
- The rule requiring non-null foreign key values to match a referenced candidate key.
- Domain constraint
- A restriction on the type or permitted values of an attribute.
Relational Model and Schema Mapping FAQ
Does adding a surrogate key remove natural uniqueness?
No. A surrogate can provide a compact stable reference, but it does not stop two rows from repeating a combination that the business says is unique. Retain a UNIQUE constraint on the natural candidate key, and test both rules separately: surrogate identity supports references while natural uniqueness protects domain meaning.
Why is a foreign key not automatically unique?
It represents a reference, and many rows may legitimately refer to the same parent. Uniqueness is added only when the business rule permits at most one referencing row for each referenced value.
Can sample data prove a candidate key?
No. A sample can reveal duplicates and refute a proposed key, but uniqueness must hold in every legal state. The business rule, not a temporarily unique spreadsheet column, provides the proof.
How should deletion of referenced rows be handled?
Choose a declared action that matches policy: restrict the deletion, cascade when dependants should disappear, or permit null when absence is meaningful. An invented placeholder value is valid only if it references a real authorised tuple.
Exam move
For each relation, mark candidate keys before selecting a primary key. Test minimality by removing one attribute at a time and invent a legal counterexample. Draw arrows from each foreign key to its referenced candidate key, then ask what insertion, update or deletion the constraint should reject. Practise mapping one-to-many and many-to-many relationships without using lists inside a cell.
Finish every answer with a legal-state sentence, such as why two enrolments can share student and subject identifiers when semester differs. This makes the key choice traceable rather than cosmetic.
Working through Relational Model and Schema Mapping in INFO20003? Sia is AskSia’s AI Computer Science tutor — ask any INFO20003 Relational Model and Schema Mapping question and get a clear, step-by-step explanation grounded in how INFO20003 is taught and assessed. Read this chapter free, then take your hardest questions to Sia.