🗊Презентация Проектування реляційної бази даних. Лекції 6, 7, 8, 9

Бази даних та інформаційні системи семестр 2 Проектування реляційної бази даних

Подходы к проектированию базы данных Существуют два основных подхода к проектированию систем баз данных: нисходящий; восходящий; смешанная стратегия проектирования Восходящий подход Содержание: при восходящем подходе работа начинается с атрибутов (т.е. свойств сущностей и связей), которые на основе анализа существующих между ними связей группируются в отношения, представляющие типы сущностей и связи между ними. Базовая методология: НОРМАЛИЗАЦИЯ Нормализация предусматривает идентификацию требуемых атрибутов с последующим созданием из них нормализованных таблиц, основанных на функциональных зависимостях между этими атрибутами. Применение: Приемлем для проектирования простых баз данных с относительно небольшим количеством атрибутов. Недостатки: Использование этого подхода существенно усложняется при проектировании баз данных с большим количеством атрибутов; На начальных стадиях формулирования требований к данным в крупной базе данных может быть трудно установить все атрибуты, которые должны быть включены в модели данных.

Подходы к проектированию базы данных Нисходящий подход Содержание: при восходящем подходе работа начинается с разработки моделей данных, которые содержат несколько высокоуровневых сущностей и связей, затем работа продолжается в виде серии нисходящих уточнений низкоуровневых сущностей, связей и относящихся к ним атрибутов. Базовая методология: МОДЕЛЬ «СУЩНОСТЬ-СВЯЗЬ» или ER-модель (Entity-Relationship) Применение: Приемлем для проектирования сложных баз данных Смешанная стратегия проектирования В смешанной стратегии сначала используются восходящий и нисходящий подходы для создания разных частей модели, после чего все подготовленные фрагменты собираются в единое целое.

Моделирование данных Основные цели моделирования данных состоят в: упрощении описания требований к данным предметной области (ПрО) и процедурам взаимодействия этих данных; едином понимании требования к данным отдельных пользователей и разработчиков; Если обе стороны знакомы с системой обозначений, используемой для создания модели, то наличие модели данных будет способствовать более плодотворному общению пользователей и разработчиков. отражении характера самих данных независимо от их физического представления; использовании данных в пределах области применения приложения. На предприятиях все шире применяются средства стандартизации для моделирования данных путем выбора определенного метода моделирования и использования его во всех проектах разработки базы данных. Самая популярная технология высокоуровневого моделирования данных построена на концепции модели "сущность-связь" (Entity-Relationship model — ER-модель).

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

Бази даних та інформаційні системи семестр 2 Проектування реляційної бази даних на основі ER-діаграми Концептуальне та логічне проектування РМД Лекція 3,4

План лекции Введение. Этапы проектирования РМД Концептуальное проектирование Логическое проектирование Физическое проектирование Заключение

Цель лекции: Рассмотреть пошагово концептуальный и логический этапы проектирования РБД; Рассмотреть примеры разработки схемы БД

Введение Подход к проектированию БД: НИСХОДЯЩИЙ Базовая методология: ПОСТРОЕНИЕ ER-ДИАГРАММЫ

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

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

Физическое проектирование базы данных Физическое проектирование базы данных Физическое проектирование базы данных - описание способа физической реализации логической модели базы данных Описание: на этом этапе рассматриваются создание отношений, организация файлов и индексов, предназначенных для обеспечения эффективного доступа к данным, а также все связанные с этим ограничения целостности и средства защиты. В случае РМД : создание набора реляционных таблиц и ограничений для них на основе информации, представленной в логической модели данных; определение конкретных структур хранения данных и методов доступа к ним (организация файлов, индексов), обеспечивающих оптимальную производительность СУБД; разработка средств защиты создаваемой системы. Замечание! На этапах концептуального и логического проектирования решается вопрос «ЧТО ДЕЛАТЬ», а на этапе физического проектирования - «КАК ДЕЛАТЬ». Этапы выполняются последовательно, поскольку понять, что надо сделать, следует прежде, чем решить, как это сделать. Этапы требуют совершенно разных навыков и опыта, поэтому требуют привлечения специалистов различного профиля.

