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

В таблицах InterBase  нет автоинкрементного типа данных, который удобно было бы использовать при построении первичных ключей, чтобы однозначно определять любую запись в таблице. Для помещения в такие столбцы уникальных значений, в InterBase предусмотрен специальный механизм генераторов. Для каждого автоинкрементного столбца БД создается свой генератор. Генератор – это хранящаяся в БД программа, выдающая при каждом обращении к ней уникальное число, последовательность создания которого следующая:

1) создание генератора оператором:

Create Generator Имя_генератора

2) установка начального значения генератора оператором:

Set Generator Имя_генератора To Нач_значение

3) создание триггера, устанавливающего в автоинкрементное поле значение, полученное от генератора:

Create Trigger Имя_триггера For Имя_таблицы

Active Before Insert  {активизация триггера перед вставкой новой записи}

As

Begin

    New.Имя_автоинкр_поля   = Gen_ID(Имя_генератора, 1);

End

 Где  New – указывается при обращении из триггера к новому значению поля;

Gen_ID – встроенная функция, возвращающая текущее значение генератора, увеличенное на 1.

Операции изменения записей в таблицах

Добавление данных

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

insert into <имя_таблицы>[<имя_столбца>,<имя_столбца> , …] values ( <значение>, < значение >, . . .);

insert into Students values (11, ‘Комсомоленко’, ‘В-31’);

Каждое вводимое значение в предложении, должно совпадать с типом данных столбца, в который оно вставляется. Значение NULL, при вводе не заключается в одиночные кавычки.

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

Указание имен столбцов позволяет изменить порядок вставки значений в таблицу.

insert into Students (группа, фио) values (‘В-31’, ‘Репин’);

Использование оператора вставки с синтаксисом  insert into <имя_таблицы> <оператор Select> - позволяет выбирать данные из другой таблицы и вставлять их в данную. Эту возможность можно использовать для копирования всех данных одной таблицы в другую, причем эти таблицы могут быть созданными разными СУБД (попробуйте использовать эту возможность для переноса данных из таблиц формата Paradox во вновь созданные таблицы).

Редактирование данных осуществляется оператором:

Update <имя_таблицы> Set < <имя_поля>=<выражение>,<имя_поля>=<выражение>,…> Where <условие>

Наличие в операторе условия позволяет редактировать сразу множество записей, например при переименовании значения сразу во всей таблице. (А.с.644)

Удаление данных осуществляется оператором:

Delete from < имя_таблицы > Where <условие>

Наличие в операторе условия позволяет удалять сразу множество записей. Если не использовать секцию Where, то будут удалены все записи из таблицы.

Хранимые процедуры

Хранимая процедура – это отдельная программа, написанная на языке процедур и триггеров используемого сервера баз данных, и сохраненная как часть метаданных базы данных.

Существует две разновидности хранимых процедур: процедуры выбора (аналог Select-запросов) и исполняемые процедуры. Процедуры выбора возвращают наборы данных, которые состоят из строк таблицы или ее отдельных значений. Исполняемые процедуры не возвращают данные, т. к. они предназначены для исполнения команд, например Delete. Инструкция EXECUTE PROCEDURE осуществляет вызов процедуры для выполнения.

Оператор CREATE PROCEDURE определяет новую сохраненную процедуру в базе данных.

Сохраненные процедуры состоят из заголовка и тела.

Заголовок процедуры содержит:

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

Тело процедуры содержит:

• Факультативный список локальных переменных и их типов данных.
• Блок инструкций на языке процедур и триггеров InterBase, ограниченный BEGIN и END. Блок может включать в себе другие блоки, так, чтобы имелось несколько уровней вложения.

Важно: Так как каждая инструкция в теле сохраненной процедуры должна завершатся точкой с запятой, вы должны определить другой символ для завершения инструкции CREATE PROCEDURE в ISQL. Используйте SET TERM ^ перед CREATE PROCEDURE чтобы определить терминатор отличный от точки с запятой. После инструкции CREATE PROCEDURE, включите SET TERM ^, что бы изменить терминатор обратно к точке с запятой.

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

