INFO20003 Database Systems

How INFO20003 is assessed

The 2026 University handbook publishes the assignments at 30%, weekly quizzes at 10% and open book written examination at 60%. The LMS subject overview identifies the three assignments as 10% each and publishes both cumulative hurdle rules. Numeric weights total 100%.

What INFO20003 covers

Move from database requirements and schema design to SQL, query cost, transactions and distributed data choices.

It builds formal query reasoning through relational algebra and practical capability through SQL before opening the DBMS to examine pages, indexes, join algorithms, query optimisation, transactions, recovery and administration. The final weeks compare operational databases with data warehouses, distributed databases and NoSQL models.

The assessment rewards both construction and explanation.

The practical assignments cover modelling, relational algebra and SQL, then query processing and optimisation. Weekly quizzes maintain coverage across lecture material. The final examination is a three hour written open book assessment worth 60%. Two cumulative hurdles apply: at least 15 out of 30 across the three assignments, and at least 35 out of 70 across the weekly quizzes plus examination.

Because these are aggregate hurdles, no single assignment or exam row should be treated as a separate pass rule.

This guide is organised around repeatable answer methods. Modelling chapters show how to justify identity, cardinality and assumptions. Query chapters preserve the requested population and result grain. Systems chapters keep tuples, pages, buffers and I/O units separate.

Every practice scenario is original, while the response types follow the University teaching materials and sample solutions.

Worked example · free

Cost a block nested loops join

Key terms

Candidate key

A minimal set of attributes that uniquely identifies every tuple in a relation.

Foreign key

An attribute set whose non-null values reference a candidate key in another or the same relation.

Cardinality

The permitted number of relationship instances associated with one entity instance.

Relational algebra

A closed formal language whose operators consume relations and return relations.

Result grain

The real world unit represented by one output row of a query.

Clustered index

An index whose search key order matches or closely controls the physical order of data records.

Reduction factor

The estimated fraction of input tuples that survive a predicate.

Query plan

A physical operator tree chosen to execute a declarative query.

Functional dependency

A legal state rule in which agreement on one attribute set requires agreement on another.

Serializability

The property that a concurrent schedule has the same effect as some serial transaction order.

Fact table grain

The precise event or snapshot represented by one row in a dimensional fact table.

Partition tolerance

The ability of a distributed system to continue operating despite disrupted communication between nodes.

INFO20003 FAQ

Questions students ask in each INFO20003 chapter

How do I verify traceability across the lifecycle?

Select one business rule and follow it through the conceptual model, logical constraint and implementation test. Then reverse the path from the implemented object to the approved requirement. A missing link means either the rule is unenforced or the technical object lacks a demonstrated purpose; both findings require review at the earliest affected stage. Chapter 01 · Database Systems and Design Lifecycle

When should time become part of relationship identity?

Include a temporal discriminator when the same participant pair may form more than one valid relationship instance over time and history must remain distinguishable. If only the current relationship is permitted, the pair can remain unique. State that business rule before choosing a composite key or introducing a separate relationship identifier. Chapter 02 · Entity Relationship Modelling

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. Chapter 03 · Relational Model and Schema Mapping

How can I check an algebra tree before simplifying it?

Write the heading below every intermediate relation and mark the last point where each attribute is needed. Then test one ordinary tuple, one missing match and one pair that becomes duplicate after projection. These checks reveal predicates moved below their required attributes, incompatible set operands and projections that discard a later join key. Chapter 04 · Relational Algebra

Why should I declare the output grain first?

The grain determines whether a join is allowed to multiply rows, which columns belong in GROUP BY and what one aggregate value represents. Writing one row per customer, order or match before the query makes accidental fan-out visible and helps choose between an ordinary join, EXISTS or pre-aggregation. Chapter 05 · SQL Query Construction

Why can a selective predicate still make an index expensive?

If the index is unclustered, qualifying records may be scattered across many data pages, producing nearly one random fetch per match. Estimate index traversal and data-page work rather than relying on the result percentage alone. Clustering, covering columns and cached pages can change that physical cost without changing selectivity. Chapter 06 · Storage Structures and Indexes

Why must join feasibility be checked before cost?

Compact formulas assume enough memory for a stated number of passes or partitions. If a hash partition does not fit or a sort needs another merge pass, the printed estimate omits real I/O. State the memory condition, reject an infeasible candidate or add its extra passes, and only then rank algorithms. Chapter 07 · Query Processing and Join Costs

Why can the optimiser keep a locally dearer plan?

A plan may deliver an interesting order or partitioning that saves a later sort, enables merge processing or supports grouping. Dynamic programming therefore compares plans for the same relation subset together with useful physical properties, not cost alone. The global plan can be cheaper even when one retained intermediate is not. Chapter 08 · Query Optimisation

Are normal form and lossless decomposition the same property?

No. Normal form constrains dependency patterns inside each resulting relation. Losslessness asks whether joining the components recreates exactly the legal original tuples, and dependency preservation asks whether original rules remain enforceable without a join. Report and test these three properties separately rather than treating a 3NF label as proof of all three. Chapter 09 · Normalisation and Dependencies

What evidence proves a backup strategy works?

A successful copy job proves only that bytes were written. Perform a timed restore using the retained backup and log chain, validate schema and application-level facts, record achieved recovery point and recovery time, and test access credentials plus dependencies. That exercise demonstrates recoverability for the declared failure scenario. Chapter 10 · Transactions and Database Administration

What must accompany a polyglot persistence recommendation?

Name the access pattern or consistency boundary each store improves, then account for duplicated facts, event ordering, schema evolution, monitoring and recovery across systems. Using several data models is justified only when the workload benefit exceeds the coordination and operational cost transferred into applications and platform processes. Chapter 11 · Data Warehousing, Distribution and NoSQL

How to study for the exam

Organise revision by task type rather than lecture order. First build a one-page modelling checklist covering entity identity, relationship attributes, optionality, maximum cardinality and assumptions. Next maintain a SQL pattern page for outer joins, self joins, aggregation, EXISTS, NOT EXISTS and universal conditions.

Keep a separate systems sheet that labels every quantity as tuples, pages, buffers, blocks or I/Os and includes the join and index formulas used in the teaching materials. Practise normalisation by writing functional dependencies before moving any attribute, then verify lossless reconstruction and dependency preservation.

For transactions and architectures, answer with a failure or workload: name the undesirable state, the mechanism that prevents it and the trade-off introduced. Because the examination is open book, invest in retrieval. Add tabs and a short index that points to methods and worked structures, then practise finding each within seconds.

Complete original mixed questions under time and recompute every numeric answer from the printed steps.

Your AI Computer Science tutor for INFO20003

Stuck on a hard INFO20003 question? Sia is AskSia’s AI Computer Science tutor — ask any INFO20003 Database 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.