Проектирование БД

Для функционирования информационной системы (см. “БД и СУБД”) необходимо, чтобы инфологическая, концептуальная модель адекватно отображала реалии предметной области. Фундаментальными же реалиями при построении инфологических моделей являются объекты (сущности) с их свойствами (атрибутами) и связи между ними. Методологии, позволяющие отображать существующую смысловую содержательность реальности независимо от компьютерного представления, относятся к так называемым семантическим методологиям.

Подобные методологии являются нисходящими, поскольку они начинаются на высшем уровне абстракции и заканчиваются представлением макета конкретной БД для конкретной СУБД.

С начала 70-х годов XX века было предложено несколько семантических методик построения инфологических моделей. Наиболее популярной и употребительной стала методика на основе так называемых “ER-моделей” (ER — “сущность-связь”, entity-relationship). ER-модели были разработаны П.Ченом в 1976 г. Отличительные особенности ER-моделей — мощность, гибкость, прозрачность.

Для повышения (существенного!) эффективности процесса разработки моделей применяют специальные комплексы программных средств — CASE-средства (Computer Aided Software/System Engineering).

ER-модели

Для основных элементов — сущностей, связей, атрибутов — в ER-моделях используются следующие обозначения (надписи на всех рисунках будут сделаны на этапе верстки):

Как правило, для именования сущностей используют существительные, для связей — глаголы.

Если на этапе построения ER-модели определяются атрибуты, являющиеся первичными ключами соответствующих сущностей, то они, как правило, подчеркиваются.

Принадлежность атрибутов сущностям и связи между сущностями обозначают линиями. Линии, обозначающие связи, снабжаются указаниями на тип связи (“один к одному”, “один ко многим”, “многие ко многим”). Единого стандарта на способ обозначения типа связи нет, в последнее время одним из наиболее удобных и наглядных признан стиль, который используется в Microsoft Access. Согласно ему связи обозначаются цифрой 1 на стороне “одного” и символом “Ґ” на стороне “многих”.

Рассмотрим примеры из близкой всем нам школьной жизни.

Сущность “ученик”

Атрибуты — уникальный (допустим — в пределах данной школы) номер, фамилия, имя, дата рождения.

Сущность “класс”

Атрибуты — уникальный номер, номер параллели, буква класса внутри параллели.

Сущность “учитель”

Атрибуты — уникальный номер, фамилия, имя, отчество.

Сущность “предмет”

Атрибуты — уникальный номер, название.

Сущность “класс—ученик”

Тип — “один ко многим”.

Сущность “учитель—предмет”

Тип — “многие ко многим”.

Связи, как и сущности, могут иметь атрибуты. Например, администрацию школы нередко интересует стаж преподавания данным учителем данного предмета. Если подобную информацию необходимо хранить, то естественно добавить “стаж” в качестве атрибута связи “учитель—предмет”.

Переход от ER-модели к реляционной

Описанию реляционной модели данных посвящена отдельная статья (см. “Реляционные БД”).

При переходе от ER-модели к даталогической реляционной модели, как правило:

— каждая сущность описывается отдельной таблицей;

— атрибуты становятся полями таблиц, для них задаются подходящие типы данных, имеющиеся в используемой СУБД;

— в таблицах определяются первичные ключи, при необходимости вводятся суррогатные;

— для связей “многие ко многим” вводятся соответствующие таблицы, снабженные, возможно, требуемыми атрибутами;

— при необходимости производится нормализация таблиц до заданной нормальной формы (как правило — до 3НФ).

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

Разработчики баз данных в целом следуют приведенной выше схеме. Вместе с тем они, например, часто предусматривают первичные ключи еще на этапе построения ER-модели, а таблицы сразу стараются делать нормализованными. Однако спроектировать БД “с ходу” можно лишь на небольшом проекте. Как правило, все описанные выше этапы все равно приходится проходить. И чем квалифицированнее специалист, тем тщательнее он относится ко всем шагам разработки.

Методические рекомендации

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

Продемонстрируем, как можно разумно минимизировать процесс демонстрационной разработки БД, сразу спустившись до нужного уровня абстракции, не используя понятий, не поддержанных временем, требуемым на усвоение и закрепление, и акцентируя внимание лишь на наиболее важных вопросах. Разработаем БД “Страница классного журнала”. Очень важно то, что типичная страница классного журнала хорошо знакома ученикам.

Приступая к проектированию базы данных, прежде всего следует определиться с тем, какие данные требуется хранить. Иногда, для того чтобы получить ответ на этот вопрос, приходится долго работать с заказчиком (экспертом) или структурировать свои собственные мысли. В нашем случае ситуация облегчается тем, что мы знаем, как выглядит журнал и что в нем хранят. Поскольку мы не собираемся делать “лучше лучшего”, постараемся просто “положить” бумажный классный журнал на “музыку” реляционной базы данных. На странице журнала, относящейся к одному предмету, присутствует список учеников (обычно для каждого ученика указываются имя и фамилия), даты уроков и оценки. Кроме того, учителя обычно помечают карандашом типы некоторых уроков (и, соответственно, происхождение полученных на них оценок: “с/р”, “тест” и т.д.). Все?.. Нет, не все. Помимо оценок, в журнале обычно отмечаются пропуски уроков. Это тоже следует учесть при проектировании структуры БД. Наконец, введем очень важное ограничение: мы будем считать, что в одной клеточке журнала хранится только одна оценка, т.е. клеточек вида 2/5 в журнале не встречается.

