kGxKTk3R

1DROP DATABASE IF EXISTS MyCoachDatabaseBig;
2
3CREATE DATABASE MyCoachDatabaseBig;
4
5GRANT ALL PRIVILEGES ON MyCoachDatabaseBig.* to admin@localhost IDENTIFIED BY 'admin';
6
7USE MyCoachDatabaseBig;
8
9CREATE TABLE `User` (
10    uid INTEGER AUTO_INCREMENT NOT NULL,
11    name VARCHAR(128),
12    email VARCHAR(128) UNIQUE,
13    password VARCHAR(54),
14    isAdmin BOOLEAN DEFAULT FALSE,
15    PRIMARY KEY (uid)
16);
17
18CREATE TABLE `Coach` (
19    uid INTEGER NOT NULL,
20    salary DOUBLE,
21    PRIMARY KEY (uid),
22    FOREIGN KEY (uid) REFERENCES User (uid)
23        ON DELETE CASCADE
24);
25
26CREATE TABLE `Athlete` (
27    uid INTEGER NOT NULL,
28    height DOUBLE,
29    weight DOUBLE,
30    PRIMARY KEY (uid),
31    FOREIGN KEY (uid) REFERENCES User (uid)
32        ON DELETE CASCADE
33);
34
35CREATE TABLE `TeamMascot` (
36    school VARCHAR(128),
37    mascot VARCHAR(128),
38    PRIMARY KEY (school)
39);
40
41CREATE TABLE `Team` (
42    tid INTEGER NOT NULL AUTO_INCREMENT,
43    school VARCHAR(128) NOT NULL,
44    hometown VARCHAR(128),
45    PRIMARY KEY (tid),
46    FOREIGN KEY (school) REFERENCES TeamMascot (school)
47        ON DELETE NO ACTION
48);
49
50CREATE TABLE `SportSeason` (
51    name VARCHAR(64),
52    season VARCHAR(6),
53    PRIMARY KEY (name)
54);
55
56CREATE TABLE `Sport` (
57    sid INTEGER AUTO_INCREMENT NOT NULL,
58    name VARCHAR(64),
59    PRIMARY KEY (sid),
60    FOREIGN KEY (name) REFERENCES SportSeason (name)
61        ON DELETE NO ACTION
62);
63
64CREATE TABLE `Workout` (
65    wid INTEGER AUTO_INCREMENT NOT NULL,
66    tid INTEGER NOT NULL,
67    date_assigned DATETIME,
68    uid INTEGER NOT NULL,
69    PRIMARY KEY (wid),
70    FOREIGN KEY (tid) REFERENCES Team (tid)
71        ON DELETE CASCADE,
72    FOREIGN KEY (uid) REFERENCES Coach (uid)
73        ON DELETE CASCADE
74);
75
76CREATE TABLE `ExerciseMuscles` (
77    name VARCHAR(128),
78    muscle_group VARCHAR(32),
79    PRIMARY KEY (name)
80);
81
82CREATE TABLE `Exercise` (
83    eid INTEGER AUTO_INCREMENT NOT NULL,
84    name VARCHAR(128) UNIQUE NOT NULL,
85    PRIMARY KEY (eid),
86    FOREIGN KEY (name) REFERENCES ExerciseMuscles (name)
87        ON DELETE CASCADE
88);
89
90CREATE TABLE `coaches` (
91    uid INTEGER NOT NULL,
92    tid INTEGER NOT NULL,
93    since DATE,
94    PRIMARY KEY (uid, tid),
95    FOREIGN KEY (uid) REFERENCES Coach (uid)
96        ON DELETE CASCADE,
97    FOREIGN KEY (tid) REFERENCES Team (tid)
98        ON DELETE CASCADE
99);
100
101CREATE TABLE `plays` (
102    sid INTEGER NOT NULL,
103    tid INTEGER NOT NULL,
104    PRIMARY KEY (sid, tid),
105    FOREIGN KEY (sid) REFERENCES Sport (sid)
106        ON DELETE CASCADE,
107    FOREIGN KEY (tid) REFERENCES Team (tid)
108        ON DELETE CASCADE
109);
110
111CREATE TABLE `member_of` (
112    uid INTEGER NOT NULL,
113    tid INTEGER NOT NULL,
114    position VARCHAR(10),
115    number INTEGER,
116    PRIMARY KEY (uid, tid),
117    FOREIGN KEY (uid) REFERENCES Athlete (uid)
118        ON DELETE CASCADE,
119    FOREIGN KEY (tid) REFERENCES Team (tid)
120        ON DELETE CASCADE
121);
122
123CREATE TABLE `consists_of` (
124    wid INTEGER,
125    eid INTEGER NOT NULL,
126    sets INTEGER,
127    reps INTEGER,
128    PRIMARY KEY (wid, eid),
129    FOREIGN KEY (wid) REFERENCES Workout (wid)
130        ON DELETE CASCADE,
131    FOREIGN KEY (eid) REFERENCES Exercise (eid)
132        ON DELETE CASCADE
133); 
134
135CREATE TABLE `does` (
136    wid INTEGER,
137    uid INTEGER,
138    date_done DATETIME,
139    PRIMARY KEY (wid, uid),
140    FOREIGN KEY (wid) REFERENCES Workout (wid)
141        ON DELETE CASCADE,
142    FOREIGN KEY (uid) REFERENCES Athlete (uid)
143        ON DELETE CASCADE
144); 
145
146
147CREATE TABLE `performance` (
148    eid INTEGER,
149    uid INTEGER,
150    wid INTEGER,
151    reps_performed INTEGER,
152    max_weight DOUBLE,
153    PRIMARY KEY (eid, uid, wid),
154    FOREIGN KEY (wid) REFERENCES Workout (wid)
155        ON DELETE CASCADE,
156    FOREIGN KEY (eid) REFERENCES Exercise (eid)
157        ON DELETE CASCADE,
158    FOREIGN KEY (uid) REFERENCES Athlete (uid)
159        ON DELETE CASCADE
160); 
161
162DELIMITER |
163CREATE PROCEDURE TeamProgress (
164    IN tid INTEGER,
165    IN wid INTEGER
166)
167BEGIN
168    (SELECT M.uid, A.name, FALSE as completed
169    FROM member_of M, User A
170    WHERE M.tid = tid 
171        AND M.uid = A.uid
172        AND M.uid NOT IN (SELECT D.uid FROM does D WHERE D.wid=wid)
173    )
174    UNION
175    (SELECT M.uid, A.name, TRUE as completed
176    FROM member_of M, User A
177    WHERE M.tid = tid 
178        AND M.uid = A.uid
179        AND M.uid IN (SELECT D.uid FROM does D WHERE D.wid=wid)
180    );
181END |
182DELIMITER ; 
183
184DELIMITER $$
185CREATE FUNCTION `NextTeamNumber` (tid INTEGER) RETURNS INTEGER
186BEGIN
187    DECLARE last INTEGER;
188    SELECT MAX(number) INTO last FROM member_of WHERE tid=tid;
189    RETURN last+1;
190END $$
191DELIMITER ;