ПБД_лабораторная2

Лабораторная работа № 2 (2 часа)

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

Цель работы:

−освоить роль CASE-средства ERWin при нормализации и денормализации БД,

−построить физическую модель.

1. Нормализация

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

o первая нормальная форма (1NF); o вторая нормальная форма (2NF); o третья нормальная форма (3NF);

o нормальная форма Бойса - Кодда (усиленная 3NF); o четвертая нормальная форма (4NF);

o пятая нормальная форма (5NF).

Поддержка нормализации в ERwin. ERwin не содержит полного алгоритма нормализации и не может проводить нормализацию автоматически, однако его воз- можности облегчают создание нормализованной модели данных. Запрет на при- своение неуникальных имен атрибутов в рамках модели (при соответствующей ус- тановке опции Unique Name) облегчает соблюдение правила "один факт - в одном месте".

Денормализация. В результате нормализации все взаимосвязи данных стано- вятся правильно определены, исключаются аномалии при оперировании с данными, модель данных становится легче поддерживать. Однако часто нормализация данных не ведет к повышению производительности БД в целом.

Для получения полной информации о сотруднике из ненормализованной структуры данных достаточно обратиться к одной таблице (см. рис. 1).

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

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

денном примере общая производительность БД в результате нормализации скорее

1

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

Рис. 1. Ненормализованная сущность "Сотрудник"

Рис. 2. Сущность "Сотрудник", приведенная к третьей нормальной форме

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

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

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

Должность и Сотрудник. Если в таблицу Сотрудник добавить колонку Оклад

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

Для решения проблемы можно делать выборку только из таблицы Сотрудник,

2

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

Рис. 3. Пример денормализации

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

подождать до начала следующего дня после редактирования справочника Должность, то такое решение вполне допустимо.

Заметим, что приведенный пример следует воспринимать исключительно как иллюстрацию, а не как руководство к действию.

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

ERwin имеет следующую функциональность для поддержки денормализации: Сущности, атрибуты, ключи и домены можно создавать только на уровне ло- гической модели, включив в соответствующих редакторах опцию Logical Only. Та- кие объекты не будут отображаться на уровне физической модели и не будут созда-

ваться при генерации БД.

Таблицы, колонки, домены и индексы можно создавать только на уровне фи- зической модели (опция Physical Only). Например, на уровне только физической мо- дели может быть создана колонка Оклад таблицы Сотрудник, см. рис. 3.

При автоматическом разрешении связи многие-ко-многим в физической моде-

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

3

2. Домены

Домен можно определить как совокупность значений, из которых берутся зна- чения атрибутов. Каждый атрибут может быть определен только на одном домене, но на каждом домене может быть определено множество атрибутов. В понятие до- мена входит не только тип данных, но и область значений данных. Например, мож- но определить домен "Возраст" как положительное целое число и определить атри- бут Возраст сотрудника как принадлежащий этому домену.

В ERwin домен может быть определен только один раз и использоваться как в логической, так и в физической модели.

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

Для создания домена в логической модели служит диалог Domain Dictionary (рис. 4).

Рис. 4. Диалог Domain Dictionary

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

Domain Dictionary следует:

1)щелкнуть по кнопке New. Появляется диалог New Domain (рис. 5);

2)выбрать родительский домен из списка Domain Parent. Новый домен можно

создать на основе уже созданного пользователем домена либо на основе изначально существующего. По умолчанию ERwin имеет четыре предопределенных домена (String, Number, Blob, Datetime). Новый домен наследует все свойства родительского

4

домена. Эти свойства в дальнейшем можно переопределить;

Рис. 5. Диалог New Domain

3)набрать имя домена в поле Logical Name. Можно также указать имя домена на физическом уровне в поле Physical Name. Если физическое имя не указано, по умолчанию оно принимает значение логического имени;

4)щелкнуть по кнопке ОК.

Вдиалоге Domain Dictionary можно связать домен и иконкой, с которой он бу- дет отображаться в списке доменов (Domain Icon), и иконкой, с которой атрибут, определенный на домене, будет отображаться в модели (Icon Inherited by Attribite).

Каждый домен может быть описан в закладке Definition, снабжен комментари- ем в закладке Note или свойством, определенным пользователем в закладке UDP.

На физическом уровне диалог Domain Dictionary позволяет редактировать фи- зические свойства домена. На рис. 6 показана закладкаSQL Server. Имя этой заклад- ки зависит от выбранного сервера БД. На ней можно задать конкретный тип данных, соответствующих домену, правила присвоения NULL-значений, правила валидации (правила проверки допустимых значений) и задания значения по умолчанию. Пра-

вила валидации и значения по умолчанию должны быть предварительно описаны и именованы (на рис. 6 для домена "Возраст" заданы соответственно правило валида- ции "Проверка_возраста" и значение по умолчанию "Возраст по умолчанию"). Для вызова диалогов редактирования правил валидации и значений по умолчанию слу-

жат кнопки справа от соответствующего списка выбора (Valid и Default). Рассмотрим функции других закладок диалога Domain Dictionary Editor: General Задание родительского домена (Domain Parent) и имени, присваивае-

мого колонке при ее создании с помощью Independent Column Browser. С помощью опции Physical Only домен можно определить только на уровне физической модели.

5

