Ключевые поля.Связывания таблиц.

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

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

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

Пример. Рассмотрим связь между двумя таблицами «Покупатель» и «Заказы» в базе данных Trade.mdb. Структура каждой таблицы представлена на рис. 4.10.

В таблице «Покупатель» ключевым полем является поле Код покупателя. Данное поле является счетчиком и содержит уникальные значения для каждой записи этой таблицы. Поле данной таблицы назовем первичным ключом.

Если каждый покупатель имеет право сделать только один заказ, то в таблице «Заказы» ключевым полем может являться аналогичное первой таблице поле-счетчик Код покупателя. В таблице «Заказы» ключевое поле Код покупателя будем называть внешним ключом.

В этом случае тип связи, установленной между первичным и внешним ключом, называют связью один к одному. Этот тип связи представлен на рисунке 4.11

Если в таблице «Заказы» один покупатель имеет право сделать несколько заказов, то поле Код покупателя уже не будет уникальным, так как может повторяться многократно. В этом случае тип данных в этом поле может принимать числовые значения, а ключевым полем с уникальными значениями может быть определено поле Номер заказа. В этом случае тип связи, установленной между одноименными полями Код покупателя в обеих таблицах, называют связью один ко многим, как показано на рисунке 4.12.

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

 

Для создания Схемы данных в СУБД Access необходимо выполнить следующее:

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

2. Щелкните мышью по значку Схема данных или выполните цепочку команд Сервис®Схема данных на панели Стандартная.

3. Выделите первую таблицу, для которой устанавливается связь в окне Добавление таблицы. Для примера эта таблица - «Покупатель».

4. Щелкните мышью на кнопке Добавить. На экране будет отображена таблица, которую мы добавили, как показано на рисунке 4.13. Затем таким же образом требуется добавить в Схему данных остальные связываемые таблицы, например «Заказы» и щелкнуть мышью по кнопке Закрыть.

 

5. В окне первой таблицы щелкните мышью на поле, которое будете использовать для установки связи, например Код покупателя и перетащите его на совпадающее поле второй таблицы.

 

6. В окне Изменение связей убедитесь, что связаны необходимые поля и установите флажок Обеспечение целостности данных, затем нажмите кнопку Создать

В результате выполненных действий в окне Схема данных два связанных поля соединятся линией, как показано на рисунках 4.11 и 4.12.

В дальнейшем для сохранения созданных связей необходимо щелкнуть мышкой по кнопке Сохранить и закрыть окно Схема данных.

Созданные связи в дальнейшем можно удалить в окне Схема данных, щелкнув мышью по линии связи, чтобы выделить ее, а затем нажать клавишу Delete (Удалить). Двойной щелчок мышью по линии связи приведет к открытию окна Изменение связей, в котором связи могут быть отредактированы.

Ключевое поле — это одно или несколько полей, комбинация значений которых однозначно определяет каждую запись в таблице. Если для таблицы определены ключевые поля, то Microsoft Access предотвращает дублирование или ввод пустых значений в ключевое поле. Ключевые поля используются для быстрого поиска и связи данных из разных таблиц при помощи запросов, форм и отчетов.

В Microsoft Access можно выделить три типа ключевых полей: счетчик, простой ключ и составной ключ. Рассмотрим каждый из этих типов.

Для создания ключевого поля типа Счетчик необходимо в режиме Конструктора таблиц:

Включить в таблицу поле счетчика.

Задать для него автоматическое увеличение на 1.

Указать это поле в качестве ключевого путем нажатия на кнопку Ключевое поле (Primary Key) на панели инструментов Конструктор таблиц (Table Design).

Если до сохранения созданной таблицы ключевые поля не были определены, то при сохранении будет выдано сообщение о создании ключевого поля. При нажатии кнопки Да (Yes) будет создано ключевое поле счетчика с именем Код (ID) и типом данных Счетчик (AutoNumber).

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

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

Открыть таблицу в режиме Конструктора.

Выделить поля, которые необходимо определить как ключевые.

Нажать кнопку Ключевое поле (Primary Key) на панели инструментов Конструктор таблиц

 

Запросы и их применение.

Microsoft Access объединяет сведения из разных источников в одной реляционной базе данных.

Для поиска и отбора данных, удовлетворяющих определенным условиям создается запрос. С помощью запросов можно просматривать, анализировать и изменять данные из нескольких таблиц, выполнять встроенные или специальные вычисления. Запросы также используются в качестве источника данных для форм и отчетов.

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

 

