Relational Database Design Sql Queries Clinic

Introduction

A booking system sits at the heart of almost every modern clinic, recording who is seen, by whom, when and for what purpose. This assignment works through the design of a relational database for a small general-practice clinic, using the scenario as a vehicle to demonstrate the standard stages of database design taught at undergraduate level. The aim is not to build a production system but to show, as a model worked example, how a designer moves from an informal description of the data to a sound, normalised relational schema and a set of queries that answer realistic operational questions.

The report follows the conventional design pipeline. It first captures the requirements and states the assumptions that bound the problem. It then develops a conceptual model in the form of an entity-relationship (ER) diagram, before applying the rules of normalisation to remove redundancy and update anomalies. From the normalised design it derives the logical relational schema, expressed in SQL data-definition language, and finally presents a series of annotated SQL queries. Throughout, the emphasis falls on the reasoning behind each decision, because it is that reasoning, rather than the finished tables alone, that marks out a competent design (Connolly and Begg, 2015).

Scenario and Requirements

The clinic employs a number of doctors, each with a particular speciality, and operates from a set of consulting rooms spread across two floors. Patients register with the clinic and may book appointments with any available doctor. Each appointment takes place in a single room, at a stated date and time, and lasts for a standard or extended duration. Reception staff need to record the outcome of every appointment, for example whether the patient attended, cancelled or failed to attend, and clinicians may issue one or more prescriptions during a consultation.

From this description the following functional requirements were identified:

• the system must store personal and contact details for every registered patient, including a unique NHS number;
• it must hold a record of each doctor and their speciality;
• it must allow an appointment to be booked for a given patient with a given doctor, in a specific room, on a specific date and time;
• it must track the status of each appointment through a controlled list of permitted values;
• it must record the medications prescribed at an appointment, recognising that a single consultation may generate several prescriptions.

Two assumptions bound the design. First, an appointment involves exactly one patient and one doctor, so group clinics are out of scope. Second, medication is described by a central catalogue, so that the same drug is never recorded twice with different spellings. These assumptions keep the model tractable while remaining faithful to how a small clinic actually operates, and stating them explicitly is itself part of good design documentation (Churcher, 2012).

Conceptual Design: the Entity-Relationship Model

The conceptual model identifies the things about which the clinic needs to store data, the entities, together with their attributes and the relationships between them (Chen, 1976). Six entities emerged from the requirements: Patient, Doctor, Room, Appointment, AppointmentStatus and, to handle prescribing, Medication. The Appointment entity is central: it is the event that connects patients, doctors and rooms.

Entities and Attributes

Relationships and Cardinality

Because a single appointment can give rise to many prescriptions, and a single medication can be prescribed at many different appointments, the link between Appointment and Medication is many-to-many. A many-to-many relationship cannot be represented directly in a relational database, so it is resolved by an associative entity, Prescription, which also carries attributes of its own such as the dosage and the instructions (Elmasri and Navathe, 2016). Every other relationship is one-to-many: one patient books many appointments, one doctor conducts many appointments, one room hosts many appointments, and one status value classifies many appointments.

