Система управления базами данных Microsoft Access 2007

Размещено на http://www.allbest.ru/

3

Размещено на http://www.allbest.ru/

Федеральное агентство по образованию

Государственное образовательное учреждение Высшего профессионального образования

«Санкт-Петербургский государственный инженерно-экономический университет»

Система управления базами данных Microsoft Access 2007

Методические указания

по выполнению лабораторных работ

Для студентов всех специальностей

Санкт-Петербург

2008

Допущено редакционно-издательским советом СПбГИЭУ в качестве методического издания

Составители

канд. экон.наук, доц. Е.Б. Попов

Подготовлено на кафедре вычислительных систем и программирования

Одобрено научно-методическим советом СПбГИЭУ

Отпечатано в авторской редакции с оригинал-макета, представленного составителем

СПбГИЭУ, 2008

Содержание

Введение

1. Лабораторная работа № 1. Таблицы

2. Лабораторная работа № 2. Запросы

3. Лабораторная работа № 3. Формы

4. Лабораторная работа № 4. Отчеты

Список литературы

Введение

Методические указания по выполнению лабораторных работ ориентированы на современное программное средство «Microsoft Access», входящее в состав «Microsoft Office».

Access 2007 представляет собой реляционную базу данных. Данные представляют собой набор фактов; они превращаются в полезную информацию лишь после того, как будут упорядочены каким-то разумным способом. Access представляет собой инструмент такого упорядочения.

1. Лабораторная работа № 1. Таблицы

Цель работы: Ознакомление с основными понятиями СУБД Access на примере учебной базы данных компании «Борей». Освоение технологии конструирования и связывания реляционных таблиц.

Краткие сведения

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

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

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

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

Запросы создаются для выборки данных из одной или нескольких связанных таблиц.

Формы предназначены для ввода, редактирования и просмотра табличных данных на экране в удобном виде.

Страницы доступа к данным представляют специальный тип веб-страниц, предназначенный для просмотра и работы через Интернет или интрасеть с данными, хранящимися в базах данных Microsoft Access или в базах данных Microsoft SQL Server.

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

Макросы используются для автоматизации различных процедур обработки данных, являются программами, состоящими из макрокоманд высокого уровня. Макропрограммирование в Access не требует знания языка Visual Basic. Имеющийся в Access набор из около 50 макрокоманд обеспечивает практически любые действия, необходимые для решения задач.

Модули являются программами на языке Visual Basic, которые служат для реализации нестандартных процедур обработки данных.

Все данные БД Microsoft Access и средства их отображения хранятся в одном файле с расширением MDB.

Задание 1. Ознакомьтесь с учебной базой данных компании «Борей», входящей в комплект поставки Microsoft Access.

Технология

Загрузите Microsoft Access.

Установите низкий уровень безопасности. Для этого выполните команду Office/Параметры Access/ Центр Управления Безопасностью./Параметры Центра Управления Безопасностью.

На вкладке Параметры макросов включите переключатель Включить все макросы.

Загрузите Учебную базу данных Борей. Для этого Office / Учебная база данных. Нажмите Northwind. Откроется Борей.

ЕЕсли НЕ откроется, это означает, что Ваша версия Microsoft Access не прошла проверку на подлинность. В этом случае переходите сразу к разделу Таблицы, (стр. 8).

Закройте заставку и перейдите в окно базы данных и щелкните по типу объектов: таблицы. Просмотрите данные каждой таблицы, открыв их.

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

Откройте таблицу «Клиенты». Выполните следующие операции:

Определите количество записей в таблице;

Просмотрите 45-ую запись, введя ее номер в окно номеров записей.

Рассортируйте таблицу по должностям. Для этого установите указатель мыши на заголовок столбца и щелкните правой кнопкой мыши. Столбец будет выделен и появится контекстное меню. Выберите в контекстном меню пункт Сортировка по от А до Я или Сортировка от минимального к максимальному.

Скройте столбец Обращаться к, выделив его и выполнив команду щелкните правой кнопкой мыши. Столбец будет выделен и появится контекстное меню. Выберите в контекстном меню пункт Скрыть столбцы Отобразите скрытый столбец. Для этого выполните команду щелкните правой кнопкой мыши Отобразить столбцы затем поставьте «галочку» у скрытого столбца.

