Целостность данных в языке SQL Лекция 2
Три составляющие целостности: • Целостность объектов обеспечивается применением ограничения PRIMARY KEY, в соответствии с которым СУБД производит контроль уникальности атрибута или группы атрибутов, входящих в составной первичный ключ. • Целостность приложений основана на использовании ограничений CHECK опции DEFAULT, задающих бизнес-правила. Сложные бизнесправила поддерживаются с помощью механизма триггеров. • Ссылочная целостность базируется на идентификации в родительской таблице родительского ключа и на выделении в дочерней таблице внешнего ключа. • В качестве родительского ключа выбирается либо первичный ключ, либо уникальный ключ, который является потенциально возможным ключом. Для атрибутов, входящих в родительский ключ, должны быть использованы ограничения PRIMARY KEY либо UNIQUE
Внешний ключ создается в дочерней таблице, его значения обязательно должны совпадать со значениями родительского ключа либо им должно быть присвоено неопределенное значение NULL. Количество атрибутов составного внешнего ключа должно быть равно количеству атрибутов составного родительского ключа. • Ограничения на уровне таблицы имеют следующий синтаксис; • CONSTRAINT <имя ограничения> • FOREIGN KEY (<имя атрибута 1>, <имя атрибута 2>) • REFERENCES <имя таблицы> (<имя атрибута 3>, <имя атрибута 4>), • где <имя атрибута 1 > и <имя атрибута 2> – это имена атрибутов составного внешнего ключа; <имя таблицы> – это название родительской таблицы, в которой в качестве родительского ключа выбраны атрибут 3 и атрибут 4. • Для отражения взаимосвязи между родительским и внешним ключами в SQL 2 определены правила удаления и обновления. • Правило удаления/обновления имеет следующий синтаксис: ОN DELETE CASCADE -автоматическое удаление строк-потомков • RESTRICT -запрещает удаление строки из родительской таблицы • SET NULL -внешним ключам присваивает неопределенные значения • SET DEFAULT -устанавливает значение по умолчанию всем внешним ключам. • ОN UPDATE ………… •
ПРИМЕР. Для создаваемых баз данных необходимо указать правила удаления и обновления данных • • • Sotr_1 – родительская таблица, Kafedra, Sarplata – отношения-потомки. Внешние ключи: fio_sav, cod_st. Родительские ключи: cod, fio. Первичные ключи: cod, naim_kaf, cod_ch, prik. CREATE TABLE sotr_1 (cod INT PRIMARY KEY NOT NULL, fio VARCHAR 2(35) UNIQUE, dol VARCHAR 2(25), vosr INT, pod VARCHAR 2(25)); • • CREATE TABLE kafedra (naim_kaf VARCHAR 2(10) PRIMARY KEY NOT NULL, fio_sav VARCHAR 2 (35) DEFAULT 'ИВАНОВ', god DATE, FOREIGN KEY (fio_sav) REFERENCES sotr_1 (fio) ON UPDATE CASCADE ON DELETE DEFAULT); • • • CREATE TABLE sarplata (cod_ch INT NOT NULL, cod_st INT NOT NULL, prik CHAR (20) NOT NULL, summa DEC (7, 2), PRIMARY KEY (cod_ch, prik), FOREIGN KEY (cod_st) REFERENCES sotr_1 (cod) ON DELETE CASCADE ON UPDATE CASCADE);
Создание индексов в SQL. Индекс – это объект базы данных, позволяющий существенно ускорить доступ к данным. В реализациях языка SQL, используемых в промышленных СУБД, применяются следующие виды индексов: • индексы на основе B+дерева (B-tree index); • индексы битовой карты (bitmap index); • индексы по обратному ключу (reverse – key index); • индексы на основе значения функции (function – based index); • таблиц, организованных по индексу (index organized table).
Индексы в виде B+дерева создаются автоматически СУБД для атрибутов с ограничениями PRIMARY KEY, UNIQUE. • Такие индексы содержат уникальные значения атрибутов и им присваиваются имена, совпадающие с именами ограничений, которые используются при создании таблицы. • Неуникальные индексы конструируются разработчиками приложений с помощью команды CREATE INDEX, имеющей следующий синтаксис: CREATE INDEX <имя индекса> ON <имя таблицы> (<имя атрибута 1> [, <имя атрибута 2>]);
Пример. • Инспекторы кадрового управления с высокой интенсивностью формируют запросы на получение информации о сотрудниках, возраст которых удовлетворяет определенным ограничениям. Разработчиком приложений принято решение о создании неуникального индекса по атрибуту возраст vosr таблицы sotr: СREATE INDEX ind_vosr ON sotr (vosr);
Следует руководствоваться правилами при создании индексов: • Правило 1. Индекс должен быть высокоизбирательным. Эмпирическое правило определяет индекс как высокоизбирательный, если он содержит более двадцати процентов уникальных значений • Правило 2. Не рекомендуется создавать индексы для таблиц малой размерности. Критерием малой размерности является размещение всех строк таблицы в одном блоке дискового пространства. • Правило 3. Нецелесообразно создавать индексы для атрибутов, у которых преобладают операции манипулирования данными, т. е. операции вставки, изменения и удаления значений. Эти операции приводят к необходимости реорганизации индекса. • Правило 4. Составные вторичные индексы целесообразно конструировать для атрибутов, которые совместно используются в сложном условии в предложении WHERE. • Правило 5. Если атрибут является лидирующим в составном атрибуте, то для него не рекомендуется создавать отдельный индекс. • Правило 6. Индекс нецелесообразно создавать, если количество возвращаемых кортежей при выполнении запроса более 2 -4 процентов от общего количества кортежей.
Индексы битовой карты в качестве столбцов содержат значения индексируемого атрибута, а в качестве строк - адреса кортежей таблицы. Значение битовой карты равно единице, если кортеж имеет указанное индексируемое значение, а биты для всех остальных значений этого кортежа устанавливаются в значение ноль. Индексы битовой карты конструируются следующей командой: CREATE INDEX <имя индекса> ON <имя таблицы> (<имя атрибута 1> [, <имя атрибута 2>]) BITMAP; Рекомендуется создавать для атрибутов, имеющих небольшое количество различных значений. Битовые карты для этих атрибутов имеют небольшое количество столбцов, что существенно снижает время поиска необходимой строки.
Индексы, основанные на функции. В существующих реализациях СУБД предусмотрена возможность формирования индекса на основе значений функции, которая должна иметь тот же самый вид, что функция в предложении условия WHERE. • Пример. В качестве исходного рассматривается отношение sotr. Необходимо увеличить оклад в 1, 5 раза для тех сотрудников, у которых он будет меньше 2500 даже после запланированного повышения. • Команда обновления оклада имеет вид: UPDATE sotr SET oklad=oklad*1. 50 WHERE (oklad*1. 50) < 2500; Для ускорения выборки кортежей, удовлетворяющих условию в предложении WHERE, необходимо создать индекс: CREATE INDEX okl_indx ON sotr (oklad*1. 50); • Для удаления индекса используется следующая команда SQL: DROP INDEX <имя индекса>;
Применение последовательностей и синонимов в промышленных СУБД • Последовательность (sequence) – это объект базы данных, который создается пользователем для генерации уникальных значений первичного ключа: CREATE SEQUENCE <имя последовательности>; Используются следующие опции при создании последовательности: • • • START WITH <начальное число>; (1) INCREMENT BY <приращение>; (1) MAXVALUE <максимальное число>; (10 27) MINVALUE <минимальное число>; (-1) CACHE <предварительное количество чисел>; (20) CYCLE/NOCYCLE <разрешение либо прекращение циклической генерации чисел>. (запрещена)
Для обращения к значениям последовательности в СУБД ORACLE используются два псевдостолбца: NEXTVAL и CURRVAL. • Псевдостолбец NEXTVAL предназначен для выборки следующего свободного числа последовательности, т. е. этот псевдостолбец возвращает уникальные значения при каждом обращении к нему. • Псевдостолбец CURRVAL применяется для ссылки на последнее число, сгенерированное в текущем сеансе доступа. Для обращения к псевдостолбцам необходимо применять их полные имена: • <имя последовательности>. NEXTVAL, <имя последовательности>. CURRVAL.
Известны следующие правила использования псевдостолбцов NEXTVAL и CURRVAL. Они могут применяться в следующих командах и предложениях: • в предложении SET команды UPDATE; • в предложении VALUES команды INSERT; • в команде SELECT с определенными ограничениями. Псевдостолбцы не могут использоваться в следующих командах и предложениях: • в команде SELECT с ключевым словом DISTINCT; • в команде SELECT с предложениями GROUP BY, HAVING, ORDER BY; • в подзапросах, входящих в команды SELECT, DELETE и UPDATE; • в команде SELECT для формирования представления; • в выражении DEFAULT в качестве значения по умолчанию.
Пример. Создана таблица kafedra_1, содержащая следующие атрибуты: код кафедры cod_k; наименование кафедры naim_k; фамилия заведующего fio_sav; учебная нагрузка заведующего кафедрой nagrs. • Необходимо ввести в базу данных информацию о новой кафедре, созданной в университете. • Для формирования значений атрибута код кафедры cod_k необходимо создать последовательность: CREATE SEQUENCE seq_kaf, а в команде INSERT для присвоения атрибуту cod_k следующего значения использовать псевдостолбец seq_kaf. NEXTVAL: • INSERT INTO kafedra_1 (cod_k, naim_k, fio_sav, nagrs) VALUES (seq_kaf. NEXTVAL, ‘ИНФОРМАТИКА’, ‘ИВАНОВ В. Е. ’, 700);
Наряду с таблицей kafedra_1 логическая модель базы данных включает отношение sotr_4 со следующими атрибутами: • код сотрудника cod_s; • код кафедры cod_kf; • фамилия сотрудника fio_s; • должность сотрудника dol_s. Для ввода информации о сотрудниках, принимаемых на работу на кафедру ‘ИНФОРМАТИКА’, необходимо сформировать следующую команду: • INSERT INTO sotr_4 (cod_s, cod_kf, fio_s, dol_s) • VALUES (seq_ sotr. NEXTVAL, seq_kaf. CURRVAL, ‘ЗУБОВ В. И. ’, ‘ДОЦЕНТ’); • Перед выполнением этой команды должна быть создана последовательность seq_ sotr, предназначенная для генерации кодов сотрудников: CREATE SEQUENCE seq_sotr;
• С помощью команды ALTER SEQUENCE можно производить изменения шага приращения, максимального и минимального значений, режима циклической генерации значений, а также кэширования. • В частности, при достижении максимального значения генерация новых чисел прекращается и выдается сообщение об ошибке. • Для продолжения использования последовательности с помощью команды ALTER SEQUENCE необходимо установить новое максимальное значение: ALTER SEQUENCE seq_sotr MAXVALUE 999999; • Однако опция START WITH не может быть изменена с помощью команды ALTER SEQUENCE. Для получения начального числа последовательности ее необходимо удалить и создать новую последовательность. Для удаления последовательности используется команда: DROP SEQUENCE <имя последовательности>;
С помощью команды CREATE SYNONYM администраторы баз данных могут создавать общедоступные синонимы, а пользователи – частные. • Синонимы обеспечивают краткие альтернативные имена таких объектов как таблицы, представления, последовательности и др. Для создания общедоступного синонима применяется команда: CREATE PUBLIC SYNONYM <имя синонима> FOR <имя объекта>; • а для формирования частного синонима команда: CREATE SYNONYM <имя синонима>FOR <имя объекта>; • Удаление синонимов производится командой: DROP SYNONYM <имя синонима>; Общедоступный синоним может быть удален только администратором базы данных.
Реализация операции проекции в простых запросах на языке SQL Простые запросы на чтение предполагают доступ к данным, хранящимся в одной таблице. Операция проекции основана на выборке значений тех атрибутов, имена которых указаны в списке команды SELECT. • Выбор отдельных атрибутов. Базовая конструкция этой команды для выполнения операции проекции имеет следующий синтаксис: SELECT <имя атрибута 1>, … , <имя атрибута n> FROM <имя таблицы>; На логическом уровне команда SELECT выполняется путем построчного просмотра таблицы, указанной в предложении FROM. Для каждой строки таблицы выбираются значения тех атрибутов, которые включены в список атрибутов предложения SELECT. Запрос 1. Из таблицы sotr получить распределение сотрудников по должностям: SELECT fio, dol FROM sotr;
Переименование столбцов атрибутов. • Изменить формат вывода заголовков, используемый по умолчанию, можно с помощью псевдонима или алиаса столбца. Псевдоним указывается после имени столбца через пробел либо используется необязательное ключевое слово AS. Псевдоним, содержащий пробелы либо такие специальные символы, как #, $ или различающие регистры символов, заключается в двойные кавычки (“ ”).
• Если псевдоним содержит только буквы, цифры и знаки подчеркивания в любой комбинации, то кавычки можно не указывать. Предложение AS целесообразно применять для именования производных столбцов, т. е. тех столбцов, значения которых определяются выражениями. Зарезервированные слова также можно использовать в качестве псевдонимов. В этом случае они должны быть заключены в двойные кавычки. • По умолчанию псевдонимы выводятся заглавными буквами.
Выбор всех столбцов/атрибутов • Для выбора всех столбцов таблицы в предложении SELECT в списке атрибутов необходимо указать символ звездочки (*). Такую конструкцию обычно применяют для таблиц с небольшим количеством атрибутов с целью получения общего представления о структуре базы данных. • Запрос 2. Вывести все данные, хранящиеся в таблице sotr: • SELECT * • FROM sotr;
Устранение строк-дубликатов. • Для устранения дубликатов и получения уникальных значений необходимо в предложении SELECT перед списком атрибутов указать ключевое слово DISTINCT. • Запрос 3. Выдать перечень различных должностей для сотрудников университета: SELECT DISTINCT dol FROM sotr; Отличительная особенность обработки предложением SELECT DISTINCT значений NULL состоит в том, что они рассматриваются как дубликаты и эта команда возвращает только одно значение NULL.
Вычисляемые столбцы • В списке атрибутов предложения SELECT могут быть указаны арифметические выражения, включающие имена атрибутов, числовые константы и арифметические операторы: сложение (+), вычитание (-), умножение (*), деление (/). • Последовательность выполнения операторов является традиционной: умножение и деление имеют приоритет перед сложением и вычитанием; операторы с одинаковым приоритетом выполняются слева направо; для изменения порядка выполнения операторов необходимо использовать скобки. • Вычисляемые столбцы не являются новыми столбцами таблицы, а используются только для вывода результатов вычислений. По умолчанию имя такого столбца в результирующей таблице совпадает с записью арифметического выражения. • Запрос 4. Выдать новые оклады сотрудников после их увеличения на 500 рублей: SELECT DISTINCT fio, oklad +500 FROM sotr; • Запрос 5. Вывести годовой доход сотрудников и присвоить этому вычисляемому атрибуту псевдоним “Annual Salary”: SELECT DISTINCT fio, oklad, 12*(oklad +800) AS “Annual Salary” FROM sotr; В вычисляемом атрибуте константа 800 соответствует ежемесячной премии сотрудника. Заголовок Annual Salary содержит пробелы, поэтому он заключен в кавычки.
Неопределенные значения в арифметических выражениях. Результат вычисления выражения, содержащего неопределенное значение, так же будет неопределенным – NULL. Например, операция деления числа на ноль приводит к ошибке, но результатом деления на неопределенное значение будет неопределенное значение. Оператор конкатенации ( ) в списке атрибутов. • Позволяет соединять одни атрибуты с другими атрибутами, арифметическими выражениями или константами. Атрибуты, указанные с различных сторон оператора, соединяются и образуют единый выходной атрибут. • Запрос 6. Соединить атрибуты fio и dol в единый атрибут, присвоить этому атрибуту псевдоним “Employees”: • SELECT fio dol AS “Employees” • FROM sotr; • • Необходимо отметить, что конкатенировать можно только строковые данные. В том случае, когда один из атрибутов имеет числовой тип данных либо тип данных дата/время, то значения такого атрибута должны быть преобразованы в символьное представление.
Использование литералов в SELECT-списке • Литерал – это любой символ, выражение или число, включенные в SELECT-список и не являющиеся именем или псевдонимом столбца. • Включенные в SELECT-список символьные литералы и литералы с датами, должны быть заключены в одиночные апострофы (‘’), для цифровых литералов апострофы не применяются. Особенность обработки литералов состоит в том, что приведенное в SELECT-списке значение повторяется для каждой возвращаемой строки. • Запрос 7. Вывести для каждого сотрудника фамилию fio, зарплату как сумму оклада okl и надбавки 800, в каждую выводимую строку включить текст ‘зарплата за месяц’. Присвоить псевдоним литералу, включенному в SELECT-список: • SELECT fio, ‘зарплата за месяц’ AS INTSAL, okl+800 • FROM sotr;