Автотехцентр: база данных

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

Первичные ключи относятся к кластерным индексам и являются уникальными.

Теперь создадим два некластеризованных индекса  для таблиц Клиенты и Специалисты  по столбцу Фамилия. Элементы этих индексов будут располагаться по возрастанию. Эти индексы создаются вручную при помощи запроса.

Создание индекса index_clients для таблицы Клиенты:

CREATE UNIQUE NONCLUSTERED INDEX index_clients

ON Клиенты(Фамилия  asc)

Содание индекса index_spec для таблицы Специалисты:

CREATE UNIQUE NONCLUSTERED INDEX index_spec

ON Специалисты(Фамилия asc)

Данные индексы  являются уникальными.

 

3. Создание представлений

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

Представление – это предопределенный запрос, хранящийся в базе данных, который  выглядит подобно обычной таблице  и не требует для своего хранения дисковой памяти.

В данном разделе  приводятся текст запроса, SQL-сценарии для создания представлений и результаты их работы в форме таблицы (или рисунка).

1. Однотабличный запрос на выборку по условиям

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

Для базы данных «Клиенты» был создан однотабличный  запрос, который осуществляет выборку всех клиентов из таблицы «Клиенты», которые имеют марку машины «Audi» и проживающих по улице «Лениградская». Данный запрос был записан в представление view1.

Текст запроса  выглядит следующим образом:

CREATE  VIEW view1 AS

SELECT * FROM Клиенты

WHERE Марка_машины like '%Audi%' and Адрес like '%Ленинградская%'

Результат запроса показан на рисунке 2.3.1.1.

Рисунок 2.3.1.1 – Представление view1

2. Многотабличный запрос

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

Для базы данных «Договор» был создан многотабличный запрос, который осуществляет выборку всех клиентов из таблицы «Клиенты», чье транспортное средство нуждается в покраске. Выборка данных осуществляется также из таблицы «Договор», так как она является связующей между таблицами «Клиенты» и «Ремонт».  Данный запрос был записан в представление view2.

Текст запроса  выглядит следующим образом:

CREATE  VIEW view2 AS

SELECT Клиенты.Фамилия, Клиенты.Имя, Клиенты.Отчество,

Клиенты.Марка_машины, Ремонт.Название AS [Вид технического обслуживания]

FROM Клиенты,  Договор, Ремонт 

WHERE Ремонт.Название like '%Покраска%' and

Договор.Код_клиента=Клиенты.Код_клиентаand

Договор.Код_ремонта=Ремонт.Код_ремонта

Результат запроса показан на рисунке 2.3.2.1.

Рисунок 2.3.2.1 – Представление view2.

3. Запрос с использованием подзапроса

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

Для базы данных «Договор» был создан запрос с использованием подзапроса, который показывает данные о клиентах, у которых сумма за ремонт больше 2000 .  Данный запрос был записан в представление view3. Текст запроса выглядит следующим образом:

CREATE  VIEW view3 AS

SELECT Ремонт.Название, Специалисты.Фамилия AS Специалисты,  Клиенты.Фамилия AS Клиенты,

Договор.Дата_ремонта, Ремонт.Стоимость_ремонта

FROM Ремонт, Специалисты,  Клиенты, Договор

WHERE Клиенты.Код_клиента=Договор.Код_клиентаand

Специалисты.Код_специалиста=Договор.Код_специалистаand

Ремонт.Код_ремонта=Договор.Код_ремонта

And Ремонт.Названиеin ((SELECT Ремонт.Название FROM

Ремонт,Договор WHERE Ремонт.Код_ремонта=Договор.Код_ремонта

And Ремонт.Стоимость_ремонта>'2000'))

Результат запроса показан на рисунке 2.3.3.1.

Рисунок 2.3.3.1 – Представление view3.

4. Итоговый запрос

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

Для базы данных «Договор» был создан итоговый запрос, который показывает количество клиентов у каждого специалиста. В данном запросе было создано вычисляемое поле Количество_клиентов при помощи выражения COUNT. Данный запрос был записан в представление view4. Текст запроса выглядит следующим образом:

CREATE  VIEW view4 AS

SELECT Специалисты.Фамилия, Специалисты.Имя, 

Специалисты.Отчество,

       COUNT (Договор.Код_клиента) AS Количество_клиентов

FROM Договор,  Специалисты

WHERE Специалисты.Код_специалиста=Договор.Код_специалиста

GROUP BY Специалисты.Фамилия,  Специалисты.Имя, Специалисты.Отчество

Результат запроса показан на рисунке 2.3.4.1.

Рисунок 2.3.4.1 – Представление view4.

 

4. Примеры запросов на модификацию данных
1. Простые запросы

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

1. INSERT

