about COMP9120
0 · Exam Map course.json + brief
| Assessment | Weight | Format / focus |
|---|---|---|
| Assignment 1 — Conceptual design & schema | 13% | group · ER → SQL schema |
| Quiz | 24% | in-class secure · 90 min · closed book |
| Assignment 2 — Database application development | 13% | group · client + back end |
| Final Exam | 50% | paper-based · 2 h · formal period |
“This is a CLOSED book exam - no other material permitted.” COMP9120 Lecture 13 UoS review slides, 2026 (PDF_L13_UoS_Review)
Hurdle: ≥40% in the final and ≥50% overall; otherwise the final mark is capped at 45. Use this page for active recall: cover each box, recite it, then reproduce the method from a blank page.
1 · Orient the Problem Ch 1
| If the prompt asks… | Think… | Produce… |
|---|---|---|
| domain picture | conceptual | ER/EER diagram |
| tables + keys | logical | relations, PKs, FKs |
| remove anomalies | refinement | closures + decomposition |
| fast access | physical | files/indexes + I/Os |
OLTP = many short operational reads/writes on a normalised schema. OLAP = long, read-heavy aggregates over history, often denormalised. Physical independence: change storage/indexes without changing the conceptual schema. Logical independence: change the conceptual schema while external views/apps survive.
Three levels + DBMS services
External = user views; conceptual = one community-wide logical schema; internal = physical files and indexes. A DBMS bundles persistence and sharing with a query language, integrity, concurrency control, recovery and data independence. Ask which level changed before naming the independence.
2 · ER/EER: Read Every Mark Ch 2–3
Entity = object set; attribute = property; relationship degree = participating entity sets. A relationship attribute belongs to the pairing, not either entity. Arrow/key constraint = at most one relationship instance on that edge. Double/thick line = total participation (at least one).
weak entityno own key · identifying owner · total participation
PK = owner PK + discriminator
IsAF ⊆ E and attrs(F) ⊇ attrs(E)
subclass inherits superclass attributes + relationships
| ER construct | Relational mapping | Key rule |
|---|---|---|
| strong entity | own relation | entity key → PK |
| weak entity | relation + owner PK | owner PK + discriminator |
| 1:N | post 1-side PK on N side | FK on N; NOT NULL if total |
| N:M / n-ary | new relation + all FKs | combined FKs usually PK |
| relationship attrs | with FK/new relationship row | follow its relationship |
| IsA subclass | own relation | PK also FK → superclass |
2b · Mapping Algorithm exam method
- Map strong entities first; underline each PK.
- Map weak entities: copy owner PK, add discriminator; mark the copied column as both PK-part and FK.
- Read each cardinality. For 1:N add one FK to N; for N:M create a relation.
- Place relationship attributes where the relationship is represented.
- Map IsA; then draw every FK arrow and state nullability from participation.
3 · Relational Algebra Ch 4
| Operator | Effect / condition | Exam bite |
|---|---|---|
| σc(R) | rows satisfying c | schema unchanged |
| πA(R) | keep A columns | duplicates removed |
| R × S | all tuple pairs | |R|·|S| rows |
| R ∪ S | either set | union-compatible: same arity + matching domains |
| R − S | in R, not S; directional | |
| R ∩ S | in both; derived | |
| ρ | rename relation/attrs | resolve ambiguity |
| R ⋈ S | join; derived from × + σ | natural uses all same names |
SQL ↔ algebraSELECT ↔ π · FROM ↔ × / ⋈ · WHERE ↔ σ
“Never / none” pattern: begin with all wanted X, subtract X that did Y. A not-equal selection is wrong when an X has both a Y and a non-Y row. Project both operands to the same schema before difference.
3b · Rewrite Safely Ch 4 + 12
- Selection cascade: σc∧d(R) = σc(σd(R)).
- Join: commutative and associative; reorder to keep intermediates small.
- Push σ below a join only if its predicate uses attributes of that input.
- Push π early but retain join keys and every attribute needed above.
3c · Join + Set Checks Ch 4–5
Theta join = σcondition(R×S). An equi-join uses equality; a natural join equates all same-named attributes and keeps one copy of each. With no common names, natural join degenerates to a product. Before writing ∪, ∩ or −, state union compatibility: same number of attributes and matching domains in corresponding positions.
4 · SQL Execution Order Ch 5–6
logical evaluationFROM / JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY
| Need | Use | Trap |
|---|---|---|
| matching rows only | INNER JOIN … ON | missing ON → product |
| unmatched rows too | LEFT/RIGHT/FULL OUTER | NULL padding matters |
| remove result repeats | SELECT DISTINCT | SQL is a bag by default |
| combine results | UNION / INTERSECT / EXCEPT | union-compatible; duplicates removed |
| keep multiplicities | … ALL | m+n / min(m,n) / max(0,m−n) |
| pattern | LIKE; % many, _ one | strings single-quoted |
GROUP rule: every SELECT/HAVING column must be aggregated or named in GROUP BY. WHERE filters rows before grouping; HAVING filters groups after aggregation. ORDER BY is last and may use a SELECT alias.
4b · NULL + Subqueries Ch 6
NULL is unknown/not applicable, not zero or blank. Arithmetic with NULL → NULL; comparison with NULL → unknown; WHERE keeps only true, so unknown is dropped. Test with IS NULL, never = NULL.
COUNT(*) counts rows · COUNT(col), SUM, AVG, MIN, MAX ignore NULL
all-NULL input: COUNT = 0; other aggregates = NULL
| Predicate | Meaning | Recall |
|---|---|---|
| EXISTS(Q) | Q returns ≥1 row | tests emptiness |
| v op ANY(Q) | true for at least one | SOME = ANY |
| v op ALL(Q) | true for every value | ≥ALL highest; ≤ALL cheapest; ties stay |
| NOT EXISTS | no matching row | NULL-safe anti-match |
| NOT IN | not equal to every item | NULL in Q can yield no rows |
5 · Integrity: Pick the Enforcer Ch 7
| Family | Use | Key distinction |
|---|---|---|
| domain | type, DEFAULT, NOT NULL, CHECK | CHECK alone still lets NULL through |
| key | PRIMARY KEY / UNIQUE | PK: one, NOT NULL; UNIQUE: alternatives |
| referential | FOREIGN KEY | child value matches parent key or is NULL |
| semantic | CHECK / assertion / trigger | business rule |
| dynamic | trigger | Event–Condition–Action |
Parent delete/update actions: NO ACTION reject (default); CASCADE propagate; SET NULL; SET DEFAULT. They are declared on the child FK but fire when the parent changes.
Timing: NOT DEFERRABLE checks immediately; DEFERRABLE checks at COMMIT. An assertion spans rows/tables; PostgreSQL does not support CREATE ASSERTION, so use a trigger. Rewrite “for all” as NOT EXISTS a violating row: ∀x P(x) ≡ ¬∃x ¬P(x).
6 · Closures + Normal Forms Ch 9
FD X→Y: tuples agreeing on X must agree on Y. Semantics decides it; one instance can disprove, never prove. Armstrong: reflexivity Y⊆X ⇒ X→Y; augmentation X→Y ⇒ XZ→YZ; transitivity X→Y, Y→Z ⇒ X→Z; derived union/decomposition.
closure algorithmX⁺ ← X; repeatedly add Y for every U→Y with U⊆X⁺; stop at no change.
X superkey ⇔ X⁺ = all attrs · candidate key = minimal superkey
Any attribute never on an FD right-hand side belongs to every candidate key. Seed with those attributes; close; add until all attributes appear; then test minimality by removing each member.
5b · DDL Decision Routine Ch 7
- For every column: domain/type, DEFAULT, NULL status and local range CHECK.
- Choose exactly one PRIMARY KEY; declare alternate candidate keys UNIQUE.
- Add composite FOREIGN KEY clauses with matching parent key order.
- Choose parent-change action from semantics; default is rejection, not cascade.
6b · Normalise + Prove Quality Ch 9
| Form | Test for every relevant dependency | Failure cue |
|---|---|---|
| 1NF | atomic domains | repeating/multivalued field |
| 2NF | no proper subset of candidate key → non-prime | partial dependency |
| 3NF | non-trivial X→A: X superkey OR A prime | transitive non-key determinant |
| BCNF | non-trivial X→Y: X superkey | every arrow must leave a key |
| 4NF | non-trivial X↠Y: X superkey | independent multivalued facts |
BCNF split on violating X→YR₁ = X ∪ Y
R₂ = R − (Y − X)
- Find a non-trivial FD whose determinant is not a superkey.
- Split using the formula; project relevant FDs into each component.
- Repeat until every determinant is a key of its component.
Lossless: for binary R₁,R₂, shared attrs R₁∩R₂ must determine all of R₁ or all of R₂. Dependency preserving: dependencies projected into components together imply the original set, so each can be checked without a join. BCNF can sacrifice preservation; 3NF can preserve.
7 · Transactions + Cost Belt Ch 10–12
Conflict iff same item + different transactions + at least one write. Add Ti→Tj when Ti’s conflicting op occurs first. Acyclic precedence graph ⇔ conflict-serializable; topological sort gives equivalent serial order.