Qd7i448c

1drop table if exists person_ids,
2volunteers,
3sportsmen,
4places,
5delegations,
6sports,
7competitions,
8medals,
9arenas,
10occupations,
11participations,
12assignments,
13tasks,
14transports,
15countries
16cascade;
17
18drop type if exists sex_t, medal_t;
19
20create type sex_t as enum ('female', 'male');
21
22create type medal_t as enum ('gold', 'silver', 'bronze');
23
24create table person_ids (
25  id          int primary key,
26  person_name text not null
27);
28
29create table volunteers (
30  id    int primary key references person_ids (id),
31  phone varchar(16)
32);
33
34create table places (
35  id               serial primary key,
36  street           text not null,
37  house_number     int  not null,
38  house_additional text,
39  kind             text not null,
40  place_name       text
41);
42
43create table countries (
44  id      serial primary key,
45  country text unique not null
46);
47
48create table delegations (
49  country      int primary key references countries (id),
50  head         text                       not null,
51  phone        varchar(16)                not null check (phone ~ '(\+)?[0-9]{2,15}'),
52  headquarters int references places (id) not null
53);
54
55create table sportsmen (
56  id            int primary key references person_ids (id),
57  sex           sex_t                                not null,
58  height        decimal(4, 1) check (height > 0)     not null,
59  weight        decimal(6, 3) check (weight > 0)     not null,
60  age           int check (age > 0)                  not null,
61  volunteer     int references volunteers (id)       not null,
62  house         int references places (id)           not null,
63  delegation_id int references delegations (country) not null
64);
65
66create table sports (
67  id   serial primary key,
68  kind text unique not null
69);
70
71create table competitions (
72  id                    serial primary key,
73  competition_timestamp timestamp                  not null,
74  sport_id              int references sports (id) not null,
75  place                 int references places (id) not null
76);
77
78create table medals (
79  medal       medal_t                          not null,
80  sportsman   int references sportsmen (id)    not null,
81  competition int references competitions (id) not null,
82  unique (sportsman, competition)
83);
84
85create table occupations (
86  sportsman int references sportsmen (id) not null,
87  sport     text references sports (kind) not null
88);
89
90create table participations (
91  sportsman   int references sportsmen (id)    not null,
92  competition int references competitions (id) not null,
93  unique (sportsman, competition)
94);
95
96create table arenas (
97  place int references places (id)    not null,
98  sport text references sports (kind) not null
99);
100
101create table transports (
102  id             serial primary key,
103  license_number varchar(16) unique        not null,
104  capacity       int check (capacity >= 0) not null
105);
106
107create table tasks (
108  id          serial primary key,
109  task_date   date not null,
110  task_time   time not null,
111  description text not null,
112  car         varchar(16) references transports (license_number)
113);
114
115create table assignments (
116  volunteer int references volunteers (id) not null,
117  task      int references tasks (id)      not null
118);
119
120insert into countries (country)
121values ('Russia'),
122       ('Spain'),
123       ('Hungary'),
124       ('Vietnam'),
125       ('Poland');
126
127insert into person_ids (id, person_name)
128values (1, 'Mitya'),
129       (2, 'Sasha'),
130       (3, 'Kostya'),
131       (4, 'Lisa'),
132       (5, 'Katya');
133
134insert into volunteers (id, phone)
135values (1, '+79119127284'),
136       (2, '+48739606754');
137
138insert into places (street, house_number, kind, place_name)
139values ('Nevskiy avenue', 1, 'swimming pool', 'Seal'),
140       ('Bolshoy avenue', 2, 'court', 'Good Court'),
141       ('Veteranov avenue', 3, 'hotel', 'Astoria'),
142       ('Nauki avenue', 4, 'hotel', 'Radisson');
143
144insert into delegations (country, head, phone, headquarters)
145values (5, 'Mitya', '+00', 4),
146       (2, 'Kostya', '+000', 4),
147       (1, 'Lisa', '+00000', 3),
148       (4, 'Sasha', '+000000', 3);
149
150insert into sportsmen (id, sex, height, weight, age, volunteer, house, delegation_id)
151values (4, 'female', 100, 100, 100, 1, 3, 1),
152       (5, 'female', 100, 100, 100, 1, 3, 4),
153       (1, 'male', 100, 100, 100, 2, 3, 5),
154       (2, 'male', 100, 100, 100, 2, 3, 4),
155       (3, 'male', 100, 100, 100, 2, 3, 2);