aVt44F0q

1DROP TABLE IF EXISTS department CASCADE;
2DROP TABLE IF EXISTS nurse CASCADE;
3DROP TABLE IF EXISTS treatment_plan CASCADE;
4DROP TABLE IF EXISTS treatment_event CASCADE;
5DROP TABLE IF EXISTS ward CASCADE;
6DROP TABLE IF EXISTS hospital_stay CASCADE;
7DROP TABLE IF EXISTS medication CASCADE;
8DROP TABLE IF EXISTS drug CASCADE;
9DROP TABLE IF EXISTS physiotherapy CASCADE;
10DROP TABLE IF EXISTS surgery CASCADE;
11DROP TABLE IF EXISTS physician CASCADE;
12DROP TABLE IF EXISTS patient CASCADE;
13
14CREATE TABLE department (
15  dID   int8 NOT NULL, 
16  label varchar(255) NOT NULL UNIQUE, 
17  PRIMARY KEY (dID));
18CREATE TABLE ward (
19  wID          int8 NOT NULL, 
20  label        varchar(255), 
21  numberOfBeds int8 NOT NULL, 
22  PRIMARY KEY (wID));
23  
24CREATE TABLE nurse (
25  nID       	SERIAL PRIMARY KEY,
26  preName		varchar(255) NOT NULL,
27  lastName		varchar(255) NOT NULL,
28  dateOfBirth	date NOT NULL,
29  chefNurse int8);
30  
31CREATE TABLE physician (
32  phID           SERIAL PRIMARY KEY,
33  preName		varchar(255) NOT NULL,
34  lastName		varchar(255) NOT NULL,
35  dateOfBirth	date NOT NULL,
36  academicDegree varchar(255) NOT NULL, 
37  chefPhysician  int8);
38
39CREATE TABLE drug (
40  dID                     SERIAL, 
41  activeIngredient        int8, 
42  activeIngredientContent varchar(255) NOT NULL, 
43  label                   varchar(255) NOT NULL, 
44  manufacturer            varchar(255) NOT NULL, 
45  PRIMARY KEY (dID)
46  );
47
48CREATE TABLE surgery (
49  sID					SERIAL, 
50  bodyRegion          	varchar(255) NOT NULL, 
51  label               	varchar(255) NOT NULL, 
52  organ               	varchar(255) NOT NULL, 
53  surgicalForm        	varchar(255) NOT NULL,
54  PRIMARY KEY(sID)
55  );
56
57CREATE TABLE physiotherapy (
58  pID					SERIAL, 
59  duration            	varchar(255), 
60  exerciseForm        	varchar(255) NOT NULL, 
61  label               	varchar(255) NOT NULL,
62  PRIMARY KEY(pID)
63  );
64
65CREATE TABLE medication (
66  medID 					SERIAL, 
67  dosing            		varchar(255) NOT NULL, 
68  label             		varchar(255),
69  drug						int8 REFERENCES drug(dID) ON DELETE CASCADE,
70  PRIMARY KEY(medID)
71  );
72  
73CREATE TABLE treatment_event (
74  teID               	SERIAL, 
75  treatment_plantpID 	int8 NOT NULL, 
76  eventDate          	date NOT NULL, 
77  eventTime          	time NOT NULL,
78  medication			int8 REFERENCES medication(medID),
79  surgery				int8 REFERENCES surgery(sID),
80  physiotherapy			int8 REFERENCES physiotherapy(pID),
81  PRIMARY KEY (teID)
82  );
83
84CREATE TABLE treatment_plan (
85  tpID        SERIAL,
86  description varchar(255) NOT NULL, 
87  name        varchar(255) NOT NULL,
88  treatment_event	int8 REFERENCES treatment_event(teID),
89  PRIMARY KEY(tpID)
90  );
91
92CREATE TABLE patient (
93  pID             SERIAL,
94  preName			varchar(255) NOT NULL,
95  lastName			varchar(255) NOT NULL,
96  dateOfBirth		date NOT NULL,
97  insuranceNumber 	varchar(255) NOT NULL, 
98  healthInsurance 	varchar(255) NOT NULL,
99  treatment_plan	int8 REFERENCES treatment_plan(tpID),
100  PRIMARY KEY (pID)
101  );
102
103CREATE TABLE hospital_stay (
104  entryDate   	date NOT NULL, 
105  releaseDate 	date NOT NULL,
106  pID			int8 REFERENCES patient(pID));
107  
108ALTER TABLE patient ADD FOREIGN KEY (treatment_plan) REFERENCES treatment_plan;