Создание и уничтожение индексов. Поддержка ссылочной целостности.

 

Индекс – это упорядоченный список содержимого столбцов или группы столбцов в таблице. Он служит для ускорения поиска данных в таблице. Т.к. записи в таблице в общем случае не упорядочены, то поиск записи по значению поля проводится последовательным просмотром. При длине таблицы в N записей это, в среднем, требует N/2 проверок. При наличии индекса, в среднем, требуется log 2N проверок.

Индекс также служит для быстрого нахождения записей, для которых значение поля находится в определённых пределах. Быстро отсортировать записи по совпадающему значению поля, извлечь записи из таблицы в отсортированных последовательностях и т.д. Будучи однажды созданным, индекс остаётся невидимым для пользователя. SQL сам решит, когда воспользоваться индексом и сделает это автоматически. Также автоматически выполнится обновление индекса при обновлении индексируемой таблицы.

Индекс может быть непосредственно создан командами CREATE TABLE и ENTER TABLE. Формат этой команды имеет следующий вид:

 

CREATE [UNIQUE] INDEX имя_таблицы ON имя_таблицы (имя_столбца, …, имя_столбца);

 

Где:

UNIQUE – значение индекса должно быть уникальным в таблице. SQL отслеживает уникальность индекса и не позволяет изменить БД так, чтобы уникальность нарушилась. Такой индекс нельзя создать, если таблица содержит записи с повторяющимися значениями поля. Уникальные индексы следует создавать одновременно с созданием таблиц.

имя_индекса уникально в базе данных.

имя_таблицы – имя индексируемой таблицы

имя_столбца – это имя столбца таблицы по значению которого строится индекс. Если указано несколько столбцов, индексы упорядочиваются по значению первого столбца, при совпадении значения – второго и т.д.

 

Пример:

 

CREATE INDEX Студфам ON Студент (sname);

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

 

DROP INDEX имя_индекса;

 

Две таблицы находятся, как правило, в отношении один ко многим через значения связующих полей. Для приведённого примера такая связь между таблицами Студент и Оценка по значениям полей snum и osnum. При этом, связующее поле со стороны отношения один называется родительским ключом, а со стороны многие – внешним ключом. SQL автоматически поддерживает отношения ссылочной целостности при внесении данных, если для таблицы определён внешний ключ. Это можно сделать в виде ограничения на столбец, являющийся внешним ключом в следующем формате:

 

REFERENCES родительская_таблица (столбец);

А можно сделать и в виде ограничения на таблицу:

 

FOREIGN KEY (список_стобцов_внеш_ключа) REFERENS родительская_таблица [(список_столбцов)];

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

 

Пример:

 

CREATE TABLE оценка (opnum char (3) NOT NULL REFERENS преподаватель (pnum), osnum char (3) NOT NULL REFERENS студент (snum), …

Пример (для таблицы Студент):

 

CREATE TABLE студент (snum char (3) PRIMARY KEY, sname char (30) NOT NULL, sgrp char (3), FOREIGN KEY (spdp) REFERENCE преподаватель (pnum));

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

 

zavcaf char (3) FEFERENCES преподаватель (pnum);

При определении таблицы с внешним ключом можно определить или ограничить выполнение действий над таблицей с родительским ключом:

 

1. Запретить удаление записи или обновление родительского ключа при наличии связанных записей в таблице с внешним ключом.

 

UPDATE/DELETE OF родительская_табл RESTRICTED;

 

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

UPDATE/DELETE OF родительская_табл CASCADES;

1. При удалении записи или обновлении родительского ключа, значение внешнего ключа автоматически устанавливается в NULL.

 

UPDATE/DELETE OF родительская_табл NULLS;

 

Пример:

 

CREATE TABLE студент (… spdp char (3) REFERENS преподаватель (pnum), UPDATE OF преподаватель CASCADE, DELETE OF преподаватель NULLS);

Тема 10.

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

2. Определение правд доступа к данным.

 

 

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

 

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

Представление – это описание запроса, сохранённое под некоторым именем. Представление определяется с помощью следующей команды:

 

CREATE VIEW имя_представления [список_столбцов_представления] AS запрос_выборки

Пример:

Определить представление для получения сведений о преподавателях кафедры К-1.

 

CREATE VIEW имя_представления Кафедра1 AS SELECT * FROM Преподаватель WHERE pcaf = «K-1»;

Следует отметить, что запрос по команде CREATE VIEW не выполняется, а происходит запоминание описания запроса.

Если список столбцов представления не определён, то столбцы получают имена столбцов базовой таблицы, перечисленных в предложении SELECT. Запрос выборки в представлении может быть любой сложности, содержать подзапросы и сам использовать представления вместо базовых таблиц. Уничтожить представление можно с помощью следующей команды:

 

DROP VIEW имя_представления;

Представления очень удобно использовать по следующим причинам:

 

1. Часто используемые запросы можно сохранить в виде представлений, что упрощает получение выходных данных и конструирования новых запросов на основе представлений.

Пример:

Если определено представление Кафедра1, то, в дальнейшем, список сотрудников кафедры можно получить по следующим запросам:

 

SELECT * FROM Кафедра1;

 

Определим следующее представление:

 

CREATE VIEW Дипломы AS SELECT pcaf, pname, sname FROM Преподаватель WHEREdfgkjdlfjgdlkjgdklg

Теперь возможны различные запросы к этому представлению.

 

Пример 1:

Получить список дипломников определённой кафедры:

 

SELECT * FROM Дипломы WHERE pcaf = «K-1»;

Пример2:

Получить количество дипломников у преподавателя:

 

SELECT pname, COUNT(*) FROM Дипломы GROUP BY pname;

1. Возможность открывать доступ пользователям только к той части информации, которую вы посчитаете нужной для пользователей. Т.е. базовые таблицы можно целиком сделать недоступными, а через представления дать пользователю доступ и обновление только части определённой информации в таблице. Следует отметить, что использование представлений для получения данных вызывает определённые проблемы производительности. Одним из наиболее сложных и неоднозначных аспектов представлений является применение их в командах обновления данных. Т.к. сами представления не содержат данных, то обновляться должны базовые таблицы. Если команду обновления данных можно применить к представлению, то такое представление является обновляемым, если нет – читаемым. Рассмотрим основные критерии, по которым представление считается обновляемым:

 

1) Оно должно базироваться на базовой таблице (на одной) и включать её в первичный ключ.

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

3) Оно не должно содержать DISTINCT или GROUP BY.

4) Оно не должно использовать подзапросы (это ограничение стандарта ANSI, но некоторые системы это позволяют).

5) Оно может основываться на представлении, но это представление само должно быть обновляемым.

6) Для INSERT оно должно включать все поля базовой таблицы, имеющие ограничение NOT NULL.

 

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

 

INSERT INTO Кафедра1 VALUES («004», «П-4», «К-2»)

 

Однако, добавленная таким образом запись для преподавателя К-2 не будет отображена в представлении. Такая ситуация, как правило, недопустима.

Для того, чтобы обеспечить проверку условия запроса для вновь добавляемых записей, в запросах следует указывать предложение WITH CHECK OPTION. Д ля нашего примера это выглядит следующим образом:

 

CREATE VIEW Кафедра1 AS SELECT * FROM Преподаватель WHERE pcaf = «K-1» WITH CHECK OPTION;