Последовательность действий при концептуальном проектировании Данный этап включает создание и проверка локальной концептуальной модели данных для отдельных пользователей: Определение типов сущностей Определение типов связей Определение атрибутов и связывание их с типами сущностей и связей Определение потенциальных и выбор первичных ключей Проверка на избыточность (удаление избыточных связей Проверка соответствия модели требованиям пользовательских транзакций (проверка на достаточность);

Последовательность действий при логическом проектировании Данный этап включает следующие шаги 1. Создание и проверка локальной логической модели данных для отдельных пользователей 1.1 Исключение особенностей, несовместимых с РМ: - преобразование связей типа M:N за счет добавления новой сущности; - преобразование сложных связей (не бинарных) в сущности; - анализ и преобразование рекурсивных связей M:N; - преобразование связей, имеющих атрибуты, в сущности; - удаление множественных атрибутов за счет добавления новой сущности; 1.2 Дополнительный анализ: - перепроверка связей типа 1:1; - анализ рекурсивных связей 1:1, 1:M; - анализ связей супер класс/подкласс; - проверка модели с помощью правил нормализации на соответствие требованиям 3-й нормальной формы; - повторная проверка на избыточность (удаление избыточных связей); - повторная проверка соответствия отношений требованиям пользовательских транзакций (проверка на достаточность); 1.3 Определение набора отношений исходя из структуры логической модели данных; 1.4 Отражение всех требований итоговой ER-диаграммы логической модели в документации; 1.5. Согласование локальной логической модели данных с пользователем (заказчиком). 2. Создание и проверка глобальной логической модели данных

Логическое проектирование. Преобразование связей типа M:N и связей с атрибутами Проблема: присутствует связь типа M:N или связь с атрибутами Решение проблемы: создание промежуточной сущности; связь типа M:N заменяется двумя связями типа 1:М, устанавливаемыми от старых сущностей к вновь созданной сущности. Пример 1: Исходная модель: Преобразованная модель: Раскрытие схемы: Газета (ИН_газета, Назв_газета, Адрес_газета) Объект (ИН_объект, Адрес_объект) Объявление (ИН_газета(ВК), ИН_объект(ВК), Дата)

Логическое проектирование. Преобразование сложный связей Проблема: присутствует сложная связь Сложная связь – связь между тремя и более типами сущностей. Решение проблемы: создание промежуточной сущности; введение новый связей от старых сущностей к вновь созданной Пример 2.1:(БП:объекты не перепродаются, в сделке участвует 1 или 0 менеджер, 1 покупатель, 1 объект) Исходная модель: Преобразованная модель: Раскрытие схемы: Покупатель (ИН_покуп) Объект (ИН_объект) Менеджер (ИН_мен) Сделка (ИН_покуп(ВК), ИН_объект(ВК), ИН_мен (ВК))

Логическое проектирование. Преобразование сложный связей с атрибутами. Миграция атрибутов Пример 2.2 : (БП:объекты перепродаются, в сделке участвует 1 или 0 менеджер, 1 покупатель, 1 объект) Исходная модель: Преобразованная модель: Раскрытие схемы: Покупатель (ИН_покуп) Объект (ИН_объект) Менеджер (ИН_мен) Сделка (ИНсделка, ИН_покуп(ВК), ИН_объект(ВК), ИН_мен (ВК))

Логическое проектирование. Преобразование сложный связей с атрибутами. Миграция атрибутов Пример 2.3 : (БП:объекты перепродаются не чаще 1 раза в сутки, в сделке участвует 1 или 0 менеджер, 1 покупатель, 1 объект, фиксируется дата сделки) Исходная модель: Преобразованная модель: Вариант а: Раскрытие схемы: а) Сделка (ИН_покуп(ВК), ИН_объект(ВК), ИН_мен (ВК), Дата)

Логическое проектирование. Преобразование сложный связей с атрибутами. Миграция атрибутов Пример 2.3 : (БП:объекты перепродаются, в сделке участвует 1 менеджер, фиксируется дата сделки) Исходная модель: Преобразованная модель: Вариант б: Раскрытие схемы: б) Сделка (ИН_сделка, ИН_покуп(ВК), ИН_объект(ВК), ИН_мен (ВК), Дата)

Логическое проектирование. Преобразование многозначных атрибутов Проблема: присутствует многозначный атрибут Многозначный атрибут – атрибут, хранящий несколько значений, соответствующих одному экземпляру сущности Решение проблемы: создание новой сущности; Исходная модель: введение новый связей от старой сущностей к новой Пример 3.1: БП: 1.Телефонный номер принадлежит только 1 отделению 2. У отделения может быть более одного номера Преобразованная модель: Раскрытие схемы: Отделение (Номер_отд, Название_отд) Телефон (Номер_телефона, Номер_отд (ВК))

