IFB105Database Management

IFB105 — Open-Book Exam Cheat Sheets

Print this. Tab the sections. Designed for fast lookup in the 2-hour open-book exam.

1. ELEMENTARY FACTS & ORM

Elementary fact = atomic, true, no conditions, can't be split.

Object types:

Count objects = count distinct entities + value types (ignore the literal values).

Arity: Unary (1 role, e.g. "Room is vacant") · Binary (2) · Ternary (3).

Associations: 1:1 (each↔one) · 1:n (one prof, many classes) · m:n (students↔subjects).

Diagram notation: solid box = entity; dashed box = value type; box(es) between = roles; bar over role = uniqueness; ● dot = mandatory.

CSDP 7 steps (only 1–5 examined):

  1. Examples → elementary facts.
  2. Draw fact types + population check.
  3. Combine entity types; note derivations (don't store derived values).
  4. Add uniqueness constraints; check arity. n–1 rule: UC spans ≥ (n–1) roles.
  5. Add mandatory roles; check logical derivations. 6–7. (value/set/subtype/other — NOT examined).

Constraint violations (Executes vs XX Violated):

2. KEYS

Key Properties
Super key Unique
Candidate key Unique + Irreducible (can't drop a column)
Primary key Unique + Irreducible + No nulls
Alternate key A candidate key not used as PK
Foreign key Child column matching parent PK → referential integrity

3. RELATIONAL MAPPING (RMAP)

Syntax: Table {col1, col2, [optional], col3}

Apply Rules 1→4 in strict order:

4. NORMALISATION

FD: X → Y = each X has at most one Y. X = determinant, Y = determinee. A key determines all non-key attributes.

Form Requirement
0NF Not atomic / no key / repeating groups
1NF Atomic values + a PK + one type per column
2NF 1NF + no partial dependencies
3NF 2NF + non-key attrs mutually independent (no transitive)

Find highest NF: atomic+key? (else 0NF) → partial dep? (stuck 1NF) → non-key→non-key? (stuck 2NF) → else 3NF.

Normalise to 3NF: 1) ensure atomic+key; 2) pull part-key attributes into own tables (2NF); 3) pull non-key→non-key pairs into own tables keyed on determinant (3NF); 4) write RMap + add FKs.

5. SQL — DDL

CREATE TABLE T (
  id      INT PRIMARY KEY AUTO_INCREMENT,
  name    VARCHAR(50) NOT NULL,
  cat     ENUM('A','B','C'),
  parentID INT,
  FOREIGN KEY (parentID) REFERENCES Parent(id)
);
ALTER TABLE T ADD col VARCHAR(20);     -- structure
ALTER TABLE T MODIFY col INT;
ALTER TABLE T DROP COLUMN col;
DROP TABLE T;                          -- whole table
CREATE VIEW V AS SELECT a,b FROM T;    -- security: hide columns

6. SQL — DML

INSERT INTO T VALUES (...);
INSERT INTO T (c1,c2) VALUES (v1,v2);
UPDATE T SET col = val WHERE cond;     -- ONLY correct update form
DELETE FROM T WHERE cond;

INSERT fails when: duplicate PK · duplicate UNIQUE value (e.g. email) · FK points to a missing parent row. Else Executes.

7. SQL — SELECT (order: FROM→WHERE→GROUP BY→HAVING→SELECT→ORDER BY)

SELECT col, COUNT(*) AS n
FROM   t
WHERE  cond                 -- filters rows
GROUP BY col
HAVING COUNT(*) > 2         -- filters groups
ORDER BY n DESC;            -- ASC default

Pattern matching: LIKE 'A%' (starts A) · '%n' (ends n) · '_a%' (2nd char a). %=any chars, _=one char.

Set ops: UNION (either, no dupes) · UNION ALL (keep dupes) · INTERSECT (both) · EXCEPT (first not second).

Subquery (inside-out):

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

Joins:

SELECT c.name, o.total FROM Customer c
LEFT JOIN Orders o ON c.id = o.customerID;

Reading results: evaluate inner queries first, track sets, write output exactly (capitalisation/punctuation), no column names if not asked.

8. SQL — DCL

GRANT SELECT, INSERT ON t TO user; · REVOKE ... FROM user;

9. SECURITY & ADMIN

10. ETHICS & INDIGENOUS DATA

Ethics = right conduct; fills the gap laws leave; subjective/cultural. Laws: NDB scheme (AU, fines ≤ ~$2.1M) · GDPR (≤ 4% global turnover/€20M; "right to be forgotten") · Privacy Act 1988. Data breach = unauthorised copy/view/steal/use of protected data.

CARE principles (assess: yes/no + justify):

IDS = right to govern data about Indigenous communities. IDG = right to decide what/how/why data is collected/used. BADDR (bad) data: Blaming, Aggregated, Decontextualised, Deficit-focused, Restricted. Needs: narrative, disaggregated, contextualised, Indigenous-priority, available.

AIBS exam pattern: C ✓ (health benefit); A ✗ (no community access/say); R ✗ (relationships not maintained); E ✗ (no consultation on future use).

11. RELATIONAL MODEL / ARCHITECTURE