COMP9120 Database Management Systems
COMP9120 Overview
- University of Sydney
- S2 2026
- 12 chapters
- Computer Science
COMP9120 Database Management Systems is a postgraduate core unit at the University of Sydney that runs the full database pipeline: conceptual ER design, the relational model and mapping, relational algebra and SQL, integrity and application development, then normalisation, transactions, storage and query processing.
- Assessed by Assignment 1 — Conceptual design & schema (group) 13% · Quiz 24% + 2 more
- Key terms DBMS, ER model, Weak entity, Primary key
- How to prepare Treat COMP9120 Database Management Systems at the University of Sydney as a set of repeatable procedures rather than facts to memorise: for each…
- Most asked Where can I find past exam papers / practice for COMP9120?
What you get in the full COMP9120 Exam Bible
- 12 chapters, 110 pages — every examinable topic, week by week
- 11 original practice questions, each with a model answer in the Final Exam format
- 12 worked examples, solved the way COMP9120 marks
- 32 computed figures, redrawn from the course’s own numbers
- Exam-morning plan for the Final Exam (50%) — what to revise last, where marks are lost
Free on this page: assessment table, chapter map, one worked example, key terms, FAQ. The rest is in the full exam bible.
How COMP9120 is assessed
| Component | Weight | Format |
|---|---|---|
| Assignment 1 — Conceptual design & schema (group) | 13% | Build an ER model from a brief, derive a SQL schema, implement in PostgreSQL; released Week 4, due Week 7 |
| Quiz | 24% | In-class secure test, closed book, 90 minutes, covers Weeks 1-9, ~Week 10 |
| Assignment 2 — Database application development (group) | 13% | Client interface over the DB + back-end stored procedures/indexes (Java or Python); released Week 8, due Week 11 |
| Final Exam | 50% | Paper-based, 2 hours, closed book, formal exam period |
What other students are asking about COMP9120
- Where can I find lecture notes or study guides for COMP9120 at USyd?
- The COMP9120 study guide in AskSia Library covers Database Management Systems in 12 chapters, with the assessment table, a chapter-by-chapter topic map, key terms and a worked example, free to read on this page.
- Is there a final exam in COMP9120, and how much is it worth?
- Yes. In the offering this AskSia Library guide is built from, Final Exam carries 50% of the COMP9120 mark; the full split is Assignment 1 — Conceptual design & schema (group) 13%, Quiz 24%, Assignment 2 — Database application development (group) 13%, Final Exam 50%.
- What does COMP9120 cover on conceptual modelling (er/eer)?
- In AskSia Library's COMP9120 guide, the Conceptual Modelling (ER/EER) chapter explains entities, relationships, cardinality, weak entities, IsA, aggregation.
- What does COMP9120 cover on relational model & er→relational mapping?
- In AskSia Library's COMP9120 guide, the Relational Model & ER→Relational Mapping chapter explains relations, keys, foreign keys, translating an ER diagram to a schema.
- What does COMP9120 cover on relational algebra?
- In AskSia Library's COMP9120 guide, the Relational Algebra chapter explains the six basic operators, joins, set operations, expression equivalence.
What COMP9120 covers
Twelve chapters trace the database pipeline the way COMP9120 examines it - from ER models and the relational mapping, through relational algebra and SQL, to integrity, normalisation, transactions and the storage-and-query internals behind the 50% final.
Foundations & Data Models
DBMS role, OLTP vs OLAP, data independence, the design pipelineFoundations & Data Models is the opening block of COMP9120 Database Management Systems at the University of Sydney, where you learn what a database management system (DBMS) actually is and the vocabulary the rest of the unit assumes.
- What a DBMS is and the services it bundles: data independence, integrity, concurrency, recovery, a query language, persistence and sharing
- OLTP (operational, write-heavy, normalised) vs OLAP (decision-support, read-heavy, denormalised) workloads
- Physical data independence: change storage or indexes without touching the schema or applications
- Logical data independence: change the conceptual schema while existing views and apps keep working
- The three-level architecture: external (views) / conceptual (logical schema) / internal (physical storage)
- The six-stage design pipeline: requirements, conceptual, logical, refinement, physical, application & security
Conceptual Modelling (ER/EER)
Entities, relationships, cardinality, weak entities, IsA, aggregationConceptual modelling is the second step of database design in COMP9120 Database Management Systems at the University of Sydney: it turns a written description of what a business needs to store into a precise Entity–Relationship (ER) diagram, then into relational tables.
- Where conceptual (ER) modelling sits in the design pipeline: requirements to logical schema
- Entities, attributes and the key family (superkey, candidate key, primary key)
- The eight core EER symbols and when to use each attribute type
- Relationships: degree (unary/binary/ternary), role names and relationship attributes
- Mapping cardinality 1:1, 1:N and N:M — and the many-to-one direction trap
- Key and participation constraints (arrow, thick line) and (min..max) annotations
Relational Model & ER→Relational Mapping
Relations, keys, foreign keys, translating an ER diagram to a schemaThis chapter of the COMP9120 Database Management Systems study guide at the University of Sydney is the bridge from a conceptual ER diagram to a database schema.
- What a relation is: a set of tuples over a schema with atomic domains
- Schema vs instance, degree (arity) and cardinality
- The key hierarchy: superkey ⊇ candidate key (minimal) → primary key
- Foreign keys and referential integrity: every FK must point at a real row
- The 1:N rule: post the one-side primary key as a foreign key on the many side
- The N:M and n-ary rule: build a new relation keyed on the combined foreign keys
Relational Algebra
The six basic operators, joins, set operations, expression equivalenceRelational algebra is the procedural query language of the relational model, and in COMP9120 Database Management Systems at the University of Sydney it is the algebra every SQL query is compiled into before it runs.
- Name the six basic operators — selection σ, projection π, cross-product ×, union ∪, set-difference −, rename ρ — and know that intersection and join are derived
- Write and read selection (rows) and projection (columns), and why σ must sit inside π when it filters on a column you are about to drop
- Remember that projection removes duplicate rows, because relational algebra works on sets
- Build the join family from the basics: theta join, equi-join and natural join, plus the schema a natural join produces
- Apply the three set operations under the union-compatibility rule (same arity and matching domains)
- Use set-difference for the 'none / never' pattern and get its direction right (R − S is not S − R)
SQL Fundamentals
SELECT-FROM-WHERE, joins, DISTINCT, ORDER BY, set operatorsSQL Fundamentals is the core query chapter of COMP9120 Database Management Systems at the University of Sydney, introduced in the Week 4 lecture on Relational Algebra & SQL.
- Write a SELECT-FROM-WHERE query and map it to relational algebra (SELECT↔projection, FROM↔cross-product, WHERE↔selection)
- Apply the true clause evaluation order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
- Understand why a SQL result is a bag and use DISTINCT to turn it back into a set
- Sort output with ORDER BY (ASC default, DESC, multiple keys) and know it has no relational-algebra equivalent
- Choose between implicit, INNER, NATURAL, USING and OUTER joins, and know when NULL padding matters
- Use table aliases for self-joins and to disambiguate columns across tables
Advanced SQL
Nested queries, aggregation & grouping, views, NULL & three-valued logicAdvanced SQL is the Week 5 topic of COMP9120 Database Management Systems at the University of Sydney, extending the basic SELECT-FROM-WHERE surface into exam-grade querying.
- Write nested (sub-)queries and set-comparison predicates: v [NOT] IN R, [NOT] EXISTS R, v op ANY/SOME R, v op ALL R
- Express the ‘highest / cheapest’ idiom with >= ALL and <= ALL, and know both keep tied rows
- Tell correlated sub-queries (reference an outer column, re-evaluated per row) from uncorrelated ones
- Summarise rows with COUNT, SUM, AVG, MAX and MIN, and know COUNT(*) counts rows while COUNT(col) skips NULLs
- Partition rows with GROUP BY and filter groups with HAVING, keeping WHERE (rows) and HAVING (groups) in their correct roles
- Apply the true clause evaluation order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
Database Integrity
Domain/key/referential/semantic constraints, assertions, triggersDatabase integrity is the COMP9120 topic where a design becomes enforcement: the University of Sydney's Database Management Systems unit teaches you to spell out the rules that keep every stored state legal — domain, key, referential and semantic constraints, plus assertions and triggers — in SQL.
- What an integrity constraint is, and the three ways a DBMS reacts to a violation (reject, abort, maintenance action)
- Static families (domain, key, referential, semantic, assertions) vs the one dynamic family (triggers)
- Domain constraints: data type, DEFAULT, NOT NULL, CHECK, and CREATE DOMAIN
- Key constraints: PRIMARY KEY vs UNIQUE, NULL behaviour, and composite keys
- Referential integrity: foreign keys, dangling references, and the four on-delete/update actions
- Constraint timing: NOT DEFERRABLE (immediate) vs DEFERRABLE (checked at COMMIT)
Database Application Development
JDBC/Python DB-API, prepared statements, stored procedures, indexesDatabase Application Development is the Week-7 topic of COMP9120 Database Management Systems at the University of Sydney, where SQL stops living in a terminal and starts running inside real Java or Python programs.
- Why the impedance mismatch forces a call-level API between set-oriented SQL and record-oriented code
- The four-step JDBC model: connect, create a Statement, execute, iterate the ResultSet cursor
- The Python DB-API workflow with psycopg2: connection, cursor, execute, fetchone / fetchmany / fetchall
- How SQL injection works and why string concatenation of user input is never safe
- Prepared / parameterised statements: bind placeholders, reuse the query plan, block injection
- Stored procedures in PL/pgSQL with IN / OUT / INOUT parameters, called via CallableStatement or callproc
Schema Refinement & Normalisation
FDs, closures, candidate keys, 1NF–BCNF/4NF, lossless & dependency-preserving decompositionSchema refinement is the COMP9120 topic where a workable relational design becomes a good one: the University of Sydney's Database Management Systems unit teaches you to detect redundancy formally through functional dependencies, grade a relation by normal form (1NF through BCNF and 4NF), and split a bad relation into good ones without losing information.
- Why redundancy is the enemy: update, insertion and deletion anomalies
- Functional dependencies X→Y, and why an instance can disprove but never prove one
- Armstrong's axioms (reflexivity, augmentation, transitivity) plus the derived union and decomposition rules
- Attribute closure X+ as the one algorithm that tests keys and derives FDs
- Superkeys, candidate keys (minimal superkeys), and prime vs non-prime attributes
- The nested normal forms 1NF ⊇ 2NF ⊇ 3NF ⊇ BCNF ⊇ 4NF and how to name the highest one
Transaction Management & Concurrency
ACID, conflict-serializability, precedence graphs, 2PL, deadlockTransaction management is the part of COMP9120 Database Management Systems at the University of Sydney that decides what it means for a database to stay correct while many users read and write at the same time.
- What a transaction is, and how BEGIN / COMMIT / ROLLBACK delimit an atomic unit of work
- The four ACID properties — and who enforces each (Recovery Manager, Concurrency Control, the developer)
- The three concurrency anomalies: lost update, dirty read (temporary update), and incorrect summary
- Serial vs serializable schedules, and why serializability is the correctness target
- The conflict rule: two ops conflict iff same item, different transactions, and at least one is a write
- Building a precedence graph and using the acyclic test to decide conflict-serializability
Storage & Indexing
File organisation, B+ trees, clustered vs unclustered, page/I-O costingStorage & Indexing is the physical layer of COMP9120 Database Management Systems at the University of Sydney: how a table's rows pack onto fixed-size pages, and how a B+-tree or hash index turns a full scan into a short lookup.
- Why databases are I/O-bound and why cost is counted in page I/Os
- Heap vs sorted vs index file organisations and their search costs
- Records-per-page and data-page counts: floor for capacity, ceil for page counts
- Fill-factor, blocking factor, and record spanning
- The four index axes: primary/secondary, clustered/unclustered, dense/sparse, tree/hash
- Dense vs sparse data entries and when each is legal
Query Processing & Optimisation
Heuristic + cost-based optimisation, join algorithm costs, external merge sortQuery processing is how a declarative SQL statement becomes a running physical plan, and optimisation is how the engine chooses a cheap one.
- The three steps of query processing: parse & translate SQL to relational algebra, optimise, then execute
- Logical expression tree vs physical execution plan — and why one query has many plans
- Heuristic (algebraic) optimisation: push selections and projections down to shrink intermediate results
- The equivalence rules — selection cascade, join commutativity/associativity, selection distributing over a join
- Cost-based optimisation: annotate operators with physical algorithms and pick the lowest estimated page-I/O
- Join costs: tuple nested-loop bR+|R|·bS, block nested-loop bR+bR·bS, index nested-loop bR+|R|·c
This free exam bible reworks every examinable skill in University of Sydney COMP9120 Database Management Systems - ER-to-relational mapping, nested and aggregate SQL, BCNF decomposition, conflict-serializability and page-I/O costing - into worked, step-by-step problems.
It is a study aid, not a substitute for Canvas: confirm all weights, dates and rules on your COMP9120 unit page.
COMP9120 cheatsheet for the final exam
AskSia publishes a free COMP9120 cheatsheet for the final exam, a Cheatsheet that follows the official permitted-materials rule for this assessment and condenses the topics in this guide.
Worked example · free
Block nested-loop join cost
- +1Write this unit's formula: block nested-loop join cost = b_R + b_R * b_S. The inner relation S is scanned once per outer PAGE of R (not once per outer row, and not the M-2 block variant).
- +1Read the outer relation R once: b_R = 60 page reads.
- +1Each of R's 60 pages drives one full scan of S: inner reads = b_R * b_S = 60 * 500 = 30,000 page reads.
- +1Total = b_R + b_R * b_S = 60 + 30,000 = 30,060 I/Os.
Key terms
- DBMS
- Software that manages large, persistent, shared collections of data, providing data independence, integrity, concurrency control, recovery and a query language.
- ER model
- A conceptual design notation of entities, attributes and relationships (with cardinality and participation) used to model a domain before it becomes a relational schema.
- Weak entity
- An entity with no key of its own; identified by an owner via an identifying relationship, so its primary key is the owner's key plus a discriminator.
- Primary key
- The chosen candidate key of a relation; it is unique and NOT NULL, and there is exactly one per table.
- Foreign key
- An attribute (or set) whose values must match a candidate-key value in a referenced relation, enforcing referential integrity.
- Relational algebra
- A procedural query language over relations with six basic operators (selection, projection, cross-product, union, set difference, rename); join and intersection are derived.
- Functional dependency
- A constraint X -> Y meaning each value of X determines at most one value of Y; the basis of normalisation, judged by semantics not by a single instance.
- Attribute closure (X+)
- The set of all attributes determined by X under a set of FDs; X is a superkey exactly when X+ is all attributes.
- Candidate key
- A minimal superkey: it determines every attribute, and no proper subset of it does.
- BCNF
- Boyce-Codd Normal Form: for every non-trivial FD X -> Y, X is a superkey; it removes redundancy by requiring every determinant to be a key.
- Lossless-join decomposition
- A split of R into pieces that rejoin to exactly R (no spurious tuples); guaranteed when the common attributes form a key of at least one piece.
- Conflict-serializability
- A schedule is conflict-serializable when its precedence graph is acyclic, meaning it is conflict-equivalent to some serial order of the transactions.
- Two-phase locking (2PL)
- A protocol with a growing phase (acquire locks only) then a shrinking phase (release only); it guarantees conflict-serializability. Strict 2PL holds all locks until commit.
- Clustered index
- An index whose data records are stored in search-key order, so range matches sit on consecutive pages; an unclustered index scatters matches, costing up to one I/O per matching record.
COMP9120 FAQ
Can AI help me study COMP9120?
Yes, as a study aid. Sia works through COMP9120 problems step by step - it will map an ER diagram to relations, walk a BCNF decomposition, or explain why a schedule is or is not conflict-serializable, and then check your own working. It explains the method so you can reproduce it under closed-book exam conditions; it will not sit your quiz, exam or assignments for you.
Where can I find past exam papers / practice for COMP9120?
Official past papers and practice questions are released through your University of Sydney COMP9120 Canvas site and the library's exam-paper collection - always start there. This free bible adds an eleven-question practice bank with fully worked solutions across the whole unit, written in the exam style with fresh scenarios, and you can ask Sia to explain any step you get stuck on.
What can Sia do that a textbook can't?
A textbook explains a topic once, for everyone; Sia is interactive. It adapts to your exact question - your ER diagram, your FD set, your join numbers - shows each step, and answers 'why' when you are stuck. It is a tutor that explains, not an answer key: it guides you to the method rather than completing your assessment.
Is COMP9120 hard?
It is a broad core unit that combines design (ER, relational, normalisation), query languages (relational algebra and SQL) and systems reasoning (transactions, storage, query cost). Most students find it manageable by practising the recurring tasks until the method is automatic, because the exam rewards shown working. The load is real but the topics are well defined.
Is the COMP9120 final open or closed book?
The final is closed book: a paper-based, 2-hour written exam with no notes or textbook, held in the end-of-semester formal exam period (University of Sydney Semester 2, around November 2026). That is why the rule and formula belts need to be memorised - confirm the exact date, time and venue on your COMP9120 Canvas site and the University of Sydney exam timetable.
What is the COMP9120 hurdle requirement?
To pass you must score at least 40% in the final exam and at least 50% overall; if you miss either, the final mark can be capped at no more than 45 regardless of your average. The final exam is a hurdle task, so clearing 40% on it comes before chasing a grade.
How is COMP9120 assessed?
Four components sum to 100%: Assignment 1 (conceptual design and schema, 13%), an in-class closed-book Quiz (24%), Assignment 2 (database application development, 13%), and the Final Exam (50%). The two assignments are group work; the exam and quiz are individual. Confirm the current split on Canvas.
Questions students ask in each COMP9120 chapter
- Is Foundations & Data Models heavily examined in COMP9120?
- The COMP9120 final exam is applied and problem-based - it asks you to write SQL and relational algebra, map an ER diagram to relations, decompose to BCNF and reason about transactions - so foundations concepts rarely carry standalone marks. Instead they are threaded through those questions as vocabulary, so being precise about terms like conceptual vs internal, physical vs logical data independence, and OLTP vs OLAP earns easy method marks and prevents mislabelling. Confirm the exact structure for your offering on Canvas. Chapter 01 · Foundations & Data Models
- Is ER modelling actually on the COMP9120 final exam, or just in the assignment?
- Both. Conceptual modelling is Week 2 content, so it is assessed in Assignment 1 (a group task worth 13% that builds an ER model and derives a PostgreSQL schema), it is within the scope of the in-class Quiz (24%, covering Weeks 1 to 9), and the final exam (50%, paper-based, 2 hours, closed book) opens with an ER-to-relations plus data-integrity question. Confirm the exact marks and weightings on Canvas. Chapter 02 · Conceptual Modelling (ER/EER)
- In a 1:N relationship, which table gets the foreign key?
- The many (N) side. Each row on the many side relates to exactly one parent, so a single foreign-key column referencing the one-side primary key is enough, and no separate relation is needed. If participation on the many side is total, make that foreign key NOT NULL. Putting the key on the one side would force a single parent row to store many child identifiers, which breaks the atomic-domain rule. Chapter 03 · Relational Model & ER→Relational Mapping
- Can AI help me with relational algebra in COMP9120?
- Yes, as a study aid. Sia is an AI tutor that explains relational algebra step by step: it can walk you through why a selection sits inside a projection, how a natural join builds its schema, or how to turn a 'never' query into a set-difference, using its own practice examples. Use it to understand the method and check your reasoning — it will not hand you answers to graded assignments, quizzes or the exam, and it cannot promise a particular mark. The point is to learn the operators well enough to write them yourself under exam conditions. Chapter 04 · Relational Algebra
- Why does my query return duplicate rows when relational algebra wouldn't?
- A SQL result is a bag (multiset), not a set: projecting to non-key columns, or joining where one row matches several rows on the other side, produces repeats. Add SELECT DISTINCT to eliminate duplicate output rows and recover set behaviour. Projection in pure relational algebra removes duplicates automatically, which is why the two can differ. Chapter 05 · SQL Fundamentals
- Why does WHERE price < 900 quietly drop rows whose price is NULL?
- Because a comparison with NULL evaluates to unknown, not true or false, and WHERE keeps a row only when its condition is true — it treats unknown as false. So a NULL price makes price < 900 unknown and the row is discarded. Even the ‘covers everything’ test price < 900 OR price >= 900 still drops NULL prices, since both halves are unknown; you must add OR price IS NULL explicitly to include them. Chapter 06 · Advanced SQL
- What is the difference between an assertion and a CHECK constraint?
- A table-level CHECK sees only one row of one table and is re-checked only when that table changes, so it cannot express a rule that counts or compares rows across a whole table or across tables. An assertion spans multiple tables and is re-checked on any update that could violate it, which is its advantage; its disadvantages are cost and that major systems (including PostgreSQL) do not implement it, so you enforce the rule with a trigger instead. Chapter 07 · Database Integrity
- Is Database Application Development on the COMP9120 final exam?
- No. The Week-7 lecturer states this material is not covered in the final exam or the in-class quiz; the quiz covers Weeks 1-9 except Week 7. It is assessed through Assignment 2, a 13% group project that builds a client interface plus an advanced back end with stored procedures and indexes. Confirm the exact scope and dates on Canvas for your offering. Chapter 08 · Database Application Development
- What is the difference between 3NF and BCNF?
- Both require that for every non-trivial functional dependency X→Y the determinant X should ideally be a superkey. BCNF demands exactly that with no exceptions. 3NF is slightly weaker: it also accepts an FD whose determinant is not a superkey as long as the right-hand side Y is a prime attribute (part of some candidate key). That loophole means a 3NF relation can still hold a little redundancy that BCNF removes. The practical catch is that forcing BCNF can sometimes cost dependency preservation, whereas a lossless, dependency-preserving 3NF decomposition is always achievable, so 3NF is occasionally the right stopping point. Chapter 09 · Schema Refinement & Normalisation
- What is the difference between a serial and a serializable schedule?
- A serial schedule runs the transactions one after another with no interleaving — always correct but with no concurrency. A serializable schedule may interleave operations, but its overall effect is equivalent to some serial order, so it is just as correct while allowing concurrency. In the exam you usually test the stricter, mechanical version — conflict-serializability — by drawing the precedence graph and checking that it is acyclic. Chapter 10 · Transaction Management & Concurrency
- Why does COMP9120 measure everything in page I/Os instead of CPU time?
- A database is I/O-bound: reading a page from disk (seek + rotational delay + transfer) is far slower than any in-memory work, and one seek dwarfs the CPU cost of processing the page. So the unit ignores CPU and counts only the number of fixed-size pages moved between disk and memory. Every file-organisation and index cost in the topic is expressed as a page count, which is exactly what the final exam asks you to compute by hand. Chapter 11 · Storage & Indexing
- What is the difference between heuristic and cost-based optimisation?
- Heuristic (algebraic) optimisation is a logical rewrite: it applies equivalence rules — mostly pushing selections and projections down — to shrink intermediate results, without knowing any page counts. Cost-based optimisation comes next: it annotates each operator with a physical algorithm and access path, estimates the page-I/O of each candidate plan from catalogue statistics such as cardinalities and blocking factors, and keeps the cheapest. Heuristics decide the shape of the tree; cost estimates decide the algorithms. Chapter 12 · Query Processing & Optimisation
How to study for the exam
Treat COMP9120 Database Management Systems at the University of Sydney as a set of repeatable procedures rather than facts to memorise: for each topic - ER-to-relational mapping, relational algebra, SQL, functional dependencies and BCNF, conflict-serializability, and storage and join cost - drill the method until you can reproduce it from a blank page, since the closed-book final rewards shown working.
Work the practice bank by covering each solution first, then compare line by line, and rebuild the rule and formula belts from memory each week so they are automatic on the day. When a step will not click, ask Sia to explain that exact step, then redo the whole problem yourself.
Your AI Computer Science tutor for COMP9120
Stuck on a hard COMP9120 question? Sia is AskSia’s AI Computer Science tutor — ask any COMP9120 Database Management Systems question and get a clear, step-by-step explanation grounded in how the course is actually taught and assessed. Read this whole study guide free, then take your hardest questions to Sia.