Xreferat.com » Рефераты по информатике и программированию » Автоматизированная система бронирования авиабилетов

Автоматизированная система бронирования авиабилетов

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]

Если Вам нужна помощь с академической работой (курсовая, контрольная, диплом, реферат и т.д.), обратитесь к нашим специалистам. Более 90000 специалистов готовы Вам помочь.
Бесплатные корректировки и доработки. Бесплатная оценка стоимости работы.

Поможем написать работу на аналогичную тему

Получить выполненную работу или консультацию специалиста по вашему учебному проекту
Нужна помощь в написании работы?
Мы - биржа профессиональных авторов (преподавателей и доцентов вузов). Пишем статьи РИНЦ, ВАК, Scopus. Помогаем в публикации. Правки вносим бесплатно.

Похожие рефераты: