forked from Manzikevin/Database-Programming-Group-Assignment
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy path01_schema_and_data.sql
More file actions
166 lines (154 loc) · 6.9 KB
/
Copy path01_schema_and_data.sql
File metadata and controls
166 lines (154 loc) · 6.9 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
-- ============================================================
-- HOSPITAL MANAGEMENT SYSTEM DATABASE
-- Database Programming - Group Assignment I
-- Engine: MySQL 8.0+
-- ============================================================
-- This script creates the schema and loads sample data for a
-- Hospital Management System with 5 related tables:
-- departments, doctors, patients, appointments, billing
-- ============================================================
DROP DATABASE IF EXISTS hospital_management;
CREATE DATABASE hospital_management;
USE hospital_management;
-- ------------------------------------------------------------
-- Table: departments
-- ------------------------------------------------------------
CREATE TABLE departments (
department_id INT AUTO_INCREMENT PRIMARY KEY,
department_name VARCHAR(100) NOT NULL,
location VARCHAR(100)
);
-- ------------------------------------------------------------
-- Table: doctors
-- supervisor_id is a self-referencing foreign key used to model
-- the reporting hierarchy (needed for the Recursive CTE example)
-- ------------------------------------------------------------
CREATE TABLE doctors (
doctor_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
specialization VARCHAR(100),
department_id INT,
supervisor_id INT DEFAULT NULL,
salary DECIMAL(10,2),
hire_date DATE,
CONSTRAINT fk_doctor_department FOREIGN KEY (department_id)
REFERENCES departments(department_id),
CONSTRAINT fk_doctor_supervisor FOREIGN KEY (supervisor_id)
REFERENCES doctors(doctor_id)
);
-- ------------------------------------------------------------
-- Table: patients
-- ------------------------------------------------------------
CREATE TABLE patients (
patient_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
date_of_birth DATE,
gender ENUM('Male','Female','Other'),
phone VARCHAR(20)
);
-- ------------------------------------------------------------
-- Table: appointments
-- ------------------------------------------------------------
CREATE TABLE appointments (
appointment_id INT AUTO_INCREMENT PRIMARY KEY,
patient_id INT NOT NULL,
doctor_id INT NOT NULL,
department_id INT NOT NULL,
appointment_date DATE NOT NULL,
status ENUM('Scheduled','Completed','Cancelled') DEFAULT 'Scheduled',
CONSTRAINT fk_appt_patient FOREIGN KEY (patient_id)
REFERENCES patients(patient_id),
CONSTRAINT fk_appt_doctor FOREIGN KEY (doctor_id)
REFERENCES doctors(doctor_id),
CONSTRAINT fk_appt_department FOREIGN KEY (department_id)
REFERENCES departments(department_id)
);
-- ------------------------------------------------------------
-- Table: billing
-- ------------------------------------------------------------
CREATE TABLE billing (
bill_id INT AUTO_INCREMENT PRIMARY KEY,
appointment_id INT NOT NULL,
amount DECIMAL(10,2) NOT NULL,
payment_status ENUM('Paid','Pending') DEFAULT 'Pending',
bill_date DATE NOT NULL,
CONSTRAINT fk_bill_appointment FOREIGN KEY (appointment_id)
REFERENCES appointments(appointment_id)
);
-- ============================================================
-- SAMPLE DATA
-- ============================================================
-- Departments
INSERT INTO departments (department_name, location) VALUES
('Cardiology', 'Block A - Floor 2'),
('Neurology', 'Block A - Floor 3'),
('Pediatrics', 'Block B - Floor 1'),
('Orthopedics', 'Block B - Floor 2'),
('General Medicine', 'Block A - Floor 1');
-- Doctors (with reporting hierarchy via supervisor_id)
INSERT INTO doctors (first_name, last_name, specialization, department_id, supervisor_id, salary, hire_date) VALUES
('Alice', 'Uwimana', 'Chief Medical Officer', 5, NULL, 4500000, '2015-02-01'),
('Jean', 'Bosco', 'Head of Cardiology', 1, 1, 3200000, '2016-04-10'),
('Marie Claire', 'Nyirahabimana', 'Cardiologist', 1, 2, 2400000, '2019-06-15'),
('Eric', 'Nsengiyumva', 'Cardiologist', 1, 2, 2300000, '2020-01-20'),
('Grace', 'Mukamana', 'Head of Neurology', 2, 1, 3100000, '2016-09-05'),
('Patrick', 'Habimana', 'Neurologist', 2, 5, 2350000, '2019-03-11'),
('Diane', 'Umutoni', 'Head of Pediatrics', 3, 1, 3000000, '2017-05-18'),
('Emmanuel', 'Rugamba', 'Pediatrician', 3, 7, 2100000, '2021-02-01'),
('Josiane', 'Ingabire', 'Head of Orthopedics', 4, 1, 3050000, '2017-11-23'),
('Claude', 'Mugisha', 'Orthopedic Surgeon', 4, 9, 2450000, '2020-07-30');
-- Patients
INSERT INTO patients (first_name, last_name, date_of_birth, gender, phone) VALUES
('Aline', 'Umuhoza', '1990-03-12', 'Female', '0788111001'),
('Bosco', 'Niyonzima', '1985-07-24', 'Male', '0788111002'),
('Chantal', 'Uwase', '1998-11-02', 'Female', '0788111003'),
('David', 'Mugabo', '1975-01-30', 'Male', '0788111004'),
('Esperance', 'Nyiraneza', '2001-05-19', 'Female', '0788111005'),
('Felix', 'Rukundo', '1993-09-08', 'Male', '0788111006'),
('Gisele', 'Mukashema', '1988-12-15', 'Female', '0788111007'),
('Hakizimana', 'Jean Paul', '1979-04-27', 'Male', '0788111008'),
('Immaculee', 'Ingabire', '1995-06-03', 'Female', '0788111009'),
('Jules', 'Nkurunziza', '1982-10-11', 'Male', '0788111010'),
('Kevine', 'Uwamahoro', '2000-02-14', 'Female', '0788111011'),
('Leon', 'Byiringiro', '1991-08-21', 'Male', '0788111012');
-- Appointments
INSERT INTO appointments (patient_id, doctor_id, department_id, appointment_date, status) VALUES
(1, 3, 1, '2026-05-02', 'Completed'),
(2, 3, 1, '2026-05-04', 'Completed'),
(3, 4, 1, '2026-05-05', 'Completed'),
(4, 6, 2, '2026-05-06', 'Completed'),
(5, 6, 2, '2026-05-07', 'Completed'),
(6, 8, 3, '2026-05-08', 'Completed'),
(7, 8, 3, '2026-05-10', 'Completed'),
(8, 10, 4, '2026-05-11', 'Completed'),
(9, 10, 4, '2026-05-12', 'Completed'),
(10, 3, 1, '2026-05-14', 'Completed'),
(11, 4, 1, '2026-05-15', 'Completed'),
(12, 6, 2, '2026-05-16', 'Completed'),
(1, 8, 3, '2026-05-18', 'Completed'),
(2, 10, 4, '2026-05-19', 'Completed'),
(3, 3, 1, '2026-05-20', 'Scheduled'),
(4, 6, 2, '2026-06-01', 'Scheduled'),
(5, 4, 1, '2026-06-02', 'Scheduled'),
(6, 10, 4, '2026-06-03', 'Cancelled'),
(7, 8, 3, '2026-06-04', 'Scheduled'),
(9, 3, 1, '2026-06-05', 'Completed');
-- Billing (only for appointments that took place / were completed)
INSERT INTO billing (appointment_id, amount, payment_status, bill_date) VALUES
(1, 45000, 'Paid', '2026-05-02'),
(2, 52000, 'Paid', '2026-05-04'),
(3, 38000, 'Pending', '2026-05-05'),
(4, 61000, 'Paid', '2026-05-06'),
(5, 47000, 'Paid', '2026-05-07'),
(6, 29000, 'Paid', '2026-05-08'),
(7, 31000, 'Pending', '2026-05-10'),
(8, 72000, 'Paid', '2026-05-11'),
(9, 68000, 'Paid', '2026-05-12'),
(10, 40000, 'Paid', '2026-05-14'),
(11, 55000, 'Pending', '2026-05-15'),
(12, 33000, 'Paid', '2026-05-16'),
(13, 27000, 'Paid', '2026-05-18'),
(14, 81000, 'Paid', '2026-05-19'),
(20, 44000, 'Paid', '2026-06-05');