I85PREP
⌘ K
Ruankao System Analyst (Senior) study materials
Ruankao System Analyst (Senior) study resources

Ruankao System Analyst (Senior): Data Management and Database Systems

Ruankao System Analyst (Senior) · Work through your exam, one topic at a time.

All guide sections

Data Management and Database Systems

Domain 5 covers the relational model, normalization and transaction theory, plus the analytical side: indexing, distribution, warehousing and NoSQL. Case questions often give a relation with functional dependencies and ask for the normal form and a lossless decomposition.

Model and normalization

  • Keys: superkey, candidate key, primary key, alternate key, foreign key. Entity integrity forbids null primary keys; referential integrity requires foreign keys to match an existing key or be null.
  • 1NF: atomic attributes. 2NF: no non-key attribute depends on part of a composite key. 3NF: no transitive dependency of a non-key attribute on a key. BCNF: every determinant is a candidate key.
  • Mapping ER to relations: 1:N puts the foreign key on the many side; M:N needs a bridge relation keyed by the pair of entity keys; multivalued attributes get their own relation.

Transactions and concurrency

  • ACID: atomicity, consistency, isolation, durability.
  • Anomalies: dirty read (reading uncommitted data), non-repeatable read (a re-read changes), phantom (a re-executed query returns new rows).
  • Isolation levels: Read Uncommitted, Read Committed, Repeatable Read, Serializable; each stricter level prevents more anomalies at the price of concurrency.
  • Two-phase locking guarantees serializability but can deadlock; timestamps and optimistic validation avoid blocking but may abort.

Physical and analytical

  • B+ tree indexes keep all keys in linked leaves, which suits range queries; hash indexes suit equality on high-cardinality columns; bitmap indexes suit low-cardinality warehouse columns.
  • Fragmentation is horizontal (rows) or vertical (columns); distributed transparency covers location, fragmentation and replication; two-phase commit coordinates atomic distributed transactions.
  • Star schema: one fact table with measures and foreign keys plus denormalised dimensions; snowflake normalises dimensions. OLAP operations: roll-up, drill-down, slice, dice, pivot.
  • CAP states that under a partition a system chooses consistency or availability; BASE is the availability-leaning alternative to ACID.

Worked example

Enrolment(StudentID, CourseID, StudentName, CourseName, Grade) with key (StudentID, CourseID). StudentName depends on StudentID alone, a partial dependency, so the relation is not in 2NF. Decompose into Student(StudentID, StudentName), Course(CourseID, CourseName) and Enrolment(StudentID, CourseID, Grade). Each non-key attribute now depends on the whole key and nothing else, giving 3NF and a lossless join on the shared keys.

Traps

  • Claiming 3NF implies BCNF; a non-key determinant breaks BCNF but not 3NF.
  • Confusing a phantom read with a non-repeatable read.
  • Merging an M:N relationship into one entity relation and losing rows.
  • Treating star-schema denormalisation as an error rather than a deliberate performance choice.