· 5 years ago · Apr 08, 2020, 05:00 PM
1/*==============================================================*/
2/* DBMS name: MySQL 5.0 */
3/* Created on: 4/8/2020 7:56:32 PM */
4/*==============================================================*/
5
6
7drop table if exists DEFENSEMAN;
8
9drop table if exists FORWARDS;
10
11drop table if exists GAMESPLAYED;
12
13drop table if exists GOALIES;
14
15drop table if exists PLAYERS;
16
17drop table if exists PLAYERSTATISTICS;
18
19drop table if exists PRACTICE;
20
21drop table if exists PRACTICEATTENDANCE;
22
23drop table if exists STUDENTS;
24
25drop table if exists TEAM;
26
27/*==============================================================*/
28/* Table: DEFENSEMAN */
29/*==============================================================*/
30create table DEFENSEMAN
31(
32 PLAYERID int not null,
33 TEAMID int not null,
34 PLA_PLAYERID int,
35 PLA_TEAMID int,
36 NAME text,
37 LASTNAME text,
38 PICTURE text,
39 PHONENUMBER text,
40 JERSEYNUMBER text,
41 GAMESPLAYED int,
42 PLAYOFFGAMESPLAYED int,
43 HASFIRSTGAME bool,
44 FIRSTGAME int,
45 BIRTHDAY date,
46 CAPTAIN_ASSISTENT int,
47 PRICEPERTIME float,
48 MONEYOWED float,
49 DEFENSEMAN bool,
50 primary key (PLAYERID, TEAMID)
51);
52
53/*==============================================================*/
54/* Table: FORWARDS */
55/*==============================================================*/
56create table FORWARDS
57(
58 PLAYERID int not null,
59 TEAMID int not null,
60 PLA_PLAYERID int,
61 PLA_TEAMID int,
62 NAME text,
63 LASTNAME text,
64 PICTURE text,
65 PHONENUMBER text,
66 JERSEYNUMBER text,
67 GAMESPLAYED int,
68 PLAYOFFGAMESPLAYED int,
69 HASFIRSTGAME bool,
70 FIRSTGAME int,
71 BIRTHDAY date,
72 CAPTAIN_ASSISTENT int,
73 PRICEPERTIME float,
74 MONEYOWED float,
75 FORWARD bool,
76 primary key (PLAYERID, TEAMID)
77);
78
79/*==============================================================*/
80/* Table: GAMESPLAYED */
81/*==============================================================*/
82create table GAMESPLAYED
83(
84 GAMEID int not null,
85 GAMEDATE date,
86 RESULT text,
87 ISPLAYOFFGAME bool,
88 TEAMID int,
89 GAM_GAMEID int,
90 primary key (GAMEID)
91);
92
93/*==============================================================*/
94/* Table: GOALIES */
95/*==============================================================*/
96create table GOALIES
97(
98 PLAYERID int not null,
99 TEAMID int not null,
100 PLA_PLAYERID int,
101 PLA_TEAMID int,
102 NAME text,
103 LASTNAME text,
104 PICTURE text,
105 PHONENUMBER text,
106 JERSEYNUMBER text,
107 GAMESPLAYED int,
108 PLAYOFFGAMESPLAYED int,
109 HASFIRSTGAME bool,
110 FIRSTGAME int,
111 BIRTHDAY date,
112 CAPTAIN_ASSISTENT int,
113 PRICEPERTIME float,
114 MONEYOWED float,
115 GOALIE bool,
116 primary key (PLAYERID, TEAMID)
117);
118
119/*==============================================================*/
120/* Table: PLAYERS */
121/*==============================================================*/
122create table PLAYERS
123(
124 PLAYERID int not null,
125 TEAMID int not null,
126 PLA_PLAYERID int,
127 PLA_TEAMID int,
128 NAME text not null,
129 LASTNAME text not null,
130 PICTURE text,
131 PHONENUMBER text,
132 JERSEYNUMBER text,
133 GAMESPLAYED int,
134 PLAYOFFGAMESPLAYED int,
135 HASFIRSTGAME bool,
136 FIRSTGAME int,
137 BIRTHDAY date,
138 CAPTAIN_ASSISTENT int,
139 PRICEPERTIME float,
140 MONEYOWED float,
141 primary key (PLAYERID, TEAMID)
142);
143
144/*==============================================================*/
145/* Table: PLAYERSTATISTICS */
146/*==============================================================*/
147create table PLAYERSTATISTICS
148(
149 PLAYERID int not null,
150 TEAMID int,
151 GAMEID int not null,
152 PLA_PLAYERID int,
153 PLA_GAMEID int,
154 GAMEDATE date,
155 GOALS int,
156 ASSISTS int,
157 PLUS_MINUS int,
158 PENALTYMINUTES int,
159 SHOTSONNET int,
160 POWERPLAYGOALS int,
161 GAMEWINNINGGOALS int,
162 FACEOFF int,
163 BLOCKEDSHOTS int,
164 FACEOFFSWON int,
165 BESTPLAYEROFTHEGAME bool,
166 primary key (PLAYERID, GAMEID)
167);
168
169/*==============================================================*/
170/* Table: PRACTICE */
171/*==============================================================*/
172create table PRACTICE
173(
174 PRACTICEID int not null,
175 PRACTICEDATE date,
176 NUMBEROFLINES int,
177 primary key (PRACTICEID)
178);
179
180/*==============================================================*/
181/* Table: PRACTICEATTENDANCE */
182/*==============================================================*/
183create table PRACTICEATTENDANCE
184(
185 PRACTICEID int not null,
186 PLAYERID int not null,
187 TEAMID int,
188 LINE int,
189 primary key (PRACTICEID)
190);
191
192/*==============================================================*/
193/* Table: STUDENTS */
194/*==============================================================*/
195create table STUDENTS
196(
197 VARDS text,
198 UZVARDS text,
199 STUDENTA_ID text,
200 KURSS int,
201 GRUPA int
202);
203
204/*==============================================================*/
205/* Table: TEAM */
206/*==============================================================*/
207create table TEAM
208(
209 TEAMID int not null,
210 TEAMNAME text,
211 LEAGUE text,
212 EMAIL text,
213 PASSWORD text,
214 primary key (TEAMID)
215);
216
217alter table DEFENSEMAN add constraint FK_KOMANDA_IR_AIZSTAGI foreign key (PLAYERID, TEAMID)
218 references PLAYERS (PLAYERID, TEAMID) on delete restrict on update restrict;
219
220alter table FORWARDS add constraint FK_KOMANDA_IR_UZBRUCEJI foreign key (PLAYERID, TEAMID)
221 references PLAYERS (PLAYERID, TEAMID) on delete restrict on update restrict;
222
223alter table GAMESPLAYED add constraint FK_KOMANDA_SPELE_HOKEJU foreign key (TEAMID)
224 references TEAM (TEAMID) on delete restrict on update restrict;
225
226alter table GAMESPLAYED add constraint FK_PARBAUDA_IR_IR_IZSLEGSANAS_SPELE foreign key (GAM_GAMEID)
227 references GAMESPLAYED (GAMEID) on delete restrict on update restrict;
228
229alter table GOALIES add constraint FK_KOMANDA_IR_VARTUSARGI foreign key (PLAYERID, TEAMID)
230 references PLAYERS (PLAYERID, TEAMID) on delete restrict on update restrict;
231
232alter table PLAYERS add constraint FK_KATRA_KOMANDA_IR_SPELETAJI foreign key (TEAMID)
233 references TEAM (TEAMID) on delete restrict on update restrict;
234
235alter table PLAYERS add constraint FK_PARBAUDA_KOMANDAS_KAPTEINI_UN_ASSISTENTUS foreign key (PLA_PLAYERID, PLA_TEAMID)
236 references PLAYERS (PLAYERID, TEAMID) on delete restrict on update restrict;
237
238alter table PLAYERSTATISTICS add constraint FK_KATRAI_SPELEI_IR_SPELETAJA_STATISTIKA foreign key (GAMEID)
239 references GAMESPLAYED (GAMEID) on delete restrict on update restrict;
240
241alter table PLAYERSTATISTICS add constraint FK_PARBAUDA_VAI_IR_SPELES_LABAKAIS foreign key (PLA_PLAYERID, PLA_GAMEID)
242 references PLAYERSTATISTICS (PLAYERID, GAMEID) on delete restrict on update restrict;
243
244alter table PLAYERSTATISTICS add constraint FK_SPELETAJA_STATISTIKA foreign key (PLAYERID, TEAMID)
245 references PLAYERS (PLAYERID, TEAMID) on delete restrict on update restrict;
246
247alter table PRACTICEATTENDANCE add constraint FK_SPELETAJI_DODAS_UZ_TRENNINIEM foreign key (PLAYERID, TEAMID)
248 references PLAYERS (PLAYERID, TEAMID) on delete restrict on update restrict;
249
250alter table PRACTICEATTENDANCE add constraint FK_TRENNINA_APMEKLEJUMS foreign key (PRACTICEID)
251 references PRACTICE (PRACTICEID) on delete restrict on update restrict;