Создание физической модели хранилища данных

Ограничения и их использование в реляционной базе данных

В предыдущих разделах мы уже сталкивались с несколькими типами ограничений в спецификациях колонок — NOT NULL, и ограничениях в таблицах — PRIMARY KEY, FOREING KEY. В данном разделе мы изучим практически еще несколько типов ограничений, которые поддерживаются в реляционных БД. Ограничения являются важным инструментом проектировщика, с помощью которого он поддерживает целостность (strong) БД. Их можно использовать для того, чтобы быть уверенным в том, что колонка первичного ключа таблицы является уникальной и всегда содержит значения. Ограничения применяются также для поддержки ссылочной целостности. Последнее означает, что значения в колонке внешнего ключа должны существовать как некоторое значение в колонке первичного ключа другой таблицы.

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

Как мы видели выше, ограничения могут применяться на уровне колонки ( ограничения колонки ) или на уровне таблицы ( ограничения таблицы ). Ограничения первичного ключа — это ограничения, действующие на уровне таблицы, а NOT NULL -ограничения — это ограничение колонки. Существуют три основных типа ограничений, используемых в реляционной БД, — целостности данных, целостности ссылок и ограничения первичного ключа. Ограничения целостности данных (data integrity constraints) относятся к значениям данных в некоторых колонках и определяются в спецификации колонки с помощью элементов NOT NULL, UNIQUE, CHECK. Ограничения целостности ссылок (referential constraints) относятся к связям между таблицами на основе связи первичного и внешнего ключа. Ограничения первичного ключа относится к значениям данных в колонках первичного ключа таблицы и должны налагаться на каждую базовую таблицу реляционной БД. В табл. 11.3 приведен список ограничений, применяемых в реляционных БД.

Использование ограничений NOT NULL и PRIMARY KEY было рассмотрено выше в настоящей лекции. Использование ограничения FOREING KEY будет рассмотрено при обсуждении создания таблицы фактов.

Ограничение CHECK позволяет выполнять проверку содержимого колонки относительно некоторых условий и списка значений. Она налагается с помощью предложения CHECK. Для добавления этого ограничения нужно после объявления столбца в спецификации колонки определить синтаксическую конструкцию CHECK (предикат). Согласно требованиям стандарта с помощью ключевого слова VALUE в предикате вы ссылаетесь на значение колонки. Но практически во всех диалектах для этой цели используется имя колонки.

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

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

Примеры использования ограничений будут приведены ниже при анализе команд создания объектов БД для нашего учебного примера.

Анализ команд создания объектов базы данных

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

Для всех суррогатных ключей таблиц измерений и таблицы фактов мы применяем определение колонки с типом данных uniqueidentifier. Задание такого типа колонки позволит увеличивать значение суррогатного ключа автоматически. Для этих столбцов используется функция NEWSEQUENTIALID() в ограничении DEFAULT для указания значений для новых строк. Также к столбцам типа uniqueidentifier применяется свойство ROWGUIDCOL, чтобы на столбец можно было ссылаться с помощью ключевого слова $ROWGUID, и ограничение первичного ключа.

Создание таблиц измерений

Команда CREATE TABLE для создания таблицы измерения "Покупатель" (Customer) на диалекте SQL семейства СУБД MS SQL Server имеет вид:

create table Customer (
   Cust_ID uniqueidentifier 
            CONSTRAINT Guid_Default_1 DEFAULT NEWSEQUENTIALID() ROWGUIDCOL,
   FName                varchar(20)          not null,
   LName                varchar(20)          not null,
   Cust_Address         varchar(40)          null,
   Company              varchar(40)          not null,
   constraint PK_CUSTOMER primary key  (Cust_ID)
)
go

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

Команда CREATE TABLE для создания таблицы измерения "Продавец" (Employee) на диалекте SQL семейства СУБД MS SQL Server имеет вид:

create table Employee (
   Empl_ID  uniqueidentifier 
            CONSTRAINT Guid_Default_2 DEFAULT NEWSEQUENTIALID() ROWGUIDCOL,
   Empl_FName           varchar(20)          not null,
   Empl_LName           varchar(20)          not null,
   City                 varchar(20)          not null,
   Empl_Address         varchar(40)          null,
   constraint PK_EMPLOYEE primary key  (Empl_ID)
)
go

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

Команда CREATE TABLE для создания таблицы измерения "Товар" (Product) на диалекте SQL семейства СУБД MS SQL Server имеет вид:

create table Product (
   Prod_ID      uniqueidentifier 
          CONSTRAINT Guid_Default_3 DEFAULT NEWSEQUENTIALID() ROWGUIDCOL,
   Name                 varchar(80)          not null,
   Size                 varchar(20)          not null,
   Unit_Price           numeric(8,2)         not null,
   constraint PK_PRODUCT primary key  (Prod_ID)
)
go

Наименование товара является уникальным значением, поэтому применим к этой колонке ограничение, изменив строку спецификации колонки "Наименование товара" на следующую:

Name varchar(80) not null UNIQUE NONCLUSTERED,

Цена товара является величиной положительной, поэтому целесообразно ввести проверку вводимого значения цены товара. Из анализа предметной области следует, что цена товаров, продаваемых компанией, находится в пределах от 15 руб. до 1500 руб. и можно ввести проверку этого значения на диапазон. Изменим строку, определяющую колонку "Цена товара" (Unit_Price), как показано ниже:

