Автоматизована система бронювання авіаквитків

Курсова робота

з дисципліни: «Корпоративні інформаційні системи»

на тему:

Автоматизована система бронювання авіаквитків

Зміст

Введення

1. Опис моделі

2. Побудова моделі

2.1 Етап концептуального проектування

2.2 Етап логічного проектування

3. Реалізація запитів

3.1 Отримання інформації про рейси

3.2 Пошук рейсів за певними критеріями

3.3 Отримання списку замовлень

3.4 Замовлення квитків на обраний рейс

3.5 Видалення замовлення

3.6 Додавання рейсу

3.7 Редагування рейсу

3.8 Видалення рейсу

3.8 Додавання користувача

3.9 Видалення користувача

4. Опис роботи програми

Висновок

Список використаних джерел

Додаток А

Введення

Метою даної курсової роботи є розробка багатокористувацької системи бронювання авіаквитків.

Для цього необхідно вирішити такі завдання:

- Дослідити предметну область;

- Побудувати концептуальну модель предметної області;

- Побудувати даталогіческую модель організації даних;

- Реалізувати базу даних за допомогою MS SQL Server 2005;

- Реалізувати відповідне WEB-додаток;

- Зробити розгортання та тестування системи.

Об'єктом дослідження є діяльність авіа-агентства. Агентство надає послуги на замовлення квитків на авіарейси різних авіакомпаній. Кожен рейс випливає з пункту відправлення до пункту призначення. Рейс має дату і час вильоту, дату і час прибуття. Кожен рейс виконується літаком певної моделі, в салоні якого є місця першого і другого класу. Залежно від класу, квиток має різну ціну.

Для кожної моделі літака є певна кількість місць кожного класу. Кожна модель літака характеризується авіакомпанією-виробником.

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

1. Опис моделі

Найбільш важливими елементами моделі обрані рейси (flights) і замовлення (orders).

Кожен рейс характеризується наступною інформацією:

- Літак, що обслуговує рейс,

- Місто відправлення,

- Місто прибуття,

- Дата відправлення,

- Дата прибуття,

- Вартість квитків першого класу,

- Вартість квитків другого класу.

Замовлення описується таким набором полів:

- Користувач, який оформив замовлення,

- Рейс, на який оформлений замовлення,

- Кількість замовлених квитків першого класу,

- Кількість замовлених квитків другого класу,

- Номер кредитної карти.

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

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

У системі передбачається використовувати такі ролі користувачів:

Адміністратор: має права на перегляд повного списку рейсів, включаючи замовлення.

Клієнт: має право на відбір рейсів за певними параметрами та на замовлення квитка.

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

2. Побудова моделі

2.1 Етап концептуального проектування

На етапі концептуального проектування використовувалася модель «сутність-зв'язок». Як видно з малюнка 1, в предметній області виділено 6 сутностей: користувачі (users), замовлення (orders), рейси (flights), міста (cities), літаки (aircrafts) і авіакомпанії (companies). Зв'язки між сутностями також зображені на малюнку. Атрибути сутностей та їх типи більш докладно будуть розглянуті на етапі проектування.

Рисунок 1 - ER-модель розробленої системи

2.2 Етап логічного проектування

Використання реляційної моделі даних в системах управління базами даних було запропоновано в 1970 р. доктором Е. Ф. Коддом. Одним з важливих достоїнств реляційного підходу є його простота, а звідси і доступність для розуміння кінцевим користувачем.

Розглянемо схеми відносин, використовуваних в реляційної моделі:

R1 = ORDERS (OrderID, CreditCard, Number1cl, Number2cl, UserID, FlightID),

де:

- OrderID - ідентифікатор замовлення;

- CreditCard - номер кредитної картки;

- Number1cl - кількість замовлених квитків першого класу;

- Number2cl - кількість квитків другого класу;

- UserID - ідентифікатор користувача, що оформив замовлення;

- FlightID - ідентифікатор рейсу, на який оформлено дане замовлення.

