· 6 years ago · Oct 11, 2019, 11:52 AM
1create schema kek;
2
3alter schema kek owner to postgres;
4
5create table if not exists landalord
6(
7 id serial not null
8 constraint landalord_pk
9 primary key,
10 first_name varchar(256),
11 last_name varchar(256),
12 phone_number varchar(256),
13 email varchar(256)
14);
15
16alter table landalord owner to postgres;
17
18create table if not exists price_list
19(
20 id serial not null
21 constraint price_list_pk
22 primary key,
23 price_week01 numeric,
24 price_week02 numeric,
25 price_week03 numeric,
26 price_week04 numeric,
27 price_week05 numeric,
28 price_week06 numeric,
29 price_week07 numeric,
30 price_week08 numeric,
31 price_week09 numeric,
32 price_week10 numeric,
33 price_week11 numeric,
34 price_week12 numeric,
35 price_week13 numeric,
36 price_week14 numeric,
37 price_week15 numeric,
38 price_week16 numeric,
39 price_week17 numeric,
40 price_week18 numeric,
41 price_week19 numeric,
42 price_week20 numeric,
43 price_week21 numeric,
44 price_week22 numeric,
45 price_week23 numeric,
46 price_week24 numeric,
47 price_week25 numeric,
48 price_week26 numeric,
49 price_week27 numeric,
50 price_week28 numeric,
51 price_week29 numeric,
52 price_week30 numeric,
53 price_week31 numeric,
54 price_week32 numeric,
55 price_week33 numeric,
56 price_week34 numeric,
57 price_week35 numeric,
58 price_week36 numeric,
59 price_week37 numeric,
60 price_week38 numeric,
61 price_week39 numeric,
62 price_week40 numeric,
63 price_week41 numeric,
64 price_week42 numeric,
65 price_week43 numeric,
66 price_week44 numeric,
67 price_week45 numeric,
68 price_week46 numeric,
69 price_week47 numeric,
70 price_week48 numeric,
71 price_week49 numeric,
72 price_week50 numeric,
73 price_week51 numeric,
74 price_week52 numeric,
75 price_week53 numeric
76);
77
78alter table price_list owner to postgres;
79
80create table if not exists comfort_list
81(
82 id serial not null
83 constraint comfort_list_pk
84 primary key,
85 wifi boolean,
86 kettle boolean
87);
88
89alter table comfort_list owner to postgres;
90
91create table if not exists country_commission
92(
93 id serial not null
94 constraint country_commission_pk
95 primary key,
96 country varchar(256) not null,
97 commission integer not null
98);
99
100alter table country_commission owner to postgres;
101
102create table if not exists housing_for_rent
103(
104 id serial not null
105 constraint housing_for_rent_pk
106 primary key,
107 country_commission_id integer
108 constraint housing_for_rent_country_commission_id_fk
109 references country_commission,
110 address varchar(256),
111 gps_x integer,
112 gps_y integer,
113 description varchar(256),
114 max_people integer,
115 landlord_id integer
116 constraint housing_for_rent_landalord_id_fk
117 references landalord,
118 cleaning_price integer,
119 price_list_id integer
120 constraint housing_for_rent_price_list_id_fk
121 references price_list,
122 comfort_list_id integer
123 constraint housing_for_rent_comfort_list_id_fk
124 references comfort_list
125);
126
127alter table housing_for_rent owner to postgres;
128
129create unique index if not exists country_commission_country_uindex
130 on country_commission (country);
131
132create table if not exists tenant
133(
134 id serial not null
135 constraint tenant_pk
136 primary key,
137 first_name varchar(256) not null,
138 last_name varchar(256) not null,
139 email varchar(256) not null,
140 phone_number varchar(256) not null,
141 gender varchar(256),
142 date_of_birth date,
143 photo_url varchar(256)
144);
145
146alter table tenant owner to postgres;
147
148create table if not exists fun_activities
149(
150 id serial not null
151 constraint fun_activities_pk
152 primary key,
153 gps_x integer,
154 gps_y integer,
155 start date,
156 "end" date,
157 genre_id integer
158);
159
160alter table fun_activities owner to postgres;
161
162create table if not exists activity_genre
163(
164 id serial not null
165 constraint activity_genre_pk
166 primary key,
167 name varchar(256) not null
168);
169
170alter table activity_genre owner to postgres;
171
172create table if not exists fun_activity
173(
174 id serial not null
175 constraint fun_activity_pk
176 primary key,
177 gps_x integer,
178 gps_y date,
179 start_date date,
180 end_date date,
181 activity_genre_id integer
182 constraint fun_activity_activity_genre_id_fk
183 references activity_genre
184);
185
186alter table fun_activity owner to postgres;
187
188create table if not exists housing_request
189(
190 id serial not null
191 constraint housing_request_pk
192 primary key,
193 tenant_id integer not null
194 constraint housing_request_tenant_id_fk
195 references tenant,
196 housing_for_rent_id integer not null
197 constraint housing_request_housing_for_rent_id_fk
198 references housing_for_rent,
199 from_date date not null,
200 to_date date not null,
201 status varchar(256) not null
202);
203
204alter table housing_request owner to postgres;
205
206create table if not exists tenant_comment
207(
208 id serial not null
209 constraint tenant_comment_pk
210 primary key,
211 tenant_id integer
212 constraint tenant_comment_tenant_id_fk
213 references tenant,
214 housing_for_rent_id integer
215 constraint tenant_comment_housing_for_rent_id_fk
216 references housing_for_rent,
217 text varchar(1024),
218 location_grade integer,
219 cleaning_grade integer,
220 landlord_grade integer,
221 landlord_id integer
222 constraint tenant_comment_landalord_id_fk
223 references landalord
224);
225
226alter table tenant_comment owner to postgres;
227
228create table if not exists landlord_comment
229(
230 id serial not null
231 constraint landlord_comment_pk
232 primary key,
233 landlord_id integer
234 constraint landlord_comment_landalord_id_fk
235 references landalord,
236 tenant_id integer
237 constraint landlord_comment_tenant_id_fk
238 references tenant,
239 text varchar(256),
240 grade integer
241);
242
243alter table landlord_comment owner to postgres;