Проверка операции удаления данных delete

Суть данной операции заключается в проверки правильности удаления данных, как из родительской таблицы OWNERS_1, так и из дочерних ей таблиц DOG_ и EXHIBITION_1. Выполним удаление владельца по фамилии Семен с номером 102 с помощью хранимой процедуры действия DELETE_OWNER:

EXECUTE DELETE_OWNER

ID_OWNER=’102’;

Состав таблиц до (слева) и после (справа) процедуры удаления на рис. 15, 16 и 17:

Р ис. …. Таблица OWNERS_1.

Рис. …. Таблица EXHIBITION_1.

Рис. …. Таблица DOG_1.

Таким образом, приведенные результаты тестирования подтверждают работоспособность созданной базы данных «Учет участников выставки собак» на относительно малом объеме данных и демонстрируют правильность разработанных средств обеспечения целостности и безопасности.

7. Листинг script-файла бд с комментариями

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

/*Домен предназначен для определения уникальных номеров строк для всех отношений БД «Учет участников выставки собак» (поля ID_DOG, ID_OWNER, ID_PARTICIPANT и т. д.).*/

CREATE DOMAIN "IDENT" AS INTEGER;

/*Домен предназначен для определения строк для отношений БД «Учет участников выставки собак» (поля DATE_DUNG, BIRTHDAY).*/

CREATE DOMAIN "ALLDATE" AS DATE;

/*Домен предназначен для определения множеств всех наименований БД «Учет участников выставки собак» (поля NICKNAME, BREED, SEX, OKRAS, FIRST_NAME, LAST_NAME, ADRESS, ).*/

CREATE DOMAIN AllName AS CHAR(30);

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

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

CREATE TABLE "DOG_1"

(

"ID_DOG" "IDENT" NOT NULL,

"NICKNAME" "ALLNAME",

"ID_OWNER" "IDENT",

"ID_BREED" "IDENT",

"ID_SEX" "IDENT",

"ID_COL" "IDENT",

"ID_HAIR" "IDENT",

PRIMARY KEY ("ID_DOG")

);

ALTER TABLE "DOG_1" ADD FOREIGN KEY ("ID_OWNER") REFERENCES OWNER ("ID_OWN");

ALTER TABLE "DOG_1" ADD FOREIGN KEY ("ID_BREED") REFERENCES BREED ("ID_BREED");

ALTER TABLE "DOG_1" ADD FOREIGN KEY ("ID_SEX") REFERENCES SEX ("ID_SEX");

ALTER TABLE "DOG_1" ADD FOREIGN KEY ("ID_COL") REFERENCES COLOUR ("ID_COL");

ALTER TABLE "DOG_1" ADD FOREIGN KEY ("ID_HAIR") REFERENCES HAIR ("ID_HAIR");

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

/* Table: OWNERS_1, Owner: SYSDBA */

CREATE TABLE "OWNERS_1"

(

"ID_OWN" "IDENT" NOT NULL,

"F_NAME" "ALLNAME",

"L_NAME" "ALLNAME",

"ADRESS" "ALLNAME",

"DATE_BIRT" DATE,

"ID_DWELL" INTEGER,

"ID_BANK" INTEGER,

"ID_DISTR" INTEGER,

PRIMARY KEY ("ID_OWNER")

);

ALTER TABLE "OWNER_1" ADD FOREIGN KEY ("ID_DWELL") REFERENCES DWELLING ("ID_DWELL");

ALTER TABLE "OWNER_1" ADD FOREIGN KEY ("ID_BANK") REFERENCES BANK ("ID_BANK");

ALTER TABLE "OWNER_1" ADD FOREIGN KEY ("ID_DISTR") REFERENCES DISTRICT ("ID_DIST");

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

/* Table: EXHIBITION_1, Owner: SYSDBA */

CREATE TABLE "EXHIBITION_1"

(

"ID_PARTICIPANT" "IDENT" NOT NULL,

"PRIZE" "ALLNAME",

"TAKEN_PLACE" INTEGER,

"ID_OWN1" "IDENT",

"ID_DOG1" SMALLINT,

PRIMARY KEY ("ID_PARTICIPANT")

);

ALTER TABLE "EXHIBITION_1" ADD FOREIGN KEY ("ID_OWN1") REFERENCES

OWNERS_1 ("ID_OWNER");

ALTER TABLE "EXHIBITION_1" ADD FOREIGN KEY ("ID_DOG1") REFERENCES

DOG_1 ("ID_DOG");

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

CREATE TABLE " DWELLING "

(

"ID_DWELL" "IDENT" NOT NULL,

"TYPE_DWELL" "ALLNAME",

PRIMARY KEY ("ID_DWELL")

);

/* и так далее аналогично по остальным таблицам -справочникам……………*/

/*Создание ролей*/

/*Роль 1. Менеджер БД*/

/* Role: "MANAGER_1", Owner: SYSDBA */

CREATE ROLE MANAGER_1;

GRANT ALL ON DOG_1 TO MANAGER_1;

GRANT ALL ON OWNERS_1 TO MANAGER_1;

GRANT ALL ON EXHIBITION_1 TO MANAGER_1;

/*Роль 2. Директор*/

CREATE ROLE DIRECTOR_1;

GRANT SELECT ON DOG_1 TO DIRECTOR _1;

GRANT SELECT ON OWNERS_1 TO DIRECTOR _1;

GRANT ALL ON EXHIBITION_1 TO DIRECTOR _1;

/*Роль 3. Любой пользователь БД*/

CREATE ROLE OTHER;

GRANT SELECT ON DOG_1 TO OTHER;

GRANT SELECT ON OWNERS_1 TO OTHER;