R2 = FLIGHTS (FlightID, DateDeparture, DateArrival, Price1, Price2, AircraftID, CityDepatrureID, CityArrivalID),

де:

- FlightID - ідентифікатор рейсу;

- DateDeparture - дата відправлення;

- DateArrival - дата прибуття;

- Price1 - вартість квитка першого класу;

- Price2 - вартість квитка другого класу;

- CompanyID - ідентифікатор компанії, що організовує рейс;

- AircraftID - ідентифікатор літака;

- CityDepatrureID - ідентифікатор міста відправлення;

- CityArrivalID - ідентифікатор міста прибуття.

R3 = CITIES (CityID, CityName),

де:

- CityID - ідентифікатор міста;

- CityName - назва міста.

R4 = AIRCRAFTS (AircraftID, AircraftModel, Count1, Count2),

де:

- AircraftID - ідентифікатор літака;

- AircraftModel - модель літака;

- Count1 - загальна кількість місць першого класу;

- Count2 - загальна кількість місць другого класу.

R5 = COMPANIES (ID, COMPANY_NAME, COMPANY_PHONE),

де:

- CompanyID - ідентифікатор компанії;

- CompanyName-назва авіакомпанії.

R6 = USERS (ID, USER_NAME),

де

- UserID - ідентифікатор користувача;

- UserLogin - ім'я користувача;

- Password - пароль користувача;

- Email - e-mail користувача.

Дана схема відносин знаходиться в 1 НФ, так всі вхідні в неї атрибути є атомарними (неподільними). Більш того, дана схема знаходиться у НФ Бойса-Кодда, так як вона знаходиться в 1 НФ і ніякої атрибут не залежить транзитивній ні від одного ключа.

Даталогіческая схема бази даних наведена на малюнку 2. На ньому, крім відносин і зв'язків між ними показані також відповідні атрибутам типи даних.

Малюнок 2 - Даталогіческая модель бази даних

3. Реалізація запитів

У системі реалізовані наступні види запитів:

3.1 Отримання інформації про рейси

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

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

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

Ця функція повертає тривалість польоту у вигляді рядка з 5 символів у форматі hh: mm.

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

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

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

Для отримання інформації про рейси було створено уявлення FlightView:

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

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

3.2 Пошук рейсів за певними критеріями

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

Критеріями пошуку є:

- Пункт призначення;

- Дата (критерій - рівність) і час вильоту (критерій - до / після включно);

- Дата (критерій - рівність) і час прибуття (критерій - до / після включно).

Жоден з критеріїв не є обов'язковим для завдання при виконанні пошуку. Всі критерії опційні. Якщо жоден з критеріїв пошуку не заповнений, то виводиться повний список рейсів.

При завданні критерію «дата», час вильоту / прибуття може бути відсутнім. У разі відсутності дати, але вказівки часу видається помилка.

Ці критерії враховуються шляхом додавання необхідних умов до подання FlightView в секцію WHERE. Формування і виконання даного запиту відбувається в шарі доступу до даних методом SearchFlights ().

3.3 Отримання списку замовлень

Система надає можливість перегляду замовлень користувача на обраний рейс. У цьому випадку запит здійснюється за ідентифікатором користувача та рейсу.

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

3.4 Замовлення квитків на обраний рейс

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

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

3.5 Видалення замовлення

Користувач може видалити своє замовлення. Адміністратор має можливість видалити будь-який обраний замовлення. Квитки, заброньовані даними замовленням, переходять у розряд вільних.

Видалення замовлення здійснюється за допомогою збереженої процедури Delete_Order:

CREATE PROCEDURE [dbo]. [Delete_Order]

@ OrderID int

BEGIN

DELETE FROM Orders WHERE [OrderID] = @ orderID

END

3.6 Додавання рейсу

Додавання рейсу здійснюється збереженої процедурою Insert_FlightString:

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

BEGIN

INSERT INTO [Flights]

