Создание индексов в среде MS SQL Server

• Введение
• 1. Назначение разработанного приложения
• 2. Создание индексов в среде MS SQL Server
• 3. Структура базы данных
• 3.1 Состав таблиц базы данных
• 3.2 Структура таблиц базы данных
• 3.3 SQL-код по созданию таблиц и описания ограничений
• 4. Схема диалога пользователя с разработанным приложением
• 4.1 Корректировка таблиц-справочников
• 4.2 Основная функциональность приложения по ведению базы данных
• 4.3 Построение печатных форм
• 4.4 Дополнительная функциональность приложения
• 5. Использованные средства MS SQL Server
• 5.1 Использование представлений
• 5.2 Использование хранимых процедур
• Заключение
• Список использованных источников
• пользователь приложение база данные

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

Достоинством баз данных является простота использования, легкость в получении данных. Недостатком является хранение и обработка данных. При слабом компьютере работа с достаточно большой базой данных займет длительное время.

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

Разработка базы данных будет происходить в Microsoft SQL Server 2008.

Клиентское приложение создано при помощи Microsoft Access 2013.

По итогам курсового проекта мы будем иметь готовое клиент-серверное приложение для работы с разработанной базой данных.

1. Назначение разработанного приложения

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

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

Для работы приложения необходим сервер.

Основные функции и схему общения пользователя с приложением можно увидеть на рисунке 1.1.

Рисунок 1.1 — Схема работы приложения

2. Создание индексов в среде MS SQL Server

Индексы упрощают поиск необходимых данных. Это способ отсортировать данные. Когда список отсортирован, намного проще производить поиск необходимых данных. Сервер SQL получает доступ к данным одним из следующих способов:

- Сканирует все страницы таблицы — сканирование таблицы. Когда SQL Server выполняет сканирование таблицы он:

— Начинает с начала таблицы;

— Сканирует от страницы к странице через все строки таблицы;

— Выделяет строку, которая соответствует запросу.

— Используя индексы. Когда SQL Server использует индексы, он:

— Пересекает структуру дерева индексов для поиска строк, соответствующих запросу;

— Выделяет только необходимые строки, соответствующие критериям запроса.

В каждой таблице обязательно должен присутствовать первичный ключ (рисунок 2.1.), который однозначно идентифицирует запись в таблице. Индекс для первичного ключа создаётся автоматически и он становится кластерным. По определению, первичный ключ может содержать только уникальные значения.

Рисунок 2.1 — Задание первичного ключа

Индексы бывают кластерными (CLUSTERED) и не кластерными (NONCLUSTERED). В кластерном индексе строки физически сортируются на диске в соответствии с индексируемым полем (рисунок 2.2). По определению, у таблицы может быть только один кластерный индекс. Все остальные индексы могут быть только не кластерные.

Рисунок 2.2 — Сортировка данных по кластерному индексу

При не кластерном индексе строки могут на диске храниться в любом порядке, а сортировка осуществляется с помощью определенной таблицы или дерева индекса (рисунок 2.3).

Рисунок 2.3 — Сортировка данных по не кластерному индексу

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

Сервер SQL использует индексы для указания на расположение строки в странице данных вместо просматривания всех страниц таблицы. При создании индексов нужно учитывать достоинства и недостатки индексов.

Достоинства индексов:

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

— Индексы достаточно полезны, но они занимают место на диске и берут на себя дополнительные накладные расходы и расходы на эксплуатацию.

Недостатки индексов:

— Когда вы изменяете данные в индексной колонке, сервер SQL обновляет связанные индексы.

— Накладные расходы на поддержку индексов требуют времени и ресурсов. Поэтому не создавайте индексы, которые не будете часто использовать.

— Индексы на колонки, содержащие большое количество дублирующих данных могут иметь несколько преимуществ.

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

3. Структура базы данных

Разработанная база данных состоит из 10 таблиц, которые связаны между собой.

3.1 Состав таблиц базы данных

В ходе выполнения данной курсовой работы была разработана база данных, предназначенная для использования .

Разработанная база данных состоит из 10 таблиц, описание которых в таблице 3.1.

Таблица 3.1 — Состав таблиц базы данных приложения

Связи между таблицами базы данных отображены на рисунке 3.1

Рисунок 3.1 — Связи между таблицами

3.2 Структура таблиц базы данных

Создание таблиц осуществлялось в среде Microsoft Office Access.

В базе данных использовался вид связей: «один-ко-многим» — то есть, одному экземпляру родительского объекта соответствует несколько экземпляров дочернего. Этот вид связи является наиболее оптимальным при проектировании реляционных баз данных.

