Database Design Coursework: A Relational Database for a Small Clinic (ER Model and SQL)

1. Introduction

Small healthcare practices increasingly rely on structured data to manage patients, clinicians and day-to-day scheduling. Where a spreadsheet or a stack of paper forms once sufficed, even a modest clinic quickly runs into problems: the same patient’s telephone number recorded three different ways, appointments double-booked against a single doctor, and prescriptions that cannot easily be traced back to the consultation that produced them. These are precisely the redundancy and integrity problems that the relational model was designed to solve (Connolly and Begg, 2015).

This coursework designs a relational database for a small clinic that employs a handful of doctors and serves a modest patient roster. The report follows the classic three-stage design methodology: conceptual design, expressed through an entity–relationship (ER) model; logical design, in which entities and relationships are mapped to relations and normalised to third normal form (3NF); and physical design, realised as SQL Data Definition Language (DDL). The resulting schema was implemented and tested in SQLite to confirm that every statement executes and that the declared constraints behave as intended. A short set of sample queries then demonstrates that the design answers realistic operational questions, and a closing evaluation reflects on its strengths and limitations.

The relational model was chosen over alternatives such as a document store or a single denormalised table for reasons that matter particularly in a clinical setting. Data about patients, appointments and prescriptions is highly structured and richly interconnected, and the questions the clinic asks of it — who is booked in tomorrow, which patients are overdue a follow-up, what was prescribed and when — are naturally expressed as joins across those connections. The relational model, underpinned by the sound theory of functional dependency and normalisation, gives strong guarantees about consistency that are difficult to reproduce reliably in an application built on top of a looser data store (Date, 2004). Designing the schema carefully at the outset is therefore not premature optimisation but a way of encoding the clinic’s rules once, in a place where they cannot be accidentally circumvented.

2. Requirements Analysis

2.1 Scope and stakeholders

The scope of the system was established from a description of how the clinic operates and from the perspective of the three groups who interact with it. Reception staff create and amend appointments and need to see a day’s schedule at a glance; doctors consult the record during and after a visit and issue prescriptions against it; and a practice manager reports on workload and follow-up. Each of these viewpoints places a different demand on the data, but all three depend on the same underlying facts being recorded once, accurately, and in a form that can be retrieved without ambiguity. Capturing those shared facts, rather than any one screen or report, is the proper object of the database design.

2.2 Data requirements

The database must record the following.

• Patients. Each patient has a name, a date of birth, contact details (a telephone number and, optionally, an email address) and the date on which they registered with the clinic. A patient is uniquely identifiable and email addresses, where supplied, must not be duplicated.
• Doctors. Each doctor has a name, a clinical speciality (for example general practice or dermatology) and contact details.
• Appointments. An appointment links exactly one patient with exactly one doctor at a specific date and time. It carries a status (scheduled, completed, cancelled or no-show) and a free-text reason for the visit. A single doctor cannot be booked twice for the same moment in time.
• Prescriptions. During a completed appointment a doctor may issue one or more prescriptions. Each prescription names a medication, a dosage and optional instructions, and records the date of issue. Every prescription must belong to exactly one appointment.

From these statements the principal business rules follow: a patient may hold many appointments over time but each appointment concerns a single patient; a doctor likewise attends many appointments; and each appointment may generate several prescriptions. These cardinalities drive the conceptual model in the next section. The design deliberately excludes billing, staff rostering and clinical notes, which lie outside the coursework brief.

2.3 Assumptions and non-functional requirements

A handful of assumptions were made explicit so that the model could be pinned down. The clinic operates from a single site; a patient registers once and is not merged with duplicate records; and a prescription is only ever issued in the context of an appointment, never as a standalone event. These assumptions are recorded here because each one, if later found to be false, would change the schema — the single-site assumption in particular is revisited in the evaluation.

