Концептуальное проектирование

 

Концептуальное проектирование – построение смысловой модели предметной области без привязки к какой-либо базе данных [3].

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

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

Покупатели и сотрудники имеют соответствующие уникальные коды.

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

Следовательно, можно выделить следующие сущности семантической модели:

· Покупатель;

· Товар;

· Информация о товаре;

· Склад;

· Заказы;

· Подробная информация о заказе;

· Сотрудники;

· Отдел;

· Информация о сотрудниках.

Каждая сущность характеризуется своим набором атрибутов (см. таблицу 1).

 

Таблица 1. Перечень атрибутов

Сущность	Атрибуты
Покупатель	· Фамилия; · Имя; · Отчество; · Адрес; · Город; · Телефон; · Дата регистрации.
Сотрудники	· Фамилия, · Имя; · Отчество; · Должность; · Зарплата; · Премия; · Семейное положение; · Дата рождения; · Дата приема на работу; · Адрес; · Телефон. · Отдел
Отдел	· Название отдела;
Товар	· Название.
Описание товара.	· Цвет; · Материал; · Текстовое описание.
Складские запасы	· Количество.
Заказы	· Идентификатор покупателя; · Идентификатор сотрудника; · Дата заказа.
Детали заказа	· Количество заказанных позиций; · Идентификатор продукта; · Количество заказанных единиц; · Цена товара; · Итоговая сумма заказа.

 

 

ЛОГИЧЕСКОЕ ПРОЕКТИРОВАНИЕ БАЗЫ ДАННЫХ

Модель «сущность-связь»

 

Модель сущность-связь (ER-модель) − модель данных, позволяющая описывать концептуальные схемы предметной области [4]. ER-модель используется при высокоуровневом (концептуальном) проектировании баз данных. С её помощью можно выделить ключевые сущности и обозначить связи, которые могут устанавливаться между этими сущностями.

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

Ниже приведена модель «сущность-связь» интернет-магазина. Она включает в себя 8 сущностей (Покупатель, Сотрудники, Отдел, Товар, Описание Товаров, Складские запасы, Заказы, Детали заказа).

Атрибуты: «Фамилия», «Имя», «Отчество», «Адрес», «Город», «Телефон», «Дата регистрации», принадлежат сущности «Попупатели».

Атрибуты: «Фамилия», «Имя», «Отчество», «Должность», «Зарплата», «Премия», «Семейное положение», «Дата рождения», «Дата приема на работу», «Адрес», «Телефон», «Отдел» принадлежат сущности «Сотрудники».

Атрибуты: «Название отдела» принадлежат сущности «Отдел».

Атрибуты: «Название Товара» принадлежат сущности «Товар».

Атрибуты: «Цвет», «Материал», «Текстовое описание» принадлежат сущности «Описание товара».

Атрибуты: «Количество» принадлежат сущности «Склад».

Атрибуты: «Идентификатор покупателя», «Идентификатор сотрудника», «Дата заказа» принадлежат сущности «Заказы».

Атрибуты: «Количество заказанных позиций», «Идентификатор продукта», «Количество заказанных единиц», «Цена товара», «Итоговая сумма заказа принадлежат сущности «Детали заказа».

 

Классификация связей

Связь позволяет моделировать отношения между объектами предметной области. Наименование связи должно быть уникально во всей модели.

Связь «один ко многим» самая распространенная. В этом типе связей у строки таблицы А может быть несколько совпадающих строк таблицы Б, но каждой строке таблицы Б может соответствовать только одна строка из А.

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

Таблица 2. Классификация связей

Сущность А	Сущность В	Тип связи
Отдел	Сотрудник	«Один-к-одному»
Детали заказа	Заказы	«Один-ко-многим»
Покупатель
Сотрудники
Детали товара	Товары	«Один-к-одному»
Склад
Детали заказа	Товары	«Один-ко-многим»

 

Определение связей

 

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

1. Связи таблиц: «Сотрудники» и «Отдел».

2. Таблицы «Сотрудники» и «Отдел» должны быть связаны связью «один к одному» по ключу «ID «из таблицы Сотрудники и ключом «ID» из таблицы «Отдел».

3. Связи таблиц: «Товар» и «Детали товара».

Таблицы «Товар» и «Детали товара» c должны быть связаны связью «один к одному» по ключу «ID» из таблицы «Товар» и ключу «ID» из таблицы «Детали товара».

4. Связи таблиц: «Товары» и «Склад».

Таблицы «Товары» и «Склад» должны быть связаны связью «один к одному» по ключу «ID» из таблицы «Товары» и ключу «ТоварID» из таблицы Склад.

5. Связи таблиц: «Покупатели» и «Заказы».

Таблицы «Покупатели» и «Заказы» должны быть связаны связью «один ко многим» по ключу «ID» из таблицы «Покупатели» и ключу «ПокупателиID» из таблицы «Заказы».

6. Связи таблиц: «Сотрудники» и «Заказы».

Таблицы «Сотрудники» и «Заказы». c должны быть связаны связью «один ко многим» по ключу «ID» из таблицы «Сотрудники» и ключу «СотрудникиID» из таблицы «Заказы».

7. Связи таблиц: Товары и Детали заказа.

Таблицы «Товары» и «Детали заказа» c должны быть связаны связью «один ко многим» по ключу «ID» из таблицы «Товары» и ключу «ТоварID» из таблицы «Детали заказа».

После проведённого анализа необходимых связей таблиц можно построить схему модели сущности-связь (см. рис. 1).

