Проектирование схемы данных

ВВЕДЕНИЕ

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

· Спроектировать схему данных;

· Согласно полученной схеме, создать необходимые таблицы, предварительно определив тип данных каждого их поля;

· Реализовать указанные в задании запросы;

· Создать представления, имеющие смысл для данной предметной области;

· Создать триггеры и ограничения целостности для поддержания целостности данных в базе данных;

ПРОЕКТИРОВАНИЕ ИНФОРМАЦИОННОЙ СИСТЕМЫ ПРЕДСТАВИТЕЛЬСТВА ТУРИСТИЧЕСКОЙ ФИРМЫ В ЗАРУБЕЖНОЙ СТРАНЕ

Проектирование схемы данных

Рис 1.1 – Диаграмма базы данных

Рассмотрим полученные таблицы:

Таблица	Описание
airplane_race	Таблица, которая хранит данные о рейсах самолетов
excurs	Таблица экскурсий, содержащая список всех возможных экскурсий
excurs_agency	Таблица, хранит в себе список доступных экскурсионных агентств, позволяя избежать избыточности и аномалии обновлений
excurs_summary	Является связующей между таблицами excurs и excurs_agency, а также имеет связь с таблицей tourists.
hotels	Таблица, служит для хранения информации об отелях.
income_category	Таблица, является справочником категорий расходов, позволяет избежать избыточности и аномалии обновлений
income_summary	Таблица, служит для хранения информации одоходах представительства
outcome_summary	Таблица, служит для хранения информации о расходах представительства
tourist	Таблица, служит для хранения информации о туристе
tours	Являетсясвязующейдлятаблицtourist, type_tourist, hotels, airplanes_race
type_tourist	Является справочником для двух возможных категорий туристов

Рис1.2 – Описаниетаблиц

Таблицы:

airplane_race:

· _id - ПервичныйключБД

· name – Атрибут, служащий для хранения наименования рейса

· cost – Стоимость перелета рейсом.

excurs:

· _id - Первичный ключ БД

· name - Атрибут, служащий для хранения наименования экскурсий.

· cost – Стоимость экскурсии

excurs_agency:

· _id - ПервичныйключБД

· name - Атрибут, служащий для хранения наименования агентств

· multiplyCost – коэффициент стоимости экскурсии у агентства

excurs_summary:

· _id - Первичный ключ БД

· tourist_id – Внешний ключ, служит для связи с таблицей tourists

· excurs_id - Внешний ключ, служит для свези с таблицей excurs

· agency_id – Внешний ключ, служит для свези с таблицей excurs_agency

· date – Атрибут, который хранит дату проведения экскурсии

hotels:

· _id - Первичный ключ БД

· name - Атрибут, служащий для хранения наименования отелей

· cost – Стоимость размещения в отеле

income_category:

· _id - ПервичныйключБД

· name - Атрибут, служащий для хранения наименования категорий расходов

income_summary:

· _id - ПервичныйключБД

· income_id – Внешний ключ, служит для свези с таблицей income_category

· total - Атрибут, служащий для хранения суммы дохода

· date – Атрибут, служащий для хранения даты поступления дохода

· tour_id – Внешний ключ, служит для свези с таблицей type_tourist

outcome_summary:

· _id - ПервичныйключБД

· outcome_id – Внешний ключ, служит для свези с таблицей income_category

· total - Атрибут, служащий для хранения суммы дохода

· date – Атрибут, служащий для хранения даты поступления расхода

· tour_id – Внешний ключ, служит для свези с таблицей type_tourist

tourist:

· _id - Первичный ключ БД

· fio – Атрибут, служащий для хранения ФИО туриста

· passport - Атрибут, служащий для хранения паспортных данных туриста

· sex - Атрибут, служащий для хранения пола туриста

· birthday - Атрибут, служащий для хранения даты рождения туриста

· hasVisa - Атрибут, служащий для хранения информации о наличии визы

tours:

