Реализация приложения базы данных в СУБД Access
Таблицы
Access хранит данные в таблицах. Таблицы являются основной частью каждой БД. Проектирование таблиц в Access можно выполнять с помощью мастера таблиц и конструктора.
5.1.1 Конструирование таблиц. Мастер таблиц
Конструирование таблицы можно выполнять как с помощью Конструктора, так и Мастера Таблиц.
Первое поле данных таблицы всегда обеспечивает однозначную идентификацию данных, находящихся в строке. Типичными названиями таких ключевых полей являются, например, “Код_кафедры”, “Код _преподавателя”. В конструкторе таблиц они всегда расположены первыми в поле списка Образцы полей. При создании таблицы в Мастер таблиц ключевое поле можно установить автоматически или самостоятельно.
5.1.2 Конструктор таблиц
Конструктор таблиц предназначен для создания или изменения, ранее созданных таблиц, описания их структуры. Созданную или сконструированную таблицу можно либо открыть для ввода данных, либо для изменения. Для ввода данных в таблицу надо нажать кнопку Открыть на закладке Таблицы главного окна БД. Для внесения изменений в структуру ранее созданной таблицы нажать кнопку Конструктор на той же закладке. Access открывает таблицу в режиме конструктора с перечислением структурных составляющих таблицы, т.е. перечисляются имена полей активной таблицы, типы данных и их описание:
Рисунок 5.1 - Таблица в режиме Конструктора
Имена полей располагаются одно под другим. В таблице имена полей должны быть уникальными, в именах полей запрещено использование символов знаков препинания и скобок. Тип данных можно ввести или выбрать из раскрывающегося списка типов данных. Список можно вызвать, если щёлкнуть курсором мышки в конце ячейки Тип данных соответствующего поля. Список содержит следующие типы: Текстовый, Поле Мемо, Числовой, Дата/Время, Денежный, Счетчик, Логический, Поле объекта OLE, Гиперссылка, Мастер подстановок. В каждом поле должна храниться информация только одного типа.
Наиболее часто используется тип данных Текстовый, это текст или числа, не требующие проведения расчетов (число символов не более 255). Тип Мемо следует выбрать для хранения больших текстов (до 65535 символов). Числовой тип - данные, используемые для проведения расчетов. Счётчик - уникальные последовательно возрастающие (на 1), автоматически вводящиеся при добавлении каждой новой записи в таблицу. Значения полей типа счетчика обновлять нельзя. Поле объекта OLE - объект (например, электронная таблица Excel, документ Word, рисунок, другие данные в двоичном формате), связанный или внедренный.
Описание поля носит характер комментария разработчика.
В нижней части окна конструктора таблицы приведены Свойства поля на двух закладках: Общиеи Подстановка(рисунок 5.1), в них отображаются дополнительные характеристики текущего поля. Так, например, для текстового поля можно объявить длину этого поля, для денежных - количество разрядов после запятой, для телефонных номеров - маску ввода: 999-99-99. При создании таблиц особое внимание необходимо обратить на следующие свойства:
Свойство Условие на значениеопределяет требования к данным, вводящимся в запись, в поле или в элемент управления.
Свойство Сообщение об ошибкепозволяет указать текст сообщения, выводящегося на экран, если введенные данные нарушают условие, определенное в свойстве
Свойство Обязательное поле определяет обязательность ввода данных в это поле .
Свойство Пустые строки используется для разрешения ввода в данное поле пустых строк
Свойство Индексированное поле (Indexed) используется для поиска и сортировки записей по одному полю таблицы. Это свойство определяет индекс, задаваемый по одному полю. Индекс ускоряет выполнение запросов, в которых используются индексированные поля и операции сортировки и группировки. Например, если часто выполняется поиск по полю «ФИО» в таблице «Преподаватели», следует создать индекс для этого поля. Поле может содержать уникальные или повторяющееся значения. Например, в таблице «Преподаватели» можно создать индекс по полю «код_преподавателя», которое содержит уникальные значения кода или по полю «должность», которое может содержать повторяющиеся значения.
Свойство Индексированное поле (Indexed) может иметь следующие значения:
Значение | Описание |
Нет | Индекс не создается (по умолчанию). |
Да (Допускаются совпадения) | В индексе допускаются повтор. значения. |
Да (Совпадения не допускают) | Повтор. значения в индексе не допускаются. |
Первое поле таблицы, как правило, является ключевым. Чтобы установить ключевое поле, необходимо выбрать соответствующее поле, а затем кнопку Определить ключ на панели инструментов. Содержимое поля с первичным ключом дает однозначную идентификацию записи.
Поля с типами Мемо или поля объекта OLE не могут быть первичными ключами.
5.1.3 Установление связей между таблицами
Установление связи между таблицами выполняется в окне Схема данных из меню Сервис. Для того, чтобы установить связь следует добавить связываемые таблицы в окно Схема данных: в этом окне вызвать контекстное меню, выбрать пункт Добавить таблицы, затем из списка имеющихся таблиц отметить те, которые надо поместить в поле окна Схема данных. Установление связи между таблицами выполняется буксировкой ключевого поля одной таблицы на связываемое поле в другой таблице, внутри окна Схема связи. Тип создаваемой связи зависит от полей, для которых определяется связь. На рис. 5.2 приведен вид схемы реляционной базы данных.
Рисунок 5.2 - Вид схемы реляционной базы данных
Запросы
Запросы обеспечивают быстрый и эффективный доступ к данным, хранящимся в таблице. Применение запросов дополняет возможности таблиц Access: разрешает использовать вычисляемые поля, сортирует записи таблицы согласно указанному ключу (в таблице они сортируются по первичному ключу) и пр..
На основании запроса можно разработать форму (отчет). Это не отличается от процесса создания формы (отчета) на основе таблицы.
5.2.1 Создание запросов
Для создания запроса в окне базы данных следует выбрать кнопку Создать и далее в окне Новый запрос выбрать режим его создания: Конструктор, Простой запрос, Перекрёстный запрос, Повторяющиеся записи, Записи без подчинённых.
Режим Конструктор на первом шаге создания запроса требует указать, какие добавить таблицы в бланк запроса. Список созданных пользователем таблиц выводится. Количество добавляемых в запрос таблиц определяет пользователь из соображений решаемого вопроса, т.е. из каких таблиц можно получить данные по поставленному вопросу. Выбранные в бланк запроса таблицы необходимо связать линиями связи через одноимённые поля, выбрав подходящий тип связи. Связь устанавливается буксировкой поля из одной таблицы в другую. Если схема связи для таблиц была установлена заранее, то для выбираемых таблиц автоматически добавляются связи.
5.2.2 Итоговые запросы
При необходимости проанализировать данные на поиск статистических функций, для каких либо данных можно воспользоваться итоговыми запросами. В итоговых запросах используются два типа полей:
· | · Поля, по которым осуществляется группировка данных; |
· | · Поля, для которых проводятся вычисления. |
Дополнительно можно включить еще поля для определения условий.
Чтобы составить итоговый запрос, находясь в режиме конструктора запроса, следует выбрать Вид/Групповые операции. В результате чего в бланке запроса появиться строка Групповые операции. Затем для соответствующего поля вызвать раскрывающийся список функций итоговых расчетов (щёлкнув курсором мышки в строке групповые операции бланка запроса), в нём необходимо выбрать функцию “Группировка”. Например, если имеются отношения ПРЕПОДАВАТЕЛИ с их атрибутами и ШТАТНОЕ_РАСПИСАНИЕ, то можно подсчитать средние оклады по каждой кафедре. Для этого группировка выполняется по полю код_кафедры, а вычисление функции AVG (среднего) для поля оклад.
5.2.3 Запрос к связанным таблицам
Такие запросы создают, если в одном запросе необходимо обработать информацию одновременно из нескольких таблиц. При этом автоматически учитывается связь между таблицами.
Откройте окно конструктора запроса и добавьте таблицы, в которых содержится необходимая информация. Если ранее с помощью команды Схема данных была установлена связь между таблицами, то Access определит это автоматически. Эта связь будет отображена в виде линии, проведенной между полями таблиц с обозначением вида связи (например, 1:). Если связь между таблицами отсутствует, необходимо ее установить.
Вначале необходимо определить параметры связи. Укажите на соединительную линию и выберите команду Вид/Параметры объединения. В результате откроется диалоговое окно, в котором предлагаются три опции для определения параметров связи. Выберите подходящую из опций и нажмите ОК.
5.2.4 Запросы удаления
Удаление записей вручную с помощью команд Правка/Удалить занимает много времени, а также сопровождается часто ошибками. Для автоматического удаления группы записей создается запрос на удаление.
В режиме конструктора запроса выберите таблицу, в которой будет производиться удаление, в бланке запроса установите критерии (условия) отбора записей для удаления. Затем просмотрите результат выполнения запроса, отображаемый в режиме таблицы. Лишь в том случае, если в результате запроса присутствуют только подлежащие удалению записи, можно преобразовать запрос выбора в запрос удаления.
Для этого в строке меню выбрать Запрос/Удаление. После этого Access выключает в бланке запроса строки “Сортировка” и “Вывод на экран”. Для запросов удаления эти строки не нужны. Кроме того, для всех колонок бланка запроса в новой строке “Удаление” отображается текст “Условие”. Однако критерии при этом не меняются.
Если выполнить запрос выбором кнопки Выполнить, то Access не отобразит на экране результат выполнения запроса. Вместо него появиться сообщение о том, сколько записей будет удалено в исходной таблице. Можно прервать процесс удаления нажатием кнопки Отмена. Нажатие кнопки ОК приводит к безвозвратному удалению записей, удовлетворяющих критериям запроса.
Инструкция DELETE
Назначение: создание запроса на удаление записей из одной или нескольких таблиц, перечисленных в предложении FROM, которые удовлетворяют условию WHERE.
Пример
Удалить записи о преподавателях, которые занимают должность «доцент» и не имеют ученой степени (код_учен_степени=0). При этом в схеме данных необходимо при обеспечении целостности установить каскадный режим удаления для связей таблицы Преподаватели.
DELETE Преподаватели.*, Преподаватели.должность, Преподаватели.код_учен_степени
FROM Преподаватели
WHERE (((Преподаватели.должность)="доцент") AND ((Преподаватели.код_учен_степени)=0));
5.2.5 Запросы на обновление
При необходимости выполнения одинаковых замен воспользуйтесь запросом на обновление. Для этого выберите Запрос/Обновление. При этом будут выключены строки “Вывод на экран” и “Сортировка” в бланке запроса. Вместо них включается новая строка “Обновление”. В эту строку следует ввести новые выражения для заменяемых записей. Обратите внимание, что тип данных выражения должен совпадать с типом данных поля исходной таблицы. Замена выполняется аналогично запросу на удаление - сначала выдается предупреждение, что имеется некоторое количество записей, удовлетворяющих условию отбора, при дальнейшем выборе кнопки ОК обновление будет выполнено.
Инструкция UPDATE
Назначение: создание запроса на обновление записей, который изменяет значение полей указанной таблицы на основе заданного условия отбора.
Пример.
Увеличить в штатном расписании на 10 % оклады преподавателей, имеющих разряды ниже 15.
UPDATE Штат_расписание SET Штат_расписание.оклад = [оклад]*1.1
WHERE (((Штат_расписание.разряд)<15));
5.2.6 Перекрестные запросы
Такие запросы предназначены для отображения данных итоговых запросов, когда данные сгруппированы по нескольким полям.
Выберите из меню команду Запрос/ Перекрестный и перейдите в режим SQL для написания инструкции перекрестного запроса.
Инструкция TRANSFORM
Назначение: создание перекрестного запроса (запрос, возвращающий данные в виде электронной таблицы, используя указанные поля как заголовки строк и столбцов, и способный возвращать итоговые данные). Перекрестный запрос позволяет просматривать данные в форме сводной таблицы, более компактной чем представление, даваемое запросом на выборку.
Пример
Создать перекрестный запрос, показывающий распределение суммарной часовой нагрузки каждой кафедры по семестрам. Номера семестров должны определять заголовки столбцов слева направо, а названия кафедр – заголовки строк сверху вниз.
TRANSFORM Sum(Дисциплина.количество_часов) AS [Cумма_часов_за_семестр]
SELECT Кафедры.кафедра
FROM Кафедры INNER JOIN (Преподаватели INNER JOIN (Дисциплина INNER JOIN ведет ON Дисциплина.дисциплина = ведет.дисциплина) ON Преподаватели.код_преподавателя = ведет.код_преподавателя) ON Кафедры.код_кафедры = Преподаватели.код_кафедры
GROUP BY Кафедры.кафедра
PIVOT Дисциплина.семестр;
Формы
Формы являются наиболее удобным средством отображения данных в Access. Преимущество формы для ввода и редактирования данных состоит в наглядности, так как записи таблицы или запроса представлены в удобном графическом виде. В форме можно создать необходимые для решения своих задач элементы управления: кнопки, списки, флажки и т.д.
5.3.1 Режимы для создания форм
Создавать новые формы можно в разных режимах: можно воспользоваться автоформами в столбец, ленточной, таблицей или мастером форм, или конструктором. Режим создания формы выбирается из предлагаемого списка режимов на первом шаге создания формы.
5.3.2 Режим конструктора для создания форм
Перейти в режим конструктора можно, воспользовавшись кнопкой Вид. Конструктор формы состоит из трёх областей:
- заголовка (для размещения заголовков и примечания формы);
- колонтитулов (верхний, нижний с примечанием);
- данных (для непосредственного отображения данных таблицы).
Создание формы в конструкторе предполагает знание элементов управления формы.
Общие сведения об элементах управления форм
Все сведения в форме содержатся в элементах управления. Элементы управления - это объекты формы или отчета, которые служат для вывода данных на экран или оформления формы или отчета.
В Access можно создать следующие типы элементов управления:
- поле,
- надпись,
- группа,
- переключатель,
- флажок,
- выключатель,
- поле со списком,
- кнопка,
- рисунок,
- подчиненная форма,
- свободная рамка объекта и пр.
Например, поле можно использовать для вывода данных на экран в форме или отчете, кнопку - для открытия другой формы или отчета, а линию или прямоугольник - для разделения и группировки элементов управления с тем, чтобы они лучше воспринимались пользователем.
Основные возможности конструктора определяются составом инструментов Панели элементов (Toolbox), показанной на рисунке 5.3 (версия Access 2000-2002), а также Окном свойств (рисунок 5.4)
Рисунок 5.3 - Форма в режиме Конструктора