Методичка

Разработка баз данных. СУБД Access

При заполнении полей данными поля типа Счетчик ( в данном случае Разряд) заполняются автоматически!!

Аналогично создается таблица «Отделы» и заполняется данными (5 произвольно-реальных записей).

Особенности создания таблицы «Сотрудники».

1.Откройте конструктор таблиц и заполните столбцы Имя поля, Тип данных и другие характеристики для полей КодСотруд,

Фамилия, Имя, Отчество, Пол.

2.Создание фиксированного списка для поля Пол. Откройте список в столбце Тип данных для поля Пол и выберите «Мастер

подстановок» Будет введен набор фиксированных значений

число столбцов 1. Далее введите в столбец: «мужской, женский». Далее, в последующих окнах, никакие параметры не менять!

3.Аналогично создается фиксированный список для поля Об-

разование. Элементами списка могут быть: Среднее, СреднеТехни-

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

Подстановка.

4.Организация связи поля Отдел таблицы «Сотрудники» с

полем Отдел таблицы «Отдел». Выберите Мастер подстановки

(аналогично тому, как делали для поля Пол) Объект «поле под-

становки» получит значение из другой таблицы или запроса вы-

берите таблицу «Отделы» из окошка Доступные поля перена-

правьте поля КодОтдела и Отдел в окошко Выбранные поля,

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

5.Аналогично создается связь между полями «Разряд» таблиц

«Сотрудники» и «Разряды».

6.После завершения создания таблицы «Сотрудники» в режиме конструктора, закрываем её, на запрос Сохранить .. выберем

Да. На запрос Ключевые поля не заданы .. ответим Да. В окне Сохранение впишем имя таблицы «Сотрудники».

7.Заполнение таблицы «Сотрудники» данными.

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

111

Разработка баз данных. СУБД Access

ряда щелчком по кнопке раскрываем список и выбираем из него щелчком мыши нужное значение.

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

В базе данных, если не предусмотреть некоторую защиту, могут возникнуть следующие нарушения:

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

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

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

1.Выберите ленту Работа с базой данных Схема данных Добавить таблицу.

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

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

(рис.78).

Рис. 78. Схема данных

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

112

Разработка баз данных. СУБД Access

откроется окно формирования связей (рис. 79). В этом окне уста-

навливается флажок в поле Обеспечение целостности данных.

Рис.79. Изменение связей

Лабораторная работа 18. Создание запросов

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

Создание простого запроса.

Задание 1. Из таблицы «Сотрудники» отберите поля личного ха-

рактера: Фамилия, Имя, Отчество, пол, ДатаРождения, СемПолож, КолДетей, ДомТелеф, Адрес.

1.Лента Создать Запросы Конструктор запросов

открываем окно конструктора формирования запроса.

2.В окне Добавление таблицы щелчком отмечаем таблицу «Сотрудники», а затем кнопкой Добавить вводим ее в конструктор запросов. Щелчком по кнопке Закрыть завершаем ввод таблицы.

3.Конструктор запросов состоит из двух областей – верхняя содержит таблицы, отобранные в запрос, в нижней части формируется сам запрос, путем: а) отбора полей, б) отбора записей по условию.

Отбор полей можно осуществить перетаскиванием из таблицы

113

Разработка баз данных. СУБД Access

в строку Поле области формирования запроса. Или в строке Поле выбрать нужное из списка, рис. 80.

Рис. 80 Формирование запроса «Личные данные» в конструкторе

4.Закрываем запрос, присваиваем ему имя, например «Личные данные». В области навигации появится этот запрос. Открыть запрос и проверить правильность отбора полей.

Задание 2. Скопировать данный запрос, присваивая копиям имена: «Женщины», «Мужчины», «Призыв». Сформировать на их основе новые запросы, отобрать: 1) женщин, имеющих детей, 2) мужчин с высшим образованием, 3) мужчин призывного возраста с датой рождения > 1.01.1992 и < 31.12.1994.

1.Для отбора (фильтрации) записей со значением «женский»

вполе Пол. Открыть запрос «Женщины» в режиме конструктора и в строке Условие отбора для поля Пол записать женский (это значение должно соответствовать тому, как записан пол в таблице «Со-

трудники»).

2.Закройте конструктор запроса и выполните запрос. Если отбор произошел верно, вновь откройте его в конструкторе добавьте условие отбора по полю Количество Детей >0, рис. 81.

Рис. 81. Условия отбора для запроса «Женщины»

3. Аналогично формируйте запросы «Мужчины», «Призыв»,

114

Разработка баз данных. СУБД Access

указанные в задании 2.

Создание реляционного запроса.