Используя клавишу Shift выделите первые два столбца и закрепите их, выполнив команду щелкните правой кнопкой мыши Закрепить столбцы. Прокрутите таблицу по горизонтали. Отмените закрепление, выполнив команду щелкните правой кнопкой мыши Освободить все столбцы.

Примените фильтр для выделения строк с клиентами в г.Лондон. Для этого выделите в любой строке поле со значением «Лондон» и вызовите контекстное меню. Выберите пункт Текстовые Фильтры введите Лондон. Либо в контекстном меню Равно «Лондон».. Отмените фильтр, щелкнув в контекстном меню по пункту Снять фильтр.

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

Просмотрите запросы и их структуру

откройте запрос на выборку товаров с ценой выше средней;

переключите запрос в режим конструктора и просмотрите структуру запроса.

Просмотрите формы:

«Сотрудники», вкладки «Служебные данные», «Личные данные»;

«Товары»;

«Типы»;

Просмотрите форму «Сотрудники» в режиме конструктора.

Просмотрите отчеты:

«Каталог»;

«Продажи по типам»;

«Суммы продаж по годам»;

«Счет»;

«Продажи по сотрудникам и странам», введя дату начала: 01.01.1998 и дату окончания: 31.12.1998.

Просмотрите многостраничный отчет «Каталог» в режиме конструктора.

Закройте базу данных Борей, щелкнув по кнопке Закрыть в окне базы данных.

Таблицы

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

Таблица - это объект БД, который хранит данные определенной структуры. Таблица состоит из записей (строк), каждая из которых описывает одну сущность. Каждый столбец таблицы - это поле. Столбец содержит однотипную информацию.

Длина имени таблицы - не более 64 символов.

Длина имени поля - не более 64 символов.

Количество полей в одной таблице - не более 255.

Количество записей - неограниченно.

Суммарный объем информации во всей БД - не более 1 гигабайта.

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

Таблица может содержать следующие типы полей (всего 8):

Текстовый. Короткий текст. Текст и числа, например, имена и адреса, номера телефонов и почтовые индексы. Текстовое поле может содержать до 255 символов. Текст, введённый в такие поля, можно форматировать.

Поле Memo. Длинный текст и числа, например, комментарии и пояснения. Memo-поле может содержать до 65536 символов.

Числовой. Общий тип для числовых данных, допускающих проведение математических расчетов, за исключением расчетов для денежных значений. Свойство Размер поля позволяет указать различные типы числовых данных. Длина - 8 байт. Точность - 15 знаков.

Дата/время. Значения даты и времени. Пользователь имеет возможность выбрать один из многочисленных стандартных форматов или создать специальный формат. Длина - 8 байт.

Денежный. Денежные значения. Числа представляются с двумя знаками после запятой. Не рекомендуется использовать для проведения денежных расчетов значения, принадлежащие к числовому типу данных, так как последние могут округляться при расчетах. Значения типа "Денежный" всегда выводятся с указанным числом десятичных знаков после запятой. Длина - 8 байт.

Счетчик. Автоматически вставляющиеся последовательные номера. Счетчик увеличивается на единицу для каждой следующей записи. Нумерация начинается с 1. Поле счетчика удобно для создания ключа. В таблице может быть только одно такое поле. Длина - 4 байта.

Логический. Значения "Да"/"Нет", "Истина"/"Ложь", "Вкл"/"Выкл", т.е. одно из двух возможных значений. Длина - 1 байт.

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

Гиперссылка. Поле, в котором сохраняются адреса гиперссылок, позволяющих переходить к файлам, фрагментам файлов или веб-страницам. Гиперссылка может иметь вид пути UNC либо адреса URL. Сохраняет до 64 000 знаков.

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

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

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

Связи между таблицами. Таблицы могут быть связаны отношениями один-к-одному, один-ко-многим и многие-к-многим. Access позволяет использовать только отношения первых двух типов.

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

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

Наиболее часто используются отношения один-ко-многим. В этом случае одной записи в главной таблице соответствует несколько записей в подчиненной таблице.

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

Добавление таблиц

