· 6 years ago · May 29, 2019, 08:10 PM
1-- Delete the tables in IDH_STAGE
2
3if exists (select * from dbo.sysobjects where id = object_id(N'[IDH_STAGE].[Date]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
4drop table [IDH_STAGE].[Date]
5GO
6
7if exists (select * from dbo.sysobjects where id = object_id(N'[IDH_STAGE].[Stadium]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
8drop table [IDH_STAGE].[Stadium]
9GO
10
11if exists (select * from dbo.sysobjects where id = object_id(N'[IDH_STAGE].[Country]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
12drop table [IDH_STAGE].[Country]
13GO
14
15if exists (select * from dbo.sysobjects where id = object_id(N'[IDH_STAGE].[Group]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
16drop table [IDH_STAGE].[Group]
17GO
18
19if exists (select * from dbo.sysobjects where id = object_id(N'[IDH_STAGE].[Match]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
20drop table [IDH_STAGE].[Match]
21GO
22
23-- Delete the tables in IDH_HUR
24if exists (select * from dbo.sysobjects where id = object_id(N'[IDH_HUR].[Date]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
25drop table [IDH_HUR].[Date]
26GO
27
28if exists (select * from dbo.sysobjects where id = object_id(N'[IDH_HUR].[Stadium]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
29drop table [IDH_HUR].[Stadium]
30GO
31
32if exists (select * from dbo.sysobjects where id = object_id(N'[IDH_HUR].[Country]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
33drop table [IDH_HUR].[Country]
34GO
35
36if exists (select * from dbo.sysobjects where id = object_id(N'[IDH_HUR].[Group]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
37drop table [IDH_HUR].[Group]
38GO
39
40if exists (select * from dbo.sysobjects where id = object_id(N'[IDH_HUR].[Match]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
41drop table [IDH_HUR].[Match]
42GO
43
44if exists (select * from dbo.sysobjects where id = object_id(N'[IDH_HUR].[Match]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
45drop table [IDH_HUR].[Match]
46GO
47
48
49DROP SCHEMA IDH_STAGE;
50DROP SCHEMA IDH_HUR;
51
52
53-- Schema creation
54
55CREATE SCHEMA IDH_STAGE;
56GO
57
58CREATE SCHEMA IDH_HUR;
59GO
60
61--Tabele ODS
62
63CREATE TABLE [IDH_STAGE].[Date] (
64 [IdData] int identity PRIMARY KEY,
65 [Date] datetime NOT NULL
66)
67GO
68
69CREATE TABLE [IDH_STAGE].[Stadium] (
70 [IdStadium] int identity PRIMARY KEY,
71 [Name] [varchar] (150) NOT NULL,
72 [City] [varchar] (150) NOT NULL
73)
74GO
75
76CREATE TABLE [IDH_STAGE].[Country](
77 [IdCountry] int identity PRIMARY KEY,
78 [Country] [varchar] (150) NOT NULL,
79 [Initials] [varchar] (3) NOT NULL
80)
81GO
82
83CREATE TABLE [IDH_STAGE].[Group] (
84 [IdGroup] int identity PRIMARY KEY,
85 [Stage] [varchar] (150) NOT NULL
86)
87GO
88
89CREATE TABLE [IDH_STAGE].[Match] (
90 [IdMatch] int identity PRIMARY KEY,
91 [AwayTeamGoals] int NOT NULL,
92 [HomeTeamGoals] int NOT NULL,
93 [Attendance] int NOT NULL,
94 [Draw] bit NOT NULL
95)
96GO
97
98-- Table hurtowniane
99
100CREATE TABLE [IDH_HUR].[Date] (
101 [IdData] int identity PRIMARY KEY,
102 [Date] datetime NOT NULL
103)
104GO
105
106CREATE TABLE [IDH_HUR].[Stadium] (
107 [IdStadium] int identity PRIMARY KEY,
108 [Name] [varchar] (150) NOT NULL,
109 [City] [varchar] (150) NOT NULL
110)
111GO
112
113CREATE TABLE [IDH_HUR].[Country](
114 [IdCountry] int identity PRIMARY KEY,
115 [Country] [varchar] (150) NOT NULL,
116 [Initials] [varchar] (3) NOT NULL
117)
118GO
119
120CREATE TABLE [IDH_HUR].[Group] (
121 [IdGroup] int identity PRIMARY KEY,
122 [Stage] [varchar] (150) NOT NULL
123)
124GO
125
126CREATE TABLE [IDH_HUR].[Match] (
127 [IdMatch] int identity PRIMARY KEY,
128 [AwayTeamGoals] int NOT NULL,
129 [HomeTeamGoals] int NOT NULL,
130 [Attendance] int NOT NULL,
131 [Draw] bit NOT NULL,
132 [Data] int REFERENCES [IDH_HUR].[Date],
133 [Stadium] int REFERENCES [IDH_HUR].[Stadium],
134 --[City] int REFERENCES [IDH_HUR].[Stadium],
135 [HomeTeam] int REFERENCES [IDH_HUR].[Country],
136 [AwayTeam] int REFERENCES [IDH_HUR].[Country],
137 [WinningTeam]int REFERENCES [IDH_HUR].[Country],
138 [Stage] int REFERENCES [IDH_HUR].[Group]
139)
140GO
141
142-- Drop FKeys
143ALTER TABLE [IDH_HUR].[Match] DROP CONSTRAINT [Date]
144ALTER TABLE [IDH_HUR].[Match] DROP CONSTRAINT [Stadium]
145--ALTER TABLE [IDH_HUR].[Match] DROP CONSTRAINT [City]
146ALTER TABLE [IDH_HUR].[Match] DROP CONSTRAINT [HomeTeam]
147ALTER TABLE [IDH_HUR].[Match] DROP CONSTRAINT [AwayTeam]
148ALTER TABLE [IDH_HUR].[Match] DROP CONSTRAINT [WinningTeam]
149ALTER TABLE [IDH_HUR].[Match] DROP CONSTRAINT [Stage]
150
151TRUNCATE TABLE [IDH_HUR].[Match]
152
153GO
154
155--Recreate constraints
156ALTER TABLE [IDH_HUR].[Match] ADD CONSTRAINT [Date] FOREIGN KEY([IdData])
157REFERENCES [IDH_HUR].[Date] ([IdData])
158
159ALTER TABLE [IDH_HUR].[Match] ADD CONSTRAINT [Stadium] FOREIGN KEY([IdStadium])
160REFERENCES [IDH_HUR].[Stadium] ([IdStadium])
161
162ALTER TABLE [IDH_HUR].[Match] ADD CONSTRAINT [City] FOREIGN KEY([IdStadium])
163REFERENCES [IDH_HUR].[Stadium] ([IdStadium])
164
165ALTER TABLE [IDH_HUR].[Match] ADD CONSTRAINT [HomeTeam] FOREIGN KEY([IdCountry])
166REFERENCES [IDH_HUR].[Country] ([IdCountry])
167
168ALTER TABLE [IDH_HUR].[Match] ADD CONSTRAINT [AwayTeam] FOREIGN KEY([IdCountry])
169REFERENCES [IDH_HUR].[Country] ([IdCountry])
170
171ALTER TABLE [IDH_HUR].[Match] ADD CONSTRAINT [WinningTeam] FOREIGN KEY([IdCountry])
172REFERENCES [IDH_HUR].[Country] ([IdCountry])
173
174ALTER TABLE [IDH_HUR].[Match] ADD CONSTRAINT [Stage] FOREIGN KEY([IdGroup])
175REFERENCES [IDH_HUR].[Group] ([IdGroup])
176
177GO