z50tXiiw

1drop table if exists history, connction_admin_marathon, connection_coordinator_runner,
2    connection_inventory_marathon, connection_location_marathon, connection_sponsor_runner,
3    connection_volunteer_marathon;
4drop table if exists marathon;
5drop table if exists admin, coordinator, location, runners, sponsors, inventory, volanteer;
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
24create table location(
25    id serial primary key unique,
26    name text not null
27);
28
29create table marathon (
30    id serial primary key unique,
31    length int,
32    info text not null,
33    start_point int references location(id),
34    end_point int references location(id),
35    date date
36);
37
38create table coordinator(
39    id serial primary key unique,
40    name text not null,
41    password text not null
42        constraint chk_password
43            check (length(password) >= 6 and length(password) <= 20)
44);
45
46create table volunteer(
47    id serial primary key unique,
48    name text not null,
49    email text unique
50        constraint chk_email
51            check (email LIKE '%_@__%.__%')
52);
53
54create table sponsors(
55    id serial primary key unique,
56    name text not null,
57    info text
58);
59
60create table admin(
61    id serial primary key unique,
62    name text not null,
63    password text not null
64        constraint chk_password
65            check (length(password) >= 6 and length(password) <= 20)
66);
67
68create table inventory(
69    id serial primary key unique,
70    name text not null
71);
72
73create table connection_inventory_marathon(
74    id serial primary key unique,
75    id_inventory int references inventory(id),
76    id_marathon int references marathon(id),
77    count int
78);
79
80create table connection_location_marathon(
81    id serial primary key unique,
82    id_location int references location(id),
83    id_marathon int references marathon(id)
84);
85
86create table connection_volunteer_marathon(
87    id serial primary key unique,
88    id_volunteer int references volunteer(id),
89    id_marathon int references marathon(id)
90);
91
92create table connction_admin_marathon(
93    id serial primary key unique,
94    id_admin int references admin(id),
95    id_marathon int references marathon(id)
96);
97
98create table connection_sponsor_runner(
99    id serial primary key unique,
100    id_runners int references runners(id),
101    id_sponsors int references sponsors(id)
102);
103
104create table connection_coordinator_runner(
105    id serial primary key unique,
106    id_runners int references runners(id),
107    id_coordinator int references coordinator(id)
108);
109
110create table history(
111    id serial primary key unique,
112    id_runners int references runners(id),
113    id_marathon int references marathon(id),
114    result text
115);