Поделюсь с вами практическим примером реализации базы данных для некоторой железнодорожной станции (вы можете перенести данный пример на свою задачу или почерпнуть новые знания). Статья будет состоять из двух частей.
Задача
Реализовать базу данных для одной железнодорожной станции, граничащей с переездом через автомагистраль. В реализации использовать такие обязательные данные как: тип, номер поезда и количество вагонов в нем; количество мест/билетов; время прибытия и время отправления со станции; график закрытия переезда; изменение расписания в случае форс-мажорных ситуаций.
Необходимо принять во внимание, что в расписании должны фигурировать актуальные данные («вчерашние» рейсы должны храниться в архиве). Также предполагается, что переезд граничит со станцией и закрывается по прибытии поезда, и открывается – по отправлению, а количество и тип проданных билетов необходимо считать для каждого рейса в отдельности.
Средства реализации
Работа выполнена в свободной реляционной СУБД MySql. Для удобства результаты запросов и результирующие таблицы представлены в среде разработки DataGrip.
MySQL – свободная лицензированная кроссплатформенная реляционная система управления базами данных, разработку и поддержку которой осуществляет компания Oracle. Данный продукт является самым популярным в мире благодаря своей производительности, надежности и простаты использования. MySQL наиболее часто используется для веб-приложений, например, на таких ресурсах, как Facebook, Twitter, YouTube. Также — это полноценная СУБД с индексами.
Типы данных
VarChar () – строка переменной длины, где в скобках указывается максимальная длина.
Int – целое число, Диапазон числа со знаком от –2147483648 до 2147483647. Диапазон числа без знака (unsigned) от 0 до 4294967295.
Datetime – дата и время в формате (ГГГГ-ММ-ДД ЧЧ-ММ-СС). Поддерживается диапазон величин от '1000-01-01 00:00:00' до '9999-12-31 23:59:59'.
Enum («значение1», «значение2», …) - строка-объект, который может принимать только одно значение, выбирается из списка значений «значение 1», «значение 2» или NULL. ENUM максимум может иметь 65535 различных значений.
Проектирование
В качестве сущностей для данной базы данных были выбраны реальные объекты и понятия:
- Поезд – является объектом выполнения рейса.
- Рейс – функция передвижение поезда (в нашем случае: время прибытия и отправления с одной станции).
- Билет – объект, выдаваемый пассажиру, для совершения рейса на определенном поезде.
Все сущности должны определяться в таблицах с соответствующими атрибутами.
Для таблиц, данные в которых постоянно обновляются и становятся не актуальными, необходимо сделать архивные или «холодные» таблицы. А также, разработать алгоритмы синхронизации и архивации.
Во всех таблицах необходимо осуществить отношения между сущностями так, что не может существовать рейс без назначенного ему поезда или с назначенным несуществующим поездом, а также билет с несуществующим рейсом.
Для работы с базой данных будет предусмотрен следующий функционал:
- Функции внесения данных (создание нового поезда, нового рейса и продажа билетов).
- Функции изменения расписания.
- Функция синхронизации данных между актуальными и архивными таблицами.
- Функция, возвращающая расписание железнодорожного переезда.
Создание таблиц
Для реализации поставленной задачи были выбраны такие данные как: тип, номер поезда и количество вагонов в нем; количество мест/билетов; время прибытия и время отправления со станции; цена билетов, имя и фамилия владельцев; график закрытия переезда.
Все эти данные распределены по таблицам и имеют определенный тип.
В данной таблице «id» является идентификатором, который указывает номер поезда; «type» - тип поезда (возможно лишь 3 варианта: стандарт, скоростной и грузовой); «countWagons» - количество вагонов в поезда.
В данной таблице «id» - идетификатор, номер рейса; «idTrain» - номер поезда, на котором совершится поездка; «arrival» - время прибытия поезда на станцию; «departure» - время отправления поезда со станции; «leftTickets» - количество оставшихся билетов.
В данной таблице «id» - идетификатор, номер билета; «idTimeTable» - номер рейса, на который куплен билет; «price» - стоимость билета; «firstName» - имя владельца; «lastName» - фамилия владельца.
В данной таблице «close» - время начала закрытия переезда; «open» - время конца закрытия переезда.
Также были созданы 2 вспомогательные таблицы идентичные таблицам «timeTable» и «tickets» для хранения неактуальной информации. Например, информация о прошедших рейсах и их пассажирах.
Связи между таблицами
Скрипты создания таблиц
Структура запроса для создания таблицы в СУБД MySQL:
Дополнительные параметры: primary key (делает данное поле первичным ключом); not null (поле не может быть пустым); auto_increment (необходим для идентификаторов (полей primary key и типом данных int)).
Проверка на соответствие третьей нормальной форме
Так как в представленных таблицах исключено внесение одинаковых строк, и каждая строка имеет конечное значение, все таблицы соответствуют первой нормальной форме (Первая нормальная форма (1НФ) – требует, чтобы каждое поле таблицы базы данных было неделимым и не содержало повторяющихся групп).
В таблице, отвечающей за поезда, количество вагонов и тип поезда зависят от номера поезда. В таблицах, отвечающих за билеты имя, фамилия пассажира и цена зависят от номера билета. В таблицах, отвечающих за поездки, время прибытия/отправления и количество билетов зависят только от номера поездки.
В таблице, отвечающей за железнодорожный переезд, отсутствуют ключевые поля. Таким образом, все неключевые поля зависят только от одного ключевого, которое является первичным ключом (свой для каждой таблицы), следовательно, таблицы соответствуют второй нормальной форме (Вторая нормальная форма (2НФ) – это такая 1НФ, которая требует, чтобы все поля таблицы зависели от первичного ключа, то есть, чтобы первичный ключ однозначно определял запись и не был избыточен).
Так как в наших таблицах первичные ключи однозначны и несоставные, и таблицы соответствуют второй нормальной форме, то они также соответствуют и третьей нормальной форме (Третья нормальная форма (3НФ) – это такая 2НФ, которая требует, чтобы значение любого поля таблицы, не входящего в первичный ключ, не зависело от значения другого поля, не входящего в первичный ключ).
В следующей части вас ждет ...
Во второй статье мы создадим индексы, заполним таблицы, поработаем с ними и за одно протестируем.