Логическое проектирование. Преобразование многозначных атрибутов Пример 3.2а: БП: 1.Телефонный номер может принадлежать нескольким отделениям 2. У отделения может быть более одного номера Исходная модель: 3. Существуют перечень телефонных номеров, принадлежащих всему предприятию. Номера из этого списка закрепляются за отделениями. Могут существовать номера, которые в данный момент не используются. Преобразованная модель: Шаг1. Шаг2. Раскрытие схемы: Отделение (Номер_отд, Название_отд) Принадлежность_телефона (Номер_телефона (ВК), Номер_отд (ВК)) Телефон (Номер_телефона)

Логическое проектирование. Преобразование многозначных атрибутов Пример 3.2б: БП: 1.Телефонный номер может принадлежать нескольким сотрудникам 2. У сотрудника может быть более одного номера или не быть телефона вообще Исходная модель: 3. Перечень телефонных номеров, принадлежащих сотрудникам не хранится. Преобразованная модель: Раскрытие схемы: Отделение (Номер_сотр, ФИО) Телефон (Номер_телефона, Номер_сотр (ВК))

Логическое проектирование. Анализ рекурсивных связей Рекурсивная связь: 1:1, с полным участием со стороны дочерней 1:M с полным участием со стороны М Пример 4.1 БП: 1.Все сотрудники имеют консультантов 2. У сотрудника может быть только один консультант 3. Консультант может консультировать X Сотрудников (0:1 или 0:М) Исходная модель: Сотрудник (1:М, с полным участием) Раскрытие схемы: Сотрудник (Номер_сотр, ФИО, Должность, Зарплата, Адрес, Телефон, Консультант(ВК))

Логическое проектирование. Анализ рекурсивных связей Рекурсивная связь: Исходная модель: 1:1, с частичным участием со стороны дочерней 1:M с частичным участием со стороны М Пример 4.2 БП: 1. Не каждый сотрудник имеет Консультанта (част. участие со стороны дочерн) 2.У сотрудника может быть только один консультант. 3. Консультант может консультировать X сотрудников (0:1 или 0:М). Преобразованная модель: Раскрытие схемы: Сотрудник (Номер_сотр, ФИО) Консультация (Консультируемый(ВК), Консультант(ВК))

Логическое проектирование. Анализ рекурсивных связей Преобразованная модель: Раскрытие схемы: Сотрудник (Номер_сотр, ФИО) Консультация (Консультируемый(ВК), Консультант(ВК)) Сотрудник Консультация (1:1, с частичн. участием Сотрудников в обеих связях) Консультация (1:М, с частичн. участием Сотрудников в обеих связях)

Логическое проектирование. Анализ рекурсивных связей Рекурсивная связь: Исходная модель: M:N Пример 4.3 БП: 1. У сотрудника может быть более одного консультанта. 2. Консультант может консультировать нескольких сотрудников. Преобразованная модель: Сотрудник Консультация (M:N) Раскрытие схемы: Сотрудник (Номер_сотр, ФИО) Консультация (Консультируемый(ВК), Консультант(ВК))

Логическое проектирование. Перепроверка связей 1:1 дописать

Логическое проектирование. Проверка на избыточность связей Следует стремиться создавать минимальные модели При наличии нескольких связей между сущностями, необходимо проверить модель на избыточность. Пример 5.1 БП: Рассматривается только текущий брак между мужчиной и женщиной Учитываются все имеющиеся дети Вопрос: Кто является мамой и папой ребенка?

Логическое проектирование. Проверка на избыточность связей Пример 5.2 БП: Рассматривается все браки между мужчиной и женщиной Учитываются все имеющиеся дети Одна и та же пара может повторно заключать брак Вопрос: Кто является мамой и папой ребенка?

Логическое проектирование. Проверка на избыточность связей Пример 5.3 БП: Рассматривается все браки между мужчиной и женщиной Учитываются все имеющиеся дети Одна и та же пара может повторно заключать брак Вопрос: Кто является мамой и папой ребенка? В браке ли рожден ребенок и каком?

Логическое проектирование. Проверка отношений с использованием средств нормализации (обзорно) Нормализация – процесс проверки и реорганизации сущностей и атрибутов с целью удовлетворения требований к реляционной модели данных. В ре­зультате проведения нормализации должна быть создана структура данных, при которой информация о каждом факте хранится только в одном месте, и решены проблемы модификации данных (аномалии обновления). Процесс нормализации сводится к последовательному приведению структуры данных к нормальным формам - формализованным требованиям к организации данных. Известны шесть нормальных форм: первая нормальная форма (1НФ); вторая нормальная форма (2НФ); третья нормальная форма (3НФ); нормальная форма Бойса - Кодда (усиленная 3НФ); четвертая нормальная форма (4НФ); пятая нормальная форма (5НФ). На практике обычно ограничиваются приведением данных к третьей нормальной форме.

