Обеспечение поддержки целостности данных и добавление, удаление и изменение информации в таблицах

Лабораторная работа № 5

Обеспечение поддержки целостности данных и добавление, удаление и изменение информации в таблицах

Цель работы

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

Ход работы

1. В лабораторной работе № 3 Вы определились, какую базу данных будете использовать в дальнейших лабораторных работах — базу данных технологического процесса согласно индивидуальному заданию или базу данных «БИБЛИОТЕКА». Для выполнения этой и последующих лабораторных работ Вы будете модифицировать свой вариант согласно методическим указаниям. Перед началом работы ознакомьтесь с методическими указаниями. Запустите на выполнение SCRIPT-файл, который Вы написали выполняя предыдущую лабораторную работу. Подключитесь к базе данных, получившейся в результате выполнения SCRIPT-файла.

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

3. Согласно методических указаний добавьте в SCRIPT-файл операторы, которые определяют генераторы ключевых полей и их стартовые значения, необходимые для получения значений атрибутов, используемых в качестве первичных ключей. Для базы данных «БИБЛИОТЕКА» это все поля таблиц с именем Code. Для каждого такого поля должен быть в SCRIPT-файле описан соответствующий генератор. Перед тем как добавить генераторы в SCRIPT-файл опробуйте их средствами утилиты WISQL.

4. Научитесь пользоваться оператором INSERT, добавляющим новую запись в таблицу, оператором UPDATE, изменяющим значения атрибутов в уже существующих записях и оператором DELETE, удаляющим записи из таблицы в утилите WISQL. Обратите особое внимание на то, что если в операторах UPDATE и DELETE опустить WHERE <�условие поиска>, в таблице будут соответственно изменены или удалены все записи. Для проверки результатов работы операторов DELETE, UPDATE и DELETE используйте оператор SELECT следующего формата:

SELECT * FROM <�Имя таблицы к которой были применены DELETE, UPDATE или DELETE >

Этот оператор выведет на экран в информационной части утилиты WISQL значения всех атрибутов всех записей таблицы, к которой были применены DELETE, UPDATE или DELETE.

5.Добавьте SCRIPT-файл необходимое количество операторов INSERT, для добавления в каждую таблицу базы данных, обслуживающую технологический процесс согласно индивидуального задания, 10 — 20 записей. Информация в таблицах должна быть осмысленная и отвечать назначению технологического процесса. Перед добавлением оператора INSERT в SCRIPT-файл, рекомендуется проверять его работоспособность в утилите WISQL.

Те кто выбрал для себе базу данных «БИБЛИОТЕКА», вводят в SCRIPT-файл операторы INSERT, которые обеспечат наличие в таблицах базы данных информацию согласно табл. 5.1 — 5.10.

информация таблица база данные

Таблица 5.1.

Таблица Readers

Таблица 5.2.

Таблица Librarians

Таблица 5.3.

Таблица Books

Таблица 5.4.

Таблица PasportData

Таблица 5.5.

Таблица Phones

Таблица 5.6.

Таблица BookAutors

Таблица 5.7

Таблица BookInventaryNumbers

Таблица 5.8.

Таблица BookGiveOutRecord

Таблица 5.9.

Таблица BookFunds

• Таблица 5.10.
• Таблица PhoneTypes

Code	Name
	Домашний
	Рабочий
	Мобильный

• 6.После операторов всех операторов INSERT, добавляющих в SCRIPT-файл оговоренную в п. 5 информацию добавьте оператор UPDATE, изменяющий информацию в одной записи таблицы с наибольшим числом атрибутов, затем с помощью оператора DELETE удалите эту запись и, на конец, добавьте ее вновь соответствующим оператором INSERT. Рекомендуется отлаживать этот процесс пошагово средствами утилиты WISQL.
• 7.Удостовертесь, что данный файл без ошибок создает базу данных на жестком диске рабочей станции (C:DB). Средствами утилиты WISQL убедитесь, что все объекты, какие вы описали в SCRIPT-файле, присутствуют в базе данных. Наличие добавленных Вами записей проверяйте с помощью оператора SELECT, формат которого описан в п. 4.
• 8. Оформите отчет согласно требованиям методических указаний.

Обеспечение поддержки целостности данных

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

· обязательные данные;

· ограничения для доменов атрибутов;

· целостность сущностей;

· ссылочная целостность;

· требования данного предприятия.

Большая часть этих требований задается при создании доменов (оператор CREATE DOMAIN) и таблиц (оператор CREATE TABLE).

