Проектування бази даних готельного комплексу

Розділ 3. Розробка об'єктів для доступу до реляційним даним

3.1 Розробка об'єктів для маніпулювання даними

Зберігаюча процедура - іменований набір команд Transact - SQL, що зберігається безпосередньо на сервері, що представляє собою, даних. Завдяки зберігаючим процедурам користувачеві не доводиться знову й знову вводити той самий набір команд для виконання якої-небудь дії. Якщо, ви часто виконуєте однотипні операції з досить більшою кількістю команд, ви рано або пізно захочете полегшити собі життя, реалізувавши цей набір команд у вигляді збереженої процедури. У результаті сервер буде послідовно виконувати всі команди, включені в процедуру

Зберігаючі процедури існують незалежно від таблиць або яких-небудь інших об'єктів баз даних. Зберігаюча процедура може бути викликана клієнтською
програмою, іншою збереженою процедурою або тригером. Можна управляти правами доступу користувачів до збережених процедур, дозволяючи або заборонити її виконання. Змінювати ж код збереженої процедури може тільки її власник або члени фіксованої ролі бази даних. При необхідності можна передати права володіння зберігаючою процедурою від одного користувача до іншого.

Використання збережених процедур має багато переваг:

- Перш ніж виконати зберігаючу процедуру, SQL Server 2005 генерує для неї так званий план виконання, виконує її оптимізацію й компіляцію. Таким чином, при першому виклику зберігаючої процедури сервер витрачає час і ресурси не тільки на виконання команд процедури, але й на підготовку їх до виконання, що вимагає чимало ресурсів. Для підвищення продуктивності SQL Server 2005 виконує кешування плану виконання процедури, а також оптимізованого й відкомпільованого коду. При повторному виклику тієї ж процедури сервер не витрачає ресурси на підготовку, а відразу ж приступає до виконання команд, скориставшись уже наявними даними Таким чином, збережені процедури дозволяють підвищити швидкість виконання операцій, тому що при повторному використанні вони вже завантажені в пам'яті, де знайти їх можна набагато легше, ніж на диску, до того ж не потрібна повторна компіляція й оптимізація.

- Зберігаючої процедури можуть складатися з десятків і сотень команд, але для їхнього запуску досить указати всього лише ім'я потрібної збереженої процедури. Це дозволяє зменшити розмір запиту, що посилає по мережі від клієнта на сервер. Таким чином, при використанні збережених процедур можливе зменшення навантаження на мережу

- Використання зберігаючих процедур реалізує принцип модульного проектування, тому що процедури дозволяють розбивати більші задачі на самостійні, більше дрібні й зручні в управлінні частини.

Таким чином, зберігаючі процедури мають багато переваг у порівнянні зі звичайними пакетами, які зберігаються безпосередньо на робочій станції клієнта. При цьому процес взаємодії зводиться до послідовної передачі пакетів команд на сервер й одержанню результатів. Недоліки цього підходу очевидні, оскільки час доступу до даних значно збільшується, до того ж на додаток клієнта лягає додаткове навантаження. При використанні ж збережених процедур клієнт здійснює тільки виклик збереженої процедури по її імені, потім сервер бази даних виконує блок команд, що становлять тіло викликаної процедури, і повертає клієнтові результат.

В курсовій роботі було розроблено 2 зберігаючих процедури: CREATE PROCEDURE PersonClientInsert та CREATE PROCEDURE PersonEmpInsert. Коди розробки зберігаючих процедури представлені в додатку Д

Найменші труднощі при розгляді наведеного вище коду викликає знайомий оператор INSERT. Єдиною відмінністю від класичного варіанта використання цього оператора є наявність змінних у пропозиції VALUES, що дозволяє змінювати внесену в таблиці Person й Employee інформацію, не переписуючи всього оператора Transact-SQL. Оскільки в заголовку збереженої процедури були визначені типи всіх змінних, можна не піклуватися про висновок у лапки внесених у таблицю строкових значень.

Розглянемо спосіб внесення інформації в таблицю Employee.

