p align="left">SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Users]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[Users]( [UserID] [int] IDENTITY(1,1) NOT NULL, [UserLogin] [nvarchar](20) NOT NULL, [Password] [nvarchar](20) NOT NULL, [Email] [nvarchar](50) NULL, CONSTRAINT [PK_Users_1] PRIMARY KEY CLUSTERED ( [UserID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Cities]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[Cities]( [CityID] [int] IDENTITY(1,1) NOT NULL, [CityName] [nvarchar](40) NULL, CONSTRAINT [PK_Cities] PRIMARY KEY CLUSTERED ( [CityID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Aircrafts]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[Aircrafts]( [AircraftID] [int] IDENTITY(1,1) NOT NULL, [AircraftModel] [nvarchar](40) NULL, [Count1] [int] NULL, [Count2] [int] NULL, CONSTRAINT [PK_Aicrafts] PRIMARY KEY CLUSTERED ( [AircraftID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[timeFlight]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) BEGIN execute dbo.sp_executesql @statement = N'CREATE FUNCTION [dbo].[timeFlight] ( @dateArrival datetime, @dateDeparture datetime ) RETURNS char(5) BEGIN RETURN convert(char(2),datediff(hh,@dateDeparture,@dateArrival))+'':''+ convert(char(2),datediff(mi,@dateDeparture,@dateArrival)-datediff(hh,@dateDeparture,@dateArrival)*60) END' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Flights]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[Flights]( [FlightID] [int] IDENTITY(1,1) NOT NULL, [DateDeparture] [datetime] NULL, [DateArrival] [datetime] NULL, [Price1] [decimal](18, 0) NULL, [Price2] [decimal](18, 0) NULL, [CompanyID] [int] NULL, [AircraftID] [int] NULL, [CityDepartureID] [int] NULL, [CityArrivalID] [int] NULL, CONSTRAINT [PK_Flights] PRIMARY KEY CLUSTERED ( [FlightID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] END GO IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Flights]') AND name = N'IX_Fligts_AircraftID') CREATE NONCLUSTERED INDEX [IX_Fligts_AircraftID] ON [dbo].[Flights] ( [AircraftID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] GO IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Flights]') AND name = N'IX_Fligts_CityArrivalID') CREATE NONCLUSTERED INDEX [IX_Fligts_CityArrivalID] ON [dbo].[Flights] ( [CityArrivalID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] GO IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Flights]') AND name = N'IX_Fligts_CityDepartureID') CREATE NONCLUSTERED INDEX [IX_Fligts_CityDepartureID] ON [dbo].[Flights] ( [CityDepartureID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] GO IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Flights]') AND name = N'IX_Fligts_CompanyID') CREATE NONCLUSTERED INDEX [IX_Fligts_CompanyID] ON [dbo].[Flights] ( [CompanyID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[TR_Flights_Delete]')) EXEC dbo.sp_executesql @statement = N'CREATE TRIGGER [dbo].[TR_Flights_Delete] ON [dbo].[Flights] INSTEAD OF DELETE AS DELETE FROM Orders WHERE Orders.FlightID= (SELECT top(1) deleted.FlightID FROM deleted) DELETE FROM Flights WHERE Flights.FlightID= (SELECT top(1) deleted.FlightID FROM deleted) RETURN' GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Orders]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[Orders]( [OrderID] [int] IDENTITY(1,1) NOT NULL, [CreditCard] [varchar](16) NULL, [Number1cl] [int] NULL, [Number2cl] [int] NULL, [UserID] [int] NULL, [FlightID] [int] NULL, CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED ( [OrderID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] END GO IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Orders]') AND name = N'IX_Orders_UserID') CREATE NONCLUSTERED INDEX [IX_Orders_UserID] ON [dbo].[Orders] ( [UserID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Insert_User]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'-- Вставка новой записи в таблицу Users CREATE PROCEDURE [dbo].[Insert_User] @login nvarchar(20), @password nvarchar(20), @email nvarchar(50) AS BEGIN INSERT Users (UserLogin, Password, Email) VALUES (@login, @password, @email) END ; ' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Delete_User]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'-- Удаление записи из таблицы Users CREATE PROCEDURE [dbo].[Delete_User] @UserID int AS BEGIN DELETE FROM Users WHERE [UserID]=@UserID END ; ' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CountEmptyPlaces1cl]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) BEGIN execute dbo.sp_executesql @statement = N'CREATE FUNCTION [dbo].[CountEmptyPlaces1cl] ( @flightID int ) RETURNS int BEGIN DECLARE @count int; IF EXISTS (SELECT OrderID FROM Orders WHERE FlightID=@flightID AND Number1cl>0) SELECT @count=Aircrafts.Count1 -(SELECT SUM(Number1cl) FROM Orders WHERE FlightID=@flightID) FROM Flights,Aircrafts WHERE Flights.FlightID=@flightID AND Flights.AircraftID=Aircrafts.AircraftID ELSE SELECT @count=Aircrafts.Count1 FROM Flights,Aircrafts WHERE Flights.FlightID=@flightID AND Flights.AircraftID=Aircrafts.AircraftID RETURN @count END' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CountEmptyPlaces2cl]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) BEGIN execute dbo.sp_executesql @statement = N'CREATE FUNCTION [dbo].[CountEmptyPlaces2cl] ( @flightID int ) RETURNS int BEGIN DECLARE @count int; IF EXISTS (SELECT OrderID FROM Orders WHERE FlightID=@flightID AND Number2cl>0) SELECT @count=Aircrafts.Count2 -(SELECT SUM(Number2cl) FROM Orders WHERE FlightID=@flightID) FROM Flights,Aircrafts WHERE Flights.FlightID=@flightID AND Flights.AircraftID=Aircrafts.AircraftID ELSE SELECT @count=Aircrafts.Count2 FROM Flights,Aircrafts WHERE Flights.FlightID=@flightID AND Flights.AircraftID=Aircrafts.AircraftID RETURN @count END' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Insert_FlightString]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'-- Вставка новой записи в таблицу Flight CREATE PROCEDURE [dbo].[Insert_FlightString] @dateDeparture datetime, @dateArrival datetime, @price1 decimal(18,0), @price2 decimal(18,0), @company int, @aircraft int, @cityDeparture int, @cityArrival int AS BEGIN INSERT INTO [Flights] ([DateDeparture],[DateArrival], [Price1],[Price2], [CompanyID],[AircraftID], [CityDepartureID],[CityArrivalID]) VALUES ( @dateDeparture,@dateArrival, @price1,@price2, @company,@aircraft, @cityDeparture,@cityArrival) END ; ' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Update_FlightString]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'-- Обновление записи в таблице Flights CREATE PROCEDURE [dbo].[Update_FlightString] @FlightID int, @dateDeparture datetime, @dateArrival datetime, @price1 decimal(18,0), @price2 decimal(18,0), @companyID int, @aircraftID int, @cityDepartureID int, @cityArrivalID int AS BEGIN UPDATE Flights SET dateDeparture = @dateDeparture, dateArrival = @dateArrival, price1 = @price1, price2 = @price2, cityDepartureID = @cityDepartureID, cityArrivalID = @cityArrivalID, companyID = @companyID, aircraftID = @aircraftID WHERE FlightID = @FlightID END ; ' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Delete_Order]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'-- Удаление записи из таблицы Orders по OrderID CREATE PROCEDURE [dbo].[Delete_Order] @orderID int AS BEGIN DELETE FROM Orders WHERE [OrderID]=@orderID END ; ' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Delete_OrderByUserID]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'-- Удаление записей из таблицы Orders по UserID CREATE PROCEDURE [dbo].[Delete_OrderByUserID] @UserID int AS BEGIN DELETE FROM Orders WHERE [UserID]=@UserID END ; ' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[FlightView]')) EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[FlightView] AS SELECT FlightID, DateDeparture, DateArrival, dbo.timeFlight(DateArrival,DateDeparture) AS TimeFlight, CityDepartureID, CityArrivalID, DepartureCities.CityName AS CityDeparture, ArrivalCities.CityName AS CityArrival, CompanyName, AircraftModel, Price1, Price2, dbo.CountEmptyPlaces1cl(FlightID) AS EmptyPlace1cl, dbo.CountEmptyPlaces2cl(FlightID) AS EmptyPlace2cl FROM dbo.Flights INNER JOIN dbo.Companies ON Group0703b.dbo.Flights.CompanyID = Group0703b.dbo.Companies.CompanyID INNER JOIN Group0703b.dbo.Aircrafts ON Group0703b.dbo.Flights.AircraftID = Group0703b.dbo.Aircrafts.AircraftID LEFT OUTER JOIN Group0703b.dbo.Cities AS DepartureCities ON Group0703b.dbo.Flights.CityDepartureID = DepartureCities.CityID LEFT OUTER JOIN Group0703b.dbo.Cities AS ArrivalCities ON Group0703b.dbo.Flights.CityArrivalID = ArrivalCities.CityID ' GO IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Flights_Aircrafts]') AND parent_object_id = OBJECT_ID(N'[dbo].[Flights]')) ALTER TABLE [dbo].[Flights] WITH CHECK ADD CONSTRAINT [FK_Flights_Aircrafts] FOREIGN KEY([AircraftID]) REFERENCES [dbo].[Aircrafts] ([AircraftID]) GO ALTER TABLE [dbo].[Flights] CHECK CONSTRAINT [FK_Flights_Aircrafts] GO IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Flights_Cities_Arrival]') AND parent_object_id = OBJECT_ID(N'[dbo].[Flights]')) ALTER TABLE [dbo].[Flights] WITH CHECK ADD CONSTRAINT [FK_Flights_Cities_Arrival] FOREIGN KEY([CityArrivalID]) REFERENCES [dbo].[Cities] ([CityID]) GO ALTER TABLE [dbo].[Flights] CHECK CONSTRAINT [FK_Flights_Cities_Arrival] GO IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Flights_Cities_Departure]') AND parent_object_id = OBJECT_ID(N'[dbo].[Flights]')) ALTER TABLE [dbo].[Flights] WITH CHECK ADD CONSTRAINT [FK_Flights_Cities_Departure] FOREIGN KEY([CityDepartureID]) REFERENCES [dbo].[Cities] ([CityID]) GO ALTER TABLE [dbo].[Flights] CHECK CONSTRAINT [FK_Flights_Cities_Departure] GO IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Flights_Companies]') AND parent_object_id = OBJECT_ID(N'[dbo].[Flights]')) ALTER TABLE [dbo].[Flights] WITH CHECK ADD CONSTRAINT [FK_Flights_Companies] FOREIGN KEY([CompanyID]) REFERENCES [dbo].[Companies] ([CompanyID]) GO ALTER TABLE [dbo].[Flights] CHECK CONSTRAINT [FK_Flights_Companies] GO IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Orders_Flights]') AND parent_object_id = OBJECT_ID(N'[dbo].[Orders]')) ALTER TABLE [dbo].[Orders] WITH CHECK ADD CONSTRAINT [FK_Orders_Flights] FOREIGN KEY([FlightID]) REFERENCES [dbo].[Flights] ([FlightID]) GO ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Flights] GO IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Orders_Users]') AND parent_object_id = OBJECT_ID(N'[dbo].[Orders]')) ALTER TABLE [dbo].[Orders] WITH CHECK ADD CONSTRAINT [FK_Orders_Users] FOREIGN KEY([UserID]) REFERENCES [dbo].[Users] ([UserID]) GO ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Users]
Страницы: 1, 2, 3
|