1. Создание простого запроса Откроем базу данных STUD, как было рассмотрено в ЛР1. Создадим запрос, выводящий информацию о студентах, у которых имеются задолженности. Для создания запроса активизируем окно базы данных (см. ЛР1). После этого щелкнем по корешку "Запрос" и кнопке "Создать". В появившемся диалоговом окне "Создание запроса" выберите кнопку "Конструктор". Access откроет диалоговое окно "Добавление таблицы", позволяющее выбрать базовые таблицы и запросы для создаваемого запроса. Выберите таблицы "Результаты" и "Cтуденты", нажимая кнопку "Добавить", после чего закройте это окно кнопкой "Закрыть". Открывшееся окно конструктора запросов состоит из двух частей: верхняя содержит списки полей выбранных таблиц, а нижняя - бланк QBE для создания запроса. Каждый столбец бланка описывает одно поле, участвующее в запросе. Включение поля в запрос производится перетаскиванием его из списка полей таблиц (расположенного в верхней части экрана) в нужный столбец бланка QBE при помощи мышки. Включение всех полей таблицы происходит перетаскиванием символа "*", находящегося вверху списка полей данной таблицы в верхней части экрана. Включите в запрос поля таблицы "Результаты": Предмет Номер_С перетащив их в бланк QBE. Рассмотренным выше методом перетащите из таблицы “Студенты” поле Фамилия.

 

Запрос “Задолженность” будет иметь вид, как показано на рисунке. Установка связей между таблицами производится автоматически, используя структуру связей, созданную при генерации проекта БД. Можно задавать другие варианты связи таблиц и иные типы связей (внешнее объединение, "один-к-одному", "один-ко-многим" и т.д.). Изменение связей производится в верхней части окна выбором связанного поля в описании одной таблицы и транспортировкой его при нажатой кнопке мышки на описание соответствующего поля связанной таблицы. Тип связи можно изменить, активизировав ее щелчком мышки на линии связи. Условие для отбора нужных нам полей производится включением этого условия для данного поля в строке QBE "Условие отбора". Несколько значений отбора вводятся в одну строку, разделенные логическими условиями AND или OR, либо вводятся в последующие ячейки строки "или". Установим для таблицы "Результаты" условие отбора студентов, у которых задолженности, для чего в столбец описания поля внесем строку "Задолженность" из таблицы "Результаты", и в строке "Условие отбора" введем: Да Сбросим для этого поля флажок "Показать", запрещающий вывод данного поля в выборке (т.к его не обязательно выводить). Кроме стандартных операторов сравнения '=', '<', '>', '<=', '>=', Access поддерживает также BEETWEN, IN, LIKE. В запросе могут присутствовать т.н. вычисляемые поля - вычисленные значения над любыми полями таблицы. Access поддерживает большое число функций и операторов, применяемых при генерации вычисляемых полей. Чтобы просмотреть результат запроса необходимо на панели нажать кнопку:

Создадим запрос, в котором будет вычисляться дополнительное поле. Кафедра вычислительной техники решила своим работникам к новому году выплатить премию в размере 10% от зарплаты. Создадим запрос на базе таблицы “Преподаватели”, с полями Фамилия, Кафедра, Должность, Зарплата. Чтобы создать вычисляемое поле “Премия”, выводящее информацию о величине премии работнику, в новом столбце (следующим за заполненным), в строке “Поле” введем выражение Зарплата*0,1. Изменим заголовок поля, введя перед выражение его название. Ячейка будет содержать: «Премия: [Зарплата]*0,1». Изменить заголовок можно также активизировав столбец с описанием поля, а затем выполнив команду «Свойства» меню «Вид», ввести в строку подпись поля его название. Введите в строку «условие отбора» для кафедры «Вычислительная техника».

