Проектирование базы данных Дунько Элеонора Михайловна К э

Проектирование базы данных Дунько Элеонора Михайловна К. э. н. , доцент кафедры информационных технологий е-mail: dunkoaly@mail. ru

План 1. Требования к БД. 2. Этапы жизненного цикла БД. 3. Модель «сущность-связь» . 4. CASE-средства моделирования данных. 5. Нормализация таблиц. 6. Этапы проектирования БД и их процедуры

Требования к БД 1. Независимость данных. 2. Совместное использование данных многими пользователями. 3. Безопасность данных. 4. Стандартизация построения и эксплуатации БД. 5. Адекватность отображения данных соответствующей предметной области. 3

Жизненный цикл БД Жизненный цикл базы данных (ЖЦБД) – это процесс проектирования, реализации и поддержки базы данных. ЖЦБД состоит из 7 этапов: предварительное планирование; проверка осуществимости; определение требований; концептуальное проектирование; логическое проектирование; физическое проектирование; оценка работы и поддержка БД.

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

Этапы проектирования БД концептуальное проектирование; логическое проектирование; физическое проектирование. Предложено рабочей группой CODASYL в 1971 г.

Концептуальное проектирование БД Концептуальная модель – абстракция предметной области, которая базируется на графических диаграммах. Популярной методикой создания концептуальной модели является методика построения модели «Сущность–Связь» , называемая ER-моделью (Entity Relationship – сущность-связь), или моделью «Объект– Отношение» Эта методика была предложена Петером Пин-Шен Ченом в 1976 г.

Базовые понятия ER-методики: сущность, связь, атрибут Сущность – объект, информация о котором представляет интерес или часть реального мира, информация о которой хранится в БД. Менеджер Филиал Клиент Заказ Предметная область ФИРМА Связь - представляет взаимодействие между сущностями. Управляет Менеджер Атрибут – свойство, характеризу- Табельный номер ющее сущность, каждая из которых Стаж Специальность описана своим набором атрибутов.

Типы связей в ER-модели 1 Менеджер 1 Управляет 1 Филиал M Обрабатывает M Клиент Филиал Заказ N Делает Заказ

Класс принадлежности в ER-модели Если каждый экземпляр сущности А связан с экземпляром сущности В, то класс принадлежности сущности А является обязательным. Этот факт отмечается на ER-диаграмме черным кружочком, помещенным в прямоугольник, смежный с прямоугольником сущности А. Если не каждый экземпляр сущности А связан с экземпляром сущности В, то класс принадлежности сущности А является необязательным. Этот факт отмечается на ER-диаграмме черным кружочком, помещенным на линии связи возле прямоугольника сущности А.

Возможные ER-диаграммы для связи М: N c учетом класса принадлежности сущности

Пример ER-модели предметной области ФИРМА МЕНЕДЖЕР КЛИЕНТ М 1 ДЕЛАЕТ УПРАВЛЯЕТ 1 11 M N ОБРАБАТЫВАЕТ ФИЛИАЛ ЗАКАЗ В рассматриваемой предметной области ФИРМА класс принадлежности всех четырех сущностей является обязательным.

Концептуальная схема БД ФИРМА МЕНЕДЖЕР Номер менеджера (НМ) ФИЛИАЛ Номер филиала (НФ) Стаж работы (СТАЖ) Адрес филиала (АДР_Ф) Специальность (СПЕЦ) КЛИЕНТ ЗАКАЗ Номер Заказа (НЗ) Номер клиента (НК) Ф. И. О. клиента (ФИО_К) Дата заказа(ДЗ) Социальное положение (СОЦ) Вес заказа (ВЗ) Адрес клиента (АДР_К) Наборы атрибутов сущностей предметной области ФИРМА Примечание. Ключевые атрибуты выделены жирным шрифтом.

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

Преобразование ER- модели в реляционную Правило 3 Если связь типа 1: 1 и класс принадлежности обеих сущностей является необязательным, то необходимо построить три таблицы – по одной для каждой сущности и одну для связи. Первичный ключ сущности должен быть первичным ключом соответствующей таблицы. Таблица для связи среди своих атрибутов должна иметь ключи обеих сущностей. Правило 4 Если связь типа 1: М и класс принадлежности сущности на стороне М является обязательным, то необходимо построить таблицу для каждой сущности. Первичный ключ сущности должен быть первичным ключом соответствующей таблицы. Первичный ключ сущности на стороне 1 добавляется как атрибут в таблицу для сущности на стороне М.

