Тема 4.2 субд ms access

I. Задания для самостоятельной работы

1. Ознакомиться со структурой СУБД MS Access.

2. Какие объекты входят в состав базы данных, созданной в СУБД MS Access?

3. Что составляет основу базы данных?

4. Что такое запрос, и какие разновидности запросов применяются в СУБД MS Access?

5. Создание БД со связанными таблицами. Технология применения первичных и вторичных ключей

6. Для чего используются формы и отчеты.

II. Планы практических занятий

I занятие (2 ч)

1. Структура СУБД MS Access: конструктор таблиц, конструктор запросов, мастер форм, мастер отчетов.

2. Объекты базы данных: таблицы, запросы, формы, отчеты.

3. Понятие запроса и разновидности запросов.

4. .Проектирование и заполнение БД «Страны».

II занятие (2 ч)

1. Создание запросов на выборку для БД «Страны»: запрос-сортировка, запрос по критерию, запрос с параметром, запрос на вычисление.

2. Создание Форм и Отчетов.

3. Применение операторов Like, Between And.

III занятие (2 ч)

1. Самостоятельное создание БД «Склад», «Зарплата».

2. Обсуждение видов запросов, созданных для работы с базами данных.

IV занятие (2 ч)

1. Изучение новых видов запросов на изменение исходной таблицы: запросы на удаление и обновление.

2. Создание БД «Спортивные сведения о студентах» с возможностью удаления студентов 5-х курсов и обновления номеров курсов с 1 по 4.

3. Добавление в БД «Склад» запросов на изменение количества товаров.

V занятие (2 ч)

1. Понятие итоговых запросов и групповых операций.

2. Копирование задачи 9 из MS Excel и создание итоговых и других видов изученных запросов. Применение групповых операций для подсчета ГТД по таможням, годам и месяцам.

VI занятие (2 ч)

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

2. Создание БД «Моя библиотека» с двумя таблицами АВТОР и КНИГА.

3. Связывание таблиц через Схему данных.

4. Построение Составной формы.

VII занятие (2 ч)

1. Перепроектирование БД «Страны» на основе правил нормализации.

2. Изучение метода связывания таблиц при создании запроса.

3. Создание БД «Показатели по таможням» с использованием всех приемов работы и видов запросов.

III. Рекомендации по выполнению заданий и подготовке

к практическим занятиям

Создание базы данных

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

Для того, чтобы открыть уже существующую БД нужно выбрать команду ОТКРЫТЬ из меню ФАЙЛ и в открывшемся диалоговом окне выбрать нужную базу данных. При необходимости вначале следует выбрать диск и каталог, где находится MDB-файл.

Для создания новой базы данных из меню ФАЙЛ следует выбрать команду СОЗДАТЬ, в открывшемся окне выбрать Новую базу и в открывшемся диалоговом окне ввести имя файла и нажать ОК.

После открытия или создания файла БД на экране появляется окно, посредством которого осуществляется управление отдельными объектами БД, такими как таблицы, запросы, формы, отчеты, макросы и модули. В данном курсе рассматриваются только первые 4 объекта.

Чтобы создать таблицу, в ОКНЕ базы данных выбрать вкладку ТАБЛИЦА и щелкнуть по строке СОЗДАНИЕ ТАБЛИЦЫ В РЕЖИМЕ КОНСТРУКТОРА, с помощью которого задается структура таблицы.

В режиме КОНСТРУКТОР создается макет таблицы, т.е. заносятся имена полей, а их тип выбирается из списка открывающегося меню. После создания таблицы ее можно заполнять или редактировать (изменять) в режиме ТАБЛИЦА.

Запросы

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

Любой запрос создается с помощью КОНСТРУКТОРА ЗАПРОСОВ следующим образом:

1. Запустить КОНСТРУКТОР ЗАПРОСОВ, при этом откроются пустой бланк запроса и окно со списком таблиц или одной таблицей.

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

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

Сортировка записей

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

Для нужного поля в строке бланка запросов СОРТИРОВКА выберите способ сортировки (по возрастанию или по убыванию).

Запрос с критериями поиска

В окне базы данных следует выбрать ЗАПРОС и запустить КОНСТРУКТОР ЗАПРОСОВ. В следующем окне диалога следует выбрать таблицу(ы), на основе которой(ых) будет строиться запрос и нажать кнопку ЗАКРЫТЬ. 

Теперь можно выбрать одно или несколько полей и отбуксировать их в бланк запроса. Нужные поля можно выбирать также в каждой первой строке запроса. Проследите за тем, чтобы был выбран флажок ВЫВОД НА ЭКРАН. 

В строке УСЛОВИЕ ОТБОРА можно ввести теперь критерии выбора.  Можно сохранить запрос для последующего использования.

Запросы с параметрами

Откройте окно запроса и добавьте в него таблицу. Создайте запрос, отбуксировав необходимые поля в бланк запроса и задав условия выбора. Затем в качестве условия в строке бланка запроса УСЛОВИЕ ОТБОРА введите параметр, заключенный в квадратные скобки. При выполнении запроса Access отображает на экране окно с названием этого параметра и ожидает ввода. Ввод параметра является основной частью выполнения запроса. Например, если необходимо рассмотреть только книги, изданные до определенного момента времени, тогда для поля ГОД  ИЗДАНИЯ следует ввести условие: <[ГОД ИЗДАНИЯ]. Теперь для выполнения запроса сначала необходимо ввести значения параметра ГОД ИЗДАНИЯ, а результат запроса будет содержать только те книги, которые удовлетворяют заданному значению параметра.

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

