PL1GUe1f

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