Логическое проектирование. Проверка отношений с использованием средств нормализации (обзорно) Нормальные формы основаны на понятии функциональной зависимости (ФЗ). Функциональная зависимость (ФЗ). Атрибут В сущности Е функционально зависит от атрибута А сущности Е тогда и только тогда, когда каждое значение атрибута А однозначно определяет одно значение атрибута В. Полная функциональная зависимость. Атрибут В сущности Е полностью функционально зависит от ряда атрибутов А сущности Е тогда и только тогда, когда В функционально зависит от А и не зависит ни от какого подряда А. Пример ФЗ Сотрудник (СотрудникИН, ФИО, Должность, Оклад) ФЗ1: СотрудникИН  ФИО, Должность, Оклад ФЗ2: Должность  Оклад

Логическое проектирование. Проверка отношений с использованием средств нормализации (обзорно) Первая нормальная форма (1НФ). Сущность находится в 1НФ тогда и только тогда, когда все атрибуты содержат атомарные значения, т.е. не должно встречаться нескольких значений атрибута для одного экземпляра либо сложных значений, по части которых планируется поиск информации. Пример 6. БП: У отделения может быть более одного телефона, телефон принадлежит только одному отделению Для приведения сущности к 1НФ следует : 1) разделить сложные атрибуты на атомарные; Отделение (Номер_отд, Название_отд, Город, Улица, Дом_фис, ?) 2) создать новую сущность, перенести в нее все "многозначные" атрибуты, установить связь от прежней сущности к новой (РК прежней сущности станет внешним ключом (FK) для новой сущности), выбрать РК из существующих атрибутов (или создать суррогатный); Неправильно!: Отделение (Номер_отд, Название_отд, Город, Улица, Дом_фис, Тел1, Тел2, Тел3) Правильно!: Отделение (Номер_отд, Название_отд, Город, Улица, Дом_фис) Телефон (Телефон, Номер_отд (ВК))

Логическое проектирование. Проверка отношений с использованием средств нормализации (обзорно) Вторая нормальная форма (2НФ). Сущность находится во 2НФ, если она находится в 1НФ и каждый неключевой атрибут полностью функционально зависит от первичного ключа (не должно быть зависимости от части ключа). Вторая нормальная форма имеет смысл только для сущностей, имеющих составной первичный ключ. Описания предметной области можно оформить в виде таких БП: Каждым проектом последовательно может руководить несколько сотрудников с фиксированием Даты начала и Даты завершения руководства; Сотрудник может руководить многими проектами, но каждым проектом - только один раз (один промежуток времени). Руководство (ПроектИН, СотрудникИН, ДатаНачала, ДатаЗавершения, ФИО, Должность) ФЗ1: ПроектИН, СотрудникИН  ДатаНачала, ДатаЗавершения (ПФЗ) ФЗ2: СотрудникИН  ФИО, Должность (ЧФЗ) Тогда для приведения сущности ко 2НФ следует: выделить атрибуты, которые зависят только от части первичного ключа, создать новую сущность; поместить атрибуты, зависящие от части ключа, в их собственную (новую) сущность вместе атрибутом, от которого они зависят; использовать атрибут, определяющий эту зависимость, в качестве первичного ключа новой сущности; установить идентифицирующую связь от новой сущности к прежней сущности Руководство (ПроектИН, СотрудникИН (ВК), ДатаНачала, ДатаЗавершения) Сотрудник (СотрудникИН, ФИО, Должность)

Логическое проектирование. Проверка отношений с использованием средств нормализации (обзорно) 2НФ позволяет избежать следующих аномалий при выполнении операций: Обновление (UPDATE). Имело бы место дублирование данных о сотрудни­ке, если он руководит несколькими проектами. Если данные о сотруднике изменялись бы, необходимо было менять несколько записей (по числу ведомых проектов). Вставка (INSERT). Невозможно было бы ввести данные о сотруднике, если он в данный момент не руководил проектами. Удаление (DELETE). Если сотрудник временно прекращал бы руководство проектами, данные о нем терялись.