Задание 3. Из таблицы «Сотрудники» отберите поля: Фами-

лия, Имя, Отчество, КолДетей, Отдел, Разряд, из таблицы «Отделы» отберите поля: Начальник, РабТелеф, из таблицы «Разряды» − поле ТарифКоэфф.

1.Лента Создать Запросы Конструктор запросов − от-

крываем окно конструктора формирования запроса. В окне Добав-

ление таблицы щелчком отмечаем таблицы «Сотрудники», «Отде-

лы», «Разряды» с нажатой клавишей Shift, а затем кнопкой Добавить вводим их в запрос. Щелчком по кнопке Закрыть завершаем ввод таблиц.

2.Перетаскиваем поля, в соответствии с заданием, из таблиц в область формирования запроса в строку Поле рис. 82, закрываем запрос, при этом присваиваем ему имя «Служебные данные». Открываем запрос «Служебные данные» и проверяем правильность отобранных записей. Созданный запрос можно просматривать, а также редактировать, дополняя новыми полями, в том числе вычисляемыми, и условиями отбора.

Рис.82. Запрос «Служебные данные» в конструкторе запросов

Создание вычисляемых полей в запросе.

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

налог, начислено.

1. Откройте запрос «Служебные данные» в режиме конструктора и добавьте в конце строки Поле новое поле Зарплата, используя Построитель выражений, который можно вызвать из кон-

115

Разработка баз данных. СУБД Access

текстного меню (КМ) создаваемого поля или открыть на ленте Ра-

бота с запросами Конструктор Построитель, рис. 83.

Рис. 83. Запрос в конструкторе с построителем выражений

2.В верхней части окна построителя выражений наберите выражение Зарплата:[ТарифКоэфф]*5000*1,15 , где 1,15 – уральский коэффициент, 5000 − минимальная зарплата, [ТарифКоэфф] – поле, которое вводится двойным щелчком мыши из нижней части окна построителя Категории выражений. Если в формулу добавилось слово «Выражение», его нужно удалить.

3.Закройте конструктор запросов, подтвердите сохранение изменений и откройте запрос для проверки результата.

Если некоторые значения Зарплаты не пропечатались, нужно открыть таблицу «Сотрудники» и в поле КолДетей в те записи, где нет детей, прописать 0.

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

4Изменение формата поля Зарплата. Откройте запрос в ре-

жиме конструктора КМ этого поля (Зарплата) выберите Свойства Формат поля Денежный. Закройте конструктор и проверьте результат, открыв запрос.

5. Аналогично создайте вычисляемое поле Налог. Налог: ([Зарплата] – [КолДетей]*100)*0,13. Закрыть конструктор и проверить правильность работы запроса. После этого создайте поле Начислено: [Зарплата]- [Налог]. Закройте конструктор и откройте

116

Разработка баз данных. СУБД Access

запрос для проверки правильности результата.

6.Посмотрите на столбец Зарплата и выполните отбор (фильтрацию) записей. Условие фильтрации сформируйте самостоятельно так, чтобы в запросе не было самых высоких и самых низких Зарплат. Проверьте результат.

7.Наложите на предыдущий запрос дополнительное условие отбора, выводить записи для определенного отдела. Выполните запрос.

Если Вам выдается сообщение, что Типы полей не совпадают это происходит потому, что в запросе поле Отдел взято из таблицы «Сотрудники», и у него тип числовой (оно связано с полем Ко-

дОтдела типа счетчик), а в строке условие отбора введено тек-

стовое название отдела. Для устранения этой коллизии необходимо добавить в запрос поле Отдел из таблицы «Отделы», именно в этом поле указать условие отбора и в строке вывод на экран снять флажок с поля Отделы из таблицы «Сотрудники». Выполнить запрос.

Создание параметрического запроса

Задание 5. Создать запрос формирования списков сотрудников

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

1.Создайте запрос на выборку. Лента Создать Конструк-

тор запросов. В окне Добавление таблиц вкладка Запросы «Личные данные». Отберите поля Фамилия, Имя, Отчество, КолДетей.

2.В поле КолДетей в строке Условие отбора наберите текст в квадратных скобках [введите количество детей]. Закройте конструктор запросов, введите имя запросу «Количество детей». Откройте запрос «Количество детей» откроется окно, рис. 84. введите, например 1, получите список сотрудников, имеющих 1 ребенка. Введите 2, затем 3 и т. д.

Рис. 84. Ввод значения параметра в параметрическом запросе

117

Разработка баз данных. СУБД Access

Создание специальных запросов на изменение.

До создания специальных запросов включить содержимое БД. Нажать на кнопку ПАРАМЕТРЫ на панели Сообщений Пара-