Unit_Price  numeric(8,2)  not null CHECK (Unit_Price >= 15 and Unit_Price <= 1500),

Команда CREATE TABLE для создания таблицы измерения "Время" (Time) на диалекте SQL семейства СУБД MS SQL Server имеет вид:

create table Time (
   Time_ID       uniqueidentifier 
            CONSTRAINT Guid_Default_4 DEFAULT NEWSEQUENTIALID() ROWGUIDCOL,
   Year                 integer              not null,
   Quarter              integer              not null,
   constraint PK_TIME primary key  (Time_ID)
)
go

По решению руководства компании данные в ХД будут заноситься, начиная с 2007 года. Поэтому целесообразно ввести проверку на значения колонки "Год" (Year). Число кварталов в году – четыре. Введем проверку на значения колонки "Квартал" (Quarter). Строки стрипта, определяющие колонки "Год" (Year) и "Квартал" (Quarter), теперь выглядят, как показано ниже.

Year   integer  not null CHECK (Year >= 2007),
Quarter  integer   not null CONSTRAINT Q_CHK CHECK (Quarter IN ('1', '2', '3', '4''),

Создание таблицы фактов

Команда CREATE TABLE для создания таблицы фактов "Продажи" (Sale) на диалекте SQL семейства СУБД MS SQL Server имеет вид:

create table Sale (
   Sale_ID   uniqueidentifier 
       CONSTRAINT Guid_Default_5 DEFAULT NEWSEQUENTIALID() ROWGUIDCOL,
   Time_ID              uniqueidentifier               null,
   Cust_ID              uniqueidentifier               null,
   Prod_ID              uniqueidentifier               null,
   Empl_ID              uniqueidentifier               null,
   Amount               numeric(9,2)         not null,
   Quantity             integer              not null,
   constraint PK_SALE primary key  (Sale_ID)
)
go

Значения колонок "Количество" (Quantity) и "Сумма платежа" (Amount) не могут быть нулевыми. Поэтому наложим соответствующие ограничения на значения этих колонок, как показано ниже.

Amount numeric(9,2) not null CHECK (Amount >= 15),
Quantity integer not null CHECK (Quantity >= 1),

Колонки "Идентификатор времени" (Time_ID), "Идентификатор покупателя" (Cust_ID_ "Идентификатор товара" (Prod_ID)) и "Идентификатор продавца" (Empl_ID) являются значениями первичных ключей таблиц измерений и поэтому могут служить внешними ключами в таблице фактов. Наложим на значения этих колонок ограничения внешнего ключа.

Ограничения внешнего ключа в таблице фактов

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

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

ALTER TABLE table_name
{ [ ALTER COLUMN column_name 
   {DROP DEFAULT 
   | SET DEFAULT constant_expression 
   | IDENTITY [ ( seed , increment ) ]
   } 
| ADD 
   { < column_definition > | < table_constraint > } [ ,...n ] 
| DROP 
   { [ CONSTRAINT ] constraint_name 
   | COLUMN column }
] }
< column_definition > ::= 
   { column_name data_type } 
   [ [ DEFAULT constant_expression ] 
      | IDENTITY [ ( seed , increment ) ] 
   ] 
   [ROWGUIDCOL]
   [ < column_constraint > ] [ ...n ] ]
< column_constraint > ::= 
   [ NULL | NOT NULL ] 
   [ CONSTRAINT constraint_name ] 
   { 
      | { PRIMARY KEY | UNIQUE } 
      | REFERENCES ref_table [ (ref_column) ] 
      [ ON DELETE { CASCADE | NO ACTION | SET DEFAULT |SET NULL } ] 
      [ ON UPDATE { CASCADE | NO ACTION | SET DEFAULT |SET NULL } ]
   }
< table_constraint > ::= 
   [ CONSTRAINT constraint_name ] 
   { [ { PRIMARY KEY | UNIQUE } 
      { ( column [ ,...n ] ) } 
      | FOREIGN KEY 
        ( column [ ,...n ] )
        REFERENCES ref_table [ (ref_column [ ,...n ] ) ] 
      [ ON DELETE { CASCADE | NO ACTION | SET DEFAULT |SET NULL } ] 
      [ ON UPDATE { CASCADE | NO ACTION | SET DEFAULT |SET NULL } ] 
   }

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

• ALTER COLUMN. Указывает, что определенный столбец будет изменен или модифицирован.
• ADD. Указывает, что добавлено одно или несколько определений столбца или ограничений таблицы.
• DROP { [CONSTRAINT] constraint_name| COLUMN column}. Указывает, что из таблицы будет удален constraint_name или column_name.

Чтобы добавить ограничения внешнего ключа в таблицу фактов "Продажи" (Sale) с помощью команды ALTER TABLE, можно поступить следующим образом.

alter table Sale
   add constraint FK_SALE_REFERENCE_TIME foreign key (Time_ID)
      references Time (Time_ID)
go

alter table Sale
   add constraint FK_SALE_REFERENCE_CUSTOMER foreign key (Cust_ID)
      references Customer (Cust_ID)
go

alter table Sale
   add constraint FK_SALE_REFERENCE_PRODUCT foreign key (Prod_ID)
      references Product (Prod_ID)
go

alter table Sale
   add constraint FK_SALE_REFERENCE_EMPLOYEE foreign key (Empl_ID)
      references Employee (Empl_ID)
go

Теперь проектировщик хранилища данных может перейти к созданию индексов.