Астахова Е.В. Введение в информатику
Подождите немного. Документ загружается.
139
2.3 СИСТЕМА УПРАВЛЕНИЯ БАЗАМИ ДАННЫХ
2.3.1 Основные понятия
База данных (БД) — совокупность данных, организованных по определенным
правилам, предусматривающим общие принципы описания, хранения и манипулирования
данными, независимо от прикладных программ. База данных реализуется в виде записей,
хранящихся в виде организованной структуры; сведения из каждого источника хранятся в
отдельной таблице.
Система управления базами данных (СУБД) представляет собой комплекс
программ и языковых средств для создания, ведения и использования БД. Основная
особенность СУБД — наличие процедур для ввода и хранения не только самих данных, но
и описаний их структуры.
Возможности СУБД:
• поддержание логически согласованного набора файлов;
• обеспечение языка манипулирования данными;
• восстановление информации после разного рода сбоев;
• параллельная работа нескольких пользователей в реальном режиме времени.
В наиболее полном варианте СУБД может иметь следующие компоненты:
• среда пользователя, дающая возможность непосредственного управления
данными с клавиатуры;
• алгоритмический язык для программирования прикладных систем
обработки данных, реализованный как интерпретатор.
• программы-утилиты быстрого программирования рутинных операций:
отчетов, экранов, меню, запросов и т.п. (иногда их называют генераторами);
СУБД предоставляет большие возможности по обработке данных и их
совместному использованию. Можно выбирать любые поля, форматы полей, сортировать
данные, вычислять итоговые значения, отбирать интересующие данные по какому-либо
признаку, менять их, удалять, копировать в другие таблицы и т.д.
2.3.2 Интерфейс системы управления базами данных MS Access
Рис. 3 Элементы интерфейса СУБД MS Access
Таблицы являются главными объектами Access. Данные, которые они содержат,
являются основой любой СУБД. Организация данных напоминает лист книги электронной
Панель
объектов
Access
Обозначения активных режимов в
строке состояния
ФЛТР — фильтр;
N
UM — клавиатурный калькулятор
(NumLock);
ЗМЩ — замена при вводе.
140
таблицы. В каждом столбце таблицы находятся данные одинакового типа, а каждая строка
содержит одинаковые записи.
Рис. 4 Режим таблицы Access
Запись(1) — это набор взаимосвязанных данных о конкретном предмете.
Поле (2) — информационная единица записи.
Ключ (ключевое поле) — поле, которое содержит наиболее характерную для
данной записи информацию.
Группа — это идущие подряд записи с одинаковым одним или несколькими
полями.
Рис. 5 Режим конструирования запроса Access
Запрос позволяет указать, какие именно поля будут отражаться; ввести критерий
(правило), согласно которому будут определяться отображаемые записи и порядок
сортировки этих записей. Запросы можно использовать в качестве основы для форм или
отчетов, чтобы определить, какие именно записи должны в них содержаться.
Рис. 6 Режим формы Access
Формы позволяют управлять процессом отображения данных на экране.
Для вывода на печать используются отчеты, которые можно отображать в режиме
"Конструктора", либо в окне предварительного просмотра.
При коллективном использовании Access возможно разным пользователям дать
разные права по просмотру и изменению информации.
Каждая база данных хранится в виде файла с расширением mdb.
1
2
141
Рис. 7 Режим отчета Access
2.3.3 Модели данных
По способу установления связей между данными различают реляционную,
иерархическую и сетевую модели.
Реляционная («relation» — отношение) модель является простейшей и наиболее
привычной формой представления данных в виде таблицы. Достоинством реляционной
модели является сравнительная простота инструментальных средств поддержки,
недостатком - жесткость структуры данных (например, невозможность создания строк
произвольной длины) и зависимость скорости её работы от размера БД.
Иерархическая и сетевая модели предполагают наличие связей между данными,
имеющими какой-либо общий признак. В иерархической модели такие связи могут быть
отображены в виде дерева-графа, где возможны только односторонние связи от старших
вершин к младшим. Это облегчает доступ к необходимой информации, но только если
всевозможные запросы отражены в структуре дерева. Никакие другие запросы
удовлетворены быть не могут.
Указанный недостаток снят в сетевой модели, где, по крайней мере,
теоретически, возможны связи "всех со всеми". Использование иерархической и сетевой
моделей ускоряет доступ к информации в базе данных. Но поскольку каждый элемент
данных должен содержать
ссылки на некоторые другие элементы, требуются
значительные ресурсы как дисковой, так и оперативной памяти персонального
компьютера.
Реляционные системы лучше соответствуют их возможностям и вполне
удовлетворяют большинству пользователей. Скоростные характеристики этих СУБД
поддерживаются специальными средствами ускоренного доступа к информации —
индексированием баз данных.
Производительность СУБД оценивается
• временем выполнения запроса;
• временем
выполнения операции импортирования БД из других форматов;
• скоростью выполнения обновления, вставки, удаления данных;
• временем генерации отчета.
2.3.4 Технология проектирования БД
Прежде чем создавать таблицы необходимо задать структуру базы данных.
Хорошая структура базы данных является основой для эффективной работы.
Проектирование базы данных включает следующие этапы:
1. Определение цели создания базы
данных.
2. Определение таблиц базы данных.
3. Определение полей таблиц.
142
4. Задание индивидуального значения полям таблиц.
5. Определение связей между таблицами.
6. Обновление структуры базы данных.
7. Ввод данных и создание объектов для работы с таблицами.
Этап 1. Определение цели создания базы данных
На этом этапе проектирования базы данных необходимо определить:
• цель создания базы данных;
• информацию, которую будут содержать поля таблиц;
• темы, которые должна охватывать база данных;
• отчеты по запросам, которые она должна выдавать;
База данных должна отвечать требованиям тех, кто будет с ней работать.
Этап 2. Определение таблиц базы данных
Одним из наиболее сложных этапов в процессе проектирования базы данных
является разработка таблиц, так как результаты, которые должна выдавать база данных
(отчеты, выходные формы и др.) не всегда дают полное представление о структуре
таблицы.
Основные принципы проектирования:
1. Информация в одной таблице не должна дублироваться.
2. Не должно быть повторений между разными таблицами.
Хранение определенной информации только в одной таблице делает работу
более эффективной, так как исключает возможность несовпадения информации в
разных таблицах.
3. Каждая таблица должна содержать информацию только на одну тему.
Сведения на каждую тему обрабатываются намного легче, если содержатся в
независимых друг от друга таблицах.
Этап 3. Определение полей таблиц
Каждая таблица содержит информацию на отдельную тему. Каждое поле в
таблице содержит отдельные сведения по теме таблицы.
При разработке полей для каждой таблицы необходимо помнить:
• каждое поле должно быть связано с темой таблицы;
• не рекомендуется включать в таблицу данные, которые являются
результатом вычислений;
• в таблице должна присутствовать вся необходимая информация по
конкретному вопросу;
• информацию следует разбивать на наименьшие логические единицы.
Например, поля «Имя
», «Фамилия», «Отчество» а не общее поле «ФИО».
Этап 4. Задание индивидуального значения полям таблицы
При работе с данными из нескольких таблиц, необходимо установить между
ними связи. Наличие одинакового поля в таблицах позволяет установить связь между
ними и работать с данными из обеих таблиц.
Для того чтобы можно было связать данные из
разных таблиц, каждая таблица
должна содержать поле или набор полей, которые будут задавать индивидуальное
значение каждой записи в таблице. Такое поле или набор полей называют основным
ключом. Например, чтобы связать данные о клиенте и его заказы необходимо, чтобы
таблицы «Клиенты» и «Заказы» имели одинаковое поле Код Клиента.
Типы ключевых полей
Ключевые поля счетчика. При добавлении записи в таблицу в поле счетчика
должно автоматически вноситься порядковое число. Это наиболее простой способ
создания ключевых полей.
143
Простой ключ. Если поле содержит уникальные значения, такие как коды или
инвентарные номера, то это поле можно определить как ключевое. Если выбранное поле
содержит повторяющиеся или пустые значения, то оно не будет определено как ключевое.
Если устранить повторы путем изменения значений невозможно, то следует либо
добавить в таблицу поле счетчика и сделать его ключевым, либо определить составной
ключ.
Составной ключ. В случаях, когда невозможно гарантировать уникальность
значений каждого поля, существует возможность создать ключ, состоящий из нескольких
полей.
Если определить подходящий набор полей для составного ключа сложно, то
нужно добавить поле счетчика и сделать его ключевым. Например, не рекомендуется
определять ключ по полям Имена и Фамилии, поскольку нельзя исключить повторения
этой пары значений для разных людей.
Этап 5. Определение связей между таблицами
После распределения данных по таблицам и определения ключевых полей
необходимо выбрать схему для связи данных в разных таблицах. Для этого нужно
определить связи между таблицами.
Связь устанавливает отношения между совпадающими значениями в ключевых
полях, обычно между полями разных таблиц, имеющими одинаковые имена. В
большинстве случаев с ключевым полем одной таблицы, являющимся уникальным
идентификатором каждой записи, связывается внешний ключ другой таблицы.
Типы отношений между таблицами
Отношение «один-ко-многим». Связь с отношением «один-ко-многим» является
наиболее часто используемым типом связи между таблицами. В такой связи каждой
записи в таблице A могут соответствовать несколько записей в таблице B, а запись в
таблице B не может иметь более одной соответствующей ей записи в таблице A.
Отношение «многие-ко-многим». При отношении «многие-ко-многим» одной
записи в таблице A могут соответствовать несколько записей в таблице B, а одной записи
в таблице B несколько записей в таблице A.
Отношение «один-к-одному». При отношении «один-к-одному» запись в
таблице A может иметь не более одной связанной записи в таблице B и наоборот; такие
данные могут быть помещены в одну таблицу.
Связь с отношением «один-к-одному» используют для
• разделения очень широких таблиц,
• отделения части таблицы по соображениям защиты,
• сохранения сведений, относящихся к некоторым записям в главной таблице.
Отношение «один-ко-многим» создается в том случае, когда только одно из
полей является ключевым или имеет уникальный индекс.
Отношение «один-к-одному» создается в том случае, когда оба связываемых поля
являются ключевыми или имеют уникальные индексы.
Связь с отношением «многие-ко-многим» фактически является двумя связями с
отношением «один-ко-многим» через третью таблицу, ключ которой состоит из по
крайней мере двух полей, которые являются полями внешнего ключа в двух других
таблицах.
Этап 6. Обновление структуры базы данных
После проектирования таблиц, полей и связей необходимо еще раз просмотреть
структуру базы данных и выявить возможные недочеты, исключить из таблиц повторения
данных. Желательно это сделать, пока таблицы не заполнены данными.
Для проверки необходимо:
• создать несколько таблиц;
• ввести несколько записей в каждую таблицу;
144
• посмотреть, отвечает ли база данных поставленным требованиям.
Этап 7. Ввод данных и создание объектов для работы с таблицами
Если структуры таблиц отвечают поставленным требованиям, то можно вводить
все данные. Затем приступить к созданию запросов и отчетов.
2.3.5 Работа с объектами Access
2.3.5.1 Таблицы
Для нормальной работы с БД необходима однозначная идентификация каждой
записи
. Это достигается автоматической нумерацией записей в поле с типом данных
Счетчик. Возможна ручная нумерация записей в поле с типом данных Числовой и
свойством Индексированное поле — ДА (совпадения не допускаются).
Типы данных Описание
Текстовый 0-255 символов
МЕМО Текстовые или числовые данные не более 64000 символов
Числовой
Дата/время
Денежный Числа ххххххххххххххх(15),хххх(4)
Счетчик
Логический ДА или НЕТ
Числовые форматы Диапазон Значение
Байт
Целое
Длинное целое
СПТ 4 байта
СПТ 8байт
0-255
-32768 +32767
-2147483648 +2147483647
-3,402823Е38 +3,402823Е38
-1,79769313486232Е308
Целое
Целое
Целое
Десятичное
Десятичное
Значения числовых полей по умолчанию до ввода данных равны нулю.
Форматы даты/времени Отображение
Полный формат даты
Длинный формат даты
Средний формат даты
Краткий формат даты
Длинный формат времени
Средний формат времени
Краткий формат времени
21.07.03 13:05:41
21 июля 2003г.
21-июл-03
21.07.03
13:05:41
01:05
13:05
Функция поиска позволяет перейти к полю, содержимое которого известно (Правка,
Найти). Символы * и ? выполняют те же функции, что и при поиске файлов. Поиск с
заменой выполняется аналогично.
При удалении записей в поле первичного ключа нумерация не изменяется, то есть если
было 5 записей с номерами от 1 до 5, то после удаления третьей и четвертой записей,
оставшиеся будут с прежними номерами 1, 2, 5.
Выбранные вид, размер и начертание шрифта применяются ко всем записям таблицы.
Для компактного отображения полей таблицы часть из них может быть скрыта.
Скрытые поля не участвуют в поиске и обработке запросов (Формат, Скрыть
столбцы/Отобразить столбцы).
Для повышения удобочитаемости таблицы используют фиксацию
маркированных полей (Формат, Закрепить столбцы/Освободить все столбцы). В
результате фиксированное поле помещается в левой части таблицы и остается видимым
при прокрутке полей вправо.
145
Свойства полей Описание
Размер Максимальная длина текстового или числового поля (если
задан слишком большой размер, то неэкономно расходуется
память)
Формат Форма отображения данных:
С разделителями разрядов (пробел между триадами, запятая
между целой и дробной частью и 2 знака после запятой);
Основной (число отображается так, как его ввел
пользователь).
Число десятичных знаков ,ххххх…….
Подпись Выводится рядом с полем в форме или отчете, содержит
пояснение
Значение по умолчанию Пример. Если большинство студентов имеют оценку «5», то
это значение можно установить по умолчанию для всех
студентов. В записях для студентов, имеющих другие
значения оценки, выполняется корректировка.
Условие на значение Устанавливает ограничения на вводимые значения
(например, муж/жен)
Сообщение об ошибке Появляется при вводе недопустимого значения.
Обязательное поле Поле, подлежащее обязательному заполнению.
Индексированное поле Определяет простые индексы для ускорения поиска.
Уменьшение размера поля , в которое вводится данное, ведет к его обрезке по новой
границе.
Поле первичного ключа не позволяет вводить в таблицу повторяющиеся записи,
поскольку содержит однозначный идентификатор для каждой записи. Первичный ключ
определяется в режиме конструктора. Если при выходе из конструктора первичный ключ
не объявлен, Access выдает запрос на его создание и при положительном ответе создает
поле счетчика с именем КОД.
В реляционных БД отдельные таблицы связаны между собой по ключевым
полям. В диалоговом окне Связи необходимо включить опцию Обеспечение целостности
данных, чтобы обеспечить проверку ссылочной целостности связи между таблицами. Это
позволит избежать ошибок при удалении записей из первичной таблицы и вводе
информации в связанную таблицу.
Например:
• добавление в связанную таблицу записей, для которых
отсутствует
соответствующая запись в главной таблице;
• удаление записей в главной таблице, на которые ссылаются записи из
связанной таблицы.
Между двумя таблицами может быть задано только одно отношение, тип которого можно
изменить.
2.3.5.2 Запросы
Наиболее распространенный запрос — запрос на выборку, который выполняет
отбор данных из одной или нескольких таблиц по некоторым критериям. Имя поля можно
перенести в бланки запроса методом Drag and Drop. Бланк запроса называют QBE-
областью (Query by Example — запрос по образцу).
Для выполнения сортировки в окне запроса применяют команду Записи,
Сортировка или аналогичные кнопки на панели инструментов. При алфавитно-цифровой
сортировке по возрастанию вначале располагаются элементы, начинающиеся со знаков
пунктуации или спецсимволов, затем цифры и буквы.
146
В окне запроса можно добавить или изменить данные в таблице, на основе
которых создан запрос. Это может нарушить порядок следования данных в запросе. Для
восстановления порядка необходимо повторить вызов запроса и сортировку данных.
Критерии отбора, устанавливаемые в QBE-области, заключаются в кавычки.
Критерии отбора, расположенные в разных строках, объединяются логическим
оператором ИЛИ; расположенные в одной строке — логическим оператором И. Для
исключения группы данных надо записать критерий НЕ РАВНО «значение» или <>
«значение». Условия И выполняются раньше условий ИЛИ.
В запросах символы * и ? применяются также как и во всех приложениях Microsoft Office
Над полученными в результате запроса данными можно провести вычисления с
помощью функций. Для этого в QBE-области в нужном поле строки Групповая операция
выбирается из списка необходимая функция.
Функция Назначение
Группировка Объединение в одну группу записей с одинаковыми значениями
Count Определение числа записей в группе
Sum Суммирование значений поля в отобранных запросом записях
Min Определение минимального значения в заданном поле группы записей
Max Определение максимального значения в заданном поле группы записей
Avg Определение среднего значения в заданном поле группы записей
First Определение первого значения в заданном поле группы записей
Last Определение последнего значения в заданном поле группы записей
При разработке запроса для нескольких таблиц, между которыми нет связи,
Access рассматривает каждую таблицу как независимый объект и позволяет составить
любую возможную комбинацию из полей в окне конструктора. Обязательное условие
установления связи между таблицами — наличие двух одинаковых полей.
Фильтры используются в тех же целях, что и запросы на выборку данных.
Однако при фильтрации нельзя подавить отображение отдельных полей и выполнить
вычисления.
Обычный фильтр отбирает записи по содержимому нескольких полей (ИЛИ).
Фильтр по выделенному фрагменту предполагает предварительное выделение
нужных данных. При этом можно выбрать команду Фильтр, Исключить выделенное.
Расширенный фильтр создается в режиме конструктора перетаскиванием имен
нужных полей в QBE-область.
Запросы делятся на:
• QBE-запросы по образцу, создаваемые в режиме конструктора запросов;
• SQL-запросы (Structured Query Language — структурированный язык
запросов), создаваемые с помощью операторов и функций языка SQL.
QBE- и SQL-запросы взаимно преобразуемые
Конструктор позволяет сконструировать запрос на выборку самостоятельно.
Простой запрос создается на основе выбранных полей.
Перекрестный запрос предоставляет данные в компактном формате как в
электронных таблицах.
Повторяющиеся записи выбираются из таблицы или простого запроса.
Записи без подчиненных — это записи, не связанные с записями из другой
таблицы.
При составлении запроса на основе нескольких таблиц между ними должны быть
установлены связи, иначе результат обработки запроса может оказаться некорректным.
147
Разработка запроса включает:
• добавление полей в запрос;
• установку критериев отбора записей;
• сортировку записей.
Не следует включать в запрос все поля выбранных таблиц. Если же это
необходимо, то можно выделить символ * в списке полей таблицы и перетащить его в
бланк запроса. Тогда добавление и удаление полей в исходной таблице автоматически
ведет к изменению запроса.
Для удаления поля из бланка запроса используйте клавишу Delete.
Для удаления таблицы из запроса надо ее выделить, щелкнув по полю в верхней части
окна конструктора запроса и нажать Delete.
Запрос может быть использован для выполнения расчетов и подведения итогов на
основе обобщенных данных их исходных таблиц. Для этого предусмотрены
статистические функции SQL, которые задаются в строке Групповая операция. Можно
обработать содержимое каждого поля запроса.
Режим таблицы используется, если надо получить наиболее полный обзор
данных или для эпизодического изменения данных. При частом изменении данных лучше
использовать форму.
2.3.5.3 Формы
Формы используются как средство защиты БД от действий
неквалифицированных пользователей, повышают комфортность работы. Любая форма
строится на основе таблицы или запроса; поля располагаются произвольно. Логически
взаимосвязанные поля объединяются в группы. На основе одной таблицы могут быть
построены разные флрмы для различных работников.
Автоформа использует стандартные шаблоны.
Мастер форм в зависимости от назначения формы предлагает на выбор
стандартные шаблоны и стили оформления.
Конструктор предоставляет инструменты для создания формы на пустом бланке.
Мастер сводных таблиц создает сводную таблицу Excel на основе таблиц и
запросов Access.
Мастер диаграмм создает формы с диаграммами на основе выбранных полей
таблицы. По умолчанию
значения столбца образуют на диаграмме ряд данных, а значения
строки — категорию. Для отображения числовых значений служат маркеры данных —
линии, полосы, секторы и т.п. Метки у маркеров данных представляют числовые или
текстовые значения элементов ряда. Оси используются для отображения шкалы —
диапазона значений рядов данных. Легенда — пояснительный текст и свод условных
знаков. Каждый элемент диаграммы имеет набор параметров, который можно изменить в
соответствующем диалоговом окне, дважды щелкнув на нужном элементе. Круговую
диаграмму рекомендуется использовать для отображения соотношения частей целого.
Точечные диаграммы используют для представления взаимосвязи между двумя или
несколькими величинами. Гистограммы используют для сравнения значений,
относящихся к определенному моменту времени.
Из нескольких связанных таблиц можно создать составную форму. При этом
связанные поля должны иметь одинаковые типы данных. Главная форма включает
содержимое некоторой записи главной таблицы, а подчиненная форма — зависимые
записи подчиненной таблицы. Подчиненная форма встраивается в главную так, что они
обе видны в общем окне. При каждом вызове составной формы обновляются записи
соответствующих таблиц.
Содержимое формы всегда соответствует информации в таблицах и запросах и
обновляется при каждом открытии.
148
Режим редактирования полей формы включается клавишей F2. Удалить запись из
формы можно клавишей Delete.
Для отображения дополнительной информации используются колонтитулы.
Различают страничные колонтитулы (заголовок/примечание страницы) и общие
(заголовок/примечание формы). Они добавляются в верхнее и нижнее поля и удаляются
попарно. При выполнении команды Вид, Заголовок/Примечание формы в окне
конструктора появляются области заголовка и примечания, в которых можно разместить
текст и элементы управления.
2.3.5.4 Отчеты
Преимуществом отчетов перед формами является возможность группировки
данных, что не только улучшает его внешний вид, но и повышает читабельность. Данные
в отчете группируются не более чем по трем полям с возможностью изменения уровня
группировки. В диалоге Интервалы группировки при использовании стандартного
значения Обычный в одну группу объединяются записи с
одинаковыми значениями в
заданном поле. Данные автоматически сортируются по тем же полям, по которым
выполняется группировка. Для подбора оптимальной ширины полей надо включить
опцию Настроить ширину полей для размещения на одной странице.
В нижнем колонтитуле отчета автоматически устанавливается дата его создания.
Структура отчета
Заголовок расположен на первой странице отчета перед верхним
колонтитулом.
Верхний колонтитул по умолчанию помещается на каждую страницу отчета;
обычно содержит заголовки колонок. На страницах, содержащих шапку или резюме
отображение колонтитула можно заблокировать.
Заголовок группы состоит из имени группы и названия Заголовок группы.
Элементы области данных повторяются для каждой выбранной из таблицы
записи.
Примечание группы появляется в конце группы записей; применяется для
указания числа записей или суммы значений в полях группы. Заголовок этой области
включает имя группы и название Примечание.
Нижний колонтитул присутствует на каждой странице отчета; используется для
отображения номера страницы и текущей даты.
Примечание отчета вставляется в конце отчета и содержит резюме ко всему
документу. В проекте отчета
это последняя область, но при печати она идет перед нижним
колонтитулом последней страницы.
По умолчанию все области отчета имеют одинаковую ширину. Изменить высоту
и ширину области одновременно можно перемещением слева маркировочного квадратика,
либо поочередным перемещением ограничительной линии сверху и справа. Точно задать
значение высоты и ширины можно в окне Свойства.
Нумерация страниц отчета выполняется в текстовом поле, добавленном в
заголовки или колонтитулы.
Для размещения отчета в несколько колонок надо установить соответствующие
параметры на вкладке Столбцы из меню Файл, Параметры страницы.
Параметры отображения номеров страниц
Выражение в поле свойства Данные Результат
=[Page]
=”Стр.”& [Page]
=[Page]&”/3 стр.”
=[Page]&”из 3 стр.”
=”Стр.”&[Page]&”/из 3”
=Format([Page]”000”)
1,2,3…
Стр.1, Стр.2,…
1/3 стр., 2/3 стр.,…
1 из 3 стр.,…
Стр.1 из 3,…
001, 002, 003