1   2   3
Ім'я файлу: Курсова бази даних готельного комплексу.doc
Розширення: doc
Розмір: 357кб.
Дата: 16.10.2020
скачати


ДОДАТОК Б


ДОДАТОК В
USE master

GO

CREATE DATABASE Hotel

ON PRIMARY (NAME = 'Hotel_Data',

FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ Hotel_Data.MDF',

SIZE = 5MB,

FILEGROWTH = 10%)

LOG ON (NAME = 'Hotel_Log',

FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ Hotel_Log.LDF',

SIZE = 5MB,

FILEGROWTH = 10%)

GO

ДОДАТОК Г
CREATE TABLE Person

(

PersonID INT IDENTITY(1,1)NOT NULL

PRIMARY KEY CLUSTERED,

FirstName VARCHAR(50)NOT NULL,

SurName VARCHAR(50) NOT NULL,

Address VARCHAR(50) NOT NULL,

)
GO

CREATE TABLE Country

(

CountryID INT IDENTITY(1, 1) NOT NULL

CONSTRAINT PK_CountryID

PRIMARY KEY CLUSTERED,

Description VARCHAR(50) NOT NULL

)
GO

CREATE TABLE City

(

CityID INT IDENTITY(1, 1) NOT NULL

CONSTRAINT PK_CityID

PRIMARY KEY CLUSTERED,

Description VARCHAR(50) NOT NULL

)
GO

CREATE TABLE AddressType

(

AddressTypeID INT IDENTITY(1, 1) NOT NULL

CONSTRAINT PK_AddressTypeID

PRIMARY KEY CLUSTERED,

Description VARCHAR(50) NOT NULL

)
GO

CREATE TABLE Address

(

PRIMARY KEY CLUSTERED(PersonID,AddressTypeID,CityID,CountryID),

PersonID INT IDENTITY(1,1)NOT NULL,

AddressTypeID INT NOT NULL,

CityID INT NOT NULL,

CountryID INT NOT NULL,

Address VARCHAR(50)NOT NULL,

CONSTRAINT FK_Address_Person

FOREIGN KEY (PersonID)

REFERENCES Person(PersonID),
CONSTRAINT FK_Address_AddressType

FOREIGN KEY (AddressTypeID)

REFERENCES AddressType(AddressTypeID),

CONSTRAINT FK_Address_City

FOREIGN KEY (CityID)

REFERENCES City(CityID),

CONSTRAINT FK_Address_Country

FOREIGN KEY (CountryID)

REFERENCES Country(CountryID)

)
GO

CREATE TABLE Post

(

PostID INT IDENTITY(1, 1) NOT NULL

CONSTRAINT PK_PostID

PRIMARY KEY CLUSTERED,

Description VARCHAR (50) NOT NULL

)
GO

CREATE TABLE Employee

(

EmployeeID INT IDENTITY(1, 1) NOT NULL

CONSTRAINT PK_EmployeeID

PRIMARY KEY CLUSTERED (EmployeeID),

PersonID INT NOT NULL,

PostID INT NOT NULL,

Salary VARCHAR(50) NOT NULL,

CONSTRAINT FK_Employee_Person

FOREIGN KEY (PersonID)

REFERENCES Person(PersonID),

CONSTRAINT FK_Employee_Post

FOREIGN KEY (PostID)

REFERENCES Post(PostID),

)
GO

CREATE TABLE Client

(

ClientID INT IDENTITY(1,1)NOT NULL

CONSTRAINT PK_ClientID

PRIMARY KEY CLUSTERED,

PersonID INT NOT NULL,

AddressID INT NOT NULL,

OrderrID INT NOT NULL,

Status VARCHAR(50) NOT NULL

CONSTRAINT FK_Client_Person

FOREIGN KEY (PersonID)

REFERENCES Person(PersonID))
GO

CREATE TABLE TypeHotelRoom

(

TypeHotelRoomID INT IDENTITY(1, 1) NOT NULL

CONSTRAINT PK_PaymentTypeID

PRIMARY KEY CLUSTERED,

Description VARCHAR (50) NOT NULL,

PostID INT NOT NULL,

EmployeeID INT NOT NULL,

CONSTRAINT FK_TypeHotelRoom_Employee

FOREIGN KEY (EmployeeID)

REFERENCES Employee(EmployeeID)

)

GO

CREATE TABLE Comfort

(

ComfortID INT IDENTITY(1, 1) NOT NULL

CONSTRAINT PK_ComfortID

PRIMARY KEY CLUSTERED,

Description VARCHAR (50) NOT NULL

)
GO

CREATE TABLE Payment

(

PaymentID INT IDENTITY(1, 1) NOT NULL

CONSTRAINT PK_PaymentID

PRIMARY KEY CLUSTERED,

Description VARCHAR (50) NOT NULL

)
GO

CREATE TABLE Orders