В таблицах 3.2−3.11 приведено описание состава таблиц спроектированной базы данных.

Таблица 3.2 — Структура таблицы «Покупатель»

Таблица 3.3 — Структура таблицы «Билет»

Таблица 3.4 — Структура таблицы «Багаж»

Таблица 3.5 — Структура таблицы «Расписание»

Таблица 3.6 — Структура таблицы «Рейсы»

Таблица 3.7 — Структура таблицы «Самолеты» «

Таблица 3.8 — Структура таблицы «Марка»

Таблица 3.9 — Структура таблицы «Авиакомпании»

Таблица 3.10 — Структура таблицы «Экипаж»

Таблица 3.11 — Структура таблицы «Сотрудники»

3.3 SQL-код по созданию таблиц и описания ограничений

/*

Удаление связей

*/

ALTER TABLE Билет

DROP FK_Билет_Покупатель, FK_Билет_Рейсы

go

ALTER TABLE Багаж

DROP FK_Багаж_Билет

go

ALTER TABLE Рейсы

DROP FK_Рейсы_Расписание, FK_Рейсы_Самолеты

go

ALTER TABLE Самолеты

DROP FK_Самолеты_Марка, FK_Самолеты_Авиакомпании

go

ALTER TABLE Экипаж

DROP FK_Экипаж_Рейсы, FK_Экипаж_Сотрудники

/*

Создание и удаление таблиц с помощью SQL-запросов.

*/

go

drop table Авиакомпании

go

drop table Багаж

go

drop table Билет

go

drop table Покупатель

go

drop table Марка

go

drop table Расписание

go

drop table Рейсы

go

drop table Самолеты

go

drop table Сотрудники

go

drop table Экипаж

go

create table Покупатель

(

КодПокупателя int primary key identity,

ФИО varchar (50) not null,

ДатаРождения datetime not null,

СерияПаспорта varchar (2) not null,

НомерПаспорта int not null

)

go

create table Билет

(

КодБилета int primary key identity,

КодПокупателя int not null,

КодРейса int not null,

ДатаПродажи datetime not null,

Цена money not null,

Место int not null

)

go

create table Багаж

(

КодБагажа int primary key identity,

КодБилета int not null,

ТипБагажа nvarchar (25) not null,

Вес int not null

)

go

create table Расписание

(

КодРасписания int primary key identity,

НомерМаршрута int not null,

ПунктВылета varchar (15) not null,

ПунктПрилета varchar (15) not null,

ВремяОтправления datetime not null

)

go

create table Рейсы

(

КодРейса int primary key identity,

ДатаВылета datetime not null,

КодРасписания int not null,

КодСамолета int not null,

)

go

create table Самолеты

(

КодСамолета int primary key identity,

НомерСамолетаint not null,

КодМаркиint not null,

ГодВыпуска datetime not null,

КодАвиакомпании int not null

)

go

create table Марка

(

КодМаркиint primary key identity,

Названиеvarchar (20)

)

go

create table Сотрудники

(

КодСотрудникаint primary key identity,

ФИО varchar (50),

)

go

create table Экипаж

(

КодЭкипажа int primary key identity,

КодРейсаint not null,

КодСотрудникаint not null,

Должностьvarchar (20) not null

)

go

create table Авиакомпании

(

КодАвиакомпании int primary key identity,

Название varchar (20) not null,

Страна varchar (15) not null

)

/*

Определение связей между таблицами

*/

go

ALTER TABLE Билет ADD

CONSTRAINT FK_Билет_Покупатель

FOREIGN KEY (КодПокупателя)

REFERENCES Покупатель (КодПокупателя),

CONSTRAINT FK_Билет_Рейсы

FOREIGN KEY (КодРейса)

REFERENCES Рейсы (КодРейса)

go

ALTER TABLE Багаж ADD

CONSTRAINT FK_Багаж_Билет

FOREIGN KEY (Кодбилета)

REFERENCES Билет (КодБилета)

go

ALTER TABLE Рейсы ADD

CONSTRAINT FK_Рейсы_Расписание

FOREIGN KEY (КодРасписания)

REFERENCES Расписание (КодРасписания),

CONSTRAINT FK_Рейсы_Самолеты

FOREIGN KEY (КодСамолета)

REFERENCES Самолеты (КодСамолета)

go

ALTER TABLE Самолеты ADD

CONSTRAINT FK_Самолеты_Марка

