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

Для цели описания технологии создания БД и работы с ней будем  использовать следующий пример.

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

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

На этапе концептуального проектирования осуществляется анализ предметной области. В результате анализа выявлены следующие входные и выходные документы-источники данных для создания базы данных:

Входная информация

ТОВАРНО-ТРАНСПОРТНАЯ НАКЛАДНАЯ №____ Дата___________
Грузоотправитель:		ФГУП племзавод «Караваево»
Грузополучатель:
Адрес:
Код продукции	Наименование		Единица измерения	Цена	Количество	Сумма

Рисунок 1 - Внешний вид товарно-транспортной накладной

Выходная информация

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

КНИГА ПРОДАЖ
Дата	Грузополучатель	Наименование продукции	Количество	Стоимость	Сумма НДС	Всего
…	…	…	…	…	…	…
…	…	…	…	…	…	…
ИТОГО                                                                                -----             ----        -----

Рисунок 2 - внешний вид отчета Книга продаж

ОТЧЁТ ПО ПРОДУКЦИИ_______________
№	Грузополучатель	Дата отгрузки	Количество	Стоимость с учетом НДС
…	…	…	…	…
…	…	…	…	…
ИТОГО                                                                                                          --------

Рисунок 3 - внешний вид отчёта По выбранной продукции

ОТЧЁТ ЗА (дата)
№	Грузополучатель	Наименование продукции	Количество	Ед. изм.	Стоимость
…	…	…	…	…	…
…	…	…	…	…	…
ИТОГО                                                                                                            ----------

Рисунок 4 - внешний вид отчёта По реализации за определённую дату

Счёт фактура №___от «____» _____________
Грузополучатель				Грузоотправитель
Адрес				Адрес
Телефон				Телефон
Расчетный счет				Расчетный счет
ИНН				ИНН
		Коды								Коды
ОКОНХ				ОКОНХ
ОКПО				ОКПО
Код продукции	Наименование продукции		Ед. изм.		Кол-во	Цена	Сумма	Ставка НДС	Сумма НДС		Всего с учётом НДС
…	…		…		…	…	…	…	…		…
…	…		…		…	…	…	…	…		…
Всего к оплате                                                    --------                       ---------      ----

Рисунок 5 - внешний вид счёта–фактуры

Определение объектов предметной области и связей между ними

Этот этап разработки базы данных предполагает выполнение следующих действий:

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

Номер ТТН	Наименование продукции	Р/сч грузополучателя
Дата ТТН	№	ИНН грузополучателя
Грузоотправитель	Грузополучатель	ОКОНХ грузополучателя
Код грузополучателя	Дата ТТН	ОКПО грузополучателя
Грузополучатель	Количество	Грузоотправитель
Адрес грузополучателя	Стоимость с учетом НДС	Адрес грузоотправителя
Код продукции	Итого	Телефон грузоотправителя
Наименование продукции	Дата ТТН	Р/сч грузоотправителя
Единица измерения	№  п/п	ИНН грузоотправителя
Цена	Грузополучатель	ОКОНХ грузоотправителя
Количество	Наименование продукции	ОКПО грузоотправителя
Сумма	Количество	Код продукции
Дата ТТН	Единица измерения	Наименование продукции
Грузополучатель	Стоимость	Единица измерения
Наименование продукции	Итого	Количество
Количество	№ ТТН	Цена
Стоимость	Дата ТТН	Сумма
Ставка НДС	Грузополучатель	Ставка НДС
Всего	Адрес грузополучателя	Сумма НДС
Итого	Телефон грузополучателя	Всего с учетом НДС

2. анализ перечня данных, удаление повторяющихся полей, (36 показателей)

