wttpV6Eq

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