Хранилища данных и построение модели данных с помощью PowerDesigner

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

Показывать лекцию целиком

Цель лекции: Изучить основные объекты реляционных баз данных (таблицы, индексы), изучить алгоритм создания физической модели хранилища данных, рассмотреть вопросы построения скрипта создания хранилища данных.

Изучив материал настоящей лекции, вы будете знать:

сможете

научитесь

1. Объекты физической модели данных

В предыдущих лекциях мы изучали различные аспекты методов логического проектирования ХД. Методы логического проектирования основываются на абстрактном рассмотрении данных. Логическая модель никак не связана с конкретной реализацией модели в БД СУБД.

На практике ХД создаются и эксплуатируются как БД под управлением конкретной СУБД. БД, реализующие ХД, создаются на основе физической модели данных, разработанной проектировщиком ХД и реализованной в виде объектов БД.

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

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

Можно выделить два этапа создания физической модели данных. Основными целями первого этапа являются:

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

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

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

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

Далее, мы предполагаем, что решение о выборе СУБД уже принято руководителем ИТ - проекта, и согласовано с заказчиком ХД, т.е. СУБД задана. Проектировщик ХД должен ознакомиться с документацией, в которой описан диалект SQL, поддерживаемый выбранной СУБД.

Иерархия объектов реляционной БД прописана в стандартах по SQL. В упрощенном виде иерархия объектов БД показана на Рис. 6.1 ниже.

Хранилища данных. Лекция 6
Рис. 6.1. Иерархия объектов реляционной базы данных

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

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

Следует отметить, что ни одна из групп объектов стандартов SQL (начиная c SQL92) не связана со структурами физического хранения информации в памяти компьютеров.

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

2. Основные объекты реляционной базы данных

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

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

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

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

К числу основных объектов реляционных БД относятся таблица, представление и пользователь.

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

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

Пользователь (User) - это объект, обладающий возможностью создавать или использовать другие объекты базы данных и запрашивать выполнение функций СУБД таких, как организация сеанса работы, изменение состояние БД и т. д.

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

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

Определенные пользователем типы данных (User-defined data types) представляют собой определенные пользователем типы атрибутов (домены), которые отличаются от поддерживаемых (встроенных) СУБД типов. Они определяются на основе встроенных типов.

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

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

Индекс (Index) - это объект базы данных, создаваемый для повышения производительности выборки данных и контроля уникальности первичного ключа (если он задан для таблицы).

Секция (Partition) - это объект БД, который позволяет представить объект с данными в виде совокупности подобъектов, отнесенных к различным табличным пространствам. Таким образом, секционирование позволяет распределять очень большие таблицы на нескольких жестких дисках.

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

Данные объекты реляционной БД представляют собой программы, т.е. исполняемый код. Этого код обычно называют серверным кодом (server-side code), поскольку он выполняется компьютером, на котором установлено ядро реляционной СУБД.

Хранимая процедура (Stored procedure) - это объект БД, представляющий поименованный набор команд SQL и/или операторов специализированных языков программирования.

Функция (Function) - это объект БД, представляющий поименованный набор команд SQL и/или операторов специализированных языков обработки программирования базы данных, который при выполнении возвращает значение - результат вычислений.

Триггер (Trigger) - это объект БД, который представляет собой специальную хранимую процедуру. Эта процедура запускается автоматически, когда происходит связанное с триггером событие (например, вставка строки в таблицу).

Для эффективного управления разграничением доступа к данным поддерживается объект роль. Роль (Role) - объект БД, который представляет собой поименованную совокупность привилегий, которые могут назначаться пользователям, категориям пользователей или другим ролям.

3. Домены в физической модели данных

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

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

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

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

amount NUMBER (8,2) NOT NULL CONSTRAINT cc_limit_amnt CHECK (amount > 0)

В колонке «Сумма платежа» (Amount) можно размещать только числовые данные; точность этого значения - два значащих десятичных разряда (NUMBER (8,2)); она должна быть заполнена для каждой строки таблицы (NOT NULL); ее значение должно быть положительным CONSTRAINT cc_limit_amnt CHECK (amount > 0). Максимальное значение, которое может храниться в этом столбце, - 999999.99. В этом простом определении колонки мы фактически определили ряд неявных правил, проверку которых СУБД принудительно включает при вводе данных в БД.

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

Начиная со стандарта SQL-92, введено понятие доменов, определенных пользователем. Определение таких доменов базируется на встроенных типах данных СУБД.

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

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

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

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

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

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

Допустимые типы данных в СУБД MS SQL Server

Точные числа

Приблизительные числа

Дата и время

Символьные строки

Символьные строки в Юникоде

Двоичные данные

Прочие типы данных

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

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

