Пример проектирования базы данных
Прежде чем приступать к созданию базы данных, необходимо потратить какое-то время на ее проектирование.
Основная цель проектирования баз данных (БД) – это сокращение избыточности хранимых данных, а следовательно, экономия объема используемой памяти, уменьшение затрат на многократные операции обновления избыточных копий и устранение возможности возникновения противоречий из-за хранения в разных местах сведений об одном и том же объекте. Так называемый, «чистый» проект БД («каждый факт в одном месте») можно создать, используя методологию нормализации отношений. Нормализация должна использоваться на завершающей проверочной стадии проектирования БД.
Плохая проработка структуры базы почти всегда приводит к бесполезным затратам времени на ее переработку в дальнейшем. Опытные разработчики уделяют проектированию баз данных не меньше времени, чем их созданию. В целом же разработка базы данных включает следующие этапы:
1. Определение назначения базы данных.
2. Принятие решения о том, какие исходные данные база данных должна содержать.
3. Определение исходных таблиц базы данных.
4. Определение полей, которые будут входить в таблицы, и выбор полей, содержащих уникальные значения.
5. Назначение связей между таблицами и окончательный просмотр получившейся структуры.
6. Создание таблиц, связывание их между собой и экспериментальное наполнение базы пробными данными.
7. Создание форм, отчетов и запросов для операций с введенными данными.
Определение назначения базы данных
Разработка каждой базы данных начинается с изучения проблемы, которую она должна разрешить, или потребности, которую она должна удовлетворить.
В качестве примера попробуем создать простейшую базу данных библиотеки художественной литературы «Библиотека». База данных предназначена для хранения данных о приобретенных библиотекой книгах, информации о местонахождении отдельных экземпляров каждого издания и сведений о читателях.
Выбор информации, включаемой в базу
Для ведения библиотечных каталогов, организации поиска требуемых книг и библиотечной статистики в базе должны храниться сведения, большая часть которых размещаются в аннотированных каталожных карточках. Анализ запросов на литературу показывает, что для поиска подходящих книг (по тематике, автору, издательству и т.п.) и отбора нужного (например, по аннотации) следует выделить следующие атрибуты каталожной карточки:
1. Автор (фамилия и имя каждого автора книги).
2. Название книги.
3. Место издания (город).
4. Издательство (название издательства).
5. Год выпуска.
6. Аннотация.
К атрибутам, позволяющим охарактеризовать места хранения отдельных экземпляров книг, можно отнести:
1. Номер комнаты (помещения для хранения книг).
2. Номер стеллажа в комнате.
3. Номер полки на стеллаже.
4. Номер (инвентарный номер книги).
5. Дата приобретения.
6. Дата размещения конкретной книги на конкретном месте.
7. Дата изъятия книги с установленного места.
К атрибутам, позволяющим охарактеризовать читателей, можно отнести:
1. Номер читательского билета (формуляра).
2. Фамилия читателя.
3. Имя читателя.
4. Отчество читателя.
5. Адрес читателя.
6. Телефон читателя.
7. Дата выдачи читателю конкретной книги.
8. Срок, на который конкретная книга выдана читателю.
9. Дата возврата книги.
Определение исходных таблиц
Анализ определенных выше объектов и атрибутов позволяет определить для проектируемой базы данных следующие таблицы для построения базы данных:
1. Авторы. Таблица предназначена для хранения сведений об авторах издания.
2. Книги. Таблица предназначена для хранения сведений о книгах.
3. Издательства.Таблица предназначена для хранения сведений об издательствах.
4. Хранилище. Таблица предназначена для описания места хранения книг.
5. Выдача.Таблица предназначена для хранения сведений о выданных книгах.
6. Читатели.Таблица предназначена для хранения сведений о читателях библиотеки.
Выбор необходимых полей таблиц
Определив набор таблиц, входящих в базу, надо продумать, какая информация о каждом объекте будет входить в каждую из таблиц. Каждое поле должно принадлежать одной отдельной таблице. В то же время информация в каждом поле должна быть структурно-элементарной, то есть она должна храниться в полях в виде наименьших логических компонентов.
Исходя из вышесказанного, определяем поля в выбранных таблицах и тип хранимых данных.
Авторы:
· код автора – числовое поле, предназначено для однозначного определения каждого конкретного автора в базе данных;
· фамилия автора – символьное поле, не более 50 символов;
· имя автора – символьное поле, не более 25 символов;
· отчество автора – символьное поле, не более 25 символов.
Книги:
· код книги – числовое поле, предназначено для однозначного определения каждой конкретной книги в базе данных;
· название книги – символьное поле, не более 256 символов;
· аннотация – текстовое поле;
· дата издания;
· дата поступления в библиотеку;
· место хранения.
Издательства:
· код издательства – числовое поле, предназначено для однозначного определения каждого конкретного издательства в базе данных;
· название издательства – символьное поле, не более 256 символов;
· город, где расположено издательство – символьное поле, не более 25 символов.
Хранилище:
· код места – числовое поле, предназначено для однозначного определения каждой конкретной полки в базе данных;
· номер комнаты – числовое поле;
· номер стеллажа – числовое поле;
· номер полки – числовое поле.
Выдача:
· код выдачи – числовое поле, предназначено для однозначного определения каждой конкретной выдачи в базе данных;
· номер выданной книги – числовое поле;
· код читателя – числовое поле;
· дата выдачи;
· срок выдачи (количество дней);
· дата возврата.
Читатели:
· номер читательского билета – числовое поле, предназначено для однозначного определения каждого конкретного читателя в базе данных;
· фамилия – символьное поле, не более 50 символов;
· имя – символьное поле, не более 50 символов;
· отчество – символьное поле, не более 50 символов;
· адрес – символьное поле, не более 256 символов;
· телефон – символьное поле, не более 20 символов.
Выбор уникальных полей
В реляционной базе данных таблицы могут быть связаны друг с другом. Эта связь устанавливается с помощью уникальных полей. Уникальные поля – это такие поля, в которых значения не могут повторяться. Например, серия и номер паспорта однозначно идентифицируют любого человека, имеющего паспорт. Такое поле (или комбинация полей), которое однозначно идентифицирует запись в таблице, называется первичным ключом.В качестве поля первичного ключа также может выступать порядковый номер записи в каталоге, табельный номер работника предприятия, артикул товара в розничной торговле.
Для нашей базы данных первичными ключами являются следующие поля:
· Авторы – код автора.
· Книги – код книги.
· Издательства – код издательства.
· Хранилище – код места.
· Выдача – код выдачи.
· Читатели – номер билета.
Назначение связей между таблицами
Межтабличные связи увязывают две таблицы с помощью общего поля, которое имеется в обеих таблицах. Существуют три типа таких связей:
· один-к-одному– каждая запись таблицы А не может быть связана более чем с одной записью таблицы Б;
· один-ко-многим– одна запись в таблице А может быть связана со многими записями таблицы Б (например, в каждом классе может быть много учеников);
· многие-ко-многим– каждая запись в таблице А может быть связана со многими записями в таблице Б, а каждая запись в таблице Б – со многими записями в таблице А (например, у каждого учащегося может быть несколько преподавателей, а у каждого преподавателя может быть много учеников).
Реляционные базы данных не позволяют создавать связи типа многие-ко-многим напрямую. Однако в реальной жизни такие связи встречаются очень часто, поэтому их реализуют через вспомогательные таблицы, увязывая несколько таблиц связями типа один-ко-многим.
Для того чтобы связать одну таблицу с другой, надо ввести во вторую таблицу поле первичного ключа из первой таблицы, т.е. ввести во вторую таблицу внешний ключ. Связь двух таблиц выполняется подключением первичного ключа главной таблицы (находящейся на стороне отношения «один») к такому же полю внешнего ключа связанной таблицы (находящейся на стороне отношения «многие»). Поле внешнего ключа в связанной таблице должно иметь тот же тип данных, что и первичный ключ в родительской таблице, но с одним исключением. Если первичный ключ главной таблицы имеет тип данных «Счетчик», то поле внешнего ключа в связанной таблице должно иметь тип данных «Числовой».
В нашей базе данных установим следующие типы связей между таблицами:
1. Авторы – Книги. Здесь связь многие-ко-многим, у любого автора может быть более одной книги, и любая книга может быть написана несколькими авторами. Поэтому вводим вспомогательную таблицу «Авторы–книги» со следующими полями:
· код автора;
· код книги.
2. Книги – Издательства. Здесь связь многие-ко-многим, любая книга может быть издана несколькими издательствами и любое издательство издает не одну книгу. Поэтому вводим еще одну вспомогательную таблицу «Книги–издательства» со следующими полями:
· код книги;
· код издательства.
3. Хранилище – Книги. Здесь связь один-ко-многим, на одной полке можно расставить множество книг, но любая книга может быть только на одной полке в хранилище. Поэтому поле «Место хранения» в таблице «Книги» определяем как внешний ключ, и связываем таблицы «Хранилище» и «Книги» первичным ключом «Код места» и внешним ключом «Место хранения».
4. Книги – Выдача. Здесь связь один-ко-многим, т.е. одна и та же книга может быть выдана несколько раз в разные даты разным читателям. Поэтому поле «Номер выданной книги» в таблице «Выдача» определяем как внешний ключ, и связываем таблицы «Книги» и «Выдача» первичным ключом «Код книги» и внешним ключом «Номер выданной книги».
5. Читатели – Выдача. Здесь связь один-ко-многим, т.е. одна и та же книга может быть выдана несколько раз разным читателям в разные сроки. Поэтому поле «Код читателя» в таблице «Выдача» определяем как внешний ключ, и связываем таблицы «Читатели» и «Выдача» первичным ключом «Номер читательского билета» и внешним ключом «Код читателя».
Нормализация отношений
Закончив проектирование таблиц и выявив связи, существующие между ними, необходимо тщательно перепроверить полученную структуру, прежде чем приступать к созданию таблиц и вводу информации. Нормализация отношений позволяет существенно сократить объем хранимой информации и устранить аномалии в организации хранения данных.
Правило 1: каждое поле таблицы должно представлять уникальный тип информации.
В спроектированной нами базе данных нет полей в разных таблицах, содержащих одну и ту же информацию (за исключением внешних ключей).
Правило 2: каждая таблица должна иметь уникальный идентификатор, или первичный ключ, который может состоять из одного или нескольких полей.
В спроектированной нами базе данных все таблицы (за исключением вспомогательных «Авторы – книги» и «Издательства – книги») содержат первичный ключ.
Правило 3: для каждого значения первичного ключа значения в столбцах данных должны относиться к объекту таблицы и полностью его описывать.
Это правило используется двояко. Во-первых, в таблице не должно быть данных, не относящихся к объекту, определяемому первичным ключом. Например, хотя для каждой книги требуется информация о ее авторе, но автор является самостоятельным объектом, и данные о нем должны находиться в соответствующей таблице. Во-вторых, данные в таблице должны полностью описывать объект.
Правило 4: должна быть возможность изменять значения любого поля (не входящего в первичный ключ) без воздействия на данные других полей.
Последнее правило позволяет проверить, не возникнут ли проблемы при изменении данных в таблицах. Поскольку в спроектированной нами базе данные, содержащиеся в разных полях таблиц, нигде не повторяются, мы имеем возможность корректировать значения любых полей (за исключением первичных ключей).
Наполнение базы данных, создание форм и отчетов
Чтобы определить, насколько структура базы данных соответствует поставленной задаче и насколько удобно с этой базой работать, необходимо ввести несколько простейших записей. Обычно после этого приходится возвращаться к структуре базы и настраивать ее в соответствии с тем, какие результаты были получены в ходе такого теста.
На заключительном этапе создают формы для ввода информации в базу, отчеты для вывода информации и запросы, с помощью которых производится выборка информации из нескольких таблиц. Если база предназначена для передачи другим пользователям, то, скорее всего, необходимо, чтобы кто-то из посторонних людей проверил, насколько удобно работать с формами и отчетами.
Полученная схема данных разработанной БД в MS Access представлена на рис. 4.1.
Рис. 4.1. Схема данных разработанной БД в Microsoft Access
Контрольные вопросы
1. Дайте определение информационной системы.
2. Поясните понятие базы данных.
3. Что такое предметная область?
4. Дайте определение СУБД.
5. Что такое модель данных?
6. Поясните основные принципы реляционной модели данных.
7. Поясните особенности СУБД Microsoft Access.
8. Каковы основные объекты базы данных Access?
9. Поясните структуру таблицы Access.
10. Поясните понятия: запрос, форма, отчет, страница доступа к данных, макрос, модуль.
11. Каковы основные этапы проектирования базы данных?
12. Каким образом осуществляется выбор информации, включаемой в базу данных?
13. Поясните понятия: первичный ключ, внешний ключ.
14. Каково назначение связей между таблицами?
15. Поясните основные типы связей между таблицами.
16. В чем заключается нормализация отношений базы данных?