kp3MhkC6

1ALTER TABLE POSTS_HELD DROP CONSTRAINT IF EXISTS POSTS_HELD_fk0;
2
3ALTER TABLE POSTS_HELD DROP CONSTRAINT IF EXISTS POSTS_HELD_fk1;
4
5ALTER TABLE PAYMENT DROP CONSTRAINT IF EXISTS PAYMENT_fk0;
6
7ALTER TABLE PAYMENT DROP CONSTRAINT IF EXISTS PAYMENT_fk1;
8
9ALTER TABLE PAYMENT DROP CONSTRAINT IF EXISTS PAYMENT_fk2;
10
11ALTER TABLE EMPLOYEE_PROJECT DROP CONSTRAINT IF EXISTS EMPLOYEE_PROJECT_fk0;
12
13ALTER TABLE EMPLOYEE_PROJECT DROP CONSTRAINT IF EXISTS EMPLOYEE_PROJECT_fk1;
14
15ALTER TABLE EMPLOYEE_PROJECT DROP CONSTRAINT IF EXISTS EMPLOYEE_PROJECT_fk2;
16
17DROP TABLE IF EXISTS EMPLOYEE;
18
19DROP TABLE IF EXISTS POSTS_HELD;
20
21DROP TABLE IF EXISTS POST;
22
23DROP TABLE IF EXISTS PROJECT;
24
25DROP TABLE IF EXISTS PAYMENT_POLICY;
26
27DROP TABLE IF EXISTS PAYMENT;
28
29DROP TABLE IF EXISTS EMPLOYEE_PROJECT;
30
31DROP TABLE IF EXISTS PROJECT_ROLE;
32
33CREATE TABLE EMPLOYEE (
34                          "id" serial NOT NULL,
35                          "last_name" TEXT NOT NULL,
36                          "first_name" TEXT NOT NULL,
37                          "middle_name" TEXT NOT NULL,
38                          "birth_date" DATE NOT NULL,
39                          "home_address" TEXT NOT NULL,
40                          "education" TEXT NOT NULL,
41                          "experience" bigint NOT NULL,
42                          CONSTRAINT EMPLOYEE_pk PRIMARY KEY ("id")
43) WITH (
44      OIDS=FALSE
45    );
46
47
48
49CREATE TABLE POSTS_HELD (
50                            "id" serial NOT NULL,
51                            "employee_id" bigint NOT NULL,
52                            "post_id" bigint NOT NULL,
53                            "from_date" DATE NOT NULL,
54                            CONSTRAINT POSTS_HELD_pk PRIMARY KEY ("id")
55) WITH (
56      OIDS=FALSE
57    );
58
59
60
61CREATE TABLE POST (
62                      "id" serial NOT NULL,
63                      "post_name" TEXT NOT NULL,
64                      CONSTRAINT POST_pk PRIMARY KEY ("id")
65) WITH (
66      OIDS=FALSE
67    );
68
69
70
71CREATE TABLE PROJECT (
72                         "id" serial NOT NULL,
73                         "title" TEXT NOT NULL,
74                         "from_date" DATE NOT NULL,
75                         "to_date" DATE NOT NULL,
76                         CONSTRAINT PROJECT_pk PRIMARY KEY ("id")
77) WITH (
78      OIDS=FALSE
79    );
80
81
82
83CREATE TABLE PAYMENT_POLICY (
84                                "id" serial NOT NULL,
85                                "policy_name" TEXT NOT NULL,
86                                "payment_size" TEXT NOT NULL,
87                                "policy_description" TEXT NOT NULL,
88                                CONSTRAINT PAYMENT_POLICY_pk PRIMARY KEY ("id")
89) WITH (
90      OIDS=FALSE
91    );
92
93
94
95CREATE TABLE PAYMENT (
96                         "id" serial NOT NULL,
97                         "employee_id" bigint NOT NULL,
98                         "policy_id" bigint NOT NULL,
99                         "project_id" bigint NOT NULL,
100                         "payment_date" DATE NOT NULL,
101                         CONSTRAINT PAYMENT_pk PRIMARY KEY ("id")
102) WITH (
103      OIDS=FALSE
104    );
105
106
107
108CREATE TABLE EMPLOYEE_PROJECT (
109                                  "id" serial NOT NULL,
110                                  "employee_id" bigint NOT NULL,
111                                  "project_id" bigint NOT NULL,
112                                  "role_id" bigint NOT NULL,
113                                  CONSTRAINT EMPLOYEE_PROJECT_pk PRIMARY KEY ("id")
114) WITH (
115      OIDS=FALSE
116    );
117
118
119
120CREATE TABLE PROJECT_ROLE (
121                              "id" serial NOT NULL,
122                              "role_name" TEXT NOT NULL,
123                              CONSTRAINT PROJECT_ROLE_pk PRIMARY KEY ("id")
124) WITH (
125      OIDS=FALSE
126    );
127
128
129
130
131ALTER TABLE POSTS_HELD ADD CONSTRAINT POSTS_HELD_fk0 FOREIGN KEY ("employee_id") REFERENCES EMPLOYEE("id") on update cascade on delete cascade;
132ALTER TABLE POSTS_HELD ADD CONSTRAINT POSTS_HELD_fk1 FOREIGN KEY ("post_id") REFERENCES POST("id") on update cascade on delete cascade;
133
134
135
136
137ALTER TABLE PAYMENT ADD CONSTRAINT PAYMENT_fk0 FOREIGN KEY ("employee_id") REFERENCES EMPLOYEE("id") on update cascade on delete cascade;
138ALTER TABLE PAYMENT ADD CONSTRAINT PAYMENT_fk1 FOREIGN KEY ("policy_id") REFERENCES PAYMENT_POLICY("id") on update cascade on delete cascade;
139ALTER TABLE PAYMENT ADD CONSTRAINT PAYMENT_fk2 FOREIGN KEY ("project_id") REFERENCES PROJECT("id") on update cascade on delete cascade;
140
141ALTER TABLE EMPLOYEE_PROJECT ADD CONSTRAINT EMPLOYEE_PROJECT_fk0 FOREIGN KEY ("employee_id") REFERENCES EMPLOYEE("id") on update cascade on delete cascade;
142ALTER TABLE EMPLOYEE_PROJECT ADD CONSTRAINT EMPLOYEE_PROJECT_fk1 FOREIGN KEY ("project_id") REFERENCES PROJECT("id") on update cascade on delete cascade;
143ALTER TABLE EMPLOYEE_PROJECT ADD CONSTRAINT EMPLOYEE_PROJECT_fk2 FOREIGN KEY ("role_id") REFERENCES PROJECT_ROLE("id") on update cascade on delete cascade;