Готовясь к предстоящему тесту по SQL и реляционным базам данных для Launch School, я разработал простую школьную реляционную базу данных. Я опишу различные схемы и методы, которые я использовал для создания этой реляционной базы данных.
Первым шагом в создании реляционной базы данных является абстракция. В школе есть учителя, каждый из которых преподает по одному предмету, и каждый предмет может преподавать только один учитель. У каждого учителя может быть много учеников, и у каждого ученика может быть много учителей. Каждый ученик может получить только одну оценку по предмету класса, но оценки могут принадлежать более чем одному ученику. Если я извлечу все существительные из этого абзаца, у меня получится: учителя, предметы, ученики и оценки. Эти существительные могут быть представлены строками данных, называемыми сущностями. Эти сущности также связаны друг с другом отношениями.
Диаграммы отношений сущностей (ER) могут быть нарисованы, чтобы показать сущности и их отношения друг с другом. Эти объекты могут быть представлены в виде квадратов, как показано ниже.
Мне также нужно связать одну сущность с другой, чтобы представить отношения между двумя сущностями. Есть три разных типа отношений, которые могут совместно использоваться сущностями: «один-к-одному», «один-ко-многим» и «многие-ко-многим». Эти отношения могут быть представлены с использованием нотации «воронья лапа». Обозначение "вороньей лапки" для каждого отношения показано ниже.
Поскольку учитель может преподавать только один предмет, а один предмет может преподавать только один учитель, между предметами и учителями существуют индивидуальные отношения.
Поскольку у каждого учителя может быть много учеников, а у каждого ученика может быть много учителей, учителя и ученики связаны отношениями «многие ко многим».
Поскольку каждый ученик может получить только одну оценку по каждому предмету, тогда как оценки могут быть выставлены многим ученикам, ученики и оценки имеют отношение «один ко многим».
Моя последняя диаграмма ER показана ниже.
Также полезно описать отношения между двумя объектами. Описание того, как объекты взаимодействуют друг с другом, будет проиллюстрировано ромбовидной формой. Описание каждой взаимосвязи сущностей для диаграммы приведено ниже. Учителя преподают предметы и имеют учеников; у учащихся есть учителя, и они получают оценки. Обновленная диаграмма, отражающая эти изменения, показана ниже.
Еще одна тема, которую я хочу затронуть, - это модальность. Модальность указывает, является ли отношение обязательным или необязательным. Если одна сущность должна иметь связь с другой сущностью, тогда будет вертикальная линия через ссылку рядом с сущностью. Если одна сущность не должна иметь отношения с другой сущностью, тогда в строке рядом с этой сущностью будет помещен 0. Схема, иллюстрирующая модальность, показана ниже.
Сущность субъектов не обязательно должна иметь связь с сущностью учителя, или она может иметь не более одной связи с сущностью учителя. Сущность учителя должна иметь одну и только одну связь с сущностью предметов.
Сущность учителя должна иметь по крайней мере одну или несколько отношений с сущностью учеников, а сущность ученика должна иметь по крайней мере одну или несколько отношений с сущностью учителя.
Сущность студентов должна иметь одну и только одну связь с сущностью оценок. Сущность оценок не обязательно должна иметь связь с сущностью учеников, или она может иметь много отношений с сущностью ученика.
Я обновил диаграмму ER, чтобы показать не только отношения одной сущности к другой, но и модальность.
Каждая сущность имеет атрибуты, которые являются характеристиками представляемого существительного. У каждого предмета есть имя. У каждого ученика и учителя могут быть имя, фамилия, номер телефона и адрес электронной почты. Каждая оценка может иметь определенную букву. Диаграмма ER была обновлена овальными формами, чтобы проиллюстрировать атрибуты сущностей. На приведенной ниже диаграмме ER теперь отображаются эти атрибуты.
Я могу использовать физическую диаграмму, чтобы отобразить схему, которую я хочу для базы данных. Физическая диаграмма будет включать имена таблиц, имена столбцов, типы данных столбцов, ограничения столбцов и ограничения таблиц. Имена таблиц будут смоделированы по именам сущностей, а имена столбцов будут смоделированы по атрибутам сущностей. На диаграмме ниже показана физическая диаграмма реляционной базы данных, которую я подробно расскажу в следующих параграфах.
На физической диаграмме должно быть указано, какие значения могут быть добавлены в столбцы с помощью типа данных и дополнительных ограничений. Тип данных указывает, какие типы данных разрешены для столбца. Тип данных для каждого столбца в каждой таблице (кроме столбцов phone_number) обозначен как TEXT, который позволяет вводить только строки, которые могут содержать неограниченное количество символов. Тип данных для столбца phone_number обозначен как INTEGER, что позволяет вводить только целые числа.
Ограничения столбца не позволяют вводить в столбец определенные данные. Каждый столбец в каждой таблице имеет N для NOT NULL, что является ограничением столбца, которое гарантирует, что значение должно быть указано для столбца и не может быть пустым. Ограничения таблицы не позволяют вводить в таблицу определенные данные. U для UNIQUE - это ограничение таблицы, которое было добавлено в таблицы учителей и учеников, чтобы в столбцы адресов электронной почты этих таблиц не могло быть введено повторяющихся данных.
Важно иметь возможность однозначно идентифицировать каждую строку в таблице, чтобы было легче ссылаться на строку, если необходимо внести изменения или поиск значений. Уникальный идентификатор, который делает каждую строку в таблице уникальной, называется ключом. Ключ можно рассматривать как столбец данных, в котором каждая строка данных в столбце представляет собой уникальное значение. Например, в таблице учителей мы можем рассматривать столбец phone_number как ключевой, потому что у каждого человека должен быть свой номер телефона. Некоторые проблемы с этим заключаются в том, что номер телефона человека может измениться или у человека может не быть номера телефона.
Столбец phone_number является примером естественного ключа, поскольку это существующее значение в таблице, которое можно использовать для уникальной идентификации каждой строки. Поскольку естественные ключи не очень надежны, необходимо создать специальный столбец, который будет действовать как официальный ключ, называемый суррогатным ключом. В каждую схему таблиц можно добавить столбец id, который действует как уникальный идентификатор для каждой строки данных в таблицах. Поскольку столбец id будет официальным ключом для таблицы, необходимо добавить ограничения, чтобы гарантировать, что нет нулевых значений, которые могут быть добавлены в столбец, и что каждое значение должно быть уникальным. Для столбца id я собираюсь дать ему тип данных serial, который используется для столбцов id, потому что он устанавливает тип данных INTEGER, устанавливает ограничение NOT NULL, а также ограничение DEFAULT, которое автоматически добавит следующее целое число в последовательности . Я также хочу добавить ограничение UNIQUE к столбцам id.
Существует ограничение таблицы первичного ключа, которое можно добавить в столбец id. Это ОГРАНИЧЕНИЕ ПЕРВИЧНОГО КЛЮЧА эквивалентно добавлению ОГРАНИЧЕНИЯ NOT NULL и УНИКАЛЬНОГО ОГРАНИЧЕНИЯ. Столбцы id теперь называются первичным ключом для таблицы, поскольку первичные ключи однозначно идентифицируют строку данных для таблицы.
Теперь мне нужно реализовать отношения, которые были проиллюстрированы на диаграммах отношений сущностей. Внешний ключ ссылается на строку в другой таблице. Я могу добавить ограничение внешнего ключа (FK) к столбцу и указать, на какой столбец в другой таблице я хочу ссылаться. Рекомендуется, чтобы внешний ключ ссылался на первичный ключ другой таблицы, поскольку первичный ключ однозначно определяет строку таблицы. Ограничение внешнего ключа (FK) запрещает нам добавлять какие-либо значения в столбец внешнего ключа, если нет соответствующего значения первичного ключа, расположенного в таблице, на которую ссылается внешний ключ.
Диаграмма выше демонстрирует взаимно однозначное отношение. В таблице субъектов есть столбец, который действует как первичный ключ, и внешний ключ, который ссылается на столбец первичного ключа таблицы учителей.
На приведенной выше диаграмме показана связь "многие-ко-многим". При наличии отношения «многие ко многим» необходима дополнительная таблица, которая не учитывается в диаграмме взаимосвязи сущностей. Дополнительная таблица содержит внешние ключи, каждый из которых ссылается на первичный ключ другой таблицы. Таблица student_teachers имеет свой собственный столбец id, который имеет ограничение первичного ключа и имеет внешний ключ teacher_id, который ссылается на столбец первичного ключа id таблицы учителей. В таблице student_teachers также есть столбец внешнего ключа student_id, который ссылается на столбец первичного ключа id таблицы студентов.
На приведенной выше диаграмме показана связь "один ко многим". В таблице студентов есть столбец внешнего ключа, который ссылается на идентификатор столбца первичного ключа таблиц оценок.
На приведенной выше диаграмме показана окончательная физическая диаграмма. Создание таблиц, каждая из которых представляет определенный набор данных и соединяет каждую с помощью первичных и внешних ключей, называется нормализацией. Нормализация уменьшает дублирование данных и поддерживает целостность данных. Теперь, когда создана физическая диаграмма и намечена схема реляционной базы данных, можно реализовать реляционную базу данных.
Нарисовав диаграмму взаимосвязей сущностей и затем переведя ее в физическую диаграмму, можно создать схему для реляционной базы данных.