Логическое проектирование. Проверка отношений с использованием средств нормализации (обзорно) Третья нормальная форма (3НФ). Сущность находится в 3НФ форме, если она находится во второй нормальной форме и никакой неключевой атрибут не зависит от другого неключевого атрибута (исключается транзитивная зависимость, т.е. не должно быть взаимозависимости между неключевыми атрибутами). Пример 7 БП: Оклад сотрудника зависит от его должности. Сотрудник (СотрудникИН, ФИО, Должность, Оклад) ФЗ1: СотрудникИН  ФИО, Должность, Оклад ФЗ2: Должность  Оклад (ТФЗ) Для приведения сущности к 3НФ следует: создать новую сущность и перенести в нее атрибуты с одной и той же зависимостью от неключевого атрибута; использовать атрибут, определяющий эту зависимость, в качестве первичного ключа новой сущности; установить неидентифицирующую связь от новой сущности к старой. Сотрудник (СотрудникИН, ФИО, ДолжностьНазв (ВК)) Должность (ДолжностьНазв, Оклад)

Логическое проектирование. Проверка отношений с использованием средств нормализации (обзорно) Третья нормальная форма также позволяет избежать ряда аномалий, которые возникали бы без приведения к 3НФ: Обновление (UPDATE). Имело бы место дублирование данных об окладе, если одинаковую должность занимают несколько сотрудников. Если оклад соответст­вующей должности менялся, необходимо было бы менять несколько записей (по числу сотрудников на одной должности). Вставка (INSERT). Невозможно было бы ввести данные об окладе, соответст­вующем должности, если в данный момент нет сотрудника, занимающего эту должность. Удаление (DELETE). В случае удаления из таблицы сотрудника, зани­мающего уникальную должность, данные об окладе терялись бы.

Предметная область «Результат обучения» БП: Одну дисциплину может преподавать много преподавателей. Преподаватель может преподавать много дисциплин. Однако преподаватели не могут преподавать любую дисциплину, они имеют свою специализацию. Дисциплины, имеющие одинаковое название, но разное количество часов, считаются разными дисциплинами. Одинаковые дисциплины (совпадает название и количество часов) для разных студентов могут преподавать разные преподаватели. На кафедре работает много преподавателей, каждый преподаватель закреплен за одной кафедрой. Преподаватель может иметь более одного номера телефона, некоторые преподаватели могут иметь одинаковый телефонный номер. Преподаватель занимает только одну должность. Оклад преподавателя определяется его должностью. Студенческая группа состоит более чем из одного студента, каждый студент закреплен за одной группой. Организация учебного процесса: Разные студенты одной группы могут изучать разные дисциплины. После изучения дисциплины студент получает итоговый балл (оценку на экзамене). Фиксируется дата получения итогового балла. Итоговый балл по дисциплине может быть получен в разные даты (т.е. экзамен преподавателю можно сдавать в любой день сессии). Итоговый балл выставляет преподаватель, который преподавал студенту данную дисциплину. Если студент получил неудовлетворительный балл, он может пересдать дисциплину только преподавателю, который читает такую же дисциплину.

Предметная область «Результат обучения»

Пример. Предметная область В-03 «Сведения о проектах 1» (сложн.1) Рисунок –Дан фрагмент документа «Сведения о проектах 1» БП: 1. На проекте может работать много исполнителей, но д. работать хотя бы 1 исполнитель 2. Исполнитель участвует в нескольких проектах, но временно может не участвовать в проектах 3. Заказчик может заказывать более 1 проекта, у проекта только 1 заказчик 4. У исполнителя только 1 должность, которая не зависит от проекта 5. Бюджет проекта назначается заказчиком и не зависит от количества и квалификации исполнителей

Пример. В-05 ПО «Учебный план на 2014/2015» (сложн.2) БП: Учебный план охватывает все группы Преподаватель может ничего не преподавать в 2014/2015 году, каждый преподаватель закреплен за кафедрой, на кафедре работает более одного преподавателя Дисциплина может не читаться в 2014/2015 году Каждая группа закрепляется за факультетом, на факультете много групп. На изучение одной и тот же дисциплины в учебном плане разным группам может выделяться разное количество часов. Нагрузка преподавателя зависит от группы и дисциплины Доп. ПО «План дипломного проектирования на 2014/2015» БП: 8. Студенты 4,5 курсов кроме изучения плановых дисциплин занимаются написанием дипломной работы одного из типов: бакалаврская работа, дипломная работа специалиста, работа магистра 9. За каждым студентом 4,5 курса (бакалавром, специалистом, магистром) закрепляется руководитель (преподаватель), а также тема дипломной работы и назначается предприятие для прохождения преддипломной практики