· _id - Первичный ключ БД

· tourist_id- Первичный ключ БД для связи с таблицей tourist

· type_tourist_id- Первичный ключ БД для связи с таблицей type_tourist

· hotel_id- Первичный ключ БД для связи с таблицей hotels

· start_race_id- Первичный ключ БД для связи с таблицей airplanes_race, и обозначает рейс, которым турист пребывает в страну

· end_race_id- Первичный ключ БД для связи с таблицей airplanes_race, и обозначает рейс, которым турист убывает из страны

· start_day - Атрибут, служащий для хранения даты начала тура

· end_date - Атрибут, служащий для хранения даты окончания тура

type_tourist:

· _id - ПервичныйключБД

· name - Атрибут, служащий для хранения наименования категорий туристов

Создание запросов

1) Сформировать список туристов для таможни в целом и по указанной категории.

SELECT tourist.fio, tourist.passport

FROM tourist INNERJOINtours

ON tours.tourist_id = tourist._id

WHERE type_tourist_id = 2

Рис 1.3Результат запроса

2) Сформировать списки на расселение по указанным гостиницам в целом и указанной категории.

SELECT tourist.fio, tours.hotel_room_n, tours.start_day, end_date, hotels.name

FROM tourist INNERJOINtours

ON tourist._id = tours.tourist_id INNERJOINhotels

ON tours.hotel_id = hotels._id

WHERE hotels._id = 1 AND tours.type_tourist_id = 1

Рис 1.4Результат запроса

3) Получить количество туристов, побывавших в стране за определенный период в целом и по определенной категории.

SELECT tourist.fio ASФИО,COUNT(tourist.fio)AS [Количествопосещений]

FROM tourist INNER JOIN tours

ON tourist._id = tours.tourist_id JOIN type_tourist

ON tours.type_tourist_id = type_tourist._id

WHERE(type_tourist_id = 1)AND((start_day BETWEEN'2015-05-01'AND'2015-05-31')

AND(end_date BETWEEN'2015-01-01'AND'2015-09-09'))

GROUP BY tourist.fio

Рис 1.5Результат запроса

4) Получить сведения о конкретном туристе: сколько раз был в стране, даты прилета/отлета, в каких гостиницах останавливался, какие экскурсии и в каких агентствах заказывал.

SELECT fio, start_day, end_date, hotels.name

FROM tourist INNERJOINtours

ON tourist._id = tours.tourist_id INNERJOIN

ON tours.hotel_id = hotels._id

WHERE tourist_id = 1

Рис 1.6Результатзапроса

SELECTfio, excurs.name, excurs_agency.name

FROM tourist INNERJOINexcurs_summary

ON tourist._id = excurs_summary.tourist_id INNERJOIN

ON excurs_summary.excurs_id = excurs._id INNERJOINexcurs_agency

ON excurs_summary.agency_id = excurs_agency._id

WHERE excurs_summary.tourist_id = 1

Рис 1.7Результат запроса

5) Получить список гостиниц, в которых производится расселение туристов, с указанием количества занимаемых номеров и проживавших в них человек за определенный период.

SELECThotels.name,COUNT(tourist_id)AS [Кол. проживающих],COUNT(DISTINCT

hotel_room_n)AS [Кол. зан. номеров]

FROM hotels INNERJOINtours

ON hotels._id = tours.hotel_id

WHERE tours.start_day BETWEEN'01.01.2001'AND'01.01.2016'

GROUPBY hotels.name

Рис 1.8Результат запроса

6) Получить общее количество туристов, заказавших экскурсии за определенный период.

SELECT COUNT(DISTINCT tourist_id)

FROM excurs_summary

WHEREDATEBETWEEN'01.05.2015'AND'01.01.2016'

7) Выбрать самые популярные экскурсии и самые качественные экскурсионные агентства.

SELECT name,COUNT(name)as [Количествозаказов]

FROM excurs INNERJOINexcurs_summary

