Лабораторные работы
«Работа с базами данных в MS Access 2007»
Подготовила А.А. Коробецкая
Лабораторный практикум содержит 5 частей, охватывающих базовые аспекты создания и обработки баз данных MS Access 2007.
К сдаче предоставляется база данных (файл mdb) и отчет о проделанной работе (файл docx). Результат лабораторного практикума служит основой для курсового проекта.
Содержание
Задание на лабораторный практикум. 2
Л.р. 1. Создание и заполенение базы данных. 3
Л.р. 2. Сортировка, фильтрация и поиск в таблице. 9
Л.р. 3. Создание запросов. 11
Л.р. 4. Создание форм. 23
Л.р. 5. Создание отчетов. 31
Варианты заданий. 42
Задание на лабораторный практикум
Вариант индивидуального задания выдается преподавателем.
1. Разработать структуру базы данных (схема БД и описание таблиц).
2. Заполнить базу данных.
3. Осуществить сортировку, фильтрацию и поиск по таблицам.
4. Выполнить в базе данных запросы на выборку:
– запрос на выборку полей одной таблицы;
– запрос на выборку полей двух таблиц;
– запрос на выборку с различными критериями отбора;
– запрос с вычисляемыми полями;
– запрос с итогами;
– запрос с параметром;
– перекрестный запрос.
5. Создать в базе данных формы:
– автоформы в один столбец, табличную и ленточную;
– составные формы подчиненную и связанную.
6. Создать в базе данных отчеты:
– автоотчет в один столбец;
– отчет по нескольким полям одной таблицы;
– отчет с группировкой и итогами.
Результаты отражаются в отчете по лабораторной работе. Отчет создается по разработанному в первом семестре шаблону. Отчет должен содержать:
1. Титульный лист.
2. Задание (общее и индивидуальное).
3. Результат выполнения каждого из пунктов 1-6:
o Схема данных
o Описание полей таблиц
o Заполненные таблицы
o Скриншоты результатов сортировки, фильтрации и поиска с указанием их параметров
o Запросы и результаты их вызова
o Скриншоты форм (в режиме просмотра)
o Отчеты из базы данных
Как добавить в отчет те или иные результаты, указано в методичке.
Пример индивидуального задания
Создать базу данных «Продажи товаров». В базе данных хранятся сведения о товарах (артикул, наименование, цена, количество, единицы измерения), о магазинах (наименование, город, адрес, телефон, фио директора), о продажах (№ чека, дата, товар, кол-во магазин), о наличии товаров в магазинах (товар, остаток на складе). Один товар может присутствовать в нескольких магазинах, в одном магазине продается много товаров. В одном чеке может быть много товаров, каждый товар может продаваться по разным чекам.
Л.р. 1. Создание и заполенение базы данных
1. Изучите описание БД в своем индивидуальном задании. Попробуйте изобразить примерную схему данных (на бумаге). При необходимости уточните задание у преподавателя. Предложите варианты справочных таблиц.
На схеме необходимо указать таблицы и связи между ними, кратность связей. Если на схеме есть связи m-n (многие-ко-многим), их необходимо преобразовать к 1-n (один-ко-многим), введя вспомогательные таблицы.
Окончательный вариант схемы для примера:
В отчет схему вставлять не нужно.
2. Создайте в MS Access новую базу данных (пустую). Сохраните ее на локальный диск в формате mdb.
MS Access является локальной СУБД. Вся база данных хранится в одном файле (формат mdb).
Не рекомендуется работать с БД, расположенной на съемном носителе или в сети. Это замедляет работу и увеличивает риск повреждения БД.
3. Создание таблиц осуществляется через «Создание», группа кнопок «Таблица». С таблицами можно работать в двух основных режимах:
«Таблица » – ввод и редактирование данных.
«Конструктор » – формирование структуры таблице, определение полей, их типов и свойств.
По умолчанию база данных содержит одну таблицу «Таблица1». Переименуйте ее в соответствии со своим вариантом.
Введите названия полей, укажите их типы. Подберите подходящий размер поля.
1. Названия таблицы и полей всегда пишутся в ед. числе.
2. В названиях допускаются буквы (русские не всегда), цифры, знаки - и _.
3. Если у нескольких таблиц есть поля с одинаковыми названиями, их желательно называть по-разному, например, Код_магазина, Код_города.
Укажите ключевое поле или поля. При необходимости добавьте поле «Код» в качестве ключа.
Для ключей-кодов часто используется тип «Счетчик». Это целочисленное поле, в которое при добавлении новой записи автоматически вводится следующее по порядку число. Таким образом, значения счетчика никогда не повторяются. Редактировать их вручную нельзя. Никакого смысла счетчик обычно не несет, и служит только для связывания таблиц, чтобы не использовать в качестве ключа поля с длинными значениями.
Аналогично создайте остальные таблицы.
В рассматриваемом примере можно добавить ключи «Код» типа «Счетчик» в таблицы «Продажа» и «Наличие». Ни одно из полей этих таблиц не является уникальным. Использование нескольких полей одновременно возможно, но сильно загружает базу данных.
С другой стороны, ключи этих таблиц не участвуют в связях, поэтому создание ключа из нескольких полей не является ошибкой.
4. Схема данных создается через вкладку «Работа с базами данных» – «Показать или скрыть» – «Схема данных». Сами таблицы должны быть закрыты.
Добавьте на схему все таблицы БД. Разместите их на поле и добавьте связи.
Связь добавляется перетаскиванием поля одной таблицы к полю другой. Кратность связи задается автоматически. Типы данных связываемых полей должны совпадать (счетчик можно связать с длинным целым).
В свойствах связи установите флажок «Каскадное обновление » и снимите флажок «Каскадное удаление ». Каскадное удаление может привести к серьезному повреждению базы при неосторожном удалении связанных записей.
Добавьте в отчет по первой работе скриншот схемы данных. Он должен быть хорошо читаемым.
Схема данных для примера.
5. Настройка таблиц.
Там, где это возможно, задайте в свойствах полей:
– уникальность значений (могут ли значения поля повторяться в разных записях);
– значение по умолчанию;
– шаблон ввода, например для даты __.__.20__;
– условия на значения, например, количество >=0;
– выпадающий список для выбора одного из постоянных значений или подстановки значения из справочника;
– другие свойства.
Для полей, которые участвуют в связях лучше использовать подстановку значений из списка. Это значительно упрощает ввод данных и позволяет избежать ошибок.
Список может содержать значения, введенные вручную, либо из другой таблицы или запроса.
Например, в таблицу «Наличие» код магазина и артикул товара желательно брать из соответствующих таблиц.
Удобнее всего то, что в списке может отображаться не только ничего не значащий столбец «Код», но и другие – «Наименование» и т.п.
Пример настройки подстановки значений в поле «Магазин» таблицы «Наличие»:
Обратите внимание на ширину столбцов: первый столбец (собственно, «Код_магазина») имеет ширину 0. Он вообще не будет отображаться в списке, и даже в самом поле. Хотя реально в БД будет храниться именно код.
Результат в режиме таблицы:
Если установить ширину первого столбца равной 1см, то таблица будет выглядеть так:
Можно построить и сложные запросы для выбора данных. Например, в таблице «Продажа» указывать не все товары, а только имеющиеся в наличии в данном магазине. Построение запросов рассматривается в л.р.3.
Пример подстановки из списка, введенного вручную, для поля «Ед_измерения» таблицы «Товар»:
Здесь выбран тип элемента управления «Поле со списком», а не «Список», а свойство «Только значения источника» выставлено в «Нет». Это значит, что можно использовать и единицы измерения, не указанные в списке.
6. Добавление описания таблиц в отчет. Выполняется полуавтоматически, через «Работа с базой данных» – «Архивариус».
Установите «Параметры», как показано на скриншоте. Отметьте флажками все таблицы.
Откроется окно с перечнем таблиц и их полей. Выполните экспорт в MS Word (формат rtf). Удалите лишние строки, разрывы, настройте оформление шрифта и абзаца, и добавьте результат в отчет. Выделите жирным ключевые поля.
Результат для примера:
Таблица: Магазин
Имя Тип Размер
Код Длинное целое 4
Название Текстовый 20
Нас_пункт Текстовый 15
Адрес Текстовый 255
Телефон Текстовый 15
Директор Текстовый 255
Таблица: Наличие
Имя Тип Размер
Товар Длинное целое 4
Магазин Длинное целое 4