Learn the whole unit — no PDFs required

0 How it all fits together

A database stores facts about the real world. The unit is one pipeline for building one properly. Keep this order in your head — most exam questions are "do step X" or "spot the mistake in step X".

1. Understand the domain → write it as plain-English elementary facts (ORM Step 1).
2. Model it → draw an ORM diagram (the conceptual schema) using the 7-step CSDP.
3. Convert to tables → Relational Mapping (RMap) turns the diagram into table definitions.
4. Clean the tables → Normalisation removes redundancy (1NF → 2NF → 3NF).
5. Build & query → SQL (DDL creates tables, DML inserts/queries data).
6. Run it safely → security & administration (views, roles, access control, transactions).
7. Do it ethically → legal & ethical issues, privacy law and Indigenous data principles.

1 Databases & the relational model

A database is an organised collection of related data. A DBMS (e.g. MySQL) is the software that stores, manages and queries it. In the relational model, data lives in tables (also called relations).

• A row (a tuple / record) = one instance, e.g. one student.
• A column (an attribute / field) = one property, e.g. studentName.
• Every row should be unique.
• Each cell holds a single atomic value — one value, not a list.

Here's a concrete Student table. Each row is one student (a tuple); each column is one property (an attribute):

Why atomic matters: if you crammed "ava@uni.edu, ava2@gmail.com" into the one email cell, you could no longer search, sort or count emails reliably — the cell holds a list, not a value. One fact per cell keeps the data queryable. This single rule (atomic cells) is exactly what 1NF will later demand.

Why multiple tables? Redundancy & anomalies

If you cram everything into one table you get data redundancy — the same fact stored many times. That's bad for two reasons:

• Integrity — if the fact changes you must update every copy; miss one and the data is inconsistent.
• Efficiency — wasted storage and slower entry.

Redundancy causes three anomalies (memorise these — common MCQ):

The three-level ANSI-SPARC architecture

Databases are described at three levels so storage can change without breaking the programs that use it:

Data independence is the payoff: you can change one level without affecting the others (logical & physical independence).

Why this is worth examining: a real database might move to faster disks, add an index, or split a table for performance. Because programs only talk to the conceptual/external levels, none of that breaks the apps. Without the three-level split, every storage tweak would mean rewriting every program.

2 Legal, ethical & Indigenous data issues

Ethics = a set of principles of correct conduct / a system of moral values (Connolly & Begg). As Potter Stewart put it: "knowing the difference between what you have the right to do and what is right to do."

Legal vs ethical: they're not the same. Laws lag behind technology, so ethics fills the gap. Not all unethical behaviour is illegal, and not all legal behaviour is ethical.

Why organisations care (compliance)

Two ethical concern areas

• Data collection — what/how you collect; secretive or disingenuous practices (vague "Terms & Conditions", the 'Herod clause'); re-identifiability of "anonymised" data.
• Data usage — how collected data is used; grey areas like surge pricing from personal data, reusing data collected unethically, "enhanced service" vs "creepy".

Professional codes you can cite: ACM, ACS (Australian Computer Society), BCS (British Computer Society).

Indigenous data & the CARE principles

Indigenous Data Sovereignty (IDS) = the right of Indigenous peoples to govern the collection, ownership and application of data about their communities. Indigenous Data Governance (IDG) is how that data is managed to support IDS. The unit's exam ethics question is almost always: "does this scenario comply with [a letter] of CARE?"

CARE (GIDA 2019) — learn all four:

CARE complements FAIR (Findable, Accessible, Interoperable, Reusable).

Securing data (the security toolkit)

• Views — a view is a stored/named query ("virtual table"). Grant a user SELECT on a view that exposes only safe columns → hides sensitive columns and the base table. The main security mechanism examined.
• Backup vs recovery — backup = periodic copy of the DB and log file offline; recovery = replace a damaged DB with a backup. MySQL: Hot/Logical (server stays online, mysqldump) vs Cold/Physical (server offline, faster file copy).
• Journaling — a log of all changes; minor failure → auto-recover from the journal, major failure → restore from backup.
• Auditing (Oracle) — statement, privilege, object auditing.
• Encryption — a cipher makes data unreadable without the key. Public/asymmetric (encrypt with public key, decrypt with private) vs Private/symmetric (same key both ways).
• RAID — Redundant Array of Independent Disks → fault tolerance (disks are the most failure-prone component).

3 Object-Role Modelling (ORM)

ORM models the conceptual schema as objects playing roles. You start with plain-English facts and end with a diagram, following the 7-step CSDP (Conceptual Schema Design Procedure).

Elementary facts (CSDP Step 1)

An elementary fact is a simple, atomic assertion that particular objects play particular roles — it can't be split into smaller facts about the same objects.

Objects: entities vs values

• Value — a self-identifying constant (e.g. "Australia", 17). Drawn as a dashed rectangle.
• Entity — needs a definite description. Drawn as a solid rounded rectangle.