It is also worth distinguishing the functional requirements above from the non-functional expectations the design should respect. Data integrity is paramount: the system must never allow a prescription to exist without a parent appointment, nor an appointment without a valid patient and doctor, because such orphaned records would undermine any subsequent reporting. Consistency of representation matters too — a controlled set of appointment statuses is preferable to free text, so that a query for “all cancelled appointments” returns a reliable answer rather than missing rows recorded as “cancel” or “CANCELLED”. Retrievability is a further concern: the schedule and follow-up queries the clinic runs most often should remain responsive as the appointment history grows, which has implications for indexing addressed in Section 5. These concerns are addressed not by procedural code but by declarative constraints and access structures written into the schema itself, an approach that keeps the rules close to the data and independent of whichever application happens to be reading or writing it.

3. Conceptual Design

3.1 Entities and relationships

Four entities emerge directly from the requirements: Patient, Doctor, Appointment and Prescription. Appointment is the pivot of the design. It resolves the many-to-many association between patients and doctors — a patient sees many doctors over time, and a doctor sees many patients — into two one-to-many relationships, while carrying its own descriptive attributes such as the date, time and status. In ER terms, Appointment behaves as an associative entity, a modelling pattern recommended when a relationship has attributes of its own (Elmasri and Navathe, 2016).

The relationships and their cardinalities are:

• Patient (1) — books — (M) Appointment. One patient may have many appointments; each appointment belongs to one patient. Participation on the Appointment side is total (every appointment must reference a patient).
• Doctor (1) — attends — (M) Appointment. One doctor attends many appointments; each appointment is attended by exactly one doctor.
• Appointment (1) — generates — (M) Prescription. One appointment may produce many prescriptions; each prescription arises from exactly one appointment. Participation on the Prescription side is total.

3.2 ER model

Figure 1 presents the conceptual schema in crow’s-foot notation. Each entity is drawn as a box listing its key attributes; the single bar on a connector denotes the “one” end of a relationship, and the branching crow’s foot denotes the “many” end. Appointment sits at the centre with a one-to-many relationship to each of Patient and Doctor and a one-to-many relationship onward to Prescription, making visible its role as the associative entity that ties the model together.

3.3 Normalisation to 3NF

Normalisation removes the update, insertion and deletion anomalies that arise from redundant data (Connolly and Begg, 2015). To make the argument concrete rather than mechanical, it helps to begin with the kind of design a clinic might reach for first — a single wide table in which every fact about a visit is recorded on one row — and to watch the anomalies appear. Such an “Appointments” table might carry the columns appointment_id, patient_name, patient_phone, doctor_name, doctor_speciality, appt_datetime, status and a repeating list of prescribed medications. Each normal form below removes one class of problem from this starting point.

First normal form (1NF). A relation is in 1NF when every attribute holds a single atomic value and there are no repeating groups. The starting table fails this test on the medications column, which crams several drugs into one cell. Storing “Amoxicillin 500 mg; Hydrocortisone 1%” as a single string makes it impossible to query reliably for a particular medication or to attach a dosage to each drug without fragile text parsing — the classic insertion difficulty of a repeating group. The remedy is to lift prescriptions out into their own relation, one row per medication, linked back by a key. Once that is done every attribute is atomic and each table has a primary key, so all four relations satisfy 1NF.

Second normal form (2NF). A relation is in 2NF if it is in 1NF and every non-key attribute is fully functionally dependent on the whole primary key, with no partial dependency on part of a composite key. The danger arises whenever a table is keyed on a combination of columns. Had the prescription relation been keyed on the pair (appointment_id, medication), then an attribute such as the issuing date would depend only on appointment_id, not on the medication — a partial dependency that would force the date to be repeated for every drug on the same appointment, and risk two rows disagreeing about when the visit occurred. The design sidesteps this by giving every relation a single-column surrogate primary key, so no partial dependency is possible and all relations satisfy 2NF.

