· 7 years ago · Oct 15, 2018, 06:12 PM
1DROP TABLE IF EXISTS delegation CASCADE;
2DROP TABLE IF EXISTS event CASCADE;
3DROP TABLE IF EXISTS facility CASCADE;
4DROP TABLE IF EXISTS leader CASCADE;
5DROP TABLE IF EXISTS sport CASCADE;
6DROP TABLE IF EXISTS sportsman CASCADE;
7DROP TABLE IF EXISTS task CASCADE;
8DROP TABLE IF EXISTS volunteer CASCADE;
9DROP TABLE IF EXISTS transport CASCADE;
10DROP TABLE IF EXISTS medal CASCADE;
11DROP TABLE IF EXISTS sportsman_sport CASCADE;
12DROP TABLE IF EXISTS sport_facility CASCADE;
13DROP TABLE IF EXISTS sportsman_event CASCADE;
14
15CREATE TABLE IF NOT EXISTS transport (
16 registration_id TEXT NOT NULL,
17 capacity INTEGER CHECK (capacity > 0),
18 PRIMARY KEY (registration_id)
19);
20
21CREATE TABLE IF NOT EXISTS volunteer (
22 id SERIAL,
23 name TEXT NOT NULL,
24 phone_number TEXT NOT NULL,
25 PRIMARY KEY (id)
26);
27
28CREATE TABLE IF NOT EXISTS leader (
29 id SERIAL,
30 name TEXT NOT NULL,
31 phone TEXT NOT NULL,
32 PRIMARY KEY (id)
33);
34
35CREATE TABLE IF NOT EXISTS facility (
36 id SERIAL,
37 street_name TEXT NOT NULL,
38 building_number INTEGER NOT NULL,
39 name TEXT,
40 type TEXT,
41 PRIMARY KEY (id)
42);
43
44CREATE TABLE IF NOT EXISTS sport (
45 id SERIAL,
46 name TEXT NOT NULL,
47 PRIMARY KEY (id)
48);
49
50CREATE TABLE IF NOT EXISTS task (
51 id SERIAL,
52 datetime TIMESTAMP NOT NULL,
53 description TEXT NOT NULL,
54 transport_id TEXT,
55 volunteer_id INTEGER NOT NULL,
56 PRIMARY KEY (id),
57 FOREIGN KEY (transport_id) REFERENCES transport (registration_id),
58 FOREIGN KEY (volunteer_id) REFERENCES volunteer (id)
59);
60
61CREATE TABLE IF NOT EXISTS delegation (
62 id SERIAL,
63 headquoter_id INTEGER UNIQUE NOT NULL,
64 leader_id INTEGER UNIQUE NOT NULL,
65 FOREIGN KEY (leader_id) REFERENCES leader (id),
66 FOREIGN KEY (headquoter_id) REFERENCES facility (id),
67 PRIMARY KEY (id)
68);
69
70CREATE TABLE IF NOT EXISTS sportsman (
71 card_id TEXT NOT NULL,
72 name TEXT NOT NULL,
73 sex TEXT CHECK (sex = 'male' or sex = 'female'),
74 age INTEGER NOT NULL CHECK (age > 0),
75 height INTEGER NOT NULL CHECK (height > 0),
76 weight INTEGER NOT NULL CHECK (weight > 0),
77 residence_id INTEGER NOT NULL,
78 delegation_id INTEGER NOT NULL,
79 volunteer_id INTEGER NOT NULL,
80 PRIMARY KEY (card_id),
81 FOREIGN KEY (residence_id) REFERENCES facility (id),
82 FOREIGN KEY (delegation_id) REFERENCES delegation (id),
83 FOREIGN KEY (volunteer_id) REFERENCES volunteer (id)
84);
85
86CREATE TABLE IF NOT EXISTS event (
87 id SERIAL,
88 name TEXT NOT NULL,
89 datetime TIMESTAMP NOT NULL,
90 sport_id INTEGER NOT NULL,
91 facility_id INTEGER NOT NULL,
92 FOREIGN KEY (sport_id) REFERENCES sport (id),
93 FOREIGN KEY (facility_id) REFERENCES facility (id),
94 PRIMARY KEY (id)
95);
96
97CREATE TABLE IF NOT EXISTS medal (
98 id SERIAL,
99 type TEXT CHECK (type = 'bronze' or type = 'silver' or type = 'gold'),
100 sport_id INTEGER NOT NULL,
101 sportsman_id TEXT NOT NULL,
102 FOREIGN KEY (sport_id) REFERENCES sport (id),
103 FOREIGN KEY (sportsman_id) REFERENCES sportsman (card_id),
104 PRIMARY KEY (id)
105);
106
107CREATE TABLE IF NOT EXISTS sportsman_sport (
108 id SERIAL,
109 sportsman_id TEXT NOT NULL,
110 sport_id INTEGER NOT NULL,
111 FOREIGN KEY (sportsman_id) REFERENCES sportsman (card_id),
112 FOREIGN KEY (sport_id) REFERENCES sport (id),
113 PRIMARY KEY (id)
114);
115
116CREATE TABLE IF NOT EXISTS sport_facility (
117 id SERIAL,
118 sport_id INTEGER NOT NULL,
119 facility_id INTEGER NOT NULL,
120 FOREIGN KEY (sport_id) REFERENCES sport (id),
121 FOREIGN KEY (facility_id) REFERENCES facility (id),
122 PRIMARY KEY (id)
123);
124
125CREATE TABLE IF NOT EXISTS sportsman_event (
126 id SERIAL,
127 sportsman_id TEXT NOT NULL,
128 event_id INTEGER NOT NULL,
129 FOREIGN KEY (sportsman_id) REFERENCES sportsman (card_id),
130 FOREIGN KEY (event_id) REFERENCES event (id),
131 PRIMARY KEY (id)
132);