FOREIGN KEY (КодМарки)

REFERENCES Марка (КодМарки),

CONSTRAINT FK_Самолеты_Авиакомпании

FOREIGN KEY (КодАвиакомпании)

REFERENCES Авиакомпании (КодАвиакомпании)

go

ALTER TABLE Экипаж ADD

CONSTRAINT FK_Экипаж_Рейсы

FOREIGN KEY (КодРейса)

REFERENCES Рейсы (КодРейса),

CONSTRAINT FK_Экипаж_Сотрудники

FOREIGN KEY (КодСотрудника)

REFERENCES Сотрудники (КодСотрудника)

4. Схема диалога пользователя с разработанным приложением

4.1 Корректировка таблиц-справочников

Таблицы-справочники содержат в себе основную информацию аэропорта. В данном приложении это информация о покупателях, расписание полетов, экипаж самолета, авиакомпании и марка самолета.

Для просмотра таблиц справочников необходимо выбрать соответвующий пункт меню справочников (рисунок 4.1).

Рисунок 4.1 — Пункты меню для выбора таблиц-справочников

Каждую таблицу можно открыть (рисунок 4.2).

Рисунок 4.2 — Открытие справочника

Все записи можно изменить или добавить новую (рисунок 4.3).

Рисунок 4.3 — Изменение и добавление записи

Помимо этого, запись из таблицы-справочника можно удалить (рисунок 4.5) при отсутствия связанной записи в дочерней таблице. Перед удалением появится информационное окно (рисунок 4.4).

Каскадное удаление не предусмотрено в данной БД для предотвращения потери данных. Удалить запись можно при условии, если на эту запись не ссылаются другие таблицы.

Рисунок 4.4 — Предупреждение об удалении записи

Рисунок 4.5 — Результат удаления записи

4.2 Основная функциональность приложения по ведению базы данных

Помимо таблиц-справочников существуют дочерние таблицы. В них содержится дополнительная информация о таблицах-справочниках (рисунок 4.6).

Рисунок 4.6 — Дочерние таблицы

Дочерние таблицы хранят в себе данные о билетах покупателя, его багаже, информацию о рейсах, самолетах и экипаже рейса.

Каждая дочерняя таблица изменяется как и таблицы-справочники. Вместо кодов родительских таблиц используется удобный выпадающий список родительских полей (рисунок 4.7).

Рисунок 4.7 — Выпадающий список

Любое поле можно изменить (рисунок 4.8).

Рисунок 4.8 — Изменение записи

В каждую таблицу можно добавить запись (рисунок 4.9).

Рисунок 4.9 — Добавление записи

4.3 Построение печатных форм

Для построения отчетов предназначен пункт меню (рисунок 4.10).

Рисунок 4.10 — Выбор печатной формы

В разработанной БД присутствует 5 отчетов:

1. Авиакомпании (рисунок 4.11);

2. Информация о покупателе (рисунок 4.12);

3. Годовой отчет продажи билетов (рисунок 4.13);

4. Самолеты авиакомпаний (рисунок 4.14);

5. Информация о покупателях (рисунок 4.15).

Рисунок 4.11- Отчет «Авиакомпании»

Для создания отчета Информация о покупателях используется запрос Информация о покупателях. Текст отчета ниже:

SELECT ФИО,

ДатаПродажи,

Цена

FROM Покупатель, Билет

WHERE Покупатель. кодпокупателя=Билет.кодпокупателя

and введите_год=Year (ДатаПродажи);

Рисунок 4.12 — Отчет «Покупатели»

Для создания отчета Годовой отчет о продажи билетов используются запрос Годовой отчет по продажам. Текст запроса описан ниже:

SELECT ФИО,

ДатаПродажи,

Цена

FROM Покупатель, Билет

WHERE Покупатель. кодпокупателя=Билет.кодпокупателя

and введите_год=Year (ДатаПродажи)

Рисунок 4.13 — Отчет «Годовой отчет продажи билетов»

Рисунок 4.14 — Отчет «Самолеты Авиакомпаний»

Для создания отчета был использован запрос:

exec ИнфоПокупатель Сидоров

Рисунок 4.15 — Отчет «Информация о покупателе»

Формирования запроса для отчета Годовой отчет продажи билетов:

SELECT ФИО,

ДатаПродажи,

Цена

FROM Покупатель, Билет

WHERE Покупатель. кодпокупателя=Билет.кодпокупателя

and введите_год=Year (ДатаПродажи);

Формирования запроса для отчета Информация о покупателях:

