Лабораторная работа № 5
Обеспечение поддержки целостности данных и добавление, удаление и изменение информации в таблицах
Цель работы
Научиться добавлять, редактировать и удалять записи таблиц базы данных с помощью операторов, которые предоставляет SQL, учитывая ограничения наложенные на таблицы, обеспечивающие ссылочную целостность информации в базе данных.
Ход работы
. В лабораторной работе №3 Вы определились, какую базу данных будете использовать в дальнейших лабораторных работах - базу данных технологического процесса согласно индивидуальному заданию или базу данных «БИБЛИОТЕКА». Для выполнения этой и последующих лабораторных работ Вы будете модифицировать свой вариант согласно методическим указаниям. Перед началом работы ознакомьтесь с методическими указаниями. Запустите на выполнение SCRIPT-файл, который Вы написали выполняя предыдущую лабораторную работу. Подключитесь к базе данных, получившейся в результате выполнения SCRIPT-файла.
. Научитесь пользоваться операторами, которые определяют генераторы ключевых полей и их стартовые значения, необходимые для получения значений атрибутов, используемых в качестве первичных ключей. Для этого используйте возможности утилиты WISQL.
. Согласно методических указаний добавьте в SCRIPT-файл операторы, которые определяют генераторы ключевых полей и их стартовые значения, необходимые для получения значений атрибутов, используемых в качестве первичных ключей. Для базы данных «БИБЛИОТЕКА» это все поля таблиц с именем Code. Для каждого такого поля должен быть в SCRIPT-файле описан соответствующий генератор. Перед тем как добавить генераторы в SCRIPT-файл опробуйте их средствами утилиты WISQL.
. Научитесь пользоваться оператором 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
| Code | FamilyNamе | Name | Patronymic | ReaderCardNumber | PasportCode | Job | Post | Note |
| Иванов | Петр | Иванович | ДГУ, каф. ЭВТ | Ассистент | blob | |||
| Федорец | Ирина | Олеговна | ДГУ, АХЧ | Вахтер | blob | |||
| Ильин | Иван | Петрович | ДГУ, каф. физики | Доцент | blob | |||
| Суренко | Дмитрий | Павлович | ДГУ, каф. геофизики | Ст. преподаватель | blob | |||
| Коршунова | Наталья | Юрьевна | ДГУ, каф.гео-информа-тики | Ассистент | blob | |||
| Носенко | Олег | Владими-рович | ДГУ, ИКК | Инженер | blob |
Таблица 5.2.
Таблица Librarians
| Code | Clock-Number | FamilyNamе | Name | Patronymic | Pasport-Code | Post | HomePhone | Note |
| Иванова | Елена | Владими-ровна | Библио-текарь | 52-07-75 | blob | |||
| Николаенко | Любовь | Николаев-на | Библио-текарь | 46-32-19 | blob | |||
| Иноземцева | Иванна | Модестов-на | Ст. биб- лиотекарь | 775-34-00 | blob | |||
| Мальцева | Диана | Петровна | Библио-текарь | 29-06-15 | blob | |||
| Сызранцева | Татьяна | Игоревна | Библио-текарь | 370-98-22 | blob | |||
| Ставка | Лилия | Ивановна | Библио-текарь | 22-00-01 | blob |
Таблица 5.3.
Таблица Books
| Code | Name | AuthorCode | IssueYear | Drawing | UDK | Cipher | Note |
| Автоматизация производственных процессов на обогатительной фабрике | 622.7-52 | 622.7-52/Т | Blob | ||||
| Решение задач по автоматизации процессов обогащения и металлургии | 622.7-52(075) | 622.7-52(075)/Т | BLOB | ||||
| Асимтотические методы оптимального управления | 681.513.5 | 681.513.5:/А | Blob | ||||
| Синтез оптимальных автоматических ситем | 681.513.5 | 681.513.5:/К | Blob | ||||
| Методы оптимизации стохастических систем | 681.513.5 | 681.513.5:/К | BLOB | ||||
| Автоматизированные системы управления технологическим процессом обогащения руды | 622.7-52 | 622.7-52/П | Blob |
Таблица 5.4.
Таблица PasportData
| Code | Series | Number | Birthday | Birthplace | Sex | IssuePlace | IssueDate | Note |
| АА | 30.05.1930 | Россия, г. Опочка | Жен. | Днепропетровск | 12.01.1995 | blob | ||
| АА | 23.02.1930 | Россия. г. Владимир | Жен. | г. Житомир | 16.03.2000 | blob | ||
| АБ | 20.01.1963 | Днепропетровская область, село Соленое | Жен. | Днепропетровская область, село Соленое | 10.01.1998 | blob | ||
| АЕ | 12.11.1960 | Украина, г. Донецк | Муж. | Донецк | 15.12.1991 | blob | ||
| АЕ | 19.07.1981 | Украина, Днепропетровск | Муж. | Днепропетровск | 25.08.1997 | blob | ||
| АЖ | 14.09.1956 | Казахстан, город Павлодар | Муж. | Киев | 24.05.1999 | blob | ||
| АЗ | 13.11.1970 | Днепропетровская область, г. Днепродзержинск | Жен. | Днепропетровская область, г. Днепродзержинск | 15.05.1998 | blob | ||
| АК | 05.01.1961 | Россия, город Самара | Жен. | Днепропетровск | 13.09.2000 | blob | ||
| АС | 10.10.1949 | Молдова, город Кишинев | Жен. | Днепропетровск | 13.12.1998 | blob | ||
| АЯ | 14.11.1950 | Украина, город Николаев | Жен. | г. Николаев | 11.11.1998 | blob | ||
| ИК | 19.07.1949 | Украина, г. Кировоград | Муж. | Днепропетровск | 6.01.1998 | blob | ||
| ИК | 18.07.1983 | Днепропетровская область, село Петропавловка | Жен. | Днепропетровская область, село Петропавловка | 20.09.1999 | blob |
Таблица 5.5.
Таблица Phones
| ReaderCode | PhoneTypeCode | PhoneNumber |
| 29-06-15 | ||
| 47-77-10 | ||
| 68-03-09 | ||
| 370-10-20 | ||
| 744-33-00 | ||
| 33-34-35 |
Таблица 5.6.
Таблица BookAutors
| Code | FamilyName | Name | Patronymic | Birthday | Deatheday | ShortBiography | Note |
| Тихонов | Олег | Николаевич | 31.01.1940 | NULL | blob | Blob | |
| Акуленко | Леонид | Дмитриевич | 12.03.1934 | NULL | BLOB | BLOB | |
| Процуто | Виктор | Сергеевич | 25.07.1941 | NULL | BLOB | Blob | |
| Колосов | Геннадий | Евгеньевич | 29.10.1951 | NULL | BLOB | Blob | |
| Казаков | Игорь | Елисеевич | 15.08.1947 | NULL | BLOB | BLOB |
Таблица 5.7
Таблица BookInventaryNumbers
| Code | BookCode | FundCode | InventaryNumber | Cost |
| 15,56 | ||||
| 22,33 | ||||
| 34,01 | ||||
| 12,99 | ||||
| 56,78 | ||||
| 10,10 |
Таблица 5.8.
Таблица BookGiveOutRecord
| Code | ReaderCode | OutLibrarian Code | InventaryCode | IssueDate | ReturnDate | FactReturnDate | InLibrarianCode |
| 11.09.2004 | 25.09.2004 | 24.09.2004 | |||||
| 02.09.2004 | 16.09.2004 | 11.12.2004 | |||||
| 02.09.2004 | 16.09.2004 | 16.09.2004 | |||||
| 30.10.2004 | 13.11.2004 | 10.01.2005 |
Таблица 5.9.
Таблица BookFunds
| Code | Name |
| НТБ | |
| Студенческий |
Таблица 5.10.
Таблица PhoneTypes
| Code | Name |
| Домашний | |
| Рабочий | |
| Мобильный |
.После операторов всех операторов INSERT, добавляющих в SCRIPT-файл оговоренную в п. 5 информацию добавьте оператор UPDATE, изменяющий информацию в одной записи таблицы с наибольшим числом атрибутов, затем с помощью оператора DELETE удалите эту запись и, на конец, добавьте ее вновь соответствующим оператором INSERT. Рекомендуется отлаживать этот процесс пошагово средствами утилиты WISQL.
.Удостовертесь, что данный файл без ошибок создает базу данных на жестком диске рабочей станции (C:\DB\). Средствами утилиты WISQL убедитесь, что все объекты, какие вы описали в SCRIPT-файле, присутствуют в базе данных. Наличие добавленных Вами записей проверяйте с помощью оператора SELECT, формат которого описан в п. 4.
. Оформите отчет согласно требованиям методических указаний.
Обеспечение поддержки целостности данных
Фактически, создавая домены и таблицы в прошлой лабораторной работе мы уже занимались обеспечением поддержки целостности данных нашего проекта. Существуют следующие типы ограничений, поддерживающие целостность в реляционной модели данных:
· обязательные данные;
· ограничения для доменов атрибутов;
· целостность сущностей;
· ссылочная целостность;
· требования данного предприятия.
Большая часть этих требований задается при создании доменов (оператор CREATE DOMAIN) и таблиц (оператор CREATE TABLE).
Обязательные данные определяются фразой NOT NULL после описания типа данных столбца. Если мы не использовали бы домен для определения типа полей с именем Code, то фразу NOT NULL, можно было бы использовать при создании таблицы в операторе CREATE TABLE:INTEGER NOT NULL
Например такие ограничения были наложены на поля, используемые в качестве первичных ключей при создании соответствующего домена:DOMAIN AllCode AS INTEGERNULL
Ограничения для доменов атрибутов обеспечивают еще две фразы оператора CREATE DOMAIN: DEFAULT и CHECK. Например для исключения попадания в поле, отражающее тираж книги была применена фраза CHECK:DOMAIN AllDrawing AS INTEGER(VALUE >= 10)NULL;
Целостность сущностей обеспечивает первичным ключом таблицы (фраза PRIMARY KEY оператора CREATE TABLE). Ссылочная целостность обеспечивается внешними ключами (фразы FOREIGN KEY и REFERENCES, которые используются только совместно оператора CREATE TABLE). Все это Вы использовали в предыдущей лабораторной работе но не рассматривали варианты работы операторов добавляющих, обновляющих и удаляющих записи в таблице (INSERT, UPDATE и DELETE) в связи с наложенными ограничениями целостности.
Вспомним операторы CREATE TABLE, создающие таблицы BookAuthors и Books для базы данных «БИБЛИОТЕКА»:
/*Создаем таблицу в которой будут храниться сведения об авторах книг.*/TABLE BookAuthors
(Code AllCode,FIO,FIO,FIO,DATE NOT NULL,DATE,AllNote,AllNote,KEY (Code));
/*Создаем таблицу в которой будут храниться сведения о книгах, которые числятся в фондах библиотеки.*/TABLE Books
(Code AllCode,BookName,AllCode,DATE NOT NULL,AllDrawing,AllUDK,AllCipher,AllNote,KEY (Code),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. Поэтому при написании этих операторов следует понимать какие значения и в какой последовательности необходимо вводить в таблицы базы данных, чтобы не возникало исключительных ситуаций.