PATIENT            ||--o{ APPOINTMENT    : books
DOCTOR             ||--o{ APPOINTMENT    : conducts
ROOM               ||--o{ APPOINTMENT    : hosts
APPOINTMENT_STATUS ||--o{ APPOINTMENT    : classifies
APPOINTMENT        ||--o{ PRESCRIPTION   : generates
MEDICATION         ||--o{ PRESCRIPTION   : listed in

In the crow’s-foot notation above, the double bar (||) denotes the “one” end of a relationship and the circle-and-fork (o{) denotes the optional “many” end. Reading the first line, one patient may book zero or more appointments, while each appointment is booked by exactly one patient. The Prescription entity on the final two lines carries the two foreign keys that together resolve the many-to-many association between appointments and medications.

Normalisation

Normalisation is the process of organising attributes into relations so that redundancy, and the update, insertion and deletion anomalies it causes, are progressively eliminated (Codd, 1970; Date, 2004). The starting point is an unnormalised appointment sheet that mirrors a paper form: a single record per appointment, with the prescribed medications held as a repeating group.

APPOINTMENT_SHEET (AppointmentID, AppointmentDate, StartTime,
    PatientID, PatientName, PatientDOB,
    DoctorID, DoctorName, Speciality,
    RoomID, RoomName, Floor, StatusName,
    { MedicationCode, MedicationName, Dosage } )

The braces mark the repeating group: one appointment may list several medications. This structure is convenient on paper but unworkable in a table, because a relation cannot contain repeating groups and because every medication line needlessly repeats the patient, doctor and room details.

First Normal Form

A relation is in first normal form (1NF) when every attribute holds a single, atomic value and there are no repeating groups. The repeating medication group is removed by promoting each medication line to its own row, identified by the combination of the appointment and the medication. This yields two relations, one for the appointment and one for the prescription lines, with the latter taking the composite primary key (AppointmentID, MedicationCode).

Second Normal Form

Second normal form (2NF) requires, in addition, that every non-key attribute depends on the whole of the primary key, not merely on part of it. The appointment relation already satisfies this because its key is a single attribute. In the prescription-line relation, however, the medication’s name depends only on MedicationCode, which is part of the composite key:

MedicationCode                  → MedicationName        (partial dependency)
(AppointmentID, MedicationCode) → Dosage, Instructions  (full dependency)

Dosage and instructions depend on the full key, because the same drug may be prescribed at a different dose on different occasions. The partial dependency is removed by splitting off a Medication relation holding the medication’s descriptive attributes, leaving the Prescription relation with only the dosage and instructions against its composite key.

Third Normal Form

Third normal form (3NF) additionally forbids transitive dependencies, in which a non-key attribute depends on another non-key attribute rather than directly on the key. The appointment relation contains several such chains:

AppointmentID → DoctorID  → DoctorName, Speciality
AppointmentID → RoomID    → RoomName, Floor
AppointmentID → PatientID → PatientName, PatientDOB

Each chain describes an attribute that really belongs to the doctor, the room or the patient and merely travels with the appointment. Removing these transitive dependencies produces separate Patient, Doctor and Room relations and leaves Appointment holding only its own attributes and the foreign keys that point to them. The design is now in third normal form, which is sufficient for most undergraduate coursework and for the great majority of practical applications (Coronel and Morris, 2019).

The Relational Schema

The fully normalised design comprises seven relations. The table below lists each relation with its primary key and the foreign keys that enforce the relationships identified earlier.

As an illustration, the Appointment relation is defined in SQL below. The data-definition statement declares the primary key, the foreign keys that tie the table back to its parents, and a check constraint that rejects a non-positive duration.

CREATE TABLE Appointment (
    AppointmentID   INT  PRIMARY KEY,
    PatientID       INT  NOT NULL,
    DoctorID        INT  NOT NULL,
    RoomID          INT  NOT NULL,
    StatusID        INT  NOT NULL,
    AppointmentDate DATE NOT NULL,
    StartTime       TIME NOT NULL,
    DurationMinutes INT  NOT NULL DEFAULT 15,
    CONSTRAINT fk_appt_patient FOREIGN KEY (PatientID) REFERENCES Patient(PatientID),
    CONSTRAINT fk_appt_doctor  FOREIGN KEY (DoctorID)  REFERENCES Doctor(DoctorID),
    CONSTRAINT fk_appt_room    FOREIGN KEY (RoomID)    REFERENCES Room(RoomID),
    CONSTRAINT fk_appt_status  FOREIGN KEY (StatusID)  REFERENCES AppointmentStatus(StatusID),
    CONSTRAINT chk_duration    CHECK (DurationMinutes > 0)
);

Defining the keys and constraints at creation time means the database itself guarantees that no appointment can reference a patient, doctor or room that does not exist, and that the controlled status list cannot be bypassed (Silberschatz, Korth and Sudarshan, 2019).

Worked SQL Queries

With the schema in place, the value of the design becomes clear when realistic questions are answered in SQL. Six representative queries are presented below, each with a short explanation of the technique it uses. The identifiers and dates are illustrative values chosen to make the examples concrete.

-- 1. A named doctor's appointments for today
SELECT a.AppointmentID, a.StartTime, p.FirstName, p.LastName, r.RoomName
FROM   Appointment a
JOIN   Patient p ON a.PatientID = p.PatientID
JOIN   Room    r ON a.RoomID    = r.RoomID
WHERE  a.DoctorID = 14
  AND  a.AppointmentDate = CURRENT_DATE
ORDER BY a.StartTime;

This query joins three tables to list a named doctor’s appointments for the current day, ordered by start time, which is the view a clinician would want at the start of a session.

-- 2. Appointments per doctor during September
SELECT d.DoctorID, d.LastName, COUNT(*) AS TotalAppointments
FROM   Appointment a
JOIN   Doctor d ON a.DoctorID = d.DoctorID
WHERE  a.AppointmentDate BETWEEN '2026-09-01' AND '2026-09-30'
GROUP BY d.DoctorID, d.LastName
ORDER BY TotalAppointments DESC;

Aggregation with GROUP BY summarises workload, counting the appointments each doctor held during the month and ranking them from busiest to quietest.

-- 3. Patients who did not attend in a date range
SELECT DISTINCT p.PatientID, p.FirstName, p.LastName, p.Phone
FROM   Appointment a
JOIN   Patient p           ON a.PatientID = p.PatientID
JOIN   AppointmentStatus s ON a.StatusID  = s.StatusID
WHERE  s.StatusName = 'No-show'
  AND  a.AppointmentDate BETWEEN '2026-09-01' AND '2026-09-30';

A join to the status table filters for non-attendance, with DISTINCT ensuring that a patient who missed several appointments appears only once on the follow-up list.

-- 4. Rooms that are free on a given date
SELECT r.RoomID, r.RoomName
FROM   Room r
WHERE  r.RoomID NOT IN (
           SELECT a.RoomID
           FROM   Appointment a
           WHERE  a.AppointmentDate = '2026-10-28'
       );

A subquery with NOT IN finds rooms that have no booking on a given date, which is useful when a room needs to be taken out of service; a LEFT JOIN with a test for a null match would achieve the same result.

-- 5. Medications prescribed more than five times
SELECT m.Name, COUNT(*) AS TimesPrescribed
FROM   Prescription pr
JOIN   Medication m ON pr.MedicationID = m.MedicationID
GROUP BY m.Name
HAVING COUNT(*) > 5
ORDER BY TimesPrescribed DESC;

GROUP BY combined with HAVING restricts the result to medications prescribed more than five times, illustrating the difference between WHERE, which filters individual rows, and HAVING, which filters groups after aggregation.

-- 6. Cancel an appointment
UPDATE Appointment
SET    StatusID = (SELECT StatusID FROM AppointmentStatus
                   WHERE StatusName = 'Cancelled')
WHERE  AppointmentID = 2041;

Finally, a single UPDATE marks an appointment as cancelled, retrieving the correct status identifier through a subquery rather than hard-coding it, so the statement still works even if the status codes are renumbered.

Integrity, Constraints and Indexing

A sound schema is only the foundation; a working system must also protect the quality of its data and perform acceptably under load. Referential integrity is enforced by the foreign keys declared above: the database will refuse to insert an appointment for a non-existent patient and will, depending on the chosen referential action, either block or cascade the deletion of a parent row. For a clinic, blocking deletion is usually the safer choice, because appointment history should be retained even after a patient leaves the practice (Hoffer, Ramesh and Topi, 2016).

Domain integrity is strengthened by further constraints. The NHS number on the Patient table should carry a UNIQUE constraint so that no two patients can share one, and columns such as the patient’s surname or the appointment date should be declared NOT NULL. The check constraint on appointment duration shown earlier is a simple example of enforcing a business rule inside the schema, where it cannot be forgotten by application code.

Performance is addressed through indexing. Primary keys are indexed automatically, but the columns most often used in search and join conditions, namely AppointmentDate, DoctorID and PatientID on the Appointment table, would benefit from secondary indexes, since the busiest queries filter appointments by date and by doctor. Indexes speed up reads at a small cost to writes, a trade-off that clearly favours a booking system in which appointments are read far more often than they are created (Garcia-Molina, Ullman and Widom, 2014).

Conclusion

This model assignment has traced a complete, if deliberately compact, database design for a clinic booking system. Beginning from an informal scenario, it identified the entities and relationships in an ER model, applied the rules of first, second and third normal form to eliminate redundancy and update anomalies, and derived a seven-table relational schema expressed in SQL. A set of worked queries then showed how the normalised structure answers practical questions about appointments, workload, non-attendance and prescribing.

The wider lesson, and the reason this example is offered as reference material, is that each stage justifies the next: the normalisation decisions follow from the functional dependencies, the schema follows from the normalised relations, and the queries are straightforward only because the schema is sound. A student documenting their own design is encouraged to make that chain of reasoning explicit, because it is the clearest evidence of genuine understanding.

References

• Beaulieu, A. (2020) Learning SQL: Generate, Manipulate, and Retrieve Data. 3rd edn. Sebastopol, CA: O’Reilly Media.
• Chen, P.P. (1976) ‘The entity-relationship model: toward a unified view of data’, ACM Transactions on Database Systems, 1(1), pp. 9–36.
• Churcher, C. (2012) Beginning Database Design: From Novice to Professional. 2nd edn. New York: Apress.
• Codd, E.F. (1970) ‘A relational model of data for large shared data banks’, Communications of the ACM, 13(6), pp. 377–387.
• Connolly, T. and Begg, C. (2015) Database Systems: A Practical Approach to Design, Implementation, and Management. 6th edn. Harlow: Pearson.
• Coronel, C. and Morris, S. (2019) Database Systems: Design, Implementation, and Management. 13th edn. Boston: Cengage.
• Date, C.J. (2004) An Introduction to Database Systems. 8th edn. Boston: Addison-Wesley.
• Elmasri, R. and Navathe, S.B. (2016) Fundamentals of Database Systems. 7th edn. Harlow: Pearson.
• Garcia-Molina, H., Ullman, J.D. and Widom, J. (2014) Database Systems: The Complete Book. 2nd edn. Harlow: Pearson.
• Harrington, J.L. (2016) Relational Database Design and Implementation. 4th edn. Cambridge, MA: Morgan Kaufmann.
• Hoffer, J.A., Ramesh, V. and Topi, H. (2016) Modern Database Management. 12th edn. Harlow: Pearson.
• Silberschatz, A., Korth, H.F. and Sudarshan, S. (2019) Database System Concepts. 7th edn. New York: McGraw-Hill.