1.2. Создание итогового запроса Итоговые запросы позволяют получать статистическую информацию по группам данных, как например: сумму, среднее арифметическое, мат.ожидание, дисперсию значений полей таблицы; число значений полей, удовлетворяющих определенным условиям и т.д. Создадим запрос среднего бала студента. Откроем новый бланк запроса, внесем в него таблицу "Студенты", “Результаты” и установим вывод полей "Номер_С", "Фамилия", "Оценка_Т", "Оценка_П", “Оценка_Л”. Для вычисления в запросе итоговых значений щелкнем по кнопке "Групповые операции" панели инструментов. В бланке QBE появится строка "Групповая операция", где для каждого описанного поля в этой строке установлено по умолчанию "Группировка". Теперь записи по каждому полю будут группироваться, но итог подводиться не будет. Для выведения итоговых значений необходимо вместо слова "Группировка" установить какую-нибудь групповую операцию (см прил.1). Для полей “Оценка_Т”, “Оценка_П”, “Оценка_Л” операцию "Avg", выводящую среднее значение поля для группы записей (получим средний бал). Изменим также заголовки полей выводимой таблицы. Для этого в описании полей в строке "Поле" введем их новые названия, отделяя их от описания двоеточием: Оценка_Т: Теория Оценка_П: Практика Оценка_Л: Лабы Часть выражения, расположенная до двоеточия выводится после запуска запроса как заголовок столбца, вместо выводимого Access по умолчанию. Таким способом можно формировать более понятные заголовки полей, чем генерируемые базой. Сохраним полученный запрос под именем "Итог". Запустите запрос и проанализируйте полученные результаты. 1.3. Создание запроса с параметром Встречаются задачи, в которых параметры запросов заранее неизвестны, или есть необходимость в их изменении. Для этого в Access предусмотрена возможность ввода параметров запросов непосредственно перед их исполнением. Чтобы установить ввод параметра вместо конкретного значения, в строке "Условие отбора" вводятся имя или фраза, заключенные в квадратные скобки. Все, введенное в квадратных скобках Access рассматривает как имя параметра, и выводится в диалоговом окне запроса как комментарий в вводимому параметру. В запросе можно использовать несколько параметров, но с уникальными именами. Создадим запрос, выводящий список преподавателей работающих на задаваемой пользователем кафедре. За основу возьмем таблицу "Преподаватели". Выберем в бланк вывода пункт "Преподаватели.*", выводящий все поля таблицы "Преподаватели”. Введем еще раз описание поля “Кафедра”, сбросим для него флажок вывода на экран, и в строке "Условие отбора" этого поля введем выражение: =[Введите название кафедры] Сохраним полученный запрос как "Выборка преподавателей по кафедрам". Выполнив запуск запроса, мы увидим диалоговое окно, запрашивающее название кафедры.

После ввода параметра Access выводит список преподавателей, местом работы которых является запрашиваемая кафедра. Ввод параметров можно использовать при любом типе запросов: итоговом, перекрестном, запросе-действии и т.д. 1.4. Создание перекрестного запроса Access поддеpживает специальный тип итоговых запpосов, называемый перекрестным. Перекрестный запрос выводит вычисленные значения как функцию от существующих полей. Так в Access можно вывести запрос, описывающий суммарный зароботок по должностям. Необходимое требование к созданию перекрестного запроса наличие поля - заголовка строки, поля - заголовка столбца, и одно поле вычисляемого значения. Поля, являющиеся заголовками строк и столбцов должны иметь в строке "Групповая операция" установку "Группировка". Для поля - значения необходимо выбрать в "Групповой операции" какую-либо групповую функцию, или выражение, содержащее групповую операцию. Для нашего запроса для таблицы "Преподаватели" в меню "Запрос" установим указатель "Перекрестный". Access добавит в блок QBE строку "Перекрестная таблица". Затем в бланк перенесем поле "Должность", установим “заголовки столбцов”. Следующее поле-"Кафедра”, установим “заголовки строк” Результирующее поле- “Зарплата”, для него установим “значение” "групповая операция" - Avg, Сохраним полученный запрос под имеем "Зарплаты по должностям". 1.5. Создание запроса типа "внешнее объединение" Еще одним типом специальных запросов является внешнее объединение, которое применяется в многотабличных запросах. При использовании запросов данного типа итоговый набор записей формируется не только при условии совпадения связанных полей базовых таблиц, но и из записей одной таблицы, не имеющих соответствия в другой. Создадим запрос, выводящий информацию обо всех студентах, с задолженностями и без. В этом запросе будет использоваться внешнее объединение таблиц "Студенты" и "Результаты" по совпадению поля "Номер_С" со всеми записями таблицы "Студенты". Для создания внешнего объединения необходимо изменить параметры объединения. Откроем запрос "Задолженность" в режиме конструктора. Щелкнем по линии связи между таблицами "Студенты" и "Результаты" для открытия диалогового окна "Параметры объединения". По умолчанию в этом окне установлен тип объединения "выведение только тех записей, для которых совпадают связанные поля". Другие типы объединение позволяют выводить "все записи главной таблицы и только те записи дочерней, для которых совпадают значения связанных полей" и "все записи дочерней и только те записи главной, для которых связанные поля совпадают".

Выберем третий тип связи и щелкнем на кнопке "Ок". После этого на линии соединения таблиц появится стрелка, указывающая на внешнее объединение. Сохраним модифицированный запрос (через “Сохранить как” ("Save as.."), чтобы не затереть предыдущий запрос) под именем "Внешнее объединение по студентам".