SELECT Покупатель. ФИО,

Покупатель.СерияПаспорта,

Покупатель.НомерПаспорта,

Покупатель.ДатаРождения,

Билет.ДатаПродажи,

Билет.Цена,

Багаж.Вес

FROM Билет, Багаж, Покупатель

WHERE (((Покупатель.кодПокупателя)=[Билет]. КодПокупателя])

AND ((Билет.КодБилета)=[Багаж]. КодБилета]));

4.4 Дополнительная функциональность приложения

Использование отсортированных записей упрощает поиск и обработку данных. В данном приложении используется собственная индексация в таблице Авиакомпании. Сортировка происходит по Названию и страны авиакомпании.

При добавлении новой записи (рисунок 4.16).

Рисунок 4.16 — Добавление новой записи

Запись добавляется в конец списка, потому что данные не обновлены на сервере. Для этого нужно нажать на кнопку обновить все (рисунок 4.17).

Рисунок 4.17 — Обновление записей

После обновления запись займет соответствующее место в списке (рисунок 4.18).

Рисунок 4.18 — Отсортированный список

5. Использованные средства MS SQL Server

5.1 Использование представлений

Для вывода на экран монитора всех таблиц используются представления приведенные в таблице 5.1.

Таблица 5.1 — Представления

Ниже приведен текст SQL, создающий представления:

/* представление для отображения самолетов без кодов родителей*/

go

drop view СамолетыИнфо

go

create view СамолетыИнфо

as

select НомерСамолета,

Марка.Название as Марка,

ГодВыпуска,

Авиакомпании.Название as НазвАвиа,

Страна

from Самолеты, Марка, Авиакомпании

where Самолеты. КодМарки=Марка.КодМарки

and Самолеты. КодАвиакомпании=

Авиакомпании.КодАвиакомпании

/*представление для таблицы Багаж*/

go

drop view ОбладательБагажа

go

create view ОбладательБагажа

as

select Багаж. КодБилета,

ФИО,

ДатаПродажи,

ТипБагажа,

Вес

from Покупатель, Билет, Багаж

where Покупатель. КодПокупателя=Билет.КодПокупателя

and Билет. КодБилета=Багаж.КодБилета

/* представление для таблицы Билет, Экипаж*/

go

drop view БилетРейсы

go

create view БилетРейсы

as

select Рейсы. КодРейса,

Расписание.КодРасписания,

ПунктВылета,

ПунктПрилета,

ВремяОтправления,

ДатаВылета

from Билет, Рейсы, Расписание

where Билет. КодРейса=Рейсы.КодРейса

and Рейсы. КодРасписания=Расписание.КодРасписания

5.2 Использование хранимых процедур

Таблица 5.2 — Хранимые процедуры

Ниже приведен текст SQL, создающий процедуры:

/*процедура для отображения информации о покупателе*/

go

drop proc ИнфоПокупатель

go

create procedure ИнфоПокупатель @ФИО varchar (40)

as

select ФИО,

ДатаРождения,

Цена,

Место,

Вес

From Покупатель, Билет, Багаж

whereПокупатель.КодПокупателя=Билет.КодПокупателя

andБилет.КодБилета=Багаж.КодБилета

and ФИО=@ФИО

Заключение

В результате было разработано база данных управлением аэропорта. Приложение создано в среде MS Acces 2013 и MS SQL SERVER 2008.

В ходе выполнения создания приложения были выполнены следующие действия:

— База данных приведена к третьей нормальной форме;

— Разработаны процедуры, представления для выполнения действий на сервере, чтобы снизить нагрузку на пользовательский компьютер;

— Сделан интуитивно понятливый интерфейс;

— Созданы печатные отчеты.

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

Список использованных источников

1 Винкоп, С. Использование Microsoft SQL Server 7.0: специальное издание / С. Винкоп. — СПб.: Издательский дом «Вильямс», 2001. — 816 с.

2 Хоторн, Р. Разработка баз данных Microsoft SQL Server 2000 на примерах / Р. Хоторн. — М.: Бином, 2001. — 464 с.

3 Змитрович, А. И. Базы данных: учебное пособие для вузов / А. И. Змитрович. — Мн.: Университетское, 1991. — 271 с.

4 Риордан, Р. Программирование в Microsoft SQL Server 2000. Шаг за шагом / Р. Риордан. — М.: Эком, 2002. — 608 с.

5 Кренке, Д. Теория и практика построения баз данных / Д. Кренке. — 8-е изд. — СПб.: Питер, 2003. — 800 с.