AKK041705/BSEM1201F_Group2.sql

Public Health Clinic Records System SL: MySQL/MariaDB database for Sierra Leone clinics (SDG 3). Manages patients, health workers, appointments, diagnoses, and treatments with full Create,Rename, Update, Delete operations. Includes constraints, sample data, and SQL query examples (filtering, joins, aggregates). # 🏥 Public Health Clinic Records System A complete MySQL database solution for managing public health clinic operations – patients, health workers, appointments, diagnoses, and treatments. This project demonstrates database design, data integrity, CRUD operations, and advanced SQL queries. ## 👥 Authors - **Rayan Martin Turay** - **Abdul Karim Koroma** - **Foday Kamara** --- ## 📚 Table of Contents - Project Overview - Database Schema - Features - Setup & Installation - Sample Queries - User Management - Notes --- ## Project Overview This system models a real‑world public health clinic. It tracks: - **Patients** – personal details, contact info, address. - **Health Workers** – doctors/nurses and their departments. - **Appointments** – visit dates, reasons, linked to patients and staff. - **Medical Diagnoses** – conditions and clinical notes per appointment. - **Treatments** – prescribed medicines, dosage, duration, and cost. The SQL script (`BSEM1201F_Group2.sql`) creates the database, tables, constraints, sample data (20+ records each), and demonstrates a wide range of SQL operations. --- ## Database Schema | Table | Primary Key | Foreign Key(s) | Description | |-------|-------------|----------------|-------------| | `Patient` | `Patient_ID` | – | Demographics and contact | | `Health_Worker` | `Health_Worker_ID` | – | Staff roles and departments | | `Appointment` | `Appointment_ID` | `Patient_ID`, `Health_Worker_ID` | Patient visits | | `Medical_Diagnosis` | `Diagnoses_ID` | `Appointment_ID` | Diagnoses & notes | | `Treatment` | `Treatment_ID` | `Diagnoses_ID` | Prescriptions and costs | ### 🔒 Constraints - `Gender` ENUM(`'Male'`, `'Female'`, `'Other'`) - `Role` ENUM(`'Doctor'`, `'Nurse'`) - `Treatment_Cost` > 0 (CHECK constraint) - All foreign keys enforce referential integrity --- ## Features The script covers **essential SQL concepts**: ### Data Definition (DDL) - `CREATE DATABASE` & `CREATE TABLE` - `RENAME TABLE` (`Diagnoses` → `Medical_Diagnosis …