([DateDeparture], [DateArrival],

[Price1], [Price2],

[CompanyID], [AircraftID],

[CityDepartureID], [CityArrivalID])

VALUES (

@ DateDeparture, @ dateArrival,

@ Price1, @ price2,

@ Company, @ aircraft,

@ CityDeparture, @ cityArrival)

END

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

3.7 Редагування рейсу

Оновлення інформації про рейс забезпечується збереженої процедурою Update_FlightString:

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

BEGIN

UPDATE Flights SET

dateDeparture = @ dateDeparture,

dateArrival = @ dateArrival,

price1 = @ price1,

price2 = @ price2,

cityDepartureID = @ cityDepartureID,

cityArrivalID = @ cityArrivalID,

companyID = @ companyID,

aircraftID = @ aircraftID

WHERE FlightID = @ FlightID

END

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

3.8 Видалення рейсу

При видаленні рейсу спрацьовує тригер TR_Flights_Delete:

CREATE TRIGGER [TR_Flights_Delete] ON [dbo]. [Flights] INSTEAD OF DELETE

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

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

3.8 Додавання користувача

При додаванні користувача застосовується збережена процедура Insert_User:

CREATE PROCEDURE [dbo]. [Insert_User]

@ Login nvarchar (20),

@ Password nvarchar (20),

@ Email nvarchar (50)

BEGIN

INSERT Users

(UserLogin, Password, Email)

VALUES

(@ Login, password @, @ email)

END

3.9 Видалення користувача

Видалення користувача здійснюється збереженої процедурою Delete_User:

CREATE PROCEDURE [dbo]. [Delete_User]

@ UserID int

BEGIN

DELETE FROM Orders WHERE [UserID] = @ UserID

DELETE FROM Users WHERE [UserID] = @ UserID

END

Так як існує обмеження FK_Orders_Users, то перед видаленням користувача необхідно видалити всі зроблені їм замовлення.

4. Опис роботи програми

До роботи з системою допускаються тільки зареєстровані користувачі.

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

Рисунок 3 - Форма аутентифікації

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

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

Малюнок 4 - Сторінка Flights інтерфейсу адміністратора

Малюнок 5 - Сторінка Flights користувальницького інтерфейсу

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

На параметри пошуку накладається одне обмеження: час вильоту або прибуття не може бути вказано без відповідної дати. При порушенні цієї умови з'являється повідомлення про помилку (Рисунок 6).

Малюнок 6 - Повідомлення про помилку

Вибравши рейс, користувач може зробити замовлення на деяку кількість квитків першого і / або другого класу. Для цього йому необхідно на сторінці Orders вказати потрібну кількість квитків та номер кредитної карти. Інформацію про заявку неможливо буде зберегти, поки він не вкаже кількість квитків, що не перевищує кількість вільних місць заданого класу і коректний номер кредитної картки (16 цифр). У разі неправильного заповнення полів система виводить відповідні повідомлення. (Малюнок 7) Ці написи залишаються видимими до тих пір, поки помилка не буде виправлена.

Малюнок 7 - Сторінка Orders

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

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

Рисунок 8 - Видалення користувача за допомогою інтерфейсу сторінки Users

Крім цього адміністратор може додавати рейси - на сторінці AddingFlight (Малюнок 9) і редагувати інформацію про вибраний рейсу на сторінці EditFlight (Малюнок 10). На цих обов'язково заповнення всіх полів. Також перевіряється, щоб: не збігалися зазначені місто вильоту та прибуття; значення, введені в поля ціни за квитки були позитивними числами; дата прибуття була б більше дати вильоту. Якщо хоча б одна з цих умов порушується, виводиться повідомлення про помилку. У разі коректного введення інформація зберігається в базі даних і система повідомляє користувачеві про успішно виконану операцію.

Рисунок 9 - Сторінка додавання рейсу

Рисунок 10 - Сторінка редагування рейсу

Висновок

