4UZMja7a

1USE [master]
2 
3IF NOT EXISTS (SELECT name FROM sys.DATABASES WHERE name = N'HockeyTeamAccountingStage')
4    CREATE DATABASE [HockeyTeamAccountingStage]
5ELSE
6    DROP DATABASE [HockeyTeamAccountingStage]
7    CREATE DATABASE [HockeyTeamAccountingStage]
8GO
9USE [HockeyTeamAccountingStage]
10 
11CREATE TABLE StgTeam
12(
13team_id INT  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,
18 
19);/*+*/
20 
21 
22CREATE TABLE StgStadion
23(
24stadion_id INT  NOT NULL,
25 
26stadion_name VARCHAR(50) NOT NULL,
27stadion_country VARCHAR(50) NOT NULL,
28stadion_city VARCHAR(50) NOT NULL,
29);/*+*/
30 
31/*+*/
32 
33 
34CREATE TABLE StgTournament
35(
36tournament_id int NOT NULL,
37tournament_name VARCHAR(50) NOT NULL,
38tournament_status  VARCHAR(50) NOT NULL,/*international local*/
39 
40);/*+*/
41 
42 
43CREATE TABLE StgGame
44(
45game_id INT,
46tournament_id INT,
47stadion_id INT ,
48home_team_id INT,
49guest_team_id INT,
50 
51home_team_trainer_id INT,
52guest_team_trainer_id INT,
53 
54home_team_score INT DEFAULT 0,
55guest_team_score INT DEFAULT 0 ,
56overtime_home_team_score INT DEFAULT 0,
57overtime_guest_team_score INT DEFAULT 0,
58bullits_home_team_score INT DEFAULT 0,
59bullits_guest_team_score INT DEFAULT 0,
60 
61start_time DateTime DEFAULT NULL,
62);
63 
64 Create table StgTrainer
65 (
66    Stgtrainer_id int,
67    trainer_name Varchar(50),
68    trainer_surname Varchar(50),
69    trainer_country Varchar(50),
70 
71 );