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 |