Пример и алгоритм разработки и построения базы данных MS Access
Разработка структуры и построение базы данных MS Access
Методические указания
Работа выполняется в два этапа. На первом этапе разрабатывается структура базы данных. Это теоретический этап и выполняется без применения ПК. В результате выполнения первого этапа должна быть определена логическая структура данных, то есть состав реляционных таблиц, их структура, межтабличные связи и проведена нормализация таблиц. То есть перед созданием базы данных в MS Access, требуется определить, из каких таблиц должна состоять база данных, какие данные нужно поместить в каждую таблицу, как связать таблицы. На втором этапе создается база данных на основе СУБД MS Access. При этом создаются основные объекты базы данных – таблицы, запросы, формы и отчеты, создаются связи между таблицами, при необходимости в формах и отчетах создаются вычисляемые поля и управляющие элементы.
Перед выполнением задания, следует разобрать методические указания и приведенный пример, алгоритм его решения и выполнить пример в MS Access.
Выполнить контрольные индивидуальные задания согласно личному варианту (стр.14). Файл с выполненным заданием сдать для проверки на кафедру.
Основные понятия СУБД MS Access и методологии разработки структуры базы данных
База данных обеспечивает хранение информации и представляет собой поименованную совокупность данных, организованных по определенным правилам, отображающих состояние объектов и их взаимосвязей в предметной области.
Система управления базами данных (СУБД) – это совокупность языковых и программных средств, предназначенных для создания, ведения и использования базы данных, а также для обработки, управления, хранения, поиска данных, а также предоставляющая пользователю интерфейс для работы с данными, хранящимися в базе данных.
СУБД MS Access– система управления реляционной базой данных включает в свой состав таблицы, запросы, формы, отчеты.
Таблица – основной объект для хранения данных в базе данных MS Access, состоит из множества одинаковых по структуре записей (строк) и полей (столбцов). Каждая запись содержит одинаковое число полей и как правило идентифицируется уникальным ключом. Каждое поле базы данных определяется уникальным именем, типом данных и ограничениями.
Реляционная база данных состоит из взаимосвязанных таблиц. Логические связи между таблицами реализуются за счет одинаковых полей в связываемых таблицах. Различают следующие типы связей:
- 1:1 (один–к–одному), при которой одной записи первой таблицы соответствует одна запись второй таблицы.
- 1:М (один–ко–многим), при которой одной записи первой таблицы соответствует множество записей второй таблицы, а каждой записи второго таблицы соответствует одна запись первой.
- М:М (многие–ко–многим), одной записи первой таблицы соответствует множество записей второй таблицы, а каждой записи второй таблицы соответствует множество записей первой.
Схема данных – структура связей между таблицами базы данных.
Запросы – объект базы данных MS Access, в виде таблицы. Используется для сортировки, анализа данных и их выбор по определенному критерию из одной или нескольких таблиц. Результат запроса называется выборкой. Различают Запрос на выборку, Перекрестный запрос, Запрос с параметрами и ряд других.
Формы – объект базы данных MS Access, используется для отображения, ввода и редактирования данных в базах данных. Наглядность представления форм облегчает восприятие данных..
Отчеты – объект базы данных MS Access, используется для вывода данных на печать. Источниками записей Отчетов являются таблицы и запросы. При запросе “голые” отобранные данные представляются в виде таблицы. Различают Простые отчеты, Составные отчеты, Подчиненный отчет и ряд других.
Элементы управления – представляют собой объекты в формах и отчетах, используемые для отображения ввода, отбора, упорядочивания данных, в качестве элементов оформления и выполнения определенных действий пользователем или програмой. В качестве элементов управления используются Поля, Надписи, Кнопки, Списки, Флажки, Переключатели и целый ряд других элементов.
Объекты базы данных могут содержать сложные вычисления, а данные также могут отображаться в виде сводных таблиц и диаграмм.
Полная функциональная зависимость. Поле В находится в полной функциональной зависимости от поля А, если оно функционально зависит от А и не зависит функционально от любого подмножества поля А.
Первичный ключ (ключевое поле)– это поле с помощью которых однозначно идентифицируются записи в других полях. Значения в ключевом поле уникальны и не могут повторяться.
Поле, каждое значение которого однозначно определяет соответствующую запись, называется простым ключом (ключевым полем). Если записи однозначно определяются значениями нескольких полей, то такая таблица базы данных имеет составной ключ. Если при создании новой таблицы в существующую таблицу включается поле, связывающее старую и новую таблицы (как правило поля совпадают), то это «связующее» поле называется внешним ключом.
Нормализацией называется процесс разделения таблицы на две или более таблиц, в которых каждый факт появляется лишь в одном месте, при этом в любой таблице должна быть функциональная зависимость вида КðF, где К – первичный ключ, F – некоторый атрибут (поле таблицы). Цель нормализации состоит в приведения исходных таблиц к нормальной форме, то есть в удалении «лишних» функциональных зависимостей.
Таблица считается нормализованной, если она находится минимум в третьей нормальной форме или в нормальной форме Бойса–Кодда (всего существует пять нормальных форм).
Таблица находится в первой нормальной форме (1НФ) тогда и только тогда, когда ее строка содержит только одно значение для каждого поля (атрибута) и ни одно из ее ключевых полей не пусто. При этом каждое поле таблицы неделимое и не содержит повторяющихся групп. Таблица находится во второй нормальной форме (2НФ), если она удовлетворяет определению 1НФ и все ее поля, не входящие в первичный ключ, связаны полной функциональной зависимостью с первичным ключом. Таблица находится в третьей нормальной форме (3НФ), если она удовлетворяет определению 2НФ и не одно из ее неключевых полей не зависит функционально от любого другого неключевого поля. Таблица находится в нормальной форме Бойса-Кодда (НФБК), если и только если любая функциональная зависимость между его полями сводится к полной функциональной зависимости от возможного ключа (если в таблице несколько первичных ключей).
Соответственно нормализованная таблица должна одновременно соответствовать требованиям 1НФ, 2НФ и 3НФ (НФБК).
Для проведения нормализации таблиц следует пользоваться следующими правилами:
Первое правило: Если таблица имеет составной первичный ключ вида, скажем, (К1,К2), и включает также поле F, которое функционально зависит от части этого ключа, например, от К2, но не от полного ключа. В этом случае следует сформировать другую таблицу, содержащую поля К2 и F (первичный ключ – К2), и удалить F из первоначальной таблицы. То есть вместо исходной таблицы T(K,F1,F2), где первичные ключи (К1,К2) и К2ðF, создается две таблицы T1(K1,K2), где первичные ключи (К1,К2) и T2(K2,F) с первичным ключом К2.
Второе правило: Если таблица имеет первичный (возможный) ключ К и не являющееся возможным ключом поле F1, которое функционально зависит от К, и другое неключевое поле F2, которое функционально зависит от F1. В этом случае формируется другая таблица, содержащая F1 и F2, с первичным ключом F1, и F2 удаляется из первоначальной таблицы. То есть вместо исходной таблицы T(K,F1,F2) с первичным ключом К и F1ðF2, создается две таблицы T1(K,F1), где первичный ключ К и T2(F1,F2) с первичным ключом F1.
Применяя указанные правила из любой заданной таблицы получают множество таблиц, которые находятся в третьей нормальной форме или в нормальной форме Бойса-Кодда и содержат функциональные зависимости только вида КðF.
Пример и алгоритм разработки и построения базы данных MS Access
1) Разработка структуры базы данных, на основе список данных, которые необходимо хранить в базе данных включает следующие шаги:
1.1) Определение состава и структуры базовой (универсальной) таблицы;
1.2) Определение логических связей, первичных ключей и функциональных зависимостей между атрибутами (полями) базовой таблицы;
1.3) Нормализация (разделение) базовой таблицы;
1.4) Определение состава, структуры таблиц;
1.5) Выбор первичных ключей (ключевых полей),
1.6) Нормализация таблиц, определение типов данных;
1.7) Определение типа связей между таблицами;
2) Создание базы данных MS Access включает следующие шаги:
2.1) Создание таблиц MS Access;
2.2) Создание связей между таблицами;
2.3) Создание различных типов запросов;
2.4) Создание форм и отчетов;
2.5) Создание кнопочных форм.
Пример 8.
Разработать структуру базы данных и построить базу данных в MS Access для сбора и обработки данных по результатам сдачи экзаменов и зачетов студентами по разным дисциплинам, на основе следующей информации:
ФИО студента,
Семестр,
Дисциплина,
Форма отчетности,
Оценка,
Количество часов,
ФИО преподавателя.
Должность.
С помощью запросов: вывести список студентов, имеющих отличные оценки по информатике; вывести список студентов имеющих отличные отметки по заданному предмету (предусмотреть ввод названия дисциплины с клавиатуры); найти среднюю оценку каждого студента по всем предметам.
Построить перекрестный запрос, отображающий количество часов по каждой дисциплине и для каждого студента.
Построить составную форму и отчет для просмотра и печати ведомости успеваемости какого-либо студента по разным дисциплинам.
Решение примера 8
1. Разработка структуры базы данных.
1.1 Первоначально создадим базовую таблицу (табл.68, универсальная таблица), которая содержит все перечисленные данные, которые примем за имена полей таблицы. В качестве записей таблицы введем произвольные данные, учитывая при этом, что каждый студент сдает экзамен или зачет по нескольким дисциплинам определенному преподавателю и получает оценку.
Таблица 68 – Универсальная таблица «Сессия»
| Фамилия студента | Семестр | Дисциплина | Форма отчетности | Оценка | Количество часов | Фамилия преподавателя | Должность |
| Иванов | Биология | Зачет | Цветкова | Преподаватель | |||
| Иванов | Математика | Экзамен | Соболев | Доцент | |||
| Иванов | Информатика | Зачет | Волков | Профессор | |||
| Иванов | Информатика | Экзамен | Волков | Профессор | |||
| Петров | Биология | Зачет | Цветкова | Преподаватель | |||
| Петров | Математика | Экзамен | Соболев | Доцент | |||
| Петров | Информатика | Зачет | Волков | Профессор | |||
| Петров | Информатика | Экзамен | Волков | Профессор | |||
| Сидоров | Статистика | Зачет | Соболев | Доцент | |||
| Сидоров | Статистика | Экзамен | Волков | Профессор | |||
| Сидоров | Экономика | Зачет | Белкина | Ассистент | |||
| Сидоров | Экономика | Экзамен | Медведев | Профессор |
1.2 Определим функциональные зависимости и ключевые поля для таблицы 1. Предположим, что каждый студент сдаёт один раз экзамен (зачет) по дисциплине согласно учебному плану. Каждая дисциплина однозначно характеризуется наименованием, номером семестра, в котором изучается предмет, и формой отчетности. Тогда в качестве первичного (составного) ключа таблицы Сессия можно использовать следующий набор полей:
Фамилия студента, Дисциплина, Семестр, Форма отчетности.
Остальные неключевые поля табл.68 будут функционально зависеть от какой-либо части составного ключа.
1.3 Проведем нормализацию табл. 68 в соответствии с правилами нормализации и определениями нормальных форм. Так как поля таблицы 68 – Фамилия преподавателя, Количество часов и Должность функционально зависят только от полей Дисциплина, Семестр и Форма отчетности. Тогда, согласно первому правилу нормализации, выделим в отдельную таблицу 69 Учебный план поля – Дисциплина, Семестр, Форма отчетности, Количество часов, Фамилия преподавателя, Должность (ключевые поля подчеркнуты).
Таблица 69 – Учебный план
| Дисциплина | Семе-стр | Форма отчетности | Количество часов | Фамилия преподавателя | Должность |
| Биология | Зачет | Цветкова | Преподаватель | ||
| Математика | Экзамен | Соболев | Доцент | ||
| Информатика | Зачет | Волков | Профессор | ||
| Информатика | Экзамен | Волков | Профессор | ||
| Биология | Зачет | Цветкова | Преподаватель | ||
| Математика | Экзамен | Соболев | Доцент | ||
| Информатика | Зачет | Волков | Профессор | ||
| Информатика | Экзамен | Волков | Профессор | ||
| Статистика | Зачет | Соболев | Доцент | ||
| Статистика | Экзамен | Волков | Профессор | ||
| Экономика | Зачет | Белкина | Ассистент | ||
| Экономика | Экзамен | Медведев | Профессор |
Соответственно из исходной таблицы 68 поля Фамилия преподавателя, Количество часов и Должность удаляются. Из оставшихся полей сформируем таблицу 70 Результаты сессии – Фамилия студента, Дисциплина, Семестр, Форма отчетности, Оценка.
Таблица 70 – Результаты сессии
| Фамилия студента | Дисциплина | Семестр | Форма отчетности | Оценка |
| Иванов | Биология | Зачет | ||
| Иванов | Математика | Экзамен | ||
| Иванов | Информатика | Зачет | ||
| Иванов | Информатика | Экзамен | ||
| Петров | Биология | Зачет | ||
| Петров | Математика | Экзамен | ||
| Петров | Информатика | Зачет | ||
| Петров | Информатика | Экзамен | ||
| Сидоров | Статистика | Зачет | ||
| Сидоров | Статистика | Экзамен | ||
| Сидоров | Экономика | Зачет | ||
| Сидоров | Экономика | Экзамен |
1.4 Анализируя таблицу 69 очевидно, что они не соответствуют требованиям третьей нормальной формы, так как поле Должность функционально зависит от неключевого поля Фамилия преподавателя. Соответственно, используя второе правило нормализации, выделим поля Фамилия преподавателя и Должность в отдельную таблицу 71 «Кадровый состав» и исключим из таблицы 69 поле Должность. При этом в таблицу 71 для однозначной идентификации каждого преподавателя введем ключевое поле Код преподавателя.
Таблица 71 – Кадровый состав
| Код преподавателя | Фамилия | Должность |
| Волков | Профессор | |
| Белкина | Ассистент | |
| Медведев | Профессор | |
| Соболев | Доцент | |
| Цветкова | Преподаватель |
1.5 Анализируя таблицы 69 и 70 видно, что ключевые поля Дисциплина, Семестр, Форма отчетности в этих таблицах повторяются. Это приводит к избыточности и дублированию информации сразу в трех полях, поэтому целесообразно ввести вместо трех указанных ключевых полей одно ключевое поле Код учебного плана в таблицы 69 и 70. Также очевидно, что поле Фамилия студента не может однозначно идентифицировать студента, так как возможны однофамильцы, поэтому это поле выделим в отдельную таблицу 72 «Студенты», в которой введем новое поле Код студента.
Таблица 72 – Студенты
| Код студента | Фамилия студента |
| Иванов | |
| Петров | |
| Сидоров |
Рассматривая подробнее таблицу 69 Учебный план, видно, что наименование Дисциплин многократно повторяются и есть вероятность сделать ошибки при вводе одного и того же наименования. При этом может возникнуть потенциальная противоречивость данных. Поэтому, выделим поле дисциплины в отдельную таблицу 73 «Дисциплины», а для однозначной идентификации дисциплин введем поле Код дисциплины.
Таблица 73 – Дисциплины
| Код дисциплины | Наименование дисциплины |
| Биология | |
| Математика | |
| Информатика | |
| Статистика | |
| Экономика |
1.6 Преобразуем таблицы 69 и 70 в соответствии с выполненными изменениями. В таблице 69 вместо полей Фамилия студента введем поле Код студента, вместо поля Дисциплина – поле Код дисциплины, вместо поля Преподаватель – Код преподавателя. В таблице 70 вместо полей Дисциплина, Семестр, Форма отчетности введем поле Код учебного плана и вместо поля Фамилия студента – поле Код студента. В результате преобразований получим таблицы 74 и 75.
Таблица 74 –Учебный план (итоговая)
| Код учебного плана | Код дисциплины | Семестр | Количество часов | Форма отчетности | Код преподавателя |
| Зачет | |||||
| Экзамен | |||||
| Зачет | |||||
| Экзамен | |||||
| Зачет | |||||
| Экзамен | |||||
| Зачет | |||||
| Экзамен |
Таблица 75 – Результаты сессии (итоговая)
| Код студента | Код учебного плана | Оценка |
Все таблицы базы данных (табл. 71-75) находятся в первой, второй и третьей нормальной формах, то есть все поля каждой из таблиц неделимы и зависят от ключевого поля и значение любого поля в этих таблицах, не входящих в первичный ключ, не зависит от значений другого неключевого поля.
Следует отметить, что если бы вместо поля Фамилия студента в базе данных использовалось поле ФИО студента, то для того чтобы соблюдался принцип неделимости полей, в соответствии с требованиями первой нормальной формы, следовало бы в таблице Студенты дополнительно разделить поле ФИО студенты на поля Фамилия, Имя, Отчество
1.7 Определение связей между таблицами. Связь между таблицами реализуется за счет одинаковых полей в связываемых таблицах. Чтобы связать две таблицы необходимо ключ первой таблицы ввести в состав ключа второй таблицы. В противном случае нужно ввести в состав первой таблицы внешний ключ – ключ второй таблицы. В структуре разработанных таблиц 71-75 уже имеются одинаковые поля. Соответственно следует установить связи между следующими полями (см.рис.50):
Код учебного плана (табл. Учебный план) и Код учебного плана (табл. Результаты сессии) – связь один–ко–многим;
Код студента (табл. Студенты) и Код студента (табл. Результаты сессии) – связь один–ко–многим;
Код дисциплины (табл. Дисциплины) и Код дисциплины (табл. Учебный план) – связь один–ко–многим;
Код преподавателя (табл. Преподаватели) и Код преподавателя (табл. Учебный план) – связь один–ко–многим.
1.8 Определение типов данных и ограничений. Разные типы полей имеют разное назначение и разные свойства. Как правило, для ключевых полей, содержащих числовые данные, такие как Код студента, Код дисциплины выбирают тип данных Счетчик или Числовой, а ограничения Значение уникально и Значение не должно быть пустым. Для полей типа Фамилия, Дисциплина выбирают тип данных Текстовый, а ограничения Размер строки символов и Значение не должно быть пустым.
Поля для ввода дат или времени имеют тип Дата/время. Поля для ввода денежных сумм имеют тип Денежный. Поля для хранения внешних объектов (рисунков, таблиц, аудиозаписей и так далее) имеют тип OLE, а для хранения текста тип МЕМО.