wrnPdnuS

1DROP TABLE IF EXISTS Client CASCADE;
2DROP TABLE IF EXISTS Flat CASCADE;
3DROP TABLE IF EXISTS ServiceFee CASCADE;
4DROP TABLE IF EXISTS ClientFlat CASCADE;
5DROP TABLE IF EXISTS Application CASCADE;
6DROP TABLE IF EXISTS FlatReview CASCADE;
7DROP TABLE IF EXISTS ClientReview CASCADE;
8DROP TABLE IF EXISTS Entertainment CASCADE;
9
10\set STR_LEN 200
11
12CREATE TABLE Client(
13    mail VARCHAR(:STR_LEN) NOT NULL UNIQUE,
14    name VARCHAR(:STR_LEN) NOT NULL,
15    surname VARCHAR(:STR_LEN) NOT NULL,
16    phone VARCHAR(:STR_LEN) NOT NULL,
17    sex VARCHAR(:STR_LEN) NULL,
18    birthday DATE NULL,
19    photo BYTEA NULL
20);
21
22CREATE TABLE ServiceFee(
23    country VARCHAR(:STR_LEN) NOT NULL UNIQUE,
24    fee MONEY NOT NULL CHECK(fee::money::numeric::float8 >= 0)
25);
26
27CREATE TABLE Flat(
28    id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
29    country VARCHAR(:STR_LEN) NOT NULL REFERENCES ServiceFee(country),
30    city VARCHAR(:STR_LEN) NOT NULL,
31    street_address VARCHAR(:STR_LEN) NOT NULL,
32    gps_location POINT NOT NULL,
33    name VARCHAR(:STR_LEN) NOT NULL,
34    room_number INT NOT NULL CHECK(room_number > 0),
35    bed_number INT NOT NULL CHECK(bed_number >= 0),
36    capacity INT NOT NULL CHECK(capacity > 0),
37    -- facilities
38    cleaning_fee MONEY NOT NULL CHECK(cleaning_fee::money::numeric::float8 >= 0),
39    week_fee MONEY[53] NOT NULL,
40    UNIQUE(country, city, street_address)
41);
42
43CREATE TABLE ClientFlat(
44    mail VARCHAR(:STR_LEN) REFERENCES Client(mail),
45    flat_id INT REFERENCES Flat(id)
46);
47
48CREATE TABLE Application(
49    mail VARCHAR(:STR_LEN) REFERENCES Client(mail),
50    flat_id INT REFERENCES Flat(id),
51    start_date DATE NOT NULL,
52    end_date DATE NOT NULL CHECK(end_date >= start_date),
53    people_number INT NOT NULL CHECK(people_number > 0),
54    comment VARCHAR(:STR_LEN) NOT NULL,
55    accepted BOOLEAN NOT NULL,
56    UNIQUE(mail, flat_id, start_date)
57);
58
59CREATE TABLE FlatReview(
60    mail VARCHAR(:STR_LEN) NOT NULL,
61    flat_id INT NOT NULL,
62    start_date DATE NOT NULL,
63    comment VARCHAR(:STR_LEN) NOT NULL,
64    -- stars
65    FOREIGN KEY(mail, flat_id, start_date) REFERENCES Application(mail, flat_id, start_date)
66);
67
68CREATE TABLE ClientReview(
69    mail VARCHAR(:STR_LEN) NOT NULL,
70    flat_id INT NOT NULL,
71    start_date DATE NOT NULL,
72    comment VARCHAR(:STR_LEN) NOT NULL,
73    stars INT NOT NULL CHECK(stars >= 0 AND stars <= 5),
74    FOREIGN KEY(mail, flat_id, start_date) REFERENCES Application(mail, flat_id, start_date)
75);
76
77CREATE TYPE EntertainmentGenre AS ENUM('beach', 'sport');
78
79CREATE TABLE Entertainment(
80    name VARCHAR(:STR_LEN) NOT NULL UNIQUE,
81    city VARCHAR(:STR_LEN) NOT NULL,
82    gps_location POINT NOT NULL,
83    start_date DATE NOT NULL,
84    end_date DATE NOT NULL CHECK(end_date >= start_date),
85    genre EntertainmentGenre
86);