VI Международная студенческая научная конференция Студенческий научный форум - 2014

ВВЕДЕНИЕ

С распространением сетей и доступных небольших компьютерных систем практически все организации, за исключением мелких компаний, больше не используют централизованное хранение информации в одной базе данных. Важные данные, к которым необходим доступ со стороны всех пользователей, обычно разбросаны среди множества баз данных, расположенных в различных физических местах хранения. Чтобы объединить возможности баз данных и позволить всем пользователям обращаться к хранимой информации, можно применить средства MS SQL Server, называемые распределенной базой данных и тиражированием данных.

Распределенная база данных (distributed database) - это группа баз данных, которая выглядит для пользователей и приложений как одна база данных. В большинстве случаев базы данных, составляющие распределенную базу данных, расположены на отдельных компьютерах, взаимодействующих по сети. После того как система распределенной базы данных MS SQL Server сконфигурирована, все данные в этой системе становятся доступными приложениям, как если бы они находились в одной логической базе данных.

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

1 РАЗРАБОТКА КОНЦЕПТУАЛЬНОЙ МОДЕЛИ БАЗЫ ДАННЫХ

Основным назначением информационных систем является оперативное обеспечение пользователя информацией о внешнем мире путем реализации вопросно-ответного отношения, что позволяют выделить для информационной системы определенный его фрагмент - предметную область, - который будет воплощен в автоматизированной информационной системе. Информация о внешнем мире представляется в информационной системе в форме данных. Это ограничивает возможности смысловой интерпретации информации и конкретизирует семантику ее представления в информационной системе. Совокупность этих выделенных для информационной системы данных, связей между ними и операций над ними образует информационную и функциональную модели предметной области, описывающие ее состояние с определенной точностью.

Информационная и функциональная модели предметной области создаются на этапе анализа требований к базе данных и не содержат предположений о технологии реализации базы данных. Они строятся независимо от выбираемой модели данных (сетевой, иерархической, реляционной, объектно-ориентированной, многомерной и т.д.), поддерживаемой СУБД, модели вычислений, программно-аппаратной платформы для базы данных. Информационная и функциональная модели предметной области являются входными данными для процесса проектирования базы данных. Поэтому проектировщик должен уметь правильно интерпретировать их в ходе решения своих проектных задач.

Целью проектирования распределенной базы данных «Турфирма» является создание информационной системы для работы туристической фирмы. Пользователями данной распределённой базы данных будут являться сотрудники туристической фирмы, клиенты и потенциальные потребители услуг турфирмы. В базе данных должна быть отражена информация о клиентах, продажах, доступных турах, а также сведения об отелях и авиарейсах. Поэтому входными данными для проектирования базы данных должны быть следующие характеристики:

• информация о клиентах: ФИО, дата рождения, адрес, телефон;

• информация о продажах туристической фирмы: название тура, клиент, дата продажи и стоимость;

• информация о турах: дата начала и конца, тип тура, тип питания, название отеля, авиарейсы;

• информация об отеле: название отеля, место расположения (страна и город), количество звезд и цена за ночь проживания.

База данных «Турфирма» должна выполнять следующие задачи:

• предоставлять возможность для внесения, изменения и удаления информации о клиентах, турах, отелях и авиарейсах сотрудникам турфирмы;

• предоставлять информацию о доступных предложениях клиентам и потенциальным потребителям услуг туристической фирмы;

В базе данных «Турфирма» будут формироваться следующие таблицы:

• клиенты;

• продажи;

• туры;

• отели;

• города;

• страны;

• тип тура;

• тип питания;

• тип размещения;

• авиарейсы.

Таблица «Клиенты» будет содержать следующие поля:

• код;

• фамилия;

• имя;

• отчество;

• дата рождения;

• адрес;

• телефон.

Таблица «Продажи» будет содержать следующие поля:

• код;

• клиент;

• дата продажи;

• цена;

• тур.

