Проектирование базы данных автошколы

БД автошколы. Урок 2. Инфологическое проектирование

Проектирование нашей базы начнём с наиболее естественного и понятного для человека способа – инфологического проектирования. Для того чтобы получить модель нашей предметной области воспользуемся простым механизмом, элементами которого являются сущности, связи и описание сущностей (свойства или по-другому атрибуты). Сущности в виде гномиков…простите. Под словом «сущность», конечно, же люди подразумевают совсем другое, в контексте баз данных.

Сущности – это такие объекты, которые мы можем понять и отличить от других. Сущностями может быть что угодно 🙂 (по сути, Вы тоже являетесь сущностью). Сущностями могут быть: люди, животные, кот, билет в кино, самолёт, ковёр…да практически что угодно.

В нашей повседневной жизни обычно некоторые объекты связаны между собой (например, у кота есть лапы, у человека есть определенный кот и т.д.), поэтому наши сущности имеют связи между собой. Причём связи могут быть «Один ко многим» или «Многие к одному» (пример: в космосе есть много звёзд – это связь «Один ко многим». А вот у звезд есть только один космос – это связь «Многие к одному»):

БД автошколы. Урок 2. Инфологическое проектирование

Конечно же у любого объекта есть набор свойств (или по-другому атрибуты сущностей). Например, пусть нашей сущностью будет «Самолёт», у самолёта могут быть следующие свойства (атрибуты): цвет, ширина, высота, производитель, лётно-технические характеристики и т.д.

Наиболее привычное для нас восприятие – это таблицы, т.е. в контексте баз данных сущностью является таблица, атрибуты – это столбцы таблицы.

У любой таблицы в БД желательно, чтобы было такое уникальное свойство, которое могло бы идентифицировать строку таблицы. Такое уникальное свойство называется уникальный идентификатор или по-другому «первичный ключ». Столбец, который может являться частью другой таблицы и который неразрывно может быть связан и не существовать без определённой таблицы, называется «внешний ключ», но к нему вернёмся позже.

Давайте теперь перейдём к чему-нибудь более приземленному, а то космос, самолёты… 🙂

Таблицы — сущности. Определение сущностей БД автошколы

Исходя из анализа предметной области в предыдущей статье и мной были выявлены следующие сущности, необходимые, как мне кажется, для проектирования БД:

1. «Ученики» – подробная информация о тех, кто проходит обучение в автошколе. Вводим новый атрибут «ID_Ученика», который будет уникальным идентификатором. Описание атрибутов сущности «Ученики» приведено в таблице:

Название атрибута Описание атрибута Диапазон значений Тип значения Пример значения
ID_Ученика Идентификатор ученика Не пустое Счётчик 15
Фамилия Фамилия ученика >2 символов Текст Артёмов
Имя Имя ученик >2 символов Текст Артём
Отчество Отчество ученика >2 символов Текст Артёмович
Дата рождения Дата рождения ученика >01.01.1900 Дата 02.02.1999
Пол Пол ученика Мужской; Женский; Текст Мужской
Серия паспорта Серия паспорта ученика <4 символов Символы 0123
Номер паспорта Номер паспорта ученика <6 символов Символы 123456
Кем выдан паспорт Организация, выдавшая паспорт >0 символов Текст УФМС по Хабаровскому краю г.Хабаровск
Место прописки Место прописки ученика >0 символов Текст Хабаровский край, г. Хабаровск, ул. Штурманская, 7а
Контактный телефон Контактный телефон ученика <20 символов Символы +7 (914) 4922879

2. «Администрация» — информация о лицах, занимающихся управлением автошколы. Вводим новый атрибут «ID_Администратора», который будет уникальным идентификатором.

Описание атрибутов сущности «Администрация» приведено в таблице:

Название атрибута Описание атрибута Диапазон значений Тип значения Пример значения
ID_Администратора Идентификатор администратора Не пустое Счётчик 28
Фамилия Фамилия преподавателя >2 символов Текст Лопата
Имя Фамилия преподавателя >2 символов Текст Степан
Отчество Отчество преподавателя >2 символов Текст Петрович
Дата рождения Дата рождения >01.01.1900 Дата 09.05.1980
Пол Пол администратора Мужской; Женский; Текст Мужской
Серия паспорта Серия паспорта <4 символов Число 0123
Номер паспорта Номер паспорта <6 символов Число 123456
Кем выдан паспорт Организация, выдавшая паспорт Не пустое Текст УФМС по Хабаровскому краю г. Хабаровск
Место прописки Место прописки Не пустое Текст Хабаровский край, г. Хабаровск, ул. Шелеста 128, кв. 25
Контактный телефон Контактный телефон <20 символов Символы +7 (914) 6548728
Должность Должность сотрудника автошколы >5 символов Текст Директор

