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
|
\echo '--- Question 4 ---'
\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),
address varchar(255),
birth_date timestamp,
phone_number varchar(255)
);
CREATE TABLE employees(
id bigint primary key,
person_id bigint references people(id),
"type" varchar(255),
hired_at timestamp
);
CREATE TABLE nurses(
id bigint primary key,
employee_id bigint references employees(id),
certificate text
);
CREATE TABLE care_centres(
id bigint primary key,
name varchar(255),
location text,
nurse_in_charge_id bigint references nurses(id)
);
CREATE TABLE timesheets(
id bigint primary key,
employee_id bigint references employees(id),
care_centre_id bigint references care_centres(id),
hours integer,
week integer
);
CREATE TABLE physicians(
id bigint primary key,
employee_id bigint references employees(id),
specialty text,
pager_number varchar(255)
);
CREATE TABLE patients(
id bigint primary key,
person_id bigint references people(id),
physician_id bigint references physicians(id),
referring_physician_id bigint references physicians(id),
contacted_at timestamp
);
CREATE TABLE consumptions(
id bigint primary key,
physican_id bigint references physicians(id),
patient_id bigint references patients(id),
item_id bigint,
consumed_at timestamp,
quantity integer,
total_cost decimal
);
CREATE TABLE items(
id bigint primary key,
number integer,
description text,
unit_cost decimal
);
CREATE TABLE beds(
id bigint primary key,
care_centre_id bigint references care_centres(id),
bed_number integer,
room_number integer
);
CREATE TABLE laboratories(
id bigint primary key,
location text
);
CREATE TABLE staff(
id bigint primary key,
employee_id bigint references employees(id),
job_class varchar(255)
);
CREATE TABLE technicians(
id bigint primary key,
employee_id bigint references employees(id),
skill varchar(255)
);
CREATE TABLE treatments(
id bigint primary key,
physician_id bigint references physicians(id),
patient_id bigint references patients(id),
number integer,
name varchar(255),
occurred_at timestamp,
results text
);
CREATE TABLE visits(
id bigint primary key,
patient_id bigint references patients(id),
physician_id bigint references physicians(id),
comments text,
scheduled_at timestamp
);
|