Обязательные данные определяются фразой NOT NULL после описания типа данных столбца. Если мы не использовали бы домен для определения типа полей с именем Code, то фразу NOT NULL, можно было бы использовать при создании таблицы в операторе CREATE TABLE:

Code INTEGER NOT NULL

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

CREATE DOMAIN AllCode AS INTEGER

NOT NULL

Ограничения для доменов атрибутов обеспечивают еще две фразы оператора CREATE DOMAIN: DEFAULT и CHECK. Например для исключения попадания в поле, отражающее тираж книги была применена фраза CHECK:

CREATE DOMAIN AllDrawing AS INTEGER

CHECK (VALUE >= 10)

NOT NULL;

Целостность сущностей обеспечивает первичным ключом таблицы (фраза PRIMARY KEY оператора CREATE TABLE). Ссылочная целостность обеспечивается внешними ключами (фразы FOREIGN KEY и REFERENCES, которые используются только совместно оператора CREATE TABLE). Все это Вы использовали в предыдущей лабораторной работе, но не рассматривали варианты работы операторов добавляющих, обновляющих и удаляющих записи в таблице (INSERT, UPDATE и DELETE) в связи с наложенными ограничениями целостности.

Вспомним операторы CREATE TABLE, создающие таблицы BookAuthors и Books для базы данных «БИБЛИОТЕКА»:

/*Создаем таблицу в которой будут храниться сведения об авторах книг.*/

CREATE TABLE BookAuthors

(Code AllCode,

FamilyName FIO,

Name FIO,

Patronymic FIO,

Birthday DATE NOT NULL,

Deatheday DATE,

ShortBiography AllNote,

Note AllNote,

PRIMARY KEY (Code));

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

CREATE TABLE Books

(Code AllCode,

Name BookName,

AuthorCode AllCode,

IssueYear DATE NOT NULL,

Drawing AllDrawing,

UDK AllUDK,

Cipher AllCipher,

Note AllNote,

PRIMARY KEY (Code),

FOREIGN KEY (Code) REFERENCES BookAuthors);

Теперь система отклонит выполнение любых операторов INSERT или UPDATE, с помощью которых будет предпринята попытка создать в дочерней таблице Books значение внешнего ключа, не соответствующее одному из уже существующих значений потенциального ключа родительской таблицы BookAuthors. Действия системы, выполняемые при поступлении операторов UPDATE или DELETE содержащих попытку обновить или удалить значение потенциального ключа в родительской таблице, которому соответствует одна или более строк дочерней таблицы, зависят от правил поддержки ссылочной целостности, указанных во фразах ON UPDATE и ОN DELETE предложения FOREIGN KEY. Если пользователь предпринимает попытку удалить из родительской таблицы строку, на которую ссылается одна или более строк дочерней таблицы, язык SQL предоставляет следующих четыре допустимых варианта действий:

§ CASCADE — выполняется удаление строки из родительской таблицы, сопровождающееся автоматическим удалением всех ссылающихся на нее строк дочерней таблицы. Поскольку удаляемые строки дочерней таблицы также могут содержать некоторые потенциальные ключи, используемые в качестве внешних ключей в других таблицах, анализируются и применяются правила обработки внешних ключей этих таблиц — и так далее, каскадным образом;

§ SET NULL — выполняется удаление строки из родительской таблицы, а во внешние ключи всех ссылающихся на нее строк дочерней таблицы заносятся значения NULL. Этот вариант применим только в том случае, если в определении столбца внешнего ключа отсутствует ключевое слово NOТ NULL;

§ SET DEFAULT — выполняется удаление строки из родительской таблицы, а во внешние ключи всех ссылающихся на нее строк дочерней таблицы заносится значение, принимаемое по умолчанию. Этот вариант применим только в том случае, если в определении столбца внешнего ключа присутствует ключевое слово DEFAULT и задано значение, используемое по умолчанию;

§ NO ACTION — операция удаления строки из родительской таблицы отменяется. Именно это значение используется по умолчанию в тех случаях когда в описании внешнего ключа фраза ON DELETE опущена.

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

Под требованием данного предприятия подразумеваются ограничения обновлений данных в таблицах, вызванные требованиями (бизнес-правилами), установленными в отношении ручного выполнения операций, связанных с внесением изменений в информацию. Например в базе данных «БИБЛИОТЕКА» наложено ограничение на количество книг одновременно находящихся на руках одного читателя. Это правило может быть реализовано только с помощью триггеров или хранимых процедур, которые мы рассмотрим в следующей лабораторной работе.

