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

Это продолжение увлекательной статьи про физическая модель хранилища данных.

...

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

увеличить изображение
Рис. 11.7. Задание ограничений NOT NULL на значения колонок

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

В нашем примере таблица фактов имеет уникальный суррогатный ключ "Идентификатор продажи" ( 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 
        ( {  |  
                |  }
        [  ] [ ,...n ] ) 
    [ ON { partition_scheme_name ( partition_column_name ) | filegroup 
        | "default" } ] 

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

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

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

 ::= 
[ 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 ) 
} 
 ::=
column_name AS computed_column_expression 
[ PERSISTED [ NOT NULL ] ]
[ 
    [ CONSTRAINT constraint_name ]
    { PRIMARY KEY | UNIQUE }
        [ CLUSTERED | NONCLUSTERED ]
        [ 
            WITH FILLFACTOR = fillfactor 
          | WITH (  [ , ...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_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 (  [ , ...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 ) 
} 

 ::=
{
    DATA_COMPRESSION = { NONE | ROW | PAGE }
      [ ON PARTITIONS ( {  |  } 
            [ , ...n ] ) ]
}

 ::=
{ 
    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 ( {  |  } 
       [ , ...n ] ) ]
}
 ::= 
 TO 

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

• database_name. Имя БД, в которой создается таблица. В качестве аргумента database_name должно быть указано имя существующей БД. Если аргумент database_name не указан, по умолчанию таблица создается в текущей БД. Имя входа для текущего соединения должно быть связано с идентификатором пользователя, который существует в БД, указанной аргументом database_name, и этот пользователь должен обладать разрешениями CREATE TABLE.
• schema_name. Имя схемы, которой принадлежит новая таблица.
• table_name. Имя новой таблицы. Имена таблиц должны соответствовать правилам для идентификаторов. Аргумент table_name может состоять не более чем из 128 символов, за исключением имен локальных временных таблиц (имена с префиксом номера #), длина которых не должна превышать 116 символов.
• column_name. Имя столбца в таблице. Имена столбцов должны соответствовать правилам для идентификаторов и быть уникальными в данной таблице. Аргумент column_name может содержать от 1 до 128 символов. При создании столбцов с типом данных timestamp аргумент column_name может быть пропущен. Если аргумент column_name не указан, столбцу типа timestamp по умолчанию присваивается имя timestamp.
• computed_column_expression. Выражение, определяющее значение вычисляемого столбца. Вычисляемый столбец представляет собой виртуальный столбец, физически не хранящийся в таблице, если для него не установлен признак PERSISTED. Значение столбца вычисляется на основе выражения, использующего другие столбцы той же таблицы. Например, определение вычисляемого столбца может быть следующим: cost AS price * qty. Выражение может быть именем невычисляемого столбца, константой, функцией, переменной или любой их комбинацией, соединенной одним или несколькими операторами. Выражение не может быть вложенным запросом или содержать типы данных "псевдонимы".

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

1. Вычисляемый столбец нельзя использовать ни в качестве определения ограничения DEFAULT или FOREIGN KEY, ни вместе с определением ограничения NOT NULL. Однако вычисляемый столбец может использоваться в качестве ключевого столбца индекса либо части какого-либо ограничения PRIMARY KEY или UNIQUE, если значение этого вычисляемого столбца определяется детерминистическим выражением и тип данных результата разрешен в столбцах индекса.
2. Например, если таблица содержит целочисленные столбцы a и b, вычисляемый столбец a+b может быть включен в индекс, а вычисляемый столбец a+DATEPART(dd, GETDATE()) — не может, так как его значение может изменяться при последующих вызовах.
3. Вычисляемый столбец не может быть целевым столбцом инструкций INSERT или UPDATE.
   • PERSISTED. Указывает, что SQL Server Database Engine будет физически хранить вычисляемые значения в таблице и обновлять их при изменении любого столбца, от которого зависит вычисляемый столбец. Указание PERSISTED для вычисляемого столбца позволяет создать индекс по вычисляемому столбцу, который будет детерминистическим, но неточным. Любые вычисляемые столбцы, используемые в качестве столбцов секционирования в секционированной таблице, необходимо явно пометить признаком PERSISTED. Если указан признак PERSISTED, аргумент computed_column_expression должно быть детерминистическим.
   • ON { | filegroup | "default" }. Указывает схему секционирования или файловую группу, в которой хранится таблица. Если аргумент указан, таблица будет разбита на секции, сохраняемые в одной или нескольких файловых группах, указанных аргументом . Если указан аргумент filegroup, таблица сохраняется в файловой группе с таким именем. Это должна быть существующая файловая группа в базе данных. Если указано значение "default", или параметр ON не определен вообще, таблица сохраняется в установленной по умолчанию файловой группе. Механизм хранения таблицы, указанный в инструкции CREATE TABLE, изменить в дальнейшем невозможно.

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

• TEXTIMAGE_ON { filegroup | "default" }. Ключевые слова, указывающие, что столбцы типов text, ntext, image, xml, varchar(max), nvarchar(max), varbinary(max), а также пользовательских типов среды CLR хранятся в определенной файловой группе.

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

• FILESTREAM_ON { partition_scheme_name | filegroup | "default" }. Задает файловую группу для данных FILESTREAM.

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

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

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

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

1. Инструкция CREATE INDEX преобразует кучу в кластеризованный индекс. В этом случае можно указать другую файловую группу FILESTREAM, схему секционирования или значение NULL.
2. Инструкция DROP INDEX преобразует кластеризованный индекс в кучу. В этом случае можно указать другую файловую группу FILESTREAM, схему секционирования или значение "default".
   • [ type_schema_name. ] type_name. Указывает тип данных столбца и схему, к которой он принадлежит.

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

1. Системный тип данных.
2. Тип данных — псевдонимы на основе системного типа данных SQL Server. Прежде чем псевдонимы типов данных можно будет использовать в определении таблицы, их нужно создать с помощью инструкции CREATE TYPE. Состояние признака NULL или NOT NULL для типа данных – псевдонима может быть переопределено с помощью инструкции CREATE TABLE. Однако его длину изменить нельзя; длина типа данных – псевдонима не определяется инструкцией CREATE TABLE.
3. Пользовательский тип среды CLR. Прежде чем пользовательские типы среды CLR можно будет использовать в определении таблицы, их нужно создать с помощью инструкции CREATE TYPE. Для создания столбца с пользовательским типом среды CLR требуется разрешение REFERENCES на этот тип.
   • precision. Точность указанного типа данных.
   • Scale. Масштаб указанного типа данных.
   • Max. Применяется только к типам данных varchar, nvarchar и varbinary для хранения байт символьных и двоичных данных или 2^30 байт данных в Юникоде.

• CONTENT. Указывает, что каждый экземпляр типа данных xml в столбце column_name может содержать несколько элементов верхнего уровня. Аргумент CONTENT применим только к данным типа xml и может быть указан только в том случае, если одновременно указан аргумент xml_schema_collection. Если этот параметр не указан, CONTENT принимается в качестве поведения по умолчанию.
• DOCUMENT. Указывает, что каждый экземпляр типа данных xml в столбце column_name может содержать только один элемент верхнего уровня. Аргумент DOCUMENT применим только к данным типа xml и может быть указан только в том случае, если одновременно указан аргумент xml_schema_collection.
• xml_schema_collection.. Применим только к типу данных xml для коллекции XML- схем, связанной с этим типом. До помещения столбца xml схема должна быть создана в БД при помощи инструкции CREATE XML SCHEMA COLLECTION.
• DEFAULT. Указывает значение, присваиваемое столбцу в случае отсутствия явно заданного значения при вставке. Определения DEFAULT могут применяться к любым столбцам, кроме имеющих тип timestamp или обладающих свойством IDENTITY. Если значение по умолчанию указывается для столбца определяемого пользователем типа, этот тип должен поддерживать неявное преобразование выражения constant_expression в определяемый пользователем тип. Определения DEFAULT удаляются, когда таблица удаляется из памяти. В качестве значения по умолчанию могут использоваться только константы (например, символьные строки), скалярные функции (системные, определяемые пользователем или функции среды CLR) или значение NULL. Для совместимости с более ранними версиями SQL Server параметру DEFAULT может быть присвоено имя ограничения.
• constant_expression. Константа, значение NULL или системная функция, используемая в качестве значения столбца по умолчанию.
• IDENTITY. Указывает, что новый столбец является столбцом идентификаторов. При добавлении в таблицу новой строки компонент Database Engine формирует для этого столбца уникальное последовательное значение. Столбцы идентификаторов обычно используются с ограничением PRIMARY KEY для поддержания уникальности идентификаторов строк в таблице. Свойство IDENTITY может быть назначено столбцам типов tinyint, smallint, int, bigint, decimal(p,0) или numeric(p,0). Для каждой таблицы можно создать только один столбец идентификаторов. Ограниченные значения по умолчанию и ограничения DEFAULT не могут использоваться в столбце идентификаторов. Необходимо указать как начальное значение, так и приращение, или же не указывать ничего. Если ни одно из значений не указано, то действительно значение по умолчанию — (1,1).
• Seed. Значение, используемое для самой первой строки, загружаемой в таблицу.
• Increment. Значение приращения, добавляемое к значению идентификатора предыдущей загруженной строки.
• NOT FOR REPLICATION. В инструкции CREATE TABLE предложение NOT FOR REPLICATION может указываться для свойства IDENTITY, а также ограничений FOREIGN KEY и CHECK. Если это предложение указано для свойства IDENTITY, значения в столбцах идентификаторов не приращиваются, когда вставку выполняют агенты репликации. Если ограничение сопровождается этим предложением, оно не выполняется, когда агенты репликации выполняют операции вставки, обновления или удаления.
• ROWGUIDCOL. Указывает, что новый столбец является столбцом идентификаторов GUID. В качестве столбца ROWGUIDCOL можно назначить только один столбец uniqueidentifier в таблице. Применение свойства ROWGUIDCOL позволяет ссылаться на столбец с помощью ключевого слова $ROWGUID. Свойство ROWGUIDCOL можно присвоить только столбцу, имеющему тип uniqueidentifier. Ключевое слово ROWGUIDCOL недопустимо, если уровень совместимости базы данных равен 65 или ниже. Ключевым словом ROWGUIDCOL нельзя обозначать столбцы пользовательских типов данных.

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

• SPARSE. Указывает, что столбец является разреженным столбцом. Хранилище разреженных столбцов оптимизируется для значений NULL. Для разреженных столбцов нельзя указать параметр NOT NULL.
• FILESTREAM. Допустимо только для столбцов типа varbinary(max). Указывает хранилище FILESTREAM для данных BLOB типа varbinary(max).

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

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

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

• COLLATE collation_name. Задает параметры сортировки для столбца. Имя параметров сортировки может быть либо именем параметров сортировки Windows, либо именем параметров сортировки SQL. Аргумент collation_name применим только к столбцам типов данных char, varchar, text, nchar, nvarchar и ntext. Если этот аргумент не указан, столбцу назначаются либо параметры сортировки пользовательского типа (если столбец принадлежит к пользовательскому типу данных), либо установленные по умолчанию параметры сортировки для базы данных.
• CONSTRAINT. Необязательное ключевое слово, указывающее на начало определения ограничения PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN KEY или CHECK.
• constraint_name. Имя ограничения. Имена ограничений должны быть уникальными в пределах схемы, к которой принадлежит таблица.
• NULL | NOT NULL. Определяет, допустимы ли для столбца значения NULL. Параметр NULL не является ограничением в строгом смысле слова, но может быть указан так же, как и NOT NULL. Ограничение NOT NULL может быть указано для вычисляемых столбцов только в случае если одновременно указан параметр PERSISTED.
• PRIMARY KEY. Ограничение, которое обеспечивает сущностную целостность для указанного столбца или столбцов с помощью уникального индекса. Можно создать только одно ограничение PRIMARY KEY для таблицы.
• UNIQUE. Ограничение, которое обеспечивает сущностную целостность для указанного столбца или столбцов с помощью уникального индекса. Таблица может содержать несколько ограничений UNIQUE.
• CLUSTERED | NONCLUSTERED. Указывает, что для ограничения PRIMARY KEY или UNIQUE создается кластеризованный или некластеризованный индекс. Для ограничений PRIMARY KEY по умолчанию создается кластеризованный индекс ( CLUSTERED ), а для ограничений UNIQUE — некластеризованный ( NONCLUSTERED ).

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

• FOREIGN KEY REFERENCES. Ограничение, которое обеспечивает ссылочную целостность данных в этом столбце или столбцах. Ограничения FOREIGN KEY требуют, чтобы каждое значение в столбце существовало в соответствующем связанном столбце или столбцах в связанной таблице. Ограничения FOREIGN KEY могут ссылаться только на столбцы, являющиеся ограничениями PRIMARY KEY или UNIQUE в связанной таблице, или на столбцы, на которые имеются ссылки в индексе UNIQUE INDEX связанной таблицы. Внешние ключи в вычисляемых столбцах должны быть также помечены как PERSISTED.
• [ schema_name . ] referenced_table_name ]. Имя таблицы, на которую ссылается ограничение FOREIGN KEY, и схема, к которой она принадлежит.
• ( ref_column [ ,... n ] ). Столбец или список столбцов из таблицы, на которую ссылается ограничение FOREIGN KEY.
• ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }. Определяет операцию, которая производится над строками создаваемой таблицы, если эти строки имеют ссылочное отношение, а строка, на которую имеются ссылки, удаляется из родительской таблицы. Значение по умолчанию — NO ACTION.
  • NO ACTION.

продолжение следует...

Продолжение:

Часть 1 Физическая модель хранилища данных
Часть 2 Моделирование объектов физической модели хранилища данных - Физическая модель хранилища
Часть 3 Разработка скрипта для создания объектов физической модели хранилища данных -
Часть 4 Ограничения и их использование в реляционной базе данных - Физическая
Часть 5 Резюме - Физическая модель хранилища данных