Работа с таблицами в Office Access 2007 также была упрощена. Теперь можно создавать таблицу с помощью вкладки Create (Создание); при этом можно выбрать самостоятельное создание таблицы, либо с помощью шаблона таблицы (Contacts [Контакты], Tasks [Задачи], Issues [Вопросы], Events [События] или Assets [Основные фонды]). Шаблон таблицы содержит поля, наиболее общие для выбранного типа таблицы. Например, если создается таблица Contacts (Контакты), в ней будут присутствовать столбцы. First Name (Имя), Last Name (Фамилия), E-mail (Адрес электронной почты). Job Title (Должность) и т.д.

При выборе команды Table (Таблица) и затем пункта Table (Таблица) открывается новая вкладка Table (Таблица) в представлении Datasheet View (Режим таблицы), в котором можно немедленно начать ввод информации, без необходимости настраивать типы данных. Office Access 2007 автоматически распознает тип данных.

Добавление полей

Самый простой способ добавить поле в Office Access 2007 - начать ввод в последнем столбце. Дважды щелкните на заголовке, чтобы присвоить полю имя. Чтобы использовать шаблоны полей, применяемых в других таблицах или шаблонах таблиц, откройте область задач Field List (Список полей). Область задач Field List (Список полей) замещает инструмент выбора полей и представляет поля из других таблиц в базе данных. Просто перетащите нужные поля из списка в редактируемую таблицу, и Office Access 2007 автоматически создаст необходимые связи.

Соответствующая команда находится на вкладке Create (Создание) в контекстной вкладке Form Tools (Работа с макетами форм) в наборе команд Controls (Элементы управления). Выберите Add Existing Fields (Добавить существующие поля), чтобы отобразить список Field List (Список полей), и добавьте нужные поля, перетащив их в таблицу.

Рекомендации для ввода данных в таблицы

Для ввода в поле текущей записи значения из того же поля предыдущей записи нажать клавиши <Ctrl> и <“>. (Двойной апостроф на русском регистре - на клавише “2”).

Для редактирования ранее введенного значения нажимайте клавишу F2.

Задание 2. Создайте базу данных «Академия» на основе инфологической модели, приведенной на рисунке. База данных должна содержать 4 взаимосвязанных таблицы: Студент, Группа, Специальность и Факультет.

Таблица Студент:

N зачетной книжки - ключевое поле, длинное целое

N группы - числовое поле, целое

ФИО - текстовое поле 15 символов

Дата рождения - поле типа «дата»

Коммерческий - логическое поле (вкл/выкл)

Таблица Группа:

N группы - ключевое поле числового типа, целое

N специальности - числовое поле, двойное целое

- N факультета - числовое поле, байтовое

- Курс - числовое поле, байтовое

Размещено на http://www.allbest.ru/

3

Размещено на http://www.allbest.ru/

Таблица Факультет:

N факультета - ключевое поле числового типа, байтовое

Наименование факультета - текстовое поле, 30 символов

Декан - текстовое поле, 15 символов

Таблица Специальность:

N специальности - ключевое поле числового типа, двойное целое

Наименование специальности - текстовое поле, 40 символов

Стоимость обучения - денежного типа.

Командные вкладки

Открыв программу, вы сразу обратите внимание на новые вкладки Office Access 2007. Теперь командные вкладки выстроены в упрощенной последовательности: Ноте (Главная), Create (Создание), External Data (Внешние данные) и Database Tools (Работа с базами данных). В различных меню находятся следующие элементы.

¦ Вкладка Ноте (Главная) содержит наборы команд, предназначенных для смены представлений, а также для добавления, форматирования, фильтрации и поиска записей в базе данных.

¦ Вкладка Create (Создание) содержит инструменты, необходимые для разработки и добавления таблиц, а также списков, форм, отчетов, запросов и макросов Office SharePoint 2007.

¦ Вкладка External Data (Внешние данные) (см. рис. 9-4) содержит инструменты для импорта и экспорта информации в Office Access 2007, Office Excel 2007, текстовые файлы, файлы XML и др. Кроме того, здесь расположены инструменты для сохранения данных в формате XPS, сбора информации из электронной почты, а также синхронизации данных со списками Office SharePoint 2007.

¦ Вкладка Database Tools (Работа с базами данных) содержит более сложные инструменты для работы с макросами, отображения отношений и зависимостей, а также анализа, управления и синхронизации информации.

Технология создания таблицы Студент