Third normal form (3NF). A relation is in 3NF if it is in 2NF and no non-key attribute is transitively dependent on the primary key — that is, no non-key attribute is determined by another non-key attribute (Codd, 1972). This is exactly the flaw in the original wide table. There, doctor_speciality depends on doctor_name, which in turn depends on the appointment key: speciality is transitively dependent on the key rather than directly determined by it. The consequences are the three textbook anomalies. An update anomaly occurs if Dr Patel changes speciality: every appointment row bearing her name must be edited in lock-step, and any that is missed leaves the database contradicting itself. An insertion anomaly occurs because a newly hired doctor who has not yet seen a patient cannot be recorded at all — there is no appointment row on which to hang their details. A deletion anomaly occurs because removing the last appointment for a doctor silently erases the only record that the doctor, and their speciality, ever existed. Decomposing the table so that doctor_speciality lives once in a Doctor relation, and appointments merely reference doctor_id, removes all three at a stroke. Applying the same reasoning to patient contact details — which belong to the patient, not to any single visit — and checking that within Appointment the attributes status and reason depend only on appointment_id and not on one another, confirms that the whole schema is in 3NF, the normal form usually adopted as the practical target for transactional databases.

The design was checked against Boyce–Codd normal form (BCNF) as well. BCNF strengthens 3NF by requiring that every determinant be a candidate key; because each relation here has a single candidate key (its surrogate primary key) and no overlapping composite keys, the schema is already in BCNF, and no further decomposition is warranted. Normalisation, in short, trades a little query-time joining for a large gain in maintainability and correctness.

4. Logical Design

Mapping the ER model to the relational model yields four relations. Primary keys are underlined by convention; foreign keys are annotated.

• Patient (<u>patient_id</u>, first_name, last_name, date_of_birth, phone, email, registered_on)
• Doctor (<u>doctor_id</u>, first_name, last_name, speciality, phone, email)
• Appointment (<u>appointment_id</u>, patient_id → Patient, doctor_id → Doctor, appt_datetime, status, reason)
• Prescription (<u>prescription_id</u>, appointment_id → Appointment, medication, dosage, instructions, issued_on)

Each one-to-many relationship is implemented by posting the primary key of the “one” side into the “many” side as a foreign key, which is the standard mapping rule for such relationships (Elmasri and Navathe, 2016). A composite uniqueness constraint on Appointment (doctor_id, appt_datetime) enforces the business rule that a doctor cannot be double-booked, and the status attribute is restricted to a fixed set of permitted values.

4.1 Integrity constraints

Three kinds of integrity, each named in the relational literature, are enforced declaratively so that no application error or careless manual edit can bypass them.

• Entity integrity guarantees that every row is uniquely identifiable and that no part of a primary key is null. Each relation carries a single-column INTEGER PRIMARY KEY, which in SQLite also serves as an efficient rowid alias, satisfying this requirement without a separate surrogate-key mechanism.
• Referential integrity guarantees that every foreign key value matches an existing primary key in the referenced relation, so that no appointment can point at a non-existent patient or doctor and no prescription can be orphaned from its appointment. The FOREIGN KEY clauses declare these dependencies, and PRAGMA foreign_keys = ON ensures SQLite actually enforces them, since the engine leaves the check disabled by default.
• Domain integrity guarantees that each attribute holds only meaningful values. NOT NULL on names, dates and the appointment’s participants forbids the missing facts that would break reporting; the CHECK constraint confines status to its four legal values; and UNIQUE on each email column, and on the (doctor_id, appt_datetime) pair, forbids the duplicates the requirements rule out.

A design decision worth flagging concerns referential actions. The schema does not cascade deletes from Appointment to Prescription; a prescription is a clinical record that should not vanish silently because its parent appointment was removed. Under the default NO ACTION behaviour an attempt to delete an appointment that still has prescriptions is rejected, which is the safer choice in a healthcare context — records are retired deliberately, not by side effect.

5. Physical Design: Tested SQL Schema

The following DDL was executed in SQLite using Python’s sqlite3 module. All statements ran without error, and the constraint tests described in Section 7 confirmed that the keys, CHECK and UNIQUE rules behave as designed.

PRAGMA foreign_keys = ON;

CREATE TABLE Patient (
    patient_id      INTEGER PRIMARY KEY,
    first_name      TEXT    NOT NULL,
    last_name       TEXT    NOT NULL,
    date_of_birth   DATE    NOT NULL,
    phone           TEXT,
    email           TEXT    UNIQUE,
    registered_on   DATE    NOT NULL DEFAULT (DATE('now'))
);

