Ирина Эланс

Автор который поможет с любыми образовательными и учебными заданиями

Microsoft Access - функционально полная реляционная СУБД

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

Microsoft Access - это функционально полная реляционная СУБД. В ней предусмотрены все необходимые вам средства для определения и обработки данных, а также для управления ими при работе с большими объемами информации. Что касается легкости использования, то Microsoft Access совершил здесь настоящий переворот, и многие для создания своих собственных баз данных и приложений обращаются именно к нему.

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

2.2. Предназначение СУБД Access

СУБД Access предназначена для разработки баз данных реляционного типа для локального их использования на персональных компьютерах и для работы с этими базами.

При проектировании базы данных, в первую очередь, необходимо определить, что именно нужно хранить.

Данная СУБД была выбрана по следующим причинам:

простота средств реализации,
легкость освоения инструментарием разработчика (VBA),
наглядность визуализации информации.

Также «Microsoft Access» предоставляет большое количество внутренних средств по оптимизации работы проектируемого приложения. К ним относятся:

загрузка модулей по требованию;
оптимизация дерева вызовов;
использование файлов MDE;
автоматическая поддержка компилированного состояния;
использование библиотек Windows API;
индивидуальная настройка системы;
эффективное использование индексов;
встроенный оптимизатор запросов.

Система управления базами данных (СУБД) обычно поддерживает 4 основных типа отношений между таблицами:

- один-к-одному (одной записи в первой таблице соответствует одна запись во второй);

- один-ко-многим (одной записи в первой таблице соответствует много записей во второй);

- много-к-одному (многим записям в первой таблице соответствует одна запись во второй);

- много-ко-многим (одной записи в первой таблице соответствует много запией во второй и одной записи во второй таблице соответствует много записей в первой).

Связь любого из этих типов может быть обязательной, если в данной связи должен участвовать каждый экземпляр сущности, необязательной – если не каждый экземпляр сущности должен участвовать в данной связи. При этом связь может быть обязательной с одной стороны и необязательной с другой стороны.

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

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

Взаимосвязи таблиц

При создании в Access схемы данных в ней определяются и запоминаются связи между таблицами. Это позволяет системе автоматически использовать связи, один раз определенные в схеме данных, при создании форм, запросов, отчетов на основе взаимосвязанных таблиц, а пользователь освобождается от необходимости указывать эти связи при конструировании этих объектов. Схема данных базы графически отображается в своем окне, где таблицы представлены списками полей, а связи - линиями между полями разных таблиц.

Одно-многозначные (1:М) или одно-однозначные (1:1) связи. Схема данных прежде всего ориентирована на работу с таблицами, отвечающими требованиям нормализации, между которыми могут быть установлены одно-многозначные (1:М) или одно-однозначные (1:1) связи, для которых может автоматически поддерживаться связная целостность. Поэтому схему данных целесообразно строить в соответствии с информационно-логической моделью.

При построении схемы данных Access автоматически определяет по выбранному полю связи тип отношения между таблицами. Если поле, по которому нужно установить связь, является уникальным ключом как в одной таблице, так и в другой, Access выявляет отношение один-к-одному. Если поле связи является уникальным ключом в одной таблице (главной таблицы связи), а в другой таблице (подчиненной таблице связи) является не ключевым или входит в составной ключ, то есть значения его могут повторяться, Access выявляет отношение один-ко-многим между записями главной таблицы к подчиненной. В этом случае можно задать автоматическое поддержание целостности связей.

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

Однако если рассматривать взаимосвязь таблиц с двух сторон, становится очевидным, что ни одна из таблиц не может быть главной и для их связывания необходима третья таблица.

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

Связи-объединения. Между двумя таблицами может быть установлена связь-объединение по некоторому полю связи. Для связи-объединения может быть выбран один из трех способов объединения записей:

Способ 1 - объединение только тех записей, в которых связанные поля обеих таблиц совпадают (производится по умолчанию);
Способ 2 - объединение тех записей, в которых связанные поля обеих таблиц совпадают, а также объединение всех записей из первой таблицы, для которых нет связанных во второй, с пустой записью второй таблицы;
Способ 3 - объединение тех записей, в которых связанные поля обеих таблиц совпадают, а также объединение всех записей из второй таблицы, для которых нет связанных в первой, с пустой записью первой таблицы.

