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

Размещено на http://www.allbest.ru/

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

Введение

проектирование база реляционный

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

1. Предметная область: отдел кадров

Минимальный список характеристик:

· Фамилия сотрудника, имя, отчество, домашний адрес, телефон, дата рождения, образование.

· Должность, дата зачисления, оклад, объем должности.

· Наименование подразделения, количество штатных единиц, фонд заработной платы.

Один и тот же сотрудник может зачисляться на работу в разные подразделения и в одном и том же подразделении может работать несколько сотрудников.

После выделения сущностей и определения связей [1] была получена следующая ER-диаграмма:

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

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

Рис. 2. Уточненная ER-диаграмма

Должность выбирается из предустановленного набора должностей, и не зависит ни от сотрудника, ни от отдела. Поэтому "Должность" становится отдельной сущностью.

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

Зачисление сотрудника на должность в отделе (на нашей диаграмме - "Работа") является ассоциаций, связывающей не сотрудника с должностью и отделом, а сотрудника - со штатным расписанием, так как зачисление может производиться только в соответствии со штатным расписанием. Одна строка штатного расписания может быть связана с несколькими строками работ, так как ставок по данной строке может быть несколько и зачисление может происходить на дробную ставку (например, 0,5). Один сотрудник может быть зачислен на несколько работ. "Теоретически" общий объем работ, связанных со строкой штатного расписания не должен превышать указанное в штатном расписании количество ставок, но на практике это правило, по-видимому, может нарушаться. Кроме того, опять-таки "теоретически", сумма окладов по работам, связанным (через штатное расписание) с одним отделом, не должна превышать фонда зарплаты отдела, но это правило тоже не абсолютное.

Среди запросов, предложенных в задании, есть запрос, подразумевающий наличие информации об образовании сотрудника. Отсюда - необходимость иметь атрибут “Образование” в сущности “Сотрудник”

После нормализации схемы данных [2-5], получаем схему, представленную на рисунке 3. Сущности этой схемы представимы в виде реляционных таблиц.

Рис. 3. Реляционная схема базы данных

Атрибуты сущности "Отдел" - код (первичный ключ) отдела, название и фонд зарплаты отдела.

Атрибуты сущности "Сотрудник":

идентификационный код сотрудника, первичный ключ;

фамилия; имя, отчество;

дата рождения;

адрес;

телефон;

образование.

Атрибуты сущности "Должность":

код должности, первичный ключ;

название должности;

граничные оклады для должности;

Атрибуты сущности "Штатное расписание" - код строки штатного расписания (первичный ключ), коды должности и отдела и число ставок.

Атрибуты сущности "Работа":

идентификационный код сотрудника;

код строки штатного расписания;

оклад ("теоретически" оклад должен лежать в рамках граничных значений для должности);

дата зачисления и объем должности.

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

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

2. Предметная область: поставка товаров

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

В договоре о поставке товара кроме реквизитов поставщика и покупателя указываются реквизиты товара, срок поставки, минимальная партия поставки, количество поставляемого товара и сумма поставки.

Учетная информация с данными по фактической отгрузке товаров покупателю со склада фирмы в соответствии с договорами содержится в накладных. Каждая накладная содержит кроме реквизитов поставщика и покупателя также и все реквизиты продаваемого товара, цену, ставку НДС, количество и сумму.

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

1. Выделение информационных объектов

Для выделения информационных объектов необходимо сделать следующее:

1. Выявить документы, используемые фирмой в своей деятельности, и их реквизиты (поля).

2. Определить функциональную зависимость между реквизитами для каждого документа в отдельности.

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

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

Совокупность полей - реквизитов выделенного объекта должна отвечать требованиям нормализации:

· ИО должен содержать уникальный ключ (простой или составной).

· Все остальные (описательные) реквизиты должны быть независимыми друг от друга.

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

· Каждый описательный реквизит должен функционально полно зависеть от ключа, т.е. каждому значению ключа должно соответствовать только одно значение описательного реквизита.

· При составном ключе описательный реквизит должен зависеть целиком от всей совокупности реквизитов, образующих ключ.

· Каждый описательный реквизит не должен зависеть от ключа транзитивно, через другой промежуточный реквизит.