GRANT SELECT ON EXHIBITION_1 TO OTHER;

GRANT OTHER TO PUBLIC;

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

CREATE EXCEPTION UNKNOWN_DOG_ID 'Ошибка! Неизвестный номер собаки.';

CREATE EXCEPTION up_wrong_primary_key ‘Ошибка! Неправильный первичный ключ. ;

CREATE EXCEPTION up_wrong_foreign_key ‘Ошибка! Неправильный внешний ключ.’;

CREATE EXCEPTION up_wrong_data "’Ошибка! Недопустимые данные. ‘;

CREATE EXCEPTION up_wrong_data_duplicate ‘Ошибка! Дублирование данных. ;

CREATE EXCEPTION in_wrong_primary_key ‘Ошибка вставки! Неправильный первичный ключ.’ ;

CREATE EXCEPTION in_wrong_data_duplicate "Ошибка вставки! Дублирования данных. ";

CREATE EXCEPTION in_wrong_foreign_key "Ошибка вставки! Неправильный внешний ключ.";

CREATE EXCEPTION in_wrong_data "Ошибка вставки! Недопустимые данные. ";

/*Создаем генераторы.*/

CREATE GENERATOR ID_DOD_GEN;

CREATE GENERATOR ID_OWNER_GEN;

CREATE GENERATOR ID_PARTICIPANT_GEN;

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

SET TERM !! ;

CREATE TRIGGER IN_ID_DOG FOR DOG_1

BEFORE INSERT AS

BEGIN

NEW.ID_DOG = GEN_ID(ID_DOG_GEN, 1);

END !!

SET TERM !! ;

CREATE TRIGGER IN_ID_OWNER FOR OWNERS_1

BEFORE INSERT AS

BEGIN

NEW.ID_OWNER = GEN_ID(ID_ OWNER _GEN, 1);

END !!

SET TERM !! ;

CREATE TRIGGER IN_ID_PARTICIPANT FOR DOG_1

BEFORE INSERT AS

BEGIN

NEW.ID_ PARTICIPANT = GEN_ID(ID_ PARTICIPANT _GEN, 1);

END !!

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

CREATE TRIGGER IN_ID_DOG FOR DOG_1

BEFORE INSERT AS BEGIN

IF ((EXISTS (SELECT IN_DOG FROM DOG_1 WHERE Code = NEW.ID_DOG)) OR (NEW.ID_DOG NOT BETWEEN '201' AND '299')) THEN

BEGIN

EXCEPTION in_wrong_primary_key;

END

END

!!

CREATE TRIGGER IN_ID_OWNER FOR OWNERS_1

BEFORE INSERT AS BEGIN

IF ((EXISTS (SELECT IN_OWNER FROM OWNERS_1 WHERE ID_OWNER = NEW.ID_OWNER)) OR (NEW.ID_OWNER NOT BETWEEN '101' AND '199')) THEN

BEGIN

EXCEPTION in_wrong_primary_key;

END

END

!!

CREATE TRIGGER IN_ID_OWNER FOR EXHIBITION_1

BEFORE INSERT AS BEGIN

IF ((EXISTS (SELECT IN_PARTICIPANT FROM EXHIBITION _1 WHERE ID_ PARTICIPANT = NEW.ID_ PARTICIPANT)) OR (NEW.ID_ PARTICIPANT NOT BETWEEN '301' AND '399')) THEN

BEGIN

EXCEPTION in_wrong_primary_key;

END

END

!!

SET TERM ; !!

/*Процедура, обеспечивающая каскадное удаление данных при удалении владельца/

COMMIT WORK;

SET AUTODDL OFF;

SET TERM ^ ;

/* Хранимая процедура DELETE_OWNER */

CREATE PROCEDURE DELETE_OWNER

(

ID_OWNER INTEGER

)

AS

BEGIN EXIT; END ^

ALTER PROCEDURE DELETE_OWNER

(

ID_OWNER INTEGER

)

AS

BEGIN

/* Удаление владельца из таблицы DOG_1 */

DELETE FROM DOG_1

WHERE id_own = :id_owner;

/*

* Удаление владельца из таблицы EXHIBITION_1 */

DELETE FROM EXHIBITION_1

WHERE id_own1 = :id_owner;

/*

* Удаление записи в собственной таблице */

DELETE FROM OWNERS_1

WHERE id_owner = :id_owner;

SUSPEND;

END

^

SET TERM ; ^

COMMIT WORK;

SET AUTODDL ON;

/*Конец процедуры DELETE_OWNER */

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

/*Заполнение базы данных с помощью оператора INSERT*/

/*---------------------------------------------------------------------------*/

INSERT INTO DOG_1 (,NICKNAME,BREED,DATE_DUNG,SEX,WIDTH,OKRAS,ID_OWN) VALUES ( 'Шарик', 'дворняжка', '25.05.2005','кобель','серый', '102' );

/*---------------------------------------------------------------------------*/

/*Заполнение базы данных с помощью оператора INSERT*/

/*---------------------------------------------------------------------------*/

INSERT INTO OWNERS_1 (FIRST_NAME,LAST_NAME,ADRESS,BIRTHDAY)) VALUES ('Дмитрий', 'Махнюк', 'Октябрьская пл. 12 ', '12.03.1971');

/*---------------------------------------------------------------------------*/

/*Заполнение базы данных с помощью оператора INSERT*/

/*---------------------------------------------------------------------------*/

INSERT INTO EXHIBITION_1 (PRIZE,TAKEN_PLACE,ID_OWN1,ID_DOG1) VALUES ('200', '5', '103 ', '202');

/*------------------------------------------------------------------------------------------------------------*/