Пример.

      Создать связанные таблицы, которые позволяют  внешнему ключу EMPNO в таблице ABC создать ссылки на EMPNO в таблице EMP с правилом DELETE CASCADE.

      CREATE TABLE EMP

      (EMPNO INT NOT NULL,

      LNAME VARCHAR (15),

      FNAME CHAR (10),

      DEPTNO SMALLINT,

      HIREDATE DATE,

      JOB VARCHAR (15)),

      PRIMARY KEY (EMPNO)); 

      CREATE TABLE ABC

      (EMPNO INT,

      SALARY DECIMAL (9,2),

      REVIEW LONG VARCHAR,

      FOREIGN KEY (EMPNO) REFERENCES EMP

      ON DELETE CASCADE);

              Здесь первый оператор CREATE TABLE описывает таблицу ЕМР с полями

EMPNO, LNAME, FNAME, DEPTNO, HIREDATE, JOB. В качестве первичного ключа используется поле EMPNO (при этом указано,  что оно не  может содержать неопределенные значения).

              Второй оператор CREATE TABLE описывает таблицу АВС с полями

EMPNO, SALARY, REVIEW. Первичный ключ не определен. Внешним ключом является поле EMPNO,  значения которого ссылаются на соответствующие значения в таблице ЕМР. При удалении строк из родительской таблицы ЕМР осуществляется каскадное удаление строк из таблицы АВС.

    1.11.Задание ограничений целостности: общих, доменов и семантики

      Термин  целостность используется для описания точности и корректности данных, хранящихся в БД. Иными словами, под целостностью подразумевается, что пользователям БД разрешается выполнять над ней некоторые действия и эти действия выполняются корректно [8].

      Можно дать и другое определение: целостность  означает защиту БД от некорректных действий санкционированного пользователя.

      Для поддержки целостности СУБД должна содержать сведения о тех правилах, которые пользователю не следует нарушать при модификации БД (при выполнении операций UPDATE, DELETE, INSERT). Кроме того СУБД должна следить за выполнением заданных правил. Эти правила принято называть ограничениями целостности.

      Кроме рассмотренных выше ограничений ссылочной целостности стандарт языка SQL поддерживает также ограничения домена, ограничения семантики и общие ограничения – «утверждения».

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

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

        Каждый атрибут отношения определяется  на некотором домене. Домены могут  отличаться для каждого из  атрибутов (т.е. для каждого  атрибута может использоваться  свой домен). Однако два или  более атрибута могут определяться  на одном и том же домене .

    Фактически задание домена означает задание типа и размера, используемых данных, а также задание ограничений целостности этих данных.

    Имя атрибута не обязательно  должно совпадать с именем  домена. 

           Пример домена представлен в  разделе 1.3. Домен определяется оператором SQL CREATE DOMAIN. 

      Ограничения семантики 

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

      Ограничения семантики для  таблиц задаются с помощью предложения

CHECK (conditional-expression), вставляемого в оператор CREATE TABLE (где conditional-expression определяет логическое условие,   которое не может быть нарушено при модификации данных).

      Пример. 

      CREATE TABLE EMP

      (EMPNO INT NOT NULL,

      LNAME VARCHAR (15),

      FNAME CHAR (10),

      DEPTNO SMALLINT,

      HIREDATE DATE,

      JOB VARCHAR (15),

      PRIMARY KEY (EMPNO),

      CHECK (HIREDATE <2009)); 

      Здесь проверочное условие состоит в том, что столбец HIREDATE в таблице ЕМР (сотрудник) должен содержать значения меньшие, чем 2009 (текущий год).

      Общие ограничения целостности

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

      Общие ограничения целостности  задаются с помощью оператора CREATE ASSERTION.

      Синтаксис оператора:

      CREATE ASSERTION имя CHECK (условное выражение);

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

      Пример  .

      CREATE ASSERTION ABC1 CHECK

      (NOT EXISTS (SELECT * FROM P

      WHERE NOT (P.WEIGHT>0)));

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

Лекция  10

                    1.19. Проблема параллелизма 

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

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

  

        

            В момент t1 транзакция А читает запись i,  которая содержит число 10.

В момент t2 другая транзакция читает это же число. В момент t3 транзакция А модифицирует прочитанное число, прибавляя к нему число 10. В результате в БД оказывается число 20.  Но в следующий момент транзакция В модифицирует прочитанное число, прибавляя к нему число 15. Таким образам, в записи i оказывается число 25. Это результат параллельной работы этих транзакций. Очевидно, что результат неправильный, т.к. две транзакции в сумме прибавили число 25. Значит, окончательным результатом должно было быть число 35, а не 25.

Фактически результат  модификации, выполненной транзакцией А, пропал. 

20. Блокирование  информационных объектов базы данных

 

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

      Его основная идея очень проста: в случае, когда для выполнения некоторой  транзакции необходимо, чтобы некоторый объект базы данных (например, отдельный кортеж таблицы или вся таблица)  не изменялся непредсказуемо и без ведома этой транзакции, такой объект блокируется. Таким образом, эффект блокирования состоит в том, чтобы "заблокировать доступ к этому объекту со стороны других транзакций", а значит, предотвратить непредсказуемое изменение этого объекта. Следовательно, первая транзакция в состоянии выполнить всю необходимую обработку с учетом того, что обрабатываемый объект остается в стабильном состоянии настолько долго, насколько это нужно.

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

       Для пояснения идеи блокирования  рассмотрим пример, приведенный  на рис.1.18.                                     

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

                           Рис.1.18. Пример блокирования таблиц БД 
 
 

          В данном примере предполагается, что БД состоит из 3 таблиц (ТОВАР,  ОТДЕЛЕНИЕ, ЗАКАЗ). Блокирование  производится на уровне таблиц, т.е. сразу блокируется вся  таблица (хотя на практике не  обязательно блокируется вся  таблица; в данном примере без потери общности это предположение делается для простоты рассмотрения). Кроме того предполагается, что параллельно исполняются две тразакции А и В. На рис. под названием каждой транзакции приводятся операторы SQL этой транзакции с условным обозначением времени выдачи этих операторов.

           В момент времени 01 транзакция  А обновляет таблицу  ТОВАР.  Таблица оказывается заблокированной.

    В момент времени 03 выполняется вставка  в таблицу ОТДЕЛЕНИЕ.  В результате  эта таблица также блокируется.

    Затем делается  вставка в таблицу  ТОВАР. При этом таблица остается заблокированной.

    В момент 09 транзакция А заканчивается  выдачей оператора COMMIT и таблицы ТОВАР и ОТДЕЛЕНИЕ разблокируются.

       Транзакция В в момент 02 обновляет таблицу ЗАКАЗ, вследствие чего эта таблица блокируется. В момент 04 делается попытка вставки в таблицу ОТДЕЛЕНИЕ, но ранее эта таблица заблокирована транзакцией А, поэтому транзакция В не получает к ней доступа и оказывается в состоянии ожидания .  Транзакция В находится в состоянии ожидания до момента 09. когда транзакция А разблокирует таблицу ОТДЕЛЕНИЕ . Таблица разблокируется со стороны транзакции А но тут же блокируется со стороны транзакции В и транзакция В продлжает свою работу, т.е. исполняет оператор вставки. В момент 11 транзакция В завершается и разблокирует таблицы ОТДЕЛЕНИЕ и ЗАКАЗ.

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

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

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

1. блокирование всей БД.
2. блокирование отдельных таблиц БД.
3. блокирование набора кортежей ( страниц).
4. блокирование отдельных кортежей.

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