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

2008 г.

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

Умаров Амантур Амангельдыевич, администратор баз данных,
фирма АББА (Республика Казахстан, г. Туркестан)

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

Введение.

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

Базы данных (БД) составляют в настоящее время основу компьютерного обеспечения информационных процессов, входящих практически во все сферы человеческой деятельности. Тематика СУБД поистине безгранична и многогранна.

Хотя многие считают, что реляционные СУБД, являясь наиболее распространенным современным аппаратом построения информационных систем, не представляют уже интереса в научном отношении, остается еще много не решенных или частично решенных проблем. Об этом свидетельствует поток статей, посвященных тематике чисто реляционных систем, а также активная деятельность компаний-производителей коммерческих реляционных систем, стремящихся улучшать свои продукты и придавать им новые качества. Продолжающая работа исследователей затрагивают вопросы оптимизации запросов, новых алгоритмов выполнения реляционных отношений, оптимизации структур данных и другие аспекты, непосредственно определяющие эффективность СУБД [1]

В предлагаемой статье сделана попытка решения вопроса оптимизации структур данных. Понятие «оптимизация структуры данных» обозначает спроектировать такую систему базы данных, структура данных которой должна быть предельно простой и понятной. А именно этот критерий является одним из самых важных критериев разработки коммерческих приложений (банковских систем, Интернет - магазинов, систем электронных платежей, и т.д.), поскольку позволяет программе занимать совсем малую память и выполнять запросы быстрее.

Постановка задачи.

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

Какие товары продает фирма, какова их цена, описание, и т.д.
По выбранным товарам оформляются заказы. Кто (покупатели), когда и совершили заказ и какова общая сумма заказа?

Таким образом, схему организации фирмы можно представить в виде двух отношений:

Рис 1. Начальная схема отношений

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

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

Как нам известно, в теории реляционных баз данных существует пять нормальных форм [2]:

первая нормальная форма (1 NF);
вторая нормальная форма (2 NF);
третья нормальная форма (3 NF);
четвертая нормальная форма (4 NF);
пятая нормальная форма (5 NF).

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

Процесс проектирования.

1-ый шаг: 1 NF → 2 NF

Из схемы организации работы фирмы на рис. 1 определим схему отношений:

ТОВАРЫ-ЗАКАЗЫ
ТОВАРЫ {товарНом, товарНазв, товарОпис, товарРис, товарЦена, колНаСкладе, типНом, типНазв}
ЗАКАЗЫ {заказНом, заказДата, ПокупНом, ФИОПокуп, ЭлпочтаПокуп, адресПокуп, товарНом, количТовЗаказа, видДост, ценаДост, налогНДС, общСумма, заказСост}

В атрибутах «типНом» и «типНазв» содержится информация о типах товаров, например, название типа — Процессоры (типНом = 1), к которому относятся конкретные товары - Intel 386, Intel 486, AMD, и т.д. Атрибут «колНаСкладе» содержит общее количество товаров с данным номером на складе.

В одном заказе может заказываться несколько товаров с разными номерами. Атрибут «количТовЗаказа» содержит количество товаров данного типа, заказанных в данном заказе.

В атрибутах «ПокупНом», «ФИОПокуп», «ЭлпочтаПокуп», «адресПокуп» содержится информация о покупателе — номер, ФИО, Электронная почта и адрес.

В атрибутах «видДост», «ценаДост» хранится информация о доставке — номер, вид и цена доставки. Атрибут «заказСост» содержит состояние заказа, который может быть иметь значение «Сделка совершена» либо «Сделка выполнена неполно».

Для вычисления общей суммы заказа применяем формулу

общСумма = ТоварЦена*количТов + налогНДС + ценаДост.

На 1-м шаге проектирования форма 1NF приводится к виду 2NF. Для этого проверяем следующее определение:

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