Такой тип связи может быть определен, если связь характеризуется отношением 1:1 или 1:М, а также если тип отношения не может быть определен системой, то есть если не выполняются условия для этих отношений. Например, при выборе в главной таблице в качестве поля связи неключевого поля или поля, входящего в составной ключ, Access сообщает, что тип отношения не может быть определен. В этом случае между таблицами возможно установление только связи-объединения.

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

Создание схемы данных

Создание схемы данных начинается в окне Базы данных (Database) с выполнения команды Сервис|Схема данных (Tools|Relationships) или нажатия кнопки Схема данных (Relationships) на панели инструментов базы данных.

Включение таблиц в схему данных. После нажатия кнопки Схема данных (Relationships) открывается окно Добавление таблицы (Show Table) , в котором можно выбрать таблицы и запросы, включаемые в схему данных. Для размещения таблицы в окне Схема данных (Relationships) надо выделить ее в окне Добавление таблицы (Show Table) и нажать кнопку Добавить (Add). Для выделения нескольких таблиц надо, удерживая клавишу , щелкнуть мышью на каждой из этих таблиц. Включив все нужные таблицы в схему данных, нажать кнопку Закрыть (Close).

В результате в окне Схема данных (Relationships) будут представлены все включенные таблицы со списком своих полей. Далее можно приступать к определению связей между ними.

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

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

При создании связи по составному ключу необходимо выделить все поля, входящие в ключ главной таблицы, и перетащить их на одно из полей связи в подчиненной таблице. Для выделения всех полей, входящих в составной уникальный ключ, необходимо отмечать поля при нажатой клавише . После создания связи откроется окно Изменение связей (Edit Relationships). При этом в строке Тип отношения (Relationship Type) автоматически установится тип один-ко-многим (One-To-Many).

При составном ключе связи в окне Изменение связей (Edit Relationships) необходимо для каждого поля ключа в главной таблице ТАБЛИЦА/ЗАПРОС (Table/Query) выбрать соответствующее поле подчиненной таблицы, названной СВЯЗАННАЯ ТАБЛИЦА/ЗАПРОС (Related Table/Query).

Обеспечение целостности данных

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

Обеспечение связной целостности данных означает, что Access при корректировке базы данных обеспечивает для связанных таблиц контроль за соблюдением следующих условий:

В подчиненную таблицу не может быть добавлена запись с несуществующим в главной таблице значением ключа связи;
В главной таблице нельзя удалить запись, если не удалены связанные с ней записи в подчиненной таблице;
Изменение значений ключа связи в записи главной таблицы невозможно, если в подчиненной таблице имеются связанные с ней записи.

При попытке пользователя нарушить эти условия в операциях добавления и удаления записей или обновления ключевых данных в связанных таблицах Access выводит соответствующее сообщение и не допускает выполнения операции.

Установление между двумя таблицами связи типа 1:М или 1:1 и задание для нее параметров целостности данных возможно только при следующих условиях:

Связываемые поля имеют одинаковый тип данных, причем имена полей могут быть различными;
Обе таблицы сохраняются в одной базе данных Access;
Главная таблица связывается с подчиненной по первичному простому или составному ключу (уникальному индексу) главной таблицы.

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

Каскадное обновление и удаление связанных записей

Если для выбранной связи обеспечивается поддержание целостности, можно задать режим каскадного обновления связанных полей и режим каскадного удаления связанных записей.

В режиме каскадного обновления связанных полей при изменении значения поля связи в записи главной таблицы, Access автоматически изменит значения в соответствующем поле в подчиненных записях.

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

При удалении записей непосредственно в таблице или через форму выводится предупреждение о возможности удаления связанных записей.

Язык SQL (аббревиатура Structured Query Language) – это язык структурированных запросов, стандартный язык, предназначенный для создания баз данных, добавления новых и поддержки имеющихся данных, а также извлечения требуемой информации. Язык SQL с самого начала был создан, чтобы работать с данными из тех баз, которые следуют реляционной модели.

