Question 1
15 marks
Relational Modeling, Electronic Health Records, Transaction States, and Crash Recovery
- Databases
(a)
3 marks
- Identify primary entities: `Clinic`, `Dentist`, and `Nurse`.
- Define multiplicities with Clinic: A single clinic employs many dentists (1-to-many relationship from `Clinic` to `Dentist`), and the same clinic employs many nurses (1-to-many relationship from `Clinic` to `Nurse`).
- Define the relationship between Dentist and Nurse: Because a dentist collaborates with multiple nurses and a nurse assists multiple dentists, their direct association is many-to-many (M:N). In a normalized relational implementation, this M:N relationship is resolved through an associative link table (such as `DutyAssignment` or `Appointment`) having two 1-to-many relationships pointing from `Dentist` and `Nurse`.
Mentor tip
Explicitly draw or state the entities with crow's foot notation or an associative intersection table to resolve many-to-many.
(b)
6 marks
- Advantage 1 (Concurrent Multi-User Access and Mobility): Storing patient records in a centralized relational database allows dentists, dental hygienists, and reception staff to retrieve and update patient medical charts simultaneously across multiple clinical terminals. Unlike physical paper files that can only exist in one room at a time, digital records facilitate seamless cross-departmental coordination.
- Advantage 2 (Automated Data Validation, Integrity, and Redundancy Prevention): Database management systems enforce domain constraints, required foreign keys, and standardized picklists during input. This eliminates handwriting ambiguity, prevents contradictory records, and drastically reduces clerical errors compared to paper charting.
- Advantage 3 (Disaster Recovery and Longitudinal Data Mining): Electronic databases support automated off-site backups and replication, safeguarding patient histories against physical perils like fire or water damage. Furthermore, digitized records enable structured SQL analytics to monitor patient treatment progressions and track clinic-wide outcomes.
Mentor tip
Provide two comprehensive explanations, detailing the operational mechanism and its clinical benefit.
(c)
3 marks
- Step 1 (Active State): The transaction begins execution, performing read and write operations in main memory buffers as patient billing data is updated.
- Step 2 (Partially Committed State): Once the final database statement executes, the transaction enters the partially committed state, where updates reside in volatile memory awaiting physical persistence.
- Step 3 (Committed or Aborted State): If all write operations and integrity constraints flush successfully to non-volatile disk storage, the transaction transitions to the Committed state. If a hardware fault or constraint violation occurs before completion, it transitions to the Failed state and is rolled back to the Aborted state, restoring data consistency.
Mentor tip
Trace the lifecycle: Active -> Partially Committed -> Committed (or Failed -> Aborted).
(d)
3 marks
- Step 1 (Log Scan and Checkpoint Identification): Following a system failure, the recovery manager scans backward through the write-ahead transaction log to identify the most recent consistent checkpoint record.
- Step 2 (Redo Phase): Scanning forward from the checkpoint, the recovery manager re-executes all operations for transactions that logged a successful `COMMIT` record prior to the failure, ensuring the ACID durability property.
- Step 3 (Undo Phase): The recovery manager identifies all active transactions that were still uncommitted at the moment of the crash and reverses their operations in reverse chronological order using logged 'before-values', restoring full atomicity.
Mentor tip
Highlight the checkpoint reference, the REDO step for committed operations, and the UNDO step for uncommitted transactions.