CREATE TABLE Doctor (
    doctor_id       INTEGER PRIMARY KEY,
    first_name      TEXT    NOT NULL,
    last_name       TEXT    NOT NULL,
    speciality      TEXT    NOT NULL,
    phone           TEXT,
    email           TEXT    UNIQUE
);

CREATE TABLE Appointment (
    appointment_id  INTEGER PRIMARY KEY,
    patient_id      INTEGER NOT NULL,
    doctor_id       INTEGER NOT NULL,
    appt_datetime   DATETIME NOT NULL,
    status          TEXT    NOT NULL DEFAULT 'Scheduled'
                    CHECK (status IN ('Scheduled','Completed','Cancelled','No-show')),
    reason          TEXT,
    FOREIGN KEY (patient_id) REFERENCES Patient(patient_id),
    FOREIGN KEY (doctor_id)  REFERENCES Doctor(doctor_id),
    UNIQUE (doctor_id, appt_datetime)
);

CREATE TABLE Prescription (
    prescription_id INTEGER PRIMARY KEY,
    appointment_id  INTEGER NOT NULL,
    medication      TEXT    NOT NULL,
    dosage          TEXT    NOT NULL,
    instructions    TEXT,
    issued_on       DATE    NOT NULL DEFAULT (DATE('now')),
    FOREIGN KEY (appointment_id) REFERENCES Appointment(appointment_id)
);

A representative set of sample rows was inserted to exercise the schema — four patients, three doctors, six appointments spanning every status value, and three prescriptions.

5.1 Indexing

A schema is only half of physical design; access structures are the other half. SQLite automatically creates an index behind every PRIMARY KEY and UNIQUE constraint, so lookups by patient_id, doctor_id or appointment_id, and the double-booking check on (doctor_id, appt_datetime), are already served by a B-tree without further effort. The queries the clinic runs most often, however, filter or join on columns that are not covered by those automatic indexes, and it is worth adding a small number of deliberate ones.

CREATE INDEX idx_appt_datetime ON Appointment(appt_datetime);
CREATE INDEX idx_appt_patient  ON Appointment(patient_id);
CREATE INDEX idx_presc_appt    ON Prescription(appointment_id);

The first index supports the daily-schedule query, which filters appointments by date; without it the engine must scan every appointment ever recorded to find the handful on a given day, a cost that grows linearly as the history accumulates. The second and third accelerate the joins from Patient to Appointment and from Appointment to Prescription that recur throughout the reporting queries, letting the optimiser look up matching rows directly rather than scanning the child table. Indexes are not free — each one consumes storage and must be maintained on every insert and update — so they are added selectively, targeting the columns that appear in WHERE and JOIN clauses rather than being scattered across every attribute (Silberschatz, Korth and Sudarshan, 2019). For a clinic-sized dataset the read benefit on these hot paths comfortably outweighs the modest write overhead.

6. Sample Queries with Illustrative Output

The four queries below demonstrate that the schema supports common clinic operations. Each was run against the populated database; the output shown is the genuine result returned by SQLite.

Query 1 — the day’s schedule for a given date, with patient and doctor names.

SELECT a.appt_datetime,
       p.first_name || ' ' || p.last_name AS patient,
       d.last_name AS doctor,
       a.status
FROM Appointment a
JOIN Patient p ON a.patient_id = p.patient_id
JOIN Doctor  d ON a.doctor_id  = d.doctor_id
WHERE DATE(a.appt_datetime) = '2024-05-06'
ORDER BY a.appt_datetime;

appt_datetime      | patient      | doctor | status
2024-05-06 09:00   | Aisha Khan   | Patel  | Completed
2024-05-06 09:30   | Tom Bennett  | Patel  | Completed

Query 2 — workload per doctor, using an aggregate over a LEFT JOIN so that doctors with no appointments would still appear.

SELECT d.first_name || ' ' || d.last_name AS doctor,
       COUNT(a.appointment_id) AS total_appointments
FROM Doctor d
LEFT JOIN Appointment a ON d.doctor_id = a.doctor_id
GROUP BY d.doctor_id
ORDER BY total_appointments DESC;

