RekMDDQr

1CREATE EXTENSION IF NOT EXISTS CITEXT;
2SET TIMEZONE TO 'Europe/Moscow';
3
4CREATE TABLE IF NOT EXISTS users (
5  id       SERIAL PRIMARY KEY,
6  is_admin BOOLEAN NOT NULL DEFAULT FALSE,
7  login    CITEXT  NOT NULL UNIQUE CHECK (login <> ''),
8  password CITEXT  NOT NULL CHECK (password <> ''),
9  fullname CITEXT  NOT NULL CHECK (fullname <> ''),
10  bdate    DATE    NOT NULL DEFAULT CURRENT_DATE
11);
12
13CREATE TABLE IF NOT EXISTS countries (
14  id    SERIAL PRIMARY KEY,
15  title CITEXT NOT NULL CHECK (title <> '')
16);
17
18CREATE TABLE IF NOT EXISTS cities (
19  id    SERIAL PRIMARY KEY,
20  title CITEXT NOT NULL CHECK (title <> '')
21);
22
23CREATE TABLE IF NOT EXISTS airports (
24  id      SERIAL PRIMARY KEY,
25  city    INT4 REFERENCES cities (id),
26  country INT4 REFERENCES countries (id),
27  code    CITEXT NOT NULL UNIQUE CHECK (code <> '')
28);
29
30CREATE TABLE IF NOT EXISTS companies (
31  id    SERIAL PRIMARY KEY,
32  title CITEXT NOT NULL UNIQUE CHECK (title <> '')
33);
34
35CREATE TABLE IF NOT EXISTS pilots (
36  id       SERIAL PRIMARY KEY,
37  fullname CITEXT NOT NULL CHECK (fullname <> ''),
38  airport  INT4   NOT NULL REFERENCES airports (id)
39);
40
41CREATE TABLE IF NOT EXISTS payments (
42  id           SERIAL PRIMARY KEY,
43  payee        INT4        NOT NULL REFERENCES pilots (id),
44  payment_date TIMESTAMPTZ NOT NULL DEFAULT NOW(),
45  amount       INT         NOT NULL
46);
47
48CREATE TABLE IF NOT EXISTS planes (
49  id      SERIAL PRIMARY KEY,
50  company INT4   NOT NULL REFERENCES companies (id),
51  model   CITEXT NOT NULL UNIQUE CHECK (model <> '')
52);
53
54CREATE TABLE IF NOT EXISTS tickets (
55  id           SERIAL PRIMARY KEY,
56  buyer        INT4        NOT NULL REFERENCES users (id),
57  pilot        INT4        NOT NULL REFERENCES pilots (id),
58  from_airport INT4        NOT NULL REFERENCES airports (id),
59  to_airport   INT4        NOT NULL REFERENCES airports (id),
60  flight_date  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
61  gate         INT         NOT NULL,
62  seat         CITEXT      NOT NULL CHECK (seat <> ''),
63  company      INT4        NOT NULL REFERENCES companies (id),
64  plane        INT4        NOT NULL REFERENCES planes (id)
65);
66
67CREATE TABLE IF NOT EXISTS sales (
68  id        SERIAL PRIMARY KEY,
69  ticket_id INT4        NOT NULL REFERENCES tickets (id),
70  sale_date TIMESTAMPTZ NOT NULL DEFAULT NOW()
71);