Туристическое агентство, SQL Server. Курсовой проект по дисциплине Современные технологии управления базами данных разработка распределенной базы данных турфирма на основе ms sql server
 Скачать 0.7 Mb.
|
|
КУРСОВОЙ ПРОЕКТ по дисциплине Современные технологии управления базами данных РАЗРАБОТКА РАСПРЕДЕЛЕННОЙ БАЗЫ ДАННЫХ «ТУРФИРМА» НА ОСНОВЕ MS SQL SERVER Выполнил: ст. гр. ИФСТ-31 Дорожкин В. А. 2013 2 СОДЕРЖАНИЕ Введение 3 1 Разработка концептуальной модели базы данных 5 2 Логическое проектирование 10 3 Физическое проектирование 14 3.1 Создание базы данных и таблиц 14 3.2 Добавление внешних ключей 15 3.3 Заполнение таблиц 16 3.4 Создание запросов 23 3.5 Создание представлений 26 3.6 Создание процедур и триггеров 27 Заключение 28 Список литературы 29 Приложение А 30 3 ВВЕДЕНИЕ С распространением сетей и доступных небольших компьютерных систем практически все организации, за исключением мелких компаний, больше не используют централизованное хранение информации в одной базе данных. Важные данные, к которым необходим доступ со стороны всех пользователей, обычно разбросаны среди множества баз данных, расположенных в различных физических местах хранения. Чтобы объединить возможности баз данных и позволить всем пользователям обращаться к хранимой информации, можно применить средства MS SQL Server, называемые распределенной базой данных и тиражированием данных. Распределенная база данных (distributed database) - это группа баз данных, которая выглядит для пользователей и приложений как одна база данных. В большинстве случаев базы данных, составляющие распределенную базу данных, расположены на отдельных компьютерах, взаимодействующих по сети. После того как система распределенной базы данных MS SQL Server сконфигурирована, все данные в этой системе становятся доступными приложениям, как если бы они находились в одной логической базе данных. Каждый сервер базы данных в системе распределенной базы данных управляет доступом к своей локальной базе данных — за управление системой в целом не отвечает ни один сервер. Однако все серверы системы должны взаимодействовать друг с другом, чтобы обеспечить согласованность и точность данных, во всей системе. 4 1 РАЗРАБОТКА КОНЦЕПТУАЛЬНОЙ МОДЕЛИ БАЗЫ ДАННЫХ Основным назначением информационных систем является оперативное обеспечение пользователя информацией о внешнем мире путем реализации вопросно-ответного отношения, что позволяют выделить для информационной системы определенный его фрагмент - предметную область, - который будет воплощен в автоматизированной информационной системе. Информация о внешнем мире представляется в информационной системе в форме данных. Это ограничивает возможности смысловой интерпретации информации и конкретизирует семантику ее представления в информационной системе. Совокупность этих выделенных для информационной системы данных, связей между ними и операций над ними образует информационную и функциональную модели предметной области, описывающие ее состояние с определенной точностью. Информационная и функциональная модели предметной области создаются на этапе анализа требований к базе данных и не содержат предположений о технологии реализации базы данных. Они строятся независимо от выбираемой модели данных (сетевой, иерархической, реляционной, объектно- ориентированной, многомерной и т.д.), поддерживаемой СУБД, модели вычислений, программно-аппаратной платформы для базы данных. Информационная и функциональная модели предметной области являются входными данными для процесса проектирования базы данных. Поэтому проектировщик должен уметь правильно интерпретировать их в ходе решения своих проектных задач. Целью проектирования распределенной базы данных «Турфирма» является создание информационной системы для работы туристической фирмы. Пользователями данной распределённой базы данных будут являться сотрудники туристической фирмы, клиенты и потенциальные потребители услуг турфирмы. В базе данных должна быть отражена информация о клиентах, продажах, доступных турах, а также сведения об отелях и авиарейсах. Поэтому входными 5 данными для проектирования базы данных должны быть следующие характеристики: • информация о клиентах: ФИО, дата рождения, адрес, телефон; • информация о продажах туристической фирмы: название тура, клиент, дата продажи и стоимость; • информация о турах: дата начала и конца, тип тура, тип питания, название отеля, авиарейсы; • информация об отеле: название отеля, место расположения (страна и город), количество звезд и цена за ночь проживания. База данных «Турфирма» должна выполнять следующие задачи: • предоставлять возможность для внесения, изменения и удаления информации о клиентах, турах, отелях и авиарейсах сотрудникам турфирмы; • предоставлять информацию о доступных предложениях клиентам и потенциальным потребителям услуг туристической фирмы; В базе данных «Турфирма» будут формироваться следующие таблицы: • клиенты; • продажи; • туры; • отели; • города; • страны; • тип тура; • тип питания; • тип размещения; • авиарейсы. Таблица «Клиенты» будет содержать следующие поля: • код; • фамилия; • имя; 6 • отчество; • дата рождения; • адрес; • телефон. Таблица «Продажи» будет содержать следующие поля: • код; • клиент; • дата продажи; • цена; • тур. Таблица «Туры» будет содержать следующие поля: • код; • дата начала тура; • дата конца тура; • тип тура; • тип питания; • отель; • рейс вылета; • рейс прилета. Таблица «Отели» будет содержать следующие поля: • код; • название; • количество звезд; • город расположения; • тип размещения; • цена за одну ночь. Таблица «Города» будет содержать следующие поля: • код; 7 • название города; • страна. Таблица «Страны» будет содержать следующие поля: • код; • название страны. Таблица «Тип тура» будет содержать следующие поля: • код; • тип тура. Таблица «Тип питания» будет содержать следующие поля: • код; • тип питания. Таблица «Тип размещения» будет содержать следующие поля: • код; • тип размещения. Таблица «Авиарейсы» будет содержать следующие поля: • код; • Город вылета; • Дата вылета; • Время вылета; • Город прилета; • Дата прилета; • Время прилета. 8 Рисунок 1 – ER-диаграмма базы данных «Турфирма» 9 2 ЛОГИЧЕСКОЕ ПРОЕКТИРОВАНИЕ База данных должна удовлетворять следующим эксплуатационным требованиям: • База данных должна удовлетворять актуальным информационным потребностям; • База данных должна удовлетворять актуальным требованиям за приемлемое время, т. е. заданным требованиям производительности; • База данных должна удовлетворять выявленным и вновь возникающим требованиям конечных пользователей; • База данных должна легко расширяться при реорганизации и расширении предметной области; • База данных должна легко изменяться при изменении программной и аппаратной среды; • Загруженные в базу данных корректные данные должны оставаться корректными; • Данные до включения в базу данных должны проверяться на достоверность; • Доступ к данным, размещаемым в базе данных, должны иметь только лица с соответствующими полномочиями. Таблица 1 - Таблица атрибутов для сущности «Клиенты» Поле Ключи Размер Формат Свойства Код Первичный ключ 11 int Not null Фамилия Нет 255 varchar Not null Имя Нет 255 varchar Not null Отчество Нет 255 varchar Not null Дата рождения нет 23 date Not null 10 Адрес Нет 255 varchar Not null Телефон Нет 11 int Not null Таблица 2 - Таблица атрибутов для сущности «Продажи» Поле Ключи Размер Формат Свойства Код Первичный ключ 11 int Not null Клиент Внешний ключ 11 int Not null Дата Нет 23 date Not null Цена Нет 11 int Not null Тур Внешний ключ 11 int Not null Таблица 3 - Таблица атрибутов для сущности «Туры» Поле Ключи Размер Формат Свойства Код Первичный ключ 11 int Not null Дата начала Нет 23 date Not null Дата конца Нет 23 date Not null Тип тура Внешний ключ 11 int Not null Тип питания Внешний ключ 11 int Not null Отель Внешний ключ 11 int Not null Рейс вылета Внешний ключ 11 int Not null Рейс прилета Внешний ключ 11 int Not null Таблица 4 - Таблица атрибутов для сущности «Отели» Поле Ключи Размер Формат Свойства Код Первичный ключ 11 int Not null Название Нет 255 varchar Not null Звезды Нет 11 int Not null Город Внешний ключ 11 int Not null Тип Внешний ключ 11 int Not null 11 размещени Цена Нет 11 int Not null Таблица 5 - Таблица атрибутов для сущности «Города» Поле Ключи Размер Формат Свойства Код Первичный ключ 11 int Not null Название Нет 255 varchar Not null Страна Внешний ключ 11 int Not null Таблица 6 - Таблица атрибутов для сущности «Страны» Поле Ключи Размер Формат Свойства Код Первичный ключ 11 int Not null Название Нет 255 varchar Not null Таблица 7 - Таблица атрибутов для сущности «Тип тура» Поле Ключи Размер Формат Свойства Код Первичный ключ 11 int Not null Тип тура Нет 255 varchar Not null Таблица 8 - Таблица атрибутов для сущности «Тип размещения» Поле Ключи Размер Формат Свойства Код Первичный ключ 11 int Not null Тип размещения Нет 255 varchar Not null Таблица 9 - Таблица атрибутов для сущности «Тип питания» Поле Ключи Размер Формат Свойства 12 Код Первичный ключ 11 int Not null Тип питания Нет 255 varchar Not null Таблица 10 - Таблица атрибутов для сущности «Авиарейсы» Поле Ключи Размер Формат Свойства Код Первичный ключ 11 int Not null Город вылета Нет 255 varchar Not null Дата вылета Нет 23 date Not null Время вылета Нет 11 int Not null Город прилета Нет 255 varchar Not null Дата прилета Нет 23 date Not null Время прилета Нет 11 int Not null Для реализации базы данных был выбран MS SQL Server. MS SQL Server – это система управления распределенными базами данных, разработанная корпорацией Microsoft. Основной используемый язык запросов — Transact-SQL, создан совместно Microsoft и Sybase. Transact-SQL является реализацией стандарта ANSI/ISO по структурированному языку запросов (SQL) с расширениями. MS SQL Server позволяет обеспечить высокопроизводительную работу с большими объемами данных, реализовать целостность базы данных и её безопасность, а так же разграничение прав доступа пользователей. 13 3 ФИЗИЧЕСКОЕ ПРОЕКТИРОВАНИЕ 3. 1 Создание базы данных и таблиц Ниже представлен код создания базы данных на языке SQL: create database dorozhkin on ( name = 'dorozhkin', filename = 'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\dorozhkin.mdf', size = 10 mb) log on ( name = 'dorozhkin_log', filename = 'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\dorozhkin_log.ldf', size = 3 mb) Код создания таблицы «Туры»: create table tour ( id int primary key, type_tour int not null, date_begin date not null, date_end date not null, type_pitania int not null, hotel int not null, reys int not null) Код создания таблицы «Тип питания»: create table type_pitania ( id int primary key, type varchar (30) not null) Код создания таблицы «Тип тура» create table type_tour ( id int primary key, type varchar (30) not null) Код создания таблицы «Авиарейсы»: create table reys ( id int primary key, gorod_vyleta varchar (30) not null, date_vyleta date not null, time_vyleta int not null, gorod_prileta varchar (30) not null, date_prileta date not null, time_prileta int not null) Код создания таблицы «Отели»: create table hotel ( id int primary key, name varchar (30) not null, stars int not null, gorod int not null, type_razmeshcheniya int not null, price float not null) Код создания таблицы «Тип размещения»: create table type_razmeshcheniya ( id int primary key, type_razm varchar (30)) 14 Код создания таблицы «Города»: create table gorog ( id int primary key, name varchar (30) not null, land int not null) Код создания таблицы «Страны»: create table land ( id int primary key, name varchar (30) not null) Код создания таблицы «Клиенты»: create table clients ( id int primary key, f varchar (30) not null, i varchar (30) not null, o varchar (30) not null, date_rozd date not null, adres varchar (30) not null, telefon varchar (30) not null) Код создания таблицы «Продажи»: create table prodazha ( id int primary key, client int not null, date date not null, price float not null) 3. 2 Добавление внешних ключей Ниже представлен код SQL-запросов на добавление внешних ключей в таблицы. Добавление внешних ключей в таблицу «Продажа»: alter table prodazha add constraint f_client foreign key (client) references clients (id) alter table prodazha add tour int not null alter table prodazha add constraint f_tour foreign key (tour) references tour (id) Добавление внешних ключе в таблицу «Города»: alter table gorog add constraint f_land foreign key (land) references land (id) Добавление внешних ключей в таблицу «Отель»: alter table hotel add constraint f_gorod foreign key (gorod) references gorog (id) alter table hotel add constraint f_type_razm foreign key (type_razmeshcheniya) references type_razmeshcheniya (id) 15 Добавление внешних ключей в таблицу «Тур»: alter table tour add constraint f_hotel foreign key (hotel) references hotel (id) alter table tour add constraint f_type_pitaniya foreign key (type_pitania) references type_pitania (id) alter table tour add constraint f_type_tour foreign key (type_tour) references type_tour (id) alter table tour add reys_ein int not null alter table tour add reys_aus int not null alter table tour add constraint f_reys_ein foreign key (reys_ein) references reys (id) alter table tour add constraint f_reys_aus foreign key (reys_aus) references reys (id) 3. 3 Заполнение таблиц Заполнение таблицы «Страны»: insert into land values (1, 'Австралия' ) insert into land values (2, 'Великобритания' ) insert into land values (3, 'Германия' ) insert into land values (4, 'Греция' ) insert into land values (5, 'Израиль' ) insert into land values (6, 'Испания' ) insert into land values (7, 'Италия' ) insert into land values (8, 'Монако' ) insert into land values (9, 'США' ) insert into land values (10, 'Таиланд' ) insert into land values (11, 'Турция' ) insert into land values (12, 'Франция' ) insert into land values (13, 'Чехия' ) insert into land values (14, 'Швейцария' ) 16 insert into land values (15, 'Япония' ) Последующие таблицы были заполнены с помощью конструктора. Рисунок 1 – Таблица «Страны» Рисунок 2 – Таблица «Продажи» 17 Рисунок 3- Таблица «Города» Рисунок 4 – Таблица «Туры» 18 Рисунок 5 – Таблица «Клиенты» Рисунок 6 – Таблица «Отели» 19 Рисунок 7 – Таблица «Рейсы» Рисунок 8 – Таблица «Тип питания» Рисунок 9 – Таблица «Тип размещения» 20 Рисунок 10 – Таблица «Тип тура» 21 Рисунок 11 – Диаграмма баз данных clients id f i o date_rozd adres telefon gorog id name land hotel id name stars gorod type_razmeshcheniya price f_gorod land id name f_land prodazha id client date price tour f_client reys id gorod_vyleta date_vyleta time_vyleta gorod_prileta date_prileta time_prileta tour id type_tour date_begin date_end type_pitania hotel reys_ein reys_aus f_hotel f_tour f_reys_ein f_reys_aus type_pitania id type f_type_pitaniya type_razmeshcheniya id type_razm f_type_razm type_tour id type f_type_tour 22 3. 4 Создание запросов Запрос 1 на выборку городов Великобритании с использованием соединения: select gorog.name from gorog inner join land on gorog.land=land.id where land.name='Великобритания' Рисунок 12 – Результат запроса 1 Запрос 2 на выборку всех отелей Великобритании с использованием соединения и сортировка по количеству звезд, а так же по цене проживания за ночь: select hotel.name, stars, price from hotel inner join gorog inner join land on gorog.land=land.id on gorod=gorog.id where land.name='Великобритания' order by stars asc, price asc Рисунок 13 – Результат запроса 2 Запрос 3, подсчитывающий среднюю стоимость ночи проживания в отеле в каждой из стран: 23 select land.name, AVG(hotel.price) as [Средняя стоимость] from hotel inner join gorog inner join land on gorog.land=land.id on gorod=gorog.id group by (land.name) Рисунок 14 – Результат запроса 3 Запрос 4, отображающий отели, стоимость проживания в которых ниже либо равна средней стоимости проживания в отелях за одну ночь с сортировкой по названию страны, цене и количеству звезд: select hotel.name as [Отель], stars as [Звезды], gorog.name as [Город], land.name as [Страна], price as [Цена] from hotel inner join gorog inner join land on gorog.land=land.id on gorod=gorog.id where hotel.price <= (select AVG(hotel.price) from hotel) order by land.name, price asc, stars asc Рисунок 15 – Результат запроса 4 24 Запрос 5, отображающий продажи клиентам и подсчитывающий общую сумму продаж каждому из клиентов: select f, prodazha.date, prodazha.price, type_tour.type from clients inner join prodazha inner join tour inner join type_tour on tour.type_tour=type_tour.id on prodazha.tour=tour.id on prodazha.client=clients.id order by f compute sum(price) by f Рисунок 16 – Результат запроса 5 Запрос 6 на нахождение разницы суммы продаж: select ((select MAX(price)from prodazha) - (select MIN(price) from prodazha)) as 'Разница' Рисунок 17 – Результат запроса 6 Запрос 7, отображающий продажи текущего месяца: select type_tour.type, prodazha.date from prodazha inner join tour inner join type_tour on tour.type_tour=type_tour.id on prodazha.tour=tour.id where DATEPART(month, prodazha.date)=DATEPART(month, getdate()) 25 Рисунок 18 – Результат запроса 7 3. 5 Создание представлений Код создания представления: CREATE VIEW hotel1 as select hotel.name as [Отель], stars as [Звезды], gorog.name as [Город], land.name as [Страна], price as [Цена] from hotel inner join gorog inner join land on gorog.land=land.id on gorod=gorog.id where hotel.price <= (select AVG(hotel.price) from hotel) Рисунок 19 – Представление «Отели» 26 3. 6 Создание процедур и триггеров Код создания процедуры добавления данных о новом клиенте в таблицу «Клиент»: create procedure clients1 (@f varchar(30), @i varchar(30), @o varchar(30), @date date, @adres varchar(30), @telefon varchar(30)) as declare @id int set @id = (select MAX(id) from clients) set @id=@id+1 insert into clients values ( @id, @f, @i, @o, @date, @adres, @telefon) execute clients1 'Иванов', 'Иван', 'Иванович', '1987-10-25', 'Балаково', '+79283854875' Код создания триггера удаления сведений о стране и связанных с ней записях: create trigger landdelete on land for delete as begin declare @a int set @a = (select id from deleted) delete from gorog where gorog.land=@a end 3. 7 Создание транзакции Код создания транзакции на добавление данных о новом городе и отеле: begin tran insert into gorog values ('57', 'Balakovo', '6') insert into hotel values ('169', 'Balakovo', '4', '57', '2', '4230') if (@@ERROR=0) begin commit tran end 27 else begin print 'Error' rollback tran end 28 ЗАКЛЮЧЕНИЕ В ходе выполнения курсового проекта «Разработка распределенной базы данных на основе MS SQL Server» были получены навыки концептуального, логического и физического проектирования распределенных баз данных. Были изучены синтаксис и основные команды языка Т-SQL. Освоено создание базы данных, таблиц, запросов, триггеров, процедур, представлений и транзакций, а так же установка ограничений. Итогом выполнения курсового проекта стала распределенная база данных «Турфирма», которая позволяет хранить, обрабатывать и изменять информацию о клиентах, турах, продажах и авиарейсах. 29 СПИСОК ИСПОЛЬЗОВАННЫХ ИСТОЧНИКОВ 1. Виейра Р. Программирование баз данных Microsoft SQL Server 2005. Базовый курс. / Роберт Виейра. – М.: Диалектика, 2008. – 1066с. 2. Голицына О.Л. Базы данных. / О.Л. Голицина, Н.В. Максимов, И.И. Попов. – М.: Форум – Инфра-М, 2007. – 400с. 3. Грабер М. SQL. Справочное руководство. 2-е изд. / М. Грабер. – М.: Лори, 2006. – 354с. 4. Грабер М. Введение в SQL. / М. Грабер. – М.: Лори, 2010. – 238с. 5. Диго С.М. Базы данных. Проектирование и использование: учебник. / С.М. Диго. – М.: Финансы и статистика, 2005. – 592с. 6. Клайн К. SQL. Справочник. – 2-е изд. / К. Клайн, Д. Клайн, Б. Хант. – М.: Кудиц-образ, 2006. – 832с. 7. Крёнке Д. Теория и практика построения баз данных. – 9-е изд./ Д. Крёнке. – СПб.: Питер, 2005. – 864с. 8. Малыхина М.П. Базы данных: основы, проектирование, использование. / М.П. Малыхина. – СПб.: БХВ-Петербург, 2004. – 512с. 9. Пирогов В.Ю. Информационные системы и базы данных: организация и проектирование. / В.Ю. Пирогов. – СПб.: БХВ-Петербург, 2009. 528с. 10. Фленов М. Transact-SQL. / М. Фленов. – СПб.: БХВ-Петербург, 2006. – 576с. 30 ПРИЛОЖЕНИЕ А (обязательное) Информация предоставлена на цифровом носителе. |