Язык процедур и триггеров InterBase это полный язык программирования для сохраненных процедур и триггеров. Он включает:

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

Синтаксис оператора для создания хранимой процедуры:

CREATE PROCEDURE ИмяПроцедуры

[(входной_параметр тип_ данных[,входной_параметр тип_ данных ...])]

  [RETURNS выходной_параметр тип_ данных [, выходной_параметр тип_ данных ...])]

  AS

DECLARE VARIABLE локальная_переменная  тип_данных;

[DECLARE VARIABLE локальная_переменная  тип_данных; ...]

BEGIN

<оператор>;

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

END

Входные параметры служат для передачи в процедуру значений из вызывающего приложения, выходные – для возвращения результирующих значений. Локальные переменные служат для временного хранения промежуточных результатов вычислений.

Если локальные переменные, входные или выходные параметры указываются в теле SQL-оператора, их именам должны предшествовать двоеточия.

Триггеры

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

Следующая таблица отражает расширения языка для триггеров:

Синтаксис

CREATE TRIGGER имя_ триггера FOR имя_ таблицы

  [ACTIVE | INACTIVE]

  {BEFORE | AFTER}

  {DELETE | INSERT | UPDATE}

  [POSITION номер]

  AS

  DECLARE VARIABLE локальная_переменная тип_ данных;

  [DECLARE VARIABLE локальная_переменная тип_ данных; ...]

BEGIN

  <оператор>;

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

END

Таблица 4. Описание аргументов оператора создания триггеров

Домены

Если в таблице присутствуют столбцы с одинаковыми характеристиками, то можно предварительно описать их тип и поведение с помощью домена.

Синтаксис

CREATE DOMAIN domain [AS] <datatype>

[DEFAULT {literal | NULL | USER}]

  [NOT NULL] [CHECK (<dom_search_condition>)]

  [COLLATE collation];

Например, создать тип Pol_type для таблицы Sotr.

Create domain Pol_type as char (3) collate PXW_CYRL;

Create table Sotr (

Fio char (20) not null,

Pol Pol_type,

otdel char (10),

dolj char (20),

primary key (Fio)

);

где Not null – столбцы ассоциированные с доменом обязательно должны содержать значение.

Ограничения на значения домена

Записываются в определении столбца и начинаются со слова check.

<огр_домена>={

value <оператор> <значение>

| value [not] between <значение 1> and <значение 2>

| value [not] like <значение> [escape <значение>]

| value [not] in ( <значение 1> [, <значение 2>…] )

| value is [not] null

| value [not] containing <значение>

| not <огр_домена>

| <огр_домена> or <огр_домена>

| <огр_домена> and <огр_домена>

}

где:

<оператор> = {= |<|>|<=|=>|!<|>!|<>|!=}

!< – не меньше, !> – не больше, != – не равно.

Value означает, что элементы считаются правильными.

Create domain ID_Type as integer check (value >=100); id_Type >= 100.

Between <значение 1> and <значение 2> – значение домена в интервале от значения 1 до значения 2, включая их.

Like <значение 1> [<значение 2>] – значение домена должно "походить" на значение 1.

Like "%USD" – вводимое значение должно оканчиваться на USD, все предыдущие значения не имеют.

Like "__04" ("_"– единичный символ) вводится значение четырьмя символами, два последних – 04.

Если "%" и "_" нужны как символы в Like, то указываются значения в Escape и заменяются другими символами.

Например, Summa должна заканчиваться %.

Create domain summa as char (10) check (like "%!%" escape "!");

После символа ! служебные символы (%) теряют свою силу и становятся обычными символами.

In (<значение 1>[,<значение 2>…]) – значение домена должно совпадать с одним из приведенных

параметров списка.

Create domain Pol_type as

char (3) check (value in ("муж","жен"));

Containing <значение> – значение домена должно иметь вхождения параметра <значение> в любом месте.

Например, в наименовании отдела вхождения 041 где угодно "отдел – 041002" или "00304192" и т. д.

Create domain otdel_type as

