vv8JxY65

1USE [master]
2 
3IF NOT EXISTS (SELECT name FROM sys.DATABASES WHERE name = N'HockeyTeamAccountingDBUA')
4    CREATE DATABASE [HockeyTeamAccountingDBUA]
5ELSE
6    DROP DATABASE [HockeyTeamAccountingDBUA]
7    CREATE DATABASE [HockeyTeamAccountingDBUA]
8GO
9USE [HockeyTeamAccountingDBUA]
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,
19CONSTRAINT UniqueTeams UNIQUE (team_name),
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 
32CONSTRAINT UniqueTrainer UNIQUE (trainer_name,trainer_surname,trainer_country,birth_date/*,team_id*/),
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,
44CONSTRAINT UniqueStadion_ UNIQUE (stadion_name),
45 
46PRIMARY KEY(stadion_id)
47);/*+*/
48 
49CREATE TABLE Players
50(
51player_id INT IDENTITY(1,1) NOT NULL,
52current_team_id INT,
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,
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,
71CONSTRAINT UniqueStadion UNIQUE (tournament_id,team_id/*,team_id*/),
72PRIMARY KEY(team_tournament_id )
73);
74
75 
76CREATE TABLE Tournament
77(
78tournament_id INT IDENTITY(1,1) NOT NULL,
79tournament_name VARCHAR(50) NOT NULL,
80
81tournament_start Datetime not null,
82tournament_finish Datetime not null,
83
84PRIMARY KEY(tournament_id)
85);
86 
87CREATE TABLE Game
88( 
89game_id INT IDENTITY(1,1) NOT NULL,
90tournament_id INT NOT NULL,
91 
92home_team_id INT NOT NULL,
93guest_team_id INT NOT NULL,
94 
95stadion_id INT ,
96home_team_trainer_id INT,
97guest_team_trainer_id INT,
98 
99home_team_score INT DEFAULT 0,
100guest_team_score INT DEFAULT 0 ,
101overtime_home_team_score INT DEFAULT 0,
102overtime_guest_team_score INT DEFAULT 0,
103bullits_home_team_score INT DEFAULT 0,
104bullits_guest_team_score INT DEFAULT 0,
105start_time DATETIME DEFAULT NULL,
106 
107PRIMARY KEY(game_id)
108);
109 
110 
111CREATE TABLE PlayerGameApplication
112(
113gameApplication_id INT IDENTITY(1,1) NOT NULL,
114game_id INT NOT NULL,
115player_id INT NOT NULL,
116team_id int not null,
117ganeral_game_time_goal_scored int Default 0,
118overtime_goal_scored int Default 0,
119after_total_time_bullit_scored int Default 0,
120PRIMARY KEY(gameApplication_id),
121CONSTRAINT UC UNIQUE (gameApplication_id,game_id,player_id/*,team_id*/)
122);
123 
124 
125 
126 
127ALTER TABLE Players
128WITH CHECK ADD CONSTRAINT FK_Current_Team_ID FOREIGN KEY(current_team_id)
129REFERENCES Team(team_id);
130 
131 
132 
133ALTER TABLE Team_Tournament
134WITH CHECK ADD CONSTRAINT FK_Tournament_id FOREIGN KEY(tournament_id)
135REFERENCES Tournament(tournament_id);
136 
137ALTER TABLE Team_Tournament
138WITH CHECK ADD CONSTRAINT FK_Team3_id FOREIGN KEY(team_id)
139REFERENCES Team(team_id);
140 
141ALTER TABLE Game
142WITH CHECK ADD CONSTRAINT FK_Tournament2_id FOREIGN KEY(tournament_id)
143REFERENCES Tournament(tournament_id);
144 
145 ALTER TABLE Team
146WITH CHECK ADD CONSTRAINT FK_team_main_stadion_id FOREIGN KEY(team_main_stadion)
147REFERENCES Stadion(stadion_id);
148 
149ALTER TABLE Game
150WITH CHECK ADD CONSTRAINT FK_Stadion_id FOREIGN KEY(stadion_id)
151REFERENCES Stadion(stadion_id);
152 
153 
154ALTER TABLE PlayerGameApplication
155WITH CHECK ADD CONSTRAINT FK_Game1_id FOREIGN KEY(game_id)
156REFERENCES Game(game_id);
157 
158 ALTER TABLE PlayerGameApplication
159WITH CHECK ADD CONSTRAINT FK_Game15_id FOREIGN KEY(player_id)
160REFERENCES Players(player_id);
161 
162 
163 
164 
165 ALTER TABLE Game
166WITH CHECK ADD CONSTRAINT FK_Team_ID_199 FOREIGN KEY(home_team_id)
167REFERENCES Team(team_id);
168 
169 ALTER TABLE Game
170WITH CHECK ADD CONSTRAINT FK_Team_ID_1999 FOREIGN KEY(guest_team_id)
171REFERENCES Team(team_id);
172 
173  ALTER TABLE Game
174WITH CHECK ADD CONSTRAINT FK_Home_Team_Trainer_ID FOREIGN KEY(home_team_trainer_id)
175REFERENCES Trainer(trainer_id);
176 
177  ALTER TABLE Game
178WITH CHECK ADD CONSTRAINT FK_Guest_Team_Trainer_ID FOREIGN KEY(guest_team_trainer_id)
179REFERENCES Trainer(trainer_id);
180 
181 
182 
183ALTER TABLE Trainer
184WITH CHECK ADD CONSTRAINT FK_CurrentTeam_id FOREIGN KEY(current_team_id)
185REFERENCES Team(team_id);