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

Допустимые типы данных

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

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

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

Описание типов, данное в таблице ниже, относится к диалекту SQL для СУБД семейства MS SQL Server, которое имеет существенные отличия от предписаний стандарта SQL. Жирным шрифтом выделена часть зарезервированного слова для определения типа, которую можно использовать как аббревиатуру при определении типа в спецификации колонки.

MS SQL Server предоставляет набор системных типов данных, определяющих все типы данных, которые могут использоваться в нем. Можно также определять собственные типы данных в Transact-SQL или Microsoft .NET Framework. Псевдонимы типов данных основываются на системных типах. Пользовательские типы данных обладают свойствами, зависящими от методов и операторов класса, который создается для них на одном из языков программирования, поддерживающих .NET Framework.

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

Типы данных в СУБД семейства MS SQL Server объединены в следующие категории:

Итак, мы рассмотрели основные объекты БД и допустимые типы данных, которыми располагает проектировщик ХД при использовании СУБД семейства MS SQL Server.

Далее поговорим об алгоритме создания физической модели ХД.

Моделирование объектов физической модели хранилища данных

Описание учебного примера

Рассмотрим в качестве примера логическую модель ХД типа "звезда" на рис 11.2 и построим на ее основе физическую модель ХД.

Логическая модель ХД, приведенная на рисунке, была разработана для анализа продаж компании в разрезах товаров, продавцов, покупателей, времени продаж. Она включает в себя четыре сущности для измерений: "Время" (Time), "Покупатель" (Customer), "Товар" (Product), "Продавец" (Employee) – и одну сущность для фактов "Продажи" (Sale).

Как видно из приведенной схемы, для атрибутов определены домены "Целое число", "Десятичное число", "Текст" размером в 20 и 40 символов.

Описание атрибутов модели приведено в табл. 11.2.

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

Моделирование таблиц хранилища данных

Определение базовых таблиц

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

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

Для нашего учебного примера создадим пять таблиц: четыре таблицы измерений "Время" (Time), "Покупатель" (Customer), "Товар" (Product), "Продавец" (Employee) и одну таблицу фактов "Продажи" (Sale). Имена таблиц будут соответствовать именам сущностей логической модели ХД (рис 11.3).

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

Определение колонок в таблицах

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

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

Имеется еще одна проблема в именовании колонок – имена колонок должны интерпретироваться пользователем однозначно. Например, если проектировщик назначит для фамилии сотрудника короткое имя LN, то, наверное, потребуется комментарий, в котором необходимо указать, что это фамилия, а не линия (например, в смысле "линия производства"). Если невозможно использовать по каким-то причинам длинные имена полей, то следует использовать словарь данных для интерпретации введенных аббревиатур.

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

Заметим, что для соблюдения принципа уникальности имен полей в рамках модели при определении полей на основе имен атрибутов сущностей мы изменили некоторые имена. Так, имя атрибута "Адрес" (Address) сущности "Продавцы" (Employee) в соответствующей таблице "Продавцы" (Employee) физической модели стало "Адрес продавца" (Empl_Address). Аналогично имя атрибута "Адрес" (Address) сущности "Покупатели" (Customer) в соответствующей таблицы "Покупатели" (Customer) физической модели стало "Адрес покупателя" (Cust_Address).

Определение типов данных для колонок

После идентификации колонок необходимо задать их тип в соответствии с допустимыми для данной СУБД типами данных. Эта задача упрощается, если в отношениях логической модели определены домены атрибутов. Некоторые из доменов могут быть определены уже в терминах СУБД. Для таких атрибутов практически ничего делать не нужно. Определение домена в терминах типа данных СУБД нужно просто перенести в спецификацию колонки. Возможно, проектировщику будет нужно уточнить второстепенные параметры типа. Например, если задан домен как DEC(9,2), а из контекста предметной области следует, что в этой колонке будет накапливаться итоговая сумма расходов за год, то, может быть, целесообразно определить тип как DEC(15,2), чтобы избежать возможного переполнения при работе приложений базы данных.

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