varchar (10) check (value containing "041" collate PXW_CYRL;

Starting [with] <значение> – значение домена должно начинаться с <значение>, например "041".

Большинство условий могут комбинироваться AND или OR или указывать NOT.

Check (value not between 1 and 100);

и т.д.

Работа с индексами

Первичный и внешний ключи строятся для обеспечения ссылочной целостности реляционно -связанных таблиц. Кроме этого, первичный ключ, выполняет функции уникальности своих значений. Для этих же целей используется и просто уникальный ключ.

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

Необходимость создания индексов возникает в случаях когда:

• часто производится поиск в БД;
• столбец (столбцы)) часто перечисляется в предложении where оператора select;
• часто строится объединение таблиц;
• часто производится сортировка (order by в операторе select).

Не рекомендуется строить индексы по столбцам или группами столбцов, которые:

• редко используются для поиска;
• часто меняют значение (надо часто обновлять индекс);
• содержит небольшое число вариантов значения.

Синтаксис оператора create index

Оператор

Create [unique] [asс| desс] Index <имя индекса> on <имя таблицы> (столбец 1,…);

Unique – уникальный индекс, не допускает одинаковых значений

Asс– сортировка полей индекса по возрастанию (по умолчанию)

desс– сортировка полей индекса по убыванию.

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

·        выполнять пересоздание и балансировку индекса оператором alter index <имя индекса> deactivate; (alter index <имя индекса> activate);

·        переписывать выбираемость индекса оператором set statistics;

·        уничтожать и вновь создавать индекс операторами drop index <имя индекса>;и create index.

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

Просмотры (Виды)

CREATE VIEW описывает вид данных, основанный на одной или более основной таблицы в базе данных. Возвращаемые строки определены инструкцией SELECT, в которой перечисляются столбцы исходных таблиц. В базе данных существует только определение вида; вид непосредственно не представляет собой сохраненные данные. Над видами допустимо выполнение операций выбора, project, join и union, как и над таблицами.

Пользователь, который создает вид - его владелец, и имеет все привилегии для этого, включая возможность предоставлять (GRANT) привилегии другим пользователям, триггерам и сохраненным процедурам.

Обратите внимание: Любые столбцы, используемые в значении выражения, должны присутствовать раньше, чем выражение может быть определено.

Предложение инструкции SELECT не должно включать предложение ORDER BY.

Вид модифицируем если:

• Он является подмножеством одиночной таблицы или другого модифицируемого вида.
• Все столбцы базовой таблицы, включенные в определение вида, допускают значения NULL.
• Инструкция SELECT вида не содержит подзапросов, предикат DISTINCT, предложение HAVING, агрегатных функций, определенных пользователем функций или хранимых процедур.

Если определение вида не удовлетворяет этим условиям, он используется,  только для чтения.

Синтаксис

CREATE VIEW имя_вида [(имя_столбца_вида [,имя_столбца_вида ...])]

  AS <select> [WITH CHECK OPTION];

Изменение определения данных

Оператор alter table позволяет:

. добавить определение нового столбца;

. удалить столбец из таблицы;

. удалить атрибуты целостности таблицы или отдельного столбца;

. добавить новые атрибуты целостности.

Эта команда не является частью стандарта ANSI; но это – широко доступная, и довольно содержательная форма, хотя ее возможности несколько ограничены. Она используется, чтобы изменить определение существующей таблицы. Обычно, она добавляет столбцы к таблице. Иногда она может удалять столбцы или изменять их размеры.

Добавление нового столбца в таблицу БД:

Alter table <имя таблицы> add <имя столбца1> [, <имя столбца 2>…];

Столбец будет добавлен со значением NULL для всех строк таблицы. Новый столбец станет последним по порядку столбцом таблицы.

Добавление новых ограничений целостности:

Alter table <имя таблицы> add [constraint <имя ограничения>] <определение целостности>;

Удаление столбца (столбцов) из таблицы:

Alter table <имя таблицы> drop <имя столбца1> [, <имя столбца 2>…];

Удаление ограничений целостности (уровень таблицы):

Alter table <имя таблицы> drop <ограничения целостности>;

Удаление таблицы целиком:

Drop table <имя таблицы>;

