46669 (607907), страница 3
Текст из файла (страница 3)
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].[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]'))
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
