· 7 years ago · Oct 30, 2018, 10:26 AM
1drop table if exists person_ids,
2 volunteers,
3 sportsmen,
4 places,
5 delegations,
6 sports,
7 competitions,
8 medals,
9 arenas,
10 occupations,
11 participations,
12 assignments,
13 tasks,
14 transports,
15 countries
16 cascade;
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
43
44create table countries (
45 id serial primary key,
46 country text unique not null
47);
48
49create table delegations (
50 country int primary key references countries(id),
51 head text not null,
52 phone varchar(16) not null check (phone ~ '(\+)?[0-9]{2, 15}'),
53 headquarters int references places(id) not null
54);
55
56create table sportsmen (
57 id int primary key references person_ids(id),
58 sex sex_t not null,
59 height decimal(4, 1) check(height > 0) not null,
60 weight decimal(6, 3) check(weight > 0) not null,
61 age int check(age > 0) not null,
62 volunteer int references volunteers(id) not null,
63 house int references places(id) not null,
64 delegation_id int references delegations(country) not null
65);
66
67create table sports (
68 id serial primary key,
69 kind text unique not null
70);
71
72create table competitions (
73 id serial primary key,
74 competition_timestamp timestamp not null,
75 sport_id int references sports(id) not null,
76 place int references places(id) not null
77);
78
79create table medals (
80 medal medal_t not null,
81 sportsman int references sportsmen(id) not null,
82 competition int references competitions(id) not null,
83 unique(sportsman, competition)
84);
85
86create table occupations (
87 sportsman int references sportsmen(id) not null,
88 sport text references sports(kind) not null
89);
90
91create table participations (
92 sportsman int references sportsmen(id) not null,
93 competition int references competitions(id) not null,
94 unique(sportsman, competition)
95);
96
97create table arenas (
98 place int references places(id) not null,
99 sport text references sports(kind) not null
100);
101
102create table transports (
103 id serial primary key,
104 license_number varchar(16) unique not null,
105 capacity int check (capacity >= 0) not null
106);
107
108create table tasks (
109 id serial primary key,
110 task_date date not null,
111 task_time time not null,
112 description text not null,
113 car varchar(16) references transports(license_number)
114);
115
116create table assignments (
117 volunteer int references volunteers(id) not null,
118 task int references tasks(id) not null
119);