"Информатика (базовый и углублённый уровни) (в 2 частях)", Поляков К.Ю., Еремин Е.А.
Многотабличная база данных хранит сведения об объектах разных типов в отдельных, связанных между собой таблицах. Такое устройство помогает не дублировать данные и менять их в одном месте, а не во множестве записей. Чтобы таблицы могли «общаться», используют первичные и внешние ключи. Понимание ключей и связей позволяет правильно проектировать базу и составлять запросы, которые собирают нужные сведения из нескольких таблиц.
Текст подготовлен нейронной сетью по теме параграфа и не заменяет учебник — важные факты и формулировки сверяйте с ним.
Многотабличная база данных — база, в которой сведения хранятся в нескольких связанных таблицах. Таблицы разделяют по типам объектов: например, отдельно хранят данные о классах и учениках. Это помогает не повторять одни и те же сведения в каждой записи и упрощает их изменение.
Первичный ключ — поле или набор полей, однозначно определяющих запись таблицы. Значения ключа должны быть уникальными, а пустое значение недопустимо: иначе запись нельзя будет надёжно отличить и найти. Ключ нужен, чтобы ссылки из других таблиц указывали на конкретную запись.
Внешний ключ — поле таблицы, в котором хранится значение первичного ключа другой таблицы. Например, код класса в записи ученика показывает, к какой записи таблицы «Классы» относится этот ученик. Такой указатель связывает таблицы, не копируя в таблицу учеников все сведения о классе.
Связь таблиц — соответствие записей разных таблиц, задаваемое общими ключевыми полями. При связи «один ко многим» одной записи первой таблицы соответствуют несколько записей второй. Так один класс может быть связан со многими учениками, а каждый ученик относится к одному классу.
При проектировании базы сведения об объектах разных типов разносят по отдельным таблицам. Если записывать название класса, например, в каждой строке с учеником, оно будет многократно повторяться и при переименовании класса придётся исправлять много записей. Отдельная таблица классов позволяет хранить название один раз, а в таблице учеников оставить только ссылку на нужный класс. Так меньше риск, что в разных строках появятся разные варианты одного и того же значения.
Для каждой таблицы выбирают первичный ключ, чтобы отличать одну запись от всех остальных. Фамилия или название не всегда подходит: у двух учеников могут совпасть фамилии, а название объекта может измениться. Поэтому часто используют отдельный код, например номер класса или ученика. Даже если остальные данные в двух записях одинаковы, разные ключи позволяют программе обращаться к каждой из них отдельно.
Чтобы связать таблицы, в одной из них создают поле внешнего ключа. Его значение берут из первичного ключа таблицы, на запись которой нужно сослаться: например, код класса ученика должен соответствовать коду в таблице классов. Благодаря этому связь можно восстановить без копирования названия класса в каждую запись. При этом внешний ключ может встречаться много раз, если на одну запись ссылаются несколько записей.
Связь «один ко многим» возникает, когда одной записи первой таблицы соответствуют несколько записей второй. Например, в таблице «Классы» строка с кодом 11А может соответствовать многим строкам в таблице «Ученики», где указан тот же код. Каждый ученик при этом относится к одному классу, но у класса может быть много учеников. Такая организация естественно описывает ситуации, в которых один объект объединяет множество других.
Запрос к нескольким таблицам сопоставляет записи по связанным полям и выводит выбранные сведения вместе. Например, чтобы получить фамилию ученика и название его класса, запрос соединяет запись ученика с записью класса по совпадающим кодам. Условие связи обязательно: без него программа не узнает, какая запись одной таблицы соответствует какой записи другой. Тогда она может объединить каждую строку первой таблицы со всеми строками второй, создав множество лишних сочетаний.
| Элемент | Где находится | Назначение | Пример |
|---|---|---|---|
| Первичный ключ | В таблице, запись которой идентифицируется | Однозначно отличает запись; не повторяется и не бывает пустым | «Код класса» в таблице «Классы» |
| Внешний ключ | В таблице, которая ссылается на другую | Указывает на запись по значению её первичного ключа | «Код класса» в таблице «Ученики» |
| Связь «один ко многим» | Между двумя таблицами | Одной записи первой таблицы соответствуют несколько записей второй | Один класс — много учеников |
| Связь «многие ко многим» | Между двумя таблицами, обычно через дополнительную | Нескольким записям каждой таблицы могут соответствовать записи другой | Книга — автор: у книги несколько авторов, автор может написать несколько книг |
Сначала определите, какая таблица описывает каждый тип объектов, затем найдите поля-ключи. Сравните назначение и допустимую повторяемость значений: первичный ключ идентифицирует запись, а внешний ссылается на неё. Для связи «многие ко многим» ищите дополнительную таблицу, в которой хранятся ключи обеих сторон.
Условие. В таблице «Классы» есть поля «Код класса» и «Название», а в таблице «Ученики» — «Фамилия» и «Код класса». Нужно определить первичный и внешний ключи. Проверяем не название поля само по себе, а его роль и значения. Код класса должен отличать классы друг от друга, а код в записи ученика — указывать на один из них.
Ответ. «Код класса» в таблице «Классы» — первичный ключ. «Код класса» в таблице «Ученики» — внешний ключ; он может повторяться, поскольку в одном классе учатся несколько учеников.
Условие. У книги может быть несколько авторов, и каждый автор мог написать несколько книг. Одного внешнего ключа в таблице книг недостаточно: он позволил бы указать только одного автора на одну книгу. Нужно сохранить все соответствия, не помещая списки кодов в одну ячейку.
Ответ. Связь «многие ко многим» реализуется через дополнительную таблицу с ключами книг и авторов. Одна книга будет представлена несколькими строками с разными авторами, а автор — несколькими строками с разными книгами.
Условие. Даны таблицы «Ученики» (код ученика, фамилия) и «Оценки» (код оценки, код ученика, предмет, балл). Нужно вывести фамилию, предмет и балл только для оценок не ниже 4. Чтобы фамилия относилась к правильной оценке, строки следует соединить по коду ученика.
Ответ. Запрос должен соединять таблицы по коду ученика, отбирать строки с баллом не ниже 4 и выводить три указанных поля. В SQL его условие отбора можно записать как WHERE Оценки.балл >= 4, а соединение — как ON Ученики.код_ученика = Оценки.код_ученика.
Не следует считать внешний ключ первичным ключом связанной таблицы. Эти поля могут иметь одинаковые названия, но внешний ключ часто повторяется, поскольку на одну запись могут ссылаться многие другие. Ошибку допускают, принимая название поля за признак его роли. Проверьте, уникальны ли значения в этой таблице и на какую запись они указывают.
Похожие названия полей сами по себе не доказывают, что таблицы связаны. Два поля могут называться «Код», но содержать значения для разных типов объектов. Ошибку легко допустить, если ориентироваться только на подписи столбцов. Сверьте смысл значений и убедитесь, что значения внешнего ключа действительно соответствуют первичным ключам другой таблицы.
Если забыть условие соединения в запросе, записи разных таблиц будут сочетаться произвольно. Это происходит потому, что системе не задано правило, по которому нужно подобрать соответствующие строки. В результате строк может стать намного больше, а данные окажутся приписаны не тем объектам. Проверьте, что в запросе есть равенство связанных ключевых полей.
Хранить связь «многие ко многим» одним полем со списком значений неудобно и ненадёжно. Такой список сложнее искать, изменять и проверять, а отдельные значения труднее связать с записями другой таблицы. Так поступают, пытаясь уместить все связи в одну строку. Проверьте, не соответствует ли ячейке несколько значений; если да, вынесите пары ключей в дополнительную таблицу.
Для работы с несколькими таблицами нужно знать устройство обычной таблицы: поля, записи и типы данных. Представление о ключе помогает отличать записи и связывать их без копирования полных сведений. Эти знания применяются при проектировании баз данных, когда решают, какие объекты хранить отдельно и как их связать. Затем связи используют в запросах, чтобы объединять таблицы и фильтровать полученные результаты.
Зачем разделять сведения по нескольким таблицам?
Так данные разных типов хранятся отдельно, а повторяющиеся сведения не приходится записывать заново. Если информация изменится, её достаточно исправить в одном месте, что уменьшает вероятность противоречий.
Может ли первичный ключ повторяться?
Нет, значение первичного ключа должно однозначно определять запись, поэтому оно не повторяется. Первичный ключ также не может быть пустым: иначе запись нельзя надёжно найти по ключу.
Чем внешний ключ отличается от первичного?
Первичный ключ идентифицирует запись в своей таблице, а внешний ключ указывает на запись в другой таблице. Значения внешнего ключа могут повторяться, если несколько записей ссылаются на одну и ту же запись.
Как понять, что связь между таблицами имеет тип «один ко многим»?
Одной записи первой таблицы соответствует несколько записей второй, но каждая запись второй связана с одной записью первой. Например, одному классу могут соответствовать многие ученики.
Почему запрос без условия соединения таблиц может выдать неверный результат?
Без условия соединения программе не указано, какие записи таблиц соответствуют друг другу. Она может сочетать каждую запись одной таблицы с записями другой, из-за чего результат окажется избыточным и неверным.
§ 21. Работа с многотабличной базой данных
