· 4 years ago · Apr 05, 2021, 08:12 AM
1DROP DATABASE IF EXISTS kramseODS
2GO
3
4CREATE DATABASE kramseODS
5GO
6
7USE [kramseODS]
8GO
9/****** Object: Table [dbo].[Dim_Consignor] Script Date: 3/26/2021 11:55:50 AM ******/
10SET ANSI_NULLS ON
11GO
12SET QUOTED_IDENTIFIER ON
13GO
14CREATE TABLE [dbo].[Dim_Consignor](
15 [Id] [int] IDENTITY(1,1) NOT NULL,
16 [ConsignorId] [int] NOT NULL,
17 [Consignor] [nvarchar](100) NULL,
18 [City] [nvarchar](100) NULL,
19 [Country] [nvarchar](100) NULL,
20 [CountryCode] [char](2) NULL,
21 [Discount] [int] NULL,
22 [EffectiveDate] [date] NOT NULL,
23 [ExpirationDate] [date] NULL,
24 CONSTRAINT [PK_Dim_Consignor] PRIMARY KEY CLUSTERED
25(
26 [Id] ASC
27)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
28) ON [PRIMARY]
29GO
30/****** Object: Table [dbo].[Dim_Container] Script Date: 3/26/2021 11:55:50 AM ******/
31SET ANSI_NULLS ON
32GO
33SET QUOTED_IDENTIFIER ON
34GO
35CREATE TABLE [dbo].[Dim_Container](
36 [Id] [int] IDENTITY(1,1) NOT NULL,
37 [ContainerId] [int] NOT NULL,
38 [Type] [nvarchar](100) NULL,
39 [RefrigerationFlag] [nvarchar](50) NULL,
40 [PowerFlag] [nvarchar](50) NULL,
41 [Length] [int] NULL,
42 [Cubes] [int] NULL,
43 [EurPricePerKm] [money] NULL,
44 [EffectiveDate] [date] NOT NULL,
45 [ExpirationDate] [date] NULL,
46 CONSTRAINT [PK_Dim_Container] PRIMARY KEY CLUSTERED
47(
48 [Id] ASC
49)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
50) ON [PRIMARY]
51GO
52/****** Object: Table [dbo].[Dim_Item] Script Date: 3/26/2021 11:55:50 AM ******/
53SET ANSI_NULLS ON
54GO
55SET QUOTED_IDENTIFIER ON
56GO
57CREATE TABLE [dbo].[Dim_Item](
58 [Id] [int] IDENTITY(1,1) NOT NULL,
59 [ItemId] [int] NOT NULL,
60 [Description] [nvarchar](100) NULL,
61 [Category] [nvarchar](100) NULL,
62 [Manufacturer] [nvarchar](100) NULL,
63 [StorageType] [nvarchar](50) NULL,
64 [HazardFlag] [nvarchar](50) NULL,
65 [EffectiveDate] [date] NOT NULL,
66 [ExpirationDate] [date] NULL,
67 CONSTRAINT [PK_Dim_Item] PRIMARY KEY CLUSTERED
68(
69 [Id] ASC
70)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
71) ON [PRIMARY]
72GO
73/****** Object: Table [dbo].[Dim_Port] Script Date: 3/26/2021 11:55:50 AM ******/
74SET ANSI_NULLS ON
75GO
76SET QUOTED_IDENTIFIER ON
77GO
78CREATE TABLE [dbo].[Dim_Port](
79 [PortId] [int] PRIMARY KEY NOT NULL,
80 [PortOrder] [int] NULL,
81 [PortName] [nvarchar](100) NULL,
82 [Country] [nvarchar](100) NULL,
83 [DistanceFromOslo] [int] NULL,
84 [DistanceFromPiraeus] [int] NULL
85)
86GO
87/****** Object: Table [dbo].[Dim_Ship] Script Date: 3/26/2021 11:55:50 AM ******/
88SET ANSI_NULLS ON
89GO
90SET QUOTED_IDENTIFIER ON
91GO
92CREATE TABLE [dbo].[Dim_Ship](
93 [Id] [int] IDENTITY(1,1) NOT NULL,
94 [ShipId] [int] NOT NULL,
95 [Name] [nvarchar](100) NULL,
96 [MaxNrContainers] [int] NULL,
97 [SpeedInKnots] [int] NULL,
98 [SpeedInKmh] [int] NULL,
99 [Country] [nvarchar](100) NULL,
100 [YearCost] [money] NULL,
101 [Co2Efficiency] [float] NULL,
102 [EffectiveDate] [date] NOT NULL,
103 [ExpirationDate] [date] NULL,
104 CONSTRAINT [PK_Dim_Ship] PRIMARY KEY CLUSTERED
105(
106 [Id] ASC
107)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
108) ON [PRIMARY]
109GO
110/****** Object: Table [dbo].[Fact_Shipment] Script Date: 3/26/2021 11:55:50 AM ******/
111SET ANSI_NULLS ON
112GO
113SET QUOTED_IDENTIFIER ON
114GO
115CREATE TABLE [dbo].[Fact_Shipment](
116 [Id] [int] NOT NULL,
117 [VoyageId] [int] NOT NULL,
118 [ShipmentId] [int] NOT NULL,
119 [ConsignorId] [int] NOT NULL,
120 [ItemId] [int] NOT NULL,
121 [ShipId] [int] NOT NULL,
122 [ContainerTypeId] [int] NOT NULL,
123 [PortIdStart] [int] NOT NULL,
124 [PortIdEnd] [int] NOT NULL,
125 [PortIdCurrent] [int] NOT NULL,
126 [PortIdNext] [int] NOT NULL,
127 [ContainerFrom] [int] NULL,
128 [ContainerTo] [int] NULL,
129 [TEU] [int] NULL,
130 [Distance] [int] NULL,
131 [DepartmentDate] [date] NULL,
132 [ArrivalDate] [date] NULL,
133 [VoyageDepartmentDate] [date] NULL,
134 CONSTRAINT [PK_Fact_Shipment] PRIMARY KEY CLUSTERED
135(
136 [Id] ASC
137)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
138) ON [PRIMARY]
139GO
140/****** Object: Table [dbo].[Fact_TimeInPort] Script Date: 3/26/2021 11:55:50 AM ******/
141SET ANSI_NULLS ON
142GO
143SET QUOTED_IDENTIFIER ON
144GO
145CREATE TABLE [dbo].[Fact_TimeInPort](
146 [ShipId] [int] NOT NULL,
147 [PortId] [int] NOT NULL,
148 [Arrival] [date] NOT NULL,
149 [Department] [date] NOT NULL
150) ON [PRIMARY]
151GO
152ALTER TABLE [dbo].[Dim_Consignor] ADD CONSTRAINT [DF_Dim_Consignor_EffectiveDate] DEFAULT (getdate()) FOR [EffectiveDate]
153GO
154ALTER TABLE [dbo].[Dim_Item] ADD CONSTRAINT [DF_Dim_Item_EffectiveDate] DEFAULT (getdate()) FOR [EffectiveDate]
155GO
156ALTER TABLE [dbo].[Fact_Shipment] WITH CHECK ADD CONSTRAINT [FK_Fact_Shipment_Dim_Consignor] FOREIGN KEY([ConsignorId])
157REFERENCES [dbo].[Dim_Consignor] ([Id])
158GO
159ALTER TABLE [dbo].[Fact_Shipment] CHECK CONSTRAINT [FK_Fact_Shipment_Dim_Consignor]
160GO
161ALTER TABLE [dbo].[Fact_Shipment] WITH CHECK ADD CONSTRAINT [FK_Fact_Shipment_Dim_Container] FOREIGN KEY([ContainerTypeId])
162REFERENCES [dbo].[Dim_Container] ([Id])
163GO
164ALTER TABLE [dbo].[Fact_Shipment] CHECK CONSTRAINT [FK_Fact_Shipment_Dim_Container]
165GO
166ALTER TABLE [dbo].[Fact_Shipment] WITH CHECK ADD CONSTRAINT [FK_Fact_Shipment_Dim_Item] FOREIGN KEY([ItemId])
167REFERENCES [dbo].[Dim_Item] ([Id])
168GO
169ALTER TABLE [dbo].[Fact_Shipment] CHECK CONSTRAINT [FK_Fact_Shipment_Dim_Item]
170GO
171ALTER TABLE [dbo].[Fact_Shipment] WITH CHECK ADD CONSTRAINT [FK_Fact_Shipment_Dim_Port1] FOREIGN KEY([PortIdStart])
172REFERENCES [dbo].[Dim_Port] ([PortId])
173GO
174ALTER TABLE [dbo].[Fact_Shipment] CHECK CONSTRAINT [FK_Fact_Shipment_Dim_Port1]
175GO
176ALTER TABLE [dbo].[Fact_Shipment] WITH CHECK ADD CONSTRAINT [FK_Fact_Shipment_Dim_Port2] FOREIGN KEY([PortIdEnd])
177REFERENCES [dbo].[Dim_Port] ([PortId])
178GO
179ALTER TABLE [dbo].[Fact_Shipment] CHECK CONSTRAINT [FK_Fact_Shipment_Dim_Port2]
180GO
181ALTER TABLE [dbo].[Fact_Shipment] WITH CHECK ADD CONSTRAINT [FK_Fact_Shipment_Dim_Port3] FOREIGN KEY([PortIdCurrent])
182REFERENCES [dbo].[Dim_Port] ([PortId])
183GO
184ALTER TABLE [dbo].[Fact_Shipment] CHECK CONSTRAINT [FK_Fact_Shipment_Dim_Port3]
185GO
186ALTER TABLE [dbo].[Fact_Shipment] WITH CHECK ADD CONSTRAINT [FK_Fact_Shipment_Dim_Port4] FOREIGN KEY([PortIdNext])
187REFERENCES [dbo].[Dim_Port] ([PortId])
188GO
189ALTER TABLE [dbo].[Fact_Shipment] CHECK CONSTRAINT [FK_Fact_Shipment_Dim_Port4]
190GO
191ALTER TABLE [dbo].[Fact_Shipment] WITH CHECK ADD CONSTRAINT [FK_Fact_Shipment_Dim_Ship] FOREIGN KEY([ShipId])
192REFERENCES [dbo].[Dim_Ship] ([Id])
193GO
194ALTER TABLE [dbo].[Fact_Shipment] CHECK CONSTRAINT [FK_Fact_Shipment_Dim_Ship]
195GO
196ALTER TABLE [dbo].[Fact_TimeInPort] WITH CHECK ADD CONSTRAINT [FK_Fact_TimeInPort_Dim_Port] FOREIGN KEY([PortId])
197REFERENCES [dbo].[Dim_Port] ([PortId])
198GO
199ALTER TABLE [dbo].[Fact_TimeInPort] CHECK CONSTRAINT [FK_Fact_TimeInPort_Dim_Port]
200GO
201ALTER TABLE [dbo].[Fact_TimeInPort] WITH CHECK ADD CONSTRAINT [FK_Fact_TimeInPort_Dim_Ship] FOREIGN KEY([ShipId])
202REFERENCES [dbo].[Dim_Ship] ([Id])
203GO
204ALTER TABLE [dbo].[Fact_TimeInPort] CHECK CONSTRAINT [FK_Fact_TimeInPort_Dim_Ship]
205GO