Найбільш важливий момент, що стосуються внесення інформації в таблицю Employee - необхідність добування з таблиці Person значення відповідного тільки що внесеним даним первинного ключа Personl.

Необхідно звернути увагу на оголошення змінної @PersonID у. розташований далі оператор, привласнює значення цієї змінної, рівне значенню стовпця PersonID у тільки що внесеної в таблицю Person нового запису. Оскільки Рersonl є ідентифікаційним стовпцем таблиці Person, варто скористатися деяким спеціальним методом добування інформації із цього стовпця.

MS SQL Server надає кілька різних методів добування інформації з ідентифікаційного стовпця таблиці, однак у цьому випадку для виконання цієї операції скористаємося функцією IDENT_CURRENT. Як аргумент ця функція приймає ім'я таблиці, а як результат повертає значення первинного ключа останньої внесеної в таблицю запису.

Значення первинного ключа PersonID останньої внесеної в таблицю Person запису необхідно для створення відповідного йому зовнішнього ключа таблиці Employee, що дозволить встановити зв'язок між записами обох таблиць. Наявність такого зв'язку надто важливо для цілісності бази даних взагалі й для проведення об'єднань таблиці Employee з іншими таблицями зокрема. Значите первинного ключа таблиці Person привласнюється локальної змінної @PersonID і передається як параметр у наступний оператор INSERT.

Оператор INSERT, здійснює внесення інформації в таблицю Employee. Виконаєте код, у результаті чого MS SQL Server поверне повідомлення, що підтверджує успішне створення збереженої процедури.

Для того щоб упевнитися в працездатності тільки що створеної збереженої процедури, її необхідно хоча б один раз виконати. Але перед цим давайте здійснимо додаткову перевірку зберігаюча в таблицях Person й Employee інформації й переконаємося в тім, що її внесення, принаймні теоретично, пройде коректно. Процедура CREATE PROCEDURE PersonClientInsert дуже схожа на попередню.

3.2 Розробка об'єктів для відображення реляційних даних

Представлення для кінцевих користувачів виглядає як таблиця але при цьому не містить даних, а лише представляє дані, розташовані в таблиці. Фізично Представлення реалізоване у вигляді запиту SQL, на основі якого виробляється вибірка даних з однієї або декількох таблиць або подань. Таким чином, Представлення - це віртуальні таблиці, обумовлені запитом. Подібно реальним таблицям, представлення містять іменовані стовпці й рядки з даними, які вони динамічно вибирають із таблиць і пропонують користувачеві як самостійні дані.

Найпростіше подання, створене на основі однієї таблиці й не має фільтрів, містить точно такий же набір даних, як і вихідна таблиця. Більш складні представлення містять інформацію з декількох таблиць, причому можна фільтрувати список рядків, які будуть включені в подання.

Представлення можна розглядати як фільтр, що накладає на таблицю. Запит, що визначає подання, може вибирати дані з однієї або декількох таблиць, а також інших подань у поточній або іншій базі даних. Для визначення подання, що поєднує дані із множини різних джерел, можуть бути використані розподілені запити.

Представлення є потужним і зручним інструментом керування даними. Представлення часто застосовується для обмеження доступу користувачів до конфіденційних даних у таблиці. Якщо один зі стовпців таблиці необхідно сховати від користувачів (наприклад, дані про зарплату співробітників), то найпростішим виходом буде створення подання, що включає всі стовпці крім одного. Користувачі можуть працювати з поданням, не підозрюючи про існування у вихідній таблиці додаткового стовпця. Коли в Представлення не включається стовпець вихідної таблиці, говорять, що на таблицю накладений вертикальний фільтр. Якщо в SQL - коді запиту встановлено одне або кілька умов для вибірки рядків, говорять, що на таблицю накладений горизонтальний фільтр.

