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, 4