Пример проектирования БД.

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

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

1. Ведомость наличия лекарств в аптеках города - по следующей форме:

Ведомость наличия в аптеках лекарства ______________

№ п/п	№ аптеки	Адрес аптеки	вид упаковки	стоимость за ед.	количество

2.Сведения о поставщиках лекарств.

Сведения о поставщике ________________________

импортных лекарств.

№ п/п	Поставщик	№ лицензии

3. Запрос на поставку лекарства в аптеку.

Запрос на поставку лекарств

в аптеку _____________________

№ п/п	Наименование лекарства	объем поставки	поставщик

3. Распечатка нормативного количества лекарств по аптекам.

Нормативное количество лекарств

в аптеке ___________________

№ п/п	Наименование лекарства	Нормативное количество

Первая нормальная форма.По определению, отношение находится в I НФ, если все его атрибуты атомарны. Это означает, что ни один из атрибутов исходного отношения не должен допускать деления на какие- либо составные части без потерь информации. Вообще говоря, этому требованию легко удовлетворить, исходя из самых общих и очевидных соображений о семантике данных. На этом этапе проектирования наиболее трудным и ответственным шагом является принятие решений о собственно включении тех или иных атрибутов в исходное отношение.

Здесь же принимается решение о включении в информационную модель “искусственных” атрибутов - кодов, которые призваны стать однозначными идентификаторами кортежей отношений. Такая необходимость может возникнуть, например, если “естественный” первичный ключ для данного отношения либо отсутствует в предметной области, либо не обеспечивает достаточно надежной идентификации кортежа (как фамилии человека недостаточно для надежной персональной идентификации - и там, где это необходимо вводятся, например, табельные номера).

Введение цифровых кодов вместо строковых идентификаторов в случаях, когда число кортежей невелико, может снизить вероятность ошибки оператора при вводе данных и увеличить скорость ввода. При большой длине кода для облегчения работы оператора прибегают к структуризации его. Это означает, что код разбивается на последовательность числовых групп, каждой из которых присваивается собственное значение. Например, в рассматриваемой задаче возникает необходимость построения однозначного идентификатора лекарственного средства. Торговое название не может служить таким идентификатором, поскольку во- первых, трудно гарантировать уникальность наименований, во- вторых, одно и то же вещество может поступать в продажу в разных упаковках и дозировках, что с точки зрения и фармацевтики и торговли является совершенно различными случаями, и, наконец, в- третьих, торговые названия часто сложны, длинны или труднопроизносимы, что резко увеличивает вероятность ошибок оператора при вводе/ корректировке данных. Обойти эти затруднения возможно, если сделать следующие допущения:

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

- Идентификатором лекарства служит числовой код.

- Числовой код состоит из трех групп чисел и имеет следующую структуру.

XXX-YYY-ZZZ

ZZZ - Идентификатор собственно лекарства.

YYY - Идентификатор упаковки/дозировки

XXX - Группа лекарственных средств (сердечно - сосудистые, транквилизаторы, желудочно- кишечные и т.д.)

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

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

Естественным идентификатором аптеки является ее номер.

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

Перейдем к построению первой нормальной формы. Сведем в таблицу 1 имена атрибутов предварительного отношения, выделенных в описании задачи и коды, описанные в предыдущих абзацах. Помня, что эти имена должны стать именами полей таблиц, будем пользоваться соглашениями об именах полей, общепринятыми для большинства современных СУБД. Имя должно состоять не более чем из десяти символов, может включать в себя буквы латинского алфавита, цифры и некоторые специальные символы, такие, как знак подчеркивания <_>. Имя не должно начинаться с цифры, не должно содержать пробелы, строчные и прописные буквы равноправны. Также в таблице 1 представлено содержимое каждого атрибута.

Таблица 1

№ п/п	Имя атрибута	Содержание
	No_Apt	Номер аптеки - идентифицирует аптеку
	Adr_Apt	Адрес аптеки
	Kod	Код лекарственного средства
	Name	Наименование лекарственного средства
	Pack	Вид упаковки лекарственного средства
	Amount	Остаток лекарственного средства в аптеке
	Amount_N	Нормативное количество лекарственного средства
	Ed	Единицы измерения лекарственного средства
	Price	Цена за единицу измерения лекарственного средства
	GNI	Код ГНИ поставщика
	Salor	Наименование поставщика
	Lic	№ лицензии поставщика для работы с данным лекарственным средством
	DocNum	№ требования на поставку лекарственного средства
	Volume	Объем поставки лекарственного средства