Каждому запросу MS Access можно сопоставить эквивалентную инструкцию SQL. В MS Access пользователи, знакомые с языком SQL, могут использовать его для просмотра и изменения запросов в режиме конструктора, определения свойств форм и отчетов, создания специальных запросов (запросы объединения, запросы к серверу и управляющие запросы), создания подчиненных запросов.

При создании каждого запроса MS Access автоматически составляет эквивалентную ему инструкцию SQL. Изменения, внесенные в инструкцию SQL, автоматически отражаются в бланке конструктора.

Просмотрим или изменим инструкцию SQL:

1. Выполнив анализ предметной области, создадим групповой запрос «Список студентов группы ДФД-31» на основании таблиц «Студент» и «Группа» (рис. 21), в который включим список следующих полей таблиц:

Группа.[Обозначение группы], Студент.[Номер зачетной книжки], Студент.Фамилия, Студент.Имя, Студент.Отчество, Студент.Год рождения, Студент.Адрес, Студент.[Домашний телефон], Студент.[Балл при поступлении].

В поле Группа.[Обозначение группы] используем условие отбора по коду группы: «ДФД-31».

Групповой запрос «Список студентов группы ДФД-31» в MS Access в режиме конструктора

2. Выберем Режим SQL в меню Вид или через кнопку «Режим SQL» на панели инструментов. На экране появится текущий запрос в режиме SQL эквивалентный созданному в режиме конструктора (рис. 22).

Запрос «Список студентов группы ДФД-31» в MS Access в режиме SQL

3. В этот запрос можно внести изменения, и эти изменения будут отражены в бланке конструктора. Например, попробуем удалить в текстовом окне режима SQL записи Студент.Адрес, Студент.[Домашний телефон], Студент.[Балл при поступлении].

Результат выполнения измененного запроса «Список студентов группы ДФД-31»

Основной инструкцией языка SQL, всегда содержащейся в запросе, является команда SELECT [2].

В простейшей форме эта команда занимается поиском информации в таблице. Она имеет следующий формат:

SELECT field1, field 2, …

FROM Table;

Здесь field1, field 2,… – список столбцов таблицы Table, которые должны быть представлены в результате запроса.

Для получения всей таблицы вместо списка столбцов необходимо поставить символ «*» (звездочка).

Команда SELECT имеет следующие параметры:

A. DISTINCT (получить список без повторений)

Формат: SELECT DISTINCT field1, field2, …

FROM Table;

B. ALL (получить список со всеми повторениями)

Формат: SELECT ALL field1, field2, …

FROM Table;

C. WHERE (извлечь нужные строки)

Формат: SELECT field1, field2, …

FROM Table WHERE predicate;

Здесь predicate – логическое выражение, которое может быть истинно или ложно для каждой записи таблицы.

D. ORDER BY (рассортировать выходные данные)

Формат: SELECT field1, field2, …

FROM Table

ORDER BY field1 DESC;

Это означает, что выходные данные будут рассортированы по столбцу field1 в порядке убывания (порядок возрастания задается по умолчанию или с помощью слова ASC).

E. GROUP BY (группировать выходные данные)

Формат: SELECT field1, field2, …

FROM Table

GROUP BY [field1, field2, …]

ORDER BY field1 DESC;

Группировка – это объединение записей в соответствии со значениями некоторого заданного поля.

Агрегатные функции

Существуют следующие основные агрегатные функции:

Count – определение численности;
Sum – определение суммы;
First/Last – определение первого/последнего значения;
Min/Max – определение минимума/максимума;
Avg – определение среднего значения.

Для обозначения связи двух таблиц дополнительно к команде FROM используются атрибуты INNER JOIN и ON.

Так как в сложном запросе появляется несколько таблиц, то появляется необходимость указывать поле с обозначением таблицы, например, см. рис. 22.

С помощью атрибута INNER JOIN мы указали, что таблица «Студент» связана с таблицей «Группа». А с помощью атрибута ON мы указали, как именно связаны между собой две таблицы «Студент» и «Группа»: по полю Код группы (FROM Группа INNER JOIN Студент ON Группа.[Код группы] = Студент.[Код группы]).

С помощью атрибута WHERE мы указали, что нужно извлечь только строки, которые содержат запись в поле Группа.[Обозначение группы] «ДФД-31»: WHERE (((Группа.[Обозначение группы])="ДФД-31")).