При звертанні до представлення сервер перевіряє правильність всіх посилань у запиті. Перевіряється, чи існують об'єкти, які потрібні для виконання визначальне представлення запиту. Якщо одна з таблиць, на які посилається запит, була знищена, то представлення виявиться непрацездатним, і при спробі звернутися до нього користувачі одержать повідомлення про помилку. Якщо ж замість вилученої таблиці створюється нова таблиця з таким же ім'ям і структурою, то Представлення можна буде використати знову. Якщо нова таблиця має структуру, відмінну від структури первісної таблиці, то Представлення повинне бути знищене й створене заново.

Представлення дозволяє визначити спосіб подачі інформації, що зберігається кінцевому користувачеві. Використовуючи представлення, можна сховати від користувача код SQL, необхідний для добування з бази даних складних структур інформації.

У багатьох аспектах представлення характеризується функціями. Варто лише відзначити, що деякі розроблювачі звикли називати представлення "віртуальною таблицею". Справа в тому, що представлення саме по собі не зберігає ніякої інформації з бази даних. Усе, що містить представлення, - це посилання на таблицю (або таблиці), що зберігає потрібні дані. Таким чином, при оголошенні інформації в поданні насправді обновляється інформація базових таблицях, на яких посилається це представлення.

Завдяки схожому з таблицею поводженню, представлення дозволяє виконати стосовно нього характерні для таблиць функції. Це має на увазі можливість використання таких операторів керування даними, як INSERT, UPDATE й DELETE, хоча тут існують деякі специфічні обмеження.

Отже, головна задача представлень - спростити спосіб перегляду, що зберігається в базі дані інформації для кінцевого користувача. Представлення мають кілька істотних переваг:

- можливість приречення структури даних для перегляду кінцевим користувачем;

- можливість обмеження прав на перегляд представлення;

- можливість використання представлення для створення звітності.

В даній курсовій роботі використано 2 представлення PersonAddress та OrdersData. Код використання представлення розмішений в додатку Д.

Оператор CREATE VIEW вказує СУБД на створення представлення PersonAddress. Ключове слово AS вказує на початок оператора. Увесь наступний за цим словом код визначає поведінку данного представлення.

Далі йде перелік стовпців, які необхідно витягти з бази даних . Наявність додаткових префіксів (Р., А. і т.п.) перед кожним ім'ям стовпця. Подібна практика іменування стовпців відома як використання псевдонімів. Використання псевдонімів (aliasing) - досить розповсюджений термін у більшості мов програмування. Цей термін позначає присвоєння об'єкту "дружнього" імені (як правило, скороченого). Використання псевдоніма дозволяє на короткий період змінити ім'я об'єкта. MS SQL Server допускає призначення псевдонімів для досить великої кількості об'єктів бази даних, підтвердженням тому може служити призначення псевдонімів для таблиць (наприклад, псевдонім 'А' позначає таблицю Address) і для стовпців (наприклад, псевдонім 'Country' відповідає стовпцю С.Description) у наведеному вище визначенні подання PersonAddress.

Використання псевдонімів в іменах стовпців дозволяє явно вказати таблиці, з яких були взяті ці стовпці. Слід зазначити, що в цьому твердженні криється невелика неточність, тому що, наприклад, у наведеному вище коді був використаний псевдонім А, хоча в дійсності таблиці з таким ім'ям у базі даних немає. Визначення псевдоніма стовпця дозволяє призначити ім'я, яке цей стовпець буде мати в представленні. У цьому випадку стовпцю Description таблиці Country був призначений псевдонім Country, а стовпцю Description таблиці AddressType - псевдонім AddiessType.

Далі визначається рядок що являється джерелом даних для оператора SELECT.

Розмішений далі код призначений для витягу інформації з інших таблиць бази даних за допомогою використання оператора INNER JOIN. Цей оператор указує MS SQL Server на необхідність об'єднання результатів запиту з даними, які зберігаються в таблиці, зазначеної безпосередньо після ключового слова INNER JOIN. Наприклад, вказується необхідність об'єднання таблиці Person з таблицею Address. Використовуючи оператор INNER JOIN, варто обов'язково вказати спосіб об'єднання двох таблиць. У випадку з таблицями Person й Address об'єднання здійснюється по первинному ключу таблиці Person (P.Personl) і зовнішньому ключу таблиці Address (A. Personl). Таким чином ми вказуємо MS SQL Server на необхідність об'єднання інформації, що зберігається в таблицях.

