👨‍💻Author: Keabetswe Masole

Software Used: SQL Server Management Studio 20

---

📌 Overview

This project creates a fully structured clinic management database. It tracks patient information, doctor details, services offered, appointments, and payments while ensuring data integrity through constraints and relationships.

---

🧠 Database Structure

📍 Tables

-> Patient

• PatientID (Primary Key)
• PatientName
• Phone
• CreatedAt

-> Doctor

• DoctorID (Primary Key)
• DoctorName
• Specialization
• CreatedAt

-> Service

• ServiceID (Primary Key)
• ServiceName
• Cost

-> Appointment

• AppointmentID (Primary Key)
• PatientID (Foreign Key)
• DoctorID (Foreign Key)
• ServiceID (Foreign Key)
• AppointmentDate
• Status (Scheduled, Completed, No-Show, Cancelled)
• Prevents double booking using a unique constraint

-> Payment

• PaymentID (Primary Key)
• AppointmentID (Foreign Key)
• Amount
• PaymentStatus (Paid, Unpaid, Partial)
• PaymentDate

---

⚙️ Features

• Fully normalized relational database design
• Data integrity using primary keys, foreign keys, and constraints
• Prevents doctor double-booking with unique constraints
• Tracks appointments and payment statuses
• Generates insights using advanced SQL queries

---

▶️ How to Run

1. Open SQL Server Management Studio (SSMS)
2. Create a new query
3. Copy and paste the SQL script
4. Execute the script
5. Run the SELECT queries to view results

---

🔍 Advanced Queries

📊 Total Revenue Collected:

SELECT SUM(Amount) AS TotalRevenue FROM Payment WHERE PaymentStatus = 'Paid';

👨‍⚕️ Revenue by Doctor:

SELECT d.DoctorName, SUM(p.Amount) AS Revenue FROM Doctor d JOIN Appointment a ON d.DoctorID = a.DoctorID JOIN Payment p ON a.AppointmentID = p.AppointmentID WHERE p.PaymentStatus = 'Paid' GROUP BY d.DoctorName ORDER BY Revenue DESC; --Decreasing order

🏆 Most Popular Service:

SELECT s.ServiceName, COUNT(*) AS TimesBooked FROM Service s JOIN Appointment a ON s.ServiceID = a.ServiceID GROUP BY s.ServiceName ORDER BY TimesBooked DESC; --Decreasing order

💰 Outstanding Patient Balances:

SELECT p.PatientName, SUM(pay.Amount) AS OutstandingBalance FROM Patient p JOIN Appointment a ON p.PatientID = a.PatientID JOIN Payment pay ON a.AppointmentID = pay.AppointmentID WHERE pay.PaymentStatus IN ('Unpaid','Partial') GROUP BY p.PatientName HAVING SUM(pay.Amount) > 0 ORDER BY OutstandingBalance DESC;

---

🚀 Future Improvements

• Add user authentication (admin/staff roles)
• Create stored procedures and triggers
• Build a front-end application (web or desktop)
• Add reporting dashboards
• Optimize performance with indexing