sCufvLzK

1
2use [master]
3 
4IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'HockeyStatistic')
5    CREATE DATABASE [HockeyStatistic]
6else
7    DROP DATABASE [HockeyStatistic]
8    CREATE DATABASE [HockeyStatistic]
9go
10use [HockeyStatistic]
11 
12CREATE TABLE Team
13(
14team_id INT IDENTITY(1,1) NOT NULL,
15team_country VARCHAR(50) NOT NULL,
16team_city VARCHAR(50) NOT NULL,
17team_name VARCHAR(50) NOT NULL,
18team_fondation_date DateTime Not null,
19team_main_stadion int,/*-1 якшо нема*/
20PRIMARY KEY(team_id)
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/*+*/
86CREATE TABLE Player_statistic
87(
88statistic_id INT IDENTITY(1,1) NOT NULL,
89contract_id int not Null,
90 
91total_time_played time not null,
92goals_from_game_bullits_count int not null,
93goals_from_game_count int not Null,
94key_passes_for_goals_count int not Null,
95saves_count int not Null,
96two_min_fare int not Null,
97five_min_fare int not Null,
98 
99wins int not null,
100loses int not null,
101loses_on_draw int not null,
102wins_on_draw int not null,
103PRIMARY KEY(statistic_id)
104);/*+-*/
105 
106CREATE TABLE Coach
107(
108coach_id INT IDENTITY(1,1) NOT NULL,
109current_team_id int not Null,
110coach_name VARCHAR(50) NOT NULL,
111coach_surname VARCHAR(50) NOT NULL,
112coach_country  VARCHAR(50) NOT NULL,
113coach_city VARCHAR(50) NOT NULL,
114coach_born_date DateTime Not null,
115PRIMARY KEY(coach_id)
116);/*+*/
117 
118CREATE TABLE Coach_Contracts
119(
120contract_id INT IDENTITY(1,1) NOT NULL,
121team_id int not Null,
122coach_id int not Null,
123contract_start_time DateTime not null,
124contract_deyure_end_time DateTime not null,
125contract_defacto_end_time DateTime,
126PRIMARY KEY(contract_id)
127);
128/*+*/
129CREATE TABLE Coach_statistic
130(
131statistic_id INT IDENTITY(1,1) NOT NULL,
132contract_id int not Null,
133 
134wins int not null,
135loses int not null,
136loses_on_draw int not null,
137wins_on_draw int not null,
138 
139PRIMARY KEY(statistic_id)
140);/*+*/
141 
142 
143CREATE TABLE Referies
144(
145 referie_id INT IDENTITY(1,1) NOT NULL,
146 referie_name VARCHAR(50) NOT NULL,
147 referie_surname VARCHAR(50) NOT NULL,
148 referie_born_date DateTime Not null,
149 
150 
151PRIMARY KEY(referie_id)
152);/*+*/
153 
154 
155CREATE TABLE Team_Tournament
156(
157team_tournament_id INT IDENTITY(1,1) NOT NULL,
158tournament_id int not null,
159team_id int not null,
160PRIMARY KEY(team_tournament_id )
161);
162/*common table for Tour and team*/
163 
164CREATE TABLE Tournament
165(
166tournament_id INT IDENTITY(1,1) NOT NULL,
167tournament_name VARCHAR(50) NOT NULL,
168tournament_status  VARCHAR(50) NOT NULL,/*international local*/
169PRIMARY KEY(tournament_id)
170);/*+*/
171 
172CREATE TABLE Game
173(
174game_id INT IDENTITY(1,1) NOT NULL,
175tournament_id int NOT NULL,
176stadion_id int not Null,
177referie_id int not null,
178home_team int not null,
179guest_team int not null,
180
181home_team_score int not null,
182guest_team_score int not null,
183start_time date not null,
184 
185PRIMARY KEY(game_id)
186);/*+*/
187 
188CREATE TABLE GameApplication
189(
190gameApplication_id INT IDENTITY(1,1) NOT NULL,
191game_id int NOT NULL,
192player_id int not null,
193team_id int not null,
194PRIMARY KEY(gameApplication_id)
195);/*+*/
196 
197 
198CREATE TABLE GameLog
199(
200gameLog_id INT IDENTITY(1,1) NOT NULL,
201game_id int not null,
202 
203action_time Time not null,
204main_game_period bit not null,
205game_period int not null,
206player_id int not null,
207player_action int ,/*action of player triger for statistic and score update*/
208PRIMARY KEY(gameLog_id)
209);
210 
211CREATE TABLE PlayerAction
212(
213    player_Action_id INT IDENTITY(1,1) NOT NULL,
214    action_commited VARCHAR(50) NOT NULL, /*goal, assist, save, boolit, noboolit*/
215    PRIMARY KEY(player_Action_id)
216);
217 
218 
219 
220ALTER TABLE Team_statistic
221WITH CHECK ADD CONSTRAINT FK_Team_ID FOREIGN KEY(team_id)
222REFERENCES Team(team_id);
223 
224ALTER TABLE Players
225WITH CHECK ADD CONSTRAINT FK_Current_Team_ID FOREIGN KEY(current_team_id)
226REFERENCES Team(team_id);
227 
228ALTER TABLE Player_Contracts
229WITH CHECK ADD CONSTRAINT FK_Team1_ID FOREIGN KEY(team_id)
230REFERENCES Team(team_id);
231 
232ALTER TABLE Player_Contracts
233WITH CHECK ADD CONSTRAINT FK_Player_Id FOREIGN KEY(player_id )
234REFERENCES Players(player_id);
235 
236ALTER TABLE Player_statistic
237WITH CHECK ADD CONSTRAINT FK_Contract_Id FOREIGN KEY(contract_id)
238REFERENCES Player_Contracts(contract_id );
239 
240ALTER TABLE Coach
241WITH CHECK ADD CONSTRAINT FK_Current_team1_id FOREIGN KEY(current_team_id)
242REFERENCES Team(team_id);
243 
244ALTER TABLE Coach_Contracts
245WITH CHECK ADD CONSTRAINT FK_Team2_id FOREIGN KEY(team_id)
246REFERENCES Team(team_id);
247 
248ALTER TABLE Coach_Contracts
249WITH CHECK ADD CONSTRAINT FK_Coach_id FOREIGN KEY(coach_id)
250REFERENCES Coach(coach_id);
251 
252ALTER TABLE Coach_statistic
253WITH CHECK ADD CONSTRAINT FK_Contract7_id FOREIGN KEY(contract_id)
254REFERENCES Coach_Contracts(contract_id);
255 
256ALTER TABLE Team_Tournament
257WITH CHECK ADD CONSTRAINT FK_Tournament_id FOREIGN KEY(tournament_id)
258REFERENCES Tournament(tournament_id);
259 
260ALTER TABLE Team_Tournament
261WITH CHECK ADD CONSTRAINT FK_Team3_id FOREIGN KEY(team_id)
262REFERENCES Team(team_id);
263 
264ALTER TABLE Game
265WITH CHECK ADD CONSTRAINT FK_Tournament2_id FOREIGN KEY(tournament_id)
266REFERENCES Tournament(tournament_id);
267 
268ALTER TABLE Game
269WITH CHECK ADD CONSTRAINT FK_Stadion_id FOREIGN KEY(stadion_id)
270REFERENCES Stadion(stadion_id);
271 
272ALTER TABLE Game
273WITH CHECK ADD CONSTRAINT FK_Referie_id FOREIGN KEY(referie_id)
274REFERENCES Referies(referie_id);
275 
276ALTER TABLE GameApplication
277WITH CHECK ADD CONSTRAINT FK_Game1_id FOREIGN KEY(game_id)
278REFERENCES Game(game_id);
279 
280ALTER TABLE GameLog
281WITH CHECK ADD CONSTRAINT FK_Game2_id FOREIGN KEY(game_id)
282REFERENCES  Game(game_id);
283
284ALTER TABLE GameLog
285WITH CHECK ADD CONSTRAINT FK_Player_Action_id FOREIGN KEY(player_action)
286REFERENCES   PlayerAction(player_Action_id);