Для других типов параметров необходимо установить точно его тип. Для этого выберите команду ПАРАМЕТРЫ из меню ЗАПРОС и в диалоговом окне ПАРАМЕТРЫ ЗАПРОСА введите параметр ГОД ИЗДАНИЯ без квадратных скобок и в качестве типа выберите ЦЕЛОЕ.

Вычисляемые поля в запросах

Создание вычисляемого поля осуществляется путем простого введения выражения для вычисления в пустом столбце бланка запроса. Так, например, общая стоимость товара на складе вычисляется при помощи следующего выражения: [ЦЕНА]*[НА СКЛАДЕ]. Вам необходимо составить соответствующий запрос с использованием имен полей. После того, как ввод будет завершен, выражение будет иметь следующий вид: ВЫРАЖЕНИЕ1: [ЦЕНА]*[НА СКЛАДЕ]. В результате запроса присутствует новое поле, в котором отображается новая цена. Выражение строится с помощью мастера (в контекстном меню опция "Построить").

Итоговые запросы

Итоговые запросы значительно отличаются от обычных. В них можно использовать два типа полей:

• поля, по которым осуществляется группировка данных, и

• поля, для которых проводятся вычисления; 

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

Для того, чтобы составить итоговый запрос, находясь в режиме КОНСТРУКТОРА запроса, следует выбрать ГРУППОВЫЕ ОПЕРАЦИИ  или воспользоваться соответствующей командой из меню ВИД. 

В результате чего в бланке запроса появится строка "Групповая операция". Затем для соответствующего поля из списка следует выбрать функцию "Группировка".

Если требуется узнать количество товаров на складе, имеющих одинаковую категорию, например, "Молочные продукты", то в первый столбец бланка запроса следует поместить поле КАТЕГОРИЯ. Для этого столбца нужно задать функцию Группировка, в результате чего все записи будут сгруппированы по категориям товара.

Затем необходимо поместить поле КОЛИЧЕСТВО во второй столбец бланка запроса. Для этого столбца следует установить функцию Sum (Сумма) из списка Групповая операция. Результатом запроса будет таблица, содержащая информацию о количестве товаров на складе, имеющих одинаковую категорию.

Запросы удаления

Удаление большого количества записей вручную с помощью команды УДАЛИТЬ из меню ПРАВКА занимает очень много времени. Пользователь может составить запрос удаления, который автоматически удаляет все записи, удовлетворяющие некоторому критерию.

В режиме конструктора запроса выберите таблицу, в которой позднее будет производиться удаление. Затем определите поле, по которому будут удаляться записи. После этого выберите команду УДАЛЕНИЕ в меню ЗАПРОС. После появления строки УДАЛЕНИЕ, выберите в ней УСЛОВИЕ и в строке УСЛОВИЕ ОТБОРА запишите критерий для удаления. Окно закрыть и запустить запрос. Посмотрите результат выполнения запроса, отображаемый в режиме таблицы.

Запросы замены

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

Сначала создайте запрос выбора и введите критерии для выбора изменяемых записей. Затем выберите команду ОБНОВЛЕНИЕ из меню ЗАПРОС. В строку ОБНОВЛЕНИЕ следует ввести новые выражения для заменяемых записей. Обратите внимание, что тип данных выражения должен совпадать с типом данных поля исходной таблицы.

Например, можно задать запрос замены для цены товара. Выберите команду ОБНОВЛЕНИЕ в меню ЗАПРОС и затем поместите поле ЦЕНА в бланк запроса. В качестве критерия введите выражение <20. В строке ОБНОВЛЕНИЕ введите следующее выражение: [Цена]*0,5. При выполнении запроса отбираются товары, цены на которые составляют менее 20 рублей и увеличиваются в 0,5 раза. В диалоговом окне содержится информация о том, сколько записей будет заменено.

Выводы

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

По окончании создания каждого запроса следует закрыть окно конструктора и задать имя новому запросу.

Связывание таблиц

База данных хорошо спроектирована в том случае, если каждая запись в любой таблице однозначно идентифицируется. Это означает, что значение некоторого поля (или нескольких полей) не повторяется ни в одной записи в таблице. Такой идентификатор называется первичным ключом (или просто ключом). Значения первичного ключа уникальны, т.е. не повторяются.

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

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

IV. Рекомендуемые источники

Основная литература

1. Кудрявцева, Л. Б. Информатика : учеб. пособие / Л. Б. Кудрявцева. – Ростов н/Дону : Российская таможенная академия, Ростовский филиал, 2003

2. Соболь, Б. В. Информатика : учебник / Б. В. Соболь, А. П. Галин, Ю. В. Панов. – Ростов н/Д : Феникс, 2006 (Раздел 4, тема 4.4).

Дополнительная литература

1. Вейскас, Д. Эффективная работа с MS ACCESS 2000 / Д. Вейскас. – СПб. : BPV-Санкт-Петербург, 2001 (Части 1 – 5).

2. Кудрявцева, Л.Б. Информатика : учеб. пособие [Электронный ресурс] / Л. Б. Кудрявцева. – Режим доступа : учебный сервер Ростовского филиала Российской таможенной академии, 2008(Раздел «Системы управления базами данных», тема «СУБД MS Access»).

V. Контрольные вопросы для самопроверки

Проверьте свои знания, ответив на следующие вопросы.

1. Назовите последовательность действий при проектировании базы данных в MS Access.

2. Что обязательно вносится в Конструктор запросов для создания запроса?

3. Чем отличаются запросы с параметром и по критерию?

4. В чем сходство и различие запросов на вычисление и итоговых?

5. Какое минимальное количество таблиц необходимо для создания составной формы?

6. Какие объекты входят в состав базы данных, созданной в СУБД MS Access?

7. Как вывести на принтер информацию, полученную после запроса?

8. Какими способами можно связывать таблицы, чтобы использовать поля из связанных таблиц?