Примеры подходов к проектированию реляционных баз данных

1.

Московский авиационный институт
(национальный исследовательский университет)
Факультет №4 «Радиоэлектроника летательных аппаратов»
Направление подготовки: Безопасность информационных технологий
правоохранительной сфере
Специализация
Дисциплина:
подготовки:
в
Информационно-аналитическое обеспечение
правоохранительной
деятельности
с
использованием космических технологий
БАЗЫ ДАННЫХ
СЕРПУХОВ 2013

2.

Лекция 3 Примеры подходов к проектированию реляционных баз
данных
Учебные вопросы:
3.1 Проектирование БД на основе метода «сущность-связь»
3.2 Проектирование БД методом, основанным на анализе документов,
содержащих данные, которые должны быть размещены в БД
Литература:
Швецов В.И., Визгунов А.Н., Мееров И.Б. Базы данных. Учебное пособие.
Нижний Новгород: Изд-во ННГУ, 2004.

3.

3.1 Проектирование БД на основе метода «сущность-связь»

4.

При создании реляционной базы данных пользователь должен
располагать описанием предметной области. На основе такого описания в
процессе проектирования БД осуществляется определение состава и
структуры данных.
Приведем пример проектирования БД с использованием метода
«сущность-связь», построения ER-диаграммы и в последствии –
реляционной схемы БД.
Предметная область: ОТДЕЛ КАДРОВ
Минимальный список характеристик:
- Фамилия сотрудника, имя, отчество, домашний адрес, телефон, дата рождения,
образование.
- Должность, дата зачисления, оклад, объем должности.
- Наименование подразделения, количество штатных единиц, фонд заработной
платы.
Один и тот же сотрудник может зачисляться на работу в разные
подразделения и в одном и том же подразделении может работать несколько
сотрудников.

5.

ER-диаграмма предметной области

6.

Проведём анализ предметной области
Должность выбирается из предустановленного набора должностей, и не зависит ни от
сотрудника, ни от отдела. Поэтому "Должность" становится отдельной сущностью.
Перечень должностей, которые могут быть в каждом отделе, определяется штатным
расписанием. Таким образом, "Штатное расписание" является ассоциативной сущностью,
связывающей сущности "Отдел" и "Должность".
Строка штатного расписания содержит информацию о количестве ставок одной должности в
одном отделе. Таким образом, одной должности может соответствовать несколько строк в
"Штатном расписании" (для разных отделов), и одному отделу - тоже несколько строк (для разных
должностей).
Так как зачисление работника на должность в отдел может производиться только в
соответствии со штатным расписанием, следовательно надо ввести ассоциативную сущность,
связывающую сотрудника не с должностью и отделом, а со штатным расписанием. Эту сущность
назовём «Работа».
Одна строка штатного расписания может быть связана с несколькими строками работ, так
как ставок по данной строке может быть несколько и зачисление может происходить на
дробную ставку (например, 0,5).
Один сотрудник может быть зачислен на несколько работ.

7.

Реляционная схема базы данных
ОТДЕЛ
Код отдела
ШТАТНОЕ РАСПИСАНИЕ
ДОЛЖНОСТЬ
Код штатного
расписания
Код должности
Код должности
Наименование
Код отдела
Мин. оклад
Наименование
Фонд
Макс. оклад
Число ставок
РАБОТА
СОТРУДНИК
Код сотрудника
Код сотрудника
Код штатного
расписания
ФИО
Дата рождения
Оклад
Адрес
Дата зачисления
Телефон
Объем должности
Образование

8.

Атрибуты сущности "Отдел" :
код (первичный ключ) отдела;
название отдела;
фонд зарплаты отдела.
Атрибуты сущности "Сотрудник":
идентификационный код сотрудника (первичный ключ);
Фамилия, имя, отчество;
дата рождения;
адрес;
телефон;
образование.
Атрибуты сущности "Должность":
код должности (первичный ключ);
название должности;
граничные оклады для должности;
Атрибуты сущности "Штатное расписание«:
код строки штатного расписания (первичный ключ), коды должности и
отдела и число ставок.
Атрибуты сущности "Работа":
идентификационный код сотрудника;
первичный ключ
код строки штатного расписания;
оклад;
дата зачисления и объем должности.

9.

3.2 Проектирование БД методом, основанным на анализе
документов, содержащих данные, которые должны быть
размещены в БД

10.

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

11.

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

12.

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

13.

