HKrN4idC

1drop table if exists history, connection_admin_marathon, connection_coordinator_runner,
2    connection_inventory_marathon, connection_location_marathon, connection_sponsor_runner,
3    connection_volunteer_marathon, request;
4drop table if exists marathon;
5drop table if exists admin, coordinator, location, runners, sponsors, inventory, volunteer;
6
7
8create table runners (
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
15        constraint chk_age
16            check (age >= 18 and age <= 45),
17    number text,
18    email text unique
19        constraint chk_email
20            check (email LIKE '%_@__%.__%'),
21    info text
22);
23
24
25create table location(
26    id serial primary key unique,
27    name text not null
28);
29
30create table marathon (
31    id serial primary key unique,
32    length int,
33    count int,
34    info text not null,
35    start_point int references location(id),
36    end_point int references location(id),
37    date date
38);
39
40create table coordinator(
41    id serial primary key unique,
42    name text not null,
43    password text not null
44        constraint chk_password
45            check (length(password) >= 6 and length(password) <= 20)
46);
47
48create table volunteer(
49    id serial primary key unique,
50    name text not null,
51    email text unique
52        constraint chk_email
53            check (email LIKE '%_@__%.__%')
54);
55
56create table sponsors(
57    id serial primary key unique,
58    name text not null,
59    info text
60);
61
62create table admin(
63    id serial primary key unique,
64    name text not null,
65    password text not null
66        constraint chk_password
67            check (length(password) >= 6 and length(password) <= 20)
68);
69
70create table inventory(
71    id serial primary key unique,
72    name text not null
73);
74
75create table connection_inventory_marathon(
76    id serial primary key unique,
77    id_inventory int references inventory(id),
78    id_marathon int references marathon(id),
79    count int
80);
81
82create table connection_location_marathon(
83    id serial primary key unique,
84    id_location int references location(id),
85    id_marathon int references marathon(id)
86);
87
88create table connection_volunteer_marathon(
89    id serial primary key unique,
90    id_volunteer int references volunteer(id),
91    id_marathon int references marathon(id)
92);
93
94create table connection_admin_marathon(
95    id serial primary key unique,
96    id_admin int references admin(id),
97    id_marathon int references marathon(id)
98);
99
100create table connection_sponsor_runner(
101    id serial primary key unique,
102    id_runners int references runners(id),
103    id_sponsors int references sponsors(id)
104);
105
106create table connection_coordinator_location(
107    id serial primary key unique,
108    id_location int references location(id),
109    id_coordinator int references coordinator(id)
110);
111
112create table state_runner (
113    id serial primary key unique,
114    id_location int references location(id),
115    id_runners int references runners(id) unique
116);
117
118create table history(
119    id serial primary key unique,
120    id_runners int references runners(id),
121    id_marathon int references marathon(id),
122    result text
123);
124
125create table request(
126    id serial primary key unique,
127    id_runners int references runners(id),
128    id_marathon int references marathon(id),
129    state text
130        constraint chk_state
131            check (state = 'Одобрено.' or state = 'Отклонено.')
132);