doctor        | total_appointments
Sarah Patel   | 3
Mary Fischer  | 2
James Wright  | 1

Query 3 — prescriptions issued, traced across three tables back to the patient.

SELECT p.first_name || ' ' || p.last_name AS patient,
       pr.medication, pr.dosage, pr.issued_on
FROM Prescription pr
JOIN Appointment a ON pr.appointment_id = a.appointment_id
JOIN Patient p     ON a.patient_id = p.patient_id
ORDER BY pr.issued_on;

patient        | medication            | dosage | issued_on
Aisha Khan     | Amoxicillin           | 500 mg | 2024-05-06
Tom Bennett    | Amlodipine            | 5 mg   | 2024-05-06
Grace O'Neill  | Hydrocortisone cream  | 1%     | 2024-05-07

Query 4 — a correlated subquery identifying patients who have never had a completed appointment, useful for follow-up.

SELECT p.first_name || ' ' || p.last_name AS patient
FROM Patient p
WHERE NOT EXISTS (
    SELECT 1 FROM Appointment a
    WHERE a.patient_id = p.patient_id
      AND a.status = 'Completed'
);

patient
David Osei

7. Evaluation

The design was verified rather than merely asserted. Beyond confirming that the four queries return correct results, three deliberately invalid inserts were attempted, one for each class of integrity introduced in Section 4.1. Booking a second appointment for the same doctor at an already-taken time raised a UNIQUE constraint failed error; inserting an appointment with a status of 'Bogus' raised a CHECK constraint failed error; and inserting an appointment referencing a non-existent patient raised a FOREIGN KEY constraint failed error. Each failure is the desired behaviour — the database rejects data that would violate a business rule, which is the central benefit of enforcing integrity in the schema rather than trusting the application layer (Connolly and Begg, 2015). Together these tests exercise domain, referential and uniqueness integrity respectively, giving confidence that the declarative constraints are doing the work claimed of them.

The schema’s principal strengths are its normalised structure, which eliminates the redundancy that plagues flat-file record-keeping, and its declarative constraints, which push integrity enforcement down to the database where it cannot be bypassed. The associative Appointment entity keeps the model flexible: new statuses or attributes can be added without restructuring the patient or doctor relations, and the indexing strategy of Section 5.1 keeps the most frequent queries responsive as the appointment history grows.

Several limitations should be acknowledged. The double-booking rule protects only against identical start times; it does not model appointment duration, so overlapping appointments of unequal length would not be caught without additional logic, most likely an application-level or trigger-based check comparing time ranges. The design assumes a single clinic site; supporting multiple locations would require a Clinic entity and a further foreign key on Appointment, and would reopen the double-booking rule to include the room or site. Storing medication as free text risks the very inconsistency that normalisation set out to remove — “Amoxicillin”, “amoxycillin” and “Amox 500” would all count as distinct drugs — so a production system would introduce a controlled Medication lookup table and reference it by key, mirroring the treatment of doctor speciality. Finally, a live deployment handling sensitive patient data would demand access controls, an audit trail of who changed what, and encryption at rest, none of which the coursework brief required. These extensions are natural next steps, but the core design meets the requirements set out in Section 2 and does so on a verified, normalised and appropriately indexed foundation.

References

Codd, E.F. (1972) ‘Further normalization of the data base relational model’, in Rustin, R. (ed.) Data Base Systems. Englewood Cliffs, NJ: Prentice-Hall, pp. 33–64.

Connolly, T. and Begg, C. (2015) Database Systems: A Practical Approach to Design, Implementation, and Management. 6th edn. Harlow: Pearson Education.

Date, C.J. (2004) An Introduction to Database Systems. 8th edn. Boston, MA: Pearson/Addison-Wesley.

Elmasri, R. and Navathe, S.B. (2016) Fundamentals of Database Systems. 7th edn. Boston, MA: Pearson.

Silberschatz, A., Korth, H.F. and Sudarshan, S. (2019) Database System Concepts. 7th edn. New York: McGraw-Hill.