IFB105Database Management

IFB105 Database Management — Complete Study Guide

Built from your course materials. Written to teach you from scratch. Read it top to bottom once, then use the cheat sheets for fast revision.

The exam is open book. You can bring unlimited paper notes (no digital). It is 2 hours, a mix of multiple choice (Section A, 15 × 1 mark, on a bubble sheet in pen) and short answer (Section B — writing SQL, drawing ORM, doing RMaps and normalisation, in the boxes on the paper). It covers the whole semester. Steps 6–7 of the CSDP are not examined.

PART 0 — The big picture (how the course fits together)

A database stores facts about the real world. This unit teaches you the full pipeline for designing one properly:

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

Keep that order in your head. Most exam questions are "do step X" or "spot the mistake in step X".

PART 1 — Databases and the relational model

What a database is

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).

Why use multiple tables? (Redundancy)

If you cram everything into one table you get data redundancy — the same data stored many times. Example: storing a doctor's name next to every appointment means the name is repeated on every row.

Redundancy is bad for two reasons:

The guiding principle: each row is an elementary fact, and we do not repeat elementary facts. Splitting data across linked tables fixes this.

Data anomalies (what redundancy causes)

Normalisation (Part 6) exists to eliminate these.

Three-level ANSI-SPARC architecture

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

Data independence is the payoff:

Information systems modelling

A conceptual model must be powerful enough to express the universe of discourse (UoD) — the slice of the real world you're modelling — and must come with a procedure to build and validate the model. That procedure is the CSDP (Part 4).

