Проектирование структуры базы данных. Нормализация таблиц

 

Выбор значения поля Ow из набора данных quOwner

Контрольные задания:

1. Пояснить особенности редактирования фамилии владельца.
2. Почему в организации процесса редактирования договора использован компонент TQuery, а не Ttable.
3. Создать SQL-запрос на удаление записи о договоре.

 

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

Создание генераторов и триггеров. Каскадные воздействия.

Цель работы: научится создавать, ставить на выполнение, редактировать и удалять триггер; создавать, ставить на выполнение, редактировать и удалять генератор.

Триггеры

Триггер — это процедура, автоматически  исполняемая SQL-сервером при наступлении  события "обновление", "удаление" или "добавление" новой записи таблицы. По отношению к инициализирующему  их событию различают два типа триггеров:

• выполняемые до или после наступления события;
• автоматически обеспечивающие каскадные воздействия в дочерних таблицах при изменении, удалении записи в родительской таблице.

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

• Создание триггеров

Триггер создается SQL-командой:

CREATE TRIGGER ИмяТриггера FOR ИмяТаблицы

[ACTIVE/INACTIVE]

{BEFORE/AFTER}

{DELETE/INSERT/UPDATE}

[POSITION номер]

AS <тело триггера>[;]

Структура тела триггера:

[<объявление локальных  переменных процедуры>]

BEGIN

<оператор>

[<оператор>]

END

Значение  номера задается числом. Триггеры с  меньшими номерами выполняются раньше.

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

Ход работы:

1. Запустите утилиту Interactive SQL.
2. Создайте триггер updat_realty.

 

CREATE TRIGGER UPDAT_REALTY FOR Realty

ACTIVE

BEFORE UPDATE

AS BEGIN

IF (OLD.Adr <> NEW.Adr) THEN UPDATE Lease

SET Adr = NEW.Adr WHERE Adr = OLD.Adr;

END

 

3. Попробуйте изменить значение адреса в таблице Realty. Как видите, это не удается. ПОЯСНИТЕ.

 

 

 

 

 

Сообщение о  нарушении ссылочной целостности

 

 

 

Изменение значения адреса в таблице Realty

 

4. Удалите связь между таблицами Realty и Lease (ограничение INTEG_7):

ALTER TABLE Lease

DROP CONSTRAINT INTEG_7

5. Можете убедиться, что она отсутствует: теперь таблица Lease не связана с Realty.
6. Измените значение адреса в таблице Realty.

 

 

 

 

 

 

 

Просмотр  связей таблицы Lease

Как видите, теперь при изменении значения ее первичного ключа триггер автоматически изменяет значение внешнего ключа дочерней таблицы Lease. Другими словами, реализован механизм обеспечения ссылочной целостности "cascade".

• Создание генераторов

Генераторы  предназначены для обеспечения  уникальности значений ключевых столбцов и используются совместно с триггерами

Ход работы:

При создании таблицы Owner поле Non было объявлено как ключевое целочисленного типа.

1. Создадим генератор GenStore,  который при обращении к нему возвращает уникальное целочисленное значение:

 

CREATE GENERATOR GenStore

SET GENERATOR GenStore TO 1;

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

CREATE TRIGGER NZ_Non FOR Owner

ACTIVE

BEFORE INSERT

AS

BEGIN

NEW.Non=GEN_ID (GenStore,1);

END

Контрольные задания:

1. Создать триггер для родительской таблицы Owner, который при изменении значения ее первичного ключа будет автоматически изменять значение внешнего ключа дочерней таблицы Lease, т.е. если в таблице Owner изменилось значение поля NOn, то триггер изменит значение поля NOn в соответствующей записи таблицы Lease.
2. Создать триггер для каскадного удаления записей из таблицы Realty и Lease.

 

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

Создание хранимых процедур.

Цель  работы: научится создавать, ставить на выполнение, редактировать и удалять хранимую процедуру.

Хранимая процедура — это отдельная программа, написанная на процедурном языке используемого сервера баз данных. Существует две разновидности хранимых процедур: процедуры выбора (аналог SELECT-запросов) и исполняемые процедуры. Процедуры выбора возвращают наборы данных, которые состоят из строк или отдельных значений. Исполняемые процедуры не возвращают данные. Они предназначены для исполнения команд, например, delete. Синтаксис объявления хранимой процедуры:

 