(

PRIMARY KEY CLUSTERED (TypeHotelRoomID,ClientID,ComfortID,PaymentID ),

TypeHotelRoomID INT NOT NULL,

ClientID INT NOT NULL,

ComfortID INT NOT NULL,

PaymentID INT NOT NULL,

TimeResidence DATETIME NOT NULL

CONSTRAINT FK_Orders_TypeHotelRoom

FOREIGN KEY (TypeHotelRoomID)

REFERENCES TypeHotelRoom(TypeHotelRoomID),

CONSTRAINT FK_Orders_Client

FOREIGN KEY (ClientID)

REFERENCES Client(ClientID),

CONSTRAINT FK_Orders_Comfort

FOREIGN KEY (ComfortID)

REFERENCES Comfort(ComfortID),

CONSTRAINT FK_Orders_Payment

FOREIGN KEY (PaymentID)

REFERENCES Payment(PaymentID)

)

GO

CREATE UNIQUE NONCLUSTERED INDEX IDX_Employee_Person

ON Employee(PersonID)
GO

CREATE UNIQUE NONCLUSTERED INDEX IDX_Orders_Comfort

ON Orders(ComfortID)
GO

CREATE UNIQUE NONCLUSTERED INDEX IDX_Orders_Payment

ON Orders(PaymentID)

ДОДАТОК Д
CREATE PROCEDURE PersonEmpInsert

@Firstname VARCHAR(50),

@Surname VARCHAR (50),

@Address VARCHAR (50),

@PostID INT = NULL,

@Salary INT = NULL

AS

DECLARE @LocalError INT

BEGIN TRANSACTION

INSERT INTO Person (Firstname, Surname, Address)

VALUES (@Firstname, @Surname, @Address)

SET @LocalError = @@ERROR

DECLARE @PersonID INT

SET @PersonID = IDENT_CURRENT('Person')

INSERT INTO Employee (PersonID, PostID,

Salary)

VALUES (@PersonID, @PostID,

@Salary)

SET @LocalError = @LocalError + @@ERROR

IF @LocalError = 0

BEGIN

COMMIT TRANSACTION

PRINT 'Вы успешно добавили нового клиента в список'

END

ELSE

BEGIN

IF @LocalError = 547

BEGIN

ROLLBACK TRANSACTION

PRINT 'Ошибка ввода. Вы должны добавить

клиента в таблицу Person перед лобавлением его в таблицу Client'

END

ELSE

BEGIN

ROLLBACK TRANSACTION

PRINT 'Произошла ошибка, попробуйте повторить ввод '

END

END
CREATE PROCEDURE PersonClientInsert

@Firstname VARCHAR(50),

@Surname VARCHAR (50),

@Status VARCHAR(25) = NULL,

@AddressTypeID VARCHAR(10),

@CityID VARCHAR(10),

@CountryID VARCHAR(10),

@Address VARCHAR(10)

AS

DECLARE @PersonID INT

INSERT INTO Person (Firstname, Surname, Address)

VALUES (@Firstname, @Surname, @Address)

SET @PersonID = IDENT_CURRENT('Person')

INSERT INTO Client (PersonID, Status)

VALUES (@PersonID, @Status)

INSERT INTO Address (AddressTypeID, CityID, CountryID, Address )

VALUES (@AddressTypeID, @CityID, @CountryID, @Address)

SET @PersonID = IDENT_CURRENT('Person')
GO

Create view OrdersData AS

SELECT

P.Surname AS 'Фамилия',

P.FirstName AS 'Имя',

OT.Description AS 'Тип заказа',

T.Description AS 'Тип транспорта'

FROM Orders O

INNER JOIN Client C

ON O.ClientID = C.ClientID

INNER JOIN Person P

ON C.PersonID = P.PersonID

INNER JOIN OrderType OT

ON OT.OrderTypeID = O.OrderTypeID

INNER JOIN Transport T

ON T.TransportID = O.TransportID
Go

CREATE VIEW PersonAddress AS

SELECT

P.Firstname,

P.Surname,

A.Address,

AType.Description AS AddressType

FROM Person P

INNER JOIN Address A

ON P.PersonID = A.PersonID

INNER JOIN AddressType AType

ON A.AddressTypeID = AType.AddressTypeID
CREATE TRIGGER CheckClientNotInEmployee

ON Client

FOR INSERT, UPDATE

AS

BEGIN TRANSACTION

DECLARE @EmployeeID INT

DECLARE @PersonID INT

SELECT @PersonID = i.PersonID

FROM inserted i

SELECT @EmployeeID = EmployeeID

FROM Employee E

WHERE E.PersonID = @PersonID

IF (@EmployeeID IS NOT NULL) AND (@EmployeeID > 0)

BEGIN

RAISERROR ('Попытка вставить в таблицу Client объект, присутствующий в

таблице Employee.', 16, 1)

ROLLBACK TRANSACTION

END

ELSE

BEGIN

COMMIT TRANSACTION

END
GO
CREATE TRIGGER CheckEmployeeNotInClient

ON Employee

FOR INSERT, UPDATE

AS

BEGIN TRANSACTION

DECLARE @ClientID INT

DECLARE @PersonID INT

SELECT @PersonID = i.PersonID

FROM inserted i

SELECT @ClientID = ClientID

FROM Client C

WHERE C.PersonID = @PersonID

IF (@ClientID IS NOT NULL) AND (@ClientID > 0)

BEGIN

RAISERROR ('Попытка вставить в таблицу Employee объект,

присутствующий в таблице Client.',

16, 1)

ROLLBACK TRANSACTION

END

ELSE

BEGIN

COMMIT TRANSACTION

END
1   2   3

скачати

© Усі права захищені
написати до нас