3. «Преподаватели» – информация о лицах, обучающих теоретическим и практическим основам. Вводим новый атрибут «ID_Преподавателя», который будет уникальным идентификатором. Описание атрибутов сущности «Преподаватели» приведено в таблице:

Название атрибута Описание атрибута Диапазон значений Тип значения Пример значения
ID_Преподавателя Идентификатор преподавателя Не пустое Счётчик 25
Фамилия Фамилия преподавателя >2 символов Текст Дружко
Имя Фамилия преподавателя >2 символов Текст Евгений
Отчество Отчество преподавателя >2 символов Текст Онегович
Дата рождения Дата рождения >01.01.1900 Дата 25.12.1975
Пол Пол Мужской; Женский; Текст Мужской
Серия паспорта Серия паспорта <4 символов Число 0123
Номер паспорта Номер паспорта <6 символов Число 123456
Кем выдан паспорт Организация, выдавшая паспорт Не пустое Текст УФМС по Хабаровскому краю г.Хабаровск
Место прописки Место прописки Не пустое Текст Хабаровский край, г. Хабаровск, ул. Ленинградская 139, кв. 45
Контактный телефон Контактный телефон <20 символов Символы +7 (914) 4881457
Должность Должность сотрудника автошколы >5 символов Текст Инструктор

4. «Группы» – информация о группах автошколы. Вводим новый атрибут «ID_Группы», который будет уникальным идентификатором. Описание атрибутов сущности «Группы» приведено в таблице:

Название атрибута Описание атрибута Диапазон значений Тип значения Пример значения
ID_Группы Идентификатор группы Не пустое Счётчик 1
Дата Дата создания группы >19.09.2000 Дата 25.10.2016
Количество человек Количество человек в группе <30 Число 20

5. «Автомобили» – сведения об автомобилях, используемых для практических занятий. Вводим новый атрибут «ID Автомобиля», который будет уникальным идентификатором. Описание атрибутов сущности «Автомобили» приведено в таблице:

Название атрибута Описание атрибута Диапазон значений Тип значений Пример значения
ID_Автомобиля Идентификатор автомобиля Не пустое Счётчик 19
Фирма Фирма, выпустившая автомобиль >2 символов Текст Toyota
Модель Конкретная модель автомобиля >2 символов Текст Corolla
Год выпуска Год, когда выпустили автомобиль >1800 Число 1998
Трансмиссия Тип трансмиссии Автомат; Механика; Текст Механика
Объём двигателя Характеристика двигателя <10 символов Символы 1.5
Тип кузова Вариант кузова >4 символов Текст Седан
Количество дверей Количество дверей в автомобиле <9 символов Число 4
Расположение руля Расположение руля в автомобиле Левый; Правый; Текст Правый
Гос. номер Гос. номер автомобиля <6 символов Текст s777bt
Регион Регион, в котором находится автомобиль(цифра) <200 символов Число 27

6. «Учет оплаты» — данные об оплате учениками обучения в автошколе. Вводим новый атрибут «Номер_платежа», который будет уникальным идентификатором. Описание атрибутов сущности «Учет оплаты» приведено в таблице:

Название атрибута Описание атрибута Диапазон значений Тип значений Пример значения
Номер_платежа Идентификатор платежа Не пустое Счётчик 56
Дата платежа Дата, когда был произведён платеж >19.09.2000 Дата 05.09.2016
Сумма платежа Сумма, внесенная учеником >5000 Число 10000
Размер долга Оставшаяся сумма, которую необходимо внести. <30000 Число 20000

7. «Сведения о теоретических и практических занятиях» — данные о проведенных занятиях инструкторами и преподавателями. Первичным ключом будет являться атрибут «Номер_занятия». Описание атрибутов сущности «Сведения о теоретических и практических занятиях» приведено в таблице:

Название атрибута Описание атрибута Диапазон значений Тип значений Пример значения
Номер занятия Идентификатор занятия Не пустое Счётчик 15
Тип занятия Тип занятия в автошколе Теоретическое; Практическое; Текст Практическое
Время занятия Время проведения занятия >1 символа Текст 2 часа
Место проведения занятия Место, где проводилось занятие >1 символа Текст Автодром