метры безопасности Включить это содержимое.

Запрос на создание таблицы.

Задание 6. Создать таблицу «Армия», поместив в неё список мужчин в возрасте 18-20 лет.

1.Создайте запрос на выборку в соответствии с заданием или используйте ранее созданный (Задание 2).

2.Откройте его в режиме конструктора, выберите на ленте −

Работа с запросами Создание таблицы. В окне Создание таб-

лицы введите имя новой таблицы, например «Армия», выберите в текущей БД. Сохраните запрос, вид его значка в области навигации изменится добавится «!».

3. Выполните этот запрос (дважды щелкните по значку левой клавишей мыши), отвечайте на вопросы, которые будут заданы. В результате выполнения в области таблиц появится новая таблица «Армия». Просмотрите её содержимое.

Запрос на обновление таблицы «Разряд».

Задание 7. Обновить таблицу «Разряд», увеличив тарифные коэффициенты на 10%.

1.Создайте запрос на выборку, отобрав из таблицы «Разряд»

поля Разряд и Тарифный коэффициент. Сохраните его, присвоив имя, например «Обновление».

2.Откройте этот запрос в конструкторе и выберите на ленте −

Работа с запросами Обновление. Нижняя часть конструктора запросов изменится, рис. 85, введите в строке Обновление выражение 1,1*[ТарифКоэфф]. Закройте запрос, сохранив изменения, значок запроса изменится «!». Выполните этот запрос. Читайте сообщения и подтверждайте их.

Рис.85. Конструирование обновления значений

118

Разработка баз данных. СУБД Access

3. Откройте таблицу «Разряды» и проверьте результат – значения столбца ТарифКоэфф увеличатся на 10%.

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

Задание 8. Удалить записи из таблицы «Отдел» и всех, связанных с ней записей из таблицы «Сотрудники». (Например, упразднили отдел и всех работников данного отдела увольняют).

1.Убедитесь, что БД доступна не только для чтения. Закройте файл БД, в КМ файла выберите Свойства, флажок Только для чтения должен быть снят.

2.Создайте копию файла БД, если она ранее не была создана.

3.Откройте файл БД, откройте Схему данных, щелкните на

нужной связи в КМ выберите Изменить связь в окне уста-

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

4.Создайте запрос на выборку по таблице «Отдел». Введите в строку Условие отбора поля Отдел название удаляемого отдела. Закройте запрос, присвойте ему имя, например «Удаление», а затем откройте его и проверьте правильность отбора − в запросе должна быть одна запись, содержащая имя удаляемого отдела.

5.Откройте этот запрос в режиме конструктора и выберите на

ленте Работа с запросами Удаление. Сохраните запрос. Выполните запрос «Удаление» и проверьте результат, т. е. содержание таблиц «Отделы» и «Сотрудники».

Лабораторная работа 19. Создание форм

Форма – это объекты БД, предназначенные для работы с одной записью: добавляя, удаляя, редактируя данные в таблицах. Форма может быть простой, которая строится на основе одной таблицы или реляционной, построенной по двум и более связанным таблицам.

Построение простой формы для работы с таблицей «Со-

трудники».

1.Выделите таблицу «Сотрудники». Выполнить команду Со-

здание Форма, создается Макет формы «в столбец» (Все поля в форме располагаются в столбцах). Закройте форму и присвойте ей имя «Сотрудники».

2.Откройте форму «Сотрудники», в нижней части формы располагается узел навигации по записям. Кнопки: Первая запись,

119

Разработка баз данных. СУБД Access

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

(пустая) запись. Добавьте новую запись в таблицу «Сотрудники» используя форму.

Построение формы для связанных таблиц «Отделы» - «Со-

трудники».

1.Выделить таблицу «Отделы». Выполнить команду Создание

Форма, создается Макет вложенных форм, где форма по таблице «Отделы» − главная, по таблице «Сотрудники» − подчиненная (встроенная в главную). Закройте и сохраните форму, присвоив ей имя «Отделы», рис. 86.

Рис. 86. Открытая форма «Отделы» с подчиненной формой «Со-

трудники»

2.Откройте форму «Отделы». Форма имеет два узла навигации, нижний (наружный) узел для главной формы по таблице «Отделы». Внутренний узел для подчиненной формы по таблице «Сотрудники». Переключайте записи нижним узлом навигации и просмотрите информацию в форме. Что отражается в форме?

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

Построение связанной формы по таблицам «Разряд» и «Со-

трудники» с использованием Мастера форм и установки кнопки.

1.Выполните команду Создание Мастер форм.

2.В окне мастера выберите таблицу «Разряды» и перена-

120