ветствующих сущностей и атрибутов, учитывая максимальную длину имени и дру- гие синтаксические ограничения, накладываемые СУБД. При генерации имени таб- лицы или колонки по умолчанию все пробелы автоматически преобразуются в сим- волы подчеркивания, а длина имени обрезается до максимальной длины, допусти- мой для выбранной СУБД. Все изменения, сделанные в Table Properties или Columns, не отражаются на именах сущностей и атрибутов, поскольку информация на логическом и физическом уровнях в ERwin хранится отдельно.

Редактор Table Properties позволяет задать свойства любой таблицы модели, отличные от значения по умолчанию, в том числе имя таблицы, синонимы, правила валидации, процедуры и т. д. Переключиться на другую таблицу можно при помощи раскрывающегося списка выбора в верхней части диалога (рис. 7).

Рис. 7. Диалог Table Properties

Окно Name служит для задания имени текущей таблицы. Окно Owner позво- ляет внести имя владельца таблицы, отличное от имени пользователя, производяще- го генерацию схемы БД. Окно выбора Physical Only служит для создания объектов только на физическом уровне. Если выбрана опция Generate, при генерации схемы БД будет выполняться команда CREATE TABLE. Кнопка DB Sync служит для не- медленной синхронизации модели с системным каталогом БД.

Диалог Table Properties содержит следующие закладки: Comment. Внесение комментария к таблице.

7

Volumetrics. Служит для оценки размера БД.

Physical Props. Позволяет задать физические свойства таблицы. UDP. Задание свойств, определяемых пользователем. Validation. Задание правил валидации.

Для задания свойств колонок, отличных от значения по умолчанию, служит редактор Columns (рис. 8).

Рис. 8. Диалог Columns

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

По умолчанию ERwin присваивает режимы нулевых значений всем неключе- вым колонкам, исходя из значений по умолчанию, устанавливаемых в редакторе Target Server. Для колонок первичного ключа и альтернативных ключей устанавли- вается режим NOT NULL. Режим NOT NULL не присваивается автоматически ин-

версионным входам (Inversion Entry).

Внешне диалог Columns напоминает диалог Attributes. В правой части диалога находятся закладки:

General. Позволяет присвоить колонку определенному домену, создать ко- лонку только на физическом уровне и включить ее в состав первичного ключа.

Закладка, соответствующая выбранной СУБД (на рис. 8 – SQL Server). Имя закладки устанавливается автоматически соответствующей выбранной СУБД. По- зволяет задать тип данных, опцию NULL, правила валидации и значение по умолча- нию. Правила валидации и значение по умолчанию должны быть описаны и имено- ваны предварительно соответственно в диалогах Validation Rules и Default/Initial

Values. Для вызова этих диалогов служат кнопки справа от соответствующих раскрывающихся списков (рис. 8). Для СУБД Access, AS/400, PROGRESS и Teradata

создаются дополнительные закладки для задания свойств.

8

Comment. Служит для внесения комментария к каждой колонке. UDP. Задание свойств, определяемых пользователем.

Index. Служит для включения колонки в состав индексов.

В левой части диалога содержится упорядоченный список колонок таблицы.

Кнопки “ ”, “ ” предназначены для перемещения колонки в списке на пози- цию вверх и вниз. Кнопки New, Rename и Delete служат соответственно для созда- ния, переименования и удаления колонки. При помощи кнопки Reset можно переус- тановить свойства колонки, заданные вручную, на значения по умолчанию. Кнопка DB Sync позволяет запустить процесс синхронизации модели с системным катало- гом БД.

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

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

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

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

связи нужно щелкнуть по кнопке затем по родительской таблице и, наконец, по представлению (рис. 9). Связи с представлениями и прямоугольники представлений показываются на диаграмме пунктирными линиями.

Для редактирования представления служит диалог Views (рис. 10). Для его вызова следует щелкнуть правой кнопкой мыши по представлению и выбрать в ме-

ню пункт Database View Properties.

Раскрывающийся список View позволяет выбрать для редактирования любое представление модели. Окно Name служит для редактирования имени, а Owner- владельца представления.

Диалог Views имеет следующие закладки (рис. 10):

Select. Имеет два списка: в правом отображаются колонки представления, в левом - колонки доступные для включения в представление. Кнопка New Expression позволяет задать выражение в качестве выходного столбца. Например, для пред- ставления V_43 на рис. 9 в качестве колонок созданы City и выражение с именем "Количество_клиен-тов_в_городе", которое представляет собой агрегативную функцию, подсчитывающую количество строк, Count(*). По умолчанию при созда- нии связи ERwin включает в представление все колонки родительских таблиц.

From. Позволяет выбрать родительские таблицы представления. По умолча- нию включаются таблицы, с которыми связано представление. Каждой таблице можно задать синоним (поле Alias), который будет использоваться при создании

9

SQL-команды создания представления.

Рис. 9. Создание представления

Where. Закладка содержит три поля - Where, Group By и Having.

На основе этой информации Erwin генерирует SQL-команду создания пред- ставления, причем на основе содержания этих полей генерируются предложения SQL-запроса.

Для представления V_43 в поле Where содержатся значения

"Соuntrу='Россия"', Group By - "City", Having - "Count(*)>2" (см. рис. 11).

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

SQL. Закладка содержит поле, в котором отображается SQL-запрос создания представления и окно выбора User-Defined SQL.

По умолчанию опция User-Defined SQL выключена, и SQL-запрос генерирует- ся автоматически на основе информации, занесенной в закладках Select, From и Where.

Запрос можно задать вручную, включив эту опцию, но в этом случае список полей и связи представления на диаграмме отображаться не будут.

10