4ep5L3E8

1USE [master]
2 
3IF NOT EXISTS (SELECT name FROM sys.DATABASES WHERE name = N'HockeyTeamAccountingDWincremently')
4    CREATE DATABASE [HockeyTeamAccountingDWincremently]
5ELSE
6    DROP DATABASE [HockeyTeamAccountingDWincremently]
7    CREATE DATABASE [HockeyTeamAccountingDWincremently]
8GO
9USE [HockeyTeamAccountingDWincremently]
10 
11CREATE TABLE DimTeam
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 
20CONSTRAINT UniqueTeams UNIQUE (team_country,team_city,team_name),
21PRIMARY KEY(team_id)
22);/*+*/
23 
24 
25CREATE TABLE DimStadion
26(
27stadion_id INT NOT NULL,
28 
29stadion_name VARCHAR(50) NOT NULL,
30stadion_country VARCHAR(50) NOT NULL,
31stadion_city VARCHAR(50) NOT NULL,
32 
33PRIMARY KEY(stadion_id)
34);/*+*/
35 
36/*+*/
37 
38 
39CREATE TABLE DimTournament
40(
41tournament_id INT  NOT NULL,
42tournament_name VARCHAR(50) NOT NULL,
43tournament_status  VARCHAR(50) NOT NULL,/*international local*/
44 
45CONSTRAINT UniqueTournament UNIQUE (tournament_name,tournament_status),
46PRIMARY KEY(tournament_id)
47);/*+*/
48 
49 
50CREATE TABLE FactGame
51(
52game_id INT NOT NULL,
53tournament_id INT NOT NULL,
54stadion_id INT ,
55home_team_id INT NOT NULL,
56guest_team_id INT NOT NULL,
57 
58home_team_trainer_id INT ,
59guest_team_trainer_id INT,
60 
61home_team_score INT DEFAULT 0,
62guest_team_score INT DEFAULT 0 ,
63overtime_home_team_score INT DEFAULT 0,
64overtime_guest_team_score INT DEFAULT 0,
65bullits_home_team_score INT DEFAULT 0,
66bullits_guest_team_score INT DEFAULT 0,
67 
68start_time_id int DEFAULT NULL,
69 
70CONSTRAINT UniqueGame UNIQUE (tournament_id,stadion_id,home_team_id,guest_team_id),
71PRIMARY KEY(game_id)
72);/*+*/
73 
74 Create Table DimTime
75 (
76    DimTimeId int  not null,
77    year_ int ,
78    month_ int,
79    day_ int,
80    hour_ int,
81    minutes_ int,
82    PRIMARY KEY(DimTimeId)
83 );
84 
85 Create table DimTrainer
86 (
87    Trainer_id int  not null,
88    trainer_name Varchar(50),
89    trainer_surname Varchar(50),
90    trainer_country Varchar(50),
91    PRIMARY KEY(Trainer_id)
92 );
93 
94ALTER TABLE FactGame
95WITH CHECK ADD CONSTRAINT FK_Tournament_id FOREIGN KEY(tournament_id)
96REFERENCES DimTournament(tournament_id);
97 
98ALTER TABLE FactGame
99WITH CHECK ADD CONSTRAINT FK_Stadion_id FOREIGN KEY(stadion_id)
100REFERENCES DimStadion(stadion_id);
101 
102 ALTER TABLE FactGame
103WITH CHECK ADD CONSTRAINT FK_Home_Team_ID FOREIGN KEY(home_team_id)
104REFERENCES DimTeam(team_id);
105 
106ALTER TABLE FactGame
107WITH CHECK ADD CONSTRAINT FK_Guest_Team_ID FOREIGN KEY(guest_team_id)
108REFERENCES DimTeam(team_id);
109 
110 ALTER TABLE FactGame
111WITH CHECK ADD CONSTRAINT FK_Home_Team_Trainer_ID FOREIGN KEY(home_team_trainer_id)
112REFERENCES DimTrainer(Trainer_id);
113 
114ALTER TABLE FactGame
115WITH CHECK ADD CONSTRAINT FK_Guest_Team_Trainer_ID FOREIGN KEY(guest_team_trainer_id)
116REFERENCES DimTrainer(Trainer_id);
117 
118ALTER TABLE FactGame
119WITH CHECK ADD CONSTRAINT FK_Game_Time_ID FOREIGN KEY(start_time_id)
120REFERENCES DimTime(DimTimeId);