· 6 years ago · Mar 23, 2019, 12:04 PM
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,
14
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_city VARCHAR(50) NOT NULL,
51Stadion_fondation_date DateTime Not null,
52Stadion_places_count int not null,
53Stadion_location_latitude float NOT NULL,
54Stadion_location_longitude fload NOT NULL,
55
56PRIMARY KEY(Stadion_id)
57);
58
59CREATE TABLE Players
60(
61player_id INT IDENTITY(1,1) NOT NULL,
62current_team_id int not Null,
63player_name VARCHAR(50) NOT NULL,
64player_surname VARCHAR(50) NOT NULL,
65player_country VARCHAR(50) NOT NULL,
66player_born_date DateTime Not null,
67PRIMARY KEY(player_id)
68);
69
70
71CREATE TABLE Player_Contracts
72(
73Contract_id INT IDENTITY(1,1) NOT NULL,
74team_id int not Null,
75player_id int not Null,
76player_number int not Null,
77contract_start_time DateTime not null,
78contract_end_time DateTime not null,
79PRIMARY KEY(Contract_id)
80);
81/**/
82CREATE TABLE Player_statistic
83(
84statistic_id INT IDENTITY(1,1) NOT NULL,
85Contract_id int not Null,
86one_point int not Null,
87two_point int not Null,
88three_point int not Null,
89fols_commited int not null,
90block_shots int not null,
91wins int not null,
92loses int not null,
93loses_on_draw int not null,
94wins_on_draw int not null,
95contract_end_time DateTime not null,
96PRIMARY KEY(statistic_id)
97);
98
99CREATE TABLE Coach
100(
101coach_id INT IDENTITY(1,1) NOT NULL,
102current_team_id int not Null,
103coach_name VARCHAR(50) NOT NULL,
104coach_surname VARCHAR(50) NOT NULL,
105coach_country VARCHAR(50) NOT NULL,
106coach_born_date DateTime Not null,
107PRIMARY KEY(coach_id)
108);
109
110CREATE TABLE Coach_Contracts
111(
112Contract_id INT IDENTITY(1,1) NOT NULL,
113team_id int not Null,
114coach_id int not Null,
115contract_start_time DateTime not null,
116contract_end_time DateTime not null,
117PRIMARY KEY(Contract_id)
118);
119/**/
120CREATE TABLE Coach_statistic
121(
122statistic_id INT IDENTITY(1,1) NOT NULL,
123Contract_id int not Null,
124
125wins int not null,
126loses int not null,
127draws int not null,
128loses_on_draw int not null,
129wins_on_draw int not null,
130
131PRIMARY KEY(statistic_id)
132);
133
134
135CREATE TABLE referies
136(
137 referie_id INT IDENTITY(1,1) NOT NULL,
138 referie_name VARCHAR(50) NOT NULL,
139 referie_surname VARCHAR(50) NOT NULL,
140 referie_born_date DateTime Not null,
141 referies_status VARCHAR(50) NOT NULL,
142PRIMARY KEY(coach_id)
143
144PRIMARY KEY(statistic_id)
145);
146
147/*common table for Tour and team*/
148
149CREATE TABLE Tournament
150(
151Tournament_id INT IDENTITY(1,1) NOT NULL,
152Tournament_name VARCHAR(50) NOT NULL,
153Tournament_name VARCHAR(50) NOT NULL,
154Tournament_status VARCHAR(50) NOT NULL,/*international local*/
155PRIMARY KEY(Tournament_id)
156);
157
158CREATE TABLE Game
159(
160Game_id INT IDENTITY(1,1) NOT NULL,
161Tournament_id int NOT NULL,
162Stadion_id int not Null,
163home_team int not null,
164guest_team int not null,
165home_team_score int not null,
166guest_team_score int not null,
167start_time date not null,
168PRIMARY KEY(Game_id)
169);
170
171CREATE TABLE GameApplication
172(
173GameApplication_id INT IDENTITY(1,1) NOT NULL,
174Game_id int NOT NULL,
175Player_id int not null,
176Team_id int not null,
177PRIMARY KEY(GameApplication_id)
178);
179
180
181CREATE TABLE GameLog
182(
183GameLog_id INT IDENTITY(1,1) NOT NULL,
184Game_id int not null,
185/*guest_team int not null,
186home_team_score int not null,
187guest_team_score int not null,*/
188action_time Time not null,
189game_period int not null,
190player_id int not null,
191player_action int not null,/*action of player triger for statistic and score update*/
192PRIMARY KEY(GameLog_id)
193);