ON excurs_summary.excurs_id = excurs._id

GROUPBY name

ORDERBY [Количествозаказов] DESC

Рис 1.9 Результатзапроса

SELECT name,COUNT(name)AS [Количествозаказов]

FROM excurs_agency INNERJOINexcurs_summary

ON excurs_summary.agency_id = excurs_agency._id

GROUPBY name

ORDERBY [Количествозаказов] DESC

Рис 1.10Результат запроса

8) Получить данные о загрузке указанного рейса самолета на определенную дату: количество мест.

SELECTname,COUNT(name)AS [Загруженностьрейсанадень]

FROM airplanes_race INNERJOINtours

ON airplanes_race._id = tours.start_race_id OR airplanes_race._id = tours.end_race_id

WHERE (start_day ='2015-05-11'OR end_date ='2015-05-11')AND airplanes_race._id = 2

GROUPBY name

Рис 1.11Результат запроса

9) Получить полный финансовый отчет в целом и для определенной категории туристов.

SELECT income_category.name,SUM(outcome_summary.total)AS [Расходы],

SUM(income_summary.total)AS [Доходы]

FROM outcome_summary INNER JOIN income_category ON income_category._id

outcome_summary.outcome_idINNER JOIN income_summary ON income_category._id

income_summary.income_id

WHERE outcome_summary.tour_id = 2 OR income_summary.tour_id = 2

GROUP BY income_category.name

Рис 1.12Результат запроса

10) Получить данные о расходах и доходах за определенный период: обслуживание самолета, гостиница, экскурсии, визы, расходы представительства и т.п.

SELECT name,SUM(DISCTINCT outcome_summary.total)AS [Расходы],SUM(DISTINCT

income_summary.total)AS [Доходы]

FROM outcome_summary INNER JOIN income_category ON income_category._id

outcome_summary.outcome_idINNER JOIN income_summary ON income_category._id = income_summary.income_id

WHERE outcome_summary.DATEBETWEEN'01.01.2007'AND'01.12.2015'

OR income_summary.DATEBETWEEN'01.01.2007'AND'01.12.2015'

GROUP BY name

Рис 1.13Результат запроса

11) Вычислить рентабельность представительства (соотношение доходов и расходов).

SELECT SUM(DISCTINCT outcome_summary.total)AS [Расходы],SUM(DISTINCT

income_summary.total)AS [Доходы]

FROM outcome_summary INNER JOIN income_category ON income_category._id =

outcome_summary.outcome_idINNER JOIN income_summary ON income_category._id =

income_summary.income_id

WHERE outcome_summary.DATEBETWEEN'01.01.2007'AND'01.12.2015'

OR income_summary.DATEBETWEEN'01.01.2007'AND'01.12.2015'

Рис 1.14Результат запроса

12) Получить сведения о туристах указанного рейса: список группы, гостиницы

SELECT tourist.fio AS [Списокрейса]

FROM tourist INNERJOIN tours

ON tours.tourist_id = tourist._id INNERJOINairplanes_race

ON airplanes_race._id = tours.start_race_id OR airplanes_race._id = tours.end_race_id

WHERE airplanes_race._id = 1

GROUPBY fio

Рис 1.15Результат запроса

SELECT name as [Списокгостиниц]

FROM hotels INNERJOIN tours

ON tours.hotel_id = hotels._id

WHERE tours.end_race_id = 1 OR tours.start_race_id = 1

GROUPBY name

Планы запросов

· Получить список гостиниц, в которых производится расселение туристов, с указанием количества занимаемых номеров и проживавших в них человек за определенный период.

SELECT hotels.name,COUNT(tourist_id)AS [Кол. проживающих],COUNT(DISTINCT

hotel_room_n)AS [Кол. зан. номеров]

FROM hotels INNERJOINtours

ON hotels._id = tours.hotel_id

WHERE tours.start_day BETWEEN'01.01.2001'AND'01.01.2016'

GROUPBY hotels.name