HfGWvpKR

1create type sex_t as enum ('female', 'male');
2
3drop table if exists person_ids,
4					volunteers, 
5					sportsmen, 
6					places, 
7					delegations, 
8					sports, 
9					competitions, 
10					medals, 
11					arenas, 
12					occupations, 
13					participations,
14					assignments,
15					tasks,
16					transports,
17					countries
18					cascade;
19
20create table person_ids (
21	id int primary key,
22	person_name text not null
23);
24
25create table volunteers (
26	id int primary key references person_ids(id),
27	phone varchar(16)
28);
29
30create table places (
31	id serial primary key,
32	street text not null,
33	house_number int not null,
34	house_additional text,
35	kind text not null,
36	place_name text 
37);
38
39
40create table countries (
41    id serial primary key,
42	country text unique not null   
43);
44
45create table delegations (
46    country int primary key references countries(id),
47	head text not null,
48	phone varchar(16) not null check (phone ~ '(\+)?[0-9]{2, 15}'),
49	headquarters int references places(id) not null
50);
51
52create table sportsmen (
53	id int primary key references person_ids(id),
54	sex sex_t not null,
55	height decimal(4, 1) check(height > 0) not null,
56	weight decimal(6, 3) check(weight > 0) not null,
57	age int check(age > 0) not null,
58	volunteer int references volunteers(id) not null,
59	house int references places(id) not null,
60	delegation_id int references delegations(country) not null
61);
62
63create table sports (
64    id serial primary key,
65	kind text unique not null
66);
67
68create table competitions (
69	id serial primary key,
70	competition_date date not null, 
71	competition_time time not null,
72	sport_id int references sports(id) not null,
73	place int references places(id) not null
74);
75
76create table medals (
77	place_number smallint check(1 <= place_number and place_number <= 3) not null,
78	sportsman int references sportsmen(id) not null,
79	competition int references competitions(id) not null,
80	unique(sportsman, competition)
81);
82
83create table occupations (
84	sportsman int references sportsmen(id) not null,
85	sport text references sports(kind) not null
86);
87
88create table participations (
89	sportsman int references sportsmen(id) not null,
90	competition int references competitions(id) not null
91);
92
93create table arenas (
94	place int references places(id) not null,
95	sport text references sports(kind) not null
96);
97
98create table transports (
99	license_number varchar(16) primary key,
100	capacity int check (capacity >= 0) not null
101);
102
103create table tasks (
104	id serial primary key,
105	task_date date not null,
106	task_time time not null,
107	description text not null,
108	car varchar(16) references transports(license_number)
109);
110
111create table assignments (
112	volunteer int references volunteers(id) not null,
113	task int references tasks(id) not null
114);