Проектирование реляционных БД

Базы данных Тема 4. Проектирование реляционных БД Преподаватель: к.ф.н., доцент кафедры АСУ, Муртазина М.Ш. Powerpoint Templates План 1. Процесс проектирования БД 2. Концептуальное проектирование БД. ER-диаграмм 3. Правила получения реляционной схемы из ER-модели Powerpoint Templates 2 Процесс проектирования БД Процесс проектирования баз данных – это анализ того, какого рода информация должна быть в ней представлена и каковы взаимосвязи между элементами информации. Структура или схема базы определяется средствами языков или систем обозначений пригодных для описания проектов. По завершению уточнений и согласований проект преобразовывается в форму, которая может быть воспринята конкретной выбранной СУБД – инструкции языка DDL (Data Definition Language). Powerpoint Templates 3 Процесс проектирования БД 1. Анализ предметной области (неформальный текст) 2. Концептуальное проектирование (ER-модель) 3. Логическое проектирование (реляционная модель, нормализация, ограничения) 4. Определение требований, выбор СУБД 5. Физическое проектирование в конкретной СУБД Powerpoint Templates 4 Процесс проектирования БД Подходы к концептуальному моделирования: • Декомпозиционный подход: базируется на системном анализе ПрО и предполагает последовательное, многоуровневое разбиение моделируемой системы на подсистемы до тех пор, пока не станет очевидным информационное поле составных частей. • Интеграционный подход: основан на анализе существующих потребностей пользователей в информации Примеры методик, где используется интеграционный методика Case*Method, методика моделирования IDEF1X, подход: методика КИМПО (проектирования концептуальной информационной модели ПрО). Powerpoint Templates 5 Концептуальное проектирование БД. ER-диаграмма ER-модель (entity relationship diagram – ERD, модель «сущностьсвязь») используется при высокоуровневом (концептуальном) проектировании баз данных. ER-модель отображается графически, в виде диаграммы сущностей и связей состоящих из множеств: • Сущности • Атрибуты • Связи Любой фрагмент предметной области может быть представлен как множество сущностей, между которыми существует некоторое множество связей. Powerpoint Templates 6 Концептуальное проектирование БД Сущность – это некоторый обособленный объект или событие, информацию о котором необходимо сохранять в базе данных, имеющий определенный набор свойств - атрибутов. Сущности могут быть как физические (реально существующие объекты: например, СТУДЕНТ с атрибутами - № зачетной книжки, фамилия, имя, отчество, факультет, специальность, № группы и т.д.), так и абстрактные (например, ЭКЗАМЕН с атрибутами - дисциплина, преподаватель, дата, время, аудитория и пр.). Для сущностей различают ее тип и экземпляр. Тип сущности характеризуется именем и списком свойств, а экземпляр - конкретными значениями свойств. Powerpoint Templates 7 Концептуальное проектирование БД Атрибуты сущности бывают: • Идентифицирующие и описательные. Идентифицирующие атрибуты имеют уникальное значение для сущностей данного типа и являются потенциальными ключами. Они позволяют однозначно распознавать экземпляры сущности. Из потенциальных ключей выбирается один первичный ключ. В качестве первичного ключа обычно выбирается потенциальный ключ, по которому чаще происходит обращение к экземплярам записи. Первичный ключ должен включать в свой состав минимально необходимое для идентификации количество атрибутов. Powerpoint Templates Остальные атрибуты называются описательными. 8 Концептуальное проектирование БД • Простые и составные. Простой атрибут состоит из одного компонента, его значение неделимо. Составной атрибут является комбинацией нескольких компонентов, возможно, принадлежащих разным типам и доменам данных (например, адрес). Решение об использовании составного атрибута или разбиении его на компоненты может быть связано с обеспечением высокой скорости работы с большими БД. Powerpoint Templates 9 Концептуальное проектирование БД • Однозначные и многозначные - могут иметь соответственно одно или много значений для каждого экземпляра сущности. • Основные и производные. Значение основного атрибута не зависит от других атрибутов. Значение производного атрибута вычисляется на основе значений других атрибутов (например, возраст человека вычисляется на основе даты его рождения и текущей даты). Powerpoint Templates 10 Концептуальное проектирование БД Спецификация атрибута состоит из его названия, указания типа данных и описания ограничений целостности - множества значений (или домена), которые может принимать данный атрибут. Домен - это набор всех допустимых значений, которые может содержать атрибут. Тип данных - это физическая концепция, а домен - логическая. Например, "целое число" – это тип данных, а «возраст человека» - это домен. В понятие домена входит не только тип данных, но и область значений данных. Домен «возраст человек» как положительное целое Powerpoint Templates число и определить атрибут им «Возраст сотрудника». 11 Концептуальное проектирование БД Связи - на концептуальном уровне представляют собой простые ассоциации между сущностями. Например, утверждение «Преподаватель обучает студентов» указывает, что между сущностями «Преподаватель» и «Студент» существует связь. Спецификация связи обычно включает наименование связи, тип, признак обязательности, степень связи (унарная, бинарная, тернарная, Nарная), иногда, и атрибут. Существует несколько типов связей между двумя сущностями: это связи «один к одному», «один ко многим» и « многие ко многим». Если сущность одного типа оказывается по необходимости связанной с сущностью другого типа, то между этими типами объектов существует обязательная связь. (необязательной). Иначе связь Powerpoint Templates является факультативной 12 ER-диаграмма Метод диаграммы "сущность-связь" был предложен в 1976 г. Питером Пин Шань Ченом (Peter Pin Shan Chen). В дальнейшем многими авторами были разработаны свои варианты подобных моделей (нотация Мартина, нотация IDEF1X, нотация Баркера и др.). В программных продукта для построения ER-моделей может предоставляться выбор нотации, может использоваться некоторая облегченная версия нотаций или с нестандартными обозначениями. Примеры он-лайн сервисов, в которых можно построить ER-модели: https://erdplus.com/ https://app.creately.com/ Powerpoint Templates 13 ER-диаграмма Основные идеи ER-модели П.Чена былb представленs им в работе «Модель сущность-связь - Унифицированное представление данных» («The entity-relationship model—toward a unified view of data», 1976) Оригинал работы можно найти по ссылке https://dl.acm.org/doi/10.1145/320434.320440 Следует отметить, что основные положения графического моделирования БД были опубликованы в ряде работ других авторов, например, в статье А. П. Г. Брауна «Modelling a Real-World System and Designing a Schema to Represent It» 14 Powerpoint Templates ER-диаграмма Powerpoint Templates 15 ER-диаграмма Иногда для более удобной классификации используются так называемые подтипы (подкатегории или дочерние категории) сущностей. Их обозначают с помощью треугольника с надписью "есть" (т.е. "является"). Powerpoint Templates 16 ER-диаграмма Далее рассмотрим обозначения, предлагаемые для построения ERдиаграмм в нотации П.Чена сервисом https://app.creately.com/ Элемент диаграммы Обозначает Элемент диаграммы Сущность (entity) Зависимая сущность (weak entity) Обозначает Получаемый атрибут (derived attribute or computed attribute) Атрибут (attribute ) Связь (strong relationship) Ключевой атрибут или первичный ключ (key attribute) Идентифицирующая связь (weak (identifying) relationship) Отношение ISA Множественный атрибут (multivalued attribute ) Powerpoint Templates 17 ER-диаграмма Элемент диаграммы Обозначает Признак обязательности Обязательная связь (mandatory) Опциональная связь (optional) Признак участия сущности в связи Полное участие (Total participation) Частичное участие (Partial participation) Powerpoint Templates 18 Получение реляционной схемы из ER-модели Шаг 1. Каждая простая сущность превращается в отношение. Имя сущности становится именем отношения. Шаг 2. Каждый атрибут становится столбцом с тем же именем. Шаг 3. Компоненты уникального идентификатора сущности превращаются в первичный ключ отношения. Шаг 4. Вспомогательные атрибуты , перемещенные в другие сущности после формализации связей, становятся внешними ключами. Шаг 5 Если в концептуальной схеме присутствовали подтипы, то возможны два способа их отображения: * все подтипы размещаются в одной таблице (а); * для каждого подтипа строится отдельная таблица (б). При применении способа (а) таблица создается для внешнего супертипа. В таблицу добавляется по крайней мере один столбец, содержащий код ТИПА, и он становится частью первичного ключа. Шаг 6. Выполнить шаги по нормализации полученных отношений. Шаг 8. Указать ограничения целостности проектируемой базы данных и дать краткое описание полученных таблиц и их полей. Шаг 9. Создать индексы для первичного ключа (уникальный индекс), внешних ключей и тех атрибутов, на которых предполагается в основном базировать запросы и выполнять соединения. Powerpoint Templates 19 Пример Рассмотри фрагмент текстого описания предметной области: Оклад каждого сотрудника зависит от занимаемой им должности. На предприятии допускается совместительство должностей, т.е. каждый сотрудник может иметь более чем одну должность (и работать более чем в одном структурном подразделении), причем может занимать неполную ставку. В то же время, одну и ту же должность могут занимать одновременно несколько сотрудников. Из этого описания предметной области можно выделить четыре сущности: • Структурное подразделение (СП) • Должность • Сотрудник Powerpoint Templates 20 Пример Отразим сущности и связи, на основании текстового описания. «работает в» - тернарная связь Powerpoint Templates 21 Пример Для n-арных (n>2) связей необходимо выявить сущности (искусственные сущности), чтобы трактовать эти связи как бинарные. Переход от n-арной связи к набору бинарных связей необходим для однозначному порождению реляционной модели данных из концептуальной модели. В нашем случае добавляется искусственная сущность «Штатная единица», данная сущность выявлена на основе анализа документов «Штатное расписание». Для штатного расписания на предприятиях используется унифицированная формы штатного расписания № Т-3 (Постановление Госкомстата РФ от 05.01.2004 N 1 «Об утверждении унифицированных форм первичной учетной документации по учету труда и его оплаты») Powerpoint Templates 22 Пример Powerpoint Templates 23 Пример Powerpoint Templates 24 Пример Powerpoint Templates 25 Пример Далее определим атрибуты сущностей. Например, для сотрудника, атрибуты могут быть выявлены на основании анализа личной карточки, трудового договора и других документов о сотрудники. В качестве примера возьмем для сотрудника только несколько атрибутов. Powerpoint Templates 26 Пример На диаграмме показаны естественные первичные ключи Powerpoint Templates 27 Пример Powerpoint Templates 28 Пример Следует отметить, что для сущностей, где естественный ключ представлен текстовым полем, или где естественный ключ содержал комбинацию из нескольких полей, при этом на такую таблицу нужно сделать ссылку из другой таблицы, был введен искусственный первичный ключ - счетчик Powerpoint Templates 29 Спасибо за внимание! Powerpoint Templates