Курсовая работа
по дисциплине: «Корпоративные информационные системы»
на тему:
Автоматизированная системабронирования авиабилетов
Содержание
Введение
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:
CREATEFUNCTION [dbo].[timeFlight]
(
@dateArrivaldatetime,
@dateDeparturedatetime
)
RETURNSchar(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.
CREATEFUNCTION [dbo].[CountEmptyPlaces1cl]
(
@flightIDint
)
RETURNSint
BEGIN
DECLARE@count int;
IFEXISTS (SELECT OrderID FROM Orders WHERE FlightID=@flightID
AND Number1cl>0)
SELECT@count=Aircrafts.Count1
-(SELECTSUM(Number1cl) FROM Orders WHERE FlightID=@flightID)
FROMFlights,Aircrafts WHERE Flights.FlightID=@flightID
ANDFlights.AircraftID=Aircrafts.AircraftID
ELSE
SELECT@count=Aircrafts.Count1
FROMFlights,Aircrafts WHERE Flights.FlightID=@flightID
ANDFlights.AircraftID=Aircrafts.AircraftID
RETURN@count
END
CREATEFUNCTION [dbo].[CountEmptyPlaces2cl]
(
@flightIDint
)
RETURNSint
BEGIN
DECLARE@count int;
IFEXISTS (SELECT OrderID FROM Orders WHERE FlightID=@flightID
ANDNumber2cl>0)
SELECT@count=Aircrafts.Count2
-(SELECTSUM(Number2cl) FROM Orders WHERE FlightID=@flightID)
FROMFlights,Aircrafts WHERE Flights.FlightID=@flightID
ANDFlights.AircraftID=Aircrafts.AircraftID
ELSE
SELECT@count=Aircrafts.Count2
FROMFlights,Aircrafts WHERE Flights.FlightID=@flightID
ANDFlights.AircraftID=Aircrafts.AircraftID
RETURN@count
END
Дляполучения информации по рейсам было создано представление FlightView:
CREATEVIEW [dbo].[FlightView] AS
SELECT
FlightID,
DateDeparture,
DateArrival,
dbo.timeFlight(DateArrival,DateDeparture)AS TimeFlight,
CityDepartureID,
CityArrivalID,
DepartureCities.CityNameAS CityDeparture,
ArrivalCities.CityNameAS CityArrival,
CompanyName,
AircraftModel,
Price1,
Price2,
dbo.CountEmptyPlaces1cl(FlightID)AS EmptyPlace1cl,
dbo.CountEmptyPlaces2cl(FlightID)AS EmptyPlace2cl
FROMdbo.Flights
INNERJOIN dbo.Companies
ONGroup0703b.dbo.Flights.CompanyID = Group0703b.dbo.Companies.CompanyID
INNERJOIN Group0703b.dbo.Aircrafts
ONGroup0703b.dbo.Flights.AircraftID = Group0703b.dbo.Aircrafts.AircraftID
LEFTOUTER JOIN Group0703b.dbo.Cities AS DepartureCities
ONGroup0703b.dbo.Flights.CityDepartureID = DepartureCities.CityID
LEFTOUTER JOIN Group0703b.dbo.Cities AS ArrivalCities
ONGroup0703b.dbo.Flights.CityArrivalID = ArrivalCities.CityID
Благодарясозданию перечисленых серверных сущностей мы оптимизируем выполнение данныхвидов запросов, тем самым получая выигрышь в производительности. Кроме тогоиспользование представление, позволяет упростить наисание клиентскогоприложения, делает запросы более компактными и наглядными.
/>3.2 Поиск рейсов по определеннымкритериям
Системапозволяет выполнять отбор рейсов с заданными параметрами.
Критериямипоиска являются:
— пункт назначения;
— дата(критерий — равенство) и время вылета (критерий – до/после включительно);
— дата(критерий — равенство) и время прибытия (критерий – до/после включительно).
Ниодин из критериев не является обязательным для задания при выполнении поиска.Все критерии опциональны. Если ни один из критериев поиска не заполнен, товыводится полный список рейсов.
Призадании критерия «дата», время вылета/прибытия может отсутствовать. В случаеотсутствия даты, но указания времени выдается ошибка.
Этикритерии учитываются путем добавления необходимых условий к представлениюFlightView в секцию WHERE. Формирование и выполнение данного запроса происходитв слое доступа к данным методом SearchFlights().
/>
3.3 Получениесписка заказов
Системапредоставляет возможность просмотра заказов пользователя на выбранный рейс. Вэтом случае запрос осуществляется по идентификатору пользователя и рейса.
Еслитекущий пользователь имеет администраторские права, то он может просмотреть какзаказы конкретного пользователя, так и все заказы на определенный рейс. В этомслучае запрос осуществляется по идентификатору рейса.
/>3.4 Заказ билетов на выбранныйрейс
Послевыбора пользователем рейса, имеется возможность заказать определенноеколичество мест выбранного класса на данный рейс. Количество заказанных билетоввводится пользователем.
Системаконтролирует, чтобы количество заказанных билетов не превышало количество местданного класса на рейсе. При количестве свободных мест равном нулю системазапрещать бронировать билеты данного класса на данный рейс.
/>
3.5Удаление заказа
Пользовательможет удалить свой заказ. Администратор имеет возможность удалить любойвыбранный заказ. Билеты, забронированные данным заказом, переходят в разрядсвободных.
Удалениезаказа осуществляется посредством хранимой процедуры Delete_Order:
CREATEPROCEDURE [dbo].[Delete_Order]
@orderIDint
AS
BEGIN
DELETEFROM Orders WHERE [OrderID]=@orderID
END
/>
3.6Добавление рейса
Добавлениерейса осуществляется хранимой процедурой Insert_FlightString:
CREATEPROCEDURE [dbo].[Insert_FlightString]
@dateDeparturedatetime,
@dateArrivaldatetime,
@price1decimal(18,0),
@price2decimal(18,0),
@companyint,
@aircraftint,
@cityDepartureint,
@cityArrivalint
AS
BEGIN
INSERTINTO [Flights]
([DateDeparture],[DateArrival],
[Price1],[Price2],
[CompanyID],[AircraftID],
[CityDepartureID],[CityArrivalID])
VALUES(
@dateDeparture,@dateArrival,
@price1,@price2,
@company,@aircraft,
@cityDeparture,@cityArrival)
END
Передпередачей в неё параметров осуществляется контроль их корректности. Вчастоности проверяется, чтобы город вылета и прибытия не совпадали. Цены забилеты должны быть положительными числами. Дата прилета должна быть больше датывылета. Эти условия проверяются на клиентской стороне.
3.7 Редактирование рейса
Обновлениеинформации о рейсе обеспечивается хранимой процедурой Update_FlightString:
CREATEPROCEDURE [dbo].[Update_FlightString]
@FlightIDint,
@dateDeparturedatetime,
@dateArrivaldatetime,
@price1decimal(18,0),
@price2decimal(18,0),
@companyIDint,
@aircraftIDint,
@cityDepartureIDint,
@cityArrivalIDint
AS
BEGIN
UPDATEFlights SET
dateDeparture= @dateDeparture,
dateArrival= @dateArrival,
price1= @price1,
price2= @price2,
cityDepartureID= @cityDepartureID,
cityArrivalID= @cityArrivalID,
companyID= @companyID,
aircraftID= @aircraftID
WHEREFlightID = @FlightID
END
Привнесении изменений в рейс, на передаваемые параметры накладываются те жеограничении, как и при добавлении рейса.
3.8Удаление рейса
Приудалении рейса срабатывает триггер TR_Flights_Delete:
CREATETRIGGER [TR_Flights_Delete] ON [dbo].[Flights] INSTEAD OF DELETE
AS
DELETEFROM Orders
WHEREOrders.FlightID= (SELECT top(1) deleted.FlightID FROM deleted)
DELETEFROM Flights
WHEREFlights.FlightID= (SELECT top(1) deleted.FlightID FROM deleted)
RETURN
Таккак в базе данных существует связь FK_Orders_Flights между таблицами Flight иOrders, то невозможно удалить рейс, пока есть хотя бы одна заявка на него.Поэтому сначала должны быть удалены все связанные заявки, а уже потом – самрейс. Эту логику и осуществляет даный триггер.
3.8 Добавление пользователя
Придобавлении пользователя применяется хранимая процедура Insert_User:
CREATEPROCEDURE [dbo].[Insert_User]
@loginnvarchar(20),
@passwordnvarchar(20),
@emailnvarchar(50)
AS
BEGIN
INSERTUsers
(UserLogin,Password, Email)
VALUES
(@login,@password, @email)
END
3.9 Удаление пользователя
Удалениепользователя осуществляется хранимой процедурой Delete_User:
CREATEPROCEDURE [dbo].[Delete_User]
@UserIDint
AS
BEGIN
DELETEFROM Orders WHERE [UserID]=@UserID
DELETEFROM 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-интерфейса.
Список использованных источников
1. Жилинский А.А. Самоучитель MicrosoftSQL Server 2005 – СПб.: БХВ-Петербург, 2007. – 224 с.
2. Рихтер ДЖ. CLR via C#. Программированиена платформе.NET Framework 2.0 на языке С#. Мастер-класс./ Пер. с англ. – М.:Издательство «Русская редакция»; СПб.; Питер, 2007. – 656 с.
3. Экспозито Д. Microsoft ASP.NET 2.0.Базовый курс. Мастер-класс/ Пер. с англ. – М. Издательство «Русская редакция»;СПб.; Питер, 2007. – 688 с.
4. Экспозито Д. Microsoft ASP.NET 2.0.Углубленное изучение/ Пер. с англ. – М. Издательство «Русская редакция»; СПб.;Питер, 2007. – 592 с.
5. Microsoft Corporation. Проектирование иреализация баз данных Microsoft SQL Server 2000. Учебный курс MCAD/MSCE, MCDMA/Пер. с англ. – 2-е изд., испр. – М.: Издательско-торговый дом «Русскаяредакция», 2003. – 512с.
Приложение А
Скриптдля создания базы данных
CREATEDATABASE [Group0703b]
GO
USE[Group0703b]
GO
SET ANSI_NULLSON
GO
SETQUOTED_IDENTIFIER ON
GO
IF NOTEXISTS (SELECT * FROM sys.objects WHERE object_id =OBJECT_ID(N'[dbo].[Companies]') AND type in (N'U'))
BEGIN
CREATETABLE [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
GO
SETANSI_NULLS ON
GO
SETQUOTED_IDENTIFIER ON
GO
IF NOTEXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Users]')AND type in (N'U'))
BEGIN
CREATETABLE [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
SETANSI_NULLS ON
GO
SETQUOTED_IDENTIFIER ON
GO
IF NOTEXISTS (SELECT * FROM sys.objects WHERE object_id =OBJECT_ID(N'[dbo].[Cities]') AND type in (N'U'))
BEGIN
CREATETABLE [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
SETANSI_NULLS ON
GO
SETQUOTED_IDENTIFIER ON
GO
IF NOTEXISTS (SELECT * FROM sys.objects WHERE object_id =OBJECT_ID(N'[dbo].[Aircrafts]') AND type in (N'U'))
BEGIN
CREATETABLE [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
SETANSI_NULLS ON
GO
SETQUOTED_IDENTIFIER ON
GO
IF NOTEXISTS (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
executedbo.sp_executesql @statement = N'CREATE FUNCTION [dbo].[timeFlight]
(
@dateArrivaldatetime,
@dateDeparturedatetime
)
RETURNSchar(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
SETANSI_NULLS ON
GO
SETQUOTED_IDENTIFIER ON
GO
IF NOTEXISTS (SELECT * FROM sys.objects WHERE object_id =OBJECT_ID(N'[dbo].[Flights]') AND type in (N'U'))
BEGIN
CREATETABLE [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 NOTEXISTS (SELECT * FROM sys.indexes WHERE object_id =OBJECT_ID(N'[dbo].[Flights]') AND name = N'IX_Fligts_AircraftID')
CREATENONCLUSTERED INDEX [IX_Fligts_AircraftID] ON [dbo].[Flights]
(
[AircraftID]ASC
)WITH(PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
IF NOTEXISTS (SELECT * FROM sys.indexes WHERE object_id =OBJECT_ID(N'[dbo].[Flights]') AND name = N'IX_Fligts_CityArrivalID')
CREATENONCLUSTERED INDEX [IX_Fligts_CityArrivalID] ON [dbo].[Flights]
(
[CityArrivalID]ASC
)WITH(PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
IF NOTEXISTS (SELECT * FROM sys.indexes WHERE object_id =OBJECT_ID(N'[dbo].[Flights]') AND name = N'IX_Fligts_CityDepartureID')
CREATENONCLUSTERED INDEX [IX_Fligts_CityDepartureID] ON [dbo].[Flights]
(
[CityDepartureID]ASC
)WITH(PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
IF NOTEXISTS (SELECT * FROM sys.indexes WHERE object_id =OBJECT_ID(N'[dbo].[Flights]') AND name = N'IX_Fligts_CompanyID')
CREATENONCLUSTERED INDEX [IX_Fligts_CompanyID] ON [dbo].[Flights]
(
[CompanyID]ASC
)WITH(PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SETANSI_NULLS ON
GO
SETQUOTED_IDENTIFIER ON
GO
IF NOTEXISTS (SELECT * FROM sys.triggers WHERE object_id =OBJECT_ID(N'[dbo].[TR_Flights_Delete]'))
EXECdbo.sp_executesql @statement = N'CREATE TRIGGER [dbo].[TR_Flights_Delete] ON[dbo].[Flights] INSTEAD OF DELETE
AS
DELETEFROM Orders
WHEREOrders.FlightID= (SELECT top(1) deleted.FlightID FROM deleted)
DELETEFROM Flights
WHEREFlights.FlightID= (SELECT top(1) deleted.FlightID FROM deleted)
RETURN'
GO
SETANSI_NULLS ON
GO
SETQUOTED_IDENTIFIER ON
GO
IF NOTEXISTS (SELECT * FROM sys.objects WHERE object_id =OBJECT_ID(N'[dbo].[Orders]') AND type in (N'U'))
BEGIN
CREATETABLE [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 NOTEXISTS (SELECT * FROM sys.indexes WHERE object_id =OBJECT_ID(N'[dbo].[Orders]') AND name = N'IX_Orders_UserID')
CREATENONCLUSTERED INDEX [IX_Orders_UserID] ON [dbo].[Orders]
(
[UserID]ASC
)WITH(PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SETANSI_NULLS ON
GO
SETQUOTED_IDENTIFIER ON
GO
IF NOTEXISTS (SELECT * FROM sys.objects WHERE object_id =OBJECT_ID(N'[dbo].[Insert_User]') AND type in (N'P', N'PC'))
BEGIN
EXECdbo.sp_executesql @statement = N'-- Вставка новой записи в таблицу Users
CREATEPROCEDURE [dbo].[Insert_User]
@loginnvarchar(20),
@passwordnvarchar(20),
@emailnvarchar(50)
AS
BEGIN
INSERTUsers
(UserLogin,Password, Email)
VALUES
(@login,@password, @email)
END
;
'
END
GO
SETANSI_NULLS ON
GO
SETQUOTED_IDENTIFIER ON
GO
IF NOTEXISTS (SELECT * FROM sys.objects WHERE object_id =OBJECT_ID(N'[dbo].[Delete_User]') AND type in (N'P', N'PC'))
BEGIN
EXECdbo.sp_executesql @statement = N'-- Удаление записи из таблицы Users
CREATEPROCEDURE [dbo].[Delete_User]
@UserIDint
AS
BEGIN
DELETEFROM Users WHERE [UserID]=@UserID
END
;
'
END
GO
SETANSI_NULLS ON
GO
SETQUOTED_IDENTIFIER ON
GO
IF NOTEXISTS (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
executedbo.sp_executesql @statement = N'CREATE FUNCTION [dbo].[CountEmptyPlaces1cl]
(
@flightIDint
)
RETURNSint
BEGIN
DECLARE@count int;
IFEXISTS (SELECT OrderID FROM Orders WHERE FlightID=@flightID AND Number1cl>0)
SELECT@count=Aircrafts.Count1
-(SELECTSUM(Number1cl) FROM Orders WHERE FlightID=@flightID)
FROMFlights,Aircrafts WHERE Flights.FlightID=@flightID
ANDFlights.AircraftID=Aircrafts.AircraftID
ELSE
SELECT@count=Aircrafts.Count1
FROMFlights,Aircrafts WHERE Flights.FlightID=@flightID
ANDFlights.AircraftID=Aircrafts.AircraftID
RETURN@count
END'
END
GO
SETANSI_NULLS ON
GO
SETQUOTED_IDENTIFIER ON
GO
IF NOTEXISTS (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
executedbo.sp_executesql @statement = N'CREATE FUNCTION [dbo].[CountEmptyPlaces2cl]
(
@flightIDint
)
RETURNSint
BEGIN
DECLARE@count int;
IFEXISTS (SELECT OrderID FROM Orders WHERE FlightID=@flightID AND Number2cl>0)
SELECT@count=Aircrafts.Count2
-(SELECTSUM(Number2cl) FROM Orders WHERE FlightID=@flightID)
FROMFlights,Aircrafts WHERE Flights.FlightID=@flightID
ANDFlights.AircraftID=Aircrafts.AircraftID
ELSE
SELECT@count=Aircrafts.Count2
FROMFlights,Aircrafts WHERE Flights.FlightID=@flightID
ANDFlights.AircraftID=Aircrafts.AircraftID
RETURN@count
END'
END
GO
SETANSI_NULLS ON
GO
SETQUOTED_IDENTIFIER ON
GO
IF NOTEXISTS (SELECT * FROM sys.objects WHERE object_id =OBJECT_ID(N'[dbo].[Insert_FlightString]') AND type in (N'P', N'PC'))
BEGIN
EXECdbo.sp_executesql @statement = N'-- Вставка новой записи в таблицу Flight
CREATEPROCEDURE [dbo].[Insert_FlightString]
@dateDeparturedatetime,
@dateArrivaldatetime,
@price1decimal(18,0),
@price2decimal(18,0),
@companyint,
@aircraftint,
@cityDepartureint,
@cityArrivalint
AS
BEGIN
INSERTINTO [Flights]
([DateDeparture],[DateArrival],
[Price1],[Price2],
[CompanyID],[AircraftID],
[CityDepartureID],[CityArrivalID])
VALUES(
@dateDeparture,@dateArrival,
@price1,@price2,
@company,@aircraft,
@cityDeparture,@cityArrival)
END
;
'
END
GO
SETANSI_NULLS ON
GO
SETQUOTED_IDENTIFIER ON
GO
IF NOTEXISTS (SELECT * FROM sys.objects WHERE object_id =OBJECT_ID(N'[dbo].[Update_FlightString]') AND type in (N'P', N'PC'))
BEGIN
EXECdbo.sp_executesql @statement = N'-- Обновление записи в таблице Flights
CREATEPROCEDURE [dbo].[Update_FlightString]
@FlightIDint,
@dateDeparturedatetime,
@dateArrivaldatetime,
@price1decimal(18,0),
@price2decimal(18,0),
@companyIDint,
@aircraftIDint,
@cityDepartureIDint,
@cityArrivalIDint
AS
BEGIN
UPDATEFlights SET
dateDeparture= @dateDeparture,
dateArrival= @dateArrival,
price1= @price1,
price2= @price2,
cityDepartureID= @cityDepartureID,
cityArrivalID= @cityArrivalID,
companyID= @companyID,
aircraftID= @aircraftID
WHEREFlightID = @FlightID
END
;
'
END
GO
SETANSI_NULLS ON
GO
SETQUOTED_IDENTIFIER ON
GO
IF NOTEXISTS (SELECT * FROM sys.objects WHERE object_id =OBJECT_ID(N'[dbo].[Delete_Order]') AND type in (N'P', N'PC'))
BEGIN
EXECdbo.sp_executesql @statement = N'-- Удаление записи из таблицы Orders поOrderID
CREATEPROCEDURE [dbo].[Delete_Order]
@orderIDint
AS
BEGIN
DELETEFROM Orders WHERE [OrderID]=@orderID
END
;
'
END
GO
SETANSI_NULLS ON
GO
SETQUOTED_IDENTIFIER ON
GO
IF NOTEXISTS (SELECT * FROM sys.objects WHERE object_id =OBJECT_ID(N'[dbo].[Delete_OrderByUserID]') AND type in (N'P', N'PC'))
BEGIN
EXECdbo.sp_executesql @statement = N'-- Удаление записей из таблицы Orders поUserID
CREATEPROCEDURE [dbo].[Delete_OrderByUserID]
@UserIDint
AS
BEGIN
DELETEFROM Orders WHERE [UserID]=@UserID
END
;
'
END
GO
SETANSI_NULLS ON
GO
SETQUOTED_IDENTIFIER ON
GO
IF NOTEXISTS (SELECT * FROM sys.views WHERE object_id =OBJECT_ID(N'[dbo].[FlightView]'))
EXECdbo.sp_executesql @statement = N'CREATE VIEW [dbo].[FlightView] AS
SELECT
FlightID,
DateDeparture,
DateArrival,
dbo.timeFlight(DateArrival,DateDeparture)AS TimeFlight,
CityDepartureID,
CityArrivalID,
DepartureCities.CityNameAS CityDeparture,
ArrivalCities.CityNameAS CityArrival,
CompanyName,
AircraftModel,
Price1,
Price2,
dbo.CountEmptyPlaces1cl(FlightID)AS EmptyPlace1cl,
dbo.CountEmptyPlaces2cl(FlightID)AS EmptyPlace2cl
FROMdbo.Flights INNER JOIN dbo.Companies
ONGroup0703b.dbo.Flights.CompanyID = Group0703b.dbo.Companies.CompanyID
INNERJOIN Group0703b.dbo.Aircrafts
ONGroup0703b.dbo.Flights.AircraftID = Group0703b.dbo.Aircrafts.AircraftID
LEFTOUTER JOIN Group0703b.dbo.Cities AS DepartureCities
ONGroup0703b.dbo.Flights.CityDepartureID = DepartureCities.CityID
LEFTOUTER JOIN Group0703b.dbo.Cities AS ArrivalCities
ONGroup0703b.dbo.Flights.CityArrivalID = ArrivalCities.CityID '
GO
IF NOTEXISTS (SELECT * FROM sys.foreign_keys WHERE object_id =OBJECT_ID(N'[dbo].[FK_Flights_Aircrafts]') AND parent_object_id =OBJECT_ID(N'[dbo].[Flights]'))
ALTERTABLE [dbo].[Flights] WITH CHECK ADD CONSTRAINT [FK_Flights_Aircrafts] FOREIGNKEY([AircraftID])
REFERENCES[dbo].[Aircrafts] ([AircraftID])
GO
ALTERTABLE [dbo].[Flights] CHECK CONSTRAINT [FK_Flights_Aircrafts]
GO
IF NOTEXISTS (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]'))
ALTERTABLE [dbo].[Flights] WITH CHECK ADD CONSTRAINT [FK_Flights_Cities_Arrival]FOREIGN KEY([CityArrivalID])
REFERENCES[dbo].[Cities] ([CityID])
GO
ALTERTABLE [dbo].[Flights] CHECK CONSTRAINT [FK_Flights_Cities_Arrival]
GO
IF NOTEXISTS (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]'))
ALTERTABLE [dbo].[Flights] WITH CHECK ADD CONSTRAINT [FK_Flights_Cities_Departure]FOREIGN KEY([CityDepartureID])
REFERENCES[dbo].[Cities] ([CityID])
GO
ALTERTABLE [dbo].[Flights] CHECK CONSTRAINT [FK_Flights_Cities_Departure]
GO
IF NOTEXISTS (SELECT * FROM sys.foreign_keys WHERE object_id =OBJECT_ID(N'[dbo].[FK_Flights_Companies]') AND parent_object_id =OBJECT_ID(N'[dbo].[Flights]'))
ALTERTABLE [dbo].[Flights] WITH CHECK ADD CONSTRAINT [FK_Flights_Companies] FOREIGNKEY([CompanyID])
REFERENCES[dbo].[Companies] ([CompanyID])
GO
ALTERTABLE [dbo].[Flights] CHECK CONSTRAINT [FK_Flights_Companies]
GO
IF NOTEXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Orders_Flights]')AND parent_object_id = OBJECT_ID(N'[dbo].[Orders]'))
ALTERTABLE [dbo].[Orders] WITH CHECK ADD CONSTRAINT [FK_Orders_Flights] FOREIGNKEY([FlightID])
REFERENCES[dbo].[Flights] ([FlightID])
GO
ALTERTABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Flights]
GO
IF NOTEXISTS (SELECT * FROM sys.foreign_keys WHERE object_id =OBJECT_ID(N'[dbo].[FK_Orders_Users]') AND parent_object_id =OBJECT_ID(N'[dbo].[Orders]'))
ALTERTABLE [dbo].[Orders] WITH CHECK ADD CONSTRAINT [FK_Orders_Users] FOREIGNKEY([UserID])
REFERENCES[dbo].[Users] ([UserID])
GO
ALTERTABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Users]