🏥 FIA Medical Center — Database Model & SQL Implementation

A relational database system designed to manage staff, patients, consultations, charges, and specialities for a medical center.

---

📋 Table of Contents

• Overview
• Database Schema
• Entity Relationship Diagram (ERD)
• Tables & Attributes
• Business Rules & Constraints
• Optimisation Techniques
• Sample Data
• Setup & Usage

---

Overview

The FIA Medical Center Database is a SQL Server relational database built to support the day-to-day operations of a medical center. It tracks:

• Staff members and their roles
• Staff specialities and qualification validity
• Patient records and their assigned GP
• Consultation history including charges, times, and attending staff
• Charge types for different service categories

---

Database Schema

The database consists of 7 tables:

Table	Description
StaffRole	Defines the roles a staff member can hold (e.g. GP, Nurse)
Staff	Stores all staff member records and employment dates
Patient	Stores patient details including assigned GP
Speciality	Lists all medical specialities available
StaffSpeciality	Junction table linking staff to their specialities (with qualification dates)
Charge	Defines billing types with duration and hourly rate
Consultation	Records each patient consultation — who, when, and what was charged

---

Entity Relationship Diagram

Business Rules & Constraints

The following business rules are enforced through data types and constraints in the schema:

1. Consultation duration cannot exceed 60 minutes

   • CONSTRAINT CK_duration CHECK (Duration <= 60)

2. Hourly rate is capped

   • CONSTRAINT CK_hourlyrate CHECK (HourlyRate <= 399)

3. Gender must be a valid value

   • Applied to both Staff and Patient tables: CHECK (Gender IN ('M', 'F', 'O'))

4. Staff speciality is unique per staff member

   • Composite primary key on StaffSpeciality (StaffID, SpecialityID) prevents duplicate entries

5. Speciality expiry is tracked

   • ValidTillDate in StaffSpeciality stores when a qualification expires
   • Where no expiry exists, date defaults to 2050-12-31

6. Employment history is tracked

   • DateJoined and DateLeft on Staff allow tracking of current and former employees
   • DateLeft is NULL for currently active staff

7. Patient age is derivable

   • DOB is stored in the Patient table; age can be calculated dynamically from this

---

Optimisation Techniques

Many-to-Many Resolution

The many-to-many relationship between Staff and Speciality is resolved through the StaffSpeciality junction table. This avoids data redundancy and allows storing additional relationship-specific data (qualification date, expiry date, and details).

Referential Integrity

All foreign key constraints are explicitly defined, ensuring no orphaned records can exist (e.g. a consultation cannot reference a non-existent patient or staff member).

Normalisation

The schema follows Third Normal Form (3NF):

• No repeating groups
• All non-key attributes depend on the whole primary key
• No transitive dependencies

NULL Handling

• DateLeft in Staff is nullable, cleanly distinguishing active from former employees
• SpecNotes in Speciality is nullable since not all specialities require notes

---

Sample Data

The database is pre-loaded with the following sample records:

Staff Roles: GP, Receptionist, Administrator, Registered Nurse, Enrolled Nurse, Nurse Practitioner

Staff Members (8): Including Homer Robbins (GP), Lisa Simpson (GP), Ned Flanders (Registered Nurse), and others

Patients (5): Caroline Smith, James Miller, Sarah Walker, Sam Paul, Jack Johnson

Specialities (5): GP License, Registered Surgeon, Renew License, Training, Practitioner

Charge Types (6): General GP (registered/casual), Emergency care, Vaccination/Test, Repeat prescription

Consultations (5): Sample consultations from September 2018

---