CZeVaenE

1use [master]
2
3IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'BasketballStatistic')
4	CREATE DATABASE [BasketballStatistic]
5else
6	DROP DATABASE [BasketballStatistic]
7	CREATE DATABASE [BasketballStatistic]
8go
9use [BasketballStatistic]
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_date DateTime Not null,
18team_main_stadion int not null,
19PRIMARY KEY(team_id)
20);/*+*/
21
22CREATE TABLE Team_statistic
23(
24Team_statistic_id INT IDENTITY(1,1) NOT NULL,
25Team_id int not Null,
26
27wins int not null,
28loses int not null,
29loses_on_draw int not null,
30wins_on_draw int not null,
31
32PRIMARY KEY(Team_statistic_id)
33);/*+*/
34/*CREATE TABLE Team_Stadion
35(
36	Team_Stadion_id INT IDENTITY(1,1) NOT NULL,
37	team_id INT not null,
38	Stadion_id INT NOT NULL,
39	Start_stadion_use DateTime Not null,
40	Finish_stadion_use DateTime Not null,
41	PRIMARY KEY(Team_Stadion_id)
42);*/
43
44
45CREATE TABLE Stadion
46(
47Stadion_id INT IDENTITY(1,1) NOT NULL,
48
49Stadion_name VARCHAR(50) NOT NULL,
50Stadion_country VARCHAR(50) NOT NULL,
51Stadion_city VARCHAR(50) NOT NULL,
52Stadion_fondation_date DateTime Not null,
53Stadion_places_count int not null,
54Stadion_location_latitude float NOT NULL,
55Stadion_location_longitude fload NOT NULL,
56
57PRIMARY KEY(Stadion_id)
58);/*+*/
59
60CREATE TABLE Players
61(
62player_id INT IDENTITY(1,1) NOT NULL,
63current_team_id int not Null,
64player_name VARCHAR(50) NOT NULL,
65player_surname VARCHAR(50) NOT NULL,
66player_country  VARCHAR(50) NOT NULL,
67player_born_date DateTime Not null,
68PRIMARY KEY(player_id)
69);/*+*/
70
71
72CREATE TABLE Player_Contracts
73(
74Contract_id INT IDENTITY(1,1) NOT NULL,
75team_id int not Null,
76player_id int not Null,
77player_amplua VARCHAR(50) NOT NULL,/*goalkeeper defender*/
78player_number_on_contract int not Null,
79contract_start_time DateTime not null,
80contract_deyure_end_time DateTime not null,
81contract_defacto_end_time DateTime,
82PRIMARY KEY(Contract_id)
83);
84/*+*/
85CREATE TABLE Player_statistic
86(
87statistic_id INT IDENTITY(1,1) NOT NULL,
88Contract_id int not Null,
89
90total_time_played time not null,
91goals_from_game_bullits_count int not null, 
92goals_from_game_count int not Null,
93key_passes_for_goals_count int not Null,
94saves_count int not Null,
95two_min_fare int not Null,
96five_min_fare int not_Null,
97
98wins int not null,
99loses int not null,
100loses_on_draw int not null,
101wins_on_draw int not null,
102PRIMARY KEY(statistic_id)
103);/*+-*/
104
105CREATE TABLE Coach
106(
107coach_id INT IDENTITY(1,1) NOT NULL,
108current_team_id int not Null,
109coach_name VARCHAR(50) NOT NULL,
110coach_surname VARCHAR(50) NOT NULL,
111coach_country  VARCHAR(50) NOT NULL,
112coach_city VARCHAR(50) NOT NULL,
113coach_born_date DateTime Not null,
114PRIMARY KEY(coach_id)
115);/*+*/
116
117CREATE TABLE Coach_Contracts
118(
119Contract_id INT IDENTITY(1,1) NOT NULL,
120team_id int not Null,
121coach_id int not Null,
122contract_start_time DateTime not null,
123contract_deyure_end_time DateTime not null,
124contract_defacto_end_time DateTime,
125PRIMARY KEY(Contract_id)
126);
127/*+*/
128CREATE TABLE Coach_statistic
129(
130statistic_id INT IDENTITY(1,1) NOT NULL,
131Contract_id int not Null,
132
133wins int not null,
134loses int not null,
135loses_on_draw int not null,
136wins_on_draw int not null,
137
138PRIMARY KEY(statistic_id)
139);/*+*/
140
141
142CREATE TABLE referies
143(
144 referie_id INT IDENTITY(1,1) NOT NULL,
145 referie_name VARCHAR(50) NOT NULL,
146 referie_surname VARCHAR(50) NOT NULL,
147 referie_born_date DateTime Not null,
148
149
150PRIMARY KEY(referie_id)
151);/*+*/
152
153/*common table for Tour and team*/
154
155CREATE TABLE Tournament
156(
157Tournament_id INT IDENTITY(1,1) NOT NULL,
158Tournament_name VARCHAR(50) NOT NULL,
159Tournament_status  VARCHAR(50) NOT NULL,/*international local*/
160PRIMARY KEY(Tournament_id)
161);/*+*/
162
163CREATE TABLE Game
164(
165Game_id INT IDENTITY(1,1) NOT NULL,
166Tournament_id int NOT NULL,
167Stadion_id int not Null,
168referie_id int not null,
169home_team int not null,
170guest_team int not null,
171home_team_score int not null,
172guest_team_score int not null,
173start_time date not null,
174
175PRIMARY KEY(Game_id)
176);/*+*/
177
178CREATE TABLE GameApplication
179(
180GameApplication_id INT IDENTITY(1,1) NOT NULL,
181Game_id int NOT NULL,
182Player_id int not null,
183Team_id int not null,
184PRIMARY KEY(GameApplication_id)
185);/*+*/
186
187
188CREATE TABLE GameLog
189(
190GameLog_id INT IDENTITY(1,1) NOT NULL,
191Game_id int not null,
192
193action_time Time not null,
194main_game_period bit not null
195game_period int not null, 
196player_id int not null,
197player_action int not null,/*action of player triger for statistic and score update*/
198PRIMARY KEY(GameLog_id)
199);
200
201CREATE TABLE PlayerAction
202(
203	Player_Action_id INT IDENTITY(1,1) NOT NULL,
204	action_commited VARCHAR(50) NOT NULL, /*goal, assist, save, boolit, noboolit*/
205	PRIMARY KEY(Player_Action_id)
206);