· 9 years ago · Oct 14, 2016, 08:52 AM
1DROP TABLE IF EXISTS "countries" CASCADE;
2CREATE TABLE "countries" (
3 "country" text NOT NULL,
4 Constraint "country_prim" Primary Key ("country")
5);
6
7DROP TABLE IF EXISTS "competitors" CASCADE;
8CREATE TABLE "competitors" (
9 "competitor_id" integer NOT NULL,
10 "country" text references countries(country),
11 "is_team" text,
12 "gender" text,
13 Constraint "competitor_prim" Primary Key ("competitor_id")
14);
15
16DROP TABLE IF EXISTS "sports" CASCADE;
17CREATE TABLE "sports" (
18 "sport" text NOT NULL,
19 "gender" text,
20 "gold" integer references competitors("competitor_id"),
21 "silver" integer references competitors("competitor_id"),
22 "bronze" integer references competitors("competitor_id"),
23 Constraint "sport_prim" Primary Key ("sport")
24);
25
26
27DROP TABLE IF EXISTS "individuals" CASCADE;
28CREATE TABLE "individuals" (
29 "individual_id" integer references competitors("competitor_id") UNIQUE,
30 "first_name" text,
31 "last_name" text,
32 Constraint "integrity" CHECK (("individual_id" NOTNULL))
33);
34
35DROP TABLE IF EXISTS "registered_for" CASCADE;
36CREATE TABLE "registered_for" (
37 "competitor_id" integer references competitors("competitor_id"),
38 "sport" text references sports(sport),
39 Constraint "integrity" CHECK ((("competitor_id" NOTNULL) AND (sport NOTNULL)))
40);
41
42DROP TABLE IF EXISTS "teams" CASCADE;
43CREATE TABLE "teams" (
44 "team_id" integer references competitors("competitor_id") UNIQUE,
45 "sport" text references sports(sport),
46 Constraint "integrity" CHECK (sport NOTNULL)
47);
48
49DROP TABLE IF EXISTS "member_of" CASCADE;
50CREATE TABLE "member_of" (
51 "team_id" integer references teams("team_id"),
52 "individual_id" integer references individuals("individual_id"),
53 "role" text NOT NULL,
54 Constraint "integrity" CHECK ((("team_id" NOTNULL) AND ("individual_id" NOTNULL)))
55);
56
57
58
59DROP TABLE IF EXISTS "officials" CASCADE;
60CREATE TABLE "officials" (
61 "official_id" integer NOT NULL,
62 "last_name" text,
63 "first_name" text,
64 Constraint “officials_prim†Primary Key ("official_id")
65);
66
67DROP TABLE IF EXISTS "qualified_for" CASCADE;
68CREATE TABLE "qualified_for" (
69 "official_id" integer references officials("official_id"),
70 "sport" text references sports("sport"),
71 Constraint "integrity" CHECK ((("official_id" NOTNULL) AND ("sport" NOTNULL)))
72);
73
74DROP TABLE IF EXISTS "arenas" CASCADE;
75CREATE TABLE "arenas" (
76 "arena" text,
77 Constraint "arenas_prim" Primary Key ("arena")
78);
79
80DROP TABLE IF EXISTS "places" CASCADE;
81CREATE TABLE "places" (
82 "place" text NOT NULL,
83 "arena_name" text references arenas(arena),
84 CHECK ((("arena_name" NOTNULL))),
85 Constraint "places_prim" Primary Key ("place","arena_name")
86
87);
88
89DROP TABLE IF EXISTS "competitions" CASCADE;
90CREATE TABLE "competitions" (
91 "competition_id" integer NOT NULL,
92 "competition_name" text,
93 "sport" text references sports("sport"),
94 "official_id" integer references officials("official_id"),
95 "arena" text,
96 "place" text,
97 "time" TIMESTAMP,
98 "duration" time,
99 Constraint "competitions_pkey" Primary key ("competition_id"),
100 Constraint "places_fkey" FOREIGN KEY (place,arena) references places("place","arena_name"),
101 CHECK ((("official_id" NOTNULL) AND (sport NOTNULL)))
102
103);
104
105DROP TABLE IF EXISTS "competes_in" CASCADE;
106CREATE TABLE "competes_in" (
107 "competition_id" integer references competitions("competition_id"),
108 "competitor_id" integer references competitors("competitor_id"),
109 Constraint "integrity" CHECK ((("competition_id" NOTNULL) AND ("competitor_id" NOTNULL)))
110);
111
112DROP FUNCTION IF EXISTS checkcountry() CASCADE;
113CREATE FUNCTION checkcountry() RETURNS trigger AS $pname$
114BEGIN
115IF (SELECT country FROM competitors WHERE competitors.competitor_id = NEW.individual_id) <> (SELECT country FROM competitors WHERE competitors.competitor_id = NEW.team_id)
116THEN
117RAISE EXCEPTION 'This person is not from that country';
118END IF;
119RETURN NEW;
120END;
121$pname$ LANGUAGE plpgsql;
122
123CREATE TRIGGER only_one_country
124BEFORE INSERT ON member_of
125FOR EACH ROW
126EXECUTE PROCEDURE checkcountry();
127
128DROP FUNCTION IF EXISTS checkofficial() CASCADE;
129CREATE FUNCTION checkofficial() RETURNS trigger AS $fname$
130BEGIN
131IF (NEW.sport) NOT IN (SELECT sport FROM qualified_for WHERE qualified_for.official_id = NEW.official_id)
132THEN
133RAISE EXCEPTION 'That official is not qualified for that sport';
134END IF;
135RETURN NEW;
136END;
137$fname$ LANGUAGE plpgsql;
138
139CREATE TRIGGER rightoffical
140BEFORE INSERT ON competitions
141FOR EACH ROW
142EXECUTE PROCEDURE checkofficial();
143
144DROP FUNCTION IF EXISTS checktime() CASCADE;
145CREATE FUNCTION checktime() RETURNS trigger AS $gname$
146BEGIN
147IF EXISTS (SELECT competition_name FROM competitions WHERE arena = NEW.arena AND place = NEW.place AND (time,duration) OVERLAPS (NEW.time,NEW.duration))
148THEN
149RAISE EXCEPTION 'That time is occupied';
150END IF;
151RETURN NEW;
152END;
153$gname$ LANGUAGE plpgsql;
154
155CREATE TRIGGER righttime
156BEFORE INSERT ON competitions
157FOR EACH ROW
158EXECUTE PROCEDURE checktime();
159
160
161COPY "countries" FROM stdin;
162sweden
163germany
164france
165\.
166
167COPY "competitors" FROM stdin;
1681 sweden f man
1692 germany f man
1703 france f man
1714 sweden f man
1725 germany f man
1736 france f man
1747 sweden f man
1758 germany f man
1769 france f man
17710 sweden t man
17811 germany t man
17912 france t man
180\.
181
182COPY "individuals" FROM stdin;
1831 Lars Larsson
1842 Heinriche Schultz
1853 Jean Michelle
1864 Krister Johansson
1875 Kashan Khan
1886 Juan Debedima
1897 Adam Lindgren
1908 Zach Cordoni
1919 Joseph Marquez
192\.
193
194
195
196COPY "sports" FROM stdin;
197high jump man 1 2 3
198golf man 4 5 1
199long jump man 1 3 9
200football man 10 2 12
201\.
202
203
204COPY "teams" FROM stdin;
20510 football
20611 football
20712 football
208\.
209
210
211
212COPY "registered_for" FROM stdin;
2131 high jump
2142 high jump
2153 high jump
2164 golf
2175 golf
2186 golf
2197 long jump
2208 long jump
2219 long jump
2222 long jump
2238 high jump
2247 high jump
2253 golf
2261 football
2272 football
2283 football
2294 football
2305 football
2316 football
2327 football
2338 football
2349 football
23510 football
23611 football
23712 football
238\.
239
240
241
242COPY "member_of" FROM stdin;
24310 1 back
24410 4 forward
24510 7 center
24611 2 back
24711 5 forward
24811 8 center
24912 3 back
25012 6 forward
25112 9 center
252\.
253
254COPY "officials" FROM stdin;
2551 Chan Jackie
2562 Pitt Brad
257\.
258COPY "qualified_for" FROM stdin;
2591 football
2602 high jump
2612 long jump
2621 golf
263\.
264
265COPY "arenas" FROM stdin;
266Friends Arena
267Tele2 Arena
268\.
269
270COPY "places" FROM stdin;
271back Friends Arena
272front Friends Arena
273back Tele2 Arena
274front Tele2 Arena
275\.
276
277
278COPY "competitions" FROM stdin;
2791 final football 1 Tele2 Arena back '2016-05-10 10:50:55' '02:00:00'
2802 final high jump 2 Tele2 Arena front '2016-05-11 11:30:17' '03:00:00'
2813 final golf 1 Friends Arena back '2016-05-12 13:37:37' '02:30:00'
2824 final long jump 2 Friends Arena front '2016-05-13 18:47:22' '04:25:00'
283\.
284
285COPY "competes_in" FROM stdin;
2861 10
2871 11
2881 12
2892 1
2902 2
2912 3
2922 8
2932 7
2943 3
2953 4
2963 5
2973 6
2984 7
2994 8
3004 9
3014 2
302\.