3DC5PEmd

1USE [master]
2 
3IF NOT EXISTS (SELECT name FROM sys.DATABASES WHERE name = N'HockeyStatisticUA')
4    CREATE DATABASE [HockeyStatisticUA]
5ELSE
6    DROP DATABASE [HockeyStatisticUA]
7    CREATE DATABASE [HockeyStatisticUA]
8GO
9USE [HockeyStatisticUA]
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_year int NOT NULL,
18team_main_stadion INT,/*-1 якшо нема*/
19CONSTRAINT UniqueTeams UNIQUE (team_country,team_city,team_name/*,team_id*/),
20PRIMARY KEY(team_id)
21);/*+*/
22 
23 
24CREATE TABLE Stadion
25(
26stadion_id INT IDENTITY(1,1) NOT NULL,
27 
28stadion_name VARCHAR(50) NOT NULL,
29stadion_country VARCHAR(50) NOT NULL,
30stadion_city VARCHAR(50) NOT NULL,
31/*stadion_fondation_date DateTime NOT NULL,*/
32stadion_places_count INT NOT NULL,
33/*stadion_location_latitude FLOAT NOT NULL,
34stadion_location_longitude FLOAT NOT NULL,*/
35 
36PRIMARY KEY(stadion_id)
37);/*+*/
38 
39CREATE TABLE Players
40(
41player_id INT IDENTITY(1,1) NOT NULL,
42current_team_id INT,
43player_name VARCHAR(50) NOT NULL,
44player_surname VARCHAR(50) NOT NULL,
45player_country  VARCHAR(50) NOT NULL,
46player_born_date DateTime NOT NULL,
47CONSTRAINT UniquePlayers UNIQUE (player_name,player_surname,player_country,player_born_date/*,team_id*/),
48PRIMARY KEY(player_id)
49);/*+*/
50 
51 
52CREATE TABLE Player_Contracts
53(
54contract_id INT IDENTITY(1,1) NOT NULL,
55team_id INT NOT NULL,
56player_id INT NOT NULL,
57player_amplua VARCHAR(50) NOT NULL,/*goalkeeper defender*/
58player_number_on_contract INT /**/,
59contract_start_time DateTime NOT NULL,
60contract_deyure_end_time DateTime NOT NULL,
61contract_defacto_end_time DateTime,
62PRIMARY KEY(contract_id)
63);
64/*+*/
65
66/*
67CREATE TABLE Coach
68(
69coach_id INT IDENTITY(1,1) NOT NULL,
70current_team_id INT NOT NULL,
71coach_name VARCHAR(50) NOT NULL,
72coach_surname VARCHAR(50) NOT NULL,
73coach_country  VARCHAR(50) NOT NULL,
74coach_city VARCHAR(50) NOT NULL,
75coach_born_date DateTime NOT NULL,
76PRIMARY KEY(coach_id)
77);/*+*/
78 
79CREATE TABLE Coach_Contracts
80(
81contract_id INT IDENTITY(1,1) NOT NULL,
82team_id INT NOT NULL,
83coach_id INT NOT NULL,
84contract_start_time DateTime NOT NULL,
85contract_deyure_end_time DateTime NOT NULL,
86contract_defacto_end_time DateTime,
87PRIMARY KEY(contract_id)
88);*/
89/*+*/
90
91 
92 
93/*CREATE TABLE Referies
94(
95 referie_id INT IDENTITY(1,1) NOT NULL,
96 referie_name VARCHAR(50) NOT NULL,
97 referie_surname VARCHAR(50) NOT NULL,
98 referie_born_date DateTime NOT NULL,
99 
100 
101PRIMARY KEY(referie_id)
102);*//*+*/
103 
104 
105CREATE TABLE Team_Tournament
106(
107team_tournament_id INT IDENTITY(1,1) NOT NULL,
108tournament_id INT NOT NULL,
109team_id INT NOT NULL,
110PRIMARY KEY(team_tournament_id )
111);
112/*common table for Tour and team*/
113 
114CREATE TABLE Tournament
115(
116tournament_id INT IDENTITY(1,1) NOT NULL,
117tournament_name VARCHAR(50) NOT NULL,
118tournament_status  VARCHAR(50) NOT NULL,/*international local*/
119PRIMARY KEY(tournament_id)
120);/*+*/
121 
122CREATE TABLE Game
123(
124game_id INT IDENTITY(1,1) NOT NULL,
125tournament_id INT NOT NULL,
126stadion_id INT NOT NULL,
127referie_id INT NOT NULL,
128home_team INT NOT NULL,
129guest_team INT NOT NULL,
130 
131 
132home_team_score INT NOT NULL,
133guest_team_score INT NOT NULL,
134overtime_home_team_score INT NOT NULL,
135overtime_guest_team_score INT NOT NULL,
136bullits_home_team_score INT NOT NULL,
137bullits_guest_team_score INT NOT NULL,
138start_time DATE NOT NULL,
139 
140PRIMARY KEY(game_id)
141);/*+*/
142 
143CREATE TABLE GameApplication
144(
145gameApplication_id INT IDENTITY(1,1) NOT NULL,
146game_id INT NOT NULL,
147player_id INT NOT NULL,
148in_start_squad bit not null,
149PRIMARY KEY(gameApplication_id),
150CONSTRAINT UC UNIQUE (gameApplication_id,game_id,player_id/*,team_id*/)
151);/*+*/
152 
153 
154CREATE TABLE GameLog
155(
156gameLog_id INT IDENTITY(1,1) NOT NULL,
157game_id INT NOT NULL,
158game_period  INT,/* 1- 3 0 */
159overtime_period INT,/* 1-2  0  якщо буліти або основний час*/
160action_time TIME /**/,
161 
162/*player_id INT NOT NULL, */
163/*player_action INT ,action of player triger for statistic and score update*/
164action_initiator_id INT /**/,
165action_reciever_id INT /**/,
166action_name VARCHAR(50) NOT NULL
167PRIMARY KEY(gameLog_id)
168);
169 
170/*CREATE TABLE PlayerAction
171(
172    player_Action_id INT IDENTITY(1,1) NOT NULL,
173    action_commited VARCHAR(50) NOT NULL, /*goal, assist, save, boolit, noboolit*/
174    PRIMARY KEY(player_Action_id)
175);*/
176 
177 
178/*ALTER TABLE Team_statistic
179WITH CHECK ADD CONSTRAINT FK_Team_ID FOREIGN KEY(team_id)
180REFERENCES Team(team_id);*/
181 
182ALTER TABLE Players
183WITH CHECK ADD CONSTRAINT FK_Current_Team_ID FOREIGN KEY(current_team_id)
184REFERENCES Team(team_id);
185 
186ALTER TABLE Player_Contracts
187WITH CHECK ADD CONSTRAINT FK_Team1_ID FOREIGN KEY(team_id)
188REFERENCES Team(team_id);
189 
190ALTER TABLE Player_Contracts
191WITH CHECK ADD CONSTRAINT FK_Player_Id FOREIGN KEY(player_id )
192REFERENCES Players(player_id);
193 
194 
195/*ALTER TABLE Coach
196WITH CHECK ADD CONSTRAINT FK_Current_team1_id FOREIGN KEY(current_team_id)
197REFERENCES Team(team_id);*/
198 
199/*ALTER TABLE Coach_Contracts
200WITH CHECK ADD CONSTRAINT FK_Team2_id FOREIGN KEY(team_id)
201REFERENCES Team(team_id);*/
202 
203/*ALTER TABLE Coach_Contracts
204WITH CHECK ADD CONSTRAINT FK_Coach_id FOREIGN KEY(coach_id)
205REFERENCES Coach(coach_id);*/
206
207 
208ALTER TABLE Team_Tournament
209WITH CHECK ADD CONSTRAINT FK_Tournament_id FOREIGN KEY(tournament_id)
210REFERENCES Tournament(tournament_id);
211 
212ALTER TABLE Team_Tournament
213WITH CHECK ADD CONSTRAINT FK_Team3_id FOREIGN KEY(team_id)
214REFERENCES Team(team_id);
215 
216ALTER TABLE Game
217WITH CHECK ADD CONSTRAINT FK_Tournament2_id FOREIGN KEY(tournament_id)
218REFERENCES Tournament(tournament_id);
219 
220ALTER TABLE Game
221WITH CHECK ADD CONSTRAINT FK_Stadion_id FOREIGN KEY(stadion_id)
222REFERENCES Stadion(stadion_id);
223 
224ALTER TABLE Game
225WITH CHECK ADD CONSTRAINT FK_Referie_id FOREIGN KEY(referie_id)
226REFERENCES Referies(referie_id);
227 
228ALTER TABLE GameApplication
229WITH CHECK ADD CONSTRAINT FK_Game1_id FOREIGN KEY(game_id)
230REFERENCES Game(game_id);
231 
232ALTER TABLE GameLog
233WITH CHECK ADD CONSTRAINT FK_Game2_id FOREIGN KEY(game_id)
234REFERENCES  Game(game_id);
235 
236/*ALTER TABLE GameLog
237WITH CHECK ADD CONSTRAINT FK_Player_Action_id FOREIGN KEY(player_action)
238REFERENCES   PlayerAction(player_Action_id);*/
239 
240 
241 
242 
243 
244 
245 ALTER TABLE Game
246WITH CHECK ADD CONSTRAINT FK_Team_ID_199 FOREIGN KEY(home_team)
247REFERENCES Team(team_id);
248 
249 ALTER TABLE Game
250WITH CHECK ADD CONSTRAINT FK_Team_ID_1999 FOREIGN KEY(guest_team)
251REFERENCES Team(team_id);