University of Sydney · FACULTY OF COMPUTER SCIENCE

COMP9120 · Database Management Systems

- one subject, every graph, every model, every mark

Computer Science14 Chapters6-page Bible

Our own words - no uploaded lecturer files

Updated for this semester

Chapter 1 of 12 · COMP9120

Foundations & Data Models

Foundations & 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. It fixes four ideas — the services a DBMS provides, the difference between OLTP and OLAP workloads, physical vs logical data independence, and the six-stage database design pipeline — that reappear inside every later exam question on ER modelling, SQL, normalisation and transactions.

In this chapter

What this chapter covers

01What a DBMS is and the services it bundles: data independence, integrity, concurrency, recovery, a query language, persistence and sharing
02OLTP (operational, write-heavy, normalised) vs OLAP (decision-support, read-heavy, denormalised) workloads
03Physical data independence: change storage or indexes without touching the schema or applications
04Logical data independence: change the conceptual schema while existing views and apps keep working
05The three-level architecture: external (views) / conceptual (logical schema) / internal (physical storage)
06The six-stage design pipeline: requirements, conceptual, logical, refinement, physical, application & security
07Which pipeline stage each later chapter owns (ER, ER-to-relational mapping, normalisation, storage/indexing)
08How to name the right term precisely to earn method marks in an applied exam

Worked example · free

Classify the workload and the change type

Q [6 marks]. For a campus bike-share database, classify each item. Workload: (a) record a new rental as a member scans a bike; (b) compute average trip duration per station per month for the last two years; (c) update a member's email address; (d) rank all stations by total rentals to plan capacity. Change type: (e) the DBA replaces the heap file for Bike with a B+-tree index on bike_id; (f) a new column is added to Bike and a view is published exposing only the ops dashboard's columns.

+1For (a)-(d) ask: is it a short read/write of a few rows (OLTP) or a long read-only aggregate over history (OLAP)?
+1(a) records one new row and (c) writes a member's row, so both are OLTP (operational, write-heavy).
+1(b) and (d) each aggregate over two years of history for planning, so both are OLAP (decision support, read-heavy).
+1For (e)-(f) ask: which level changed - internal (physical independence) or conceptual (logical independence)?
+1(e) swaps storage structures at the internal level only, so the schema and views are unchanged: physical data independence.
+1(f) grows the conceptual schema but leaves existing external views and apps working: logical data independence.

Workload: two OLTP (a, c) and two OLAP (b, d). Change type: (e) is physical data independence (storage only), (f) is logical data independence (the conceptual schema changes but existing external views survive).

Sia tip — Decide OLTP vs OLAP by the read/write pattern (write-heavy points to normalised OLTP; read-heavy aggregation points to denormalised OLAP), and decide physical vs logical independence by which architectural gap the change crosses.

Glossary

Key terms

DBMS: Database management system: software that manages a large, persistent, shared collection of data and provides data independence, integrity, concurrency control, recovery and a query language.
OLTP: Online transaction processing: operational, write-heavy workloads made of many short transactions over a normalised schema (for example, recording an enrolment).
OLAP: Online analytical processing: decision-support, read-heavy workloads made of large aggregate queries over historical data, typically on a denormalised design.
Physical data independence: The ability to change storage structures or indexes (the internal level) without altering the conceptual schema or the applications above it.
Logical data independence: The ability to change the conceptual schema without breaking existing external views and the applications that use them.
Three-level architecture: The separation of a database into an external level (user views), a conceptual level (one logical schema), and an internal level (physical files and indexes).
Conceptual schema: The single community-wide logical description of the data - entities, relationships and constraints - independent of any particular DBMS or storage layout.
Design pipeline: The staged path from a problem to a running database: requirements, conceptual (ER) design, logical (relational) design, schema refinement (normalisation), physical design, and application & security design.

FAQ

Foundations & Data Models FAQ

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.

What is the difference between physical and logical data independence?

Physical data independence lets you change storage or indexes (the internal level) - for example replacing a heap file with a B+-tree - without touching the schema or the applications. Logical data independence lets you change the conceptual schema - for example adding a column or splitting a table - while existing external views and applications keep working. The test is which architectural gap the change crosses: internal-to-conceptual is physical, conceptual-to-external is logical.

Can AI help me with database design foundations in COMP9120?

Yes, as a study aid. Sia is an AI tutor that explains concepts step by step - it can walk you through why a workload is OLTP or OLAP, trace the six-stage design pipeline on a practice scenario, or check your reasoning about data independence, so you build the understanding yourself. It will not hand you completed assignment or exam answers and cannot promise a mark or a pass; the aim is to help you learn the material, which is what the closed-book exam rewards.

Studying with AI? Sia — free AI data science tutor works through COMP9120 step by step.

Study strategy

Exam move

Treat this chapter as vocabulary you must own cold rather than a big source of marks. Learn the DBMS services, the OLTP/OLAP distinction, the two data independences and the six design stages, and - most usefully - memorise which later chapter owns each stage (conceptual design is ER modelling, logical design is ER-to-relational mapping, refinement is normalisation, physical design is storage and indexing). When you then meet an applied question, first name the stage you are in, because a conceptual question wants a diagram, a logical question wants relations with keys and foreign keys, and a refinement question wants functional dependencies and a decomposition. Because the final exam is 2 hours and closed book, spend most revision time drilling those applied stages and only enough here to use the terms precisely.

Keep going — explore the course

A+Everything unlocked

Unlocks this Bible + all 25 of your University of Sydney subjects - and 1,000+ Bibles across every Australian university.

Sia - your COMP9120 tutor, unlimited, worked the way the exam marks it

The full 6-page Bible + practice bank with worked solutions

Chrome extension - sync your LMS so Sia knows your deadlines

Bilingual EN / Chinese on every Bible and every Sia answer

$25/ month

30-day money-back · cancel in one tap · how it works