1. Создайте новую базу данных, Office /Создать. Появится Область задач с заголовком Создание файла. Выберите гиперссылку Новая база данных. В окне Файл новой базы данных выберите для файла папку Мои документы и присвойте файлу имя Академия2007. Щелкните по кнопке Создать. Появится окно базы данных с перечнем объектов.

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

3. Начните с таблицы Студенты, включите в создаваемую таблицу поля:

- N зачетной книжки

- N группы

- ФИО

- дату рождении

- коммерческий

Для этого вводим название, нажимаем Enter, появилось сначала поле с названием Поле1 (а потом Поле2 и т.д.) , при этом столбец Добавить поле сместился вправо.

Название Поле1 можно изменить сразу, для этого нужно щелкнуть правой кнопкой мыши на прежнем названии поля и в появившемся контексном меню выбрать пункт Переименовать столбец, но можно (наверное и лучше) сделать это чуть позже, что и описано в п.4. Ширину столбца можно изменить, перемещая границы.

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

Должна быть возможность быстрого ввода информации в представление Datasheet View (Режим таблицы), отображения элементов данных, таким образом, чтобы другие пользователи могли легко обрабатывать данные в представлении Forms View (Режим формы), создания и изменения формы для других пользователей (в представлении Design View [Конструктор]) и настройки отображения информации в отчете в представлении Layout View (Режим макета). Теперь можно легко переключаться между этими представлениями при работе с информацией с помощью кнопки View (Вид или Режим) на вкладке Ноте (Главная).



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

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

6. Введите данные для 3-х групп по 10 студентов в каждой с различными значениями полей.

7. После ввода данных сохраните базу данных.

8. Чтобы сделать нужные поля ключевыми в режиме Конструктор щелкните по кнопке Ключевое поле.

9. Для того, чтобы сделать столбец уже, измените название столбца таблицы, соответствующего полю N зачетной книжки. Замените название столбца на сокращенное: НЗК. Для этого в режиме конструктора выделите указанное поле и введите на вкладке Общие в строку Подпись новое название. Просмотрите таблицу.

10. Предусмотрите контроль данных. Запретите ввод даты рождения меньше заданной, например, даты более ранней, чем 01.01.1970 г. При попытке ввода такой даты предусмотрите вывод сообщения: Слишком старый студент. Для этого в режиме конструктора установите окно свойств поля Дата рождения. Для свойства Условие на значение введите: >#01.01.1970#. Для свойства Сообщение об ошибке введите: Слишком старый студент. Проверьте правильность установленного контроля значений поля Дата рождения.

Освоение приемов работы с фильтрами в таблицах

MS Access позволяет применять несколько видов фильтров для работы с таблицами. Фильтрация и сортировка данных находится в группе Сортировка и фильтр.

Задание 4. Найдите студентов, фамилия которых начинается на заданную букву, например на букву «В». Список найденных студентов должен быть упорядочен по алфавиту. Для поиска использовать расширенный фильтр.

Технология поиска с помощью фильтра по выделенному

1. Найдите в поле ФИО любую фамилию, начинающуюся на букву «В» и выделите мышкой эту букву. Если Вы такую фамилию не вводили, то используйте имеющиеся.

2. Щелкните по кнопке Выделение и выберите начинается с. В результате на экране останутся только строки таблицы с фамилиями, начинающимися на заданную букву.

3. Для отмены фильтра щелкните по кнопке с фильтром на строке состояния (это внизу окна).

Технология поиска с помощью расширенного фильтра

1. Для установки расширенного фильтра введите команду Дополнительно/Расширенный фильтр. Появится окно с бланком фильтра.

2. Укажите порядок сортировки. Для выберите от А до Я (по возрастанию).

3. Введите условие отбора. Для этого выберите текстовые фильтры введите в 3-ью строку 2 символа: В*

4. Примените фильтр. Для этого можно воспользоваться 3-мя способами:

выполнить команду /Применить фильтр.

щелкнуть по кнопке инструментальной панели Применение фильтра.

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

6. Отмените фильтр. Для просмотра таблицы в полном виде нужно выполнить команду Дополнительно/ Очистить фильтр, либо в контекстном меню, поставить кнопку мыши на Применить фильтр , появится Удалить фильтр либо с помощью соответствующей кнопки в строке состояния (это внизу окна).

