Понятие данных, принципы построения информационных систем

Использование представлений имеет  глубокий смысл, который формулируется в правиле №7 Кодда для реляционных баз данных:

«База данных должна быть доступна конечным пользователям только через представления».

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

Для разработчика использование представлений  упрощает разработку сложных SQL-запросов, которые можно строить на основе представлений и отлаживать по частям.

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

4.5.2. Создание и удаление представлений

Поскольку представление является объектом базы данных, для его создания используется стандартная команда языка DDL CREATE... CREATE VIEW имя [(списокстолбцов)] AS SELECT ...// любой запрос на выборку

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

Например, создадим представление, содержащее столбцы  «код студента, фамилия студента, его средний балл» : CREATE VIEW studmark (codst, namest, avgmark) AS

SELECT st.cod._st, st.namest, AVG(m.mark) FROM students st LEFT JOIN marks m ON st.cod_st=m.cod_st GROUP BY st.codst, st.namest

При создании представления пользователю не возвращается виртуальная таблица, которую он обычно получает при выполнении SELECT... Вместо этого он получит лаконичное сообщение типа «Представление создано».

Для того, чтобы материализовать представление, т.е. получить данные в виде виртуальной таблицы, необходимо выполнить запрос SELECT к представлению так же, как к обычной таблице: SELECT * FROM stud_mark

Можно написать и любой другой запрос на выборку к представлению: SELECT codsub, avgmark FROM studmark WHERE cod_st=123

Удаляется представление также  стандартными средствами DDL: DROP VIEW имяпредставления

Выполнив  последовательно команды DROP VIEW..., а затем снова CREATE VIEW ... с тем же самым запросом SELECT, мы не напрасно потеряем время. Во многих случаях СУБД сформирует новый план ис полнения того же самого запроса в соответствии с изменившимся наполнением таблиц или появлением новых индексов. Это приведет к сокращению времени материализации представления.

СУБД  Oracle позволяет изменить план исполнения запроса без удаления представления, используя команду: ALTER VIEW имя RECOMPLILE

4.5.3. Обновление представлений

На некоторые представления  можно писать запросы UPDATE, DELETE и INSERT, как на обычные таблицы. При выполнении таких запросов реально все изменения вносятся в физические таблицы.

Такие представления называются обновляемыми. Согласно стандарту SQL, обновляемыми являются представления, основанные на запросах:

1. только по одной таблице
2. запрос не должен содержать ключевых слов DISTINCT, GROUP BY, HAVING
3. не должен содержать вложенных запросов
4. не должен быть комбинированным, т.е. не должен содержать операций UNION, INTERSECT, EXCEPT (MINUS).

Oracle, дополнительно, не позволяет обновлять запросы с сортировкой результатов.

Таким образом, обновляемых представлений не так  и много:

• представления с отбором столбцов (вертикальные представления)
• представления с отбором строк (горизонтальные представления)
• представления с отбором строк и столбцов (смешанные представления).

Например, создадим горизонтальное представление  на основе выборки из таблицы оценок marks, содержащее только оценки по Математике.

Предварительно  мы выяснили из таблицы предметов  subjects, что математика имеет код 1.

Тогда для создания представления  потребуется выполнить команду: CREATE VIEW markl AS

SELECT *FROM marks WHERE cod_sub=l

Это представление  будет обновляемым, поэтому преподаватель  математики, которому разрешено обновление этого представления, может изменить любую (одну) оценку, например, таким запросом: UPDATE markl SET mark=5 WHERE cod_st=123

Однако запрос: UPDATE markl SET cod_sub=2 WHERE cod_st=123

который также, как и первый, является разрешенным, приведет к неожиданным последствиям. Обновленная строка окажется за пределами представления markl и запрос: SELECT * FROM markl эту строку не покажет.

Если  такой побочный эффект нежелателен, в команду CREATE VIEW ... следует добавить дополнительную фразу WITH CHECK OPTION:

DROP VIEW markl CREATE VIEW markl AS

SELECT *FROM marks WHERE cod_sub= 1 WITH CHECK OPTION

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

Если говорить о данном конкретном примере, то можно было бы поступить еще проще: не включать столбец codsub в представление, тогда у преподавателя математики просто не будет никакой возможности изменить (нечаянно или преднамеренно) код своего предмета.

 
Триггеры. 
Триггеры

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

Особенности триггеров

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

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

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

Такой триггер  характеризуется следующими признаками, которые должны быть заданы при его  создании:

•     уникальное имя триггера (задание  параметров не требуется, поскольку триггер - процедура без параметров);

• активизирующее действие - команда, которая вызывает запуск триггера, такими командами являются INSERT, DELETE, UPDATE;
• время активизации - выполнение триггера до (BEFORE) или после (AFTER) выполнения активизирующего действия;
• область действия - выполнение триггера либо один раз для каждого оператора модификации таблицы, либо для каждой строки (в последнем случае следует добавить фразу FOR EACH ROW);
• условие активизации - необязательное дополнительное условие, которое должно выполняться для запуска триггера (фраза WHEN);
• тело   триггера   -   действия,   выполняемые   триггером   (блок PL/SQL).

