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

Вид материала

Диплом

Содержание 6.2. Выявление и формирование связей один - ко многим

Подобный материал:

• Учебное пособие Допущено Министерством образования Российской Федерации в качестве, 2582.59kb.
• Д. В. Андреев Программирование микроконтроллеров mcs-51, 2064.3kb.
• И. В. Борискина, А. А. Плотников, А. В. Захаров проектирование современных оконных, 1699.55kb.
• «История нового времени», 4001.1kb.
• Учебное пособие. 3-е изд., испр и доп, 125.38kb.
• М. В. Ломоносова Хрестоматия по истории государства и права зарубежных стран, 11295.75kb.
• В. В. Крупица Личность Коллектив Стиль отношений (социально-психологический аспект), 4876.34kb.
• И. М. Синяева, В. М. Маслова, В. В. Синяев сфера, 5230.77kb.
• И. К. Корнеев информационная безопасность и защита информации учебное пособие, 7667.6kb.
• Курслекций допущено умо по образованию в области социальной работы в качестве учебного, 2178.14kb.

6.2. Выявление и формирование связей один - ко многим

Связи один - ко многим между двумя отношениями имеют место тогда, когда значения ключевых атрибутов одного отношения совпадают со значениями неключевых атрибутов другого отношения.

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

В качестве примера рассмотрим отношение ”A” (табл. 6.2.1) и отношение ”B” (табл. 6.2.2).


Т а б л и ц а 6.2.1

Категория продукта	Поставщик
Птица	Тульская птицефабрика
Рыба	Мурманск
Молочные	Росмолоко
Бакалея	Шебекино

Т а б л и ц а 6.2.2

Товар	Категория	Цена	Срок годности
Куры	птица	110	90
Индюки	птица	150	90
Семга	рыба	360	60
Окунь	рыба	120	60
Молоко	молочные	25	30
Ряженка	молочные	23	30
Кефир	молочные	23	30

Если принять то, что в отношении ”A” атрибут ”Категория продукта” является ключевым, то данное отношение связано с отношением ”B” связями один - ко многим. Действительно, в отношении ”B” имеется неключевой атрибут ”Категория”, значения которого повторяют значения атрибута ”Категория продукта”.

A₁ – атрибут с первичными ключами отношения А.

B_j – неключевой атрибут отношения В_.

Z (A _i) = (a_1i, … , a_2i, … , a_mi ) – множество значений атрибута А;

Z (B_j) = (b_1j, … , b_2j, … , b_qj) – множество значений атрибута B;

Неформально условие наличия между двумя отношениями связи один – ко многим звучит следующим образом. Если найдется такой ключевой атрибут в первом отношении и такой неключевой атрибут во втором отношении, что каждому значению неключевого атрибута второго отношения найдется равное ему значение ключевого атрибута первого отношения, то между двумя отношениями имеется связь один - ко многим.

Формализованное условие наличия связи один – ко многим выглядит следующим образом.

Пусть q ≥ m и для каждого b_pj найдется такое a_ri такое, что b_pj = a_ri;

p = 1,q; j = 1,k;

r = 1,m; i = 1,n.

Тогда между отношениями А и В имеется связь 1 : .

И наоборот.

Пусть m ≥ q и для каждого a_ri найдется такое b_pj такое, что a_ri = b_pj;

p = 1,q; j = 1,k;

r = 1,m; i = 1,n;

Тогда между отношениями А и В имеется связь 1 : .

Формальные условия наличия связи один - ко многим выглядят следующим образом.

( b_pj) (Z (B_j)  b_pj) (a_ri) (Z (A_i)  a_ri) (b_pj = a_ri),

p = 1,q; j = 1,k;

r = 1,m; i = 1,n;

q ≥ m.

A_i – ключевой атрибут.

(a_ri) (Z (A_i)  a_ri) (b_pj) (Z (B_j)  b_pj) (a_ri = b_pj),

p = 1,q; j = 1,k;

r = 1,m; i = 1,n;

m ≥ q.

B_j – ключевой атрибут.

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


CNT = 0

IF q >= m THEN

GOTO П1

ELSE

GOTO П2