Рис. 1. Модель «сущность-связь»

Нормализация отношений

Нормальная форма − требование, предъявляемое к структуре таблиц в теории реляционных баз данных для устранения из базы избыточных функциональных зависимостей между атрибутами (полями таблиц) [5].

Каждая таблица в реляционной БД удовлетворяет условию, в соответствии с которым в позиции на пересечении каждой строки и столбца таблицы всегда находится единственное атомарное значение, и никогда не может быть множества таких значений. Любая таблица, удовлетворяющая этому условию, называется нормализованной. Фактически, ненормализованные таблицы, т.е. таблицы, содержащие повторяющиеся группы, даже не допускаются в реляционной БД. [6]

Всякая нормализованная таблица автоматически считается таблицей в первой нормальной форме, сокращенно 1НФ. Таким образом, строго говоря, "нормализованная" и "находящаяся в 1НФ" означают одно и то же. Однако на практике термин "нормализованная" часто используется в более узком смысле – "полностью нормализованная", который означает, что в проекте не нарушаются никакие принципы нормализации.

Теперь в дополнение к 1НФ можно определить дальнейшие уровни нормализации – вторую нормальную форму (2НФ), третью нормальную форму (3НФ) и т.д.

По существу, таблица находится в 2НФ, если она находится в 1НФ и удовлетворяет, кроме того, некоторому дополнительному условию, суть которого будет рассмотрена ниже. Таблица находится в 3НФ, если она находится в 2НФ и, помимо этого, удовлетворяет еще другому дополнительному условию и т.д.

Таким образом, каждая нормальная форма является в некотором смысле более ограниченной, но и более желательной, чем предшествующая. Это связано с тем, что "(N+1)-я нормальная форма" не обладает некоторыми непривлекательными особенностями, свойственным "N-й нормальной форме". Общий смысл дополнительного условия, налагаемого на (N+1)-ю нормальную форму по отношению к N-й нормальной форме, состоит в исключении этих непривлекательных особенностей. В п. 4.3 мы выявляли непривлекательные особенности таблицы рис. 4.2 и для их исключения выполняли "интуитивную нормализацию". [7]

Ключевым свойством 1НФ является атомарность: значение в ячейке таблицы может быть только одно, например, если столбец называется ФИО, то и значение «Иванов Иван Иванович» должно быть только одно.

Таблица 3. Пример неправильной 1НФ

Код	Сотрудник	Отдел	Дата рождения
	Перминов Алексей Владимирович	Отдел продаж, Отдел поддержки.	08.12.1990
	Савиных Олег Анатольевич	Отдел продаж	27.01.1989

В 1НФ атрибуты не должны иметь несколько значений, например, если в столбце «Отдел», прописано два значение «Отдел поддержки» и «Отдел поддержки» то необходимо разбить эти значения.

Таблица 4. Пример неправильной 1НФ

Код	Сотрудник	Отдел	Дата рождения
	Перминов Алексей Владимирович	Отдел продаж	08.12.1990
	Туманов Георгий Викторович	Отдел продаж	27.01.1989
	Перминов Алексей Владимирович	Отдел поддержки.	08.12.1990

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

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

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

Таблица 5. Пример неправильной 2НФ

ID	Сотрудник	Товар	Клиент	Отдел	Тел. клиент
	Перминов	Пивоварня Золингер	Савиных	Отдел продаж	22-43-77
	Туманов	Коптильня ханхи	Вараксин	Отдел продаж	23-44-33
	Суворов	Коптильня дымка	Жарков	Отдел продаж	11-33-35

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

Создаём таблицы на основе функциональных связей:

Таблица 6. Таблица-справочников «Продажи» во 2НФ

ID	Товар	Сотрудник	Клиент
	Пивоварня Золингер
	Коптильня ханхи
	Коптильня дымка

 

Таблица 7. Таблица-справочников «Сотрудники» во 2НФ

ID	Сотрудник	Д.Р. сотрудника
	Перминов	08.12.1990
	Туманов	27.01.1989
	Суворов	17.03.1991

 

Таблица 8. Таблица-справочников «Сотрудники» во 2НФ

ID	Клиент	Телефон клиента
	Савиных	22-43-77
	Вараксин	23-44-33
	Жарков	11-33-35

Выполнив преобразования, получаем отношения, находящееся во второй нормальной форме, без избыточности данных.

Переменная отношения находится в третьей нормальной форме тогда и только тогда, когда она находится во 2НФ, и отсутствуют транзитивные функциональные зависимости неключевых атрибутов от ключевых [8].

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

Таблица 9. Пример таблицы не находящейся в 3НФ

Модель	Материал
Коптильня дымка	Нержавеющая сталь
Коптильня ханхи	Чёрная сталь
Пивоварня Золингер	Чёрная сталь

В таблице 9 ключом является имя название товара, а неключевым полем – Материал. Логично предположить, что в этой таблице поле «Материал» может быть одинаковыми для нескольких записей. И для того, чтобы эта таблица находилась в третей нормальной форме, не обходимо ее разделить на две.

Таблица 10. Таблицы «Материал» находящейся в 3НФ

ID	Поставщик
	Нержавеющая сталь
	Чёрная сталь

Таблица 11. Пример таблицы не находящейся в 3НФ

Товар	Материал
Пивоварня Золингер
Коптильня ханхи
Коптильня дымка

Для правильного функционирования базы данных все таблицы необходимо преобразовать во 2НФ или 3НФ.