hospitalPractice

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)
	);