Создавать физическую модель будем с помощью CASE-инструмента Sybase PowerDesigner. Этот инструмент позволяет создавать таблицы, определять атрибуты, устанавливать связи. На Рис. 6.2 показана таблица физической модели с тремя колонками, имеющими целочисленный (первичный ключ), строковый и числовой типы данных и одной колонкой с типом дата.

Хранилища данных. Лекция 6
Рис. 6.2. Объект физической модели ХД - таблица фактов

Связь между таблицами (Рис. 6.3) устанавливается с помощью «стрелки». При этом первичный ключ одной таблицы мигрирует в другую таблицу, как внешний (в противоположном направлении).

Хранилища данных. Лекция 6
Рис. 6.3. Объект физической модели ХД - таблица фактов

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

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

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

Хранилища данных. Лекция 6
Рис. 6.4. Логическая модель хранилища данных

Таблица 6.1. Описание атрибутов модели хранилища данных
Атрибут Значение Сущность
TimeID Идентификатор времени, ключ сущности «Время» (Time)
Year Год «Время» (Time)
Quarter Квартал года «Время» (Time)
CustID Идентификатор покупателя, ключ сущности «Покупатель» (Customer)
Cust_FName Имя покупателя «Покупатель» (Customer)
Cust_LName Фамилия покупателя «Покупатель» (Customer)
Cust_Address Адрес покупателя «Покупатель» (Customer)
Cust_Company Место работы «Покупатель» (Customer)
Cust_Tel Телефон «Покупатель» (Customer)
ProdID Идентификатор товара, ключ сущности «Товар» (Product)
Prod_Name Наименование товара «Товар» (Product)
Prod_Size Габариты товара «Товар» (Product)
Unit_Price Цена за единицу товара «Товар» (Product)
EmpID Идентификатор продавца, ключ сущности «Продавец» (Employee)
Emp_FName Имя продавца «Продавец» (Employee)
Emp_LName Фамилия продавца «Продавец» (Employee)
Emp_Address Адрес месторасположения «Продавец» (Employee)
Emp_Tel Телефон «Продавец» (Employee)
Sale_ID Идентификатор продаж, ключ сущности «Продажи» (Sale)
Amount Сумма платежа «Продажи» (Sale)
Quantity Количество «Продажи» (Sale)
     

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

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

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

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

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

Хранилища данных. Лекция 6
Рис. 6.5. Создание таблиц физической модели хранилища данных

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

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

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

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

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

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

Хранилища данных. Лекция 6
Рис. 6.6. Определение имен колонок таблиц физической модели хранилища данных

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

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

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

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

Хранилища данных. Лекция 6
Рис. 6.7. Определение типов данных для колонок таблиц физической модели хранилища данных

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

Задание колонки как первичного ключа в контексте многих СУБД считается ограничением на значение колонки.

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

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

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

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

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

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

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

Хранилища данных. Лекция 6
Рис. 6.8. Задание ограничений NOT NULL на значения колонок

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

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

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

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

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

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

Хранилища данных. Лекция 6
Рис. 6.9. Установление связей между таблицами физической модели ХД

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

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

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

4.3. Создание скрипта для создания хранилища данных

Предположим, что для реализации проекта создания ХД выбрана СУБД Oracle 11g. Sybase PowerDesigner позволяет сгенерировать скрипт для создания ХД, как показано ниже.

/*==============================================================*/ /* СУБД: ORACLE Version 11g */ /* Создан: 02.11.2019 11:29:59 */ /*==============================================================*/ /* Таблица: "Customer" */ /*==============================================================*/ CREATE TABLE "Customer" ( "CustID" INTEGER not null, "Cust_FName" VARCHAR2(20) not null, "Cust_LName" VARCHAR2(20) not null, "Cust_Address" VARCHAR2(40) not null, "Cust_Company" VARCHAR2(20) not null, "Cust_Tel" VARCHAR2(20) not null, constraint PK_CUSTOMER primary key ("CustID") ); /*==============================================================*/ /* Таблица: "Employee" */ /*==============================================================*/ CREATE TABLE "Employee" ( "EmpID" INTEGER not null, "Emp_FName" VARCHAR2(20) not null, "Emp_LName" VARCHAR2(20) not null, "Emp_Address" VARCHAR2(40) not null, "Emp_Tel" VARCHAR2(20) not null, constraint PK_EMPLOYEE primary key ("EmpID") ); /*==============================================================*/ /* Таблица: "Product" */ /*==============================================================*/ CREATE TABLE "Product" ( "ProdID" INTEGER not null, "Prod_Name" VARCHAR2(80) not null, "Prod_Size" VARCHAR2(20) not null, "Unit_Price" NUMBER(8,2) not null, constraint PK_PRODUCT primary key ("ProdID") ); /*==============================================================*/ /* Таблица: "Sale" */ /*==============================================================*/ CREATE TABLE "Sale" ( "SaleID" INTEGER not null, "TimeID" INTEGER, "CustID" INTEGER, "EmpID" INTEGER, "ProdID" INTEGER, "Amount" NUMBER(9,2) not null, "Quantity" INTEGER not null, constraint PK_SALE primary key ("SaleID") ); /*==============================================================*/ /* Таблица: "Time" */ /*==============================================================*/ CREATE TABLE "Time" ( "TimeID" INTEGER not null, "Year" INTEGER not null, "Quanter" INTEGER not null, constraint PK_TIME primary key ("TimeID") ); ALTER TABLE "Sale" add constraint FK_SALE_REFERENCE_TIME foreign key ("TimeID") references "Time" ("TimeID"); ALTER TABLE "Sale" add constraint FK_SALE_REFERENCE_CUSTOMER foreign key ("CustID") references "Customer" ("CustID"); ALTER TABLE "Sale" add constraint FK_SALE_REFERENCE_EMPLOYEE foreign key ("EmpID") references "Employee" ("EmpID"); ALTER TABLE "Sale" add constraint FK_SALE_REFERENCE_PRODUCT foreign key ("ProdID") references "Product" ("ProdID");

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