How to turn a messy sentence into elementary facts — split on every "and", remove every quantifier, and name the objects with their reference modes:

Fact types & reference modes

An entity is identified by three things: entity type + reference mode + value — e.g. Employee (.ID) 'E0002'. (In "The Lecturer with StaffID 'E22'…", StaffID is the reference mode — a classic MCQ.)

A predicate is a sentence template with 'object holes'; its arity = the number of roles: unary (1), binary (2), ternary (3), quaternary (4). A fact type = object type(s) + a predicate, e.g. Employee works in Department.

Surface vs deep structure: "Mary works in Sales" and "Sales employs Mary" look different but are the same fact type (same deep structure).

Diagram notation — how to read & draw it

Constraints & reading multiplicity

A uniqueness constraint (UC) bar means the values under that role (or combination) are unique. Read multiplicity from where the bar sits on a binary:

A mandatory-role dot means every instance of that object type must play that role (no NULLs there). A "?" in a population = NULL = optional role.

The n−1 rule & legal diagrams

• n−1 rule: in an n-ary fact type, a uniqueness constraint must span at least n−1 roles.
• Legal-diagram rules: objects must play a role (you can't join two object types directly); each role connects to exactly one object type (no role played by two types; no dangling role).

The 7-step CSDP

The procedure that turns facts into a diagram. Steps 6–7 are not examined, but know the first five cold.

1. Transform familiar examples into elementary facts.
2. Draw the fact types and apply a population check (populate with sample instances).
3. Check for entity types to be combined; note arithmetic derivations. (Combine when the same entity belongs to two types — e.g. Stephen Chow is actor and director → one Person type.)
4. Add uniqueness constraints and check arity.
5. Add mandatory role constraints and check for logical derivations.
6. Add value, set-comparison, subtyping constraints. not examined
7. Add other constraints; final checks. not examined

4 Relational mapping (ORM → tables)

RMap converts the ORM diagram into table definitions in Optimal Normal Form (a minimal set of normalised relations). First you must know keys precisely.

Three inherent integrity constraints: Entity (PK unique & not null), Referential (a FK must match an existing row; deletion rules: cascade, restrict, set null, set default), Domain (values fit a data type).

RMap syntax — write it exactly like this

Student {studentID, name, email, [phone]}
Studies {studentID, unitID}      FK studentID → Student,  FK unitID → Unit
Unit    {unitID, title}

The four mapping rules (apply in strict order 1→2→3→4)

Rule 4 decision tree (1:1)

1. Does either object play another functional role? No → 4.3: the 1:1 fact gets its own table.
2. Only one does → 4.1: group the fact onto that object's table.
3. Both do → is exactly one role mandatory? Yes → 4.2: group onto the mandatory side. Neither/both → 4.4: your discretion, minimise NULLs.

Member     {memberID, name, [email]}
Class      {classID, className, trainerID}   → trainerID FK → Trainer
Trainer    {trainerID, trainerName}
EnrolledIn {memberID, classID}              → FKs to Member, Class   (Rule 1)

5 Normalisation

Normalisation improves integrity by minimising redundancy — every non-key attribute fully depends on the whole key, and each fact is stored once. It's driven by functional dependencies.

Functional dependencies (FDs)

X → Y ("X determines Y") means: for each value of X there is at most one value of Y. X is the determinant, Y the determinee. (It does not imply causation — just identification.) A key determines the non-key attributes.

How to read them off a table: if every time two rows share the same X value they also share the same Y value, then X → Y. This is the test you apply when hunting for partial and transitive dependencies in the next section.

The normal forms — 1NF → 2NF → 3NF

Student   {StudentID, StudentName}
Unit      {UnitCode, UnitName}
Tutor     {TutorID, TutorName}
Enrolment {StudentID, UnitCode, Grade, TutorID}   → FKs to Student, Unit, Tutor

The routine to follow every time (using the ENROLMENT example above):

1. Write the key and the FDs. PK = (StudentID, UnitCode). FDs: StudentID→StudentName; UnitCode→UnitName; (StudentID,UnitCode)→Grade,TutorID; TutorID→TutorName.
2. 1NF — atomic? Every cell holds one value and there's a key → already 1NF.
3. 2NF — any partial deps? The key is composite, so check each non-key against part of the key. StudentName depends on StudentID alone, UnitName on UnitCode alone → partial. Move each out: Student{StudentID, StudentName}, Unit{UnitCode, UnitName}.
4. 3NF — any transitive deps? In what's left, TutorID→TutorName is non-key→non-key → transitive. Move it out: Tutor{TutorID, TutorName}.
5. Write the final set and add the foreign keys (shown in the box above). Re-check: every non-key now depends on the whole key of its table, and nothing else. Done.

6 SQL

SQL has two sub-languages here: DDL (define structure) and DML (manipulate data), plus DCL (permissions). Data integrity is enforced by constraints.

DDL — creating tables

CREATE TABLE Ticket (
  ticketID    INT PRIMARY KEY AUTO_INCREMENT,
  eventID     INT NOT NULL,
  customerID  INT NOT NULL,
  ticketType  ENUM('GA','VIP','Backstage'),
  price       DECIMAL(6,2),
  FOREIGN KEY (eventID)    REFERENCES Event(eventID),
  FOREIGN KEY (customerID) REFERENCES Customer(customerID)
);

• NOT NULL = value required; UNIQUE = a candidate key (no duplicates).
• Restrict values with ENUM(...) or a CHECK constraint.
• ALTER TABLE t ADD COLUMN col TYPE; changes structure. DROP TABLE removes the whole table; DELETE removes rows only.

DML — insert / update / delete

INSERT INTO Ticket (eventID, customerID, ticketType, price) VALUES (2, 3, 'GA', 45);
UPDATE Ticket SET price = 50 WHERE ticketID = 4;     -- omit WHERE → every row!
DELETE FROM Ticket WHERE ticketID = 4;

SELECT & filtering

SELECT firstName, lastName FROM Customer
WHERE city = 'Brisbane'
ORDER BY lastName DESC;

• Pattern matching with LIKE: % = any number of characters, _ = exactly one. So 'a%'=starts with a, '_a%'=2nd char is a, '%a'=ends with a.
• ORDER BY is ascending by default; add DESC to reverse.
• DISTINCT removes duplicate rows.

So "the last names of Brisbane customers, A→Z" becomes: from Customer, where city = 'Brisbane', select lastName, order by lastName. Try it in the live sandbox below and tweak the city.

Grouping & aggregates

Aggregates: COUNT, SUM, AVG, MIN, MAX. GROUP BY collapses rows into groups so an aggregate is computed per group.

SELECT e.eventName, COUNT(*) AS ticketsSold
FROM Event e JOIN Ticket t ON e.eventID = t.eventID
GROUP BY e.eventID, e.eventName
HAVING COUNT(*) > 2;

SQL actually evaluates a query in this order, which explains a lot of "why won't this work" errors: FROM/JOIN → WHERE → GROUP BY → aggregate → HAVING → SELECT → ORDER BY.

Set operations: UNION (combine, drop duplicates), UNION ALL (keep duplicates), INTERSECT (in both), EXCEPT (in the first but not the second).

Joins & subqueries

SELECT c.firstName, c.lastName
FROM Customer c JOIN Ticket t ON c.customerID = t.customerID
WHERE t.ticketType = 'VIP';

Writing a join in three moves:

1. Which tables hold the columns I need? Want customer names + ticket types → Customer and Ticket.
2. What's the shared key? customerID → that's the ON condition: ON c.customerID = t.customerID.
3. Filter and pick columns. Add the WHERE and the SELECT list. Use table aliases (c, t) to keep it short.

A subquery is a query nested in another; the inner runs first, then the outer uses its answer. Use = when the inner returns one value, IN when it returns many.

SELECT eventName FROM Event
WHERE venueID = (SELECT venueID FROM Venue WHERE venueName = 'The Triffid');

Read it inside-out: the inner query finds The Triffid's venueID first; the outer query then lists events with that venueID. Open the live sandbox below and run it — then change the venue name and re-run.

DCL — permissions

GRANT SELECT, INSERT ON Orders TO clerk_role;
REVOKE INSERT ON Orders FROM clerk_role;

7 Security & administration

A role is a named bundle of privileges granted to users (simplifies admin).

• DAC (Discretionary Access Control) — the owner grants privileges directly.
• RBAC (Role-Based Access Control) — privileges are assigned via roles.

CREATE ROLE clerk_role;
GRANT SELECT, INSERT ON Orders TO clerk_role;
GRANT clerk_role TO user;

Transactions (ACID)

A transaction is a logical unit of work — a sequence of operations treated as one all-or-nothing action. It must be ACID:

In SQL you'd wrap the two updates between a start and a COMMIT; if anything goes wrong you ROLLBACK and it's as if nothing happened. A SAVEPOINT lets you undo just part of a long transaction.

TCL commands: COMMIT (save), ROLLBACK (undo), SAVEPOINT (partial-rollback marker).

8 Exam technique

It's open book (unlimited paper notes, no digital), 2 hours, on-campus and handwritten, covering the whole semester. The lecturer warns: be selective — don't waste time flicking through too many pages.

• Section A (MCQ): do these first, fast — they're 1 mark each. Don't overthink.
• Section B (short answer): writing SQL, drawing ORM, doing RMaps, normalising, ethics. Show working — partial credit is real.
• ORM: always start from elementary facts, then add UC bars, then mandatory dots. Reject any "fact" with and/or/not/if/all/some.
• RMap: apply rules 1→2→3→4 in order; underline PKs, bracket optional columns, point FKs at PKs.
• Normalisation: name the partial deps (→2NF) and transitive deps (→3NF) explicitly, then give the split tables.
• SQL "will it run?": check entity integrity (duplicate PK/unique) and referential integrity (FK with no parent).
• Ethics: for CARE, define the letter → cite the facts → conclude.