XbFp0Sar

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,
18PRIMARY KEY(team_id)
19);
20
21
22CREATE 
23
24CREATE TABLE Stadion
25(
26Stadion_id INT IDENTITY(1,1) NOT NULL,
27
28Stadion_name VARCHAR(50) NOT NULL,
29Stadion_city VARCHAR(50) NOT NULL,
30Stadion_fondation_date DateTime Not null,
31
32Stadion_location_latitude float NOT NULL,
33Stadion_location_longitude fload NOT NULL,
34
35PRIMARY KEY(team_id)
36);
37
38CREATE TABLE Players
39(
40player_id INT IDENTITY(1,1) NOT NULL,
41current_team_id int not Null,
42player_name VARCHAR(50) NOT NULL,
43player_surname VARCHAR(50) NOT NULL,
44player_country  VARCHAR(50) NOT NULL,
45player_born_date DateTime Not null,
46PRIMARY KEY(player_id)
47);
48
49CREATE TABLE Contracts
50(
51Contract_id INT IDENTITY(1,1) NOT NULL,
52team_id int not Null,
53player_id int not Null,
54player_number int not Null,
55contract_start_time DateTime not null,
56contract_end_time DateTime not null,
57PRIMARY KEY(Contract_id)
58);
59/**/
60CREATE TABLE Player_statistic
61(
62statistic_id INT IDENTITY(1,1) NOT NULL,
63Contract_id int not Null,
64one_point int not Null,
65two_point int not Null,
66three_point int not Null,
67fols_commited int not null,
68block_shots int not null,
69wins int not null,
70loses int not null,
71draws int not null,
72loses_on_draw int not null,
73wins_on_draw int not null,
74contract_end_time DateTime not null,
75PRIMARY KEY(statistic_id)
76);
77/*common table for Tour and team*/
78
79CREATE TABLE Tournament
80(
81Tournament_id INT IDENTITY(1,1) NOT NULL,
82Tournament_name VARCHAR(50) NOT NULL,
83Tournament_name  VARCHAR(50) NOT NULL,
84Tournament_status  VARCHAR(50) NOT NULL,/*international local*/
85PRIMARY KEY(Tournament_id)
86);
87
88CREATE TABLE Game
89(
90Game_id INT IDENTITY(1,1) NOT NULL,
91Tournament_id int NOT NULL,
92home_team int not null,
93guest_team int not null,
94home_team_score int not null,
95guest_team_score int not null,
96start_time date not null,
97PRIMARY KEY(Game_id)
98);
99
100CREATE TABLE GameApplication
101(
102GameApplication_id INT IDENTITY(1,1) NOT NULL,
103Game_id int NOT NULL,
104Player_id int not null,
105Team_id int not null,
106PRIMARY KEY(GameApplication_id)
107);
108
109
110CREATE TABLE GameLog
111(
112GameLog_id INT IDENTITY(1,1) NOT NULL,
113Game_id int not null,
114/*guest_team int not null,
115home_team_score int not null,
116guest_team_score int not null,*/
117action_time Time not null,
118game_period int not null, 
119player_id int not null,
120player_action int not null,/*action of player triger for statistic and score update*/
121PRIMARY KEY(GameLog_id)
122);