Волк В.К. Базы данных. Практикум по проектированию, программированию и администрированию баз данных, включающий примеры и практические задания для самостоятельного выполнения
 Скачать 3.17 Mb.
|
|
ГЛАВА 4. ТЕХНИЧЕСКИЙ ПРОЕКТ. РАЗРАБОТКА РЕЛЯЦИОННОЙ МОДЕЛИ ДАННЫХ Реляционная модель относится к категории логических моделей данных и формируется на следующей стадии разработки базы данных — стадии техниче- ского проекта (рис. 2.1). На этой стадии концептуальная ER-модель предметной области АИС преобразуется в схему (R-модель или R-схему) реляционной базы данных, которая затем получает программную реализацию на языке SQL в сре- де СУБД, поддерживающей функционирование АИС. Процесс преобразования ER-модели в R-схему БД реализуется двумя последовательными этапами: вначале ER-модель преобразуется в исходную R-схему, а затем проводится анализ исходной R-схемы, по результатам которо- го может быть принято решение о необходимости ее дальнейшего преобразова- ния (так называемой нормализации) с целью улучшения эксплуатационных ха- рактеристик проектируемой базы данных. Первый этап такого преобразования достаточно формализован — он реа- лизуется в соответствии с простыми правилами формирования реляционных структур данных (отношений) по описанию сущностей и связей ER-модели. Второй этап связан с проведением процедуры нормализации, которая, хо- тя и базируется на разработанной Э. Коддом теории нормальных форм отноше- ний, требует проведения неформального анализа семантики предметной обла- сти для выявления зависимостей между атрибутами сущностей, то есть, по су- ществу, требует возврата на стадию эскизного проекта базы данных. 4.1. Преобразование ER-модели в исходную схему реляционной БД На первом этапе разработки реляционной модели данных описание сущ- ностей ER-модели и связей между ними преобразуется во множество схем вза- имосвязанных отношений. Технология такого преобразования достаточно про- ста и может быть представлена последовательностью типовых шагов и правил, применяемых на каждом шаге. Шаг 1. Формирование схем отношений Каждая сущность ER-модели преобразуется в соответствующую схему отношения: – отношению присваивается имя; – каждый из агрегированных атрибутов сущности ER-модели (при нали- чии у сущности таких атрибутов) преобразуется во множество «атомарных» ат- рибутов соответствующего отношения; – для каждого атрибута отношения определяются: • имя атрибута; • соответствующий скалярный тип данных (из множества типов данных, поддерживаемых СУБД); − в необходимых случаях для атрибутов определяются: • ограничения обязательности NOT NULL и значения по умолчанию DEFAULT; 16 / 19 55 • ограничения уникальности UNIQUE (для возможных ключей); • проверяемые ограничения целостности CONSTRAINT … CHECK; – определяется первичный ключ отношения: • из числа возможных ключей отношения (атрибутов со свойством UNIQUE) выбирается первичный ключ; • в необходимых случаях для этой цели в схему отношения добавляется более экономичный искусственный атрибут автоинкрементного типа данных; • для единственного атрибута отношения, назначенного первичным ключом, задается ограничение целостности PRIMARY KEY. В результате выполнения первого шага преобразования ER-модели сфор- мировано множество схем отношений проектируемой базы данных, представ- ляющих соответствующие объекты предметной области. Все атрибуты сущно- стей получили свою реализацию в атрибутах соответствующих отношений: – агрегированные атрибуты (при их наличии) декомпозированы; – для каждого атрибута определены имя и скалярный тип данных; – свойства атрибутов отображены в ограничения целостности; – в схеме каждого отношения задан единственный первичный ключ. Полученный промежуточный результат еще не является полноценной ре- ляционной моделью, так как не отображает информацию о связях между сущ- ностями, представленную в ER-модели, и, как следствие, не позволяет иденти- фицировать кортежи отношений по их связям с другими кортежами. Для интеграции разрозненных схем отношений в единую схему реляци- онной базы данных необходимо определить для каждой пары связанных отно- шений ограничения ссылочной целостности FOREIGN KEY, реализация которых базируется на концепции внешних ключей отношений. Шаг 2. Определение внешних ключей Каждая связь между сущностями ER-модели реализуется парой «первич- ный ключ — внешний ключ» соответствующих отношений R-модели, сформиро- ванных на предыдущем шаге преобразования. Внешний ключ — это дополнительный атрибут, включаемый в схему ссы- лающегося (подчиненного, или дочернего) отношения для реализации ссылок на кортежи родительского (главного) отношения. Внешний ключ дочернего отношения должен быть совместим по типу и домену с первичным ключом родительского отношения и может наследовать все его свойства, за исключением свойства уникальности. Значение внешнего ключа в кортежах дочернего отношения должно быть равным значению пер- вичного ключа в связанном с ними кортеже родительского отношения, что, собственно, и позволяет реализовать ссылки между этими кортежами путем за- дания ограничений целостности FOREIGN KEY для внешних ключей дочерних отношений. Для практического использования концепции внешних ключей необхо- димо конкретизировать понятия родительского и дочернего отношений, кото- рые определяются на основе анализа кратности связей между сущностями ER-модели в соответствии с тремя базовыми правилами. 17 / 19 56 Правило № 1. Если между парой сущностей ER-модели установлена асимметричная связь кратности «1:M» («один ко многим»), то дочерним счита- ется отношение, реализующее сущность, участвующую в связи со стороны M («много»), а другое отношение в этой паре считается родительским. Правило № 2. Если между парой сущностей ER-модели установлена сим- метричная связь кратности «1:1», то дочерним считается отношение, имеющее потенциально меньшую мощность. Правила реализации в R-модели связей кратностей «1:M» и «1:1» между сущностями ER-модели иллюстрируются на примере модели системы учета кон- тингента студентов (рис. 2.14). Так как студенческих групп всегда меньше, чем сту- дентов, для реализации связи «назначен старостой» кратности «1:1» в качестве дочернего принято отношение «Группы» с внешним ключом «ID_Group_Monitor». а б Рис. 2.14 Модель системы учета контингента студентов: а — ER-диаграмма; б — схема реляционной БД. Правило № 3. Если между n сущностями ER-модели установлена связь кратности «N:M»(«многие ко многим»), то схема базы данных дополняется n-арным ассоциативным отношением, которое получает статус дочернего от- ношения со схемой, содержащей n внешних ключей, а все n отношений, участ- вующих в связи, получают статус родительских. В роли первичного ключа ас- социативного отношения выступает составной атрибут, включающий все n его внешних ключей — любое подмножество этого составного атрибута может 18 / 19 57 дублироваться в различных кортежах ассоциативного отношения, но все вместе они наделяются свойством уникальности и получают ограничение целостности PRIMARY KEY. Такое решение вызвано «бедностью» реляционной модели данных, не имеющей естественных механизмов реализации связей множественной кратно- сти: по существу, каждый экземпляр n-арной связи кратности «N:M» заменяет- ся n экземплярами связей кратности «1:M». Иллюстрация правила реализации связей множественной кратности приведена на рисунке 2.15 на примере модели системы учета успеваемости студентов. а б Рис. 2.15 Модель системы контроля успеваемости студентов: а — ER-диаграмма; б — схема реляционной БД. Правила № 4 и 5 уточняют применение рассмотренных выше трех базо- вых правил для реализации иерархических и сетевых структур данных сред- ствами реляционной модели. Правило № 4 определяет способ реализации иерархических связей типа «обобщение» между сущностями ER-модели, когда дочерняя сущность пред- ставляет множество объектов, каждый из которых является частным случаем другого объекта, представленного родительской сущностью. Дочерняя сущность наследует атрибуты и связи своей родительской сущ- ности, но может иметь и свои собственные атрибуты и участвовать в связях с другими сущностями, не связанными с родительской сущностью (так, напри- мер, связаны дочерние сущности Книги и Журналы с родительской сущностью Библиотечный фонд на рисунке 2.16, представляющем фрагмент модели авто- матизированной библиотечной системы). 19 / 19 58 Кратность такой связи — «1:1», из чего следует (согласно рассмотренно- му выше базовому правилу № 2), что внешние ключи должны быть добавлены в схемы дочерних отношений, так как мощность любого из них будет меньше мощности родительского отношения, которая равна сумме мощностей всех ее дочерних отношений. Специфика реализации иерархических связей типа обобщение заключает- ся в том, что дочерние отношения не имеют собственных первичных ключей — их роль выполняют внешние ключи, наследующие значения первичных ключей связанных кортежей родительского отношения и соответственно получающие ограничение целостности PRIMARY KEY (рис. 2.16б). Правило № 5 определяет способ реализации иерархических и сетевых унар- ных связей, то есть связей между различными экземплярами одной сущности. Между экземплярами сущности Категории (рис. 2.16а) задана иерархи- ческая связь «предок» — «потомок» кратности «1:M» — это означает, что лю- бой экземпляр сущности может быть «потомком» какого-либо одного экзем- пляра этой же сущности и одновременно «предком» одного или нескольких ее экземпляров. Например, категории «Учебники для вузов» и «Учебники для общеобразо- вательных школ» могут быть «потомками» категории «Учебная литература», и при этом категория «Учебники для вузов» может быть «предком» для катего- рий «Учебники по гуманитарным дисциплинам» и «Учебники по техническим дисциплинам». Заметим, что наличие такого рода связи не исключает возмож- ности экземплярам сущности не иметь ни «предков», ни «потомков». Применение базового правила № 1 к отношению, представляющему сущ- ность Категории, потребует присвоения ему одновременно двух статусов: ро- дительского и дочернего отношений. В результате в схему этого отношения (рис. 2.16б) будет добавлен внешний ключ Код предка, значением которого для кортежа-«потомка» будет значение первичного ключа Код категории из друго- го кортежа, представляющего кортеж-«предок» данного кортежа-«потомка». Такие внешние ключи называют рефлексивными. В качестве примера реализации унарной связи множественного наследо- вания можно рассмотреть сущность Жанры (рис. 2.16а). Кратность этой связи определена как «M:N», что дает возможность любому жанру выступать как в ро- ли «потомка», так и в роли «предка» любого количества других жанров. Напри- мер, жанр «Фэнтези» может быть объявлен «потомком» жанров «Фантастика» и «Сказки народов мира», и такое проектное решение может облегчить поиск нужной книги читателям, не отягощенным глубокими познаниями в области библиографии. Кратность «M:N» такой связи позволяет применить к ней базовое правило № 3, согласно которому в базу данных добавляется ассоциативное бинарное отно- шение, схема которого сформирована из двух атрибутов — внешних ключей, пред- ставляющих первичные ключи двух связываемых кортежей основного отношения. Так, например, схема ассоциативного отношения «Под_жанры» (рис. 2.16б) включает два таких атрибута — «Код_жанра_предка» и «Код_жанра_потомка». 1 / 19 59 Шаг 3. Представление описательных атрибутов связей Правило № 6. Если для связи между сущностями ER-модели определены описательные атрибуты, то соответствующие атрибуты добавляются в схему того отношения, в которое был добавлен внешний ключ, реализующий эту связь (рис. 2.15 и 2.16). а б Рис. 2.16 Модель библиотечного каталога: а — ER-диаграмма; б — схема реляционной БД. 4.2. Пример разработки исходной схемы реляционной БД В качестве примера продолжим рассмотрение подсистемы учета работы с клиентами интернет-провайдера, ER-диаграмма которой приведена на рисун- ке 2.13. Применяя рассмотренные выше правила преобразования к сущностям и связям этой ER-модели, получим R-модель, представленную на рисунке 2.17. 2 / 19 60 Рис. 2.17 Исходная R-модель подсистемы у чета работ ы с клиентами интернет- провайдера 3 / 19 61 Контрольные вопросы и задания 1. Перечислите правила преобразования сущностей ER-модели в схемы соответствующих отношений R-модели данных. 2. Определите понятие «внешний ключ отношения». Как в R-модели реа- лизуются связи кратности «1:M», «1:М» и«M:N», установленные между сущ- ностями ER-модели? 3. Как в R-модели реализуются иерархические связи вида «потомок» — «предок», установленные между сущностями ER-модели (рис. 2.16)? 4. Как в R-модели отображаются атрибуты связей между сущностями ER-модели? 5. Какова роль отношений Предмет договора и Услуги_Тариф- ных_Планов в схеме БД, приведенной на рисунке 2.17? 6. Задание: напишите выражения реляционной алгебры, реализующие следующие запросы к базе данных, схема которой приведена на рисунке 2.17: • список неисполненных заявок, поступивших от клиентов (имя клиен- та, номер заявки и дата ее регистрации); • список тарифных планов, предназначенных для клиентов категории «корпоративные клиенты»; • список оборудования, установленного у определенного клиента, за- ключившего договор (имя клиента, № договора, тарифный план, услуга, мо- дель оборудования). 7. На рисунке 2.10 приведена ER-диаграмма пакета «Тарифные планы». Задание: • перестройте эту диаграмму с использованием связей вида «Обобще- ние» для условий, когда категорий услуг всегда ровно три, а параметры услуг одной категории не могут входить в состав параметров услуг других категорий; • используя правила преобразования ER-модели в R-модель, разрабо- тайте схему реляционной базы данных, соответствующую полученной ER-модели. 4.3. Нормализация реляционной базы данных 4.3.1. Аномальное поведение слабоструктурированных БД Если ER-модель адекватно представляет свойства моделируемых объек- тов предметной области, то настолько же информационно-адекватной будет и реляционная БД, схема которой получена путем формальных преобразований описаний сущностей и связей ER-модели в схемы соответствующих отноше- ний. Такая БД будет способной к реализации информационных запросов поль- зователей проектируемой АИС, что, однако, не исключает проявления различ- ного рода аномалий в процессе ее эксплуатации. Аномалии могут приводить в лучшем случае к излишнему дублированию данных и снижению производительности работы АИС, а в худшем — к нару- шениям целостности базы данных и безвозвратной потере хранимой в ней ин- формации при вполне корректном выполнении типичных модифицирующих 4 / 19 62 операций, таких, например, как вставка или удаление кортежей отношений или изменение значений их атрибутов. Причины такого «аномального поведения» базы данных следует искать в недостатках структуры ее R-схемы, полученной путем формального преобразо- вания ER-модели и унаследовавшей от нее все основные характеристики, в том числе и недостатки структурного характера. В отличие от R-схемы базы дан- ных, процесс разработки ER-модели на стадии эскизного проекта формализован довольно слабо, всегда имеются альтернативные варианты получения его ре- зультатов, качество которых во многом определяется опытом разработчика. Следующий (намеренно утрированный) пример иллюстрирует ситуацию, в которой некачественная декомпозиция предметной области при разработке ER-модели приводит к созданию схемы базы данных, которая, оставаясь ин- формационно адекватной и удовлетворяющей всем базовым требованиям и ограничениям реляционной модели данных, оказывается совершенно непри- годной к эксплуатации по причине явного проявления в ней аномалий попол- нения, удаления и изменения данных. Пусть разработчику (не отягощенному знаниями в области технологий проектирования программных систем) поставлена задача создания базы данных для учета успеваемости студентов. Анализируя предметную область, разработчик получил доступ к «бумаж- ному» документу «Экзаменационная ведомость» и обнаружил, что в этом до- кументе содержится вся информация, необходимая для оперативного учета и анализа успеваемости студентов. В результате была создана ER-модель, содер- жащая единственную сущность «Экзамены», описательные атрибуты которой были скопированы с соответствующих информационных полей экзаменацион- ной ведомости: Экзамены (Семестр, Группа, Дисципл., Студент, Препод., Дата, Оценка) В качестве возможного первичного ключа этой сущности может быть ис- пользован составной атрибут (Семестр, Дисципл., Студент, Дата), что позво- ляет уникально идентифицировать каждый экземпляр сущности и соответству- ет реальной ситуации с приемом экзаменов: в частности, включение атрибута Дата в состав первичного ключа позволит зарегистрировать факты и результа- ты повторной сдачи студентом экзаменов по дисциплинам. Информационная адекватность такой модели не вызывает сомнений: каждый экземпляр этой сущности представляет один экзамен, принятый у од- ного студента по одной из дисциплин, изучаемых в одном семестре, при этом регистрируется дата проведения экзамена, полученная студентом оценка и рек- визиты преподавателя, принявшего экзамен. Аналитические возможности такой модели также соответствуют требова- ниям заказчика: модель позволяет формировать разнообразные рейтинговые списки студентов, их группировку по различным критериям и вычисление со- ответствующих интегральных показателей успеваемости. На основании этой ER-модели была разработана R-схема базы данных, включающая единственное отношение с набором атрибутов, аналогичным ат- 5 / 19 63 рибутам сущности, и дополнительным искусственным атрибутом автоинкре- ментного типа данных, которому присвоен статус первичного ключа. Схема такого отношения соответствует всем базовым требованиям реля- ционной модели данных: – атрибуты отношения являются атомарными, им присвоены соответ- ствующие скалярные типы данных; – в отношении имеется первичный ключ, что гарантирует отсутствие в нем кортежей-дубликатов; – в определении ограничений ссылочной целостности нет необходимо- сти, так как R-схема содержит единственное отношение. При всей простоте и информационной адекватности полученной R-схемы она не обеспечивает эффективного хранения информации по причине излишне- го дублирования данных: например, фамилия студента будет дублироваться во всех кортежах, описывающих сданные этим студентом экзамены; также будут многократно повторяться наименование дисциплины и фамилия преподавателя. Кроме этого, попытки выполнения операций, модифицирующих базу данных, приведут к проявлению следующих аномалий: – аномалия пополнения — невозможно вставить кортеж, описывающий студента, еще не сдавшего экзамен, или дисциплину, по которой экзамен еще не сдавался, так как атрибуты, входящие в состав возможного ключа, не могут иметь неопределенных значений; – аномалия удаления — удаление кортежа, описывающего некоторого студента (например, при его отчислении), может привести к потере информа- ции о дисциплине (в том случае, если отчисленный студент был единственным, кто сдавал экзамен по этой дисциплине); – аномалия изменения — если, например, студент(ка) меняет фамилию, то потребуется внести соответствующие изменения в несколько десятков корте- жей. Совсем другая ER-модель той же самой «экзаменационной ведомости», полученная в результате проведения более детальной декомпозиции предмет- ной области на стадии эскизного проекта, представлена на рисунке 2.15а. Схема реляционной БД (рис. 2.15б), сформированная на основе этой ER- модели, лишена рассмотренных выше недостатков первоначальной схемы, что избавит БД от многих аномалий в процессе ее эксплуатации. |