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

Вид работы:

Практическое задание
Предмет:

Информационное обеспечение, программирование
Язык:

Русский
,
Формат файла:
MS Word

12,49 Кб
Опубликовано:

2012-09-09

Все задачи по информационному обеспечению

Скачать практическое задание Читать текст online Заказать контрольную
*Помощь в написании! Посмотреть все задачи

Вы можете узнать стоимость помощи в написании студенческой работы.

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

Лабораторная работа № 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

CodeFamilyNamеNamePatronymicReaderCardNumberPasportCodeJobPostNote1ИвановПетрИванович3174ДГУ, каф. ЭВТАссистентblob2ФедорецИринаОлеговна281ДГУ, АХЧВахтерblob3ИльинИванПетрович134511ДГУ, каф. физикиДоцентblob4СуренкоДмитрийПавлович5436ДГУ, каф. геофизикиСт. преподавательblob5КоршуноваНатальяЮрьевна1288ДГУ, каф.гео-информа-тикиАссистентblob6НосенкоОлегВладими-рович56725ДГУ, ИККИнженерblob

Таблица 5.2.

Таблица Librarians

CodeClock-NumberFamilyNamеNamePatronymicPasport-CodePostHomePhoneNote128ИвановаЕленаВладими-ровна2Библио-текарь52-07-75blob212НиколаенкоЛюбовьНиколаев-на10Библио-текарь46-32-19blob3187ИноземцеваИваннаМодестов-на9Ст. биб- лиотекарь775-34-00blob483МальцеваДианаПетровна12Библио-текарь29-06-15blob510СызранцеваТатьянаИгоревна3Библио-текарь370-98-22blob6100СтавкаЛилияИвановна7Библио-текарь22-00-01blob

Таблица 5.3.

Таблица Books

CodeNameAuthorCodeIssueYearDrawingUDKCipherNote1Автоматизация производственных процессов на обогатительной фабрике1198560000622.7-52622.7-52/ТBlob2Решение задач по автоматизации процессов обогащения и металлургии11969200000622.7-52(075)622.7-52(075)/ТBLOB3Асимтотические методы оптимального управления21987 100000681.513.5681.513.5:/АBlob4Синтез оптимальных автоматических ситем4198450000681.513.5681.513.5:/КBlob5Методы оптимизации стохастических систем51987450000681.513.5681.513.5:/КBLOB6Автоматизированные системы управления технологическим процессом обогащения руды31987400000622.7-52622.7-52/ПBlob

Таблица 5.4.

Таблица PasportData

CodeSeriesNumberBirthdayBirthplaceSexIssuePlaceIssueDateNote1АА4500330.05.1930Россия, г. ОпочкаЖен.Днепропетровск12.01.1995blob2АА1570023.02.1930Россия. г. ВладимирЖен.г. Житомир16.03.2000blob3АБ8713420.01.1963Днепропетровская область, село СоленоеЖен.Днепропетровская область, село Соленое10.01.1998blob4АЕ1230012.11.1960Украина, г. ДонецкМуж. Донецк15.12.1991blob5АЕ0106719.07.1981Украина, ДнепропетровскМуж.Днепропетровск25.08.1997blob6АЖ0156814.09.1956Казахстан, город ПавлодарМуж.Киев24.05.1999blob7АЗ4318813.11.1970Днепропетровская область, г. ДнепродзержинскЖен.Днепропетровская область, г. Днепродзержинск15.05.1998blob8АК2349005.01.1961Россия, город СамараЖен.Днепропетровск13.09.2000blob9АС9084310.10.1949Молдова, город КишиневЖен.Днепропетровск13.12.1998blob10АЯ9076414.11.1950Украина, город НиколаевЖен.г. Николаев11.11.1998blob11ИК1084219.07.1949Украина, г. КировоградМуж.Днепропетровск6.01.1998blob12ИК4519018.07.1983Днепропетровская область, село ПетропавловкаЖен.Днепропетровская область, село Петропавловка20.09.1999blob

Таблица 5.5.

Таблица Phones

ReaderCodePhoneTypeCodePhoneNumber1129-06-152247-77-103168-03-0941370-10-2051744-33-006133-34-35

Таблица 5.6.

Таблица BookAutors

CodeFamilyNameNamePatronymicBirthdayDeathedayShortBiographyNote1ТихоновОлегНиколаевич31.01.1940NULLblobBlob2АкуленкоЛеонидДмитриевич12.03.1934NULLBLOBBLOB3ПроцутоВикторСергеевич 25.07.1941NULLBLOBBlob4КолосовГеннадийЕвгеньевич29.10.1951NULLBLOBBlob5КазаковИгорьЕлисеевич15.08.1947NULLBLOBBLOB

Таблица 5.7

Таблица BookInventaryNumbers

CodeBookCodeFundCodeInventaryNumberCost111456789015,56221451000022,33331453247734,01441451289012,99552467853256,78662463211210,10

Таблица 5.8.

Таблица BookGiveOutRecord

CodeReaderCodeOutLibrarian CodeInventaryCodeIssueDateReturnDateFactReturnDateInLibrarianCode124611.09.200425.09.200424.09.20043234402.09.200416.09.200411.12.20043364302.09.200416.09.200416.09.20041443630.10.200413.11.200410.01.20056

Таблица 5.9.

Таблица BookFunds

CodeName1НТБ2Студенческий

Таблица 5.10.

Таблица PhoneTypes

CodeName1Домашний2Рабочий3Мобильный

.После операторов всех операторов 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. Поэтому при написании этих операторов следует понимать какие значения и в какой последовательности необходимо вводить в таблицы базы данных, чтобы не возникало исключительных ситуаций.

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

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

Для создания генератора используется операторGENERATOR ИмяГенератора;

Для генератора необходимо установить стартовое значение при помощи оператораGENERATOR ИмяГенератора ТО СтартовоеЗначение;

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

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

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

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

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

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

(Code, FamilyName, Name, Patronymic, Birthday, Deatheday)(GEN_ID(GenCodeInBookAuthors,1),

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

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

Оператор INSERT применяется для добавления записей в объект. Он имеет следующий формат:INTO <объект> [(столбец1 [, столбец2 ...1)]

{VALUES (<значение1> [, <значение2> ...]) | <оператор SELECT>}

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

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

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

Формат оператора UPDATE:<объект>столбец1 = <значение1> [,столбец2 = <значение2>...]

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

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

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

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

Формат оператора DELETE:FROM <объект>

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

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

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

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

/*Если необходимо включаем оператор, который удаляет предыдущую версию базы данных "БИБЛИОТЕКА" на жестком диске. Для этого необходимо сначала подсоединиться к удаляемой базе данных, а затем ее удалить. Например: */"C:\DB\LIBRARY.GDB""S""s";DATABASE;

/*Оператор, который создает БД "БИБЛИОТЕКА", определяя ее пользователя и его пароль.*/DATABASE "C:/DB/LIBRARY.GDB""S""s"CHARACTER SET WIN1251;

/*Оператор, который подключает созданную базу данных, с указанием пользователя и его пароля.*/"C:\DB\LIBRARY.GDB""S""s";

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

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

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

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

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

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

/*Домен предназначен для определения множеств всех тиражей книг БД «Библиотека» (поле Drawing в таблице Books).*/DOMAIN AllDrawing AS INTEGER(VALUE >= 10)NULL;

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

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

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

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

/*Создаем таблицу в которой будут храниться сведения об авторах книг.*/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);

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

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

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

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

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

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

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