Задание 5. Найдите студентов, родившихся в заданном году, например в 1978 г.

Технология поиска с помощью Фильтра дат

1. Щелкните клавишей мышки по стрелочке рядом с полем Дата рождения любой записи. В контекстном меню выберите пункт Фильтры дат:

2. Выберите между и введите значения Введите в строку условия не ранее 01.01.1978 не позднее 31.12.1978 значение: *.*.1978 и нажмите клавишу ОК. Удалите фильтр.

Технология поиска с помощью расширенного фильтра

1. Вызвать меню, нажав Дополнительно/Расширенный фильтр очистите бланк фильтра.

2. Введите в 1-ый столбец бланка условие для поля дата рождения >= заданная дата, а во 2-ой столбец для того же поля условие <= заданная дата, где заданная дата - какая-либо дата по выбору студента или указанию преподавателя.

3. Для просмотра результата фильтрации щелкните по кнопке инструментальной панели Применить фильтр. Удалите фильтр.

Задание 6. Найдите студентов, родившихся в заданном году и обучающихся на коммерческой основе в заданной группе.

Для решения задачи используйте 4 столбца бланка с названиями полей: дата рождения, дата рождения, N группы, коммерческий.

Задание 7. Предварительно создав, введите данные в таблицы: Группа, Факультет, Специальность.

Таблица Группа должна иметь не менее 5 строк и содержать поле N группы того же типа и с таким же названием как в таблице Студент. Кроме того, должны иметься 3 строки со значением этого поля таким же, как в таблице Студент.

Таблица Факультет должна иметь не менее 5 строк и содержать поле N факультета того же типа и с таким же названием как в таблице Группа. Кроме того, должны иметься 3 строки со значением этого поля таким же, как в таблице Группа.

Таблица Специальность должна иметь не менее 5 строк и содержать поле N специальности того же типа и с таким же названием как в таблице Группа. Кроме того, должны иметься 3 строки со значением этого поля таким же, как в таблице Группа.

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

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

Технология

1. Откройте таблицу Группа.

2. Откроется окно со списком полей таблицы и окно с макетом сводной таблицы.

3. Перетащите мышкой поле N Специальности в область заголовка столбцов, поле N Факультета - в область заголовка строк, поле Курс - в область фильтра, поле N группы - в область итогов или деталей. Закройте окно со списком полей. На экране останется окно со сводной таблицей, имеющее вид примерно такой, как на рис.1.1.

4. Настройте сводную таблицу для просмотра сведений о распределении групп только 1-го курса по специальностям и факультетам.

5. Закройте сводную таблицу с сохранением данных.

Рис.1.1. Вид сводной таблицы Группа.

Связи между таблицами

Таблицы могут быть связаны отношениями один-к-одному, один-ко-многим и многие-к-многим. Access позволяет использовать только отношения первых двух типов.

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

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

Наиболее часто используются отношения один-ко-многим. В этом случае одной записи в главной таблице соответствует несколько записей в подчиненной таблице.

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

Задание 8. Установите связи между данными таблиц Факультет, Специальность, Группа и Студент.

Технология.

1. Загрузите файл базы данных Академия.

2. Щелкните по кнопке инструментальной панели Схема данных в группе Показать или скрыть на вкладке Работа с базами данных.

3. В окне Добавление таблицы последовательно выберите указанные выше таблицы, щелкая затем по кнопке Добавить.

4. Добавив последнюю таблицу, щелкните по кнопке Закрыть.

5. В окне Схема данных установите связь между таблицей Факультет и таблицей Группа. Для этого:

- выделите щелчком поле N факультета в таблице Факультет и, удерживая нажатой левую клавишу мыши, переместите указатель на поле с тем же названием в таблице Группа;

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

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

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

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

6. Аналогично установите связь между таблицами Специальность и Группа по полю N специальности. Установите связь между таблицами Группа и Студент по полю N группы.

7. Закройте окно Схема данных с сохранением данных.

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

Скрыть записи подчинённой таблицы можно, щёлкнув кнопкой мыши на значке «-».

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

Служебные средства Access

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

Задание 10. Сжатие базы данных и создание резервной копии базы данных

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

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

