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
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
|
\echo '--- Module 2 ---'
\set ON_ERROR_STOP on
-- DROP TABLE IF EXISTS beds;
-- DROP TABLE IF EXISTS consumptions;
-- DROP TABLE IF EXISTS items;
-- DROP TABLE IF EXISTS laboratories;
-- DROP TABLE IF EXISTS staff;
-- DROP TABLE IF EXISTS technicians;
-- DROP TABLE IF EXISTS timesheets;
-- DROP TABLE IF EXISTS treatments;
-- DROP TABLE IF EXISTS visits;
-- DROP TABLE IF EXISTS patients;
-- DROP TABLE IF EXISTS physicians;
-- DROP TABLE IF EXISTS care_centres;
-- DROP TABLE IF EXISTS nurses;
-- DROP TABLE IF EXISTS employees;
-- DROP TABLE IF EXISTS people;
-- CREATE TABLE people(
-- id bigint primary key,
-- name varchar(255) NOT NULL,
-- address varchar(255),
-- birth_date timestamp,
-- phone_number varchar(255)
-- );
-- CREATE TABLE employees(
-- id bigint primary key,
-- person_id bigint references people(id) NOT NULL UNIQUE,
-- hired_at timestamp NOT NULL
-- );
-- CREATE TABLE nurses(
-- id bigint primary key,
-- employee_id bigint references employees(id) NOT NULL UNIQUE,
-- certificate text
-- );
-- CREATE TABLE care_centres(
-- id bigint primary key,
-- name varchar(255),
-- location text,
-- nurse_in_charge_id bigint references nurses(id) NOT NULL
-- );
-- CREATE TABLE timesheets(
-- id bigint primary key,
-- employee_id bigint references employees(id) NOT NULL,
-- care_centre_id bigint references care_centres(id) NOT NULL,
-- hours integer,
-- week integer
-- );
-- CREATE TABLE physicians(
-- id bigint primary key,
-- employee_id bigint references employees(id) NOT NULL UNIQUE,
-- specialty text,
-- pager_number varchar(255)
-- );
-- CREATE TABLE patients(
-- id bigint primary key,
-- person_id bigint references people(id) NOT NULL UNIQUE,
-- physician_id bigint references physicians(id),
-- referring_physician_id bigint references physicians(id) NOT NULL,
-- contacted_at timestamp
-- );
-- CREATE TABLE items(
-- id bigint primary key,
-- number integer,
-- description text,
-- unit_cost decimal
-- );
-- CREATE TABLE consumptions(
-- id bigint primary key,
-- physican_id bigint references physicians(id) NOT NULL,
-- patient_id bigint references patients(id) NOT NULL,
-- item_id bigint references items(id) NOT NULL,
-- consumed_at timestamp,
-- quantity integer,
-- total_cost decimal
-- );
-- CREATE TABLE beds(
-- id bigint primary key,
-- care_centre_id bigint references care_centres(id),
-- bed_number integer NOT NULL,
-- room_number integer NOT NULL
-- );
-- CREATE TABLE laboratories(
-- id bigint primary key,
-- location text
-- );
-- CREATE TABLE staff(
-- id bigint primary key,
-- employee_id bigint references employees(id) NOT NULL UNIQUE,
-- job_class varchar(255)
-- );
-- CREATE TABLE technicians(
-- id bigint primary key,
-- employee_id bigint references employees(id) NOT NULL UNIQUE,
-- skill varchar(255)
-- );
-- CREATE TABLE treatments(
-- id bigint primary key,
-- physician_id bigint references physicians(id) NOT NULL,
-- patient_id bigint references patients(id) NOT NULL,
-- number integer,
-- name varchar(255),
-- occurred_at timestamp NOT NULL,
-- results text
-- );
-- CREATE TABLE visits(
-- id bigint primary key,
-- patient_id bigint references patients(id) NOT NULL,
-- physician_id bigint references physicians(id) NOT NULL,
-- comments text,
-- scheduled_at timestamp NOT NULL
-- );
-- -- dr mo
-- INSERT INTO people(id, name) VALUES (1, 'dr. mo');
-- INSERT INTO employees(id, person_id, hired_at) VALUES (1, 1, '2004-06-15');
-- INSERT INTO physicians(id, employee_id) VALUES (1, 1);
-- -- dr allison
-- INSERT INTO people(id, name) VALUES (2, 'dr. allison');
-- INSERT INTO employees(id, person_id, hired_at) VALUES (2, 2, '2010-01-01');
-- INSERT INTO physicians(id, employee_id) VALUES (2, 2);
-- -- dr didi
-- INSERT INTO people(id, name) VALUES (3, 'dr. didi');
-- INSERT INTO employees(id, person_id, hired_at) VALUES (3, 3, '2020-01-01');
-- INSERT INTO physicians(id, employee_id) VALUES (3, 3);
-- -- dr nini
-- INSERT INTO people(id, name) VALUES (4, 'dr. nini');
-- INSERT INTO employees(id, person_id, hired_at) VALUES (4, 4, '2020-01-01');
-- INSERT INTO physicians(id, employee_id) VALUES (4, 4);
-- -- moe sislack
-- INSERT INTO people(id, name) VALUES (10, 'moe sislack');
-- INSERT INTO patients(id, person_id, referring_physician_id) VALUES (10, 10, 1);
-- -- mose allison
-- INSERT INTO people(id, name) VALUES (11, 'mose allison');
-- INSERT INTO patients(id, person_id, referring_physician_id) VALUES (11, 11, 2);
-- -- didi kong
-- INSERT INTO people(id, name) VALUES (12, 'didi kong');
-- INSERT INTO patients(id, person_id, referring_physician_id) VALUES (12, 12, 1);
-- INSERT INTO treatments(id, physician_id, patient_id, name, occurred_at) VALUES (1, 1, 10, 'heart surgery', '2020-03-01');
-- INSERT INTO treatments(id, physician_id, patient_id, name, occurred_at) VALUES (2, 2, 11, 'knee surgery', '2020-03-02');
-- INSERT INTO treatments(id, physician_id, patient_id, name, occurred_at) VALUES (3, 1, 12, 'brain surgery', '2020-03-02');
-- INSERT INTO treatments(id, physician_id, patient_id, name, occurred_at) VALUES (4, 1, 10, 'foot surgery', '2020-03-02');
-- INSERT INTO treatments(id, physician_id, patient_id, name, occurred_at) VALUES (5, 1, 10, 'knee surgery', '2020-03-02');
-- SELECT pe.name as physician, t.name as treatment, t.occurred_at
-- FROM treatments t
-- INNER JOIN physicians p on p.id = t.physician_id
-- INNER JOIN employees e on e.id = p.employee_id
-- INNER JOIN people pe on pe.id = e.person_id
-- WHERE t.occurred_at BETWEEN CURRENT_DATE - INTERVAL '14 days' AND CURRENT_DATE
-- ;
-- ORDER BY t.id ASC, t.occurred_at DESC;
\echo '--- Module 3 ---'
ALTER TABLE IF EXISTS Nurses DROP CONSTRAINT care_centres_fk;
DROP TABLE IF EXISTS Treatments;
DROP TABLE IF EXISTS Physicians;
DROP TABLE IF EXISTS Patients;
DROP TABLE IF EXISTS Care_centres;
DROP TABLE IF EXISTS Nurses;
CREATE TABLE Nurses(
nid bigint primary key,
name varchar(255),
care_centre_id bigint,
certificate_type varchar(255),
telephone varchar(255),
salary decimal
);
CREATE TABLE Care_centres(
cid bigint primary key,
name varchar(255),
location text,
nurse_charge_id bigint references Nurses(nid)
);
CREATE TABLE Patients(
pid bigint primary key,
name varchar(255),
address text,
telephone varchar(255),
care_centre_id bigint references Care_centres(cid)
);
CREATE TABLE Physicians(
phid bigint primary key,
name varchar(255),
pager_number varchar(255),
specialization varchar(255),
salary decimal
);
CREATE TABLE Treatments(
tid bigint primary key,
patient_id bigint references Patients(pid),
physician_id bigint references Physicians(phid),
treatment_name varchar(255),
"date" timestamp
);
ALTER TABLE Nurses ADD CONSTRAINT care_centres_fk FOREIGN KEY (care_centre_id) REFERENCES Care_centres (cid);
INSERT INTO Care_centres VALUES(1, 'A', 'Earth');
INSERT INTO Nurses VALUES (1, 'Sharp', 1, 'birth', '111-222-3333', 100000);
INSERT INTO Physicians VALUES (1, 'dr. mo', '555-555-5555', 'love', 10000000);
INSERT INTO Physicians VALUES (2, 'dr. allison', '555-555-0001', 'head aches', 10000000);
INSERT INTO Physicians VALUES (3, 'dr. didi', '555-555-0002', 'foot pain', 10000000);
INSERT INTO Physicians VALUES (4, 'dr. didi', '555-555-0003', 'back pain', 10000000);
INSERT INTO Patients VALUES (1, 'moe sislack', '123 street nw', '777-777-7777', 1);
INSERT INTO Patients VALUES (2, 'mose allison', '234 street nw', '888-888-8888', 1);
INSERT INTO Patients VALUES (3, 'didi kong', '345 street nw', '999-999-9999', 1);
|
