COMP9120 · Database Management Systems
The Complete Exam Bible · S2 2026Database Management Systems
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. 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.
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.
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 |
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.
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.