2. Определим в документе Справочник товаров функциональные зависимости между реквизитами и присвоим им сокращенные имена (рис. 4).

Рис. 4. Функциональные зависимости в документе Справочник товаров

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

Аналогично легко определить ИО ПОКУПАТЕЛЬ и ИО СКЛАД:

ПОКУПАТЕЛЬ (Код_пок, ИНН, наимен_пок, адрес_пок, нм_расч, банк). Код_пок - ключ.

СКЛАД ( Код_ск., Наим_ск., Адрес, Отв._лицо). Код_ск. - ключ.

3. Определим состав документа Договор, содержащего данные о плановых поставках товара.

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

Описательные реквизиты товара (наименование, единица измерения, цена) однозначно определены кодом товара. Эти реквизиты можно не включать в таблицу зависимостей, поскольку ранее их взаимосвязи были установлены при анализе ИО ТОВАР. Остальные реквизиты одного договора (количество поставки товара, минимальная партия, сумма за товар) однозначно определяются кодом товара. На всем же множестве договоров эти реквизиты будут функционально полно зависеть от составного ключа: номер договора+код товара. Будем исходить из того, что в договоре для одного товара возможно несколько сроков поставки, тогда срок поставки войдет в составной ключ номер договора+код товара+срок поставки (рис. 5).

Рис. 5. Функциональные зависимости в документе Договор

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

ДОГОВОР (Ном_дог, Дата_дог, Код_пок., Сумма_дог.). Ключ - Ном. Дог.

ПОСТАВКА_ПЛАН (Ном._дог., Код_тов., Срок_пост.,Кол_пост., Мин._пост., Сумма_пост.). Ключ составной - Ном._дог.+Код._тов.+Срок+пост.

Выполним анализ документа Накладная, содержащего информацию об отгрузке товаров по договорам.

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

Описательные реквизиты товара (наименование, единица измерения, цена, ставка НДС) однозначно определены кодом товара, что уже учтено в ИО ПОКУПАТЕЛЬ.

Количество отгруженного товара и сумма за товар определяются кодом товара в соответствующей строке, а полная идентификация по всем накладным определяется составным ключом: Номер накладной+Код склада+Код товара (рис. 6)

Рис. 6. Функциональные зависимости в документе Накладная

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

Сгруппируем реквизиты, одинаково зависимые от ключевых и объединим их вместе с ключевыми реквизитами в соответствующие информационные объекты:

НАКЛАДНАЯ (Ном. накл, Код ск., Дата отгр., Ном дог, Сумма накл). Ключ составной- Ном. накл. + Код ск.

ОТГРУЗКА( Ном. накл., Код ск., Код тов, Кол. отгр., Сумма отгр.). Ключ составной - Ном. накл.+Код ск.+Код тов.

4. Определение структуры базы данных

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

5. Определение логической структуры БД

Рис. 7. Логическая структура БД

Логическая структура БД изображена на рис. 7. Все связи характеризуются отношением 1:М. Имена ключевых полей выделены жирным шрифтом.

Список литературы

1. В.Н. Шакин, Г.К. Сосновиков, И.Б. Юскова. Методические указания по дисциплине Теоретические основы построения БД. М., МТУСИ. 2004.

2. Г.К. Сосновиков, В.Н. Шакин, И.Б. Юскова. Методические указания и контрольные задания по дисциплине Основы построения БД. М., МТУСИ. 2004.

3. Бекаревич Ю.Б., Пушкина Н.В. Microsoft Access за 21 занятие. - СПб.: БХВ-Петербург, 2005.-544 с.

4. Пушников А.Ю. Введение в системы управления базами данных. Часть 1. Реляционная модель данных: Учебное пособие. /Изд-е Башкирского ун-та. - Уфа, 1999. - 108 с. - ISBN 5-7477-0350-1.

5. Пушников А.Ю. Введение в системы управления базами данных. Часть 2. Нормальные формы отношений и транзакции: Учебное пособие. /Изд-е Башкирского ун-та. - Уфа, 1999. - 138 с. - ISBN 5-7477-0351-X.

Размещено на Allbest.ru