В нашем учебном примере для всех атрибутов задан домен. Для суррогатных первичных идентификаторов сущностей это числовое значение. Учитывая, что ХД будет хранить большие объемы данных, для представления таких атрибутов в БД целесообразно выбрать тип данных bigint, а для остальных атрибутов — тип данных integer. Домен "Текст" можно представить типом данных varchar(n), для представления десятичных чисел использовать тип данных numeric (p, s ).

Результат определения типов колонок таблиц физической модели ХД показан на рис 11.5.

Назначение первичных ключей таблицам

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

Задание колонки как первичного ключа в контексте многих СУБД, в том числе и семейства MS SQL Server, считается ограничением на значение колонки (см. далее настоящую лекцию).

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

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

Задание ограничений NOT NULL на значения колонок

При определении спецификаций колонок таблиц проектировщик должен рассмотреть ограничения, которые могут быть наложены на значения колонок. В реляционных СУБД предусмотрено достаточно много подобных ограничений. Здесь мы остановимся на одном из главных – на обязательности присутствия значения в колонке. Такое ограничение на значения колонки называется NOT NULL-ограничением.

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

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

При назначении NULL-значений колонкам проектировщику необходимо принимать во внимание следующие факторы.

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

Создание связей между таблицами

Следующим шагом в моделировании физической модели ХД является установление взаимосвязи между таблицами модели ХД.

Таблицы измерения и таблица фактов в многомерной модели данных находятся в отношении "родитель-потомок". Первичный и соответствующий ему внешний ключ позволяют реализовать отношение "родитель-потомок" (parent/child relationship) между таблицами реляционной БД. Они отражают взаимосвязь между объектами предметной области (представленными кортежами таблиц ) через значения некоторых их атрибутов по принципу иерархического подчинения, когда объект-родитель определяет существование объектов-потомков. Сами объекты-потомки могут также выступать в качестве родителей для других объектов (descendents).

Таблица реляционной базы данных, содержащая первичный ключ, называется таблицей-родителем (parent table), или родительской таблицей, а таблица, содержащая соответствующий первичному ключу внешний ключ, — таблицей-потомком (child table), или дочерней таблицей. Таблица измерений "Товары" (Product) учебного примера является таблицей-родителем для таблицы фактов "Продажи" (Sale).

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

Отношения "родитель-потомок" между двумя таблицами отражают взаимосвязь по включению на доменах соответствующих атрибутов.

Установив связь "родитель-потомок" между таблицами измерений и таблицей фактов нашего учебного примера, получим физическую модель ХД (рис 11.7)

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

В нашем примере таблица фактов имеет уникальный суррогатный ключ "Идентификатор продажи" ( Sale_ID ), поэтому внешние ключи, реализующие отношение "родитель-потомок" между таблицами измерений и таблицей фактов, не обязательно включать в составной первичный ключ таблицы фактов. Любая строка таблицы фактов однозначно идентифицируется уже назначенным первичным ключом.

После создания физической модели ХД проектировщик ХД может перейти к решению задачи разработки скрипта для создания ХД. Эта задача может быть решена и вручную, как мы сделаем это в следующем разделе настоящей лекции, и при помощи CASE-средств.

Разработка скрипта для создания объектов физической модели хранилища данных

Команды SQL для создания таблиц базы данных

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

Команда CREATE TABLE создает новую таблицу в БД. Синтаксис этой команды для диалекта SQL (Transact-SQL) СУБД семейства MS SQL Server приведен ниже.

