EHLwU2eU

1DROP TABLE IF EXISTS Users CASCADE;
2DROP TABLE IF EXISTS Apartments CASCADE;
3DROP TABLE IF EXISTS Countries CASCADE;
4DROP TABLE IF EXISTS Applications CASCADE;
5DROP TABLE IF EXISTS OwnerReviews CASCADE;
6DROP TABLE IF EXISTS ClientReview CASCADE;
7DROP TABLE IF EXISTS Entertainment CASCADE;
8
9CREATE TYPE gender_type AS ENUM ('man', 'woman');
10CREATE TYPE apartments_feature AS ENUM (
11  'wi-fi', 'iron', 'kitchen_appliances',
12  'kettle', 'smoking_allowed', 'pets_allowed'
13);
14CREATE TYPE entertainment_genre AS ENUM (
15  'sport', 'jazz'
16)
17CREATE TABLE Users (
18  id SERIAL PRIMARY KEY,
19  user_name VARCHAR(40) UNIQUE NOT NULL,
20  name VARCHAR(40) NOT NULL,
21  surname VARCHAR(40) NOT NULL,
22  email VARCHAR(40) UNIQUE NOT NULL,
23  phone_number VARCHAR(40) NOT NULL,
24  gender gender_type,
25  birthday DATE,
26  photo BYTEA
27);
28
29CREATE TABLE Apartments (
30  id SERIAL PRIMARY KEY,
31  user_id INT REFERENCES Users (id) NOT NULL,
32  country_id INT REFERENCES Countries (id) NOT NULL,
33  other_address_components VARCHAR(100) NOT NULL,
34  gps POINT NOT NULL,
35  name VARCHAR(80) NOT NULL,
36  rooms_number INT NOT NULL CHECK(rooms_number >= 0),
37  beds_number INT NOT NULL CHECK(beds_number >= 0),
38  max_residents INT NOT NULL CHECK(max_residents > 0),
39  additional_information apartments_feature[] NOT NULL,
40  price MONEY[] CHECK(array_length(price, 1) == 52 || array_length(price, 1) == 53),
41  cleaning_price MONEY CHECK(cleaning_price >= 0)
42);
43
44CREATE TABLE Countries (
45  id SERIAL PRIMARY KEY,
46  country_name VARCHAR(40) NOT NULL,
47  comission MONEY CHECK(comission >= 0) DEFAULT 0 NOT NULL
48);
49
50CREATE TABLE Applications (
51  id SERIAL PRIMARY KEY,
52  apartment_id INT REFERENCES Apartments (id) NOT NULL,
53  arrival_data TIMESTAMP                      NOT NULL,
54  departure_date TIMESTAMP                    NOT NULL,
55  residents_number INT CHECK(residents_number > 0) NOT NULL,
56  comment VARCHAR(200),
57  total_price MONEY
58);
59
60CREATE TABLE OwnerReviews (
61  apartment_id INT REFERENCES Apartments (id) NOT NULL,
62  user_id INT REFERENCES Users (id) NOT NULL,
63  comment TEXT NOT NULL,
64  rate INT CHECK(1 <= rate && rate <= 5) NOT NULL
65);
66
67CREATE TABLE ClientReview (
68  apartment_id INT REFERENCES Apartments (id) NOT NULL,
69  user_id INT REFERENCES Users(id) NOT NULL,
70  comment TEXT NOT NULL,
71  location_rate INT CHECK(1 <= location_rate && location_rate <= 5) NOT NULL,
72  cleanness_rate INT CHECK(1 <= cleanness_rate && cleanness_rate <= 5) NOT NULL,
73  friendliness_rate INT CHECK(1 <= friendliness_rate && friendliness_rate <= 5) NOT NULL
74);
75
76CREATE TABLE Entertainment (
77  name VARCHAR(40) NOT NULL,
78  gps POINT NOT NULL,
79  start_date DATE NOT NULL,
80  end_date DATE NOT NULL CHECK(start_date <= end_date),
81  genre entertainment_genre NOT NULL
82);