Cr2mqPP4

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*/
81PRIMARY KEY(tournament_id)
82);/*+*/
83 
84CREATE TABLE Game
85(
86game_id INT IDENTITY(1,1) NOT NULL,
87tournament_id INT NOT NULL,
88stadion_id INT NOT NULL,
89/*referie_id INT NOT NULL,*/
90home_team INT NOT NULL,
91guest_team INT NOT NULL,
92 
93 
94home_team_score INT NOT NULL,
95guest_team_score INT NOT NULL,
96overtime_home_team_score INT NOT NULL,
97overtime_guest_team_score INT NOT NULL,
98bullits_home_team_score INT NOT NULL,
99bullits_guest_team_score INT NOT NULL,
100start_time DATE NOT NULL,
101 
102PRIMARY KEY(game_id)
103);/*+*/
104 
105CREATE TABLE GameApplication
106(
107gameApplication_id INT IDENTITY(1,1) NOT NULL,
108game_id INT NOT NULL,
109player_id INT NOT NULL,
110in_start_squad bit not null,
111PRIMARY KEY(gameApplication_id),
112CONSTRAINT UC UNIQUE (gameApplication_id,game_id,player_id/*,team_id*/)
113);/*+*/
114 
115 
116CREATE TABLE GameLog
117(
118gameLog_id INT IDENTITY(1,1) NOT NULL,
119game_id INT NOT NULL,
120game_period  INT,/* 1- 3 0 */
121overtime_period INT,/* 1-2  0  якщо буліти або основний час*/
122action_time TIME /**/,
123
124action_initiator_id INT /**/,
125action_reciever_id INT /**/,
126action_name VARCHAR(50) NOT NULL
127PRIMARY KEY(gameLog_id)
128);
129
130 
131ALTER TABLE Players
132WITH CHECK ADD CONSTRAINT FK_Current_Team_ID FOREIGN KEY(current_team_id)
133REFERENCES Team(team_id);
134 
135ALTER TABLE Player_Contracts
136WITH CHECK ADD CONSTRAINT FK_Team1_ID FOREIGN KEY(team_id)
137REFERENCES Team(team_id);
138 
139ALTER TABLE Player_Contracts
140WITH CHECK ADD CONSTRAINT FK_Player_Id FOREIGN KEY(player_id )
141REFERENCES Players(player_id);
142 
143ALTER TABLE Team_Tournament
144WITH CHECK ADD CONSTRAINT FK_Tournament_id FOREIGN KEY(tournament_id)
145REFERENCES Tournament(tournament_id);
146 
147ALTER TABLE Team_Tournament
148WITH CHECK ADD CONSTRAINT FK_Team3_id FOREIGN KEY(team_id)
149REFERENCES Team(team_id);
150 
151ALTER TABLE Game
152WITH CHECK ADD CONSTRAINT FK_Tournament2_id FOREIGN KEY(tournament_id)
153REFERENCES Tournament(tournament_id);
154 
155 ALTER TABLE Team
156WITH CHECK ADD CONSTRAINT FK_team_main_stadion_id FOREIGN KEY(team_main_stadion)
157REFERENCES Stadion(stadion_id);
158
159ALTER TABLE Game
160WITH CHECK ADD CONSTRAINT FK_Stadion_id FOREIGN KEY(stadion_id)
161REFERENCES Stadion(stadion_id);
162 
163 
164ALTER TABLE GameApplication
165WITH CHECK ADD CONSTRAINT FK_Game1_id FOREIGN KEY(game_id)
166REFERENCES Game(game_id);
167 
168 ALTER TABLE GameApplication
169WITH CHECK ADD CONSTRAINT FK_Game15_id FOREIGN KEY(player_id)
170REFERENCES Players(player_id);
171
172ALTER TABLE GameLog
173WITH CHECK ADD CONSTRAINT FK_Game2_id FOREIGN KEY(game_id)
174REFERENCES  Game(game_id);
175 
176 
177 
178 ALTER TABLE Game
179WITH CHECK ADD CONSTRAINT FK_Team_ID_199 FOREIGN KEY(home_team)
180REFERENCES Team(team_id);
181 
182 ALTER TABLE Game
183WITH CHECK ADD CONSTRAINT FK_Team_ID_1999 FOREIGN KEY(guest_team)
184REFERENCES Team(team_id);
185
186ALTER TABLE GameLog
187WITH CHECK ADD CONSTRAINT FK_GameLogPlayerInitiator_id FOREIGN KEY(action_initiator_id)
188REFERENCES  Players(player_id);
189
190ALTER TABLE GameLog
191WITH CHECK ADD CONSTRAINT FK_GameLogPlayerReciever_id FOREIGN KEY(action_reciever_id)
192REFERENCES  Players(player_id);