Предмет: Информатика Учитель: Гейтс Б.

Сразу отметим, поскольку речь идет об одной странице журнала, предмет и учитель являются свойствами самой БД, и хранить их в БД не имеет смысла. Пойдем дальше.

Вообще говоря, страница сама по себе уже является таблицей, но есть одна неприятность: количество столбцов в ней не фиксировано. Хотя в каждом конкретном журнале оно одно и то же, но и журналы разные бывают, и не все столбцы всегда заполнены (например, новую четверть нередко начинают на новой странице). Поэтому первое, что сделаем, — набросаем макет таблицы, содержащей все требуемые данные и имеющей фиксированное число столбцов.

Является ли такое представление данных удовлетворительным? Нет, не является. Но, отвечая на этот вопрос, важно не “перегнуть палку”, не быть слишком категоричным. Не раз приходилось видеть, как учитель, задав в классе приведенный выше вопрос, считал его риторическим и немедленно продолжал: “…поэтому изменим структуру базы данных…”. Ведь дети могут просто не понять, в чем дело: вся требуемая информация, присутствующая на странице журнала, в этой таблице есть, что же еще надо?

Перечислим те недостатки выбранного представления данных, которые лежат на поверхности.

1. Зачем мы много раз повторили, что 8.09 была самостоятельная работа? Ясно, что если уж она была, то у всех. И вообще, если уж был урок, то у всех (но не следует забывать, что ученик мог отсутствовать на уроке или просто не получить за него оценку).

2. Как мы собираемся хранить значения в поле “Содержимое клеточки журнала”? Как число? Но в этом столбце встречаются и символы “н”. Как символ? Можно, конечно, но тогда при различных вычислениях (например, при вычислении средней оценки) придется что-то с этим символом делать, чтобы превратить его в цифру.

3. Вдруг у нас в классе имеются два Банановых Бори? Как мы их собираемся различать?

4. Наконец, зачем нам строки с пустыми клеточками в поле “Содержимое клеточки журнала”? Это в журнале имеются пустые клеточки, нам-то они зачем?

Рассмотрим эти вопросы по порядку, начиная с последнего.

4. Такие строки нам не нужны. Можно просто выбросить их из таблицы.

3. Отвечая на третий вопрос, стоит еще раз вспомнить, как выглядит страница журнала. Все ли данные мы перенесли в таблицу базы данных? Ведь вопрос, который возник перед нами, не является специфичным именно для базы данных, его как-то приходится решать и в обычном журнале. Оказывается, мы просто забыли о том, что список учеников в журнале пронумерован, а номер и является тем уникальным полем, которое однозначно определяет каждого ученика. Имеет смысл завести отдельную таблицу для хранения номеров (уникальных идентификаторов) учеников и их имен и фамилий. Тогда именно номера можно будет использовать в других таблицах.

2. Символы “н” нам мешают. Так, может быть, и для фиксации пропусков завести отдельную таблицу? И отмечать в этой таблице, кто и в какой день отсутствовал.

1. И для уроков тоже можно завести отдельную таблицу. Достаточно будет один раз пометить в этой таблице дату урока и, возможно, здесь же записать дополнительную информацию.

После этих рассуждений выпишем новую структуру таблиц. Теперь, поскольку таблиц стало несколько, дадим им названия.

Лучше? Лучше. Но… Все ли мы предусмотрели в нашей модели? Ведь мы не учли важную особенность исходных данных: дело в том, что в один день может быть проведено больше одного урока. Конечно, записать их в таблицу “Уроки” не проблема, но вот как мы поймем из таблицы “Оценка”, за какой именно урок выставлена оценка? Эта проблема решается тем же способом, каким мы обеспечили уникальность учеников: уроки должны получить уникальные (для простоты — числовые) идентификаторы. Проще говоря, как и учеников, уроки (все, от начала до конца учебного года, насквозь) надо пронумеровать. И указывать в таблице “Оценка” не даты, а номера уроков. В таблице “Пропуск” мы тоже будем указывать номера уроков (ведь ученик может прийти на контрольную, а обычный урок пропустить). Снова изменим структуру таблиц (изменения коснутся лишь таблиц “Оценка”, “Урок” и “Пропуск”).

Вот БД и разработана. На ней легко показать и что такое ключи (причем в таблицах “Оценка” и “Пропуск” первичные ключи составные), и что такое связи (связь “один ко многим” здесь видна сразу, а вот показывать ли связь “многие ко многим”, которая разрешена посредством таблицы “Оценка”, остается на усмотрение учителя). Учитель также должен решить, на каком этапе перейти к использованию формальных терминов (например, когда назвать уникальный номер ключом). Однозначной рекомендации здесь нет, различные подходы могут быть удачными. Возможно, разумнее все же сначала разработать БД, дать учащимся прочувствовать смысл понятий, а уже потом сказать, что и как называется.

Все сказанное выше относилось преимущественно к базовому курсу. Для тех, кто ведет профильный курс, имеется достаточное количество удачных разработок. Например, учебное пособие по элективному курсу “Информационные системы и модели” И.Г. Семакина и Е.К. Хеннера, в котором, в частности, имеется разработка БД “Школа”.