Определение связей между сущностями

Следующим шагом является установление связей между сущностями:

«Ученики» — «Группы». Связь «Многие к одному». В одной группе состоит несколько учеников.

БД автошколы. Урок 2. Инфологическое проектирование

«Преподаватели» — «Группы». Связь «один ко многим». Один преподаватель может сопровождать несколько групп.

БД автошколы. Урок 2. Инфологическое проектирование

«Администрация» — «Преподаватели». Связь «один ко многим». В автошколе как минимум несколько преподавателей, но одна администрация.

БД автошколы. Урок 2. Инфологическое проектирование

«Преподаватели» — «Автомобили». Связь «Один ко многим». У одного сотрудника может быть несколько автомобилей, но один автомобиль принадлежит только одному сотруднику.

БД автошколы. Урок 2. Инфологическое проектирование

«Ученики» — «Сведения о теоретических и практических занятиях». Связь «Один ко многим». Один ученик проходит несколько практических занятий во время курса обучения.

БД автошколы. Урок 2. Инфологическое проектирование

«Ученики» — «Учёт оплаты». Связь «Один ко многим». Каждый ученик гасит долг перед организацией и производит платежи.

БД автошколы. Урок 2. Инфологическое проектирование

«Автомобили» — «Сведения о теоретических и практических занятиях». Связь «Один ко многим». На одном автомобиле может проводиться несколько занятий с разными учениками.

БД автошколы. Урок 2. Инфологическое проектирование

«Преподаватели» — «Сведения о теоретических и практических занятиях». Связь «Один ко одному». Один преподаватель проводит несколько занятий с учениками.

БД автошколы. Урок 2. Инфологическое проектирование

«Группы» — «Сведения о теоретических и практических занятиях». Связь «Один ко одному». Занятия проходят у нескольких групп.

БД автошколы. Урок 2. Инфологическое проектирование

Построение концептуальной схемы

На основании связей между сущностями построим общую концептуальную схему, будет выглядеть она вот таким образом:

БД автошколы. Урок 2. Инфологическое проектирование

Составление справочника задач

После определения сущностей создадим справочник задач, учитывая основные сущности базы данных который содержит наиболее частые запросы к созданной базе данных. Результат я отобразил в таблице:

Наименование задачи Цель решения задачи Сущности, используемые в задаче Частота решения задачи
Сведения о практических занятиях Вывод полной информации о всех практических занятиях «Сведения о теоретических и практических занятиях», «Преподаватели», «Ученики», «Автомобили» 20 раз в месяц
Сведения об автомобилях с автоматической коробкой передач Вывод полной информации о автомобилях, оборудованных АКПП «Автомобили», «Преподаватели» 10 раз в месяц
Сведения об учениках, год рождения которых <1985 Вывод информации об учениках «Ученики» 30 раз в месяц
Вывод должников Вывод информации о учениках, имеющих задолженность по оплате «Учёт оплаты», «Ученики» 70 раз в месяц
Вывод женщин преподавательниц Вывод информации о лицах женского пола «Преподаватели» 25 раз в месяц
Группы, месяц создания которых >5 Вывод информации о группах, месяц создания которых > 5 «Группы», «Ученики», «Преподаватели» 30 раз в месяц
Самое длинное практическое занятие Вывод информации о практическом занятии, длящемся больше остальных «Сведения о теоретических и практических занятиях», «Преподаватели», «Ученики», «Автомобили» 5 раз в месяц
Количество платежей размером 5000 Вывод количества платежей размером 5000 «Учёт оплаты», «Ученики» 60 раз в месяц
Самый молодой ученик Вывод информации о самом молодом ученике автошколы «Ученики» 3 раз в месяц
Средний возраст преподавателей Вывод среднего значения возраста преподавателей «Преподаватели» раза в месяц

На этом, чтобы не растягивать сильно статью, закончим наш урок. Вообще, инфологическое проектирование — важный этап на начальной стадии разработки базы данных, потому что именно на этом этапе продумываются сущности (таблицы), атрибуты (столбцы таблицы), а также как будут связаны таблицы между собой. Если проигнорировать этот этап, то в будущем придётся постоянно менять структуру таблиц и связи, что не очень хорошо, если в таких таблицах содержится уже достаточное количество данных. Также очень важно определить, какие задачи будет выполнять база данных для оценки ресурсов, необходимых для выполнения запросов к базе данных, которые могут быть ресурсозатратными.