Преобразование ER- модели в реляционную Правило 5 Если связь типа 1: М и класс принадлежности сущности на стороне М является необязательным, то необходимо построить три таблицы – по одной для каждой сущности и одну для связи. Первичный ключ сущности должен быть первичным ключом соответствующей таблицы. Таблица для связи среди своих атрибутов должна иметь ключи обеих сущностей. Правило 6 Если связь типа М: N, то необходимо построить три таблицы – по одной для каждой сущности и одну для связи. Первичный ключ сущности должен быть первичным ключом соответствующей таблицы. Таблица для связи среди своих атрибутов должна иметь ключи обеих сущностей.

CASE-средства моделирования данных CASE-средства (Computer Aided System Engineering) предназначены для автоматизированного проектирования реляционных БД. Широко распространены CASE-системы, позволяющие выполнять ER-диаграммы в соответствии со стандартом IDEF 1 X: q Erwin; q Design/IDEF; q Power Designer.

Характерные особенности CASE-средств единый графический язык; использование репозитария; поддержка коллективной разработки и управления проектом; макетирование; генерация документации; верификация проекта; поддержка всех этапов ЖЦ БД.

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

Главное окно ERwin

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

Первой нормальной формой называется реляционная таблица, в которой все значения полей являются атомарными, т. е. любая реляционная база данных находится в первой нормальной форме. Номер заказа 1021 1022 1023 1024 1025 1026 1027 1028 1029 1030 1031 1032 1033 1034 Код клиента АА АА АС АС АВ АВ АД АС АС Город Улица Минск Пуховичи Пуховичи Мюнхен Минск Пуховичи Правды 11 Широкая 1 Широкая 1 Лейбница 8 Захарова 20 Широкая 1 Вес заказа 100 150 300 400 700 230 120 100 300 500 300 200 300 Дата заказа 01. 02. 06 10. 03. 06 11. 03. 06 12. 03. 06 10. 03. 06 15. 04. 06 20. 04. 06 08. 05. 06 15. 05. 06 Код валюты 100 101 103 101 102 101 103 102 101 Все заказы клиентов сведены в одну таблицу Заказ. Инфо Наименование валюты Бел рубль Росс рубль Евро Росс рубль Доллар США Росс рубль Евро Доллар Росс рубль

Номер заказа 1021 1022 1023 1024 1025 1026 1027 1028 … 1034 Код клиента АА АА АС АС АС. . . АС Город Улица Минск Пуховичи Пуховичи … Пуховичи Правды 11 Широкая 1 Широкая 1 … Широкая 1 Вес заказа 100 150 300 400 700 230 120 100 … 300 Дата заказа 01. 02. 06 10. 03. 06 11. 03. 06 12. 03. 06 10. 03. 06 15. 04. 06 … 15. 06 Код валюты 100 101 103 101 102 … 101 Наименование валюты Бел рубль Росс рубль Евро Росс рубль Доллар США … Росс рубль Таблицы в первой нормальной форме являются, как правило, избыточными, например сведения о клиенте (Код клиента, Адрес) повторяется в записи о каждом заказанном продукте. Проблемы такой таблицы: • • • Узнаем адрес клиента, только в том случае если он что- то заказал При удалении записи о продукте одновременно удаляются все сведения о самом заказе и о клиенте Если сменил адрес, например, то его надо менять в каждой записи Вывод: Данные в таблице необходимо нормализовать путем разбиения исходной таблицы на несколько таблиц, связанных первичными и внешними ключами.

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

В исходной таблице поле Код клиента определяется как внешний ключ 1 -й шаг нормализации: Для связывания в дальнейшем таблиц, получаемых из Таблицы Заказ. Инфо определим первичный ключ. Такое поле рекомендуется выбирать из тех полей, записи в которых являются избыточными: определим поле Код клиента в качестве первичного ключа. По определению ссылочной целостности первичный ключ не должен содержать повторяющихся записей, поэтому первая промежуточная таблица, назовем ее Заказ. Инфо 1, будет содержать только неповторяющееся записи Кода клиента. Номер заказа Код клиента Город Улица Вес заказа Дата заказа Код валюты Наименование валюты 1021 1022 1024 1025 АА АС АВ АД Минск Пуховичи Мюнхен Минск Правды 11 Широкая 1 Лейбниц 8 Захарова 20 100 300 500 300 01. 02. 06 10. 03. 06 20. 04. 06 08. 05. 06 100 101 103 102 Бел рубль Росс рубль Евро Доллар В исходной таблице поле Код клиента определяется как внешний ключ

