CC BY
43
Федеральный портал "Инженерное образование"
т электронный журнал
ОБРАЗОВАНИЕ
Инженерное образование Ассоциация технических университетов
#2 февраль 2007
Общие проблемы
инженерного
образования
Инженер в современной России
Наука в образовании: Электронное научное издание
CALS-технологии
Зарубежное образование
История технического прогресса
Учебные программы Будущий инженер Вне рубрик
English Library
Пресс-релизы
Библиотека
Конференции
Выставки
Форум
Доска объявлений
Архив
Переписка
Информация о проекте About project
Найти!
# Гос. регистрации 0420700025
ISSN 1994-0408 Ред. совет Специальности Рецензентам Авторам English Koi-8 Win
Найти выделенное
Преобразование заполненных реляционных таблиц ко второй нормальной форме #2 февраль 2007
УДК 681.3.07
А.В. Брешенков, В.В. Белоус
Отношение считается приведенным ко 2-й нормальной форме, если никакие неключевые атрибуты не являются функционально зависимыми лишь от части ключа. Таким образом, 2-я нормальная форма может быть нарушена только в том случае, если ключ составной [1].
В табл. 1 приведен пример отношения, которое не удовлетворяет 2-й нормальной форме.
Т а б л и ц а 1
Табельный № Код работы Начало работы Исполнитель
1121 27 1.1.6 Федоров
2231 15 15.1.6 Петров
3781 27 15.1.6 Котов
2231 14 17.2.6 Петров
1121 11 20.1.6 Федоров
3781 12 20.5.6 Котов
2231 33 1.1.7 Петров
Здесь в качестве составного ключа используются атрибуты "Табельный №" и "Код работы". Нетрудно заметить, что неключевой атрибут 'Исполнитель' зависит лишь от части ключа - от атрибута 'Табельный №'. Это приводит к тому, что значение атрибута "Исполнитель", как видно из табл. 1, дублируется. Это в свою очередь приводит к повышению вероятности ошибок ввода данных - если много раз вводится фамилия исполнителя, то легко ошибиться в написании или сопоставить ее не тому табельному номеру. Кроме того, если потребуется изменить имя исполнителя, то придется его менять во всех записях или использовать специальный параметрический запрос на обновление.
И, наконец, если исполнителю еще не выдано задание, то при таком представлении данных информация о сотруднике будет отсутствовать.
Чтобы привести данное отношение ко 2-й нормальной форме, его необходимо представить в виде двух таблиц. При этом 1-я таблица должна включать в себя все атрибуты, кроме последнего атрибута, а 2-я таблица должна включать в себя последний атрибут и атрибут "Табельный №".
Таким образом, 1-я таблица примет вид таблицы 2.
Т а б л и ц а 2
Табельный № Код работы Начало работы
1121 27 1.1.6
2231 15 15.1.6
3781 27 15.1.6
2231 14 17.2.6
1121 11 20.1.6
3781 12 20.5.6
2231 33 1.1.7
Вторая таблица примет вид таблицы 3.
Т а б л и ц а 3
Табельный № Исполнитель
1121 Федоров
2231 Петров
3781 Котов
2231 Петров
1121 Федоров
3781 Котов
2231 Петров
После исключения повторов таблица 3 примет вид таблицы 4.
Т а б л и ц а 4
Б и ГОР
База и Генератор Образовательных Ресурсов
Табельный № Исполнитель
1121 Федоров
2231 Петров
3781 Котов
Теперь табл. 4 и табл. 2 связаны между собой посредством атрибута "Табельный №".
Когда мощность отношения, как в примере, невелика, нетрудно выполнить рассмотренные мероприятия вручную. В противном случае это далеко не всегда возможно.
В связи с этим предлагается алгоритм автоматизированного приведения заполненных таблиц ко 2-й нормальной форме. Кратко сформулировать его можно следующим образом.
Для каждого атрибута, входящего в первичный ключ, выполняется поиск неключевых атрибутов, находящихся в полной функциональной зависимости. Для этого необходимо выявить ситуации, когда при равных значениях атрибутов, входящих в первичный ключ, равны значения неключевых атрибутов, находящихся в одноименных строках. Если такие атрибуты, входящие в первичный ключ найдутся, то исходная таблица разбивается на две таблицы. Для этого осуществляется операция проекции для исходной таблицы по всем столбцам, кроме функционально зависимого столбца.
Далее осуществляется операция проекции для исходной таблицы по столбцу с функционально зависимым атрибутом и столбцу, который является частью первичного ключа и для которого выявлена функциональная зависимость.
Таблица, в которую не включен функционально зависимый атрибут, повторно проверяется на соответствие 2-й нормальной форме, и все перечисленные манипуляции повторяются.
Для описания алгоритма используется общий вид отношения, представленный в табл. 2. _Т а б л и ц а 2_
A1 A2 Ai Aj Ak
a11 a12 a1i a1j a1k
a21 a22 a2i a2j a2k
... . . .
an1 an2 ani ani ank
... . . . .
am1 am2 ami amj amk
В формализованном виде алгоритм выглядит следующим образом. Выявление функциональной зависимости. FOR s = 3 то k C=0
FOR r = 1 то m
AR1 = ar1 ARS = ars
FOR r1 = 1 то m
IF NOT((ar1 1 = AR1) and (ar1 s = ARS)) THEN GOTO M
NEXT r1 C = C +1 IF C = r THEN sz = s
PRINT ("ЗАВИСИМЫЙ АТРИБУТ", sz) STOP END IF NEXT r M: NEXT s
PRINT ("ЗАВИСИМОГО АТРИБУТА НЕТ")
Поясним алгоритм.
Во внешнем цикле перебираются все столбцы (атрибуты) кроме столбцов, входящих в первичный ключ. Предполагается, что в ключ входят два первых атрибута. Переменная "С" используется для подсчета "успешных" проходов - проходов, которые не были завершены по команде "GOTO M".
В цикле по "г" выбираются очередное значение первого атрибута, входящего в первичный ключ "AR1 = ar j" и соответствующее ему значение неключевого атрибута из той же строки "ARS = ars" и s-го
столбца.
Для выбранной пары в цикле по "г1" перебираются все записи отношения. В данном цикле выполняется проверка равенства значения выбранного неключевого атрибута и текущего неключевого атрибута при равенстве выбранного атрибута, входящего в первичный ключ и текущего атрибута, входящего в первичный ключ. Если данное условие не выполняется, то осуществляется "аварийный" выход
из цикла и в качестве проверяемого атрибута выбирается следующий s-й столбец отношения.
Если в цикле по "г" не было "аварийных" выходов (C = г), то зависимый атрибут найден, номер столбца запоминается в переменную sz для использования ее на следующих шагах алгоритма нормализации.
Следует обратить внимание на то, что, несмотря на сходство работы алгоритма с двумерным массивом, при его реализации в виде программ вероятно данные не будут изначально представлены двумерным массивом. Таблица может быть представлена текстовым файлом, электронной таблицей или файлом БД. В связи с этим в алгоритме намеренно используются обозначения типа ars а не a(r,s).
В соответствии с предложенным алгоритмом на функциональную зависимость проверяется первый атрибут, входящий в первичный ключ. Для проверки на функциональную зависимость второго атрибута, входящего в первичный ключ необходимо выполнить такой же алгоритм. Только вместо ari нужно использовать ar2 а вместо arll - arl2 Это можно реализовать, расширив предыдущий алгоритм, но от этого он станет менее понятен.
При маловероятном стечении обстоятельств программные средства, реализованные в соответствии с приведенным алгоритмом могут ошибочно указать на функционально зависимый атрибут. Разработчик БД легко может выявить эту ошибку, визуально проанализировав две записи отношения, в которых предполагаются функционально зависимые атрибуты. Поэтому предлагаемый способ приведения отношения ко 2-й нормальной форме человеко-машинный (автоматизированный).
Если зависимый атрибут выявлен, то выполняются следующие шаги. Формирование отношения из зависимых атрибутов.
FOR i =1 TO m bil = ail bi2 = ai sz
NEXT i
REM Исключение дублирования
FOR i =1 TO m s = Concat(bii, bi2) FOR J =i+1 TO m si = Concat(bii, bi2) IF si = s THEN DLETE(Sj)
NEXT J
NEXT i
Поясним алгоритм.
В рамках 1-го цикла строится отношение "В" из 2-х атрибутов. При этом для каждой строки нового отношения в качестве ключевого (1-го) значения атрибута используется значение исходного отношения, которое соответствует части ключа, а в качестве значения второго атрибута используется соответствующее функционально зависимое значение исходного отношения.
В рамках второго цикла исключается дублирование записей в полученном отношении. Для этого во внешнем цикле посредством оператора Concat(b^ b^) формируется строка из значений текущей записи
отношения, а во внутреннем цикле эта строка сравнивается с оставшимися строками отношения. Если обнаруживается дублирование строк, то текущая строка внутреннего цикла посредством оператора DLETE (Sj) удаляется.
Следует обратить внимание на то, что в соответствии с предложенным алгоритмом предполагается, что функционально зависимым является первый атрибут, входящий в первичный ключ. Если функционально зависимым является второй атрибут, входящий в первичный ключ, то вместо переменной a^i нужно использовать переменную a^.
Упрощение исходного отношения.
FOR i = 1 TO m
C = 0
FOR J = 2 TO k
IF j <> sz THEN C = C + 1
di,c = aij END IF NEXT J
NEXT i
Поясним алгоритм.
В рамках внешнего цикла перебираются все записи исходного отношения и формируются записи нового отношения "D".
Во внутреннем цикле заполняются все столбцы нового отношения значениями столбцов исходного отношения. Причем 1-й столбец не включается, так как он в качестве ключевого атрибута задействован в отношении "B". Не включаются также в отношение "D" значения функционально зависимого столбца с номером "sz" так как он задействован в отношении "B".
Приведение отношений ко 2-й нормальной форме реализовано в отдельных инструментальных системах, ориентированных на создание БД, в частности в Microsoft Access. Для этого, вероятно, использован алгоритм, подобный алгоритму, описанному выше. Рассмотрим преобразование отношения, представленного на рис 1.
Ell J Работы ; т^йлчцо
ЛИ
"1121 2231 22Э1 2231 37В1 37В I
Табельный Na Код работы ¡Начало раБотьд Исполнитель
1 1 27
I 4 It
ЗЭ
II 27
20 Üt.200S Ol U1.2Q0S f 7 02 200& fS Ü1.20ÜG □ 1 G1.2007 2Û □ ! .20ÛÊ 15.0 t 2006
Федоров Федоров
П отрос*
Петров Ht* трое Котов Котое
Рис. 1. Ненормализованная таблица С помощью меню Microsoft Access Сервис/Анализ/Таблица необходимо запустить мастер для преобразования таблицы. Выполняя шаги мастера, разработчик в конечном итоге получит схему данных, представленную на рис. 2.
Рис. 2. Схема данных, полученная после нормализации
Как видно из комментариев, представленных в окне, пользователю предоставляется возможность изменения имен таблиц. Кроме того, пользователю предоставляется возможность самостоятельно сформировать подходящие по смыслу группы. Нередко такое предложение может скорее запутать, чем помочь.
Как видно из схемы данных, в "Таблица1" добавлено ключевое поле с именем "Уникальный код". Неочевидно, что это ключевое поле нужно. Правда, при желании впоследствии его можно удалить и сформировать новое ключевое поле.
Кроме того, в "Таблица1" появилось поле с именем "Подстановка Таблица2". Из связи, сформированной на схеме данных, можно догадаться, что это бывшее поле "Табельный №".
Рассмотрим содержимое сформированных таблиц. На рис 3 приведено содержимое "Таблица2".
Рис. 3. Содержимое таблицы "Таблица2" На этом рисунке то, что и ожидалось от результатов нормализации. Он практически совпадает с табл.
1.13.
На рис 4 приведено содержимое таблицы "Таблица1".
I aï Г аблица! : таблица
Код работы Начало работы Подстановка Таблицэ2 Код
ш □1.01 200В 1121. -Федоров \
15 15.01.2006 2231, Петров 2
27 15.01.2006 37S1, Котое Э
14 17.02.2006 2231, Петров 4
11 20.01.2006 1121. Федоров 5
11 20.01.2006 37S1, КрТбй 6
33 01 01 2007 2231. Петров 7
* (Счетчик)
Рис. 4. Содержимое таблицы "Таблица1" Некоторое недоумение вызывает поле "Подстановка Таблица2" Из его содержимого можно предположить, что это бывшее поле "Табельный №". Действительно, если открыть данную таблицу в режиме Конструктора (рис. 5), можно увидеть, что поле с именем "Табельный №" в "Таблица1" присутствует. Все дело в том, что в качестве его подписи использовано другое имя - "Подстановка Таблица2"
UJI Таблица! : таблица
Инд поло Тип даиныи
Кил Рввдгы ЧцСлО^йй
Начало работы Датд/орсия
> Табельный Ni1 Чнслосой
? Код Счетчик
Свойство полч
QCuLtne [ Подстановка 1
РоЫ-иЧЭ ПОЛЯ ДЛПЩ-IÎH; ченое-Формат "йЛй
Число Десятичных ÏI-T^KI» Дето
маек.» «ш«
Подгись Подстановка Т-аблнц-аЕ Значение пй умол'-tjHt-fro
Рис. 5. "Таблица1" в режиме Конструктора Для того, чтобы выяснить откуда такое необычное содержимое поля "Табельный №" (новое имя "Подстановка Таблица2") необходимо открыть вкладку "Подстановка", рис. 6.
1Ш Тпбпицл) ; твблицв
Имя ПОЛЯ Тип данных
Кпд рвЕэОТЫ Чис УК44Й
Начало p-söoTfi Д-эта/ере мч
I ТлГ.е льНЫЙ МР| ЧшспйнОЙ
Код Счетчик
Соойствд ПОЛЯ
Обшив Подстановка |
Тип зпгш-итл упр-пгмпгтмпя Пс?лс СО списком Ttm источника строк Тоблнща unit з-апрос
Источник; строг SELECT [Тайвльный IW] AS jiyг_, [Табвлы
ПипСиадпмь'никм СТалб^ц I
Чнспо столбцов Н Заглавия столбцов
Ширима стопбцов Осн^ОСЕсм^З^Зсм^г^сн
Число строк списка Ий И
Ширима сличив Э^ОЗЗСМ
Ограничиться СПИСКОМ Д1Э
Рис. 6. Вкладка "Подстановка" для Таблицы Как видно из рисунка, в свойстве "Число столбцов" задействовано 4-е столбца. В свойстве "Источник строк" задействован SQL-запрос, который выглядит следующим образом:
SELECT [Табельный №] AS xyz_ID_xyz, [Табельный №] & ', ' & [Исполнитель] AS xyz_DispExpr_xyz, [Табельный №], [Исполнитель] FROM Таблица2 ORDER BY [Табельный №];
Анализируя эту конструкцию, можно сделать вывод, что для поля с именем "Табельный №" в "Таблица1" при его заполнении предлагается список из 2-х полей ([Табельный №], [Исполнитель]) таблицы "Таблица2". В качестве содержимого в поле вводятся поля [Табельный №], [Исполнитель], разделенные символом ', ' и находящиеся в таблице "Таблица2", в которой данные отсортированы по полю [Табельный №] (ORDER BY [Табельный №]).
Проще говоря, при вводе данных в поле Таблицы1 используется поле со списком, который строится посредством команды SELECT
Выбор значений из предлагаемого списка проиллюстрирован на рис. 7. Справа от поля "Подстановка Таблица2" имеется маркер списка. После щелчка по этому маркеру сформируется список для выбора нужного значения.
|Ш Тийлицл) : тлблицл
Код работы ' Начало работы j Подстановка Таблицэ2 1 Код [
> 27 15 01.01 20ÛS 15 01 2QQ6 1 121 ; Федоров i и 1
jTaüi-Льиын Me 1 l'ltriOJlHiiTÖJlb j 2|
27 15.01 2006 1121 Федоров 3
\4 T7.D2.2ùae 2231 Пет (Зов Китов Л
11 20. Q1 2006 13731 5
1 1 20.0 t.2006 3781, Котов 6
33 □ 1 .Ol 20G7 2231, Петров 7
(Счетчиц
Рис. 7. Иллюстрация поля со списком Резюмируя сказанное выше относительно инструментальных средств Microsoft Access, ориентированных на приведение отношений ко 2-й нормальной форме, можно сделать вывод о том, что эти средства чаще всего обеспечивают реализацию нужных функций. Однако результаты преобразований не всегда очевидны и легко понимаемы, а модифицировать результаты преобразования может только квалифицированный пользователь Microsoft Access.
В частности, можно убрать "Подпись", изменить вкладку "Подстановка" в соответствии с рис. 8.
□II ТобмУца! : ТА&шца
И1Ч ПОЛЯ Тип J.lllIL E 1
Код роботы Числовой
i Начало pjjjin ы ГАСЯЛЬНЬ.!*-! JJÇ Длта/йренй
ЧИСЛОИС1Й
Код Сче-тчир
—
Свойства погач
Твбдцд kfiwi мпрос __
SELECT I Гвбсп&№1Й Ni], [ИСпотоГГсль] ("ROM т; 1_
г
Общи* Постановка |
Тип элемент* управления Попе со списком Тип истичи«:^ строк Источник Строк ПЙНсббдлисимбИЙ ^топЬг-ц ^«гпр Стороной
3*П9ВИЯ Столбцов Дл
Шицино ':1!>п6ц|:.п
"-ft КЛО СТрОК СПИСКА S
Ширина CriP54.Kd 5,093сп
CTpiHimntbift СПнСк'ОН [до
Рис. 8. Модифицированная вкладка "Подстановка" для Таблицы 1 Затем можно переписать оператор SELECT следующим образом: SELECT [Табельный №], [Исполнитель] FROM Таблица2 ORDER BY [Табельный №];
После этого Таблица1 примет более очевидный и удобный для дальнейшей модификации вид (рис. 9).
1Ш Таблица! : таблица
Код работы H а 4эл о работы [ Тральный N5 Код
► 27 qi 01 дюе ща ч 1
15 15.D1.2006 I'd бальный Ns 1 И С пол Интелу 2
27 15.01 .мое I III Фэдоров 3
и 17 02.2006 2231 Петрос 4
11 20.01.200S I37S1 Котов 5
11 20.01.2006 3781 6
33 01.01.2007 2231 7
+ (Счетчик) |
Рис. 9. Вид модифицированной таблицы "Таблица1" В связи с вышесказанным можно сделать вывод о том, что квалифицированный пользователь может использовать средства Microsoft Access для приведения таблиц ко 2-й нормальной форме. Однако, в этом случае необходимо самостоятельно выявить "подозрительные таблицы", а затем выполнить необходимые модификации.
Предложенные алгоритмы актуальны в случае таблиц большой степени и мощности, а также в случае, если данные не представлены или не могут быть непосредственно представлены в СУБД типа Microsoft Access.
Список литературы.
1. Дейт К., Дж. Введение в системы баз данных. 8-е изд.: Пер. с англ.- М.: Вильямс, 2005. - 1328 с.
2. Брешенков А.В. Неформальная постановка проблемы преобразования информации табличного вида в файлы баз данных. Сб. трудов АУ МВД России "Актуальные вопросы технологий в деятельности органов внутренних дел". - М.: 2004. - 20 с.
3. Брешенков А.В., Бараков Д.Д. Вопросы преобразования электронных таблиц в таблицы реляционных баз данных. Современные информационные технологии. Сб. трудов каф. ИУ-6, посвященный 175-летию МГТУ им. Н.Э. Баумана. - М.: Эликс +, 2004.-5 с.
Публикации с ключевыми словами: базы данных - реляционные таблицы - нормальная форма - функциональная зависимость -зависимые атрибуты
Публикации со словами: базы данных - реляционные таблицы - нормальная форма - функциональная зависимость - зависимые
атрибуты
См. также:
maiL.ru
■ Преобразование заполненных таблиц к третьей нормальной форме
■ Приведение заполненных реляционных таблиц к четвертой нормальной форме
■ Преобразование заполненных таблиц к первой нормальной форме
■ Книга. Информационные технологии бухгалтерского учета.
■ Базы данных. Проектирование, реализация и сопровождение
■ Основы пространственных баз данных
■ Системы баз данных: Проектирование, реализация и управление Все публикации на ту же тему>>
Написать комментарий >>
Журнал | Портал | Раздел Copyright © 2003 «Наука и образование. Инженерное образование» E-mail: [email protected] | тел.: +7 (495) 263-68-67
Вход для редакторов