Определим в документе Справочник Товаров функциональные
зависимости между реквизитами и присвоим им сокращенные имена
Реквизиты
Код товара
Наименование
товара
Единица
измерения
Цена
Ставка НДС
Имя реквизита
Функциональные
зависимости
Код_тов
Наим_тов
Еи
Цена
Ставка_НДС
Из анализа документа очевидно, что ключом является Код_тов, от которого
функционально полно зависят все остальные описательные реквизиты. Все
реквизиты составляют содержание ИО ТОВАР.

14.

Аналогично легко определить ИО ПОКУПАТЕЛЬ и ИО СКЛАД:
ПОКУПАТЕЛЬ (Код_пок, ИНН, наимен_пок, адрес_пок, нм_расч, банк).
Код_пок – ключ.
СКЛАД ( Код_ск., Наим_ск., Адрес, Отв._лицо).
Код_ск. – ключ.
Определим состав документа Договор, содержащего данные о плановых
поставках товара.

15.

Наименование
реквизитов
Имя реквизита
Номер договора
Ном_дог
Дата договора
Дата_дог
Код покупателя
Код пок
Сумма по договору
Сумма дог
Код товара
Код тов
Срок поставки
Срок пост
Количество поставки
Кол пост
Минимальная партия
Мин пост
Сумма поставки
Сумма пост
Функциональные зависимости
Кодом покупателя однозначно определяются описательные реквизиты
покупателя – наименование, ИНН, адрес, телефон, расчетный счет, банк. В таблице
зависимостей эти реквизиты можно не отображать, поскольку информационный
объект ПОКУПАТЕЛЬ, образованный этими реквизитами, был уже выделен.

16.

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

17.

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

18.

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

19.

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

20.

Наименование
Имя реквизита
реквизитов
Функциональные
зависимости
Номер накладной
Ном накл
Код склада
Код ск
Дата отгрузки
Дата отгр
Номер договора
Ном дог
Сумма всего
Сумма накл
Код товара
Код тов
Количество
Кол отгр
отгруженного товара
Сумма за товар
Сумма отгр
Из рисунка
видно, что реквизиты Дата отгр., Ном дог. и Сумма накл.
зависят от ключевых атрибутов Ном. накл + Код ск. , а реквизиты Кол. отгр. и
Сумма отгр. зависят от Ном. накл.+ Код ск. + Код тов.

21.

Наименование
Имя реквизита
реквизитов
Функциональные
зависимости
Номер накладной
Ном накл
Код склада
Код ск
Дата отгрузки
Дата отгр
Номер договора
Ном дог
Сумма всего
Сумма накл
Код товара
Код тов
Количество
Кол отгр
отгруженного товара
Сумма за товар
Сумма отгр
Сгруппируем реквизиты, одинаково зависимые от ключевых и объединим их
вместе с ключевыми реквизитами в соответствующие информационные объекты:
НАКЛАДНАЯ (Ном. накл, Код ск., Дата отгр., Ном дог, Сумма накл). Ключ
составной– Ном. накл. + Код ск.
ОТГРУЗКА( Ном. накл., Код ск., Код тов, Кол. отгр., Сумма отгр.). Ключ
составной – Ном. накл.+Код ск.+Код тов.

22.

Определение структуры базы данных
Для определения структуры БД (построения информационнологической модели) необходимо установить связи между ИО.
Объекты ДОГОВОР и ТОВАР имеют
отношения М:М, поэтому
необходима сущность-связка, в качестве которой выступает сущность
ПОСТАВКА_ПЛАН.
Такая же ситуация и между объектами ТОВАР и НАКЛАДНАЯ – в
качестве связки между ними выступает сущность ОТГРУЗКА.
Остальные связи – 1:М - между объектами ПОКУПАТЕЛЬ-ДОГОВОР,
СКЛАД-НАКЛАДНАЯ,ДОГОВОР-НАКЛАДНАЯ.

23.

ПОКУПАТЕЛЬ
ТОВАР
СКЛАД
Код пок
Инн
Наим пок
Адрес пок
Ном расч
Банк
Код тов
Наим
Цена
Еи
Ндс
Код ск
Наим ск
Отв лицо
Адрес ск
НАКЛАДНАЯ
ДОГОВОР
Ном накл
Код ск
Дата отгр
Ном дог
Сумма
Ном дог
Дата дог
Код пок
Сумма дог
ОТГРУЗКА
ПОСТАВКАПЛАН
Ном дог
Код тов
Срок пост
Мин пост
Кол пост
Сумма пост
Ном накл
Код ск
Код тов
Кол отгр
Сумма отгр

24.

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