Для создания резервной копии базы данных выполните команду Office/Управление/Резервная копия базы данных. Выберите диск и папку для размещения резервной копии и щелкните по кнопке Сохранить. Если резервная копия сохраняется в той же папке, что и основной файл базы данных, то Access автоматически добавляет в имя файла резервной копии дату сохранения.

Защита базы данных.

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

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

Установка пароля

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

Для установки пароля выполните команду Работа с базами данных/ Зашифровать паролем.

При этом база данных должна быть открыта в монопольном режиме. Для выбора монопольного режима Office/ /Параметры Access/ Дополнительно/Монопольный доступ либо можно использовать диалоговое окно открытия БД. Для этого Office/Открыть. Внизу справа в этом окне имеется список, выбрать Открыть монопольно.

Преобразование базы данных Access 2007 к формату Access 2000-2003

Технология

1. В меню Office/Сохранить как/Сохранить базу данных в другом формате. Выберите формат файла Access той версии, которая нужна.

2. В окне Сохранение данных выберите диск, папку и введите имя новой создаваемой базы данных формата предыдущей версии Access. Нажмите кнопку Сохранить.

Лабораторная работа № 2. Запросы

Цель работы: Конструирование и использование запросов на выборку и изменение базы данных.

Запросы

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

Условие может определять:

- порядок сортировки выводимых данных;

- фильтрацию данных;

- вычисляемые поля;

- вывод данных из нескольких связанных таблиц;

- и т.п.

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

Все запросы можно разделить на 2 группы:

- запросы-выборки;

- запросы действия.

Запросы-выборки извлекают данные из таблиц в соответствии с заданными условиями.

Основные виды запросов-выборок:

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

- запрос с критерием поиска;

- запрос перекрестный;

- запрос с параметром;

- запрос с вычисляемым полем;

- запрос с итогами;

- запрос к связанным таблицам.

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

- запрос на удаление;

- запрос на замену (обновление);

- запрос на создание новой таблицы;

- запрос на добавление новых записей в таблицу.

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

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

2-ая строка таблицы называется Сортировка. Она определяет способ сортировки по соответствующему полю (по возрастанию, по убыванию или без сортировки).

3-ья строка таблицы управляет Выводом на экран значения соответствующего поля.

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

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

При записи в запросе условия можно объединять критерии при помощи операций И (AND) или ИЛИ (OR).

Использование вычисляемых полей в запросах

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

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

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

Например, для создания поля Сумма следует ввести в ячейку в строке Поле выражение: Сумма: [Цена]*[Количество]

При выполнении запроса в поле Сумма будет занесено новое значение, найденное путем перемножения содержимого полей Цена и Количество.

Вычисляемые поля позволяют:

- рассчитывать числовые значения и даты;

- комбинировать значения в текстовых полях;

- создавать подчиненные запросы;

- рассчитывать значения итоговых полей с помощью групповых функций.

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

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

При делении денежного значения на любое число возвращается числовое значение со значением свойства Размер поля - С плавающей точкой (8 байт). Для того чтобы возвращалось значение типа Денежный, следует указать этот тип в свойстве запроса Формат поля.

В Microsoft Access определена специальная функция Count, обеспечивающая подсчет числа записей в запросе. Для этого следует ввести Count(*) в ячейку в строке полей.

Окно Область ввода, выводящееся нажатием клавиш Shift+F2, позволяет просматривать выражения целиком (без прокрутки).

Элементы выражений

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

Оператор =, <, &, And, Or, Like Определяет операцию, выполняемую над одним или несколькими элементами.

Идентификатор Формы![Заказы]![Заказ] или Отчеты! [Счет].Вывод На Экран Задает ссылку на значение поля элемента управления или свойства.

Функции Например, Date, Sum, Dlookup, Dmax и др. Возвращают значения, найденные в результате расчета или другой операции. Access Basic предоставляет пользователю возможность создавать собственные функции.

Литерал 100, #1-янв-94#, "New York" Представляет значение, например, число, строку или дату, которое используется в Microsoft Access, именно в том виде, как оно записано. Даты заключаются в символы номера (#), а строки в прямые кавычки (").

Константа True, False, Да, Нет, Null Представляет значение, остающееся неизменным.

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

Задание 1. Создать простой запрос для выборки сведений из базы данных, включающий следующие поля:

наименование факультета

N группы

