94jLs0mk

1USE [master]
2 
3IF NOT EXISTS (SELECT name FROM sys.DATABASES WHERE name = N'HockeyStatistic1999')
4    CREATE DATABASE [HockeyStatistic1999]
5ELSE
6    DROP DATABASE [HockeyStatistic1999]
7    CREATE DATABASE [HockeyStatistic1999]
8GO
9USE [HockeyStatistic1999]
10 
11CREATE TABLE Team
12(
13team_id INT IDENTITY(1,1) NOT NULL,
14team_country VARCHAR(50) NOT NULL,
15team_city VARCHAR(50) NOT NULL,
16team_name VARCHAR(50) NOT NULL,
17team_fondation_date DateTime NOT NULL,
18team_main_stadion INT,/*-1 якшо нема*/
19PRIMARY KEY(team_id)
20);/*+*/
21 
22 /*
23CREATE TABLE Team_Statistic
24(
25team_statistic_id INT IDENTITY(1,1) NOT NULL,
26team_id INT NOT NULL,
27 
28wins INT NOT NULL,
29loses INT NOT NULL,
30loses_on_draw INT NOT NULL,
31wins_on_draw INT NOT NULL,
32 
33PRIMARY KEY(team_statistic_id)
34);*//*+*/
35/*CREATE TABLE Team_Stadion
36(
37    Team_Stadion_id INT IDENTITY(1,1) NOT NULL,
38    team_id INT not null,
39    Stadion_id INT NOT NULL,
40    Start_stadion_use DateTime Not null,
41    Finish_stadion_use DateTime Not null,
42    PRIMARY KEY(Team_Stadion_id)
43);*/
44 
45 
46CREATE TABLE Stadion
47(
48stadion_id INT IDENTITY(1,1) NOT NULL,
49 
50stadion_name VARCHAR(50) NOT NULL,
51stadion_country VARCHAR(50) NOT NULL,
52stadion_city VARCHAR(50) NOT NULL,
53stadion_fondation_date DateTime NOT NULL,
54stadion_places_count INT NOT NULL,
55stadion_location_latitude FLOAT NOT NULL,
56stadion_location_longitude FLOAT NOT NULL,
57 
58PRIMARY KEY(stadion_id)
59);/*+*/
60 
61CREATE TABLE Players
62(
63player_id INT IDENTITY(1,1) NOT NULL,
64current_team_id INT NOT NULL,
65player_name VARCHAR(50) NOT NULL,
66player_surname VARCHAR(50) NOT NULL,
67player_country  VARCHAR(50) NOT NULL,
68player_born_date DateTime NOT NULL,
69PRIMARY KEY(player_id)
70);/*+*/
71 
72 
73CREATE TABLE Player_Contracts
74(
75contract_id INT IDENTITY(1,1) NOT NULL,
76team_id INT NOT NULL,
77player_id INT NOT NULL,
78player_amplua VARCHAR(50) NOT NULL,/*goalkeeper defender*/
79player_number_on_contract INT NOT NULL,
80contract_start_time DateTime NOT NULL,
81contract_deyure_end_time DateTime NOT NULL,
82contract_defacto_end_time DateTime,
83PRIMARY KEY(contract_id)
84);
85/*+*/
86/*
87CREATE TABLE Player_statistic
88(
89statistic_id INT IDENTITY(1,1) NOT NULL,
90contract_id INT NOT NULL,
91 
92total_time_played TIME NOT NULL,
93goals_from_game_bullits_count INT NOT NULL,
94goals_from_game_count INT NOT NULL,
95key_passes_for_goals_count INT NOT NULL,
96saves_count INT NOT NULL,
97two_min_fare INT NOT NULL,
98five_min_fare INT NOT NULL,
99 
100wins INT NOT NULL,
101loses INT NOT NULL,
102loses_on_draw INT NOT NULL,
103wins_on_draw INT NOT NULL,
104PRIMARY KEY(statistic_id)
105);*//*+-*/
106 
107CREATE TABLE Coach
108(
109coach_id INT IDENTITY(1,1) NOT NULL,
110current_team_id INT NOT NULL,
111coach_name VARCHAR(50) NOT NULL,
112coach_surname VARCHAR(50) NOT NULL,
113coach_country  VARCHAR(50) NOT NULL,
114coach_city VARCHAR(50) NOT NULL,
115coach_born_date DateTime NOT NULL,
116PRIMARY KEY(coach_id)
117);/*+*/
118 
119CREATE TABLE Coach_Contracts
120(
121contract_id INT IDENTITY(1,1) NOT NULL,
122team_id INT NOT NULL,
123coach_id INT NOT NULL,
124contract_start_time DateTime NOT NULL,
125contract_deyure_end_time DateTime NOT NULL,
126contract_defacto_end_time DateTime,
127PRIMARY KEY(contract_id)
128);
129/*+*/
130/*
131CREATE TABLE Coach_statistic
132(
133statistic_id INT IDENTITY(1,1) NOT NULL,
134contract_id INT NOT NULL,
135 
136wins INT NOT NULL,
137loses INT NOT NULL,
138loses_on_draw INT NOT NULL,
139wins_on_draw INT NOT NULL,
140 
141PRIMARY KEY(statistic_id)
142);*//*+*/
143 
144 
145CREATE TABLE Referies
146(
147 referie_id INT IDENTITY(1,1) NOT NULL,
148 referie_name VARCHAR(50) NOT NULL,
149 referie_surname VARCHAR(50) NOT NULL,
150 referie_born_date DateTime NOT NULL,
151 
152 
153PRIMARY KEY(referie_id)
154);/*+*/
155 
156 
157CREATE TABLE Team_Tournament
158(
159team_tournament_id INT IDENTITY(1,1) NOT NULL,
160tournament_id INT NOT NULL,
161team_id INT NOT NULL,
162PRIMARY KEY(team_tournament_id )
163);
164/*common table for Tour and team*/
165 
166CREATE TABLE Tournament
167(
168tournament_id INT IDENTITY(1,1) NOT NULL,
169tournament_name VARCHAR(50) NOT NULL,
170tournament_status  VARCHAR(50) NOT NULL,/*international local*/
171PRIMARY KEY(tournament_id)
172);/*+*/
173 
174CREATE TABLE Game
175(
176game_id INT IDENTITY(1,1) NOT NULL,
177tournament_id INT NOT NULL,
178stadion_id INT NOT NULL,
179referie_id INT NOT NULL,
180home_team INT NOT NULL,
181guest_team INT NOT NULL,
182 
183 Withoutovertime INT,
184 Withoutboolits INT,
185
186home_team_score INT NOT NULL,
187guest_team_score INT NOT NULL,
188start_time DATE NOT NULL,
189 
190PRIMARY KEY(game_id)
191);/*+*/
192 
193CREATE TABLE GameApplication
194(
195gameApplication_id INT IDENTITY(1,1) NOT NULL,
196game_id INT NOT NULL,
197player_id INT NOT NULL,
198team_id INT NOT NULL,
199PRIMARY KEY(gameApplication_id),
200CONSTRAINT UC UNIQUE (gameApplication_id,game_id,player_id,team_id)
201);/*+*/
202 
203 
204CREATE TABLE GameLog
205(
206gameLog_id INT IDENTITY(1,1) NOT NULL,
207game_id INT NOT NULL,
208game_period  INT,
209overtime INT,
210action_time TIME NOT NULL,
211
212player_id INT NOT NULL,
213player_action INT ,/*action of player triger for statistic and score update*/
214PRIMARY KEY(gameLog_id)
215);
216 
217CREATE TABLE PlayerAction
218(
219    player_Action_id INT IDENTITY(1,1) NOT NULL,
220    action_commited VARCHAR(50) NOT NULL, /*goal, assist, save, boolit, noboolit*/
221    PRIMARY KEY(player_Action_id)
222);
223 
224 
225/*ALTER TABLE Team_statistic
226WITH CHECK ADD CONSTRAINT FK_Team_ID FOREIGN KEY(team_id)
227REFERENCES Team(team_id);*/
228 
229ALTER TABLE Players
230WITH CHECK ADD CONSTRAINT FK_Current_Team_ID FOREIGN KEY(current_team_id)
231REFERENCES Team(team_id);
232 
233ALTER TABLE Player_Contracts
234WITH CHECK ADD CONSTRAINT FK_Team1_ID FOREIGN KEY(team_id)
235REFERENCES Team(team_id);
236 
237ALTER TABLE Player_Contracts
238WITH CHECK ADD CONSTRAINT FK_Player_Id FOREIGN KEY(player_id )
239REFERENCES Players(player_id);
240 
241/*ALTER TABLE Player_statistic
242WITH CHECK ADD CONSTRAINT FK_Contract_Id FOREIGN KEY(contract_id)
243REFERENCES Player_Contracts(contract_id );*/
244 
245ALTER TABLE Coach
246WITH CHECK ADD CONSTRAINT FK_Current_team1_id FOREIGN KEY(current_team_id)
247REFERENCES Team(team_id);
248 
249ALTER TABLE Coach_Contracts
250WITH CHECK ADD CONSTRAINT FK_Team2_id FOREIGN KEY(team_id)
251REFERENCES Team(team_id);
252 
253ALTER TABLE Coach_Contracts
254WITH CHECK ADD CONSTRAINT FK_Coach_id FOREIGN KEY(coach_id)
255REFERENCES Coach(coach_id);
256 
257/*ALTER TABLE Coach_statistic
258WITH CHECK ADD CONSTRAINT FK_Contract7_id FOREIGN KEY(contract_id)
259REFERENCES Coach_Contracts(contract_id);*/
260 
261ALTER TABLE Team_Tournament
262WITH CHECK ADD CONSTRAINT FK_Tournament_id FOREIGN KEY(tournament_id)
263REFERENCES Tournament(tournament_id);
264 
265ALTER TABLE Team_Tournament
266WITH CHECK ADD CONSTRAINT FK_Team3_id FOREIGN KEY(team_id)
267REFERENCES Team(team_id);
268 
269ALTER TABLE Game
270WITH CHECK ADD CONSTRAINT FK_Tournament2_id FOREIGN KEY(tournament_id)
271REFERENCES Tournament(tournament_id);
272 
273ALTER TABLE Game
274WITH CHECK ADD CONSTRAINT FK_Stadion_id FOREIGN KEY(stadion_id)
275REFERENCES Stadion(stadion_id);
276 
277ALTER TABLE Game
278WITH CHECK ADD CONSTRAINT FK_Referie_id FOREIGN KEY(referie_id)
279REFERENCES Referies(referie_id);
280 
281ALTER TABLE GameApplication
282WITH CHECK ADD CONSTRAINT FK_Game1_id FOREIGN KEY(game_id)
283REFERENCES Game(game_id);
284 
285ALTER TABLE GameLog
286WITH CHECK ADD CONSTRAINT FK_Game2_id FOREIGN KEY(game_id)
287REFERENCES  Game(game_id);
288 
289ALTER TABLE GameLog
290WITH CHECK ADD CONSTRAINT FK_Player_Action_id FOREIGN KEY(player_action)
291REFERENCES   PlayerAction(player_Action_id);
292 
293 
294 
295 
296 
297 
298 ALTER TABLE Game
299WITH CHECK ADD CONSTRAINT FK_Team_ID_199 FOREIGN KEY(home_team)
300REFERENCES Team(team_id);
301 
302 ALTER TABLE Game
303WITH CHECK ADD CONSTRAINT FK_Team_ID_1999 FOREIGN KEY(guest_team)
304REFERENCES Team(team_id);