Операция вставки INSERT вставляет новые строки в таблицу. При этом значения столбцов могут представлять собой константы либо являться результатом выполнения подзапроса.  Был создан запрос, осуществляющий вставку записи в таблицу «Специалисты». Текст запроса выглядит следующим образом:

INSERTINTO Специалисты (Фамилия, Имя, Отчество, Должность)

VALUES ('Гончаров', 'Павел', 'Алексеевич', 'Механик')

Из запроса  видно, что была вставлена запись со следующими полями: Фамилия – Гончаров,  Имя – Павел, Отчество – Алексеевич, Должность –Механик.

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

2. DELETE

Операция удаления DELETE.  Был создан запрос, который удаляет клиента из таблицы Клиенты, имеющих фамилию «Акулов». Текст запроса выглядит следующим образом:

DELETE  FROM Клиенты

WHERE Фамилия='Акулов'

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

3. UPDATE

Операция обновления UPDATE.  Данный оператор применяется для изменения значений в группе записей или в одной записи указанной таблицы. Был создан запрос, который изменяет дату начала ремонта «12/12/19» на «12/12/21». Текст запроса выглядит следующим образом:

UPDATE Договор

SET Дата_ремонта='12/12/21'  

WHERE Дата_ремонта='12/12/19'

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

2. Запросы с использованием подзапросов

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

Был создан запрос с использованием подзапроса и оператора  обновления данных UPDATE, который осуществляет замену названий документов, которые не были подписаны на название «Неподписанный документ». Текст запроса выглядит следующим образом:

UPDATE Ремонт

SET Название='Корректировка  развал-схождения'

WHERE Название in

(SELECT Ремонт.Название

FROM Ремонт,Договор

WHERE Ремонт.Код_ремонта=Договор.Код_ремонтаand

Ремонт.Название='Регулировка развал-схождения')

Также был создан запрос с использованием подзапроса и оператора удаления DELETE. Данный запрос выполняет удаление записей, которые были созданы ранее 1 декабря 2012 года.

Текст запроса  выглядит следующим образом:

DELETE  FROM Договор

WHERE Дата_ремонтаin

(SELECT Дата_ремонта

FROM Договор 

WHERE Дата_ремонта< '12/12/01')

 

 

2.5 Описание  триггеров

Триггер – это  откомпилированная SQL-процедура, исполнение которой обусловлено наступлением определенных событий внутри реляционной базы данных.

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

На данном этапе  для базы данных «Автотехцентр» были реализованы определенные ограничения посредством триггеров.

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

С помощью триггеров достигаются следующие цели:

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

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

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

Типология триггеров:

1. INSERT TRIGGER – запускаются при попытке вставки данных с помощью команды INSERT.
2. UPDATE TRIGGER – запускаются при попытке изменения данных с помощью команды UPDATE.
3. DELETE TRIGGER – запускаются при попытке удаления данных с помощью команды DELETE.

Для базы данных было разработано три триггера: два на удаление (DELETE), на вставку (INSERT).

Для базы данных «Автотехцентр» было создано два  триггера на удаление.

При попытке  удалить определенного специалиста  или клиента из таблиц «Специалисты»  и «Клиенты» соответственно происходит ошибка и записи не удаляются. Это связано с тем, что поля Код_специалиста и Код_клиента, являющиеся первичными ключами для таблиц «Специалисты» и «Клиенты» являются для таблицы «Договор» внешними ключами. И поэтому происходит конфликт в базе данных, связанный с таблицей «Договор» с полями Код_специалиста и Код_клиента. Необходимо исправить эту ошибку путем создания триггера на удаление.

Текст создания двух триггеров на удаление будет  выглядеть следующим образом:

1) CREATE TRIGGER Триггер_del1

ON Специалисты  INSTEAD OF DELETE

AS

IF @@ROWCOUNT=1

BEGIN

  DECLARE @y INT

  SELECT @y=Код_специалиста

  FROM deleted

     DELETE Договор

     WHERE Договор.Код_специалиста=@y

     DELETE Специалисты

     WHERE Специалисты.Код_специалиста=@y

END

2) CREATE TRIGGER Триггер_del2

ON Клиенты INSTEAD OF DELETE

AS

IF @@ROWCOUNT=1

BEGIN

  DECLARE @y INT

  SELECT @y=Код_клиента

  FROM deleted

     DELETE Договор

     WHERE Договор.Код_клиента=@y

     DELETE Клиенты

     WHERE Клиенты.Код_клиента=@y

END

Эти триггеры работают следующим образом. Когда мы удаляем  запись из таблиц «Специалситы» или  «Клиенты», то удаляются записи из таблицы из таблицы «Договор» с соответствующими значениями полей Код_специалиста или Код_клиента.