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
create table person (
person_type varchar(1) not null check ( person_type = 'V' or person_type = 'D' or person_type = 'A' or person_type = 'N' or person_type = 'T' or person_type = 'I' or person_type = 'O' ),
person_first_name varchar(32) not null,
person_last_name varchar(32) not null,
doctor_privilege varchar(1),
patient_id integer(20) unique,
room_id integer(2),
emergency_contact_name varchar(64),
emergency_contact_number integer(11),
insurance_policy_number varchar(32),
insurance_policy_company varchar(32),
patient_doctor_last_name varchar(32),
admission_date date,
discharge_date date,
person_id integer primary key autoincrement default 000000000
);
create table employee (
employee_type varchar(1) not null check ( employee_type = 'V' or employee_type = 'D' or employee_type = 'A' or employee_type = 'N' or employee_type = 'T' ),
employee_first_name varchar(32) not null,
employee_last_name varchar(32) not null,
person_id integer not null unique,
employee_id integer unique primary key autoincrement default 000000,
foreign key (person_id) references person(person_id)
);
create table technician (
employee_type varchar(1) not null check ( employee_type = 'T'),
technician_first_name varchar(32) not null,
technician_last_name varchar(32) not null,
employee_id integer primary key unique,
person_id integer not null unique,
foreign key (employee_id) references employee(employee_id),
foreign key (person_id) references person(person_id)
);
create table nurse (
employee_type varchar(1) not null check ( employee_type = 'N'),
nurse_first_name varchar(32) not null,
nurse_last_name varchar(32) not null,
employee_id integer primary key unique,
person_id integer not null unique,
foreign key (employee_id) references employee(employee_id),
foreign key (person_id) references person(person_id)
);
create table administrator (
employee_type varchar(1) not null check ( employee_type = 'A'),
administrator_first_name varchar(32) not null,
administrator_last_name varchar(32) not null,
employee_id integer primary key unique,
person_id integer not null unique,
foreign key (employee_id) references employee(employee_id),
foreign key (person_id) references person(person_id)
);
create table doctor (
employee_type varchar(1) not null check ( employee_type = 'D'),
doctor_first_name varchar(32) not null,
doctor_last_name varchar(32) not null,
doctor_privilege varchar(1) not null check ( doctor_privilege = 'C' or doctor_privilege = 'A' ),
employee_id integer primary key unique,
person_id integer not null unique,
foreign key (employee_id) references employee(employee_id),
foreign key (person_id) references person(person_id)
);
create table volunteer (
employee_type varchar(1) not null check ( employee_type = 'V'),
volunteer_first_name varchar(32) not null,
volunteer_last_name varchar(32) not null,
employee_id integer primary key unique,
person_id integer not null unique,
foreign key (employee_id) references employee(employee_id),
foreign key (person_id) references person(person_id)
);
create table out_patient (
patient_type varchar(1) not null check ( patient_type = 'O'),
patient_first_name varchar(32) not null,
patient_last_name varchar(32) not null unique,
patient_id integer(20) not null primary key,
patient_doctor_last_name varchar not null,
person_id integer not null unique,
foreign key (patient_id) references person(patient_id),
foreign key (person_id) references person(person_id)
);
create table in_patient (
patient_type varchar(1) not null check ( patient_type = 'I'),
patient_first_name varchar(32) not null,
patient_last_name varchar(32) not null unique,
patient_id integer(20) not null primary key,
room_id integer(2) not null check (room_id > 0 and room_id < 21 ),
emergency_contact_name varchar(64) not null,
emergency_contact_number integer(11) not null,
insurance_policy_number varchar(32) not null,
insurance_policy_company varchar(32) not null,
patient_doctor_last_name varchar(32) not null,
admission_date date not null,
discharge_date date,
person_id integer not null unique,
foreign key (patient_id) references person(patient_id),
foreign key (person_id) references person(person_id)
);
create table patient (
patient_type varchar(1) not null check ( patient_type = 'I' or patient_type = 'O' ),
patient_first_name varchar(32) not null,
patient_last_name varchar(32) not null unique,
patient_id integer(20) not null unique primary key,
patient_doctor_last_name varchar(32) not null,
person_id integer not null unique,
foreign key (patient_id) references person(patient_id),
foreign key (person_id) references person(person_id)
);