Таблица «Туры» будет содержать следующие поля:

• код;

• дата начала тура;

• дата конца тура;

• тип тура;

• тип питания;

• отель;

• рейс вылета;

• рейс прилета.

Таблица «Отели» будет содержать следующие поля:

• код;

• название;

• количество звезд;

• город расположения;

• тип размещения;

• цена за одну ночь.

Таблица «Города» будет содержать следующие поля:

• код;

• название города;

• страна.

Таблица «Страны» будет содержать следующие поля:

• код;

• название страны.

Таблица «Тип тура» будет содержать следующие поля:

• код;

• тип тура.

Таблица «Тип питания» будет содержать следующие поля:

• код;

• тип питания.

Таблица «Тип размещения» будет содержать следующие поля:

• код;

• тип размещения.

Таблица «Авиарейсы» будет содержать следующие поля:

• код;

• Город вылета;

• Дата вылета;

• Время вылета;

• Город прилета;

• Дата прилета;

• Время прилета.

Рисунок 1 – ER-диаграмма базы данных «Турфирма»

2 ЛОГИЧЕСКОЕ ПРОЕКТИРОВАНИЕ

База данных должна удовлетворять следующим эксплуатационным требованиям:

• База данных должна удовлетворять актуальным информационным потребностям;

• База данных должна удовлетворять актуальным требованиям за приемлемое время, т. е. заданным требованиям производительности;

• База данных должна удовлетворять выявленным и вновь возникающим требованиям конечных пользователей;

• База данных должна легко расширяться при реорганизации и расширении предметной области;

• База данных должна легко изменяться при изменении программной и аппаратной среды;

• Загруженные в базу данных корректные данные должны оставаться корректными;

• Данные до включения в базу данных должны проверяться на достоверность;

• Доступ к данным, размещаемым в базе данных, должны иметь только лица с соответствующими полномочиями.

Таблица 1 - Таблица атрибутов для сущности «Клиенты»

Поле	Ключи	Размер	Формат	Свойства
Код	Первичный ключ	11	int	Not null
Фамилия	Нет	255	varchar	Not null
Имя	Нет	255	varchar	Not null
Отчество	Нет	255	varchar	Not null
Дата рождения	нет	23	date	Not null
Адрес	Нет	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	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 - Таблица атрибутов для сущности «Тип питания»

Поле	Ключи	Размер	Формат	Свойства
Код	Первичный ключ	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 позволяет обеспечить высокопроизводительную работу с большими объемами данных, реализовать целостность базы данных и её безопасность, а так же разграничение прав доступа пользователей.

3 ФИЗИЧЕСКОЕ ПРОЕКТИРОВАНИЕ

3. 1 Создание базы данных и таблиц

Ниже представлен код создания базы данных на языке SQL:

create database dorozhkin on

( name = 'dorozhkin',

filename = 'E:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATAdorozhkin.mdf',

size = 10 mb)

log on (

name = 'dorozhkin_log',

filename = 'E:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATAdorozhkin_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))

Код создания таблицы «Города»:

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)

Добавление внешних ключей в таблицу «Тур»:

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, 'Швейцария' )

insert into land values (15, 'Япония' )

Последующие таблицы были заполнены с помощью конструктора.

Рисунок 1 – Таблица «Страны»

Рисунок 2 – Таблица «Продажи»

Рисунок 3- Таблица «Города»

Рисунок 4 – Таблица «Туры»

Рисунок 5 – Таблица «Клиенты»

Рисунок 6 – Таблица «Отели»

Рисунок 7 – Таблица «Рейсы»

Рисунок 8 – Таблица «Тип питания»

Рисунок 9 – Таблица «Тип размещения»

Рисунок 10 – Таблица «Тип тура»

Рисунок 11 – Диаграмма баз данных

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, подсчитывающий среднюю стоимость ночи проживания в отеле в каждой из стран:

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

Код для цитирования: Скопировать