j5zBQDTv

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