Project · SQL and Databases
Project: a database for a family clinic
The brief
Sunrise Family Clinic in Lucknow books appointments in one wide spreadsheet. Design a normalised schema with keys and rules, move the old data into it, then add an index, a view for the reception desk, an audit trigger and a weekly doctor report.
Why it matters
Small clinics, diagnostic labs and coaching centres all outgrow their booking sheet the same way: phone numbers typed differently, double bookings, no history of who changed what. Backend developers and database administrators are paid to design this schema and move the data across without losing a row.
Skills you practise
- normalisation to 3NF
- CREATE TABLE with keys and CHECK
- UNIQUE across columns
- INSERT … SELECT
- indexes
- views
- triggers and audit logs
- CTE with RANK
What you will have at the end
A clinic database in four scripts: a normalised schema with keys and rules, a data migration from the old sheet, an index, a view and an audit trigger, and a weekly doctor report. Add a one-page note on the design (tables, keys and why).
How it is marked (rubric, 100 points)
| Correct and completeAll seven checks pass; the migration loses no visit and creates no duplicate patient or doctor. | 35 |
|---|---|
| Schema designEach fact is stored once (3NF), every table has a primary key, and NOT NULL, UNIQUE, CHECK and foreign keys stop bad data and double bookings. | 30 |
| Performance and safety objectsThe index matches a real query, the view hides the joins, and the trigger logs only real changes. | 15 |
| Design noteA short document with the table diagram, the anomalies in the old sheet and how each design choice fixes one. | 20 |
0/7 steps done
Step 1 of 7
Split the sheet: patients and doctors
01-schema.sql creates patients (id, name, phone) with a unique phone, and doctors (id, name, speciality, fee) with a fee above 0.
Look at visits_sheet: Ramesh Gupta's phone appears three times and Dr. Kavita Rao's fee five times. If her fee changes, someone must edit five rows and will miss one (an update anomaly). Normalisation fixes this: store each fact once. Patient details depend only on the patient, doctor details only on the doctor, so each gets its own table (third normal form).
Create patients with id INTEGER PRIMARY KEY, name TEXT NOT NULL and phone TEXT NOT NULL UNIQUE (the phone identifies a patient at the desk). Create doctors with id INTEGER PRIMARY KEY, name, speciality (both TEXT NOT NULL) and fee INTEGER NOT NULL CHECK (fee > 0). The check tries to add a duplicate phone, a patient with no name and a doctor with a fee of 0.
Run shows what your code prints. Check runs the step's tests. Ctrl+Enter checks too.