Оператори INNER JOIN виконують об'єднання таблиці Address із двома іншими таблицями, які містять первинний ключ, що відповідає зовнішньому ключу таблиці Address.

Далі виконується об'єднання таблиць Address й AddressType. Це досягається шляхом зв'язування первинного ключа таблиці AddressType (AddressTypel) і зовнішнього ключа таблиці Address (AddressTypel). Аналогічним образом (тобто використовуючи зовнішній ключ однієї й первинний ключ іншої таблиці) у рядках 6 й 6а виконується об'єднання таблиць Address й Country.

3.3 Розробка об'єктів для обробки подій в базі даних

Залежно від виконуваних користувачем дій, що приводять до запуску тригера, вони діляться на три категорії:

- тригери зміни (UPDATE TRIGGER);

- тригери вставки (INSERT TRIGGER);

- тригери видалення (DELETE TRIGGER).

Починаючи з SQL Server 7.0., з'явилася можливість визначати для таблиці кілька тригерів кожного типу. У попередніх версіях SQL Server для кожної таблиці можна було створити тільки один тригер кожного типу. Крім того, дії, виконувані в одному тригері, можуть привести до виклику інших тригерів. У цьому випадку використаються вкладені тригери

Тригер - це спеціальний тип збережених процедур, що запускають сервером автоматично при виконанні тих або інших дій з даними таблиці. Кожен тригер прив'язаний до конкретної таблиці. Коли користувач намагається, наприклад, змінити дані в таблиці, сервер автоматично запускає тригер й, якщо він завершується успішно, дозволяється виконання змін. Всі вироблені тригером модифікації даних розглядаються як одна транзакція. У випадку виявлення помилки або порушенні цілісності даних відбувається відкат цієї транзакції. Тим самим внесення змін забороняється. Скасовуються також всі зміни, уже зроблені тригером.

Область застосування тригерів досить широка. Наприклад, при реплікації відомістю тригери використаються для відстеження всіх виконуваних у таблиці змін. Тригери збирають інформацію про вироблені зміни й зберігають її в системних таблицях підтримки реплікації. Тригери також дозволяють створювати складні значення за замовчуванням, обчислюючи їх за допомогою даних декількох стовпців таблиць і функцій Transact - SQL. Іншим прикладом використання тригерів є забезпечення нестандартної цілісності посилань, підтримка якої звичайними засобами SQL Server неможливо. Крім того, за допомогою тригерів можна виконувати каскадні зміни в декількох зв'язаних таблицях

Область застосування тригерів не обмежується якимись строго обкресленими рамками. Можна вільно застосовувати тригери за своїм розсудом виходячи з вимог до зручності й продуктивності.

В курсовій роботі було використано 2 тригери CREATE TRIGGER CheckEmployeeNotInClient та CREATE TRIGGER CheckClientNotInEmployee. Код використання тригерів пердставлений у додатку Д.

Створення тригера асоціюється з деякою подією. Оскільки подія виникає внаслідок здійснення дії над таблицею, при визначенні тригера необхідно вказати ім'я таблиці, для якої він призначається. У цьому випадку вказується, що тригер CheckClientNotlnEmployee призначений для таблиці Client.

Далі визначаються події, з якими повинен бути асоційований тригер. Тригер CheckClientNotInEmployee асоціюється відразу із двома подіями таблиці Client - INSERT й UPDATE. При бажанні можна зв'язати тригер з усіма подіями таблиці (DELETE, INSERT й UPDATE) або ж тільки з одним з них.

Ключові слова AS повністю збігається з його призначенням в оголошеннях подань і збережених процедур.

Розташований далі спеціальний оператор, головна функція якого - гарантувати можливість повернення до попереднього стану таблиці у випадку внесення (INSERT)в неї некоректної інформації.

