9nq6u99y

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