2 -й шаг нормализации: В исходной таблице определяем поля, которые зависят только от первичного ключа, т. е. их значения не меняются при одинаковом значении записи первичного ключа: поля Город и Улица. Только эти поля остаются в таблице Заказ. Инфо 1, остальные поля удаляются. Таблицу Заказ. Инфо 1 переименуем в таблицу Клиенты. Из исходной таблицы Заказ. Инфо эти же поля удаляются и исходную таблицу Заказ. Инфо переименовываем в таблицу Заказ. Инфо 2.

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

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

1 -й шаг нормализации В таблице Заказ. Инфо 2, находящейся во второй нормальной форме, определим поле Код валюты в качестве первичного ключа. Полностью от этого поля зависит только поле Наименование валюты. 2 -й шаг нормализации q. Из таблицы Заказ. Инфо 2 выбираются только уникальные (неповторяющиеся) записи Код Валюты, и из полей Код Валюты и Наименование получается третья таблица Банк. q. Из таблицы Заказ. Инфо 2 удаляется поле Наименование валюты и таблица Заказ. Инфо 2 переименовывается в таблицу Заказы 3 -й шаг нормализации Объединяются Таблицы Клиенты, Заказы и Банк с помощью первичных и внешних ключей

Банк Код валюты 100 101 102 103 Наименование валюты Бел рубль Росс рубль Доллар США Евро Заказы Код Номер клиента заказа АА 1021 АА 1022 АС 1023 АС 1024 АС 1025 АС 1026 АС 1027 АС 1028 АС 1029 АВ 1030 АВ 1031 АД 1032 АС 1033 АС 1034 Вес заказа 100 150 300 400 700 230 120 100 300 500 300 200 300 Дата заказа 01. 02. 06 10. 03. 06 11. 03. 06 12. 03. 06 10. 03. 06 15. 04. 06 20. 04. 06 08. 05. 06 15. 05. 06 Структура базы данных в третьей нормальной форме: Код валюты 100 101 103 101 102 101 103 102 101

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

Этапы проектирования БД концептуальное проектирование; логическое проектирование; физическое проектирование. Предложено рабочей группой CODASYL в 1971 г.

Концептуальное проектирование Цель этапа концептуального проектирования – создание Цель концептуального проектирования концептуальной модели данных исходя из представлений пользователей о предметной области. Этапы: 1. 2. 3. 4. 5. 6. 7. Определение сущностей. Определение связей между сущностями. Создание ER-модели предметной области. Определение атрибутов. Определение значений атрибутов. Определение первичных ключей для сущностей. Обсуждение концептуальной модели данных с конечными пользователями. Обязательное документирование каждого этапа!

Логическое проектирование Цель этапа логического проектирования – Цель логического проектирования преобразование концептуальной модели на основе выбранной модели данных в логическую модель, не зависимую от особенностей используемой в дальнейшем СУБД для физической реализации БД. Этапы: 1. Выбор модели данных. 2. Определение набора таблиц исходя из ER-модели и их документирование. 3. Нормализация таблиц. 4. Проверка логической модели данных на предмет возможности выполнения транзакций. 5. Определение требований поддержки целостности данных и их документирование 6. Создание окончательного варианта логической модели данных и обсуждение его с пользователями.

Физическое проектирование Цель этапа физического проектирования –описание Цель физического проектирования конкретной реализации БД, размещаемой во внешней памяти компьютера. Это описание структуры хранения данных и эффективных методов доступа к данным базы. Этапы: 1. Проектирование таблиц базы данных средствами выбранной СУБД. 2. Реализация бизнес-правил в среде выбранной СУБД. 3. Проектирование физической организации базы данных. 4. Разработка стратегии защиты базы данных. 5. Организация мониторинга функционирования базы данных и ее настройка.

Спасибо за внимание!