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

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

Механизм представлений является мощным средством СУБД, позволяющим скрыть реальную структуру БД от некоторых пользователей за счет определения его собственного представления БД. Представление дает возможность пользователю работать только с теми данными, которые ему нужны,  и скрыть служебные данные, которые ему неинтересны (например, шифры).

Реально представление является хранимым в БД запросом, а для пользователя ничем не отличается от базового отношения БД. При изменении данных в таблице они автоматически изменяются и в представлении, что обеспечивает актуальное состояние данных в представлении.

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

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

Представление создается командой CREATE VIEW, после которой указывается имя представления,   а затем следует запрос, формирующий тело представления.

Создание представлений рассматривается на примере БД, содержащей следующие таблицы:

STUDENTS (NOM_ZACH,SFAM,SNAME, STIP)

Рекомендуемые материалы

PREDM (PKOD, PNAME, TNUM,  HOURS, COURS)

                                    

 PREP (TNUM,TFAM,TNAME, TSIRNAME)

USP (NOM_ZACH, PKOD, TNUM,UDATE,   MARK)

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

CREATE VIEW OTLSTUD

AS SELECT *

 FROM STUDENTS

WHERE STIP > 70;

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

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

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

CREATE VIEW STIPOFF

AS SELECT NOM_ZACH, SFAM, SNAME

FROM STUDENTS;

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

UPDATE STIPOFF

SET SNAME=”Степан”

WHERE  NOM_ZACH= 200201;

аналогично выполнению той же команды для таблицы STUDENTS.

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

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

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

Когда делается запрос к представлению, то на самом деле система обращается к  базовым таблицам. Например, при выполнении команды

 SELECT *

FROM OTLSTUD

WHERE SNUM >  200201;

СУБД фактически осуществляет следующий запрос:

SELECT *

FROM STUDENTS

WHERE STIP >30 and SNUM >  200201;

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

CREATE VIEW STIPCOUNT(STIP,KOL)

AS SELECT STIP,COUNT (*)

FROM STUDENTS

GROUP BY STIP;

Теперь обратимся к этому представлению, чтобы выяснить, есть ли такой размер стипендии, который получен двумя студентами:

SELECT *

FROM STIPCOUNT

WHERE COL =2;

Если запрос к представлению преобразовать к запросу к исходной таблице, то, скорее всего, будет получено

SELECT STIP,COUNT(*)

FROM STUDENTS

WHERE COUNT(*)=2

GROUP BY STIP;

Последний запрос недопустим, так как агрегатные функции не могут использоваться в предикате. Возникает закономерное предположение о то, что СУБД в некоторых случаях не может правильно сформулировать запрос к базовым таблицам, а значит выполнить его. Все зависит от того, каким образом и по каким алгоритмам система интерпретирует пользовательские команды. Правильным способом формирования приведенного выше  запроса будет  такой:

SELECT STIP,COUNT(*)

FROM STUDENTS

GROUP BY STIP;

HAVING COUNT(*)=2;

Однако SQL может не выполнить такого превращения. В этом случае следует проверить, справляется ли используемая СУБД с аналогичными запросам, и в соответствии с этим формировать запросы к представлениям.

В SQL существует понятие групповых представлений, то есть таких, которые содержат предложение GROUP BY   или которые основаны на других групповых представлениях. Предыдущий пример представления как раз относится к числу групповых представлений. Теперь каждый раз, когда требуется получить  количество студентов, получающих ту или иную стипендию, достаточно просто выбрать все записи рассматриваемого представления вместо того, чтобы создавать достаточно сложный запрос.

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

CREATE VIEW STUDOCEN

AS SELECT   STUDENTS.SFAM ,PREDM.PNAME, USP.MARK

FROM STUDENTS,PREDM,USP

WHERE STUDENTS.NOM_ZACH=USP.NOM_ZACH

AND USP.PKOD= PREDM.PKOD;

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

SELECT *

FROM STUDOCEN

WHERE SFAM=”Иванов”;

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

SELECT SFAM,PNAME,OCENKA, UDATE

FROM STUDOCEN,USP

WHERE STUDENTS.SFAM=”Иванов”;

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

CREATE VIEW AVGOC

AS SELECT *

FROM USP A

 WHERE MARK >  (SELECT AVG(MARK)

                                     FROM USP B

                                     WHERE A.PCOD=B.PCOD);

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

SELECT *

FROM AVGOC;

3. Модифицируемые и немодифицируемые представления

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

Команды модификации действуют на  значения базовой для представления таблицы. При выполнении операций модификации может возникнуть ряд неоднозначных ситуаций. Например, рассмотрим представление, выводящее фамилию студента, наименование предмета и оценку. Предположим, что это представление является модифицируемым и необходимо из него удалить строку. Если каким либо образом не оговорить правило удаления, то неясно  удаление строки из представления повлечет за собой удаление оценки из таблицы успеваемости USP, студента из таблицы STUDENTS или предмета из таблицы PREDM.

Если команды модификации могут выполняться в представлении, то оно считается модифицируемым: в противном случае оно предназначено только для чтения при запросе. Критерии, по которым определяют, является ли представление модифицируемым в SQL:

· Представление должно основываться только на одной базовой таблице.

· Оно должно содержать первичный ключ этой таблицы.

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

· Представление не должно содержать GROUP BY или HAVING  в своем определении.

· Желательно, чтобы оно не использовало в своем определении подзапросы.

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

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

Примеры.

1. Модифицируемое представление.

Создать представление для вывода успеваемости по математике:

CREATE VIEW MATEMUSP

AS SELECT *

FROM USP

WHERE PKOD=2003:

2. Представление Только для чтения:

CREATE VIEW IDXSTIP(NOM_ZACH,SFAM,NEWSTIP)

AS SELECT NOM_ZACH,SFAM,STIP*2

FROM STUDENTS

WHERE STIP=25000;

Представление является не модифицируемым, так как  в определении присутствует  выражение STIP*2.

Представление, выводящее информацию о студентах, получивших оценки в определенный день, будет только для чтения:

CREATE VIEW DATEOC

AS SELECT *

FROM STUDENTS

WHERE NOM_ZACH IN (SELECT NOM_ZACH

                                            FROM USP

                                            WHERE UDATE=08.01.2001);

Это происходит из-за того, что в определении представления присутствует подзапрос.

Следующее представление является модифицируемым:

CREATE VIEW DATEOC

AS SELECT *

FROM STUDENTS

WHERE   UDATE IN(08.01.2001, 09.01.2001);

Синтаксис удаления представления  из БД подобен удалению базовых таблиц:

DROP VIEW <им представления>

4. КОНТРОЛЬНЫЕ ВОПРОСЫ

1. Чем представление отличается от запроса?

2. Какова цель создания представлений?

3. Что представляют собой вертикальные представления?

4. Как создаются горизонтальные представления?

Люди также интересуются этой лекцией: Установка цвета и стиля линий.

5. Как представления связаны с базовыми таблицами, на которых они основаны?

6. Могут ли имена столбцов в представлении отличаться от имен столбцов в базовой таблице, на которой основано это представление?

7. Что понимают под групповым представлением?

8. Для чего используются представления, основанные на нескольких таблицах?

9. Что представляют собой модифицируемые представления?

10.  Перечислите основные признаки модифицируемых представлений.