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

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

...

строки

varchar [( n| max )] Символьные данные переменной длины, не в Юникоде. n может иметь значение от 1 до 8 000, max означает, что максимальный размер хранения равен байт. Размер хранения равен фактической длине данных плюс два байта. Введенные данные могут иметь длину 0 символов. Синонимами по стандарту ISO для типа varchar являются типы char varying или character varying char [ ( n ) ] Символьные данные фиксированной длины, не в Юникоде, с длиной n байт. Значение n должно находиться в интервале от 1 до 8000. Размер хранения данных этого типа равен n байт. Синонимом по стандарту ISO для типа char является character text Этот тип данных представляет данные, отличные от данных Юникод, с использованием кодовой страницы сервера. Максимальная длина данных - - 1 (2 147 483 647) символов. Если в кодовой странице сервера используются двухбайтовые символы, объем занимаемого типом пространства все равно не превышает 2 147 483 647 байт. Он может быть менее 2 147 483 647 байт - в зависимости от строки символов Символьные строки в Юникоде nchar [ ( n ) ] Символьные данные в Юникоде длиной в n символов. Аргумент n должен иметь значение от 1 до 4000. Размер хранилища вдвое больше n байт. Синонимами по стандарту ISO для типа nchar являются типы national char и national character nvarchar [(n|max )] Символьные данные в Юникоде переменной длины. Аргумент n может принимать значение от 1 до 4 000. Аргумент max указывает, что максимальный размер хранилища равен байт. Размер хранилища в байтах вдвое больше числа введенных символов + 2 байта. Введенные данные могут иметь длину в 0 символов. Синонимами по стандарту ISO для типа nvarchar являются типы national char varying и national character varying ntext Этот тип данных представляет символьные данные в Юникоде переменной длины, включающие до - 1 (1 073 741 823) символов. Объем занимаемого этим типом пространства (в байтах) в два раза превышает число символов. В спецификации SQL-2003 синонимом типа данных ntext является тип national text Двоичные данные binary [ ( n ) ] Двоичные данные фиксированной длины размером в n байт, где n - значение от 1 до 8000. Размер хранения составляет n байт varbinary [(n|max)] Двоичные данные переменной длины. n могут иметь значение от 1 до 8000; max означает максимальную длину хранения, которая составляет байт. Размер хранения - это фактическая длина введенных данных плюс 2 байта. Введенные данные могут иметь размер 0 символов. В SQL-2003 синонимом для varbinary является binary varying image Этот тип представляет двоичные данные переменной длины, включающие от 0 до - 1 (2 147 483 647) байт Прочие типы данных timestamp Это тип данных, который представляет собой автоматически сформированные уникальные двоичные числа в базе данных. Тип данных timestamp используется в основном в качестве механизма для отметки версий строк таблицы. Размер при хранении - 8 байт. Тип данных timestamp - всего лишь увеличивающееся значение, которое не сохраняет дату или время. Тип данных datetime используется для записи даты или времени xml ([CONTENT| DOCUMENT] xml_schema_collection ) Тип данных, в котором хранятся XML-данные. Можно хранить экземпляры xml в столбце либо в переменной типа xml.

CONTENT. Экземпляр xml должен быть корректным XML-фрагментом. XML-данные могут содержать несколько (0 или больше) элементов верхнего уровня. Текстовые узлы разрешены на верхнем уровне. Это поведение установлено по умолчанию.

DOCUMENT. Экземпляр xml должен быть корректным XML-документом. XML-данные должны содержать только один корневой элемент. Текстовые узлы на верхнем уровне запрещены.

xml_schema_collection. Имя коллекции XML- схем. Чтобы создать типизированный столбец или переменную xml, можно дополнительно указать имя коллекции XML- схем. Дополнительные сведения о типизированном и нетипизированном XML

sql_variant Столбец типа sql_variant может содержать строки различных типов данных. Например, столбец, определенный как sql_variant, может хранить значения int, binary и char table Особый тип данных, который можно использовать для хранения результирующего набора с целью последующей его обработки. Тип table применяется, главным образом, для временного хранения набора строк, возвращаемого в качестве результирующего набора возвращающей табличное значение функции cursor Тип данных для переменных или выходных параметров хранимых процедур, которые содержат ссылку на курсор. Любая переменная, созданная с типом данных cursor, может принимать значение NULL

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

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

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

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

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

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

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

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

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

• Разработка физической модели ХД:
  • определение базовых таблиц БД;
  • определение колонок в таблицах ;
  • определение типов данных для колонок;
  • назначение первичных ключей таблицам ;
  • задание ограничений NOT NULL на значения колонок;
  • создание связей между таблицами.
• Разработка скрипта создания ХД:
  • формирование команд CREATE TABLE для таблиц ХД;
  • определение ограничений на колонки таблиц ХД;
  • формирование дополнительных индексов командой CREATE INDEX.

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

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

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

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

Для нашего учебного примера создадим пять таблиц: четыре таблицы измерений "Время" (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), чтобы избежать возможного переполнения при работе приложений базы данных.

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

• Следует уточнить, как СУБД физически хранит данные того или иного предопределенного типа, и затем уточнить интервалы изменения значений колонок. Например, если тип переменной — varchar(3), и она содержит код, значение которого изменяется в интервале от '10A' до '99Z', то целесообразно с точки зрения хранения изменить тип этой переменной на char(3). Это объясняется тем, что тип varchar при физическом хранении занимает на байт-два больше, чем тип char при одной и той же объявленной длине.
• Для числовых значений фиксированной длины предпочтительнее использовать тип DEC. Он обрабатывается процессором быстрее, чем тип FLOAT. Исключение составляют данные для научных расчетов, где представление чисел в экспоненциальной форме бывает необходимо.
• Используйте INT и SMALLINT исключительно для счетчиков.
• Избегайте использовать тип CHAR для представления числовых данных. Во-первых, может потребоваться дополнительная проверка, а во-вторых, могут возникнуть проблемы при сортировке таких колонок, поскольку число, заданное строкой '11' будет находиться выше, чем число, заданной строкой '9', при упорядочивании по возрастанию.
• Используйте типы DATE и TIME только для хранения хронологических данных.
• Используйте тип DATETIME исключительно для целей управления данными.

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

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

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

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

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

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

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

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

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

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

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

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

• Колонки, являющиеся частью составного первичного ключа, должны иметь всегда ограничение NOT NULL, т.к. согласно реляционной теории значения колонок первичного ключа должны быть определены и уникальны для каждого кортежа.
• Внешние ключи должны также определяться как NOT NULL, и поскольку дочерняя таблица зависит от родительской, внешний ключ родительской не может иметь NULL-значения. Это следует из того, что существование строки дочерней таблицы без соответствующей строки родительской таблицы нарушает правило зависимости связи (о внешних ключах, родительских и дочерних таблицах см. далее).
• Только внешние ключи для таблицы с опциональной связью могут рассматриваться как кандидаты на наличие NULL-значений, чтобы показать, что для данной комбинации родительской и дочерних строк в этих таблицах связи нет.
• Внешние ключи с правилом удаления SET NULL должны определяться со спецификацией NULL.
• Используйте спецификацию NOT NULL WITH DEFAULT для колонок с типами данных DATE или TIME, чтобы сохранять текущие даты и текущее время автоматически.
• Разрешайте использовать NULL-значения только для тех колонок, которые действительно могут иметь неопределенные значения.
• Используйте NOT NULL WITH DEFAULT для всех колонок, которые не подпадают под перечисленные выше правила.

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

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

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

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

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

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

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

Установив связь "родитель-потомок" между таблицами

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

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

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