Ограничения, обеспечивающие поддержку целостности данных, влияют на результаты выполнения операторов, добавляющих, модифицирующих и удаляющих информацию из таблиц базы данных — INSERT, UPDATE и DELETE. Поэтому при написании этих операторов следует понимать какие значения и в какой последовательности необходимо вводить в таблицы базы данных, чтобы не возникало исключительных ситуаций.

Определение значения поля первичного ключа с помощью генератора

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

Для создания генератора используется оператор

CREATE GENERATOR ИмяГенератора;

Для генератора необходимо установить стартовое значение при помощи оператора

SET GENERATOR ИмяГенератора ТО СтартовоеЗначение;

При этом СтартовоеЗначение должно быть целочисленным. Для получения уникального значения к генератору можно обратиться с помощью функции

GEN_ID (ИмяГенератора, шаг);

Эта функция возвращает увеличенное на шаг предыдущее значение, выданное генератором (или увеличенное на шаг стартовое значение, если ранее обращений к генератору не было).

Значение шага должно принадлежать диапазону −231…+231 -1.

ЗАМЕЧАНИЕ. Не рекомендуется переустанавливать стартовое значение генератора или менять шаг при разных обращениях к GEN_ID. В противном случае генератор может выдать неуникальное значение и, как следствие, будет возбуждено исключение «Дублирование первичного или уникального ключа» при попытке запоминания новой записи в ТБД.

Пример. Пусть в БД определен генератор, возвращающий уникальное значение для столбца Code в таблице BookAuthors:

CREATE GENERATOR GenCodeInBookAuthors;

SET GENERATOR GenCodeInBookAuthors TO 0;

Обращение к генератору непосредственно из оператора INSERT, которое добавит первую запись таблицы BookAuthors (см. рис. 5.1):

INSERT INTO BookAuthors

(Code, FamilyName, Name, Patronymic, Birthday, Deatheday)

