· 4 years ago · Apr 05, 2021, 08:10 AM
1DROP DATABASE IF EXISTS [kramsePSA]
2GO
3
4CREATE DATABASE [kramsePSA]
5GO
6
7USE [kramsePSA]
8GO
9/****** Object: Table [dbo].[Consignor] Script Date: 3/17/2021 3:38:03 PM ******/
10SET ANSI_NULLS ON
11GO
12SET QUOTED_IDENTIFIER ON
13GO
14CREATE TABLE [dbo].[Consignor](
15 [Id] [int] IDENTITY(1,1) NOT NULL,
16 [Consignor] [nvarchar](255) NULL,
17 [City] [nvarchar](255) NULL,
18 [Country] [nvarchar](255) NULL,
19 [Discount] [int] NULL,
20 CONSTRAINT [PK_Consignor] PRIMARY KEY CLUSTERED
21(
22 [Id] ASC
23)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]
24) ON [PRIMARY]
25GO
26/****** Object: Table [dbo].[Container] Script Date: 3/17/2021 3:38:03 PM ******/
27SET ANSI_NULLS ON
28GO
29SET QUOTED_IDENTIFIER ON
30GO
31CREATE TABLE [dbo].[Container](
32 [Id] [int] IDENTITY(1,1) NOT NULL,
33 [Type] [nvarchar](50) NULL,
34 [RefrigerationFlag] [nvarchar](50) NULL,
35 [PowerFlag] [nvarchar](50) NULL,
36 [Length] [float] NULL,
37 [Cubes] [int] NULL,
38 [EuroPricePerKm] [float] NULL,
39 CONSTRAINT [PK_Container] PRIMARY KEY CLUSTERED
40(
41 [Id] ASC
42)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]
43) ON [PRIMARY]
44GO
45/****** Object: Table [dbo].[Item] Script Date: 3/17/2021 3:38:03 PM ******/
46SET ANSI_NULLS ON
47GO
48SET QUOTED_IDENTIFIER ON
49GO
50CREATE TABLE [dbo].[Item](
51 [Id] [int] IDENTITY(1,1) NOT NULL,
52 [Description] [nvarchar](50) NULL,
53 [Category] [nvarchar](50) NULL,
54 [MFGR] [nvarchar](50) NULL,
55 [StorageType] [nvarchar](50) NULL,
56 [HazardFlag] [nvarchar](50) NULL,
57 CONSTRAINT [PK_Item] PRIMARY KEY CLUSTERED
58(
59 [Id] ASC
60)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]
61) ON [PRIMARY]
62GO
63/****** Object: Table [dbo].[Port] Script Date: 3/17/2021 3:38:03 PM ******/
64SET ANSI_NULLS ON
65GO
66SET QUOTED_IDENTIFIER ON
67GO
68CREATE TABLE [dbo].[Port](
69 [P_PortOrder] [int] NOT NULL,
70 [VPS_PortId] [int] IDENTITY(1,1) NOT NULL,
71 [P_PortName] [nvarchar](50) NULL,
72 [P_Country] [nvarchar](50) NULL,
73 [P_DistanceFromOslo] [int] NULL,
74 [P_DistanceFromPiraeus] [int] NULL,
75 CONSTRAINT [PK_Port] PRIMARY KEY CLUSTERED
76(
77 [VPS_PortId] ASC
78)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]
79) ON [PRIMARY]
80GO
81/****** Object: Table [dbo].[Ship] Script Date: 3/17/2021 3:38:03 PM ******/
82SET ANSI_NULLS ON
83GO
84SET QUOTED_IDENTIFIER ON
85GO
86CREATE TABLE [dbo].[Ship](
87 [VS_Shipid] [int] IDENTITY(1,1) NOT NULL,
88 [Sh_Shipname] [nvarchar](50) NULL,
89 [Sh_MaxNumberContain] [int] NULL,
90 [Sh_SpeedInKnots] [int] NULL,
91 [Sh_SpeedInKm_H] [int] NULL,
92 [Sh_Country] [nvarchar](50) NULL,
93 [Sh_Yearcost] [money] NULL,
94 CONSTRAINT [PK_Ship] PRIMARY KEY CLUSTERED
95(
96 [VS_Shipid] ASC
97)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]
98) ON [PRIMARY]
99GO
100/****** Object: Table [dbo].[Shipment] Script Date: 3/17/2021 3:38:03 PM ******/
101SET ANSI_NULLS ON
102GO
103SET QUOTED_IDENTIFIER ON
104GO
105CREATE TABLE [dbo].[Shipment](
106 [ShipmentId] [int] IDENTITY(1,1) NOT NULL,
107 [VoyageId] [int] NULL,
108 [ConsignorId] [int] NULL,
109 [PortIdFrom] [int] NULL,
110 [PortIdTo] [int] NULL,
111 [Distance] [int] NULL,
112 [NumberContainers] [int] NULL,
113 CONSTRAINT [PK_Shipment] PRIMARY KEY CLUSTERED
114(
115 [ShipmentId] ASC
116)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]
117) ON [PRIMARY]
118GO
119/****** Object: Table [dbo].[ShipmentDetail] Script Date: 3/17/2021 3:38:03 PM ******/
120SET ANSI_NULLS ON
121GO
122SET QUOTED_IDENTIFIER ON
123GO
124CREATE TABLE [dbo].[ShipmentDetail](
125 [ShipmentId] [int] NULL,
126 [Item] [int] NULL,
127 [ContainertypeId] [int] NULL,
128 [ContainerNR] [nvarchar](50) NULL,
129) ON [PRIMARY]
130GO
131/****** Object: Table [dbo].[Voyage] Script Date: 3/17/2021 3:38:03 PM ******/
132SET ANSI_NULLS ON
133GO
134SET QUOTED_IDENTIFIER ON
135GO
136CREATE TABLE [dbo].[Voyage](
137 [VV_VoyageId] [int] IDENTITY(1,1) NOT NULL,
138 [VS_ShipId] [int] NOT NULL,
139 [V_DateDepartVoyage] [datetime] NULL,
140 [VPS_PortIdStart] [int] NULL,
141 [V_PortIdEnd] [int] NULL,
142 CONSTRAINT [PK_Voyage] PRIMARY KEY CLUSTERED
143(
144 [VV_VoyageId] ASC
145)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]
146) ON [PRIMARY]
147GO
148/****** Object: Table [dbo].[VoyagePort] Script Date: 3/17/2021 3:38:03 PM ******/
149SET ANSI_NULLS ON
150GO
151SET QUOTED_IDENTIFIER ON
152GO
153CREATE TABLE [dbo].[VoyagePort](
154 [VV_VoyageId] [int] NOT NULL,
155 [VP_PortIdCurrent] [int] NOT NULL,
156 [Vp_PortIdNext] [int] NOT NULL,
157 [Vp_LegDateDepart] [datetime] NULL,
158 [Vp_LegDateArrival] [datetime] NULL,
159 [Vp_Traject_Distance] [int] NULL,
160 [Vp_PortOrder] [nvarchar](50) NULL
161) ON [PRIMARY]
162GO
163ALTER TABLE [dbo].[Shipment] WITH CHECK ADD CONSTRAINT [FK_Shipment_Consignor] FOREIGN KEY([ConsignorId])
164REFERENCES [dbo].[Consignor] ([Id])
165GO
166ALTER TABLE [dbo].[Shipment] CHECK CONSTRAINT [FK_Shipment_Consignor]
167GO
168ALTER TABLE [dbo].[Shipment] WITH CHECK ADD CONSTRAINT [FK_Shipment_PortFrom] FOREIGN KEY([PortIdFrom])
169REFERENCES [dbo].[Port] ([VPS_PortId])
170GO
171ALTER TABLE [dbo].[Shipment] CHECK CONSTRAINT [FK_Shipment_PortFrom]
172GO
173ALTER TABLE [dbo].[Shipment] WITH CHECK ADD CONSTRAINT [FK_Shipment_PortTo] FOREIGN KEY([PortIdTo])
174REFERENCES [dbo].[Port] ([VPS_PortId])
175GO
176ALTER TABLE [dbo].[Shipment] CHECK CONSTRAINT [FK_Shipment_PortTo]
177GO
178ALTER TABLE [dbo].[Shipment] WITH CHECK ADD CONSTRAINT [FK_Shipment_Voyage] FOREIGN KEY([VoyageId])
179REFERENCES [dbo].[Voyage] ([VV_VoyageId])
180GO
181ALTER TABLE [dbo].[Shipment] CHECK CONSTRAINT [FK_Shipment_Voyage]
182GO
183ALTER TABLE [dbo].[ShipmentDetail] WITH CHECK ADD CONSTRAINT [FK_ShipmentDetail_Container] FOREIGN KEY([ContainertypeId])
184REFERENCES [dbo].[Container] ([Id])
185GO
186ALTER TABLE [dbo].[ShipmentDetail] CHECK CONSTRAINT [FK_ShipmentDetail_Container]
187GO
188ALTER TABLE [dbo].[ShipmentDetail] WITH CHECK ADD CONSTRAINT [FK_ShipmentDetail_Item] FOREIGN KEY([Item])
189REFERENCES [dbo].[Item] ([Id])
190GO
191ALTER TABLE [dbo].[ShipmentDetail] CHECK CONSTRAINT [FK_ShipmentDetail_Item]
192GO
193ALTER TABLE [dbo].[ShipmentDetail] WITH CHECK ADD CONSTRAINT [FK_ShipmentDetail_Shipment] FOREIGN KEY([ShipmentId])
194REFERENCES [dbo].[Shipment] ([ShipmentId])
195GO
196ALTER TABLE [dbo].[ShipmentDetail] CHECK CONSTRAINT [FK_ShipmentDetail_Shipment]
197GO
198ALTER TABLE [dbo].[Voyage] WITH CHECK ADD CONSTRAINT [FK_Voyage_PortEnd] FOREIGN KEY([V_PortIdEnd])
199REFERENCES [dbo].[Port] ([VPS_PortId])
200GO
201ALTER TABLE [dbo].[Voyage] CHECK CONSTRAINT [FK_Voyage_PortEnd]
202GO
203ALTER TABLE [dbo].[Voyage] WITH CHECK ADD CONSTRAINT [FK_Voyage_PortStart] FOREIGN KEY([VPS_PortIdStart])
204REFERENCES [dbo].[Port] ([VPS_PortId])
205GO
206ALTER TABLE [dbo].[Voyage] CHECK CONSTRAINT [FK_Voyage_PortStart]
207GO
208ALTER TABLE [dbo].[Voyage] WITH CHECK ADD CONSTRAINT [FK_Voyage_Ship] FOREIGN KEY([VS_ShipId])
209REFERENCES [dbo].[Ship] ([VS_Shipid])
210GO
211ALTER TABLE [dbo].[Voyage] CHECK CONSTRAINT [FK_Voyage_Ship]
212GO
213ALTER TABLE [dbo].[VoyagePort] WITH CHECK ADD CONSTRAINT [FK_VoyagePort_PortCurrent] FOREIGN KEY([VP_PortIdCurrent])
214REFERENCES [dbo].[Port] ([VPS_PortId])
215GO
216ALTER TABLE [dbo].[VoyagePort] CHECK CONSTRAINT [FK_VoyagePort_PortCurrent]
217GO
218ALTER TABLE [dbo].[VoyagePort] WITH CHECK ADD CONSTRAINT [FK_VoyagePort_PortNext] FOREIGN KEY([Vp_PortIdNext])
219REFERENCES [dbo].[Port] ([VPS_PortId])
220GO
221ALTER TABLE [dbo].[VoyagePort] CHECK CONSTRAINT [FK_VoyagePort_PortNext]
222GO
223ALTER TABLE [dbo].[VoyagePort] WITH CHECK ADD CONSTRAINT [FK_VoyagePort_Voyage] FOREIGN KEY([VV_VoyageId])
224REFERENCES [dbo].[Voyage] ([VV_VoyageId])
225GO
226ALTER TABLE [dbo].[VoyagePort] CHECK CONSTRAINT [FK_VoyagePort_Voyage]
227GO
228