Вопросы и ответы с собеседований по SQL

Базы данных могут содержать таблицы, которые связаны между собой различными связями. Связь (relationship) представляет ассоциацию между сущностями разных типов. При выделении связи выделяют главную или родительскую таблицу (primary key table / master table) и зависимую, дочернюю таблицу (foreign key table / child table). Дочерняя таблица зависит от родительской. Для организации связи используются внешние ключи. Внешний ключ представляет один или несколько столбцов из одной таблицы, который одновременно является потенциальным ключом из другой таблицы. Внешний ключ необязательно должен соответствовать первичному ключу из главной таблицы. Хотя, как правило, внешний ключ из зависимой таблицы указывает на первичный ключ из главной таблицы. Связи между таблицами бывают следующих типов: - Один к одному (One to one) - Один к многим (One to many) - Многие ко многим (Many to many)

Данный тип связей встречается не часто. В этом случае объекту одной сущности можно сопоставить только один объект другой сущности. Например, на некоторых сайтах пользователь может иметь только один блог. То есть возникает отношение один пользователь - один блог. Нередко этот тип связей предполагает разбиение одной большой таблицы на несколько маленьких. Основная родительская таблица в этом случае продолжает содержать часто используемые данные, а дочерняя зависимая таблица обычно хранит данные, которые используются реже. В этом отношении первичный ключ зависимой таблицы в то же время является внешним ключом, который ссылается на первичный ключ из главной таблицы. Например, таблица Users представляет пользователей и имеет следующие столбцы: - UserId (идентификатор, первичный ключ) - Name (имя пользователя) И таблица Blogs представляет блоги пользователей и имеет следующие столбцы: - BlogId (идентификатор, первичный и внешний ключ) - Name (название блога) В этом случае столбец BlogId будет хранить значение из столбца UserId из таблицы пользователей. То есть столбец BlogId будет выступать одновременно первичным и внешним ключом.

Это наиболее часто встречаемый тип связей. В этом типе связей несколько строк из дочерний таблицы зависят от одной строки в родительской таблице. Например, в одном блоге может быть несколько статей. В этом случае таблица блогов является родительской, а таблица статей - дочерней. То есть один блог - много статей. Или другой пример, в футбольной команде может играть несколько футболистов. И в то же время один футболист одновременно может играть только в одной команде. То есть одна команда - много футболистов. К примеру, пусть будет таблица Articles, которая представляет статьи блога и которая имеет следующие столбцы: - ArticleId (идентификатор, первичный ключ) - BlogId (внешний ключ) - Title (название статьи) - Text (текст статьи) В этом случае столбец BlogId из таблицы статей будет хранить значение из столбца BlogId из таблицы блогов.

При этом типе связей одна строка из таблицы А может быть связана с множеством строк из таблицы В. В свою очередь одна строка из таблицы В может быть связана с множеством строк из таблицы А. Типичный пример - студенты и курсы: один студент может посещать несколько курсов, и соответственно на один курс могут записаться несколько студентов. Другой пример - статьи и теги: для одной статьи можно определить несколько тегов, а один тег может быть определен для нескольких статей. Но на уровне базы данных мы не можем установить прямую связь многие ко многим между двумя таблицами. Это делается посредством вспомогательной промежуточной таблицы. Иногда данные из этой промежуточной таблицы представляют отдельную сущность. Например, в случае со статьями и тегами пусть будет таблица Tags, которая имеет два столбца: - TagId (идентификатор, первичный ключ) - Text (текст тега) Также пусть будет промежуточная таблица ArticleTags со следующими полями: - TagId (идентификатор, первичный и внешний ключ) - ArticleIdId (идентификатор, первичный и внешний ключ) Технически мы получим две связи один-ко-многим. Столбец TagId из таблицы ArticleTags будет ссылаться на столбец TagId из таблицы Tags. А столбец ArticleId из таблицы ArticleTags будет ссылаться на столбец ArticleId из таблицы Articles. То есть столбцы TagId и ArticleId в таблице ArticleTags представляют составной первичный ключ и одновременно являются внешними ключами для связи с таблицами Articles и Tags.

При изменении первичных и внешних ключей следует соблюдать такой аспект как ссылочная целостность данных (referential integrity). Ее основная идея состоит в том, чтобы две таблице в базе данных, которые хранят одни и те же данные, поддерживали их согласованность. Целостность данных представляет правильно выстроенные отношения между таблицами с корректной установкой ссылок между ними. В каких случаях целостность данных может нарушаться: - Аномалия удаления (deletion anomaly). Возникает при удалении строки из главной таблицы. В этом случае внешний ключ из зависимой таблицы продолжает ссылаться на удаленную строку из главной таблицы - Аномалия вставки (insertion anomaly). Возникает при вставке строки в зависимую таблицу. В этом случае внешний ключ из зависимой таблицы не соответствует первичному ключу ни одной из строк из главной таблицы. - Аномалии обновления (update anomaly). При подобной аномалии несколько строк одной таблицы могут содержать данные, которые принадлежат одному и тому же объекту. При изменении данных в одной строке они могу прийти в противоречие с данными из другой строки. Аномалия удаления Для решения аномалии удаления для внешнего ключа следует устанавливать одно из двух ограничений: Если строка из зависимой таблицы обязательно требует наличия строки из главной таблицы, то для внешнего ключа устанавливается каскадное удаление. То есть при удалении строки из главной таблицы происходит удаление связанной строки (строк) из зависимой таблицы. Если строка из зависимой таблицы допускает отсутствие связи со строкой из главной таблицы (то есть такая связь необязательна), то для внешнего ключа при удалении связанной строки из главной таблицы задается установка значения NULL. При этом столбец внешнего ключа должен допускать значение NULL. Аномалия вставки Для решения аномалии вставки при добавлении в зависимую таблицу данных столбец, который представляет внешний ключ, должен допускать значение NULL. И таким образом, если добавляемый объект не имеет связи с главной таблицей, то в столбце внешнего ключа будет стоять значение NULL. Аномалии обновления Для решения проблемы аномалии обновления применяется нормализация, которая будет рассмотрена далее.

Нормализация представляет процесс разделения данных по отдельным связанным таблицам. Нормализация устраняет избыточность данных (data redundancy) и тем самым позволяет избежать нарушения целостности данных при их изменении, то есть избежать аномалий изменения (update anomaly). По правилам нормализации есть семь нормальных форм баз данных: - первая - вторая - третья - нормальная форма Бойса-Кодда - четвёртая - пятая - шестая. Приводить данные к нормальным формам можно только последовательно. То есть в базе данных второй нормальной формы данные по умолчанию уже должны быть нормализованы по правилам первой нормальной формы и так далее. В итоге база данных в шестой нормальной форме — идеально нормализованная. В некоторых случаях попытка нормализовать данные до «идеального» состояния может привести к созданию множества таблиц, ключей и связей. Это усложнит работу с базой и снизит производительность СУБД. Поэтому обычно данные нормализуют до третьей нормальной формы.

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

Если упростить: у каждой записи в базе данных должен быть первичный ключ. Первичный ключ — это элемент записи, который не повторяется в других записях. Допустим, 10 декабря покупатель Егор Кузнецов купил цельнозерновой хлеб за 75 рублей в сетевом магазине продуктов города Москвы. Запись о его покупке появилась в базе данных. Нельзя исключать, что другой Егор Кузнецов в этот день купит такой же товар в другом магазине сети. Запись о покупке тоже появится в базе. Чтобы записи не перепутались, можно добавить к ним идентификатор покупки, например номер чека. Идентификатор покупки — это первичный ключ.

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

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