ПРИМЕЧАНИЕ. Обратите внимание на то, что в качестве имени поля всегда используется то имя, которое было присвоено полю в процессе создания таблицы в режиме конструктора, а не надпись, которую мы видим на экране в таблице в режиме заполнения.

4. Запрос может быть создан также только исключительно через Режим SQL в конструкторе.

Выберем в окне базы данных «Запрос», «Создать» через опцию «Конструктор». В диалоговом окне «Добавление таблицы» выберем опцию «Закрыть». В меню Вид выберите Режим SQL. Появится окно «Запрос на выборку». Наберем следующую инструкцию SQL:

Эта инструкция предназначена для получения списков всех предметов и их кодов. Данные для этого запроса берутся из таблицы «Предмет». Результатом выполнения данного запроса будет таблица, состоящая из двух полей (Наименование предмета и Код предмета) и из всех записей таблицы «Предмет».

Результат запроса для получения списков всех предметов и их кодов

Так как в запросе используется только одна таблица, то нет необходимости указывать поле с обозначением таблицы. Очевидно, что запрос выполняется на основании таблицы «Предмет».

Рассмотрим запрос «План проведения занятий в группе», созданный на основании анализа предметной области:

SELECT Группа.[Обозначение группы], Предмет.[Наименование предмета], Преподаватель.Фамилия, Преподаватель.Имя, Преподаватель.Отчество, Преподаватель.[Табельный номер], [Учебный план].Часы, [Учебный план].[Вид занятия], [Учебный план].Семестр

FROM (Группа INNER JOIN Студент ON Группа.[Код группы] = Студент.[Код группы]) INNER JOIN (Преподаватель INNER JOIN (Предмет INNER JOIN ([Учебный план] INNER JOIN Успеваемость ON [Учебный план].[Код учебного плана] = Успеваемость.[Код учебного плана]) ON Предмет.[Код предмета] = [Учебный план].[Код предмета]) ON Преподаватель.[Код преподавателя] = [Учебный план].[Код преподавателя]) ON Студент.[Код студента] = Успеваемость.[Код студента]

GROUP BY Группа.[Обозначение группы], Предмет.[Наименование предмета], Преподаватель.Фамилия, Преподаватель.Имя, Преподаватель.Отчество, Преподаватель.[Табельный номер], [Учебный план].Часы, [Учебный план].[Вид занятия], [Учебный план].Семестр

ORDER BY Группа.[Обозначение группы], Предмет.[Наименование предмета], Преподаватель.Фамилия;

Результатом будет следующий запрос на выборку

Аналогично, следует подготовить запрос «Экзаменационная ведомость», созданный на основании анализа предметной области (см. рис. 5):

SELECT Предмет.[Наименование предмета], Группа.[Обозначение группы], Студент.[Номер зачетной книжки], Студент.Фамилия, Студент.Имя, Студент.Отчество, Успеваемость.Оценка, Успеваемость.[Дата сдачи], [Учебный план].Семестр, [Учебный план].[Вид сдачи]

WHERE ((([Учебный план].[Вид сдачи])="экзамен" Or ([Учебный план].[Вид сдачи])="Зачет"))

ORDER BY Предмет.[Наименование предмета], Группа.[Обозначение группы], Студент.Фамилия;

Рассмотрим для примера запрос «Успеваемость студентов в группах по предметам у преподавателей». Запишем текст запроса на языке SQL в окно Режим SQL:

SELECT Группа.[Обозначение группы], Студент.[Номер зачетной книжки], Студент.Фамилия, Студент.Имя, Студент.Отчество, Преподаватель.Фамилия, Преподаватель.Имя, Преподаватель.Отчество, Предмет.[Наименование предмета], Avg(Успеваемость.Оценка) AS [Avg-Оценка]

GROUP BY Группа.[Обозначение группы], Студент.[Номер зачетной книжки], Студент.Фамилия, Студент.Имя, Студент.Отчество, Преподаватель.Фамилия, Преподаватель.Имя, Преподаватель.Отчество, Предмет.[Наименование предмета]

Microsoft Access - функционально полная реляционная СУБД