END IF

П1: FOR j = 1 TO k

FOR p = 1 TO q

CNT = 0

FOR i = 1 TO n

FOR r = 1 TO m

IF b_pj = a_ri THEN

CNT = CNT + 1

JZ = j

IZ = i

END IF

NEXT r

NEXT i

NEXT p

NEXT j

IF CNT = q THEN

GOTO П3

ELSE

GOTO П4

END IF

П2: FOR i = 1 TO n

FOR r = 1 TO m

CNT = 0

FOR j = 1 TO k

FOR p = 1 TO q

IF a_ri = b_pj THEN

СNT = СNT +1

JZ = j

IZ = i

END IF

NEXT p

NEXT j

NEXT r

NEXT i

IF CNT =m THEN

GOTO П3

ELSE

GOTO П4

END IF

П3: PRINT (' Обнаружены связи 1 : , столбцы ',IZ,JZ)

EXIT

П4: PRINT (' Cвязей 1 :  не обнаружено ')

Поясним работу алгоритма.

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

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

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

Рассмотрим, что можно сделать по поводу выявления связей типа один - ко многим с помощью стандартных средств СУБД.

На рис. 6.2.1 представлена таблица ”Категории продуктов” в формате СУБД Microsoft Access, которая может претендовать на расположение в связи один - ко многим со стороны ”один”.




Рис. 6.2.1. Таблица, которая может претендовать на расположение в связи один - ко многим со стороны ”один”


На рис. 6.2.2 представлена таблица ”Товары” в формате СУБД Microsoft Access, которая может претендовать на расположение в связи один - ко многим со стороны ”многие”.




Рис. 6.2.2. Таблица, которая может претендовать на расположение в связи один - ко многим со стороны ”многие”


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

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

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

В данном случае в качестве возможного претендента на связь со стороны ”один” следует проверить поле ”Категория продукта” в таблице ”Категории продуктов”. Если это поле является ключевым, то этого достаточно для того, чтобы использовать его на роль претендента. Если это поле неключевое, то необходимо проверить уникальность его значений и, если его значения уникальны, назначить это поле ключевым полем. В рамках многих СУБД проще всего попытаться назначить поле ключевым, и, если средства СУБД не выдадут сообщения об ошибке, то дублирования значений поля не обнаружено. Так и оказалось в рассматриваемом случае – поле ”Категория продукта” в таблице ”Категории продуктов” успешно назначено ключевым (см. рис. 6.2.3).




Рис. 6.2.3. Назначения ключа


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

Далее в рассматриваемом случае в качестве возможного претендента на связь со стороны ”многие” следует проверить поле ”Категория ” в таблице ”Товары”. Во-первых, данное поле не должно быть первичным ключом в таблице ”Товары”, а во - вторых, все значения этого поля должны присутствовать в столбце ”Категория продукта” в таблице ”Категории продуктов”. Для проверки этого требования можно построить SQL запрос следующего вида:


SELECT [Категории продуктов].[Категория продукта], Товары.Категория

FROM [Категории продуктов] INNER JOIN Товары ON [Категории продуктов].[Категория продукта] = Товары.Категория;


В данном запросе используется, так называемое, внутреннее объединение (INNER JOIN). Эта объединение позволяет выводить только те данные из двух таблиц, в которых связанные поля совпадают. Связанные поля в данном случае - поля [Категории продуктов].[Категория продукта] и Товары.. Следует обратить внимание на то, что в квадратные скобки заключаются имена, содержащие пробел, квалифицированное имя поля - состоит из имени таблицы и имени поля.

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

Результат выполнения запроса приведен на рис. 6.2.4.




Рис. 6.2.4. Результат выполнения запроса


Из результатов выполнения запросов видно, что выведено 7 записей, а в таблице ”Товары” также 7 записей. Таким образом, поле “Категория” таблицы “Товары” можно использовать в качестве внешнего ключа.

На рис. 6.2.5 представлена схема данных с назначенной связью один - ко многим.




Рис. 6.2.5. Схема данных со связью один - ко многим


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




Рис. 6.2.6. Реакция системы на попытку ввода несуществующей категории


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