7yybMnbT

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,/*null or 0 if have not command now*/
43player_name VARCHAR(50) NOT NULL,
44player_surname VARCHAR(50) NOT NULL,
45player_country  VARCHAR(50) NOT NULL,
46player_born_date DateTime NOT NULL,
47player_amplua VARCHAR(50) NOT NULL,/*goalkeeper defender*/
48CONSTRAINT UniquePlayers UNIQUE (player_name,player_surname,player_country,player_born_date/*,team_id*/),
49PRIMARY KEY(player_id)
50);/*+*/
51 
52 
53CREATE TABLE Player_Contracts
54(
55contract_id INT IDENTITY(1,1) NOT NULL,
56team_id INT NOT NULL,
57player_id INT NOT NULL,
58 
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 Team_Tournament
68(
69team_tournament_id INT IDENTITY(1,1) NOT NULL,
70tournament_id INT NOT NULL,
71team_id INT NOT NULL,
72PRIMARY KEY(team_tournament_id )
73);
74/*common table for Tour and team*/
75 
76CREATE TABLE Tournament
77(
78tournament_id INT IDENTITY(1,1) NOT NULL,
79tournament_name VARCHAR(50) NOT NULL,
80tournament_status  VARCHAR(50) NOT NULL,/*international local*/
81tournament_participans_origin  VARCHAR(50) NOT NULL,
82tournament_start Datetime not null,
83tournament_finish Datetime not null,
84/*CONSTRAINT UniqueTournament UNIQUE (player_name,player_surname,player_country,player_born_date/*,team_id*/),*/
85PRIMARY KEY(tournament_id)
86);/*+*/
87 
88CREATE TABLE Game
89(
90game_id INT IDENTITY(1,1) NOT NULL,
91tournament_id INT NOT NULL,
92stadion_id INT ,
93/*referie_id INT NOT NULL,*/
94home_team INT NOT NULL,
95guest_team INT NOT NULL,
96 
97 
98home_team_score INT DEFAULT 0,
99guest_team_score INT DEFAULT 0 ,
100overtime_home_team_score INT DEFAULT 0,
101overtime_guest_team_score INT DEFAULT 0,
102bullits_home_team_score INT DEFAULT 0,
103bullits_guest_team_score INT DEFAULT 0,
104start_time DATETIME DEFAULT NULL,
105 
106PRIMARY KEY(game_id)
107);/*+*/
108 
109CREATE TABLE GameApplication
110(
111gameApplication_id INT IDENTITY(1,1) NOT NULL,
112game_id INT NOT NULL,
113player_id INT NOT NULL,
114in_start_squad bit not null,
115PRIMARY KEY(gameApplication_id),
116CONSTRAINT UC UNIQUE (gameApplication_id,game_id,player_id/*,team_id*/)
117);/*+*/
118 
119 
120CREATE TABLE GameLog
121(
122gameLog_id INT IDENTITY(1,1) NOT NULL,
123game_id INT NOT NULL,
124game_period  INT,/* 1- 3 0 */
125overtime_period INT,/* 1-2  0  якщо буліти або основний час*/
126action_time TIME /**/,
127 
128action_initiator_id INT /**/,
129action_reciever_id INT /**/,
130action_name VARCHAR(50) NOT NULL
131PRIMARY KEY(gameLog_id)
132);
133 
134 
135ALTER TABLE Players
136WITH CHECK ADD CONSTRAINT FK_Current_Team_ID FOREIGN KEY(current_team_id)
137REFERENCES Team(team_id);
138 
139ALTER TABLE Player_Contracts
140WITH CHECK ADD CONSTRAINT FK_Team1_ID FOREIGN KEY(team_id)
141REFERENCES Team(team_id);
142 
143ALTER TABLE Player_Contracts
144WITH CHECK ADD CONSTRAINT FK_Player_Id FOREIGN KEY(player_id )
145REFERENCES Players(player_id);
146 
147ALTER TABLE Team_Tournament
148WITH CHECK ADD CONSTRAINT FK_Tournament_id FOREIGN KEY(tournament_id)
149REFERENCES Tournament(tournament_id);
150 
151ALTER TABLE Team_Tournament
152WITH CHECK ADD CONSTRAINT FK_Team3_id FOREIGN KEY(team_id)
153REFERENCES Team(team_id);
154 
155ALTER TABLE Game
156WITH CHECK ADD CONSTRAINT FK_Tournament2_id FOREIGN KEY(tournament_id)
157REFERENCES Tournament(tournament_id);
158 
159 ALTER TABLE Team
160WITH CHECK ADD CONSTRAINT FK_team_main_stadion_id FOREIGN KEY(team_main_stadion)
161REFERENCES Stadion(stadion_id);
162 
163ALTER TABLE Game
164WITH CHECK ADD CONSTRAINT FK_Stadion_id FOREIGN KEY(stadion_id)
165REFERENCES Stadion(stadion_id);
166 
167 
168ALTER TABLE GameApplication
169WITH CHECK ADD CONSTRAINT FK_Game1_id FOREIGN KEY(game_id)
170REFERENCES Game(game_id);
171 
172 ALTER TABLE GameApplication
173WITH CHECK ADD CONSTRAINT FK_Game15_id FOREIGN KEY(player_id)
174REFERENCES Players(player_id);
175 
176ALTER TABLE GameLog
177WITH CHECK ADD CONSTRAINT FK_Game2_id FOREIGN KEY(game_id)
178REFERENCES  Game(game_id);
179 
180 
181 
182 ALTER TABLE Game
183WITH CHECK ADD CONSTRAINT FK_Team_ID_199 FOREIGN KEY(home_team)
184REFERENCES Team(team_id);
185 
186 ALTER TABLE Game
187WITH CHECK ADD CONSTRAINT FK_Team_ID_1999 FOREIGN KEY(guest_team)
188REFERENCES Team(team_id);
189 
190ALTER TABLE GameLog
191WITH CHECK ADD CONSTRAINT FK_GameLogPlayerInitiator_id FOREIGN KEY(action_initiator_id)
192REFERENCES  Players(player_id);
193 
194ALTER TABLE GameLog
195WITH CHECK ADD CONSTRAINT FK_GameLogPlayerReciever_id FOREIGN KEY(action_reciever_id)
196REFERENCES  Players(player_id);