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

The scope of the system was established from a description of how the clinic operates. 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.

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”. These concerns are addressed not by procedural code but by declarative constraints 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 (ASCII notation)

The diagram below uses crow’s-foot notation, where || denotes “exactly one” and o< denotes “zero or many”.

                +------------------+
                |     PATIENT      |
                |------------------|
                | PK patient_id    |
                |    first_name    |
                |    last_name     |
                |    date_of_birth |
                |    phone         |
                |    email (U)     |
                |    registered_on |
                +--------+---------+
                         ||
                         |  books
                         o<
                +--------+-----------+            +------------------+
                |    APPOINTMENT     |            |      DOCTOR      |
                |--------------------|            |------------------|
                | PK appointment_id  |    >o------|| PK doctor_id    |
                | FK patient_id      | attends    |    first_name    |
                | FK doctor_id       |            |    last_name     |
                |    appt_datetime   |            |    speciality    |
                |    status          |            |    phone         |
                |    reason          |            |    email (U)     |
                +--------+-----------+            +------------------+
                         ||
                         |  generates
                         o<
                +--------+-----------+
                |    PRESCRIPTION     |
                |--------------------|
                | PK prescription_id |
                | FK appointment_id  |
                |    medication      |
                |    dosage          |
                |    instructions    |
                |    issued_on       |
                +--------------------+

3.3 Normalisation to 3NF

Normalisation removes the update, insertion and deletion anomalies that arise from redundant data (Connolly and Begg, 2015). Each relation is examined against the successive normal forms.

First normal form (1NF). Every attribute holds a single atomic value and each table has a primary key. There are no repeating groups: rather than storing a list of prescriptions inside an appointment row, prescriptions are held in their own relation. All four relations therefore 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. Because each relation uses a single-column surrogate primary key, no partial dependency on part of a composite key is possible. 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 determines another non-key attribute (Codd, 1972). Each relation was checked for such transitive dependencies. In Appointment, for example, status and reason depend only on appointment_id and not on one another; the doctor’s speciality is deliberately not stored in Appointment, because it depends on doctor_id rather than on the appointment, and duplicating it would create a transitive dependency. It is instead held once in the Doctor relation. Applying the same reasoning across all four relations confirms that the schema is in 3NF, which is the normal form usually adopted as the practical target for transactional databases.

A brief worked illustration makes the benefit concrete. Suppose the doctor’s speciality had been copied into every appointment row. If Dr Patel moved from general practice to a new speciality, that change would have to be propagated to every one of her past and future appointment records; missing even one would leave the database contradicting itself — an update anomaly. Under the normalised design the speciality lives in a single Doctor row, so the change is made once and is instantly correct everywhere it is read through a join. The same logic explains why patient contact details are not duplicated onto appointments and why prescriptions reference an appointment rather than repeating the patient and doctor identifiers. 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.

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.

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. 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).

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.

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. The design assumes a single clinic site; supporting multiple locations would require a Clinic entity and a further foreign key. Storing medication as free text risks the inconsistency that a controlled Medication lookup table would prevent, and a production system would also demand indexing on frequently queried columns such as appt_datetime, together with access controls appropriate to sensitive patient data. 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 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.