Удаление таблицы может быть блокировано для родительских таблиц, если есть дочерние.

Пусть имеем таблицу:

Create table sotr (

Id_sotr integer not null primary key,

Fio char (10),

Otdel varchar (10),

Doljnost char (10));

Пусть необходимо изменить характеристики столбца

fio c char (10) на varchar(25):

1 добавим в таблицу новый временный столбец fio_tmp:

alter table sotr

add fio_tmp char (10);

2 копируем данные из fio в fio_tmp:

update sotr

set fio_tmp = fio;

3 удаляем столбец fio:

alter table sotr

drop fio;

4 создаем новый стобец fio:

alter table sotr

add fio varchar (25);

5 переписываем данные:

update sotr

set fio = fio_tmp;

6 удаляем временный столбец:

alter table sotr

drop fio_tmp.

Задание к лабораторной работе:

1.      Выполнить анализ схемы данных разработанной ранее БД для исключения  возможности возникновения аномалий модификации данных.

2.      С целью доказательства нахождения разработанной схемы в 3НФ (или НФБК), для каждого отношения (таблицы) определить функциональные зависимости. При выявлении аномалий модели данных  выполнить ее нормализацию, следуя алгоритму нормализации.

3.      Создать БД на сервере InterBase и зарегистрировать ее с использованием приложения IBConsole.

4.      Создать алиас БД сервера InterBase с использованием приложения SQLExplorer.

5.      Познакомиться с возможностями приложений IBConsole и SQLExplorer при создании и выполнении команд SQL в интерактивном режиме.

6.      С использованием команд SQL определения данных создать следующие объекты БД:

·    домены для тех типов данных и ограничений, которые повторяются при определении  нескольких полей (определяются на основании анализа типов полей всех таблиц БД).

·    таблицы (с обязательным определением первичных и внешних ключей, ссылочной целостности связанных таблиц, с вычисляемыми полями и ограничениями на ввод допустимых значений в отдельные поля);

·    генераторы для ключевых полей таблиц, имеющих автоинкрементный тип данных;

·    триггеры для работы генераторов;

·    просмотры:

·    индексы;

·    хранимые процедуры;

·    триггеры для реализации правил бизнес – логики.

7. Познакомиться с синтаксисом операторов модификации объектов базы данных с помощью справочной системы интерактивного SQL.
8. Получить навыки работы при работе с операторами SQL модификации объектов БД.
9. Отработать в режиме интерактивного SQL операторы манипулирования данными.

При выполнении лабораторной работы в качестве примера для создания всех объектов БД использовать контрольный пример базы данных, входящий в поставку InterBase, хранящийся в  файле:

Program Files\Common Files\Borland Shared\Data\EMPLOYEE.GDB

Контрольные вопросы:

1. Что представляет собой Borland InterBase Server?
2. Как представлена БД в InterBase?
3. Что такое системные таблицы и для чего они нужны?
4. Как и с помощью какой программы производится регистрация пользователя сервера InterBase?
5. Что такое алиас БД? Как он создается?
6. Что такое язык SQL? Перечислите его составные части.
7. Язык SQL оперирует терминами, несколько отличающимися от терминов реляционной теории. Приведите примеры обозначения основных терминов в обоих случаях.
8. Что означает выражение: “Язык SQL является реляционно-полным”?
9. Для чего используется язык определения данных? Перечислите его основные команды.
10. Для чего используется язык управления данными? Перечислите его основные команды.
11. Перечислите операторы манипулирования данными.
12. Что такое ограничение целостности? Какие существуют типы ограничений целостности?
13. Какое значение должны иметь столбцы, по которым строится первичный ключ?
14. Что такое уникальный ключ?
15. Что такое внешний ключ? Для чего он предназначен?
16. Что такое генератор? Процесс создания, принцип действия и назначение.
17. Перечислите операторы добавления, редактирования и удаления данных.
18. Определение хранимой процедуры. Их разновидности, структура и назначение каждой.
19. Что такое триггеры? Их типы, структура.
20. Что такое домен? Ограничения на значения домена.
21. Каким образом производится работа с индексами?
22. Для чего используются виды (просмотры)?