cUmGQfnU

1USE [master]
2 
3IF NOT EXISTS (SELECT name FROM sys.DATABASES WHERE name = N'HockeyStatisticUAUA')
4    CREATE DATABASE [HockeyStatisticUAUA]
5ELSE
6    DROP DATABASE [HockeyStatisticUAUA]
7    CREATE DATABASE [HockeyStatisticUAUA]
8GO
9USE [HockeyStatisticUAUA]
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,/*null -1 якшо нема*/
19CONSTRAINT UniqueTeams UNIQUE (team_country,team_city,team_name/*,team_id*/),
20PRIMARY KEY(team_id)
21);/*+*/
22CREATE TABLE Trainer
23(
24trainer_id INT IDENTITY(1,1) NOT NULL,
25 
26trainer_name VARCHAR(50) NOT NULL,
27trainer_surname VARCHAR(50) NOT NULL,
28trainer_country VARCHAR(50) NOT NULL,
29birth_date DateTime not null,
30current_team_id int,
31 
32 
33PRIMARY KEY(trainer_id)
34);/*+*/
35 
36 
37CREATE TABLE Stadion
38(
39stadion_id INT IDENTITY(1,1) NOT NULL,
40 
41stadion_name VARCHAR(50) NOT NULL,
42stadion_country VARCHAR(50) NOT NULL,
43stadion_city VARCHAR(50) NOT NULL,
44 
45 
46PRIMARY KEY(stadion_id)
47);/*+*/
48 
49CREATE TABLE Players
50(
51player_id INT IDENTITY(1,1) NOT NULL,
52current_team_id INT,/*null or 0 if have not command now*/
53player_name VARCHAR(50) NOT NULL,
54player_surname VARCHAR(50) NOT NULL,
55player_country  VARCHAR(50) NOT NULL,
56player_born_date DateTime NOT NULL,
57player_amplua VARCHAR(50) NOT NULL,/*goalkeeper defender*/
58CONSTRAINT UniquePlayers UNIQUE (player_name,player_surname,player_country,player_born_date/*,team_id*/),
59PRIMARY KEY(player_id)
60);/*+*/
61 
62 
63CREATE TABLE Player_Contracts
64(
65contract_id INT IDENTITY(1,1) NOT NULL,
66team_id INT NOT NULL,
67player_id INT NOT NULL,
68 
69contract_start_time DateTime NOT NULL,
70contract_deyure_end_time DateTime NOT NULL,
71contract_defacto_end_time DateTime,
72PRIMARY KEY(contract_id)
73);
74/*+*/
75 
76 
77CREATE TABLE Team_Tournament
78(
79team_tournament_id INT IDENTITY(1,1) NOT NULL,
80tournament_id INT NOT NULL,
81team_id INT NOT NULL,
82PRIMARY KEY(team_tournament_id )
83);
84/*common table for Tour and team*/
85 
86CREATE TABLE Tournament
87(
88tournament_id INT IDENTITY(1,1) NOT NULL,
89tournament_name VARCHAR(50) NOT NULL,
90tournament_status  VARCHAR(50) NOT NULL,/*international local*/
91tournament_participans_origin  VARCHAR(50) NOT NULL,
92tournament_start Datetime not null,
93tournament_finish Datetime not null,
94/*CONSTRAINT UniqueTournament UNIQUE (player_name,player_surname,player_country,player_born_date/*,team_id*/),*/
95PRIMARY KEY(tournament_id)
96);/*+*/
97 
98CREATE TABLE Game
99(
100game_id INT IDENTITY(1,1) NOT NULL,
101tournament_id INT NOT NULL,
102 
103/*referie_id INT NOT NULL,*/
104home_team INT NOT NULL,
105guest_team INT NOT NULL,
106 
107stadion_id INT ,
108home_team_trainer_id INT,
109guest_team_trainer_id INT,
110 
111home_team_score INT DEFAULT 0,
112guest_team_score INT DEFAULT 0 ,
113overtime_home_team_score INT DEFAULT 0,
114overtime_guest_team_score INT DEFAULT 0,
115bullits_home_team_score INT DEFAULT 0,
116bullits_guest_team_score INT DEFAULT 0,
117start_time DATETIME DEFAULT NULL,
118 
119PRIMARY KEY(game_id)
120);/*+*/
121 
122CREATE TABLE PlayerGameApplication
123(
124gameApplication_id INT IDENTITY(1,1) NOT NULL,
125game_id INT NOT NULL,
126player_id INT NOT NULL,
127team_id int not null,
128ganeral_game_time_goal_scored int Default 0,
129overtime_goal_scored int Default 0,
130after_total_time_bullit_scored int Default 0,
131PRIMARY KEY(gameApplication_id),
132CONSTRAINT UC UNIQUE (gameApplication_id,game_id,player_id/*,team_id*/)
133);/*+*/
134 
135 
136CREATE TABLE GameLog
137(
138gameLog_id INT IDENTITY(1,1) NOT NULL,
139game_id INT NOT NULL,
140game_period  INT,/* 1- 3 0 */
141overtime_period INT,/* 1-2  0  якщо буліти або основний час*/
142action_time TIME /**/,
143 
144action_initiator_id INT /**/,
145action_reciever_id INT /**/,
146action_name VARCHAR(50) NOT NULL
147PRIMARY KEY(gameLog_id)
148);
149 
150 
151ALTER TABLE Players
152WITH CHECK ADD CONSTRAINT FK_Current_Team_ID FOREIGN KEY(current_team_id)
153REFERENCES Team(team_id);
154 
155ALTER TABLE Player_Contracts
156WITH CHECK ADD CONSTRAINT FK_Team1_ID FOREIGN KEY(team_id)
157REFERENCES Team(team_id);
158 
159ALTER TABLE Player_Contracts
160WITH CHECK ADD CONSTRAINT FK_Player_Id FOREIGN KEY(player_id )
161REFERENCES Players(player_id);
162 
163ALTER TABLE Team_Tournament
164WITH CHECK ADD CONSTRAINT FK_Tournament_id FOREIGN KEY(tournament_id)
165REFERENCES Tournament(tournament_id);
166 
167ALTER TABLE Team_Tournament
168WITH CHECK ADD CONSTRAINT FK_Team3_id FOREIGN KEY(team_id)
169REFERENCES Team(team_id);
170 
171ALTER TABLE Game
172WITH CHECK ADD CONSTRAINT FK_Tournament2_id FOREIGN KEY(tournament_id)
173REFERENCES Tournament(tournament_id);
174 
175 ALTER TABLE Team
176WITH CHECK ADD CONSTRAINT FK_team_main_stadion_id FOREIGN KEY(team_main_stadion)
177REFERENCES Stadion(stadion_id);
178 
179ALTER TABLE Game
180WITH CHECK ADD CONSTRAINT FK_Stadion_id FOREIGN KEY(stadion_id)
181REFERENCES Stadion(stadion_id);
182 
183 
184ALTER TABLE PlayerGameApplication
185WITH CHECK ADD CONSTRAINT FK_Game1_id FOREIGN KEY(game_id)
186REFERENCES Game(game_id);
187 
188 ALTER TABLE PlayerGameApplication
189WITH CHECK ADD CONSTRAINT FK_Game15_id FOREIGN KEY(player_id)
190REFERENCES Players(player_id);
191 
192ALTER TABLE GameLog
193WITH CHECK ADD CONSTRAINT FK_Game2_id FOREIGN KEY(game_id)
194REFERENCES  Game(game_id);
195 
196 
197 
198 ALTER TABLE Game
199WITH CHECK ADD CONSTRAINT FK_Team_ID_199 FOREIGN KEY(home_team)
200REFERENCES Team(team_id);
201 
202 ALTER TABLE Game
203WITH CHECK ADD CONSTRAINT FK_Team_ID_1999 FOREIGN KEY(guest_team)
204REFERENCES Team(team_id);
205 
206ALTER TABLE GameLog
207WITH CHECK ADD CONSTRAINT FK_GameLogPlayerInitiator_id FOREIGN KEY(action_initiator_id)
208REFERENCES  Players(player_id);
209 
210ALTER TABLE GameLog
211WITH CHECK ADD CONSTRAINT FK_GameLogPlayerReciever_id FOREIGN KEY(action_reciever_id)
212REFERENCES  Players(player_id);
213 
214ALTER TABLE Trainer
215WITH CHECK ADD CONSTRAINT FK_CurrentTeam_id FOREIGN KEY(current_team_id)
216REFERENCES Team(team_id);