Файл: Лабораторные работы по курсу Базы данных Введение.pdf

В таблице 7.1 приведены специальные переменные, которые могут быть использованы в триггерных функциях.
Таблица 7.1 – Специальные переменные в триггерных функциях
Имя
Тип данных
Описание
NEW RECORD
Новая запись базы данных, созданная командой INSERT или UPDATE при срабатывании триггера уровня записи
(ROW). Переменная используется для модификации новых записей
OLD RECORD
Старая запись базы данных, оставшаяся после выполнения команды INSERT или UPDATE при срабатывании триггера уровня записи (ROW)
TG_NAME name Имя сработавшего триггера
TG_OP text Строка INSERT, UPDATE, DELETE в зависимости от операции
TG_RELNAME name
Имя таблицы, в которой сработал триггер

Задание на лабораторную работу №7
Данная лабораторная работа не является обязательной для выполнения, однако необходима для получения оценки выше «4» на экзамене.
1. Создайте отношение A. Для этого отношения определите два поля: поле – номер (тип число) и поле – строка_значений (тип одномерная матрица). Таблица будет иметь следующую структур: номер_матрицы строка_значений
1 ‘{2,4}’
1 ‘{-3,1}’
2 ‘{0}’
2 ‘{5}’
3 ‘{-2,-4}’
Что соответствует матрицам
, и
⎟⎟
⎠
⎞
⎜⎜
⎝
⎛
−
=
1 3
4 2
1
A
⎟⎟
⎠
⎞
⎜⎜
⎝
⎛
=
5 0
2
A
(
)
4 2
3
−
−
=
A
2. Создайте отношение B, содержащее четыре поля: номер_операции
(целое), номер_первой матрицы (целое), номер_второй матрицы (целое), название_функции (текст).
3. Создайте третье отношение – C, которое имеет поля: номер_операции
(целое) и поле результат (numeric).
4. Создайте функции сложения, вычитания, транспонирования, умножения матриц, которые работают с матрицами, определенными в отношении A, т.е. на вход получают номер матрицы (или матриц – для бинарных операций).
5. Создайте функцию вычисления определителя матрицы по ее номеру.
6. Создайте триггер, который:
• при добавлении новой строки в отношение B (с новым номером_операции) производит расчет для этой операции, вызвав соответствующую функцию для переданных матриц, и записывает полученный результат в отношение C в виде нового кортежа;
• при обновлении строки в отношении B производит пересчет, согласно новых переданных параметров, если пересчет произведен без ошибок, то обновляет соответствующий кортеж в отношении C;
• при выборе строки из отношения B производит пересчет, согласно существующих параметров.
Прием работы
Прием происходит при наличии оформленного отчета и работающей БД.
Вопросы
1. Чем отличается использование атрибута %ROWTYPE от типа RECORD?
2. Что такое PL/pgSQL и из каких блоков состоит процедура на этом языке?
3. Что такое триггеры и триггерные функции?
4. Как можно вставить данные в переменную типа RECORD?
5. Какие циклы существуют в языке PL/pgSQL.

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

Тема: Объектно-ориентированный подход в создании БД с использованием объектно-реляционной СУБД PostgreSQL.
Цель: моделирование объектного подхода на реляционной БД.
Навыки и умения: написание хранимых процедур на языке PL/pgSQL, создание представлений, написание триггеров и триггерных функций, написание агрегатных функций.
Теоретический базис
Создание агрегатных функций
СУБД PostgreSQL позволяет создавать собственные агрегатные функции для применения их в запросах группировки. Для определения новой агрегатной функции в базе данных служит конструкция CREATE
AGGREGATE. Синтаксис конструкции следующий:
CREATE AGGREGATE имя ( BASETYPE = входной_тип
[ , SFUNC - функция. STYPE = переходный_тип ]
[ , FINALFUNC - завершающая_функция ]
[ , INITCOND - начальное_состояние ] )
Расшифровка параметров:
• имя. Имя создаваемой агрегатной функции;
• входной_тпип. Тип входных данных, с которыми работает создаваемая функция. Если агрегатная функция игнорирует входные данные (как, например, функция count()), вместо типа данных указывается строковая константа ANY;
• функция. Имя функции, вызываемой для обработки всех входных данных, отличных от NULL. Обычно такая функция получает два аргумента: первый аргумент относится к типу данных переходный_тип, а второй — к типу данных входной_тип. Если агрегатная функция не анализирует входные данные, она получает только один аргумент типа переходный_тип. Так или иначе, функция должна возвращать значение типа переходный_тип;
• переходный_тип. Промежуточный тип данных агрегатной функции;
• заверштощая_функция. Имя итоговой функции, вызываемой для вычисления результата агрегатной функции после обработки всех входных данных. Функция должна получать один аргумент типа переходный_тип. Выходной тип данных агрегатной функции определяется типом возвращаемого значения этой функции. Если параметр FINALFUNC не указан, то последнее переходное значение передается в качестве результата агрегатной функции, а выходной тип данных определяется типом переходный_тип;

