Другая проблема, возникающая при использовании имен в качестве уникальных идентификаторов, связана с их частым неправильным вводом. Клерк, принимающий информацию по телефону и сразу заносящий ее в базу данных, может даже не задуматься над тем, как правильно пищется имя и фамилия - Anne Ringer или Ann Ringer. Та же проблема возникает и с названиями компаний. Например, название одной телефонной компании может иметь несколько вариантов написания - AT&T, А.Т. and Т., Ма Bell и т.д. С точки зрения компьютера, это будут разные компании.

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

В таблицах authors и editors мы использовали для этих целей номера карточек социального страхования. В таблицах titles и publishers используются произвольные идентификационные номера. Чтобы показать, какие столбцы являются первичными ключами, мы подчеркиваем их (рис. 2.3).

Выбор первичных ключей является одним из основных шагов при проектировании базы данных. Несмотря на их важность, ранние версии SQL не поддерживали определение первичных ключей. ANSI-стандарт SQL 1992 года, принятый сегодня большинством производителей, поддерживает предложение PRIMARY KEY в операторе CREATE TABLE (подробнее об этом - в главе 3). Кроме того, производители разрабатывают свои собственные методы для оперирования этим важным понятием.

authors

auJd

aujname

au fname

address

phone

tides title id

name price pubdate

publishers РДЬ. id name address

editors

ed id

ed bame

ed fname

address

phone

Рис. 2.3. Определение первичных ключей

Отношение один-ко-многим

к этому моменту мы имеем готовые структуры четырех таблиц базы данных bookbiz: authors, titles, editors и publishers. Для каждой таблицы описаны атрибуты и определены первичные ключи.

Тем не менее мы пока не определили никаких зависимостей между данными. Пока остается непонятным, как, например, связаны между собой книги и конкретные издательства.

Связь между книгами и издательствами может описываться отношением один-ко-многим: каждая книга имеет единственного издателя, в то время как издатель может выпустить несколько книг. Отношение один-ко-многим часто записывается в виде 1:N.

Как реализовать это отношение? Первым порывом может быть желание добавить столбец, как показано на рис. 2.4.

Столбец titlejd будет являться внешним ключом в таблице publishers. Вы можете использовать его для ссылки на определенные строки в таблице titles и объединения информации о книгах и издателях. К сожалению, это решение уводит- вас в неправильном направлении. Вспомним моделируемое нами отношение - один издатель, много книг - и посмотрим, что произойдет при выходе новой книги. Вы добавите новую строку в таблицу titles с названием книги, ценой и т.д.

title id title price date

BU2075 You Can Combat Computer Stress! 2.99 6/30/85

authors

aujd

aujname

aujname

address

phone

publishers pub id name address titlejd

editors

ed id

edjname

edjname

address

phone

Рис. 2.4. Добавление внешнего ключа

Для каждо11 HOBOid строки таблицы titles вы должны добавить соответствующую строку в таблицу publishers. При этом в строке таблицы publishers будет повторена уже существующая там информация и добавлен один столбец с новой информацией {titlejd), отражающей появление новой книги в таблице titles.

pub id pub name address title id

0736 New Age Books 1 1st St Boston MA BU2075

Вспомните, что одной из целей процесса проектирования базы данных является контроль за избыточностью данных, так как избыточность данных увеличивает вероятность появления ощибок. Лучщим рещением является добавление внешнего ключа к таблице titles (рис. 2.5).

Теперь при выходе новой книги вы просто добавляете строку в таблицу titles (со столбцом pubJd) и ничего не изменяете в таблице publishers, пока компания не откроет новый филиал.

Title id title price

BU2075 You Can Combat Computer Stress! 2.99

date pub id T/Jo/SS 07 Гб

С учетом последних изменений мы получили следующую структуру;

таблица publishers содержит по одной строке для каждого издателя;

таблица titles имеет по одной строке на каждую книгу;

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

authors

au id

aujname

aujname

address

phone

titles title id name price

pubdate.,-pub Jd

editors

edjd

edjname

edjname

address

phone

Рис. 2.5. Изменение внешнего ключа

Вы можете использовать логическую связь между столбцами pubJd в таблицах titles и publishers для объединения этих таблиц. Другими словами, структура базы разрабатывалась в расчете на применение пользователями оператора объединения для выборки информации из таблиц titles и publishers с помощью одного запроса.

В таблице publishers столбец pubJd является первичным ключом. В таблице titles этот столбец выполняет роль внешнего ключа. Таким образом, реляционная модель требует, чтобы отношение один-ко-многим реализовывалось посредством пары первичный ключ-внешний ключ.

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

При рассмотрении логической взаимосвязи между информацией, содержащейся в первичном и внешнем ключе, может возникнуть несколько вопросов. Что произойдет со столбцом pubid в таблице titles, если в таблице publishers будет удалена или изменена строка, описывающая издателя? Можно ли ссылаться на описание книги, используя для этого идентификационный номер издателя, не существующего больше в базе данных? Такое действие не имеет под собой логического смысла и к тому же нарушает определение внешнего ключа, в соответствии с которым значения внешнего ключа должны совпадать со значениями первичного ключа, имеющегося где-то в базе данных.

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

если в таблице изменяется или удаляется идентификационный номер издателя, система должна автоматически выполнять модификации в таблице titles, т.е. либо изменять соответствующие значения в столбце pub id таблицы titles, либо удалять из нее строки с уже несуществующими в таблице значениями pubid;

если в базу данных добавляется информация по новой книге, система должна проверить корректность введенного значения в столбце pubjd (т.е. что оно присутствует в таблице publishers).

Пока просто возьмите это себе на заметку. В следующей главе мы расскажем, как можно обеспечить совместимость первичных и внешних ключей, введя офаничения в предложение REFERENCES оператора CREATE TABLE. Производители коммерческих систем баз данных также обеспечивают специальные методы для управления целостностью, например расширения SQL для процедурного кода, который может выполняться в базе данных (такой код обычно называется процедурами, или триггерами, которые мы рассмотрим в главе 10).

Отношение многий-ко-многим

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

Некоторые книги написаны несколькими авторами, к тому же некоторые авторы выпустили более одной книги. Другими словами, авторы и книги связаны отношением многий-ко-многим (которое часто записывается в виде N:N и иногда называется соединением (association)). В соответствии с реляционной теорией, соединения должны представляться отдельными таблицами, т.е. в базе данных bookbiz должна быть таблица для авторов, таблица для книг и таблица, описывающая связи между ними (рис. 2.6).

Таблица titleauthors описывает отношение типа многий-ко-многим между авторами и книгами. Это такая же базовая таблица, как titles и authors, но вместо объектов она описывает связи. Если пользователю базы данных bookbiz потребуется информация о том, кто написал какую книгу, он или она создаст запрос на объединение, в котором для связи между таблицами titles и authors будет использована таблица titleauthors.

Таблицы titleauthors и titles объединяются по столбцам titlejd, titleauthors и authors - по столбцам au id. Другими словами, titlejd в таблице titleauthors является внешним ключом, соответствующим первичному ключу titlejd в таблице titles, а auJd в таблице titleauthors является внешним ключом, соответствующим первичному ключу auJd в таблице authors. По общему принципу, сформулированному К. Дж. Дейтом, в реляционной модели участники соединения определяются внешними ключами, образующими таблицу, представляющую это соединение .