P3H754pW

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_id int DEFAULT NULL,
62);
63 
64 Create Table StgStructuredTime
65 (
66	TimeId int not null,
67    year_ int ,
68    month_ int,
69    day_ int,
70    hour_ int,
71    minutes_ int,
72 );
73
74 Create table StgTrainer
75 (
76	Trainer_id int,
77	trainer_name Varchar(50),
78	trainer_surname Varchar(50),
79	trainer_country Varchar(50),
80 );