• начальное_состояние. Начальное состояние промежуточного значения агрегатной функции. Задастся литералом типа переходный_тип. Если параметр начальное_состояние не задай, промежуточное значение инициализируется псевдозначением
NULL.
Агрегатные функции характеризуются в первую очередь типом входных данных. Допускается существование двух и более агрегатных функции с одинаковыми именами, вызываемых с разными тинами данных
(это называется перегрузкой функций).
Пример 8.1:
В следующем примере определяется агрегатная функция sum(), работающая с текстовыми данными. Она вызывает встроенную функцию
PostgreSQL – textcat(text, text) для конкатенации всего текста во входных данных:
CREATE AGGREGATE sum ( BASETYPE = text,
SFUNC = textcat,
STYPE = text,
INITCOND = '' );
Для проверки работы созданной в примере 8.1 функции-агрегата выполните запрос и разберитесь, что он делает:
SELECT substring(t2.r,1,length(t2.r)-1)
FROM (SELECT sum(t.title || ',') as r
FROM (SELECT * FROM (SELECT 'world' as title union SELECT 'Hello')
as t1 order by t1.title) as t) as t2

Задание на лабораторную работу №8
Данная лабораторная работа не является обязательной для выполнения, однако необходима для получения оценки выше «4» на экзамене.
Необходимо:
1. Создать несколько отношений, связанных в виде иерархии, как это показано на рис. 8.1: сущность
Рисунок 8.1 – Структура объектно-реляционных связей
2. Самостоятельно определить атрибуты этих отношений.
3. Иерархию реализовывать с использованием наследования (л/р 6).
4. Создать представление, которое выбирает все атрибуты объекта и его наследников в один кортеж. В случаи, если для какого-то из атрибутов имеется несколько значений необходимо формировать поле в следующем виде:
{‘<1ое значение атрибута>’, ‘<2ое значение атрибута>’, …}, где
- значение атрибута в i-ом кортеже для объекта. Для этого написать собственную агрегатную функцию(ии), работающую с типами integer, text, timestamp.
5. Определить универсальные функции для удаления, добавления, обновления любого объекта, которым передается имя отношения, фильтр (если нужно), массив имен полей (если нужно), массив новых значений полей (если нужно).
6. На основании функций из п.5 определить для каждого объекта
БД (кроме «сущность») функции: добавить, изменить, удалить. В функциях должен быть реализован контроль за уникальностью объекта.
7. Запретить добавление данных в отношения с использованием
SQL запросов (т.е. не через интерфейсные функции из пункта 5).
Для этого определить необходимые триггеры.
8. Написать функции (PL/PGSQL) вывода существующих документов человека (например, паспорт, з/к – если студент, № пропуска – если преподаватель). Функция использует представление созданное в пункте 4. Не использовать внешние ключи (реляционные связи) для связывания отношений находящихся в одной ветке иерархии, но использовать их (если человек документ студент
Тип документа преподаватель

необходимо) для связи объектов на одном уровне иерархии.
Значение потенциального ключа в базовых таблицах не должно повторяться даже при выполнении запроса без параметра ONLY.
9. Структура БД, ограничения, правила наследования, процедуры, представления, а также данные должны быть представлены в виде SQL-скрипта.

Список используемой литературы
1. Дейт К.Дж. Введение в системы баз данных. М.: Вильямс, 2000.
2. Харрингтон Дж. Проектирование объектно-ориентированных баз данных. М.: Вильямс, 2000.
3. Грабер М. SQL Справочное руководство. 2-е изд. – М.: Издательство
«Лори», 2001.
4. Астахова И.Ф. SQL в примерах и задачах: Учеб. пособие / И.Ф.
Астахова, А.П. Толстобров, В.М. Мельников. – Мн.: Новое знание,
2002.
5. Вейскас Дж. Эффективная работа с Microsoft Access 2000. - СПб.:
Питер, 2000.
6. Уорсли Дж., Дрейк Дж. PostgreSQL. Для профессионалов. - СПб.:
Питер, 2003.