Act II · one engine, one truth

The same hospital, rebuilt on hospital_db

Departments no longer own data — they connect to it. Every failure you triggered in Act I is now something the engine simply does not allow.

Solution 01
Reduced redundancy

Six copies collapse into one row

Toggle between the two designs and compare the storage cost. The same fix that removes Problem 01.

Storage for 12,480 patients

Same hospital, same people, different design.

6 copies
Stored per patient
3594 MB
Total patient storage
6 places
Places to update a change
Six departments × six copies. Storage grows six times faster than the hospital does.
Solution 02
Data consistency

Update once, every department sees it

The write that broke five files earlier now reaches all six views instantly.

One record. Six readers.

UPDATE Patients SET phone = ? WHERE PatientID = 101;

Reception

9999999999

reads Patients.phone

Doctor

9999999999

reads Patients.phone

Laboratory

9999999999

reads Patients.phone

Pharmacy

9999999999

reads Patients.phone

Billing

9999999999

reads Patients.phone

Administration

9999999999

reads Patients.phone

hospital_db

Patients(101, John Mathew, 9999999999)

Solution 03
Centralized data

One patient's history lives in one place

The six unrelated files from Problem 03 now answer to a single query.

John's whole history, one query

All departments read the same centralized store — nothing to open one at a time.

Reception

Visit Date

12 Mar 2026

Doctor

Diagnosis

Angina, follow-up in 2 weeks

Laboratory

Test

Lipid Profile — ready

Pharmacy

Medicine

Atorvastatin 10mg

Billing

Amount

₹4,200 (unpaid)

Administration

Insurance

MediCare — POL/8891

hospital_db

SELECT * FROM Patients JOIN Visits…

Solution 04
Fast searching

Ask anything, in one statement

The Cardiology question that needed a manual scan in Problem 04 is now one SQL query.

hospital_db · query console
The same question that took a clerk six manual file scans is now one declarative statement — indexed, optimised and answered in under a millisecond.
Solution 05
Concurrency

Two writers, both changes kept

Commit from both sides. Compare with Problem 05.

Same patient, two writers

Commit both. Nothing is lost this time.

Reception · address

Doctor · phone

Patients row 101

Address: 14 Rose Lane, Pune

Phone: 9999999999

ACID transactions and row-level locking let hundreds of users write at once. Each change touches only the columns it owns, and the engine serialises anything that truly conflicts.
Solution 06
Security

Permissions enforced by the database

Switch roles and watch restricted actions become unavailable — Problem 06 fixed.

GRANT and REVOKE, per role

Permissions live in the database, not in an honour system.

View patient demographics

Edit demographics

Open clinical notes

View billing & payments

Manage users & roles

As Intern, the engine refuses unauthorised statements before they touch data — and records every allowed one. An intern can no longer read clinical notes, whatever tool they connect with.
Solution 07
Backup & recovery

Deleting everything is now reversible

Take a snapshot, wipe the table, restore it. The delete from Problem 07 is no longer fatal.

Patients table · 12480 rows

Snapshot, break it, roll it back.

SELECT COUNT(*) FROM Patients; → 12480

No recovery point yet.

A DBMS keeps write-ahead logs and scheduled snapshots, so recovery is a command rather than a crisis. The same accidental delete that destroyed patient.txt is a 20-second rollback here.
Solution 08
Relationships

Keys connect patients, appointments, doctors and bills

Select a patient and every related record follows automatically — the join from Problem 08 is now built in.

Pick a patient, follow the keys

One JOIN replaces the clerk's mental map.

Patients

PK patient_id

101 · John Mathew

Appointments

FK patient_id → doctor_id

9001 · 12 Mar · 10:30

9005 · 26 Mar · 10:30

Doctors

PK doctor_id

Dr. Iyer · Cardiology

Bills

FK patient_id

5001 · ₹4200 · Unpaid

5006 · ₹900 · Paid

SELECT p.name, d.name, a.slot, b.amount
FROM Patients p JOIN Appointments a ON a.patient_id = p.patient_id
JOIN Doctors d ON d.doctor_id = a.doctor_id
LEFT JOIN Bills b ON b.patient_id = p.patient_id WHERE p.patient_id = 101;
Foreign keys make the relationship part of the data. The database refuses an appointment for a patient who doesn't exist — integrity is enforced, not hoped for.
See them side by side Where this is used today