Теория проектирования баз данных

Ссылочная целостность

Ссылочной целостностью называют особый механизм, осуществляемый средствами СУБД или программистом, ответственный за поддержание непротиворечивых данных в связанных релятивными отношениями таблицах. Ссылочная целостность подразумевает, что в таблицах, имеющих релятивные связи, нет ссылок на несуществующие записи. Взгляните на рис. 1.3. Если мы удалим из списка студента Иванова И.И., и при этом не изменим таблицу со сданными экзаменами, ссылочная целостность будет нарушена, в таблице с экзаменами появится "мусор" - данные, на которые не ссылается ни одна запись из таблицы студентов. Ссылочная целостность будет нарушена.

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

1. Удаляется запись в родительской таблице, но не удаляются соответствующие связанные записи в дочерней таблице.
2. Изменяется запись в родительской таблице, но не изменяются соответствующие ключи в дочерней таблице.
3. Изменяется ключ в дочерней таблице, но не изменяется значение связанного поля родительской таблицы.

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

Нормализация базы данных

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

Процесс нормализации данных заключается в устранении избыточности данных в таблицах.

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

Первая нормальная форма ( 1НФ ) требует, чтобы каждое поле таблицы БД было неделимым (атомарным) и не содержало повторяющихся групп.

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

Под понятием повторяющиеся группы подразумевают поля, содержащие одинаковые по смыслу значения. Взгляните на рисунок:

Рис. 1.6 . Повторяющиеся группы

Верно, такую таблицу можно сделать, однако она нарушает правило первой нормальной формы. Поля "Студент 1", "Студент 2" и "Студент 3" содержат одинаковые по смыслу объекты, их требуется поместить в одно поле "Студент", как в рисунке 1.4. Ведь в группе не бывает по три студента, правда? Представляете, как будет выглядеть таблица, содержащая данные на тридцать студентов? Это тридцать одинаковых полей! В приведенном выше рисунке поля описывают студентов в формате "Фамилия И.О.". Однако если оператор будет вводить эти описания в формате "Фамилия Имя Отчество", то нарушается также правило неделимости. В этом случае каждое такое поле следует разбить на три отдельных поля, так как поиск может вестись не только по фамилии, но и по имени или по отчеству.

Вторая нормальная форма ( 2НФ ) требует, чтобы таблица удовлетворяла всем требованиям первой нормальной формы, и чтобы любое не ключевое поле однозначно идентифицировалось полным набором ключевых полей. Рассмотрим пример: некоторые студенты посещают спортивные платные секции, и ВУЗ взял на себя оплату этих секций. Взгляните на рисунок:

Рис. 1.7 . Нарушение второй нормальной формы

В чем здесь нарушение? Ключом этой таблицы служат поля "№ студента" - "Секция". Однако данная таблица также содержит отношение "Секция" - "Плата". Если мы удалим запись студента № 110, то потеряем данные о стоимости секции по скейтборду. А после этого мы не сможем ввести информацию об этой секции, пока в нее не запишется хотя бы один студент. Говорят, что такое отношение подвержено как аномалии удаления, так и аномалии вставки.

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

Рис. 1.8 . Правильная вторая нормальная форма

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

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

Рис. 1.9 . Нарушение третьей нормальной формы

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

Поле "Общая стоимость" в данном примере можно спокойно убрать из таблицы, ведь если потребуется вывести такие данные, нетрудно будет перемножить значения полей "Плата" и "Кол-во студентов", и создать для вывода вычисляемое поле.

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

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