На каждое событие может быть создано и  несколько триггеров. Однотипные триггеры выполняются в порядке их создания.

Действие, выполняемое в триггере, может включать в себя операции INSERT, DELETE, UPDATE, которые, в свою очередь, могут запускать выполнение того же или других триггеров. Такое явление называется каскадированием триггера.

Команды SQL для работы с триггерами

Триггер создается при помощи команды  SQL: CREATE [OR REPLACE] TRIGGER имя_триггера время_активизации активюирующая_команда ON имя_таблицы [FOR EACH ROW]

[WHEN дополнительное условие запуска триггера]

AS

Блок PL/SQL

В теле триггера можно использовать любые операторы  PL/SQL, кроме операторов SQL, которые изменяют ту таблицу, для которой был создан данный триггер. Любые другие таблицы изменять можно.

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

:NEW - новое значение строки, применяется для команд INSERT и UPDATE

:OLD - старое значение строки (до модификации), применяется для команд DELETE и UPDATE

Если  триггер благополучно создан, далее  он будет запускаться сам при  любом наступлении активизирующего  события.  Удалить триггер можно  при помощи команды DROP TRIGGER имя триггера

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

Для временного отключения триггера в Oracle можно применить команду: ALTER TRIGGER имя триггера DISABLE

Чтобы снова включить существующий триггер, используют команду: ALTER TRIGGER имя триггера ENABLE

Примеры триггеров

1. Триггер на вставку нового студента

При вставке  новой строки в таблицу триггеры часто используются для задания  таких значений по умолчанию, которые  нельзя определить при создании таблицы  с помощью фразы DEFAULT. В Oracle триггер на вставку чаще всего используется для автоматического задания значений первичного ключа. В стандарте SQL 2003 для этих целей имеется специальное ключевое слово IDENTITY, но в Oracle оно не поддерживается.

Вместо  этого имеется специальный объект SEQUENCE, который предназначен для формирования последовательных целых чисел (этот объект зафиксировн в стандарте SQL 2003). Для обращения к значениям последовательности в выражении SQL используются псевдостолбцы CURRVAL и NEXTVAL. CURRVAL возвращает текущее значение. NEXTVAL инкрементирует текущее значение и возвращает результат, при этом он становится текущим значением. Триггер на вставку берет из последовательности очередное значение и помещает его в новую строку, используя предопределенную переменную :NEW.

Например, создадим последовательность для формирования кодов студентов:

CREATE SEQUENCE stud_seq

Теперь создадим триггер на вставку новой строки в таблицу students: CREATE TRIGGER st_keys BEFORE INSERT ON students FOR EACH ROW BEGIN

SELECT stud_seq.NEXTVAL INTO :NEW.cod_st FROM dual; END;

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

 

. Триггеры на удаление студента

Триггер на удаление должен предусмотреть перенос удаляемых  данных в архивную базу данных. При создании таблиц нашей базы данных вместе с удалением студента было предусмотрено и каскадное удаление оценок студента, поэтому мы можем написать аналогичные триггеры BEFORE DELETE на таблицы students и marks, используя возможность каскадирования триггеров. При удалении студента одна единственная команда удаления, полученная сервером, например: DELETE FROM students WHERE cod_st=125

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

Предположим,    что   уже    созданы   таблицы   archivestudents    и archivemarks. Создадим триггер на удаление из таблицы students: CREATE TRIGGER st_del BEFORE DELETE ON students FOR EACH ROW BEGIN

INSERT INTO archivestudents

VALUES(:OLD.cod_st, :OLD.name_st, :OLD.born, :OLD.phone); END;

Триггер на удаление из таблицы  оценок выглядит аналогично, фраза FOR EACH ROW вызовет его срабатывание при удалении каждой оценки студента.

3. Триггер на изменение оценки

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

В нашей демонстрационной базе данных, очевидно, имеет смысл контролировать изменение уже выставленной оценки. Поэтому создадим специальную таблицу  changemarklog (журнал изменений оценок), которая будет содержать столбы:

• nameuser (имя пользователя, изменившего оценку)
• datechange (дата изменения оценки)
• codst (код студента)
• codsub (код предмета)
• oldmark (старая оценка)
• newmark (новая оценка)

 

Теперь создадим триггер на обновление: CREATE TRIGGER markchange AFTER UPDATE ON marks FOR EACH ROW BEGIN

IF :OLD.mark<> :NEW.mark THEN INSERT INTO changemarklog

VALUES(user,      sysdate,      :OLD.cod_st,      :OLD.cod_sub,      :OLD.mark, :NEW.mark); END IF; END;

Проверка работоспособности триггера

Для проверки работоспособности  триггера нужно выполнить команду, активизирующую триггер.

Например, для последнего триггера, который  заполняет журнал изменения оценок: UPDATE marks SET mark=3 WHERE mark=2

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