· 8 years ago · Nov 21, 2017, 10:22 AM
1USE [Zawada]
2GO
3/****** Object: User [administrator] Script Date: 11/21/2017 11:18:58 ******/
4IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'administrator')
5CREATE USER [administrator] FOR LOGIN [administrator] WITH DEFAULT_SCHEMA=[dbo]
6GO
7/****** Object: User [bank] Script Date: 11/21/2017 11:18:58 ******/
8IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'bank')
9CREATE USER [bank] FOR LOGIN [bank] WITH DEFAULT_SCHEMA=[dbo]
10GO
11/****** Object: User [customer] Script Date: 11/21/2017 11:18:58 ******/
12IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'customer')
13CREATE USER [customer] FOR LOGIN [customer] WITH DEFAULT_SCHEMA=[dbo]
14GO
15/****** Object: User [klient] Script Date: 11/21/2017 11:18:58 ******/
16IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'klient')
17CREATE USER [klient] FOR LOGIN [klient] WITH DEFAULT_SCHEMA=[dbo]
18GO
19/****** Object: User [sales] Script Date: 11/21/2017 11:18:58 ******/
20IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'sales')
21CREATE USER [sales] FOR LOGIN [sales] WITH DEFAULT_SCHEMA=[dbo]
22GO
23/****** Object: User [zawada] Script Date: 11/21/2017 11:18:58 ******/
24IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'zawada')
25CREATE USER [zawada] FOR LOGIN [zawada] WITH DEFAULT_SCHEMA=[dbo]
26GO
27/****** Object: Schema [emp] Script Date: 11/21/2017 11:18:58 ******/
28IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'emp')
29EXEC sys.sp_executesql N'CREATE SCHEMA [emp] AUTHORIZATION [dbo]'
30GO
31/****** Object: Schema [ManageCar] Script Date: 11/21/2017 11:18:58 ******/
32IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'ManageCar')
33EXEC sys.sp_executesql N'CREATE SCHEMA [ManageCar] AUTHORIZATION [administrator]'
34GO
35/****** Object: Table [dbo].[Logs] Script Date: 11/21/2017 11:18:57 ******/
36SET ANSI_NULLS ON
37GO
38SET QUOTED_IDENTIFIER ON
39GO
40SET ANSI_PADDING ON
41GO
42IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Logs]') AND type in (N'U'))
43BEGIN
44CREATE TABLE [dbo].[Logs](
45 [customer_id] [int] NULL,
46 [mobile_phone] [int] NULL,
47 [userID] [varchar](50) NULL
48) ON [PRIMARY]
49END
50GO
51SET ANSI_PADDING OFF
52GO
53/****** Object: Table [dbo].[Car_Loans] Script Date: 11/21/2017 11:18:57 ******/
54SET ANSI_NULLS ON
55GO
56SET QUOTED_IDENTIFIER ON
57GO
58IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Car_Loans]') AND type in (N'U'))
59BEGIN
60CREATE TABLE [dbo].[Car_Loans](
61 [loan_id] [int] NOT NULL,
62 [customer_payment_id] [int] NOT NULL,
63 [loan_amount] [decimal](11, 2) NULL,
64 CONSTRAINT [PK_Car_Loans_1] PRIMARY KEY CLUSTERED
65(
66 [loan_id] ASC
67)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
68) ON [PRIMARY]
69END
70GO
71IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Car_Loans]') AND name = N'loan_amount_index')
72CREATE NONCLUSTERED INDEX [loan_amount_index] ON [dbo].[Car_Loans]
73(
74 [loan_amount] ASC
75)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 20) ON [PRIMARY]
76GO
77INSERT [dbo].[Car_Loans] ([loan_id], [customer_payment_id], [loan_amount]) VALUES (1, 1, CAST(10.12 AS Decimal(11, 2)))
78/****** Object: Table [ManageCar].[Car_Categories] Script Date: 11/21/2017 11:18:57 ******/
79SET ANSI_NULLS ON
80GO
81SET QUOTED_IDENTIFIER ON
82GO
83SET ANSI_PADDING ON
84GO
85IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ManageCar].[Car_Categories]') AND type in (N'U'))
86BEGIN
87CREATE TABLE [ManageCar].[Car_Categories](
88 [car_categories_id] [int] NOT NULL,
89 [car_description] [varchar](255) NULL,
90 CONSTRAINT [PK_Car_Categories] PRIMARY KEY CLUSTERED
91(
92 [car_categories_id] ASC
93)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
94) ON [PRIMARY]
95END
96GO
97SET ANSI_PADDING OFF
98GO
99INSERT [ManageCar].[Car_Categories] ([car_categories_id], [car_description]) VALUES (1, N'sedan')
100INSERT [ManageCar].[Car_Categories] ([car_categories_id], [car_description]) VALUES (2, N'kombi')
101INSERT [ManageCar].[Car_Categories] ([car_categories_id], [car_description]) VALUES (3, N'suv')
102/****** Object: Table [dbo].[Customers] Script Date: 11/21/2017 11:18:57 ******/
103SET ANSI_NULLS ON
104GO
105SET QUOTED_IDENTIFIER ON
106GO
107SET ANSI_PADDING ON
108GO
109IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Customers]') AND type in (N'U'))
110BEGIN
111CREATE TABLE [dbo].[Customers](
112 [customer_id] [int] NOT NULL,
113 [mobile_phone] [int] NULL,
114 [email_address] [varchar](255) NULL,
115 [first_name] [varchar](50) NULL,
116 [last_name] [varchar](50) NULL,
117 CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED
118(
119 [customer_id] ASC
120)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
121) ON [PRIMARY]
122END
123GO
124SET ANSI_PADDING OFF
125GO
126IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Customers]') AND name = N'customer_id_first_last_name_index')
127CREATE UNIQUE NONCLUSTERED INDEX [customer_id_first_last_name_index] ON [dbo].[Customers]
128(
129 [customer_id] ASC,
130 [first_name] ASC,
131 [last_name] ASC
132)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 20) ON [PRIMARY]
133GO
134INSERT [dbo].[Customers] ([customer_id], [mobile_phone], [email_address], [first_name], [last_name]) VALUES (1, 123123123, N'test_1@test.com', N'Piotr', N'Zawada')
135INSERT [dbo].[Customers] ([customer_id], [mobile_phone], [email_address], [first_name], [last_name]) VALUES (2, 123456789, N'test_2@test.com', N'Pawel', N'Kordos')
136INSERT [dbo].[Customers] ([customer_id], [mobile_phone], [email_address], [first_name], [last_name]) VALUES (3, 987654321, N'test_3@test.com', N'Dominik', N'Jakubiak')
137INSERT [dbo].[Customers] ([customer_id], [mobile_phone], [email_address], [first_name], [last_name]) VALUES (222, 123321456, N'test+222@test.com', N'Janusz', N'Biznesu')
138/****** Object: Table [ManageCar].[Car_Models] Script Date: 11/21/2017 11:18:57 ******/
139SET ANSI_NULLS ON
140GO
141SET QUOTED_IDENTIFIER ON
142GO
143SET ANSI_PADDING ON
144GO
145IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ManageCar].[Car_Models]') AND type in (N'U'))
146BEGIN
147CREATE TABLE [ManageCar].[Car_Models](
148 [modal_id] [int] NOT NULL,
149 [modal_name] [varchar](50) NOT NULL,
150 [car_categories_ID] [int] NOT NULL,
151 CONSTRAINT [PK_Car_Models] PRIMARY KEY CLUSTERED
152(
153 [modal_id] ASC
154)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
155) ON [PRIMARY]
156END
157GO
158SET ANSI_PADDING OFF
159GO
160IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[ManageCar].[Car_Models]') AND name = N'modal_name_index')
161CREATE NONCLUSTERED INDEX [modal_name_index] ON [ManageCar].[Car_Models]
162(
163 [modal_name] ASC
164)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 20) ON [PRIMARY]
165GO
166INSERT [ManageCar].[Car_Models] ([modal_id], [modal_name], [car_categories_ID]) VALUES (103, N'a3', 1)
167INSERT [ManageCar].[Car_Models] ([modal_id], [modal_name], [car_categories_ID]) VALUES (105, N'Q3', 3)
168INSERT [ManageCar].[Car_Models] ([modal_id], [modal_name], [car_categories_ID]) VALUES (204, N'a4', 2)
169INSERT [ManageCar].[Car_Models] ([modal_id], [modal_name], [car_categories_ID]) VALUES (606, N'Q7', 1)
170/****** Object: Trigger [LoanIDTrigger] Script Date: 11/21/2017 11:18:58 ******/
171SET ANSI_NULLS ON
172GO
173SET QUOTED_IDENTIFIER ON
174GO
175IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[LoanIDTrigger]'))
176EXEC dbo.sp_executesql @statement = N'CREATE TRIGGER [dbo].[LoanIDTrigger] ON [dbo].[Car_Loans]
177AFTER INSERT, UPDATE
178AS
179IF EXISTS (
180 SELECT *
181 FROM dbo.Car_Loans loan
182 JOIN inserted AS ins ON loan.loan_id = ins.loan_id
183 WHERE loan.loan_id NOT LIKE ''[^0-9]''
184)
185
186BEGIN
187 RAISERROR (''Pozyczka o podanym id istnieje'', 10, 1);
188 ROLLBACK TRANSACTION;
189 RETURN
190END;
191'
192GO
193/****** Object: Trigger [DeleteCustomerLog] Script Date: 11/21/2017 11:18:58 ******/
194SET ANSI_NULLS ON
195GO
196SET QUOTED_IDENTIFIER ON
197GO
198IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[DeleteCustomerLog]'))
199EXEC dbo.sp_executesql @statement = N'
200CREATE TRIGGER [dbo].[DeleteCustomerLog] ON [dbo].[Customers]
201FOR DELETE
202AS
203BEGIN
204 INSERT INTO Logs (customer_id, mobile_phone,userID)
205 SELECT del.customer_id AS ''customer_id'',
206 del.mobile_phone AS ''mobile_phone'',
207 SYSTEM_USER AS ''userID''
208 FROM Deleted del
209
210END
211'
212GO
213/****** Object: Table [dbo].[Cars] Script Date: 11/21/2017 11:18:57 ******/
214SET ANSI_NULLS ON
215GO
216SET QUOTED_IDENTIFIER ON
217GO
218IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Cars]') AND type in (N'U'))
219BEGIN
220CREATE TABLE [dbo].[Cars](
221 [car_id] [int] NOT NULL,
222 [sold_id] [int] NOT NULL,
223 [modal_id] [int] NOT NULL,
224 CONSTRAINT [PK_Cars] PRIMARY KEY CLUSTERED
225(
226 [car_id] ASC
227)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
228) ON [PRIMARY]
229END
230GO
231INSERT [dbo].[Cars] ([car_id], [sold_id], [modal_id]) VALUES (1, 1, 103)
232INSERT [dbo].[Cars] ([car_id], [sold_id], [modal_id]) VALUES (2, 1, 103)
233INSERT [dbo].[Cars] ([car_id], [sold_id], [modal_id]) VALUES (3, 0, 105)
234INSERT [dbo].[Cars] ([car_id], [sold_id], [modal_id]) VALUES (4, 1, 204)
235INSERT [dbo].[Cars] ([car_id], [sold_id], [modal_id]) VALUES (9901, 0, 606)
236/****** Object: StoredProcedure [dbo].[NewCLient] Script Date: 11/21/2017 11:18:58 ******/
237SET ANSI_NULLS ON
238GO
239SET QUOTED_IDENTIFIER ON
240GO
241IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[NewCLient]') AND type in (N'P', N'PC'))
242BEGIN
243EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[NewCLient]
244@ClientID int,
245@MobilePhone int,
246@EmailAddress varchar(255),
247@FirstName varchar(50),
248@LastName varchar(50)
249AS
250BEGIN
251INSERT INTO dbo.Customers(customer_id, mobile_phone, email_address, first_name, last_name)
252VALUES
253(@ClientID, @MobilePhone, @EmailAddress, @FirstName, @LastName)
254END'
255END
256GO
257/****** Object: Table [dbo].[Addresses] Script Date: 11/21/2017 11:18:57 ******/
258SET ANSI_NULLS ON
259GO
260SET QUOTED_IDENTIFIER ON
261GO
262SET ANSI_PADDING ON
263GO
264IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Addresses]') AND type in (N'U'))
265BEGIN
266CREATE TABLE [dbo].[Addresses](
267 [address_id] [int] NOT NULL,
268 [customer_id] [int] NOT NULL,
269 [address_data] [varchar](255) NULL,
270 CONSTRAINT [PK_Addresses] PRIMARY KEY CLUSTERED
271(
272 [address_id] ASC
273)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
274) ON [PRIMARY]
275END
276GO
277SET ANSI_PADDING OFF
278GO
279INSERT [dbo].[Addresses] ([address_id], [customer_id], [address_data]) VALUES (1, 1, N'Warszawa 22-222 Testowa 1')
280INSERT [dbo].[Addresses] ([address_id], [customer_id], [address_data]) VALUES (2, 2, N'Warszawa 22-222 Testowa 2')
281INSERT [dbo].[Addresses] ([address_id], [customer_id], [address_data]) VALUES (3, 3, N'Warszawa 22-222 Testowa 3')
282/****** Object: Table [ManageCar].[Car_Feature] Script Date: 11/21/2017 11:18:57 ******/
283SET ANSI_NULLS ON
284GO
285SET QUOTED_IDENTIFIER ON
286GO
287SET ANSI_PADDING ON
288GO
289IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ManageCar].[Car_Feature]') AND type in (N'U'))
290BEGIN
291CREATE TABLE [ManageCar].[Car_Feature](
292 [car_feature_id] [int] NOT NULL,
293 [modal_id] [int] NOT NULL,
294 [car_feature_desc] [varchar](50) NOT NULL,
295 CONSTRAINT [PK_Car_Feature] PRIMARY KEY CLUSTERED
296(
297 [car_feature_id] ASC
298)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
299) ON [PRIMARY]
300END
301GO
302SET ANSI_PADDING OFF
303GO
304IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[ManageCar].[Car_Feature]') AND name = N'feature_id_desc_index')
305CREATE NONCLUSTERED INDEX [feature_id_desc_index] ON [ManageCar].[Car_Feature]
306(
307 [car_feature_id] ASC,
308 [car_feature_desc] ASC
309)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 20) ON [PRIMARY]
310GO
311INSERT [ManageCar].[Car_Feature] ([car_feature_id], [modal_id], [car_feature_desc]) VALUES (1, 103, N'nawigacja')
312INSERT [ManageCar].[Car_Feature] ([car_feature_id], [modal_id], [car_feature_desc]) VALUES (2, 204, N'relingi')
313INSERT [ManageCar].[Car_Feature] ([car_feature_id], [modal_id], [car_feature_desc]) VALUES (3, 105, N'podwyzszone zawieszenie')
314/****** Object: Trigger [CustomerPhoneLength] Script Date: 11/21/2017 11:18:58 ******/
315SET ANSI_NULLS ON
316GO
317SET QUOTED_IDENTIFIER ON
318GO
319IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[CustomerPhoneLength]'))
320EXEC dbo.sp_executesql @statement = N'
321CREATE TRIGGER [dbo].[CustomerPhoneLength] ON [dbo].[Customers]
322AFTER INSERT, UPDATE
323AS
324IF EXISTS (
325 SELECT *
326 FROM dbo.Customers customer
327 JOIN inserted AS ins ON customer.mobile_phone = ins.mobile_phone
328 WHERE len(customer.mobile_phone) < 9
329)
330
331BEGIN
332 RAISERROR (''Numer musi posiadać minimum 9 cyfr'', 10, 1)
333 ROLLBACK TRANSACTION;
334 RETURN
335END;
336'
337GO
338/****** Object: Table [dbo].[Customer_Payment] Script Date: 11/21/2017 11:18:57 ******/
339SET ANSI_NULLS ON
340GO
341SET QUOTED_IDENTIFIER ON
342GO
343IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Customer_Payment]') AND type in (N'U'))
344BEGIN
345CREATE TABLE [dbo].[Customer_Payment](
346 [customer_payment_id] [int] NOT NULL,
347 [customer_id] [int] NOT NULL,
348 [car_id] [int] NULL,
349 CONSTRAINT [PK_Customer_Payment] PRIMARY KEY CLUSTERED
350(
351 [customer_payment_id] ASC
352)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
353) ON [PRIMARY]
354END
355GO
356IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Customer_Payment]') AND name = N'IX_Customer_Payment')
357CREATE NONCLUSTERED INDEX [IX_Customer_Payment] ON [dbo].[Customer_Payment]
358(
359 [customer_payment_id] ASC
360)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 20) ON [PRIMARY]
361GO
362INSERT [dbo].[Customer_Payment] ([customer_payment_id], [customer_id], [car_id]) VALUES (1, 1, 1)
363INSERT [dbo].[Customer_Payment] ([customer_payment_id], [customer_id], [car_id]) VALUES (2, 2, 2)
364INSERT [dbo].[Customer_Payment] ([customer_payment_id], [customer_id], [car_id]) VALUES (3, 3, 3)
365/****** Object: Table [dbo].[Cars_conept] Script Date: 11/21/2017 11:18:57 ******/
366SET ANSI_NULLS ON
367GO
368SET QUOTED_IDENTIFIER ON
369GO
370SET ANSI_PADDING ON
371GO
372IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Cars_conept]') AND type in (N'U'))
373BEGIN
374CREATE TABLE [dbo].[Cars_conept](
375 [car_id] [int] NOT NULL,
376 [prototype_name] [varchar](50) NOT NULL,
377 [prototype_desc] [text] NULL,
378 CONSTRAINT [PK_Cars_conept] PRIMARY KEY CLUSTERED
379(
380 [car_id] ASC,
381 [prototype_name] ASC
382)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
383) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
384END
385GO
386SET ANSI_PADDING OFF
387GO
388INSERT [dbo].[Cars_conept] ([car_id], [prototype_name], [prototype_desc]) VALUES (9901, N'Q7', N'bigger than q5')
389/****** Object: View [dbo].[PlatnoscKlientow] Script Date: 11/21/2017 11:18:57 ******/
390SET ANSI_NULLS ON
391GO
392SET QUOTED_IDENTIFIER ON
393GO
394IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[PlatnoscKlientow]'))
395EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[PlatnoscKlientow] AS
396SELECT Car_Loans.loan_amount
397FROM Customer_Payment ,Car_Loans
398WHERE Customer_Payment.customer_payment_id= Car_Loans.customer_payment_id;
399'
400GO
401/****** Object: ForeignKey [FK_Addresses_Customers] Script Date: 11/21/2017 11:18:57 ******/
402IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Addresses_Customers]') AND parent_object_id = OBJECT_ID(N'[dbo].[Addresses]'))
403ALTER TABLE [dbo].[Addresses] WITH CHECK ADD CONSTRAINT [FK_Addresses_Customers] FOREIGN KEY([customer_id])
404REFERENCES [dbo].[Customers] ([customer_id])
405GO
406IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Addresses_Customers]') AND parent_object_id = OBJECT_ID(N'[dbo].[Addresses]'))
407ALTER TABLE [dbo].[Addresses] CHECK CONSTRAINT [FK_Addresses_Customers]
408GO
409/****** Object: ForeignKey [FK_Cars_Car_Models] Script Date: 11/21/2017 11:18:57 ******/
410IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Cars_Car_Models]') AND parent_object_id = OBJECT_ID(N'[dbo].[Cars]'))
411ALTER TABLE [dbo].[Cars] WITH CHECK ADD CONSTRAINT [FK_Cars_Car_Models] FOREIGN KEY([modal_id])
412REFERENCES [ManageCar].[Car_Models] ([modal_id])
413GO
414IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Cars_Car_Models]') AND parent_object_id = OBJECT_ID(N'[dbo].[Cars]'))
415ALTER TABLE [dbo].[Cars] CHECK CONSTRAINT [FK_Cars_Car_Models]
416GO
417/****** Object: ForeignKey [FK_Cars_conept_Cars] Script Date: 11/21/2017 11:18:57 ******/
418IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Cars_conept_Cars]') AND parent_object_id = OBJECT_ID(N'[dbo].[Cars_conept]'))
419ALTER TABLE [dbo].[Cars_conept] WITH CHECK ADD CONSTRAINT [FK_Cars_conept_Cars] FOREIGN KEY([car_id])
420REFERENCES [dbo].[Cars] ([car_id])
421GO
422IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Cars_conept_Cars]') AND parent_object_id = OBJECT_ID(N'[dbo].[Cars_conept]'))
423ALTER TABLE [dbo].[Cars_conept] CHECK CONSTRAINT [FK_Cars_conept_Cars]
424GO
425/****** Object: ForeignKey [FK_Customer_Payment_Cars] Script Date: 11/21/2017 11:18:57 ******/
426IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Customer_Payment_Cars]') AND parent_object_id = OBJECT_ID(N'[dbo].[Customer_Payment]'))
427ALTER TABLE [dbo].[Customer_Payment] WITH CHECK ADD CONSTRAINT [FK_Customer_Payment_Cars] FOREIGN KEY([car_id])
428REFERENCES [dbo].[Cars] ([car_id])
429GO
430IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Customer_Payment_Cars]') AND parent_object_id = OBJECT_ID(N'[dbo].[Customer_Payment]'))
431ALTER TABLE [dbo].[Customer_Payment] CHECK CONSTRAINT [FK_Customer_Payment_Cars]
432GO
433/****** Object: ForeignKey [FK_Customer_Payment_Customers] Script Date: 11/21/2017 11:18:57 ******/
434IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Customer_Payment_Customers]') AND parent_object_id = OBJECT_ID(N'[dbo].[Customer_Payment]'))
435ALTER TABLE [dbo].[Customer_Payment] WITH CHECK ADD CONSTRAINT [FK_Customer_Payment_Customers] FOREIGN KEY([customer_id])
436REFERENCES [dbo].[Customers] ([customer_id])
437GO
438IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Customer_Payment_Customers]') AND parent_object_id = OBJECT_ID(N'[dbo].[Customer_Payment]'))
439ALTER TABLE [dbo].[Customer_Payment] CHECK CONSTRAINT [FK_Customer_Payment_Customers]
440GO
441/****** Object: ForeignKey [FK_Car_Feature_Car_Feature] Script Date: 11/21/2017 11:18:57 ******/
442IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[ManageCar].[FK_Car_Feature_Car_Feature]') AND parent_object_id = OBJECT_ID(N'[ManageCar].[Car_Feature]'))
443ALTER TABLE [ManageCar].[Car_Feature] WITH CHECK ADD CONSTRAINT [FK_Car_Feature_Car_Feature] FOREIGN KEY([modal_id])
444REFERENCES [ManageCar].[Car_Models] ([modal_id])
445GO
446IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[ManageCar].[FK_Car_Feature_Car_Feature]') AND parent_object_id = OBJECT_ID(N'[ManageCar].[Car_Feature]'))
447ALTER TABLE [ManageCar].[Car_Feature] CHECK CONSTRAINT [FK_Car_Feature_Car_Feature]
448GO
449/****** Object: ForeignKey [FK_Car_Models_Car_Categories] Script Date: 11/21/2017 11:18:57 ******/
450IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[ManageCar].[FK_Car_Models_Car_Categories]') AND parent_object_id = OBJECT_ID(N'[ManageCar].[Car_Models]'))
451ALTER TABLE [ManageCar].[Car_Models] WITH CHECK ADD CONSTRAINT [FK_Car_Models_Car_Categories] FOREIGN KEY([car_categories_ID])
452REFERENCES [ManageCar].[Car_Categories] ([car_categories_id])
453GO
454IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[ManageCar].[FK_Car_Models_Car_Categories]') AND parent_object_id = OBJECT_ID(N'[ManageCar].[Car_Models]'))
455ALTER TABLE [ManageCar].[Car_Models] CHECK CONSTRAINT [FK_Car_Models_Car_Categories]
456GO