KUjcqx0D

1drop trigger if exists triggerAgeACM on competitionsteams;
2drop function if exists functionAgeACM();
3drop table if exists universities CASCADE;
4drop table if exists participants CASCADE;
5drop table if exists trainers CASCADE;
6drop table if exists teams CASCADE;
7drop table if exists competitions CASCADE;
8drop table if exists competitionsteams CASCADE;
9drop table if exists guests CASCADE;
10drop table if exists juri CASCADE;
11drop table if exists sponsors CASCADE;
12drop table if exists guestscompetitions CASCADE;
13drop table if exists sponsorscompetitions CASCADE;
14drop table if exists juricompetitions CASCADE;
15
16
17CREATE TABLE universities(
18    unid serial PRIMARY KEY,
19    uniname varchar(30) NOT NULL
20);
21
22
23CREATE TABLE teams(
24    tid serial PRIMARY KEY,
25    tname varchar(30) NOT NULL,
26    trainerID integer NOT NULL
27);
28
29CREATE TABLE participants(
30    pid serial PRIMARY KEY,
31    pname varchar(30) NOT NULL,
32    rating integer NOT NULL,
33    birthday date,
34    universitiesID integer NOT NULL,
35    teamID integer NOT NULL
36);
37
38CREATE TABLE trainers(
39    trid serial PRIMARY KEY,
40    trname varchar(30) NOT NULL,
41    rating integer NOT NULL,
42    job varchar(20) NOT NULL
43);
44
45
46CREATE TABLE competitions(
47    cid serial PRIMARY KEY,
48    cname varchar(30) NOT NULL,
49    ctype integer NOT NULL,
50    cdate date,
51    compteamID integer NOT NULL
52);
53
54CREATE TABLE competitionsteams(
55    teamID integer NOT NULL,
56    compID integer NOT NULL,
57    PRIMARY KEY(teamID, compID),
58    rating_place integer
59);
60
61CREATE TABLE guests(
62    gid serial PRIMARY KEY,
63    gname varchar(30) NOT NULL
64);
65
66CREATE TABLE juri(
67    jid serial PRIMARY KEY,
68    jname varchar(30) NOT NULL
69);
70
71CREATE TABLE sponsors(
72    spid serial PRIMARY KEY,
73    sname varchar(30) NOT NULL
74);
75
76CREATE TABLE guestscompetitions(
77    compID integer NOT NULL,
78    guestID integer NOT NULL,
79    PRIMARY KEY(compID, guestID)
80);
81
82CREATE TABLE sponsorscompetitions(
83    compID integer NOT NULL,
84    sponsorID integer NOT NULL,
85    PRIMARY KEY(compID, sponsorID)
86);
87
88CREATE TABLE juricompetitions(
89    compID integer NOT NULL,
90    juriID integer NOT NULL,
91    PRIMARY KEY(compID, juriID)
92);
93
94ALTER TABLE teams ADD CONSTRAINT FK3 FOREIGN KEY (trainerID)
95        REFERENCES trainers (trid) MATCH SIMPLE
96        ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE;
97
98ALTER TABLE participants ADD CONSTRAINT FK FOREIGN KEY (universitiesID)
99        REFERENCES universities (unid) MATCH SIMPLE
100        ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE;
101
102ALTER TABLE participants ADD CONSTRAINT FK2 FOREIGN KEY (teamID)
103        REFERENCES teams (tid) MATCH SIMPLE
104        ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE;
105
106ALTER TABLE competitions ADD 
107    CONSTRAINT FK6 FOREIGN KEY (cid, compteamID) 
108        REFERENCES competitionsteams (compID, teamID) MATCH SIMPLE
109        ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE;
110
111-- ALTER TABLE competitions ADD 
112--     CONSTRAINT FK11 FOREIGN KEY (winnerID)
113--         REFERENCES teams (tid) MATCH SIMPLE
114--         ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE;
115
116ALTER TABLE competitionsteams ADD CONSTRAINT FK5 FOREIGN KEY (compID)
117        REFERENCES competitions (cid) MATCH SIMPLE
118        ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE;
119
120ALTER TABLE guestscompetitions ADD CONSTRAINT FK9 FOREIGN KEY (compID)
121        REFERENCES competitions (cid) MATCH SIMPLE
122        ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE;
123
124ALTER TABLE guestscompetitions ADD CONSTRAINT FK10 FOREIGN KEY (guestID)
125        REFERENCES guests (gid) MATCH SIMPLE
126        ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE;
127
128 ALTER TABLE sponsorscompetitions ADD CONSTRAINT FK7 FOREIGN KEY (compID)
129        REFERENCES competitions (cid) MATCH SIMPLE
130        ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE;
131
132 ALTER TABLE sponsorscompetitions ADD CONSTRAINT FK8 FOREIGN KEY (sponsorID)
133        REFERENCES sponsors (spid) MATCH SIMPLE
134        ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE;
135
136ALTER TABLE juricompetitions ADD CONSTRAINT FK12 FOREIGN KEY (compID)
137        REFERENCES competitions (cid) MATCH SIMPLE
138        ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE;
139
140ALTER TABLE juricompetitions ADD CONSTRAINT FK13 FOREIGN KEY (juriID)
141        REFERENCES juri (jid) MATCH SIMPLE
142        ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE;
143
144ALTER TABLE competitionsteams ADD CONSTRAINT FK4 FOREIGN KEY (teamID)
145        REFERENCES teams (tid) MATCH SIMPLE
146        ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE;
147
148CREATE FUNCTION functionAgeACM() RETURNS trigger AS $triggerAgeACM$ 
149    -- DECLARE
150    --     variable int; 
151    BEGIN
152        IF (exists(
153            SELECT * FROM competitions WHERE 
154                NEW.compID = competitions.cid AND competitions.ctype = 0 AND NEW.teamID IN (
155                    -- команды, у которых есть участник старше 25 лет
156                    SELECT participants.teamID FROM participants WHERE
157                        date_part('year', age( competitions.cdate, participants.birthday)) > 25
158                ) 
159        )) THEN
160            RAISE EXCEPTION 'ACM AGE trigger failed';
161        END IF;
162        RETURN NEW;
163    END;
164$triggerAgeACM$ LANGUAGE plpgsql;
165
166CREATE CONSTRAINT TRIGGER triggerAgeACM AFTER INSERT or UPDATE
167ON competitionsteams 
168DEFERRABLE
169FOR EACH ROW EXECUTE PROCEDURE functionAgeACM();
170
171
172
173
174
175
176-- data initialization
177
178BEGIN;
179  SET CONSTRAINTS ALL DEFERRED;
180
181  INSERT INTO universities (uniname)
182  VALUES
183    ('ITMO');
184
185  INSERT INTO participants (pname, rating, birthday, universitiesID, teamID)
186  VALUES
187    ('Nata', 1500, timestamp '1996-12-14', 1, 1);
188
189  INSERT INTO trainers (trname, rating, job)
190  VALUES
191    ('Stankevich', 1000, 'ITMO');
192
193  INSERT INTO teams (tname, trainerID)
194  VALUES
195   ('Zvezdochka', 1);
196
197  INSERT INTO juri (jname)
198  VALUES
199  ('Korneev'), ('Nigmatullin');
200
201  INSERT INTO juricompetitions (compID, juriID)
202  VALUES
203  (1, 1), (1, 2);
204
205  INSERT INTO competitionsteams (teamID, compID)
206  VALUES
207  (1, 1);
208
209  INSERT INTO competitions (cname, ctype, cdate, compteamID) 
210  VALUES
211  ('QF', 0, timestamp '2018-12-14', 1);
212COMMIT;