Номер ТТН	Стоимость
Дата ТТН	Итого
Грузоотправитель	Телефон грузополучателя
Код грузополучателя	Р/сч грузополучателя
Грузополучатель	ИНН грузополучателя
Адрес грузополучателя	ОКОНХ грузополучателя
Код продукции	ОКПО грузополучателя
Наименование продукции	Грузоотправитель
Единица измерения	Адрес
Цена	Телефон грузоотправителя
Количество	Р/сч грузоотправителя
Сумма	ИНН грузоотправителя
Стоимость	ОКОНХ грузоотправителя
Всего	ОКПО грузоотправителя
Итого	Сумма
Стоимость с учетом НДС	Сумма НДС
Итого	Всего с учетом НДС
№ п/п	Всего к оплате

3. анализ перечня данных, разделение перечня на данные, которые должны храниться в базе данных, и те, которые могут быть вычислены перед помещением их в итоговые документы Удаляем вычисляемые поля и номера по порядку. (их всегда можно получить в запросах, формах и отчетах, а в таблицах эта информация избыточна, кроме того может быть устаревшей) (23 показателя).

Номер ТТН

Дата ТТН

Грузоотправитель

Код грузополучателя

Грузополучатель

Адрес грузополучателя

Код продукции

Наименование продукции

Единица измерения

Цена

Количество

Ставка НДС

Телефон грузополучателя

Р/сч грузополучателя

ИНН грузополучателя

ОКОНХ грузополучателя

ОКПО грузополучателя

Адрес грузоотправителя

Телефон грузоотправителя

Р/сч грузоотправителя

ИНН грузоотправителя

ОКОНХ грузоотправителя

ОКПО грузоотправителя

На основании анализа документов выделяются информационные объекты. Для каждого объекта определяется ключевой реквизит. Ключевой реквизит однозначно идентифицирует экземпляры объекта. Например, реквизит "Номер зачетной книжки" однозначно идентифицирует студента.

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

Из описания предметной области Реализация следует, что в ней можно выделить четыре сущности – грузоотправитель, грузополучатель, продукция, накладная. Между ними в действительности существуют три связи:

1) грузоотправитель – имеет – продукцию;

2) продукция – отгружается  – накладной;

3) накладная – выписывается – грузополучателю.

О связях между сущностями известно, что:

§ грузоотправитель имеет несколько разной продукции. Продукция принадлежит одному грузоотправителю;

§ продукция отгружается по одной накладной. Накладная содержит несколько наименований продукции. (Один экземпляр сущности Накладная имеет много (m) связей с экземплярами сущности Продукция, так как по одной накладной может быть поставлено несколько наименований продукции. Один экземпляр сущности Продукция имеет много (n) связей с экземплярами Накладная, так как товар поставляется регулярно)

§ накладная выписывается нескольким грузополучателям. Грузополучатель получает одну накладную. (Один экземпляр сущности Накладная имеет одну (1) связь с экземплярами сущности Грузополучатель так как одна накладная выписывается одним грузополучателем. Один экземпляр сущности Грузополучатель имеет много (n) связей с экземплярами сущности Накладная, так как Накладные от Грузополучателя поступают регулярно)

На основании описания определяются тип этих связей – типа 1:М.

Дополнительная информация о связях:                               

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

· каждая продукция отражается в накладной. Каждая накладная отражает продукцию.

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

Исходя из описания, устанавливается класс принадлежности (КП) сущностей – обязательный.

Установленной информации о сущностях, их связях, типах связей, КП сущностей и знания их графического представления на ER-модели достаточно, чтобы изобразить ER-модель предметной области РЕАЛИЗАЦИЯ. Она выглядит, как на рис. 6.

Рисунок 6 - ER-модель предметной области РЕАЛИЗАЦИЯ

Исходя из описания предметной области РЕАЛИЗАЦИЯ, для каждой сущности определяется набор атрибутов.

Рисунок 7 – Установка набора атрибутов сущностей предметной области РЕАЛИЗАЦИЯ

Так начнем с № ТТН данный атрибут относится к сущности НАКЛАДНАЯ т.к. от № ТТН может быть определено какая продукция и кому отгружена т.е проведем связь между № ТТН и сущностью НАКЛАДНАЯ.

Теперь выделите линии, которые относятся к одной сущности одним цветом, к другой – другим и т.д.

Наборы атрибутов и их имена представлены на рис. 8.

