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

 

Содержание

Введение

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

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

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

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

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

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

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

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

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

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

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

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

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

.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-код по созданию таблиц и описания ограничений

/*

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

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

goTABLE БагажFK_Багаж_БилетTABLE РейсыFK_Рейсы_Расписание,FK_Рейсы_Самолеты

goTABLE СамолетыFK_Самолеты_Марка,FK_Самолеты_АвиакомпанииTABLE ЭкипажFK_Экипаж_Рейсы,FK_Экипаж_Сотрудники

/*

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

*/table Авиакомпанииtable Багажtable Билетtable Покупательtable Маркаtable Расписаниеtable Рейсыtable Самолетыtable Сотрудникиtable Экипажtable Покупатель

(

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

ФИО varchar(50) not null,

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

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

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

)table Билет

(

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

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

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

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

Цена money not null,

Место int not null

)table Багаж

(

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

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

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

Вес int not null

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

(

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

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

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

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

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

)table Рейсы

(

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

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

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

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

)table Самолеты

(

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

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

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

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

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

)table Марка

(

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

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

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

(

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

ФИО varchar(50),

)table Экипаж

(

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

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

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

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

)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(КодРейса)Рейсы(КодРейса)TABLE Багаж ADDFK_Багаж_БилетKEY(Кодбилета)Билет(КодБилета)TABLE Рейсы ADDFK_Рейсы_Расписание KEY(КодРасписания)

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

CONSTRAINT FK_Рейсы_СамолетыKEY(КодСамолета)Самолеты(КодСамолета)TABLE Самолеты ADDFK_Самолеты_МаркаKEY(КодМарки) Марка(КодМарки),

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

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

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

goTABLE Экипаж ADDFK_Экипаж_РейсыKEY(КодРейса)Рейсы(КодРейса),FK_Экипаж_СотрудникиKEY(КодСотрудника) Сотрудники(КодСотрудника)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ФИО,

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

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

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

Рисунок 4.12 - Отчет "Покупатели"

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

ФИО,

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

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

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

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

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

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

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

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

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

ФИО,

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

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

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

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

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

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

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

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

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

Билет.Цена,

Багаж.ВесБилет, Багаж, Покупатель(((Покупатель.кодПокупателя)=[Билет].[КодПокупателя])((Билет.КодБилета)=[Багаж].[КодБилета]));

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

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

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

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

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

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

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

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

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

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

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

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

НазваниеНазначениеГде используется1 СамолетыИнфоВыборка полей из таблицПросмотр таблицы самолеты, отчет самолетов авиакомпаний2 ОбладательБагажаВыборка полей из таблицПросмотр таблицы Багаж3 БилетРейсыВыборка полей из таблицПросмотр таблицы Билет,Экипаж

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

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

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

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

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

ГодВыпуска,

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

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

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

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

go

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

go

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

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

ФИО,

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

ТипБагажа,

Вес

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

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

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

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

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

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

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

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

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

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

ДатаВылета

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

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

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

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

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

НазваниеНазначениеГде используется1 ИнфоПокупательПоиск покупателя по ФИОДля отчета информации о покупателе

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

/*процедура для отображения информации о покупателе*/proc ИнфоПокупательprocedure ИнфоПокупатель @ФИО varchar(40)ФИО,

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

Цена,

Место,

ВесПокупатель,Билет,БагажПокупатель.КодПокупателя=Билет.КодПокупателяБилет.КодБилета=Багаж.КодБилетаФИО=@ФИО

Заключение

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

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

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

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

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

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

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

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

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

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

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

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

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

Содержание Введение . Назначение разработанного приложения . Создание индексов в среде MS SQL Server . Структура базы данных .1 Состав таблиц

Больше работ по теме: