FIT2094 Chap.2 Relational Models, Keys and Algebra
Relational Models, Keys and Algebra
The relational model represents facts as tuples in relations and protects meaning through keys and integrity constraints. Candidate keys are minimal attribute sets that uniquely identify tuples. A selected primary key is not the only identity rule: alternate candidate keys still require protection. Foreign keys carry identity into related relations and must obey referential integrity.
Entity integrity ensures every stored row has known primary identity, while domain rules restrict values to permitted forms.
Relational algebra describes how result relations are derived. Selection σ keeps rows that satisfy a predicate, projection π keeps attributes, joins ⋈ combine related tuples, and union ∪, intersection ∩ and difference − compare compatible relation shapes.
Long expressions become easier to verify when broken into named intermediate relations with a stated heading and meaning. Correctness depends on the business key and set semantics, not on symbol density.
Key analysis and algebra should be checked together. An incomplete event key can merge two valid occurrences before a query begins, while projection can discard a join key needed at a later stage.
For each intermediate relation, write its heading, its plain-language meaning and one tuple that should be present. Before applying a set operator, confirm that both inputs describe compatible values in compatible attributes. Before accepting a difference result, include an identifier that occurs on both sides and one that occurs only on the left.
Small boundary relations make identity mistakes and operator-order mistakes visible before an expression is compressed.
What this chapter covers
- 01
Relations, tuples, attributes and domains
- 02
Candidate keys, primary keys and alternate uniqueness
- 03
Foreign keys, entity integrity and referential integrity
- 04
Insert, update and delete anomalies as mixed-fact symptoms
- 05
Selection and projection in relational algebra
- 06
Joins and compatible set operations
- 07
Intermediate relation headings and boundary-case verification
Worked example · free
Find resources not reserved in a time window
- +2Select RESERVATION rows whose start is before the target end and whose end is after the target start. This captures every interval overlap, including reservations that began earlier.
- +1Project resource_id from the selected reservations to form the set of occupied resources.
- +2Project resource_id from RESOURCE, then subtract the occupied set. Both operands now have compatible headings.
- +2Join the remaining identifiers back to RESOURCE and project resource_id and room for the requested output.
- +1Test a reservation spanning the entire target interval, one ending exactly at the target start and one beginning exactly at the target end to verify the chosen boundary rule.
Key terms
- Relation
- A set of tuples sharing a defined heading of attributes and domains in the relational model.
- Candidate key
- A minimal attribute combination that uniquely identifies each tuple under the business rules.
- Foreign key
- An attribute set whose values reference a candidate key in another or the same relation.
- Entity integrity
- The rule that every row has a unique, non-null primary-key value and therefore known identity.
- Referential integrity
- The rule that a non-null foreign-key value must identify an existing referenced tuple.
- Projection
- A relational-algebra operation that returns selected attributes from a relation and forms a new relation.
- Selection
- A relational-algebra operation that keeps tuples satisfying a stated predicate.
Relational Models, Keys and Algebra FAQ
Why should alternate candidate keys still be constrained?
Choosing one candidate key as primary does not make the other real-world identities disappear. Without uniqueness on an alternate key, different rows can represent the same business instance under different surrogate values. Preserve every source-backed candidate key.
What causes insert, update and delete anomalies?
They arise when one relation mixes facts about different subjects. A repeated supplier address must be updated in several rows, a supplier cannot be inserted without a product, or deleting the last product erases the supplier. Dependencies reveal the mixed facts.
When should projection happen in an algebra expression?
Project when unused attributes can be safely removed, but retain every key needed for later joins or set comparisons. Projecting too early can make the next operator impossible or change the meaning of duplicate-looking tuples.
How do I choose between union, intersection and difference?
Union means membership in either compatible set, intersection means membership in both, and difference means membership in the first but not the second. State the compatible headings and test one row from each membership region.
Assessment move
Build key fluency before algebra speed. For each relation, list all candidate keys, select a primary key, mark alternate uniqueness and identify foreign keys. Invent duplicate and orphan rows to test the rules. For algebra, translate the question into sets and name each intermediate relation. Write its heading, the rows it represents and the operator that produces it.
Delay projection until join keys have been used, and check that set-operation operands are compatible. Use tiny datasets containing overlap, exclusion and duplicates. A complete solution ends with the exact requested heading and a sentence explaining one included and one excluded tuple.
Rework one expression in two valid operator orders and compare the intermediate headings; this builds flexibility without sacrificing the required result semantics.