У рамках даної курсової роботи була розроблена система бронювання авіаквитків: спроектована база даних для MS SQL Server 2005, а так само реалізовані шар доступу до даних та WEB-інтерфейс. При проектуванні бази даних акцент робився на серверну частину. На стороні сервера були застосовані індекси, збережені процедури, функції, тригери, представлення. Клієнтська частина була реалізована за допомогою мови С # на платформе.NET. Зокрема: доступ до даних був забезпечений провайдером ADO.NET, а WEB-інтерфейс побудований за допомогою ASP.NET. Додатково при реалізації WEB-вистави були використані HTML, CSS, JavaScript, AJAX.

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

Список використаних джерел

Жилінський А.А. Самовчитель Microsoft SQL Server 2005 - СПб.: БХВ-Петербург, 2007. - 224 с.
Ріхтер ДЖ. CLR via C #. Програмування на платформе.NET Framework 2.0 мовою С #. Майстер-клас. / Пер. з англ. - М.: Видавництво «Російська редакція», СПб.; Пітер, 2007. - 656 с.
Експозіто Д. Microsoft ASP.NET 2.0. Базовий курс. Майстер-клас / Пер. з англ. - М. Видавництво «Російська редакція», СПб.; Пітер, 2007. - 688 с.
Експозіто Д. Microsoft ASP.NET 2.0. Поглиблене вивчення / Пер. з англ. - М. Видавництво «Російська редакція», СПб.; Пітер, 2007. - 592 с.
Microsoft Corporation. Проектування і реалізація баз даних Microsoft SQL Server 2000. Навчальний курс MCAD / MSCE, MCDMA / Пер. з англ. - 2-е вид., Испр. - М.: Видавничо-торговий дім «Російська редакція», 2003. - 512с.

Додаток А

Скрипт для створення бази даних

CREATE DATABASE [Group0703b]

USE [Group0703b]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID (N '[dbo]. [Companies]') AND type in (N'U '))

BEGIN

CREATE TABLE [dbo]. [Companies] (

[CompanyID] [int] IDENTITY (1,1) NOT NULL,

[CompanyName] [nvarchar] (40) NULL,

CONSTRAINT [PK_Companies] PRIMARY KEY CLUSTERED

(

[CompanyID] ASC

) WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

END

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

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

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

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

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

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

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

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

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

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

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]

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]

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]

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]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

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

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 '

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

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

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]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

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)

BEGIN

INSERT Users

(UserLogin, Password, Email)

VALUES

(@ Login, password @, @ email)

END

;

END

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

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

BEGIN

DELETE FROM Users WHERE [UserID] = @ UserID

END

;

END

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

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

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

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

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

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

BEGIN

INSERT INTO [Flights]

([DateDeparture], [DateArrival],

[Price1], [Price2],

[CompanyID], [AircraftID],

[CityDepartureID], [CityArrivalID])

VALUES (

@ DateDeparture, @ dateArrival,

@ Price1, @ price2,

@ Company, @ aircraft,

@ CityDeparture, @ cityArrival)

END

;

END

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

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

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

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

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

BEGIN

DELETE FROM Orders WHERE [OrderID] = @ orderID

END

;

END

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

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

BEGIN

DELETE FROM Orders WHERE [UserID] = @ UserID

END

;

END

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

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 '

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])

ALTER TABLE [dbo]. [Flights] CHECK CONSTRAINT [FK_Flights_Aircrafts]

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])

ALTER TABLE [dbo]. [Flights] CHECK CONSTRAINT [FK_Flights_Cities_Arrival]

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])

ALTER TABLE [dbo]. [Flights] CHECK CONSTRAINT [FK_Flights_Cities_Departure]

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])

ALTER TABLE [dbo]. [Flights] CHECK CONSTRAINT [FK_Flights_Companies]

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])

ALTER TABLE [dbo]. [Orders] CHECK CONSTRAINT [FK_Orders_Flights]

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])

ALTER TABLE [dbo]. [Orders] CHECK CONSTRAINT [FK_Orders_Users]