Пример создания базы данных
Предположим, что необходимо создать базу данных (систему связанных таблиц), в которой будет храниться информация о выпускных работах студентов. Иходные данные для разработки базы представлены в виде следующей таблицы:
Рисунок 1 - Исходная таблица
В первых двух столбцах таблицы фамилия, имя и отчество студента, номер его зачетной книжки, в третьем столбце – фамилия, имя и отчество преподавателя - руководителя выпускной квалификационной работы (ВКР) студента, в четвертом – должность руководителя ВКР, в пятом столбце – оценка, полученная студентом при защите ВКР.
Уменьшаем избыточность
Первое, что можно сказать, глядя на таблицу с исходными данными, это то, что в ней содержится много повторяющейся информации. Например, мы видим, что имя и должность преподавателя Полоскова встречается в таблице 3 раза: очевидно, что преподаватель может быть руководителем у нескольких студентов.
Хранение таблицы в базе данных (БД) в таком “сыром” виде нецелесообразно. Во-первых, такая таблица занимает больше места: представьте, что в этой таблице не 10, а миллион строк с повторяющимися элеменами. Во-вторых, изменение информации о преподавателе, пусть, например, изменилась его должность или фамилия, приведет к необходимости поиска в таблице всех строк с именем этого преподавателя и изменение необходимой информации. В большой таблице это приведет к длительному поиску и большой нагрузке на сервер баз данных.
Сотрудники и студенты
Попытаемся уменьшить избыточность данных. Для начала разобъём рассматриваемую таблицу на две. В первой таблице (“Студенты”) мы будем хранить информацию о студентах и их оценках за ВКР:
Рисунок 2 - Таблица “Студенты” (первая версия)
во второй (“Преподаватели”) – информацию о преподавателях или сотрудниках:
Рисунок 3 - Таблица “Преподаватели” (первая версия)
Таким образом, мы выделили две сущности, информация о которых будет храниться в базе данных, и выделили для двух этих сущностей две таблицы: это сущность “Студент” и “Преподаватель”. Конечно, атрибутов и преподавателя и у студента гораздно больше: год рождения, адрес, телефонный номер, но мы для ясности ограничимся минимальным набором атрибутов.
Однако, разделив исходную таблицу на две мы потеряли информацию о руководителях ВКР: теперь, глядя на таблицу студентов мы не сможем определить, кто у этих студентов является руководителем ВКР. Для того, чтобы не потерять эту информацию поступим следующим образом. К таблице “Преподаватели” добавим еще один столбец с табельным номером сотрудника (преподавателя). Пусть это будет целое число. По табельному номеру мы будем отличать одного сотрудника о другого, т.е. в таблице “Преподаватели” не может быть двух строк с одинаковым табельным номером. Мы полагаем, что в таблице могут присутствовать записи о преподавателях полных тезках, поэтому для их различения мы будет использовать искусственный уникальный номер или суррогатный ключ. Таким образом, атрибут “Табельный номер сотрудника” сущности “Преподаватель” будет её ключевым атрибутом.
К таблице “Студенты” добавим столбец “Руководитель ВКР”, в котором будем хранить табельный номер преподавателя – руководителя ВКР студента. Таким способом мы сформировали отношение “один ко многим” между сущностями “Преподаватель” и “Студент”: у одного студента может быть только один руководитель ВКР, но у сотрудника (преподавателя) может быть нескольк студентов, у которых этот преподаватель является руководителем.
Рисунок 4 - Таблицы “Преподаватели” и “Студенты” (вторая версия)
Теперь в обновленной таблице “Студенты” мы не храним имя преподавателя и другие его атрибуты (их может быть много), а только ссылку (“табельный номер сотрудника”) на этого преподавателя в таблице “Преподаватели”.
Сущность “ВКР”
Выделим еще одну очевидную сущность в рассматриваемой информационной системе. Во второй версии таблицы “Студенты” содержится информация о двух сущностях – о сущности “Студент” и о сущности “ВКР”. Отношение между этими сущностями “один к одному”, т.е. каждый студент выполняет одну ВКР и ВКР выполняется только одним студентом. Целесообразно разделить эти сущности и хранить информацию о них в разных таблицах.
Преобразуем таблицу “Студенты”. Теперь она будет содержать только два столбца для хранения номера зачетной книжки и имени студента. Номер зачетной книжки будет ключевым атрибутом сущности “Студент”: будем считать, что не может быть двух студентов с одинаковыми номерами зачетных книжек.
Рисунок 5 - Таблица “Студенты” (третья версия)
Таблица “ВКР” будет содержать три столбца для хранения номера зачетной книжки студента – автора ВКР – это ссылка на таблицу “Студенты”, ссылки на табельный номер руководителя ВКР в таблице “Преподаватели” и оценку по ВКР. В этой таблице может также хранится и наименование выпускной работы, дата защиты и другие атрибуты ВКР. Здесь же мы рассматриваем упрощенную версию этой таблицы.
Рисунок 6 - Таблица “ВКР”
Сотрудник и должность
Вернемся к таблице, где хранится информация о преподавателях. В этой таблице также повторяется информация о должностях: “Профессор”, “Доцент”. Также можно сказать, что в этой таблице хранится информация о двух сущностях о преподавателях и о дожностях.
Рисунок 7 - Таблица “Преподаватели” (вторая версия)
Разделим эту таблицу на две. Создадим таблицу “Должности”, в которой будет содераться информация только о должностях сотрудников. Это будет своего рода справочником должностей университета.
Таблицу “Преподаватели” исправим: теперь столбец должность будет содержать не наименование должности, а ссылку на номер должности в справочнике должностей.
Рисунок 8 - Таблица “Преподаватели” (третья версия)
Таким образом, в результате преобразований исходной таблицы мы получили 4 таблицы для хранения четырех различных сущностей рассматриваемой информационной системы. ER-диаграмма информационной системы приведена на следующем рисунке:
Рисунок 9 - Диаграмма сущность-связь предметной области
SQL
Создадим эти таблицы в СУБД MySQL/MariaDB. Предположим, что база данных уже создана, необходимо написать SQL скрипты для создания таблиц и сформировать отношения между ними.
Должности
В таблице “Должности” (справочник должностей сотрудников) два столбца:
- первый столбец (ID) это уникальный номер должности (большое целое число), которе не может быть пустым (NULL); этот атрибут будет ключевым атрибутом сущности “Должности”;
- второй столбц (POSITION_NAME) это наименование должности (“профессор”, “доцент”, …).
CREATE TABLE POSITIONS
(
ID BIGINT UNSIGNED NOT NULL PRIMARY KEY,
POSITION_NAME VARCHAR(100)
);
Сотрудники (преподаватели)
В таблице “Сотрудники” три столбца:
- ID – уникальный идентификатор (целое беззнаковое число), которое не может быть пустым NULL;
- имя преподавателя, под которе выделяется 255 символов;
- номер должности – ссылка на таблицу “Должности”;
Ссылка на поле “ID” таблицы “POSITIONS” это внешний ключ (FOREIGN KEY) – ссылка на значение в столбеце другой таблицы. В нашем случае “POSITION_ID” ссылается на значение в столбце ID таблицы “POSITIONS”.
CREATE TABLE TEACHERS
(
ID BIGINT UNSIGNED NOT NULL PRIMARY KEY,
NAME VARCHAR(255),
POSITION_ID BIGINT UNSIGNED NOT NULL,
FOREIGN KEY (POSITION_ID) REFERENCES POSITIONS(ID)
);
Студенты
В таблице “Студенты” два столбца:
- номер зачетной книжки (10 символов), которое не может быть пустым и является главным ключом сущности;
- имя студента (до 255 символов).
CREATE TABLE STUDENTS
(
RECORD_BOOK CHAR(10) NOT NULL PRIMARY KEY,
NAME VARCHAR(255)
);
ВКР
Таблица с информацией о ВКР, называемая в базе данных “Q_WORKS”, содержит два столбца со ссылками на таблицу студентов и преподавателей и столбец с оценкой типа TINYINT:
CREATE TABLE Q_WORKS
(
STUDENT_ID CHAR(10),
SUPERVISER_ID BIGINT UNSIGNED,
MARK TINYINT,
FOREIGN KEY (STUDENT_ID) REFERENCES STUDENTS(RECORD_BOOK),
FOREIGN KEY (SUPERVISER_ID) REFERENCES TEACHERS(ID),
PRIMARY KEY (STUDENT_ID, SUPERVISER_ID)
);
То, что столбцы “STUDENT_ID” и “SUPERVISER_ID” хранят ссылки на значения из других таблиц указывается при помощи ключевого слова FOREIGN KEY:
- STUDENT_ID ссылается на поле RECORD_BOOK из таблицы STUDENTS;
- SUPERVISER_ID ссылается на поле ID таблицы TEACHERS.
Главным ключем сущности “ВКР” является составной ключ, включающий в свой состав табельный номер руководителя и номер зачетной книжки студента. В этом случае мы предполагаем, что у студента не может быть две выпускных работы с одним и тем же руководителем. Если это возможно, то необходимо добавить в составной ключ еще какой-то атрибут (дополнительный столбец), который бы отличал одну запись о ВКР от другой. Это может быть, например, дата защиты или код специальности, по которой защищет работу студент.
Рисунок 10 - Диаграмма сущность-связь предметной области
