2.7 Пример

В качестве примера реконструируем скрипт создания таблицы item в базе данных "Корпорация Кинга".

Строка таблицы item представляет одну позицию (один продукт) в заказе и содержит следующие поля:

• order_id - код заказа, к которому относится данная позиция;
• item_id - номер позиции в заказе;
• product_id - код заказанного товара;
• actual_price - цена за единицу, по которой продан товар;
• quantity - количество единиц товара в заказе;
• total - общая стоимость заказанного товара.

Определим сначала типы данных для столбцов таблицы.

Код заказа является числом, считая, что число заказов может исчисляться десятками тысяч, назначим для него тип данных NUMBER(6,0) в терминах Oracle или INTEGER - в терминах DB2. Строго говоря, тип этого столбца должен был быть определен раньше - при конструировании таблицы sales_order, здесь мы просто воспроизводим те же рассуждения.

Трудно представить себе, чтобы число позиций в заказе исчислялась тысячами, поэтому типа NUMBER(3,0) или SMALLINT для номера позиции будет вполне достаточно.

Для кода товара, исходя из соображений возможного числа товаров в ассортименте, назначаем тип NUMBER(6,0) или INTEGER.

Цена измеряется с точностью до второго знака после точки, анализ имеющихся цен показывает, что они не превосходят 20000.00. Следовательно, тип данных для цены - NUMBER(8,2) или NUMERIC(8,2).

Для количества единиц товара примем тип NUMBER(6,0) или INTEGER, что даст возможность записать число до 999999 - более, чем достаточно.

Наконец, исходя из того, что в партии дорогостоящих товаров вряд ли может быть более 100 единиц, примем для общей стоимости тип NUMBER(10,2) или NUMERIC(10,2).

Теперь определим ограничения целостности для отдельных столбцов и для всей таблицы в целом.

Что является первичным ключом таблицы? Столбец order_id не может быть таковым, так как в заказе могут фигурировать несколько товаров, поэтому в таблице item может быть несколько строк с одинаковым значением order_id. Не может быть первичным ключом и столбец item_id, так как в каждом заказе своя нумерация позиций, начиная с 1. А вот комбинация (order_id, item_id) первичным ключом может быть, так как в каждом заказе есть только одна позиция 1, одна позиция 2 и т.д.

Отметим еще одно соображение, которое мы рассмотрели при проектировании ограничений целостности. На первый взгляд нам показалось, что комбинация (order_id, product_id) также является уникальной и может быть либо первичным, либо потенциальным ключом. Однако в конце концов мы пришли к выводу, что один товар может составлять несколько позиций в заказе (например, с разными ценами), и отказались от ограничения UNIQUE для этой комбинации.

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

Код товара должен совпадать с кодом товара в таблице product, столбец product_id таблицы item является внешним ключом, ссылающимся на таблицу product. Очевидно, что товар может сниматься с продажи и, следовательно, удаляться из таблицы product. Но если при этом мы будем удалять и позиции заказов, относящиеся к удаленному товару, мы потеряем правильную отчетность. С другой стороны, если мы не будем удалять позиции заказов, у нас в отчетности появятся позиции, относящиеся к неизвестно какому товару. Правильным решением в этом случае будет запрет на удаление товаров. Товары сохраняются в таблице product, даже если они сняты с продажи. Запрет на изменение кода товара диктуется теми же соображениями, что и в предыдущем случае.

Цена продажи товара не является полностью независимой. В таблице price для каждого товара хранится его объявленная цена (она может рассматриваться как максимальная цена продажи) и минимальная цена. Цена конкретной продажи должна лежать в пределах этих двух цен. Однако для того, чтобы выполнить такую проверку, нам пришлось бы выполнить запрос к таблице price, а в ограничения целостности оператора CREATE TABLE мы не можем включать вложенные запросы, поэтому данное ограничение не может быть заложено в саму структуру таблицы.

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

Итоговые скрипты на создание таблицы item выглядят следующим образом

    /* Это комментарий в ORACLE */
    CREATE TABLE item {
       order_id     NUMBER(6,0) 
         REFERENCES sales_order(order_id) ON DELETE CASCADE, 
           /* ON UPDATE RESTRICT - по умолчанию */
       item_id      NUMBER(3,0),
       product_id   NUMBER(6,0)
     REFERENCES product(product_id),
           /* ON DELETE/UPDATE RESTRICT -по умолчанию */
       actual_price NUMBER(8,2),
       quantity     NUMBER(8,0),
       total        NUMBER(10,2),
       PRIMARY KEY (order_id,item_id),
       CHECK (quantity * actual_price = total)
    );

    -- Это комментарий в DB2
    CREATE TABLE item {
       order_id     INTEGER 
         REFERENCES sales_order(order_id) ON DELETE CASCADE, 
         -- ON UPDATE RESTRICT - по умолчанию
       item_id      SMALLINT,
       product_id   INTEGER
         REFERENCES product(product_id),
         -- ON DELETE/UPDATE RESTRICT - по умолчанию 
       actual_price NUMERIC(8,2),
       quantity     INTEGER(8,0),
       total        NUMERIC(10,2),
       PRIMARY KEY (order_id,item_id),
       CHECK (quantity * actual_price = total)
    );