Первичным ключом отношения является совокупность атрибутов 1,3,10,13.

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

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

Таблица 2

№ п/п	Имя атрибута	Содержание	Функционально зависит от атрибутов
	No_Apt	Номер аптеки - идентифицирует аптеку	Ключевой атрибут
	Adr_Apt	Адрес аптеки
	Kod	Код лекарственного средства	Ключевой атрибут
	Name	Наименование лекарственного средства
	Pack	Вид упаковки лекарственного средства
	Amount	Остаток лекарственного средства в аптеке	1,3
	Amount_N	Нормативное количество лекарственного средства	1,3
	Ed	Единицы измерения
	Price	Цена за единицу измерения
	GNI	Код ГНИ поставщика	Ключевой атрибут
	Salor	Наименование поставщика
	Lic	№ лицензии поставщика для работы с данным лекарственным средством	3,10
	DocNum	№ требования на поставку лекарственного средства	Ключевой атрибут
	Volume	Объем поставки лекарственного средства	1,3,13

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

· Нормативное количество каждого лекарственного средства задается для каждой аптеки.

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

· По одному запросу на поставку может быть затребовано несколько наименований лекарств. (В противном случае поле DocNum не вошло бы в первичный ключ).

При анализе колонки “функциональные зависимости” таблицы 2 можно выделить шесть функциональных зависимостей:

1. No_Apt ® Ard_Apt

2. Kod ® Name, Pack, Ed, Price

3. No_Apt, Kod ® Amount, Amount_N

4. GNI ® Salor

5. Kod, GNI ® Lic

6. No_Apt,Kod,GNI ® Volume

Каждая из этих зависимостей должна стать основой построения проекции исходного отношения. Результаты представлены в таблицах 3..8. В тех же таблицах представлены описания типов полей,

Таблица 3

Отношение “Аптека”

№ п/п	Имя атрибута	Содержание	Тип поля	Длина	Дес. знаков
	No_Apt	Номер аптеки - идентифицирует аптеку	Numeric
	Adr_Apt	Адрес аптеки	Character

Таблица 4

Отношение “Лекарство”

№ п/п	Имя атрибута	Содержание	Тип поля	Длина	Дес. Знаков
	Kod	Код лекарственного средства	Numeric
	Name	Наименование лекарственного средства	Character
	Pack	Вид упаковки лекарственного средства	Character
	Ed	Единицы измерения лекарственного средства	Character
	Price	Цена за единицу измерения лекарственного средства	Numeric

Таблица 5

Отношение “Наличие лекарств”

№ п/п	Имя атрибута	Содержание	Тип поля	Длина	Дес. Знаков
	No_Apt	Номер аптеки - идентифицирует аптеку	Numeric
	Kod	Код лекарственного средства	Numeric
	Amount	Остаток лекарственного средства в аптеке	Numeric
	Amount_N	Нормативное количество лекарственного средства	Numeric

Таблица 6

Отношение “Поставщик”

№ п/п	Имя атрибута	Содержание	Тип поля	Длина	Дес. знаков
	GNI	Код ГНИ поставщика	Numeric
	Salor	Наименование поставщика	Character

Таблица 7

Отношение “Лицензия поставщика”

№ п/п	Имя атрибута	Содержание	Тип поля	Длина	Дес. знаков
	Kod	Код лекарственного средства	Numeric
	GNI	Код ГНИ поставщика	Numeric
	Lic	№ лицензии поставщика для работы с данным лекарственным средством	Character

Таблица 8

Отношение “Запрос на поставку”

№ п/п	Имя атрибута	Содержание	Тип поля	Длина	Дес. знаков
	No_Apt	Номер аптеки - идентифицирует аптеку	Numeric
	Kod	Код лекарственного средства	Numeric
	GNI	Код ГНИ поставщика	Numeric
	DocNum	№ требования на поставку лекарственного средства	Numeric
	Volume	Объем поставки лекарственного средства	Numeric

Примечание. В таблицах 3..8 в графе “№ п/п” сохранена нумерация, принятая в таблице 1.

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

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

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

IV и V нормальные формы. Поскольку отсутствуют многозначные зависимости, можно утверждать, что все отношения соответствуют требованиям IV НФ. [1]

Анализ атрибутов показывает, что любые две проекции любого из перечисленных отношений будут содержать общий ключ - кандидат, следовательно, все эти отношения находятся и в V НФ, в соответствии с ее формулировкой в [2].

На этом построение концептуальной модели данных можно считать законченным.

Дата добавления: 2015-07-19