VALUES (GEN_ID (GenCodeInBookAuthors, 1),

" Тихонов «, «Олег», «Николаевич «, NULL, NULL)

Добавление, изменение и удаление записей

Оператор INSERT применяется для добавления записей в объект. Он имеет следующий формат:

INSERT INTO <�объект> [(столбец1 [, столбец2 …1)]

VALUES (<�значение1> [, <�значение2> …])

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

Поставить в соответствие столбцам списки значений можно двумя способами. Первый состоит в явном указании значений после слова VALUES, второй — в формировании значений при помощи оператора SELECT.

Оператор UPDATE применяется для изменения значения в группе записей или — в частном случае — в одной записи объекта. В качестве объекта могут выступать ТБД или просмотр, созданный оператором CREATE VIEW. В последнем случае могут изменяться значения записей из нескольких таблиц.

Формат оператора UPDATE:

UPDATE <�объект>

SET столбец1 = <�значение1> [, столбец2 = <�значение2>…]

[WHERE <�условие поиска >]

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

ВНИМАНИЕ! Если опустить WHERE <�условие поиска>, в объекте будут изменены все записи.

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

Формат оператора DELETE:

DELETE FROM <�объект>

[WHERE <�условие поиска>];

ВНИМАНИЕ! Если опустить WHERE <�условие поиска>, в объекте будут удалены все записи.

Пример SCRIPT-файла.

/*Первый оператор устанавливает набор символов для текущей сессии. В нашем случае это всегда WIN1251.*/

SET NAMES WIN1251;

/*Если необходимо включаем оператор, который удаляет предыдущую версию базы данных «БИБЛИОТЕКА» на жестком диске. Для этого необходимо сначала подсоединиться к удаляемой базе данных, а затем ее удалить. Например: */

CONNECT «C:DBLIBRARY.GDB»

USER «S»

PASSWORD «s» ;

DROP DATABASE;

/*Оператор, который создает БД «БИБЛИОТЕКА», определяя ее пользователя и его пароль.*/

CREATE DATABASE «C:/DB/LIBRARY.GDB»

USER «S»

PASSWORD «s»

DEFAULT CHARACTER SET WIN1251;

/*Оператор, который подключает созданную базу данных, с указанием пользователя и его пароля.*/

CONNECT «C:DBLIBRARY.GDB»

USER «S»

PASSWORD «s» ;

/*Идут операторы, предназначенные для создания доменов, определенных в физической модели базы данных.*/

/*Домен предназначен для определения уникального номера строк для

всех отношений БД «Библиотека» (поле Code).*/

CREATE DOMAIN AllCode AS INTEGER

NOT NULL;

/*Домен предназначен для определения множеств всех фамилий, имен и отчеств людей БД «Библиотека» (поля FamilyName, Name, Patronymic).*/

CREATE DOMAIN FIO AS CHAR (30)

NOT NULL

COLLATE PXW_CYRL;

/*Домен предназначен для определения всех дополнительных сведений и автобиографий людей БД «Библиотека» (поля Note, ShortBiography).*/

CREATE DOMAIN AllNote AS BLOB;

/*Домен предназначен для определения множеств всех названий книг БД «Библиотека» (поле Name в таблице Books).*/

CREATE DOMAIN BookName AS CHAR (200)

NOT NULL

COLLATE PXW_CYRL;

/*Домен предназначен для определения множеств всех тиражей книг БД «Библиотека» (поле Drawing в таблице Books).*/

CREATE DOMAIN AllDrawing AS INTEGER

CHECK (VALUE >= 10)

NOT NULL;

/*Домен предназначен для определения множеств всех УДК БД «Библиотека» (поле UDK в таблице Books).*/

CREATE DOMAIN AllUDK AS CHAR (20)

NOT NULL

COLLATE PXW_CYRL;

/*Домен предназначен для определения множеств всех шифров книг БД «Библиотека» (поле Cipher в таблице Books).*/

CREATE DOMAIN AllCipher AS CHAR (10)

NOT NULL

COLLATE PXW_CYRL;

/*Далее идут операторы, которые создают таблицы базы данных.*/

/*Сначала создаем таблицы на которые есть ссылки внешних ключей — предложение FOREIGN KEY оператора CREATE TABLE. Из двух таблиц BookAuthors и Books первой должна быть создана таблица BookAuthors.*/

/*Создаем таблицу в которой будут храниться сведения об авторах книг.*/

CREATE TABLE BookAuthors

(Code AllCode,

FamilyName FIO,

Name FIO,

Patronymic FIO,

Birthday DATE NOT NULL,

Deatheday DATE,

ShortBiography AllNote,

Note AllNote,

PRIMARY KEY (Code));

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

CREATE TABLE Books

(Code AllCode,

Name BookName,

AuthorCode AllCode,

IssueYear DATE NOT NULL,

Drawing AllDrawing,

UDK AllUDK,

Cipher AllCipher,

Note AllNote,

PRIMARY KEY (Code),

FOREIGN KEY (Code) REFERENCES BookAuthors);

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

SET TERM ### ;

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

/*Восстанавливаем старый разделитель.*/

SET TERM; ###

/*Идут операторы, которые заносят информацию в базу данных*/

/*Определяем генератор для поля Code таблицы BookAuthors и устанавливаем его начальное значение.*/

CREATE GENERATOR BookAuthorsCode;

SET GENERATOR BookAuthorsCode TO 0;

/*Определяем генератор для поля Code таблицы Books и устанавливаем его начальное значение.*/

CREATE GENERATOR BooksCode;

SET GENERATOR BooksCode TO 0;

/*Добавляем 5 записей в таблицу BookAuthors согласно таблицы 5.6.

INSERT INTO BookAuthors

(CODE, FAMILYNAME, NAME, PATRONYMIC, BIRTHDAY)

VALUES

(GEN_ID (BookAuthorsCode, 1), 'Тихонов', 'Олег', 'Николаевич', '01/31/1940');

INSERT INTO BookAuthors

(CODE, FAMILYNAME, NAME, PATRONYMIC, BIRTHDAY)

VALUES

(GEN_ID (BookAuthorsCode, 1), 'Акуленко', 'Леонид', 'Дмитриевич', '12.03.1934');

INSERT INTO BookAuthors

(CODE, FAMILYNAME, NAME, PATRONYMIC, BIRTHDAY)

VALUES

(GEN_ID (BookAuthorsCode, 1), 'Процуто', 'Виктор', 'Сергеевич', '25.07.1941');

INSERT INTO BookAuthors

(CODE, FAMILYNAME, NAME, PATRONYMIC, BIRTHDAY)

VALUES

(GEN_ID (BookAuthorsCode, 1), 'Колосов', 'Геннадий', 'Евгеньевич', '29.10.1951');

INSERT INTO BookAuthors

(CODE, FAMILYNAME, NAME, PATRONYMIC, BIRTHDAY)

VALUES

(GEN_ID (BookAuthorsCode, 1), 'Казаков', 'Игорь', 'Елисеевич', '15.08.1947');

COMMIT;

Отчет должен содержать:

1. Номер, наименование и цель лабораторной работы.

2. Таблицы с информацией, которую Вы добавили в базу данных. Для тех кто работает с базой данных «БИБЛИОТЕКА» наличие таблиц 5.1 — 5.10 обязательно.

3. SCRIPT-файл.