6WxAhdgT

1drop table if exists history, connection_users_location,
2    connection_inventory_marathon, connection_location_marathon, connection_sponsor_users,
3    connection_volunteer_marathon, request;
4drop table if exists marathon;
5drop table if exists location, users, sponsors, inventory, volunteer;
6
7
8create table users (
9    id serial primary key unique,
10    name text not null,
11    password text not null
12        constraint chk_password
13            check (length(password) >= 6 and length(password) <= 20),
14    age int not null
15        constraint chk_age
16            check (age >= 18 and age <= 45),
17    number text not null,
18    email text unique not null
19        constraint chk_email
20            check (email LIKE '%_@__%.__%'),
21    status text not null
22        constraint chk_status
23            check (status = 'Бегун' or status = 'Организатор' or status = 'Координатор')
24);
25
26create table location(
27    id serial primary key unique,
28    name text not null
29);
30
31create table marathon (
32    id serial primary key unique,
33    length int not null,
34    count int not null,
35    info text not null,
36    start_point int references location(id) not null,
37    end_point int references location(id) not null,
38    date date not null
39);
40
41create table volunteer(
42    id serial primary key unique,
43    name text not null,
44    email text unique not null
45        constraint chk_email
46            check (email LIKE '%_@__%.__%')
47);
48
49create table sponsors(
50    id serial primary key unique,
51    name text not null,
52    info text
53);
54
55create table inventory(
56    id serial primary key unique,
57    name text not null
58);
59
60create table connection_inventory_marathon(
61    id serial primary key unique,
62    id_inventory int references inventory(id) not null,
63    id_marathon int references marathon(id) not null,
64    count int not null,
65    unique(id_inventory, id_marathon)
66);
67
68create table connection_location_marathon(
69    id serial primary key unique,
70    id_location int references location(id) not null,
71    id_marathon int references marathon(id) not null
72);
73
74create table connection_volunteer_marathon(
75    id serial primary key unique,
76    id_volunteer int references volunteer(id) not null,
77    id_marathon int references marathon(id) not null,
78    unique(id_marathon, id_volunteer)
79);
80
81create table connection_sponsor_users(
82    id serial primary key unique,
83    id_runners int references users(id) not null,
84    id_sponsors int references sponsors(id) not null
85);
86
87create table connection_users_location(
88    id serial primary key unique,
89    id_location int references location(id) not null,
90    id_coordinator int references users(id) not null
91);
92
93create table history(
94    id serial primary key unique,
95    id_runners int references users(id) unique not null,
96    id_marathon int references marathon(id) not null,
97    result int not null
98);
99--вписала
100create table request(
101    id serial primary key unique,
102    id_runners int references users(id) not null,
103    id_marathon int references marathon(id) not null,
104    state text
105        constraint chk_state
106            check (state = 'Одобрено.' or state = 'Отклонено.')
107);