Команда CREATE TABLE создает новую таблицу в БД. Базовый синтаксис этой команды приведен ниже.

CREATE TABLE Имя_таблицы ( Имя_колонки Тип_данных [ NULL | NOT NULL ] [DEFAULT выражение], …. [ CONSTRAINT Имя_ограничения ] [ PRIMARY KEY | UNIQUE ] | [ FOREIGN KEY ] [ ( Имя_колонки ) ] … ); <Тип_данных> ::= Имя_типа [ (точность [ , масштаб ] | размер |

Рассмотрим элементы и аргументы команды CREATE TABLE:

Выше было дано описание не всех аргументов команды CREATE TABLE.

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

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

Ограничения на объекты реляционной базы данных

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

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

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

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

Создание таблиц измерений. Команда CREATE TABLE для создания таблицы измерения «Покупатель» (Customer) имеет вид:

CREATE TABLE "Customer" ( "CustID" INTEGER not null, "Cust_FName" VARCHAR2(20) not null, "Cust_LName" VARCHAR2(20) not null, "Cust_Address" VARCHAR2(40) not null, "Cust_Company" VARCHAR2(20) not null, "Cust_Tel" VARCHAR2(20) not null, constraint PK_CUSTOMER primary key ("CustID") );

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

Команда CREATE TABLE для создания таблицы измерения «Продавец» (Employee) имеет вид:

CREATE TABLE "Employee" ( "EmpID" INTEGER not null, "Emp_FName" VARCHAR2(20) not null, "Emp_LName" VARCHAR2(20) not null, "Emp_Address" VARCHAR2(40) not null, "Emp_Tel" VARCHAR2(20) not null, constraint PK_EMPLOYEE primary key ("EmpID") );

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

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

CREATE TABLE "Product" ( "ProdID" INTEGER not null, "Prod_Name" VARCHAR2(80) not null, "Prod_Size" VARCHAR2(20) not null, "Unit_Price" NUMBER(8,2) not null, constraint PK_PRODUCT primary key ("ProdID") );

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

Prod_Name varchar(80) not null UNIQUE,

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

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

Команда CREATE TABLE для создания таблицы измерения «Время» (Time) имеет вид:

CREATE TABLE "Time" ( "TimeID" INTEGER not null, "Year" INTEGER not null, "Quanter" INTEGER not null, constraint PK_TIME primary key ("TimeID") );

По решению руководства компании данные в ХД будут заноситься, начина с 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) имеет вид:

CREATE TABLE "Sale" ( "SaleID" INTEGER not null, "TimeID" INTEGER, "CustID" INTEGER, "EmpID" INTEGER, "ProdID" INTEGER, "Amount" NUMBER(9,2) not null, "Quantity" INTEGER not null, constraint PK_SALE primary key ("SaleID") );

Значения колонок «Количество» (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 Имя_таблицы { [ ALTER COLUMN Имя_колонки {DROP DEFAULT } | ADD { < Определение-колонки > | < Ограничение_таблицы > } [ ,...n ] | DROP { [ CONSTRAINT ] Имя_ограничения | COLUMN колонка } ] }

Описание аргументов:

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

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

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

CREATE [ UNIQUE ] INDEX имя_индекса ON <Имя_таблицы> ( колонка [ ASC | DESC ] [ ,...n ] )

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

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

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

CREATE UNIQUE INDEX Idx1 ON Product(Prod_Name); CREATE UNIQUE INDEX Idx2 ON Employee (Emp_LName);

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

Резюме

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

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

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

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

Предыдущее занятие Следующее занятие
Вернуться к учебному плану
^