Рассмотрим отношение ЗАКАЗЫ. В этом отношении существует неполная функциональная зависимость от первичного ключа, поскольку все атрибуты, кроме товарНом и количТовЗаказа, зависят от ЗаказНом, а первичным ключом является заказНом, товарНом. И поэтому мы приводим отношение ко второй нормальной форме то есть, производим декомпозицию отношения ЗАКАЗЫ в два отношения ТИП ЗАКАЗА и ЗАКАЗЫ:

ТИП ЗАКАЗА {заказНом, товарНом, количТовЗаказа}

Первичный ключ:
заказНом, товарНом
Функциональные зависимости:
заказНом, товарНом → количТовЗаказа

ЗАКАЗЫ {заказНом, заказДата, ФИОПокуп, ЭлпочтаПокуп, адресПокуп, видДост, ценаДост, налогНДС, общСумма, заказСост}

Первичный ключ:
заказНом
Функциональные зависимости:
заказНом → заказДата
заказНом → ФИОПокуп
заказНом → ЭлпочтаПокуп
заказНом → адресПокуп
заказНом → видДост
заказНом → ценаДост
заказНом → налогНДС
заказНом → общСумма
заказНом → заказСост

Рассмотрим отношение ТОВАРЫ. В этом отношении первичным ключом является атрибут «товарНом», и оно уже находится во второй нормальной форме.

ТОВАРЫ {товарНом, типНом, товарНазв, товарОпис, товарЦена, товарРис, типНазв, колНаскладе}

Первичный ключ:
товарНом
Функциональные зависимости:
товарНом → типНом
товарНазв
товарНом → товарОпис
товарНом → товарЦена
товарНом → товарРис
товарНом → колНаСкладе
типНом → типНазв

Рис. 2. Схема отношений в виде 1NF

2-ой шаг: 2 NF → 3 NF

На 2-м шаге проектирования форма 2NF приводится к виду 3NF. Для этого проверяется следующее определение:

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

При проверке на транзитивность определяется существования зависимостей R.X → R.Z и R.Z → R.Y и отсутствие зависимости R.Z → R.X. То есть при отсутствии последнего требования мы имели бы «неинтересные транзитивные зависимости» в любом отношении, обладающем несколькими ключами.

Имея в виду выше рассмотренные ФЗ отношения ТОВАРЫ, где атрибут типНазв напрямую не зависит от первичного ключа товарНом, а непосредственно зависит от его составного атрибута типНом, мы заключаем, что здесь имеется транзитивная ФЗ товарНом → типНом → типНазв. Другими словами, название типа товара на самом деле является характеристикой не товара, а типа товара, к которому он относится. В результате сказанного, мы приводим отношение Товары к третьей нормальной форме.

Теперь можно произвести декомпозицию отношения ТОВАРЫ в два отношения ТИП ТОВАРА и ТОВАРЫ:

ТИП ТОВАРА {типНом, типНазв}

Первичный ключ:
типНом
Функциональные зависимости:
типНом → типНазв
ТОВАРЫ {товарНом, тип_Ном, товарНазв, товарОпис, товарЦена, товарРис, колНаСкладе}

Первичный ключ:
товарНом
Функциональные зависимости:
товарНом → типНом
товарНазв
товарНом → товарОпис
товарНом → товарЦена
товарНом → товарРис
товарНом → колНаСкладе

В результате из одного отношения (таблицы) ТОВАРЫ получается два отношения (две таблицы) ТИП ТОВАРА и ТОВАРЫ.

Отношение ТОВАРЫ разделяется на отношения — ТИП ТОВАРА и ТОВАРЫ, где ТИП ТОВАРА — основная таблица, а ТОВАРЫ — подчиненная таблица.

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

Рис 3. Схема отношений в виде 3NF

Техническое решение

Приложение базы данных было выполнена в среде Access 2003. Определив все взаимосвязи между отношениями, получим окончательную схему данных (структуру данных):

Рис 4. Схема данных БД

Из схемы видно, что между отношениями существуют связи: