Module 15 · Data modelling · Drill

Data modelling drill

Scenario prompts that check whether data modelling is usable under pressure. Answer out loud, then compare.

4 prompts25 minNew

0 of 4 graded

Prompt 1
Design the ER model and then the tables for a hospital: patients, doctors with specialisations, departments, appointments (a patient sees a doctor at a time, with a diagnosis), and prescriptions listing several medicines with dosages per appointment. State every cardinality, participation and key, and say which tables come from relationships.
Say your answer out loud first. Thirty seconds is enough.
Prompt 2
A legacy table Customer(email PRIMARY KEY, name, phone, city) is causing problems: customers change emails, some share a family email, and support needs to look customers up by phone. Propose a new key design with every constraint, and explain the migration without downtime.
Say your answer out loud first. Thirty seconds is enough.
Prompt 3
Explain NULL handling in SQL with three concrete bugs it causes, using the relational model's three-valued logic, and give the safe pattern for each.
Say your answer out loud first. Thirty seconds is enough.
Prompt 4
Map this specialisation hierarchy to tables three ways and recommend one: Vehicle(vehicle_id, registration, owner) with subtypes Car(seats), Truck(max_load) and Bike(engine_cc). Queries are mostly 'all vehicles of an owner', but insurance rules differ per subtype and new subtypes will be added.
Say your answer out loud first. Thirty seconds is enough.