КАТЕГОРИИ: Архитектура-(3434)Астрономия-(809)Биология-(7483)Биотехнологии-(1457)Военное дело-(14632)Высокие технологии-(1363)География-(913)Геология-(1438)Государство-(451)Демография-(1065)Дом-(47672)Журналистика и СМИ-(912)Изобретательство-(14524)Иностранные языки-(4268)Информатика-(17799)Искусство-(1338)История-(13644)Компьютеры-(11121)Косметика-(55)Кулинария-(373)Культура-(8427)Лингвистика-(374)Литература-(1642)Маркетинг-(23702)Математика-(16968)Машиностроение-(1700)Медицина-(12668)Менеджмент-(24684)Механика-(15423)Науковедение-(506)Образование-(11852)Охрана труда-(3308)Педагогика-(5571)Полиграфия-(1312)Политика-(7869)Право-(5454)Приборостроение-(1369)Программирование-(2801)Производство-(97182)Промышленность-(8706)Психология-(18388)Религия-(3217)Связь-(10668)Сельское хозяйство-(299)Социология-(6455)Спорт-(42831)Строительство-(4793)Торговля-(5050)Транспорт-(2929)Туризм-(1568)Физика-(3942)Философия-(17015)Финансы-(26596)Химия-(22929)Экология-(12095)Экономика-(9961)Электроника-(8441)Электротехника-(4623)Энергетика-(12629)Юриспруденция-(1492)Ядерная техника-(1748) |
Определение общих ограничений целостности
Для определения общего ограничения целостности служит оператор CREATE ASSERTION, задаваемый в следующем синтаксисе:
CREATE ASSERTION constraint_name CHECK (conditional_expression)
Заметим, что при создании общего ограничения целостности его имя всегда должно указываться явно. Хотя синтаксис определения общего ограничения совпадает с синтаксисом определений ограничений столбца и таблицы, в данном случае допускается только специальные виды условных выражений. Мы не можем сейчас точно сформулировать свойства этих видов условий, поскольку отложили подробное рассмотрение разновидностей условных выражений до следующих лекций. Если говорить неформально, то особые свойства условий связаны с тем, что при определении общих ограничений целостности контекстом, в котором вычисляется условное выражение, является весь набор таблиц базы данных, а не набор строк таблицы, как это было при определении табличных ограничений. Продемонстрируем и прокомментируем несколько примеров определений общих ограничений целостности.
В определении таблицы EMP содержалось ограничение столбца EMP_BDATE
CHECK (EMP_BDATE >= ‘1917-10-24’)
(к работе на предприятии допускаются только те лица, которые родились после Октябрьского переворота). Вот каким образом можно определить такое же ограничение на уровне общих ограничений целостности:
CREATE ASSERTION MIN_EMP_BDATE CHECK
В логическом условии этого общего ограничения выбирается минимальное значение столбца EMP_BDATE (дата рождения самого старого служащего). Значением условного выражения будет false в том и только в том случае, если среди служащих имеется хотя бы один, родившийся до запретной даты. Заметим, что мы снова воспользовались операцией COALESCE, поскольку в столбце EMP_BDATE допускаются неопределенные значения.* Кстати, обратите внимание, что в качестве второго операнда операции COALESCE мы указали “ниладическую” функцию CURRENT_DATE, значение которой (текущая дата) играет в данном случае роль нуля.
Теперь переформулируем в виде общего ограничения целостности ограничение таблицы EMP PRO_EMP_NO, которое определялось следующим образом:
(над одним проектом не может работать более 50 служащих).
Вот формулировка эквивалентного общего ограничения целостности:
CREATE ASSERTION NEW_PRO_EMP_NO CHECK
Логическое выражение этого ограничения может принимать только значения true и false. Внутренний оператор выборки группирует строки таблицы EMP таким образом, что в одну группу попадают все строки с одинаковым значением столбца PRO_NO. Затем эти группы фильтруются по условию раздела HAVING, и остаются только те группы, в которых содержится более 50 строк. В результирующей таблице содержатся строки из одного столбца, содержащего значение PRO_NO оставшихся групп. Предикат NOT EXISTS принимает значение true тогда и только тогда, когда эта результирующая таблица не содержит ни одной строки, т.е. нет ни одного проекта, в котором работает больше 50 служащих.
Покажем, как можно сформулировать в виде общего ограничения целостности ограничение внешнего ключа. Например, приведем такую эквивалентную формулировку для определения внешнего ключа PRO_NO, входящего в состав определения таблицы EMP:
FOREIGN KEY PRO_NO REFERENCES PRO (PRO_NO)
В виде общего ограничения целостности это может выглядеть следующим образом:
(1) CREATE ASSERTION FK_PRO_NO CHECK
Логическое выражение этого ограничения выглядит достаточно сложным и нуждается в пояснении. Условие выборки оператора SELECT на строке (2) состоит из двух частей, связанных через AND. Первая часть отфильтровывает те строки таблицы EMP, у которых в столбце PRO_NO содержится NULL. Если этот столбец содержит NULL во всех строках таблицы, то результирующая таблица оператора выборки на строке (2) будет пустой, и значением предиката NOT EXISTS будет true, т.е. ограничение удовлетворяется.
Теперь предположим, что в таблице EMP нашлась строка emp, в столбце PRO_NO которой содержится значение, отличное от NULL. Назовем это значение cand_pro_no. Для него вычисляется вторая часть условия выборки оператора SELECT на строке (2). Оператор выборки на строке (3) выбирает все строки таблицы PRO, значение столбца PRO_NO которых равняется cand_pro_no. Если для данного значения cand_pro_no нашлась хотя бы одна такая строка, то результирующая таблица оператора выборки на строке (3) будет непустой, и значением предиката NOT EXISTS на строке (3) будет false. Соответственно, все условие выборки первого оператора SELECT примет значение false, и строка со значением cand_pro_no в столбце PRO_NO будет отфильтрована.*
Если же найдется хотя бы одна строка таблицы EMP с таким значением cand_pro_no столбца PRO_NO, что в таблице PRO не найдется ни одной строки, значение столбца PRO_NO которой равнялось бы этому cand_pro_no, то результирующая таблица оператора выборки на строке (3) будет пустой, и значением предиката NOT EXISTS на строке (3) будет true. Тогда все условие выборки первого оператора SELECT примет значение true, и эта строка таблицы EMP будет пропущена в результирующую таблицу. Значением предиката NOT EXISTS будет false, т.е. ограничение не удовлетворяется.
Мы сознательно привели такое подробное пояснение не только для того, чтобы прояснить смысл условного выражения общего ограничения целостности FK_PRO_NO, но и чтобы дать понять, во что реально вырождается простая синтаксическая конструкция определения внешнего ключа. Как показывает опыт, многие начинающие проектировщики SQL-ориентированных баз данных думают, что ссылочные ограничения так же легко поддерживать, как определять.
Наконец, сформулируем общее ограничение целостности, состоящее в том, что никакой менеджер проекта, не должен иметь суммарный общий доход, больший суммарного дохода руководителя отдела, в котором работает этот менеджер.
(1) CREATE ASSERTION PRO_MNG_CONSTR CHECK (5) DEPT.DEPT_MNG = EMP2.EMP_NO AND
В логическом выражении этого ограничения используется оператор выборки SELECT, в разделе перечня таблиц (FROM) впервые в этом курсе используется несколько таблиц. Такие запросы в SQL называются запросами с соединениями, и мы воспользуемся случаем, чтобы пояснить на примере (конечно, предварительно), как их следует понимать в соответствии со стандартом языка SQL.
Итак, в разделе FROM оператора выборки, используемого в логическом условии этого ограничения, через запятую перечислены четыре элемента – EMP EMP1, EMP EMP2, DEPT и PRO. Выражение вида EMP ANOTHER_NAME означает применение своего рода операции переименования. Внутри запроса столбцы этого “экземпляра” EMP имеют “квалифицированные” имена вида ANOTHER_NAME.column_name, где column_name обозначает имя существующего столбца таблицы EMP.
Вычисление оператора выборки начинается с того, что формируется расширенное декартово произведение всех таблиц, указанных в разделе FROM. В данном случае схема результирующей таблицы раздела FROM будет содержать следующие имена столбцов: EMP1.EMP_NO, EMP1.EMP_NAME, EMP1. EMP_BDATE, EMP1. EMP_SAL, EMP1.EMP_BONUS, EMP1. DEPT_NO, EMP1. PRO_NO, EMP2.EMP_NO, EMP2.EMP_NAME, EMP2. EMP_BDATE, EMP2. EMP_SAL, EMP2.EMP_BONUS, EMP2. DEPT_NO, EMP2. PRO_NO, DEPT.DEPT_NO, DEPT.DEPT_EMP_NO, DEPT.DEPT_TOTAL_SAL, DEPT.DEPT_MNG, PRO.PRO_NO, PRO.PRO_TITLE, PRO.PRO_SDATE, PRO.PRO_DURAT, PRO.PRO_MNG, PRO_DESC. Для удобства назовем эту “широкую” таблицу ALL_TOGETHER.*
Условие раздела WHERE состоит из четырех частей, связанных через AND. Обсудим их последовательно. После проверки условия EMP1.EMP_NO = PRO.PRO_MNG в таблице ALL_TOGETHER останутся все служащие-менеджеры проектов вместе со своими проектами в комбинации со всеми возможными отделами и всеми возможными служащими (назовем эту отфильтрованную таблицу ALL_TOGETHER_STEP1). После проверки условия EMP1.DEPT_NO = DEPT.DEPT_NO в таблице ALL_TOGETHER_STEP1 останутся все служащие-менеджеры проектов вместе со своими проектами и вместе с описанием своих отделов в комбинации со всеми возможными служащими (назовем эту отфильтрованную таблицу ALL_TOGETHER_STEP2). После проверки условия DEPT.DEPT_MNG = EMP2.EMP_NO в таблице ALL_TOGETHER_STEP2 останутся все служащие-менеджеры проектов вместе со своими проектами, вместе с описанием своих отделов и вместе с руководителями этих отделов (по одной строке для каждого допустимого сочетания “проект-менеджер_проекта-отдел_менеджера_проекта-руководитель_отдела_менеджера_проекта”). Назовем эту отфильтрованную таблицу ALL_TOGETHER_STEP3. Легко видеть, что после проверки условия EMP1.EMP_SAL + EMP1.EMP_BONUS > EMP2.EMP_SAL + EMP2.EMP_BONUS в таблице ALL_TOGETHER_STEP3 могут остаться только строки “проект-менеджер_проекта-отдел_менеджера_проекта-руководитель_отдела_менеджера_проекта”, в которых суммарный доход менеджера проекта превышает суммарный доход руководителя отдела, в котором работает менеджер проекта. Если хотя бы одна такая строка действительно существует, то результат оператора выборки будет непустым, значением предиката NOT EXISTS будет false, и, тем самым, ограничение целостности PRO_MNG_CONSTR будет нарушено.
Дата добавления: 2014-01-03; Просмотров: 330; Нарушение авторских прав?; Мы поможем в написании вашей работы! Нам важно ваше мнение! Был ли полезен опубликованный материал? Да | Нет |