CREATE TABLE
    [ database_name . [ schema_name ] . | schema_name . ] table_name 
        ( { <column_definition> | <computed_column_definition> 
                | <column_set_definition> }
        [ <table_constraint> ] [ ,...n ] ) 
    [ ON { partition_scheme_name ( partition_column_name ) | filegroup 
        | "default" } ] 

    [ { TEXTIMAGE_ON { filegroup | "default" } ] 
    [ FILESTREAM_ON { partition_scheme_name | filegroup 
        | "default" } ]
    [ WITH ( <table_option> [ ,...n ] ) ]
[ ; ]

<column_definition> ::=
column_name <data_type>
    [ FILESTREAM ]
    [ COLLATE collation_name ] 
    [ NULL | NOT NULL ]
    [ 
        [ CONSTRAINT constraint_name ] DEFAULT constant_expression ] 
      | [ IDENTITY [ ( seed ,increment ) ] [ NOT FOR REPLICATION ] 
    ]
    [ ROWGUIDCOL ] [ <column_constraint> [ ...n ] ] 
    [ SPARSE ] 

<data type> ::= 
[ type_schema_name . ] type_name 
    [ ( precision [ , scale ] | max | 
        [ { CONTENT | DOCUMENT } ] xml_schema_collection ) ] 

<column_constraint> ::= 
[ CONSTRAINT constraint_name ] 
{     { PRIMARY KEY | UNIQUE } 
        [ CLUSTERED | NONCLUSTERED ] 
        [ 
            WITH FILLFACTOR = fillfactor  
          | WITH ( < index_option > [ , ...n ] ) 
        ] 
        [ ON { partition_scheme_name ( partition_column_name ) 
            | filegroup | "default" } ]
  | [ FOREIGN KEY ] 
        REFERENCES [ schema_name . ] referenced_table_name [ ( ref_column ) ] 
        [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 
        [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 
        [ NOT FOR REPLICATION ] 
  | CHECK [ NOT FOR REPLICATION ] ( logical_expression ) 
} 
<computed_column_definition> ::=
column_name AS computed_column_expression 
[ PERSISTED [ NOT NULL ] ]
[ 
    [ CONSTRAINT constraint_name ]
    { PRIMARY KEY | UNIQUE }
        [ CLUSTERED | NONCLUSTERED ]
        [ 
            WITH FILLFACTOR = fillfactor 
          | WITH ( <index_option> [ , ...n ] )
        ]
    | [ FOREIGN KEY ] 
        REFERENCES referenced_table_name [ ( ref_column ) ] 
        [ ON DELETE { NO ACTION | CASCADE } ] 
        [ ON UPDATE { NO ACTION } ] 
        [ NOT FOR REPLICATION ] 
    | CHECK [ NOT FOR REPLICATION ] ( logical_expression ) 
    [ ON { partition_scheme_name ( partition_column_name ) 
        | filegroup | "default" } ]
] 

<column_set_definition> ::=
column_set_name XML COLUMN_SET FOR ALL_SPARSE_COLUMNS

< table_constraint > ::=
[ CONSTRAINT constraint_name ] 
{ 
    { PRIMARY KEY | UNIQUE } 
        [ CLUSTERED | NONCLUSTERED ] 
                (column [ ASC | DESC ] [ ,...n ] ) 
        [ 
            WITH FILLFACTOR = fillfactor 
           |WITH ( <index_option> [ , ...n ] ) 
        ]
        [ ON { partition_scheme_name (partition_column_name)
            | filegroup | "default" } ] 
    | FOREIGN KEY 
                ( column [ ,...n ] ) 
        REFERENCES referenced_table_name [ ( ref_column [ ,...n ] ) ] 
        [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 
        [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 
        [ NOT FOR REPLICATION ] 
    | CHECK [ NOT FOR REPLICATION ] ( logical_expression ) 
} 

<table_option> ::=
{
    DATA_COMPRESSION = { NONE | ROW | PAGE }
      [ ON PARTITIONS ( { <partition_number_expression> | <range> } 
            [ , ...n ] ) ]
}

<index_option> ::=
{ 
    PAD_INDEX = { ON | OFF } 
  | FILLFACTOR = fillfactor 
  | IGNORE_DUP_KEY = { ON | OFF } 
  | STATISTICS_NORECOMPUTE = { ON | OFF } 
  | ALLOW_ROW_LOCKS = { ON | OFF} 
  | ALLOW_PAGE_LOCKS ={ ON | OFF} 
  | DATA_COMPRESSION = { NONE | ROW | PAGE }
       [ ON PARTITIONS ( { <partition_number_expression> | <range> } 
       [ , ...n ] ) ]
}
<range> ::= 
<partition_number_expression> TO <partition_number_expression>

Рассмотрим элементы и аргументы команды CREATE TАBLE.

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

Параметр ON {<partition_scheme> | filegroup | "default"} может также указываться в ограничении PRIMARY KEY или UNIQUE. С помощью этих ограничений создаются индексы. Если указан аргумент filegroup, индекс сохраняется в файловой группе с таким именем. Если указано значение "default" или параметр ON не определен вообще, индекс сохраняется в той же файловой группе, что и таблица. Если ограничение PRIMARY KEY или UNIQUE создает кластеризованный индекс, страницы данных таблицы сохраняются в той же файловой группе, что и индекс. Если ограничение создает кластеризованный индекс (с помощью параметра CLUSTERED или другим способом), то указанный аргумент <partition_scheme> отличается от аргументов <partition_scheme> и filegroup из определения таблицы, либо наоборот, принимается во внимание только определение ограничения, а все остальное не учитывается.

Параметр TEXTIMAGE_ON недопустим, если в таблице нет столбцов с большими значениями. Нельзя указывать параметр TEXTIMAGE_ON одновременно с параметром <partition_scheme>. Если указано значение "default" или параметр TEXTIMAGE_ON не определен вообще, столбцы с большими значениями сохраняются в установленной по умолчанию файловой группе. Способ хранения любых данных столбцов с большими значениями, определенный инструкцией CREATE TABLE, изменить в дальнейшем невозможно.

Если таблица содержит данные FILESTREAM и является секционированной, необходимо включить предложение FILESTREAM_ON и указать схему секционирования файловых групп файлового потока. В этой схеме секционирования должны использоваться те же функции и столбцы секционирования, что и в схеме секционирования для таблицы ; в противном случае возникает ошибка.

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

Если таблица не является секционированной и предложение FILESTREAM_ON не указано, используется файловая группа FILESTREAM, для которой задано свойство DEFAULT. При отсутствии файловой группы FILESTREAM возникает ошибка.

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

Тип данных может быть одним из следующих.

Свойство ROWGUIDCOL не обеспечивает уникальности значений, хранимых в столбце. Кроме того, при указании данного свойства автоматическое формирование значений для новых строк, вставляемых в таблицу, не выполняется. Для создания уникальных значений в каждом столбце следует применять в инструкциях INSERT функции NEWID или NEWSEQUENTIALID либо использовать эти функции по умолчанию для столбца.

Таблица также должна содержать столбец типа uniqueidentifier с атрибутом ROWGUIDCOL. Этот столбец не должен допускать значений NULL и должен иметь ограничение, относящееся к одному столбцу, UNIQUE или PRIMARY KEY. Значение идентификатора GUID для столбца должно быть предоставлено приложением во время вставки данных или ограничением DEFAULT, в котором используется функция NEWID ().

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

Если для столбца указан атрибут хранилища FILESTREAM, то все значения для этого столбца хранятся в контейнере данных FILESTREAM в файловой системе.

В инструкции CREATE TABLE параметр CLUSTERED можно задать только для одного ограничения. Если для ограничения UNIQUE указан параметр CLUSTERED и, кроме того, указано ограничение PRIMARY KEY, то для PRIMARY KEY применяется по умолчанию значение NONCLUSTERED.

<Выражение_номера_секции> можно указать одним из следующих способов:

<Диапазон> можно указать номерами секций, разделенными ключевым словом TO, например: ON PARTITIONS (6 TO 8).

Чтобы для разных секций задать разные типы сжатия данных, укажите параметр DATA_COMPRESSION несколько раз:

Выше было дано описание аргументов команды CREATE TABLE. В настоящей лекции мы будем обсуждать не все из перечисленных аргументов. Секционирование таблиц будет рассмотрено в лекции 14. Сейчас мы остановимся на использовании ограничений.

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

В предыдущих разделах мы уже сталкивались с несколькими типами ограничений в спецификациях колонок — 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. Дадим описание только специфичных для этой команды аргументов.

Чтобы добавить ограничения внешнего ключа в таблицу фактов "Продажи" (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

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

Создание индексов хранилища данных

Когда вы определяете PRIMARY KEY при создании таблицы, многие реляционные СУБД, в том числе СУБД семейства MS SQL Server, требуют обязательного создания уникального индекса первичного ключа. Индексы, так же как и таблицы, являются объектами реляционной <L (но не реляционной модели данных). Логически индексы представляют собой таблицу, в которой каждому значению индексируемой колонки ставится в соответствие некоторая информация, связанная с ее месторасположением на физическом носителе. Индексы предназначены для организации быстрого доступа к строкам таблицы и обеспечения контроля целостности данных (механизм индексов будет блокировать БД от повторного ввода строк в таблицу с одинаковыми значениями индексируемых атрибутов).

Индекс создается с помощью команды CREATE INDEX. Эта команда генерирует реляционный индекс или представление для указанной таблицы. Индекс может быть создан до появления данных в таблице. Реляционные индексы для таблиц или представлений могут быть созданы в другой базе данных, если указать ее полное имя.

Синтаксис команды CREATE INDEX приведен ниже.

Create Relational Index 
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name 
    ON <object> ( column [ ASC | DESC ] [ ,...n ] ) 
    [ INCLUDE ( column_name [ ,...n ] ) ]
    [ WHERE <filter_predicate> ]
    [ WITH ( <relational_index_option> [ ,...n ] ) ]
    [ ON { partition_scheme_name ( column_name ) 
         | filegroup_name 
         | default 
         }
    ]
    [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]
[ ; ]
<object> ::=
{
    [ database_name. [ schema_name ] . | schema_name. ] 
        table_or_view_name
}
<relational_index_option> ::=
{
    PAD_INDEX = { ON | OFF }
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | DROP_EXISTING = { ON | OFF }
  | ONLINE = { ON | OFF }
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE} 
     [ ON PARTITIONS ( { <partition_number_expression> | <range> } 
     [ , ...n ] ) ]
}
<filter_predicate> ::= 
    <conjunct> [ AND <conjunct> ]
<conjunct> ::=
    <disjunct> | <comparison>
<disjunct> ::=
        column_name IN (constant ,…)
<comparison> ::=
        column_name <comparison_op> constant
 <comparison_op> ::=
    { IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !< }
<range> ::= 
<partition_number_expression> TO <partition_number_expression>

Значения аргументов команды следующие.

Компонент Database Engine не позволяет создать уникальный индекс по столбцам, уже содержащим повторяющиеся значения, даже если параметру IGNORE_DUP_KEY присвоено значение ON. При попытке написания такого индекса компонент Database Engine выдает сообщение об ошибке. Прежде чем создавать уникальный индекс по такому столбцу или столбцам, необходимо удалить все повторяющиеся значения. Столбцы, используемые в уникальном индексе, должны иметь свойство NOT NULL, т.к. при создании индекса значения NULL рассматриваются как повторяющиеся.

Если аргумент CLUSTERED не указан, создается некластеризованный индекс.

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

Имена столбцов в списке INCLUDE не могут повторяться и не могут использоваться одновременно как ключевые и неключевые.

Предложение CREATE INDEX определяет имя индекса ; предложение ON определяет имя таблицы и колонок, для которой и по которым строится индекс ; ключевое слово UNIQUE указывает, что индексируемые значения колонок должны быть уникальными для таблицы, т. е. исключается дублирование значений в индексируемой колонке. Таблица должна быть уже создана и содержать определения индексируемых столбцов. Спецификация UNIQUE опциональна, и вы можете также создавать и неуникальные индексы.

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

Колонками – кандидатами для создания дополнительных индексов являются в нашем случае, хотя это можно и оспорить, "Наименование товара" (Name) таблицы измерения "Товар" (Product) и "Фамилия продавца" (Empl_LName) таблицы измерения "Продавцы" (Employee). Создадим эти индексы, как показано ниже.

CREATE UNIQUE CLUSTERED INDEX Idx1 ON Product(Name);
go
CREATE UNIQUE CLUSTERED INDEX Idx2 ON Employee (Empl_LName);
go

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

Резюме

В этой лекции мы рассмотрели принципы разработки физической модели ХД. Создание физической модели ХД состоит в моделировании и создании объектов для хранения данных в БД конкретной СУБД. Эта задача сводится к моделированию и созданию таблиц и объектов в БД, в которых будет храниться информация о сущностях предметной области ХД. Решая эту задачу, проектировщик отображает отношения логической модели данных ХД в таблицы и индексы БД. Для выполнения этой задачи используется подмножество команд SQL – язык определения данных DDL (Data Definition Language).

ХД данных создается в реляционной БД. Физическая модель реляционной БД есть такое представление отношений БД и связей между ними, которое воплощено в последовательность команд SQL. Выполнение этой последовательности команд создает конкретную БД и ее объекты.

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

Общий алгоритм построения физической модели ХД включает в себя следующие действия.