tGkjvCzt

1USE [master]
2 
3IF NOT EXISTS (SELECT name FROM sys.DATABASES WHERE name = N'HockeyTeamAccounting')
4    CREATE DATABASE [HockeyTeamAccounting]
5ELSE
6    DROP DATABASE [HockeyTeamAccounting]
7    CREATE DATABASE [HockeyTeamAccounting]
8GO
9USE [HockeyTeamAccounting]
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,
28trainer_country VARCHAR(50) NOT NULL,
29birth_date DateTime not null,
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 
63
64 
65 
66CREATE TABLE Team_Tournament
67(
68team_tournament_id INT IDENTITY(1,1) NOT NULL,
69tournament_id INT NOT NULL,
70team_id INT NOT NULL,
71PRIMARY KEY(team_tournament_id )
72);
73/*common table for Tour and team*/
74 
75CREATE TABLE Tournament
76(
77tournament_id INT IDENTITY(1,1) NOT NULL,
78tournament_name VARCHAR(50) NOT NULL,
79tournament_status  VARCHAR(50) NOT NULL,/*international local*/
80tournament_participans_origin  VARCHAR(50) NOT NULL,
81tournament_start Datetime not null,
82tournament_finish Datetime not null,
83/*CONSTRAINT UniqueTournament UNIQUE (player_name,player_surname,player_country,player_born_date/*,team_id*/),*/
84PRIMARY KEY(tournament_id)
85);/*+*/
86 
87CREATE TABLE Game
88(
89game_id INT IDENTITY(1,1) NOT NULL,
90tournament_id INT NOT NULL,
91 
92/*referie_id INT NOT NULL,*/
93home_team INT NOT NULL,
94guest_team INT NOT NULL,
95 
96stadion_id INT ,
97home_team_trainer_id INT,
98guest_team_trainer_id INT,
99 
100home_team_score INT DEFAULT 0,
101guest_team_score INT DEFAULT 0 ,
102overtime_home_team_score INT DEFAULT 0,
103overtime_guest_team_score INT DEFAULT 0,
104bullits_home_team_score INT DEFAULT 0,
105bullits_guest_team_score INT DEFAULT 0,
106start_time DATETIME DEFAULT NULL,
107 start_time_id int ,
108PRIMARY KEY(game_id)
109);/*+*/
110 
111 CREATE TABLE StructuredTime
112 (
113    DateiledTime_id int  IDENTITY(1,1) NOT NULL,
114	year_ int,
115	month_ int,
116	day_ int,
117	hour_ int,
118	minute_ int, 
119	PRIMARY KEY(DateiledTime_id)
120 );
121
122
123CREATE TABLE PlayerGameApplication
124(
125gameApplication_id INT IDENTITY(1,1) NOT NULL,
126game_id INT NOT NULL,
127player_id INT NOT NULL,
128team_id int not null,
129ganeral_game_time_goal_scored int Default 0,
130overtime_goal_scored int Default 0,
131after_total_time_bullit_scored int Default 0,
132PRIMARY KEY(gameApplication_id),
133CONSTRAINT UC UNIQUE (gameApplication_id,game_id,player_id/*,team_id*/)
134);/*+*/
135 
136 
137
138 
139ALTER TABLE Players
140WITH CHECK ADD CONSTRAINT FK_Current_Team_ID FOREIGN KEY(current_team_id)
141REFERENCES Team(team_id);
142 
143
144 
145ALTER TABLE Team_Tournament
146WITH CHECK ADD CONSTRAINT FK_Tournament_id FOREIGN KEY(tournament_id)
147REFERENCES Tournament(tournament_id);
148 
149ALTER TABLE Team_Tournament
150WITH CHECK ADD CONSTRAINT FK_Team3_id FOREIGN KEY(team_id)
151REFERENCES Team(team_id);
152 
153ALTER TABLE Game
154WITH CHECK ADD CONSTRAINT FK_Tournament2_id FOREIGN KEY(tournament_id)
155REFERENCES Tournament(tournament_id);
156 
157 ALTER TABLE Team
158WITH CHECK ADD CONSTRAINT FK_team_main_stadion_id FOREIGN KEY(team_main_stadion)
159REFERENCES Stadion(stadion_id);
160 
161ALTER TABLE Game
162WITH CHECK ADD CONSTRAINT FK_Stadion_id FOREIGN KEY(stadion_id)
163REFERENCES Stadion(stadion_id);
164 
165 
166ALTER TABLE PlayerGameApplication
167WITH CHECK ADD CONSTRAINT FK_Game1_id FOREIGN KEY(game_id)
168REFERENCES Game(game_id);
169 
170 ALTER TABLE PlayerGameApplication
171WITH CHECK ADD CONSTRAINT FK_Game15_id FOREIGN KEY(player_id)
172REFERENCES Players(player_id);
173 
174
175 
176 
177 ALTER TABLE Game
178WITH CHECK ADD CONSTRAINT FK_Team_ID_199 FOREIGN KEY(home_team)
179REFERENCES Team(team_id);
180 
181 ALTER TABLE Game
182WITH CHECK ADD CONSTRAINT FK_Team_ID_1999 FOREIGN KEY(guest_team)
183REFERENCES Team(team_id);
184 
185  ALTER TABLE Game
186WITH CHECK ADD CONSTRAINT FK_Home_Team_Trainer_ID FOREIGN KEY(home_team_trainer_id)
187REFERENCES Trainer(trainer_id);
188
189  ALTER TABLE Game
190WITH CHECK ADD CONSTRAINT FK_Guest_Team_Trainer_ID FOREIGN KEY(guest_team_trainer_id)
191REFERENCES Trainer(trainer_id);
192
193 ALTER TABLE Game
194WITH CHECK ADD CONSTRAINT FK_Team_ID_199999 FOREIGN KEY(guest_team)
195REFERENCES Team(team_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_startTime_id FOREIGN KEY( start_time_id)
203REFERENCES StructuredTime(
204    DateiledTime_id);