Грузоотправитель	Грузополучатель
Наименование грузоотправителя	Код грузополучателя
Адрес грузоотправителя	Наименование грузополучателя
Телефон грузоотправителя	Адрес грузополучателя
Расчетный счет грузоотправителя	Телефон грузополучателя
ИНН грузоотправителя	Расчетный счет грузополучателя
ОКОНХ грузоотправителя	ИНН грузополучателя
ОКПО грузоотправителя	ОКОНХ грузополучателя
	ОКПО грузополучателя
Продукция	Накладная
Код продукции	Номер ТТН
Наименование продукции	Дата ТТН
Единица измерения	Код грузополучателя
Цена	Код продукции
	Количество

Рисунок 8 - Наборы атрибутов сущностей предметной области РЕАЛИЗАЦИЯ

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

Сущность Грузоотправитель рассматривать не будем, так как у нас всего один грузоотправитель. Рассмотрим три сущности.

Для этого заполним таблицы всевозможными значениями атрибутов.

ПРОДУКЦИЯ

Код продукции	Наименование продукции	Ед.изм.	Цена	Ставка НДС
43101	мясо (кат 1)	ц	200	10
43102	мясо (кат 2)	ц	150	10
43103	рожь	ц	200	10
43207	язык	кг	420	20

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

Аналогично код грузополучателя в сущности грузополучатель.

Грузополучатель

Код	Грузополучатель	Адрес	Телефон	Р/сч	ИНН	ОКОНХ	ОКПО
1	ООО «Звезда"	п. Красное	66-98-52	40702810734010000000	4427001155	21250	65267151
10	ООО «Звезда"	г. Кострома	66-85-92	40502810200004000000	4414000309	21210	486913
11	ОАО Зооветснаб	п. Красное, пер. Пушкино, 39а	54-85-87	40702810000000000000	4414000203	21220	4682858

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

Накладная

Номер ТТН	Дата ТТН	Грузоотправитель	Код грузополучателя	Код продукции	Кол-во
1	25.01.2013	Караваево	1	43201	45
1	25.01.2013	Караваево	1	43202	45
2	25.01.2013	Караваево	10	43104	100
2	25.01.2013	Караваево	10	43202	45
3	26.01.2013	Караваево	8	43201	20

Убираем повторяющуюся запись Караваево

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

Накладная

Номер ТТН	Дата ТТН	Код грузополучателя	Код продукции	Количество
1	25.01.2013	1	43201	45
1	25.01.2013	1	43202	45
2	25.01.2013	10	43104	100
2	25.01.2013	10	43202	45
3	26.01.2013	8	43201	20

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

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

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

Накладная	Товарный раздел
Номер ТТН	Номер ТТН
Дата ТТН	Код продукции
Код грузополучателя	Количество

Таким образом, на основе анализа сущности НАКЛАДНАЯ выделены два информационных объекта — Накладная и ТОВАРНЫЙ РАЗДЕЛ.

ER-модель (см. рис. 6), дополненная наборами атрибутов сущностей исключив сущность грузоотправитель (см. рис. 8), – это концептуальная модель предметной области РЕАЛИЗАЦИЯ.

На этапе логического проектирования выбирается СУБД для создания базы данных. Информационно-логическая модель предметной области отображается в логическую модель, основанную на структурных единицах той базы, которая создается выбранной СУБД. Так как в нашем примере выбирается реляционная СУБД Access, то каждый информационный объект следует представить определенной таблицей и установить связи между таблицами.

Рисунок 9 – Реляционная модель базы данных

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

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

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

Отношение находится в третьей нормальной форме (3НФ), если оно удовлетворяет требованиям 2НФ и при этом неключевые поля зависят от ключа нетранзитивно. Транзитивной называется такая зависимость, при которой какое-либо неключевое поле зависит от другого неключевого поля, а то, в свою очередь, зависит от ключа. Таблицы спроектированной базы данных отвечают требованиям 3НФ.

Затем следует этап физического проектирования. На этом этапе база данных создается на внешних носителях информации.