наименование специальности

N зачетной книжки

ФИО

Технология

1. Щелкните в окне Создание/Конструктор запросов.

2. В окне Добавить таблицу добавьте все четыре таблицы и закройте окно.

3. Отрегулируйте размеры и расположение окон с таблицами на схеме данных.

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

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

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

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

7. Закройте запрос. Появится диалоговое окно, в котором необходимо подтвердить необходимость его сохранения. Присвойте запросу имя Выборка 1.

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

Технология

1. Щелкните по имени запроса правой кнопкой мыши и выберите Конструктор.

2. . Добавьте в запрос поле Коммерческий.

3. Введите в строку Условие отбора поля Коммерческий значение Да.

4. Отмените вывод на экран при выполнении запроса значения поля Коммерческий. Для этого выключите флажок вывода на экран для этого поля.

5. Просмотрите выборку, щелкнув по кнопке Выполнить на инструментальной панели.

6. Закройте запрос с сохранением макета запроса.

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

При конструировании запроса используйте стандартную функцию Access:

DMax("[Дата рождения]";"Студент";”Критерий”).

Функция имеет три аргумента, которые задаются текстовыми строками. Здесь первый аргумент функции определяет поле, по которому ведется поиск, второй аргумент - имя таблицы (запроса), в которой ведется поиск, а третий аргумент ограничивает область поиска заданным условием, этот аргумент не является обязательным. (см. Справку по языку Visual Basic Microsoft Access. Раздел Функции).

Технология

1. Щелкните в окне Создание/Конструктор запросов..

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

3. Введите в строку Условие отбора поля Дата рождения формульное выражение в соответствии с заданием. Используйте инструментальное средство Access Построитель выражений. Для этого установите курсор в соответствующую ячейку таблицы описания запроса и щелкните по кнопке инструментальной панели Построить.

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

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

DMax("[Дата рождения]";"Студент";"[N группы]=851")

Если никак не получается, скопируйте текст из методички и вставьте в построитель. Нужно только проследить, нет ли у Вас отличающихся названий полей и группа с таким номером.

6. Для записи функции в ячейку таблицы описания запроса щелкните по кнопке Ok. Закройте запрос сохранением макета и присвойте ему имя Выборка самого молодого студента. Просмотрите результат выполнения запроса двойным щелчком мыши по его имени в окне базы данных.

Задание 4. Создайте запрос для подсчета количества коммерческих студентов в каждой группе.

Технология

1. Создайте новый запрос с использованием таблиц Факультет, Группа, Студент.

2. Введите в 1-ый столбец бланка запроса поле Наименование факультета, во 2-ой столбец - поле N группы, в 3-ий столбец - поле Коммерческий.

3. Установите в качестве условия выборки для 3-го столбца - значение Да.

4. Установите для 1-го и 2-го столбцов бланка сортировку по возрастанию.

5. Отключите вывод на экран данных 3-го столбца.

6. Введите в 4-ый столбец поле Коммерческий и замените название столбца на Количество коммерческих. Для этого ячейка с названием поля должна содержать:

Количество коммерческих:Коммерческий

7. (новое и старое названия столбца разделены символом «двоеточие»).

8. Щелкните по кнопке инструментальной панели Итоги, появится строка группировка и выберите из списка для 4-го столбца операцию Count.

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

10. Сохраните запрос, присвоив ему имя Подсчет коммерческих по группам.

Задание 5. Самостоятельно составьте запрос-выборку для подсчета количества коммерческих студентов по каждому факультету.

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

Технология

1. Создайте запрос на выборку на основе таблицы Студент. Введите в 1-ую строку бланка запроса поля N группы и ФИО. Добавьте в бланк строку Групповая операция и выберите из списка для столбца ФИО операцию Count.

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

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

4. В режиме конструктора откройте запрос Подсчет количества коммерческих студентов и всего по группам. Добавьте на схему данных этого запроса запрос Подсчет количества студентов по группам. Для этого щелкните на схеме данных правой клавишей мыши и выберите из контекстного меню пункт Добавить таблицу…. В окне Добавление таблицы установите вкладку Запросы. Найдите и выделите нужный запрос и щелкните по кнопке Добавить.

5. Добавьте в бланк запроса поле Количество студентов всего и выполните запрос.

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