This is heavily examinable as short-answer (the practice exam's biggest written question, Q16, is entirely CARE principles). Learn the four CARE letters cold.

Why organisations care (compliance)

Key ethical concern areas

Securing data (the security toolkit)

Indigenous data

CARE principles (GIDA 2019) — MEMORISE

Use these to assess a scenario (the exam asks "does this comply with C/A/R/E?" — answer yes/no and justify).

CARE complements FAIR (Findable, Accessible, Interoperable, Reusable) — FAIR is about making data usable, CARE is about using it ethically/responsibly.

Worked example (the AIBS scenario from the practice exam):

PART 3 — Object-Role Modelling (ORM) & elementary facts

ORM is how IFB105 models the conceptual schema. It's built on elementary facts.

Elementary facts

An elementary fact is a simple, atomic assertion that is taken to be true, stating that particular objects play particular roles. Test for "elementary":

Standard verbalisation format:

The EntityType with ReferenceMode "value" ... [plays role] ... the EntityType with ReferenceMode "value".

Example: The Employee with EmployeeID "E1235" has the Name "Elizabeth Smith".

Spotting non-elementary facts (exam favourite):

Objects: entities vs values

In "Student N2345 received a Grade of 5 in Unit ABC123": Grade is a value type object (no reference mode); Student/Unit are entity types; StudentID is a reference mode; "ABC123" is a data value (a specific data point).

Entity types and reference modes

Fact types & semantic equivalence (Step 2 detail)

Step 3 detail — combining entity types & subtypes

Four questions to ask in Step 3: (Q1) Can the same entity belong to two entity types? (Q2) Can entities of two types be meaningfully compared? (Q3) Is the same info recorded for different entity types (might we list them together)? (Q4) Is a fact type arithmetically derivable?

NULLs and the mandatory dot (Step 5 detail)

Counting objects in a fact (exam Q17 style)

Count the distinct objects (entities + value types), not the values.

Arity (how many roles in a fact type)

Associations (multiplicity) — read these from the diagram

ORM diagram notation (how to read/draw)

Reading multiplicity from uniqueness bars

So for the practice-exam hospital diagram: a single UC arrangement giving Patient↔Bed a 1:1 means "a patient is assigned to exactly one bed and each bed has one patient"; an m:n between Patient and Clinician means "a patient can be seen by many clinicians and a clinician sees many patients."

The CSDP — Conceptual Schema Design Procedure (7 steps)

You only need Steps 1–5 for the exam (6–7 are excluded).

  1. Transform familiar examples into elementary facts. Take sample data / English statements and write atomic facts in the standard format.
  2. Draw the fact types and apply a population check. Draw each fact type (objects + roles) and fill it with sample data to confirm it works.
  3. Check for entity types to be combined, and note arithmetic derivations. Merge duplicate entity types; mark any derived values (e.g. a value computed from others — don't store it).
  4. Add uniqueness constraints and check the arity of fact types. Decide which roles are unique. The n–1 rule: in a fact type with n roles, a uniqueness constraint must span at least n–1 roles (you can't have a UC on fewer). A binary (n=2) UC must cover at least 1 role; if it covers 0 or only a partial pattern that breaks this, the diagram is wrong. (This is why Q5's option B is the incorrect diagram.)
  5. Add mandatory role constraints and check for logical derivations. Mark which roles every instance must play (the ● dot).
  6. (Not examined) Add value, set-comparison and subtyping constraints.
  7. (Not examined) Add other constraints and final checks.

Constraints you must recognise

PART 4 — Relational Mapping (RMap)

RMap turns an ORM conceptual schema into a set of relational table definitions in Optimal Normal Form (minimum number of normalised relations). You apply four rules in strict order.

RMap syntax (write it exactly like this)

TableName {column1, column2, [column3], column4}

Keys (learn the definitions precisely)

Useful truths:

The four mapping rules

Rule 1 — Many-to-many and ternary fact types → their own table. The new table's PK is the combination of the participating object identifiers. e.g. beingSeenBy {patientID, clinicianEmpID} (both underlined together), integratedDevice {bedID, deviceID}.

Rule 2 — One-to-many (and unary, and 1:1 grouped here) fact types → group onto the "one" side's entity table. Each entity type becomes a table keyed on its identifier; its functional (single-valued) attributes become columns. e.g. patient {patientID, patientName}, clinician {empID, clinicianName, medicalSpecialty}, bed {bedID, wardName}.

Rule 3 — Nested fact types (objectified relationships). If a fact type is objectified (treated as an object that itself plays roles), map it to a table whose PK is the underlying fact type's key, plus its own attributes. e.g. Installation {bedID, assetID, installationDate} (PK = bedID + assetID). If there's no nesting, Rule 3 does not apply.

Rule 4 — One-to-one fact types → put the fact in only ONE relation. Which one depends on functional roles and mandatory constraints. Four sub-rules:

Foreign keys: after applying the rules, add FK arrows wherever a column in one table references the PK of another (e.g. patient.bedID → bed.bedID; installation.assetID → device.assetID).

Worked example (practice-exam Q20 → answer)

ORM: Device tracked-by RFID (1:1), manufactured-by Manufacturer (n:1), has Technician Notes (optional), and a nested "installation" (Bed has integrated Device) that was installed-on a Date and requires-maintenance-on a Date where maintenanceDate = installationDate + 1 year (derived).

The lesson worth memorising: derived/computed values are NOT stored (note them in Step 3, leave them out of the tables).

PART 5 — Normalisation

Normalisation improves data integrity by removing redundancy so that each non-key attribute is fully dependent on the entire key, a fact is stored only once, and insert/update/delete anomalies disappear.

Functional dependencies (FDs)

For a relation, Y is functionally dependent on X (written X → Y) if for each value of X there is at most one value of Y.

Dependency types (precise lecture definitions)

Course scope: IFB105 normalises only up to 3NF. BCNF is not taught; 4NF/5NF appear only on a diagram and are not examined.

Identifying keys (method)

(Q12 worked: with PatientID repeating and (DoctorID, WardName) repeating, only (PatientID, WardName) is unique and irreducible — DoctorID can be dropped — so it's the candidate key.)

The normal forms

0NF (unnormalised): has repeating groups / non-atomic cells / no proper key.

1NF — First Normal Form. A table is in 1NF when:

2NF — Second Normal Form. In 1NF AND no partial dependencies (no non-key attribute depends on only part of a composite key).

3NF — Third Normal Form. In 2NF AND all non-key attributes are mutually independent (no transitive dependencies — no non-key attribute determines another non-key attribute).

Rule of thumb to find "highest normal form" (Q9 style):

  1. Atomic + has key? If not → 0NF. If yes → at least 1NF.
  2. Composite key with a partial dependency? → stuck at 1NF.
  3. A non-key → non-key dependency? → stuck at 2NF.
  4. Neither problem → 3NF.

(Q9 worked: the StoreID/ProductID table is 1NF; ManagerID & ManagerName depend on StoreID alone (part of the composite key) → partial dependency → highest form is 1NF.)

How to normalise to 3NF (the procedure)

  1. Confirm 1NF (atomic values, a key). If repeating groups exist, split them out.
  2. Remove partial dependencies (→ 2NF): for each part of the key that determines some attributes, pull those attributes into a new table keyed on that part.
  3. Remove transitive dependencies (→ 3NF): if a non-key attribute determines another non-key attribute, move that pair into its own table keyed on the determinant.
  4. Write it in RMap format with PKs underlined and foreign keys added back to link the tables.

Worked example (practice-exam Q21): Original PK = (ProviderID, AppointmentTime).

3NF result:

Provider {providerID, providerName, role}
Appointment {providerID, appointmentTime, patientID}
        FK providerID → Provider.providerID
        FK patientID  → Patient.patientID
Patient {patientID, patientName}

(Underline the PKs; the composite key in Appointment is providerID + appointmentTime.)

Another (Q10): A book can have multiple authors → a Book–Author m:n needs an Authorship bridge table; LoanedTo is optional ([LoanedTo]):

Book {BookID, BookTitle, [LoanedTo]}
Author {AuthorID, AuthorName}
Authorship {BookID, AuthorID}

PART 6 — SQL

SQL has sub-languages: DDL (define structure), DML (manipulate data), DQL (SELECT), DCL (permissions).

Data types & integrity (DDL building blocks)

DDL — CREATE / ALTER / DROP

CREATE TABLE Ticket (
    ticketID INT PRIMARY KEY AUTO_INCREMENT,
    eventID  INT NOT NULL,
    orderID  INT NOT NULL,
    category ENUM('Child','Adult','VIP','Concession'),
    seatNo   VARCHAR(15),
    FOREIGN KEY (orderID) REFERENCES Orders(orderID),
    FOREIGN KEY (eventID) REFERENCES Event(eventID)
);

This is exactly the practice-exam Q22 answer: A=PRIMARY KEY, B/C=NOT NULL, D=ENUM, E/G=FOREIGN KEY, F/H=REFERENCES.

DML — INSERT / UPDATE / DELETE

INSERT INTO Customers VALUES ('CU1','Name','Address','Phone');
INSERT INTO Customers (CustomerID, CustomerName) VALUES ('CU2','Name');

UPDATE Customers
SET Address = '19 Albert Street'
WHERE CustomerID = 'CU091214';      -- correct UPDATE form (Q15 answer = C)

DELETE FROM Customers WHERE CustomerID = 'CU2';

SELECT — the core

General syntax (clause order cannot change; only SELECT and FROM are mandatory):

SELECT [DISTINCT | ALL] {* | columnExpression [AS newName] [,...]}
FROM TableName [alias] [,...]
WHERE condition
GROUP BY columnList
HAVING condition
ORDER BY columnList [ASC|DESC];

Pattern matching

Ordering

Aggregate functions

COUNT(*), COUNT(col), SUM(col), AVG(col), MIN(col), MAX(col).

GROUP BY / HAVING

SELECT customerID, SUM(totalPrice)
FROM Orders
GROUP BY customerID
HAVING SUM(totalPrice) > 100;

Set operations

Combine results of two SELECTs (same columns):

Subqueries (nested queries)

A query inside another. Solve inside-out.

SELECT firstName, lastName, email FROM Customer
WHERE customerID IN (
    SELECT customerID FROM Orders
    GROUP BY customerID
    HAVING SUM(totalPrice) > 100
);

This is the Q24 answer: find each customer's total spend with GROUP BY + SUM + HAVING, then return their details.

Joins

Combine columns from multiple tables on a matching condition.

SELECT c.firstName, o.totalPrice
FROM Customer c
INNER JOIN Orders o ON c.customerID = o.customerID;

Reading query results (Q25 skill)

Work strictly inside-out, track the intermediate sets, and write the output exactly as MySQL would (right capitalisation/punctuation). Examples from the practice exam:

DCL — permissions (Data Control Language)

DCL grants/revokes privileges so users can only do their job (least privilege).

GRANT {privilegeList | ALL PRIVILEGES} ON objectName
TO {userList | PUBLIC} [WITH GRANT OPTION];

REVOKE {privilegeList | ALL PRIVILEGES} ON objectName FROM userList;

PART 7 — Database security & administration

Roles

A role = a named collection of privileges that can be assigned to users (or to another role). Roles simplify maintenance: grant privileges to a role once, then assign the role to many users.

CREATE ROLE 'order_payment_role', 'inventory_role';
GRANT SELECT, INSERT, UPDATE, DELETE ON customer TO order_payment_role;
-- then assign the role to users

Access control

Database users

The DBA creates users, assigns them roles/privileges, and manages authentication. Principle of least privilege: give each user only what they need.

Transactions

A transaction = a logical unit of work: a series of actions by one user/program that reads or updates the database, which must succeed or fail together.

Why we need them — concurrency problems (when transactions run at once):

ACID properties:

Transaction Control Language (TCL):

PART 8 — Exam technique (open-book, 2 hours)

End of study guide. Pair this with the cheat sheets (fast lookup), the worked solutions (see the reasoning), and the extra practice questions (test yourself).

APPENDIX — Verified-from-Lectures Additions

These points were confirmed by reading every lecture slide visually (page by page). They extend, and are consistent with, the main guide above.

Security (Week 1 — "Securing Data") — extra detail for the security exam questions

Ethics — the Havasupai CARE worked example (this is the exact style of the exam ethics question)

Case: In 1989 the Havasupai Tribe gave blood to ASU consenting only to diabetes study; researchers later reused it for mental-illness, alcoholism and migration studies without further consent.

Assess against each CARE letter (the answer for all four is does NOT comply):

Exam tip: for any "does this comply with [letter] of CARE?" question — (1) state what that letter means, (2) point to the specific facts, (3) conclude comply / does not comply.

ORM — extra confirmations

RMapping — Rule 4 decision tree (1:1 fact types)

  1. Does either object play another functional role?
    • No → Rule 4.3: map the 1:1 fact to its own table (PK = the UC).
    • Yes, only one → Rule 4.1: group the fact onto the table of the object with the functional role.
    • Yes, both → check mandatory:
      • One is explicitly mandatory → Rule 4.2: group onto the mandatory side.
      • Neither / both mandatory → Rule 4.4: your discretion — choose the mapping that minimises NULLs.

Order of all rules is strict: Rule 1 (UC spanning >1 role → m:n & ternary tables) → Rule 2 (simple UC / functional role → entity tables) → Rule 3 (nested fact types) → Rule 4 (1:1).