Description
see
Project
Deadline: Thursday 04/12/2025 @ 23:59
[Total Mark is 14]
Student Details:
CRN:
Name:
Name:
Name:
ID:
ID:
ID:
Instructions:
• You must submit two separate copies (one Word file and one PDF file) using the Assignment Template on
Blackboard via the allocated folder. These files must not be in compressed format.
• It is your responsibility to check and make sure that you have uploaded both the correct files.
• Zero mark will be given if you try to bypass the SafeAssign (e.g. misspell words, remove spaces between
words, hide characters, use different character sets, convert text into image or languages other than English
or any kind of manipulation).
• Email submission will not be accepted.
• You are advised to make your work clear and well-presented. This includes filling your information on the cover
page.
• You must use this template, failing which will result in zero mark.
• You MUST show all your work, and text must not be converted into an image, unless specified otherwise by
the question.
• Late submission will result in ZERO mark.
• The work should be your own, copying from students or other resources will result in ZERO mark.
• Use Times New Roman font for all your answers.
Pg. 01
Project Instructions
• You can work on this project as a group of a minimum of 2 and a maximum of 3
students. Each group member must submit the project individually with all group
member names and IDs mentioned on the cover page.
• You must use this template and failing to do so will result in a zero mark.
• The work should be your own. Copying from other students or other resources will
result in a zero mark. Zero marks will also be given if you try to bypass SafeAssign
(e.g., misspell words, remove spaces between words, hide characters, use different
character sets, convert text into an image or languages other than English).
Project Mark Distribution
This project is worth a total of 14 marks, distributed as follows:
• Conceptual Schema Design: Design a Conceptual Schema using ER modelling
concepts, including Entities, Relationships, Attributes, Participation (Total or
Partial), and Cardinality.
• Normalization:
o Tables before Normalization.
o Tables after Normalization Using the mapping algorithm.
• Database Implementation:
o Use a DBMS like MySQL to create the normalized tables and populate
your tables with at least 10 rows.
o Execute the requested sample queries.
Project Submission Content
Each student must submit one report containing the following:
• Project description.
• ER Diagram.
• All schemas before normalization.
• All schemas after normalization.
• All SQL statements for creating tables and inserting data.
• All requested queries and their results.
• Screenshots from your DBMS (e.g., MySQL) of all the tables before and after
population and query results.
Pg. 02
Project title
Database System for a Smart Healthcare Clinic Network
Project Description
Design and implement a database system for a smart healthcare clinic network operating
in multiple cities. The network manages patient registrations, appointment bookings,
doctor schedules, electronic prescriptions, billing, multiple clinics/branches, medical
inventory, security for sensitive data, and user roles (patients, doctors, admin staff). The
system must support reporting, analytics, and enforce security and privacy protocols.
Project Tasks and Mark Distribution
Task
Description
CLOs
Covered
Marks
Conceptual
Design
Design ER diagram: entities (patients,
doctors, clinics etc.), relationships, attributes,
participation, cardinality
CLO1,
CLO3,
CLO4
3
Prenormalization
Tables before normalization
CLO3,
CLO4
2
Normalization
Apply mapping algorithm, normalize and
justify
DBMS
Implementation
Using MySQL (or other DBMS): create
tables, insert at least 10 example rows per
table
Queries and
Analytics
Execute queries: see “Sample Queries” below
CLO3,
CLO4
2
Documentation
& Security
Describe security controls (user roles, access,
sensitive data protection), reflect on different
models used
CLO2,
CLO5
1
CLO3,
CLO4,
CLO5
CLO3,
CLO4,
CLO5
3
3
Pg. 03
Sample Queries & Requirements
• List the names and contact details of patients with more than two appointments
in the last month.
• List all available doctors by specialty at a given clinic (e.g., in Riyadh).
• Retrieve all prescriptions issued by a doctor within a specific time frame.
• List medicines running low in stock at any location (inventory management).
• Display appointments longer than 30 minutes, with patient and doctor details.
• Produce a report of revenue generated per clinic (by location/city).
• For security, explain how user authentication, authorization, and data encryption
would be enforced, and who has access to what data.
• Illustrate example role-permission tables (e.g., only certain staff can see billing
or medical data).
Pg. 04
1. Project Description
This project involves the design and implementation of a comprehensive database
system for a Smart Healthcare Clinic Network that operates in multiple cities across
Saudi Arabia.
The system manages patient registration, appointment scheduling, doctor management,
electronic prescriptions, billing, clinic branches, and medical inventory. It supports
data analytics and enforces strict security and privacy controls to protect patient data.
Multiple user roles (Patients, Doctors, and Administrative Staff) are supported with
distinct access permissions to ensure compliance with healthcare privacy policies and
minimize unauthorized access.
2. Conceptual Schema Design (ER Diagram)
Entities and Descriptions
• PATIENT: Stores patient demographic and contact details.
• DOCTOR: Holds doctor details, specialties, and contact info.
• CLINIC: Represents each clinic branch in the healthcare network.
• APPOINTMENT: Captures consultations between patients and doctors.
• PRESCRIPTION: Lists medications prescribed during appointments.
• MEDICINE: Stores details of available medicines in stock.
• PRESCRIPTION_LINE: Composite entity linking prescriptions to medicines.
• BILL: Manages financial transactions linked to appointments.
• STAFF: Represents administrative and support staff for each clinic.
Relationships
• A PATIENT can have many APPOINTMENTS (1:N).
• A DOCTOR can have many APPOINTMENTS (1:N).
• Each APPOINTMENT belongs to one CLINIC (N:1).
• Each DOCTOR works at one CLINIC (N:1).
• Each APPOINTMENT generates one PRESCRIPTION (1:1).
• A PRESCRIPTION can include multiple MEDICINES (M:N) via
PRESCRIPTION_LINE.
• Each APPOINTMENT produces one BILL (1:1).
• Each STAFF member works in one CLINIC (N:1).
Participation:
Pg. 05
•
Every appointment must be linked to a valid patient and doctor (total
participation).
Pg. 06
3. Pre-Normalization (Unnormalized Tables)
UNF_PATIENT_APPOINTMENT
Pati
ent_
ID
Patie
nt_N
ame
P10
0
Ahme
d AlSaud
Patie
nt_Ph
one
05011
12222
Appoi
ntment
_ID
App_
DateT
ime
A500
202405-20
10:00
Du
rat
ion
45
Doc
tor_
ID
Docto
r_Na
me
Doctor
_Speci
alty
Cli
nic
_I
D
Clin
ic_C
ity
Prescrip
tion_Det
ails
D20
Dr.
Fatim
a
Khan
Cardiol
ogy
C0
1
Riya
dh
MedX,
10mg,
2/day
UNF_CLINIC_BILLING
Bill_
ID
App_
ID
Patient_N
ame
Amo
unt
Clinic_Loc
ation
Medicine_N
ame
Medicine_
Cost
Staff_N
ame
Staff_
Role
B001
A500
Ahmed
Al-Saud
350.0
0
Riyadh
MedX
50.00
Khalid
Hassan
Recepti
on
4. Normalization (Applying Mapping Algorithm)
Normalization to 3NF
Each entity is mapped into its own relation with appropriate keys and foreign
constraints.
Final Normalized Tables:
1. PATIENT (Patient_ID, Name, Phone, Email, DOB, Address)
2. CLINIC (Clinic_ID, Name, City, Address, Phone)
3. DOCTOR (Doctor_ID, Name, Specialty, Phone, Email, Clinic_ID)
4. STAFF (Staff_ID, Name, Role, Email, Clinic_ID)
5. APPOINTMENT (Appointment_ID, Date_Time, Duration, Status, Patient_ID,
Doctor_ID, Clinic_ID)
6. PRESCRIPTION (Prescription_ID, Issue_Date, Notes, Appointment_ID)
7. MEDICINE (Medicine_ID, Name, Description, Manufacturer, Stock_Quantity)
8. PRESCRIPTION_LINE (Prescription_ID, Medicine_ID, Dosage, Frequency)
Pg. 07
9. BILL (Bill_ID, Amount, Status, Issue_Date, Appointment_ID)
All relations are in 3rd Normal Form (3NF), no transitive or partial dependencies
remain.
5. DBMS Implementation (MySQL)
5.1 Create Database
CREATE DATABASE SmartHealthcareDB;
USE SmartHealthcareDB;
5.2 Create Tables
— Create Database
CREATE DATABASE SmartHealthcareDB;
USE SmartHealthcareDB;
— Create Tables
CREATE TABLE PATIENT (
Patient_ID VARCHAR(10) PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Phone VARCHAR(15),
Email VARCHAR(100),
Date_of_Birth DATE,
Address TEXT
);
CREATE TABLE CLINIC (
Clinic_ID VARCHAR(10) PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
City VARCHAR(50) NOT NULL,
Address TEXT,
Phone VARCHAR(15)
);
CREATE TABLE DOCTOR (
Pg. 08
Doctor_ID VARCHAR(10) PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Specialty VARCHAR(50) NOT NULL,
Phone VARCHAR(15),
Email VARCHAR(100),
Clinic_ID VARCHAR(10),
FOREIGN KEY (Clinic_ID) REFERENCES CLINIC(Clinic_ID)
);
CREATE TABLE STAFF (
Staff_ID VARCHAR(10) PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Role VARCHAR(50) NOT NULL,
Email VARCHAR(100),
Clinic_ID VARCHAR(10),
FOREIGN KEY (Clinic_ID) REFERENCES CLINIC(Clinic_ID)
);
CREATE TABLE APPOINTMENT (
Appointment_ID VARCHAR(10) PRIMARY KEY,
Date_Time DATETIME NOT NULL,
Duration INT NOT NULL, — in minutes
Status VARCHAR(20) DEFAULT ‘Scheduled’,
Patient_ID VARCHAR(10) NOT NULL,
Doctor_ID VARCHAR(10) NOT NULL,
Clinic_ID VARCHAR(10) NOT NULL,
FOREIGN KEY (Patient_ID) REFERENCES PATIENT(Patient_ID),
FOREIGN KEY (Doctor_ID) REFERENCES DOCTOR(Doctor_ID),
FOREIGN KEY (Clinic_ID) REFERENCES CLINIC(Clinic_ID)
);
CREATE TABLE PRESCRIPTION (
Prescription_ID VARCHAR(10) PRIMARY KEY,
Issue_Date DATE NOT NULL,
Notes TEXT,
Pg. 09
Appointment_ID VARCHAR(10) NOT NULL,
FOREIGN KEY (Appointment_ID) REFERENCES
APPOINTMENT(Appointment_ID)
);
CREATE TABLE MEDICINE (
Medicine_ID VARCHAR(10) PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Description TEXT,
Manufacturer VARCHAR(100),
Stock_Quantity INT NOT NULL
);
CREATE TABLE PRESCRIPTION_LINE (
Prescription_ID VARCHAR(10),
Medicine_ID VARCHAR(10),
Dosage VARCHAR(50),
Frequency VARCHAR(50),
PRIMARY KEY (Prescription_ID, Medicine_ID),
FOREIGN KEY (Prescription_ID) REFERENCES
PRESCRIPTION(Prescription_ID),
FOREIGN KEY (Medicine_ID) REFERENCES MEDICINE(Medicine_ID)
);
CREATE TABLE BILL (
Bill_ID VARCHAR(10) PRIMARY KEY,
Amount DECIMAL(10, 2) NOT NULL,
Status VARCHAR(20) DEFAULT ‘Pending’,
Issue_Date DATE NOT NULL,
Appointment_ID VARCHAR(10) NOT NULL,
FOREIGN KEY (Appointment_ID) REFERENCES
APPOINTMENT(Appointment_ID)
);
Pg. 10
5.3 Sample Data Insertion
— CLINIC
INSERT INTO CLINIC VALUES
(‘C01’, ‘Central Riyadh Clinic’, ‘Riyadh’, ‘King Fahd Rd’, ‘+966112345678’),
(‘C02’, ‘Jeddah Coastal Medical’, ‘Jeddah’, ‘Al Hamra’, ‘+966122345678’),
(‘C03’, ‘Dammam Health Hub’, ‘Dammam’, ‘Prince Nayef St’, ‘+966133345678’),
(‘C04’, ‘Mecca Care Center’, ‘Mecca’, ‘Ajyad St’, ‘+966125556789’),
(‘C05’, ‘Medina Life Clinic’, ‘Medina’, ‘King Abdullah Rd’, ‘+966144556789’),
(‘C06’, ‘Tabuk Wellness’, ‘Tabuk’, ‘Al Thakra St’, ‘+966145667890’),
(‘C07’, ‘Taif General’, ‘Taif’, ‘Abu Bakr St’, ‘+966127889900’),
(‘C08’, ‘Abha Family Clinic’, ‘Abha’, ‘Al Salam Rd’, ‘+966172334455’),
(‘C09’, ‘Najran Health’, ‘Najran’, ‘King Saud Rd’, ‘+966175556789’),
(‘C10’, ‘Yanbu Wellness’, ‘Yanbu’, ‘Al Madina Rd’, ‘+966143334455’);
— PATIENT
Pg. 11
INSERT INTO PATIENT VALUES
(‘P100′,’Ahmed Al-Saud’,’0501112222′,’[email protected]’,’1985-05-15′,’Riyadh’),
(‘P101′,’Sarah Johnson’,’0501113333′,’[email protected]’,’1990-08-22′,’Jeddah’),
(‘P102′,’Omar Farooq’,’0554441234′,’[email protected]’,’1978-12-03′,’Riyadh’),
(‘P103′,’Fatimah Noor’,’0561234567′,’[email protected]’,’1988-11-05′,’Dammam’),
(‘P104′,’Yousef Abdullah’,’0542223344′,’[email protected]’,’1995-07-10′,’Medina’),
(‘P105′,’Layla Hassan’,’0507778899′,’[email protected]’,’1993-03-21′,’Taif’),
(‘P106′,’Ali Mubarak’,’0531114444′,’[email protected]’,’1980-01-25′,’Abha’),
(‘P107′,’Aisha Karim’,’0556677788′,’[email protected]’,’1999-06-14′,’Riyadh’),
(‘P108′,’Hassan Omar’,’0509991111′,’[email protected]’,’1975-02-18′,’Jeddah’),
(‘P109′,’Mariam Yusuf’,’0523334444′,’[email protected]’,’1982-09-12′,’Mecca’),
(‘P110′,’Khalid Ahmed’,’0514445555′,’[email protected]’,’1987-04-18′,’Riyadh’),
(‘P111′,’Noura Hassan’,’0536667777′,’[email protected]’,’1991-11-30′,’Riyadh’);
— DOCTOR
INSERT INTO DOCTOR VALUES
(‘D20′,’Dr. Fatima Khan’,’Cardiology’,’0505556666′,’[email protected]’,’C01′),
(‘D21′,’Dr. James Wilson’,’Pediatrics’,’0507778888′,’[email protected]’,’C02′),
(‘D22′,’Dr. Amina Chen’,’Dermatology’,’0509990000′,’[email protected]’,’C01′),
(‘D23′,’Dr. Omar Ali’,’Neurology’,’0546665555′,’[email protected]’,’C03′),
(‘D24′,’Dr. Huda Rashid’,’Orthopedics’,’0534445555′,’[email protected]’,’C04′),
(‘D25′,’Dr. Ali Ahmed’,’General Surgery’,’0521113333′,’[email protected]’,’C05′),
(‘D26′,’Dr. Maryam Saleh’,’Gynecology’,’0558889999′,’[email protected]’,’C06′),
Pg. 12
(‘D27′,’Dr. Khalid Noor’,’ENT’,’0502223333′,’[email protected]’,’C07′),
(‘D28′,’Dr. Yusuf Saeed’,’Urology’,’0569998888′,’[email protected]’,’C08′),
(‘D29′,’Dr. Laila Al-Harbi’,’Dermatology’,’0501239876′,’[email protected]’,’C09′),
(‘D30′,’Dr. Mohammed AlRashid’,’Cardiology’,’0547778888′,’[email protected]’,’C01′),
(‘D31′,’Dr. Samira Aboud’,’Pediatrics’,’0532223333′,’[email protected]’,’C01′);
— APPOINTMENT
INSERT INTO APPOINTMENT VALUES
(‘A500′,DATE_SUB(CURDATE(), INTERVAL 5 DAY),45,’Completed’,’P100′,’D20′,’C01′),
(‘A501′,DATE_SUB(CURDATE(), INTERVAL 10
DAY),30,’Completed’,’P100′,’D22′,’C01′),
(‘A502′,DATE_SUB(CURDATE(), INTERVAL 15
DAY),60,’Completed’,’P100′,’D30′,’C01′),
(‘A503′,DATE_SUB(CURDATE(), INTERVAL 7 DAY),30,’Completed’,’P107′,’D22′,’C01′),
(‘A504′,DATE_SUB(CURDATE(), INTERVAL 12
DAY),45,’Completed’,’P107′,’D20′,’C01′),
(‘A505′,DATE_SUB(CURDATE(), INTERVAL 18
DAY),30,’Completed’,’P107′,’D31′,’C01′),
(‘A506′,’2024-05-22 11:00:00′,60,’Completed’,’P102′,’D22′,’C01′),
(‘A507′,’2024-05-26 16:30:00′,60,’Scheduled’,’P106′,’D26′,’C06′),
(‘A508′,’2024-05-29 11:45:00′,60,’Scheduled’,’P109′,’D29′,’C09′),
(‘A509′,DATE_SUB(CURDATE(), INTERVAL 3 DAY),45,’Completed’,’P110′,’D30′,’C01′),
(‘A510′,’2024-05-20 10:00:00′,30,’Completed’,’P111′,’D31′,’C01′),
Pg. 13
(‘A511′,’2024-05-21 09:00:00′,30,’Completed’,’P102′,’D30′,’C01′),
(‘A512′,’2024-05-23 14:30:00′,30,’Completed’,’P110′,’D20′,’C01′);
— MEDICINE
INSERT INTO MEDICINE VALUES
(‘M01′,’MedX’,’Blood pressure’,’PharmaCorp’,150),
(‘M02′,’MedY’,’Pain reliever’,’BioLab’,5),
(‘M03′,’MedZ’,’Antibiotic’,’ChemiCare’,80),
(‘M04′,’VitaPlus’,’Vitamin supplement’,’HealthLife’,200),
(‘M05′,’InsuCare’,’Insulin therapy’,’MediLab’,3),
(‘M06′,’CoughFree’,’Cough syrup’,’BioWell’,90),
(‘M07′,’CardioHeal’,’Heart medicine’,’PharmaCorp’,60),
(‘M08′,’NeuroAid’,’Neurological support’,’NeuroPharm’,70),
(‘M09′,’BoneMax’,’Calcium support’,’StrongHealth’,40),
(‘M10′,’DermaSoft’,’Skin cream’,’CarePlus’,120),
(‘M11′,’AllerRelief’,’Allergy medicine’,’BioLab’,8),
(‘M12′,’FlexJoint’,’Joint pain relief’,’HealthLife’,2);
— PRESCRIPTION
INSERT INTO PRESCRIPTION VALUES
(‘PR100′,DATE_SUB(CURDATE(), INTERVAL 5 DAY),’Take after meals’,’A500′),
(‘PR101′,DATE_SUB(CURDATE(), INTERVAL 10 DAY),’1 tablet daily’,’A501′),
(‘PR102′,DATE_SUB(CURDATE(), INTERVAL 15 DAY),’For 7 days’,’A502′),
Pg. 14
(‘PR103′,DATE_SUB(CURDATE(), INTERVAL 7 DAY),’After breakfast’,’A503′),
(‘PR104′,DATE_SUB(CURDATE(), INTERVAL 12 DAY),’Apply twice daily’,’A504′),
(‘PR105′,DATE_SUB(CURDATE(), INTERVAL 18 DAY),’Before bed’,’A505′),
(‘PR106′,’2024-05-22′,’With food’,’A506′),
(‘PR107′,’2024-05-26′,’Morning dose’,’A507′),
(‘PR108′,’2024-05-29′,’After lunch’,’A508′),
(‘PR109′,DATE_SUB(CURDATE(), INTERVAL 3 DAY),’Three times daily’,’A509′),
(‘PR110′,’2024-05-20′,’Take with water’,’A510′),
(‘PR111′,’2024-05-21′,’As needed’,’A511′),
(‘PR112′,’2024-05-23′,’Daily supplement’,’A512′);
— PRESCRIPTION_LINE
INSERT INTO PRESCRIPTION_LINE VALUES
(‘PR100′,’M01′,’10mg’,’Once daily’),
(‘PR100′,’M02′,’500mg’,’Every 8 hours’),
(‘PR101′,’M03′,’250mg’,’Twice daily’),
(‘PR102′,’M04′,’1 capsule’,’Daily’),
(‘PR103′,’M05′,’5 units’,’Before meals’),
(‘PR104′,’M06′,’10ml’,’Every 6 hours’),
(‘PR105′,’M07′,’20mg’,’Once daily’),
(‘PR106′,’M08′,’2 tablets’,’Daily’),
(‘PR107′,’M09′,’1 tablet’,’Daily’),
(‘PR108′,’M10′,’Apply thin layer’,’Twice daily’),
Pg. 15
(‘PR109′,’M01′,’5mg’,’Twice daily’),
(‘PR110′,’M11′,’1 tablet’,’Daily’),
(‘PR111′,’M12′,’2 capsules’,’Three times daily’),
(‘PR112′,’M04′,’1 capsule’,’Daily’);
— BILL
INSERT INTO BILL VALUES
(‘B001′,350.00,’Paid’,DATE_SUB(CURDATE(), INTERVAL 5 DAY),’A500′),
(‘B002′,200.00,’Paid’,DATE_SUB(CURDATE(), INTERVAL 10 DAY),’A501′),
(‘B003′,400.00,’Paid’,DATE_SUB(CURDATE(), INTERVAL 15 DAY),’A502′),
(‘B004′,150.00,’Paid’,DATE_SUB(CURDATE(), INTERVAL 7 DAY),’A503′),
(‘B005′,500.00,’Paid’,DATE_SUB(CURDATE(), INTERVAL 12 DAY),’A504′),
(‘B006′,250.00,’Paid’,DATE_SUB(CURDATE(), INTERVAL 18 DAY),’A505′),
(‘B007′,300.00,’Paid’,’2024-05-22′,’A506′),
(‘B008′,200.00,’Paid’,’2024-05-26′,’A507′),
(‘B009′,450.00,’Paid’,’2024-05-29′,’A508′),
(‘B010′,600.00,’Paid’,DATE_SUB(CURDATE(), INTERVAL 3 DAY),’A509′),
(‘B011′,275.00,’Paid’,’2024-05-20′,’A510′),
(‘B012′,325.00,’Paid’,’2024-05-21′,’A511′),
(‘B013′,180.00,’Paid’,’2024-05-23′,’A512′);
Pg. 16
6. Queries and Analytics
Query 1: List patients with more than two appointments in the last month.
SELECT p.Patient_ID, p.Name, p.Phone, p.Email, COUNT(a.Appointment_ID) AS
Appointment_Count
FROM PATIENT p
JOIN APPOINTMENT a ON p.Patient_ID = a.Patient_ID
WHERE a.Date_Time BETWEEN DATE_SUB(CURDATE(), INTERVAL 1
MONTH) AND CURDATE()
GROUP BY p.Patient_ID, p.Name, p.Phone, p.Email
HAVING COUNT(a.Appointment_ID) > 2;
Pg. 17
Query 2: List all available doctors by specialty at a given clinic (e.g., in Riyadh).
SELECT d.Name, d.Specialty, d.Phone
FROM DOCTOR d
JOIN CLINIC c ON d.Clinic_ID = c.Clinic_ID
WHERE c.City = ‘Riyadh’
ORDER BY d.Specialty;
Pg. 18
Query 3: Retrieve all prescriptions issued by a specific doctor within a time frame.
SELECT pr.Prescription_ID, pr.Issue_Date, p.Name AS Patient_Name, m.Name AS
Medicine_Name, pl.Dosage, pl.Frequency
FROM PRESCRIPTION pr
JOIN APPOINTMENT a ON pr.Appointment_ID = a.Appointment_ID
JOIN DOCTOR d ON a.Doctor_ID = d.Doctor_ID
JOIN PATIENT p ON a.Patient_ID = p.Patient_ID
JOIN PRESCRIPTION_LINE pl ON pr.Prescription_ID = pl.Prescription_ID
JOIN MEDICINE m ON pl.Medicine_ID = m.Medicine_ID
WHERE d.Name=”Dr. Fatima Khan”
AND pr.Issue_Date BETWEEN ‘2024-05-01’ AND ‘2024-05-31’;
Pg. 19
Query 4: List medicines running low in stock at any location (e.g., less than 10).
SELECT Medicine_ID, Name, Stock_Quantity
FROM MEDICINE
WHERE Stock_Quantity 30;
Query 6: Produce a report of revenue generated per clinic (by location/city).
SELECT c.City, c.Name AS Clinic_Name, SUM(b.Amount) AS Total_Revenue
FROM BILL b
JOIN APPOINTMENT a ON b.Appointment_ID = a.Appointment_ID
Pg. 21
JOIN CLINIC c ON a.Clinic_ID = c.Clinic_ID
WHERE b.Status=”Paid”
GROUP BY c.City, c.Name;
7. Documentation & Security
Security Controls
1. Authentication: Password-based login with MFA for staff and doctors.
2. Authorization (RBAC): Patients, Doctors, and Admin Staff have distinct
permissions.
3. Encryption: Sensitive fields (phone, address) are encrypted at rest; all
communication uses TLS.
4. Audit Logging: Tracks logins, updates, and billing modifications.
5. Backups: Automated nightly incremental backups; weekly full backup cycle.
Role-Permission Mapping
Role
Patient
Permission
View own records
Description
Can view and manage personal profile,
appointments, and bills.
Pg. 22
Doctor
Manage
appointments
Can access patient appointments and
prescriptions.
Admin
Manage system
Can access all modules except confidential
clinical notes.
Inventory
Staff
Manage medicines
Can view and update stock levels only.
Purchase answer to see full
attachment