Далі оголошуються змінні , які будуть використані для зберігання значення стовпця PersonID тільки що внесеної в таблицю запису й значення стовпця Employeel таблиці Employee.

Майже всю частину, що залишилася, тригера займає оператор SELECT, призначений для перевірки наявності запису із заданим ідентифікаційним номером у таблиці Employee. Розташований у далі оператор SELECT виконує функцію присвоєння змінної @PersonID значення стовпця PersonID тільки що внесеної в таблицю Client запису.

Керуючий оператор IF, здійснює перевірку значення змінної @EmployeeID. Якщо змінна не має ніякого значення, то виконання тригера переходить у рядок ELSE. COMMIT TRANSACTION оператор, що підтверджує правочинність зміни інформації в таблиці.

Код, RAISERROR ('Попытка вставить в таблицу Employee объект, присутствующий в таблице Client.'16, 1) ROLLBACK TRANSACTION, повідомляє користувача про помилку що виникла. Для цього використається убудована функція MS SQL Server RAISERROR.

Висновок

В процесі розробки даного проекту було створено базу даних, що веде облік замовлень клієнтів та містить всю поточну інформацію про готельний комплекс.

Проблема організації замовлень готельних номерів та організацією відношень між клієнтами та співробітниками готельного комплексу була, є і, очевидно.

В проекті застосована лише одна з можливих стратегій роботи з замовленнями. Для подальшої діяльності можливе збільшення гнучкості замовлень не обмежуючись наявною кількістю товару, а й враховувати дані замовлень при плануванні виробництва.

В даній роботі як інструмент створення та ведення БД використовувався програмний продукт MS SQL 2005.

За допомогою нього для бази створено ряд таблиць, правил, збережених процедур та тригерів, які дозволяються правильно користуватися базою даних.

Розроблена БД не може претендувати на повне охоплення всієї інформаційної області обліку замовлень, але основна мета досягнута.

В ході курсової роботи були використані збережені процедури, тригери, представлення.

Список використаної літератури

1. Хансен Г., Хансен Дж. Базы данных: разработка и управление: англ.- М.: БИНОМ, 2000.- 704 c.- ISBN 5-7989-0015-0.

2. Дейт К.Дж. Введение в системы баз данных: англ: .- 6-е изд..- К.-М.: Диалектика, 1998.- 784 c.- ISBN 966-506-124-0.

3. Мамаев Е., Шкарина Л. Microsoft SQL Server 2000 для профессионалов.- СПб.: Питер, 2001.- 1088 c.- ISBN 5-272-00339- Х.

4. Галузинський Г.П., Гордієнко І.В. Перспективні технологічні засоби оброблення інформації: Навчально-методичний посібник: Навчальне видання.- К.: КНЕУ, 2002.- 280 c.- ISBN 966-574-359-7.

5. Когаловский М.Р. Энциклопедия технологий баз данных.- М.: Финансы и статистика, 2002.- 800 c.- ISBN 5-279-02276-4.

6. Косенко Е. Реванш встроенных СУБД. Часть первая. Выбор подхода или подход к выбору? // Компьютеры + программы (рус.).- 2002.- № 4.- C.50-54.

7. Хоторн Р. Разработка баз данных Microsoft SQL Server 2000 на примерах: англ.- М.: Вильямс, 2001.- 464 c.- ISBN 5-8459-0187-1. УДК - 004.65

8. Литвин І.С. Інформаційні технології в економіці: Навчальний посібник.- Тернопіль: Економічна думка, 2001.- 296 c.- ISBN 5-7763-0459-8

9. 1.Астахова И.Ф., Толстобров А.П. Мельников В.М. SQL в примерах и задачах: Учебное пособие: Навчальне видання.- Минск: Новое знание, 2002.- 176 c.- ISBN 985-475-004-3.

10. Лизун А. Работа с датами в MS-SQL // ARGC & ARGV (рус.).- 2001.- № 6.- C.42-45.

Додаток

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

Размещено на Allbest.ru