CREATE PROCEDURE ИмяПроцедуры [(вхПараметр <тип>

[,вхПараметр <тип> ...])] [RETURNS (выхПараметр <тип>

[,выхПараметр  <тип> ...])]  
AS <телоПроцедуры> [;]

 

Для передачи процедуре значений из вызывающего  приложения используют вхПараметр. Для возвращения результатов хранимой процедуры — выхПараметр. Тело процедуры имеет формат:

 

[DECLARE VARIABLE имяПерем <тип>;

[DECLARE VARIABLE имяПерем <тип>,"   . . . ] ]

BEGIN

< оператор>

[...]

[<оператор>]

END

 

Ключевые  слова declare variable объявляют локальные переменные процедуры.

• Создание хранимой процедуры

В качестве примера  приведем процедуру выбора, которая  по значению номера арендатора (Tenant_No) возвращает все номера (Lease_ID) и даты(Lease_Date) заключенных с ним договоров.

Ход работы:

1. Сохраните  в текстовом файле StoredProc.sql код скрипта создания хранимой процедуры Get__Lease_Data.

Код скрипта StoredProc.sql

SET TERM ^;

CREATE PROCEDURE Get_Lease_Data ( Tenant__No SMALLINT)

RETURNS (Lease_ID SMALLINT, LeaseJDate TIMESTAMP)

AS

BEGIN

FOR SELECT DISTINCT Lease.NLease, Lease.LDate

FROM Tenant, Lease

WHERE Tenant.NTn = :Tenant_No AND Lease.NTn = Tenant.NTn

INTO :Lease_ID, :Lease_Date

DO

SUSPEND; END ^

 

Примечание: Поясним команду set term. Точка с запятой (;) для утилиты Interactive SQL означает конец оператора. Другими словами, каждый оператор, заключенный в разделители, должен быть исполнен. Чтобы этого не произошло в момент создания хранимой процедуры, команда set term назначает разделитель операторов ^ взамен точки с запятой. Он не инициирует исполнение команд.

2. Запустите утилиту Interactive SQL.
3. Исполните скрипт, используя команду Query/Load Script.

• Изменение и удаление хранимых процедур

Изменение хранимой процедуры производится оператором

 

ALTER PROCEDURE ИмяПроцедуры [(вхПараметр <тип>

[,вхПараметр <.тип > ...])]

[RETURNS(выхПараметр <тип>

[,выхПараметр  <тип> ...])] AS <тело процедуры>;

 

После выполнения оператора alter  procedure предыдущее определение процедуры заменяется новым определением параметров, переменных и тела процедуры. Для удаления хранимой процедуры из базы данных используется оператор: DROP PROCEDURE ИмяПроцедуры;

 

• Исполнение хранимых процедур

Запуск исполняемой  хранимой  процедуры  производят командой execute procedure, а процедуры выбора — select.

Ход работы:

Создадим исполняемую  хранимую процедуру Add_0wner.

1. Запустите утилиту Interactive SQL.
2. Соединитесь с базой данных Exmpl.gdb.
3. Создайте следующую исполняемую хранимую процедуру:

 

CREATE PROCEDURE Add_0wner (NOn SMALLINT, Ow CHAR(10), AdO CHAR(20))

AS

BEGIN

INSERT INTO OWNER (NOn, Ow, AdO)

VALUES (:NOn, :Ow, :AdO);

SUSPEND;

END

 

4. Исполните процедуру следующей командой:

 

EXECUTE PROCEDURE Add_0wner (4,'Моренко','Сумская 6')

 

5. Чтобы изменения вступили в силу, выполните команду Transactions/Commit.

• Исполнение хранимых процедур выбора.

Ход работы:

1. Запустите процедуру выбора Get_Lease_Data на исполнение следующей SQL-командой:

select * from Get_Lease_Data. Результат исполнения команды.

 

 

 

 

 

 

 

 

 

 

 

 

 

2. Измените значение  входного параметра на 2 и вы  увидите, что в отличие от  просмотра хранимая процедура позволяет реализовать динамический запрос.

• Запуск процедур выбора из приложения клиента

В случаях, когда сервер базы данных (например, Sybase) поддерживает метод OPEN компонента TStoredProc, то предпочтительнее использовать его. Для InterBase-сервера это не так, поэтому рассмотрим возвращение наборов данных с помощью компонента TQuery.

Ход работы:

1. Начните новый Delphi-проект.
2. Поместите компонент TQuery на форму.
3. Установите свойство DatabaseName равным алиасу базы данных — clsrvExmpl.
4. Запишите в свойстве SQL компонента TQuery вызов процедуры выбора. Например: Get_Lease_Data: SELECT  *  FROM Get_Lease_Data
5. Поместите на форму компоненты TDBGrid, TDataSource и определите их свойства.
6. Установите свойство Active компонента TQuery равным True или вызовите метод open.
 

• Возвращение данных

Хранимые  процедуры позволяют возвращать единичные данные, например, максимальное значение столбца. Такие процедуры  можно исполнять, используя компонент TQuery.

Ход работы:

1. Создайте хранимую процедуру, используя следующий скрипт:

CREATE  PROCEDURE GET_Last_Realt

RETURNS   (Last_Realt  CHAR(20)) AS

BEGIN

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

SELECT Adr FROM Lease

WHERE (LDate  IN   (SELECT MAX(LDate)   FROM Lease))

INTO :Last_Realt;

SUSPEND;

END

2. Начните новый Delphi-проект.
3. Поместите на форму компонент TQuery.
4. Определите его свойства.

 

Свойство	Значение
DataBase	clsrvExmpl
SQL	SELECT Last_Realt FROM GET_Last_Realt

 

5. Поместите на форму компоненты TDBGrid, TDataSource и определите их свойства.
6. Установите свойство Active компонента TQuery равным True или вызовите метод open.

Возвращение данных с помощью TQuery

• Возвращение результатов

Хранимые процедуры  можно применять для возвращения  некоторого результата. Например, суммарной стоимости аренды объектов одним из арендаторов.

Создайте  процедуру Get_SumRent, используя скрипт SumRent.sql

Код скрипта SumRent.sql

CREATE PROCEDURE Get_SumRent(Tenant CHAR(10))

RETURNS (Rent_SUM INTEGER)

AS

BEGIN

SELECT SUM(Rent.Rn)

FROM Tenant, Lease, Realty, Rent WHERE (Tenant.Tn = :Tenant)

AND (Lease.NTn = Tenant.NTn) AND (Realty.AdR = Lease.AdR) AND (Rent.Тур = Realty.Тур) GROUP BY Tenant.Tn, Rent.Rn INTO :Rent_SUM;

SUSPEND; END

 

Такие хранимые процедуры следует исполнять, используя компонент TStoredProc.

Ход работы:

1. Начните новый Delphi-проект.
2. Поместите на форму компонент TDatabase с вкладки BDE и назначьте его свойства

Свойство	Значение
AliasName	clsrvExmpl
DatabaseName	Exmpl
Params	User Name=SYSDBA PASSWORD=masterkey
LoginPrompt	False
Connected	True

 

 

 

 

 

 

 

 

3. Поместите компонент TStoredProc на форму.
4. Установите свойство DatabaseName равным имени компонента TDatabase — dbExmpl.

 

6. Используя раскрывающийся список, выберите имя хранимой процедуры GET_SUMRENT в свойстве StoredProcName.
7. Определите значение свойства параметра равным Петров.
8. Поместите на форму кнопку Итого и ассоциируйте с ней

следующий код:

 

Procedure TForml.ButtonlClick(Sender:   TObject);

begin

with StoredProcl  do begin Prepare;

{исполнить  хранимую процедуру} ExecProc;

{отобразить результат}

Editl.Text := ParamByName('Rent_SUM').AsString; end; end;

 

9. Поместите на форму компонент TEdit.
10. Сохраните проект под именем RetResStorProc.
11. Откомпилируйте приложение.
12. Щелкните на кнопке Итого.