Лабораторная работа № 2. Создание простых запросов
Базы данных
Методические указания для лабораторных работ студентов специальности «Прикладная информатика в экономике»
Ростов-на-Дону
УДК 681.3.06
Базы данных: методические указания для лабораторных работ студентов специальности «Прикладная информатика в экономике». – Ростов н/Д:
Ростовский государственный строительный университет, 2012. -26 с.
Направлены на обучение методам и средствам создания реляционных баз данных в СУБД MS Access и работы с этими базами. Предназначены для лабораторных работ студентов специальности «Прикладная информатика в экономике» как очной, так и заочной форм обучения.
Составитель: канд.физ.-мат.наук, доц. О.А. Ильичева
Рецензент: канд.физ.-мат.наук, доц. В.Н. Глушкова (ДГТУ)
Редактор Т.М. Климчук
Темплан 2012 г., поз. 27
Подписано в печать 12.03.12. Формат 60х84/16. Бумага писчая. Ризограф.
Уч.-изд.л. 1,6 Тираж 100 экз. Заказ
Редакционно-издательский центр
Ростовского государственного строительного университета
344002, Ростов-на-Дону, ул. Социалистическая, 162.
©Ростовский государственный
строительный университет, 2012
Введение
Базы данных являются одним из главных достижений информатики, основой современных информационных систем. Многочисленные исследования в этой области за последние десятилетия привели к созданию весьма мощных, понятных, многообразных систем, обслуживающих как небольшие фирмы, так и гигантские корпорации. Базы данных непрерывно эволюционируют, от простых хранилищ числовой и текстовой информации до многомерных и аналитических систем, включающих мультимедийные данные разных типов, размеров и назначения.
Не будет преувеличением сказать, что базы данных проникают во все сферы человеческой деятельности; администратор баз данных – одна из наиболее востребованных профессий, в которой нуждаются как столпы мировой индустрии, так и приюты, библиотеки, и даже монастыри.
Методические указания предоставляют задачи и упражнения для практической работы с реляционными базами данных, завоевавшими в последние годы лидирующие позиции в области информационных систем.
Лабораторная работа №1. Создание базы данных (БД) Access
Создание нового файла базы данных
Откройте приложение Access:
Пуск ® Программы ® Средства Microsoft Office ® Microsoft Access.
В окне Microsoft Access на правой панели «Открытие файла» выберите раздел «Создание» и в нем переключатель «Новая база данных». В открывшемся окне сохранения файла выберите папку, в которой будет сохранена база данных, в поле «Имя файла» наберите название создаваемой базы: Учебная и нажмите кнопку «Создать».
В главном окне Microsoft Access появится окно базы данных
Левая панель Объекты этого окна позволяет выбрать тип объектов базы данных для последующей работы с ними: таблицы, запросы, формы и т.п.
Верхняя панель определяет режим работы: открыть выбранный объект в готовом виде, открыть в режиме конструктора для возможного изменения его структуры, создать новый объект указанного типа. Аналогичные действия с выбранным объектом можно выполнять, отметив соответствующую команду из списка, представленного внутри окна БД.
Создание таблиц
На левой панели объектов выберите кнопку «Таблицы» и в списке окна выберите (двойным щелчком мыши) команду «Создание таблицы в режиме конструктора». В режиме конструктора таблицы создается ее схема – задаются имена полей (атрибутов), их типы и свойства (условия на домен). Столбцы схемы заполняются построчно: каждая строка содержит сведения об одном атрибуте.
В первом столбце указывается название атрибута, во втором – его тип (тип домена), в третьем – необязательный комментарий. После выбора типа атрибута внизу открывается окно свойств, предоставляющее возможность задать ограничения на домен.
Имена полей не должны содержать знаков препинания, скобок, кавычек; желательно, чтобы они не содержали также и пробелов.
Зададим схему таблицы, содержащей сведения о сотрудниках фирмы:
1. Введите в столбец Имя поля название первого атрибута - поля таблицы: ФИО.
2. В соседнем столбце Тип данных выберите в списке (он появляется при щелчке мышью в этом столбце) тип атрибута: Текстовый.
3. В открывшемся окне Свойства поля, на вкладке Общие, в качестве значения свойства Обязательное поле выберите Да.
4. В следующем столбце схемы Описание можно (но не обязательно) ввести комментарий к этому атрибуту, например, Фамилия и инициалы сотрудника.
Далее переходим на новую строку – заполняем сведения о втором атрибуте таблице – табельном номере сотрудника.
В столбец Имя поля вводим название Табномер; в столбце Тип данных выбираем Числовой; в окне свойств отмечаем в списках: Размер поля- Длинное целое, Обязательное поле – Да, Индексированное поле - Да (Совпадения не допускаются). Последние два свойства обязательны, если данный атрибут является потенциальным (возможным) ключом. В столбец Описание введите Табельный номер сотрудника.
Заполняем сведения о третьем атрибуте таблицы – отделе, в котором работает сотрудник.
В столбец Имя поля вводим название Отдел; в столбце Тип данных выбираем Числовой; в окне свойств отмечаем: Размер поля: Целое, Условие на значение: >0 And <=10, Сообщение об ошибке: Такого отдела не существует, Обязательное поле: Нет, Индексированное поле: Нет. В столбец Описание вводим Номер отдела.
Обратите внимание, что сообщение об ошибке возникнет на экране при попытке ввести данное, не удовлетворяющее заданному условию на значение. Для нашей таблицы сообщение «Такого отдела не существует» будет выдано в случае, если пользователь базы введет отрицательный номер отдела или номер, превышающий число 10.
Зададим следующий атрибут – должность сотрудника.
В столбец Имя поля вводим название атрибута: Должность, в столбце Тип данных выбираем Текстовый, в свойствах отмечаем Обязательное поле: Нет, Индексированное поле: Нет. В столбец Описание вводим текст: Занимаемая должность.
Вводим сведения о последнем атрибуте – зарплате сотрудника.
В столбец Имя поля вводим название атрибута: Начислено, в столбце Тип данных выбираем Денежный, в свойствах отмечаем: Формат поля: Денежный, Условие на значение: >0, Сообщение об ошибке: Таких зарплат не существует, Обязательное поле: Нет, Индексированное поле: Нет. В столбец Описание вводим: Зарплата без налоговых и страховых вычетов.
После того как атрибуты определены, необходимо выбрать первичный ключ. Если этого не сделать, СУБД Access предложит вставить в таблицу свой ключ – дополнительный атрибут Счетчик, значениями которого являются номера строк. Кроме того, таблицу с не определенным первичным ключом нельзя будет связать с другими таблицами.
В качестве первичного ключа выберем атрибут Табномер. Чтобы зафиксировать это свойство в схеме, выделите строку с описанием этого атрибута: подведите указатель мыши к крайнему левому столбцу окна таблицы и щелкните в этой строке. Затем нажмите кнопку 
с изображением ключа на панели инструментов Access. Щелкните в другом поле, чтобы убрать выделение. В результате получим схему таблицы.
Созданную схему таблицы необходимо сохранить (обычным для Windows способом). В окне сохранения задайте имя таблицы «МоиСотрудники».
Теперь таблицу нужно заполнить данными – задать значения атрибутов.
Для заполнения таблицы перейдем из режима конструктора в режим таблицы. Для этого нажмите кнопку Вид - крайнюю левую на панели инструментов: 
.
Откроется окно, содержащее имена полей и свободную строку для ввода первой записи. Записи должны вводиться одна за другой (нельзя заполнять один столбец, потом другой по своему усмотрению). В каждой записи необходимо ввести значения первичного ключа и обязательных полей, иначе СУБД выдаст сообщение об ошибке и запретит дальнейший ввод. Записи добавляются в конец таблицы; в середину или в начало таблицы вставка невозможна.
Если первичным ключом является счетчик, то его значения заполняются автоматически системой.
В режиме таблицы данные можно не только вводить, но и изменять, и удалять. Для удаления записи ее необходимо выделить, щелкнув мышью на ее позиции в левом столбце таблицы, и затем нажать кнопку Delete на клавиатуре.
Перемещаться от одного поля к другому и от одного значения к другому можно с помощью мыши, клавиш Enter, Tab, и стрелок перехода на клавиатуре. Регулировать ширину столбцов и строк можно с помощью мыши, подводя указатель к разделительной линии строк или заголовков полей и перетаскивая этот разделитель.
Заполните таблицу «МоиСотрудники» следующими данными (в поле Начислено достаточно ввести только число, обозначение рубля «р.» добавляется автоматически):
| ФИО | Табномер | Отдел | Должность | Начислено |
| Бондарчук Ц.Р. | Директор | 19 300,00р. | ||
| Левый И.К. | Фин. Директор | 18 500,00р. | ||
| Вправый Т.М. | Контролер | 4 700,00р. | ||
| Жучкина Л.Л. | Товаровед | 7 400,00р. | ||
| Щипачев О.Д. | Гл. Бухгалтер | 18 000,00р. | ||
| Лага И.Н. | Инженер | 3 400,00р. | ||
| Петровская И.Д. | Зам. Директора | 17 000,00р. | ||
| Бубликов П.Л. | Нач. отдела | 14 700,00р. | ||
| Протасов Е.Г. | Инженер | 3 200,00р. | ||
| Осина Л.Д. | Нач. отдела | 15 400,00р. | ||
| Козленкин О.Д. | Инженер | 3 300,00р. | ||
| Жукова Е.Н. | Ст. инженер | 5 100,00р. | ||
| Керичеев А.Щ. | Зам. Директора | 16 900,00р. | ||
| Петров В.А. | Бухгалтер | 8 800,00р. | ||
| Петренко Д.Д. | Инженер | 3 200,00р. | ||
| Штагер А.З. | Бухгалтер | 8 300,00р. | ||
| Ли А.А. | Инженер | 3 200,00р. | ||
| Алексеев В.В. | Ст. инженер | 5 100,00р. | ||
| Опенкин Р.П. | Референт | 1 800,00р. | ||
| Отмывалова Ю.Ю. | Техник | 1 950,00р. | ||
| Буйный А.Ю. | Нач. отдела | 16 300,00р. |
Создайте самостоятельно еще одну таблицу – «Отделы» с полями: Заведующий (Текстовый), Отдел (Числовой), Название (Текстовый), Телефон (Текстовый), Характеристика (МЕМО), Фото (Поле объекта ОЛЕ).
В качестве первичного ключа выберите поле Отдел. Установите для поля Отдел такие же ограничения на домен, как и для поля Отдел из таблицы «МоиСотрудники».
Заполните эту таблицу данными, взятыми из таблицы «МоиСотрудники»: в столбец Заведующий внесите фамилию и инициалы начальников отделов, в столбец Отдел - номера соответствующих отделов, значения поля Телефон заполните по своему усмотрению.
В качестве Названий отделов введите Плановый, Финансовый, Технический для номеров отделов 1, 2, 3 соответственно.
В поле Характеристика внесите краткие сведения о заведующем, например, «добрый, имеет троих детей» или «свиреп, но справедлив» и т.п. Тип МЕМО означает, что поле предназначено для ввода текстовой информации большого размера (от 256 до 65535 символов). Отличается от типа Текстовый тем, что в таблице находятся не сами данные, а ссылки на блоки данных, хранимые отдельно (для ускорения обработки таблиц).
Тип данных Поле объекта ОЛЕ (OLE) содержит ссылки на ОЛЕ-объекты – листы MS Excel, документы Word, звуковые данные, изображения. Для вставки в таблицу рисунка (поле Фото) необходимо для каждой записи, щелкнув по соответствующей ячейке таблицы, выбрать объект с помощью меню Вставка ® Объект;в открывшемся окне выбрать тип объекта и установить переключатель Создать новый или Создать из файла. Объект можно также просто перетащить мышью в поле таблицы. В качестве объекта можно выбрать Рисунок Paintbrush, тогда при установке переключателя Создать новый можно нарисовать портрет в приложении Paint. Можно воспользоваться рисунками Microsoft Clip Gallery. Выполните вставку объектов в поле Фото с помощью этих средств.
1.3.Создание связей между таблицами
Таблицы «Отделы» и «МоиСотрудники» информационно связаны: в таблице «Отделы» речь идет об отделах, в которых работают сотрудники, указанные в таблице «МоиСотрудники». Каждой записи в таблице «Отделы» соответствуют несколько записей в таблице «МоиСотрудники» с тем же самым номером отдела. Таким образом, таблицы «Отделы» и «МоиСотрудники» связаны по номеру отдела, т.е. по полю Отдел. Такая связь называется «один ко многим». В данном случае в качестве таблицы со стороны «один» выступает «Отделы» (так называемая родительская таблица), со стороны «многие» - «МоиСотрудники» (дочерняя). Поле таблицы, участвующей в связи со стороны «один» всегда является первичным ключом этой таблицы. Поле таблицы, идущей со стороны «многие», является внешним ключом. Эти ключи должны иметь одинаковый домен.
Зафиксируем эту связь в Access. Выберите меню Сервис ® Схема данных или нажмите кнопку 
на панели инструментов. С помощью появившегося окна Добавление таблицы добавьте в схему данных таблицы «Отделы» и «МоиСотрудники» (щелкая на их именах мышью и нажимая кнопку Добавить), после чего нажмите кнопку Закрыть. В окне Схема данных будут отображаться схемы («шапки») таблиц, содержащие имена их полей с выделенным полем – первичным ключом.
Для создания связи зацепите мышью поле Отдел (по которому будет идти связь) в таблице «Отделы» и перетащите его на поле Отдел таблицы «МоиСотрудники». Таким образом, перетаскивание осуществляется от родительской таблицы к дочерней, от первичного ключа к внешнему ключу.
В появившемся окне «Изменение связей» установите флажки Обеспечение целостности данных, Каскадное обновление связанных полей, Каскадное удаление связанных записей. Нажмите кнопку Создать.
Созданная связь будет изображаться в окне схемы данных в виде линии с метками на концах (рядом с полем Отдел таблицы «Отделы» указывается цифра «1», а рядом с полем Отдел таблицы «МоиСотрудники» - знак бесконечности «»)
Эти метки означают, что данная связь имеет тип «один ко многим»: с одной («1») записью таблицы «Отделы» можно связать много («») записей из таблицы «МоиСотрудники» (иными словами, в одном отделе может работать несколько сотрудников).
Установка флажков каскадного удаления и обновления связанных данных гарантирует, что за целостностью данных будет следить СУБД Access, при этом будет применяться стратегия CASCADE при обновлении и удалении записей.
Для удаления связи между таблицами следует щелкнуть мышью на линии, изображающей данную связь (при этом толщина линии увеличится), нажать клавишу Del; либо щелкнуть правой кнопкой мыши по линии и выбрать Удалить. Далее следует подтвердить удаление в диалоговом окне, выбрав в нем вариант Да. Сохраните схему данных и закройте окно схемы данных.
Лабораторная работа № 2. Создание простых запросов
На выборку
2.1. Запросы на выборку с одной таблицей – источником данных
Запрос - это объект, позволяющий пользователю получить нужные данные из одной или нескольких таблиц. Для создания запроса можно использовать бланк QBE (запрос по образцу) или написать инструкцию SQL. Можно создавать запросы на выборку, обновление, удаление или добавление данных. С помощью запросов можно также создавать новые таблицы, используя данные из одной или нескольких существующих таблиц.
Пример 1. Выдать сведения о сотрудниках, фамилии которых начинаются на букву «П».
Для реализации запроса выполните следующие шаги:
1. В окне БД перейдите на вкладку Запросы. Дважды щелкните на значке Создание запроса в режиме конструктора. В появившемся окне Добавление таблицы выделите на вкладке Таблицы таблицу «МоиСотрудники» и щелкните на кнопке Добавить, а затем Закрыть. На экране появится так называемый бланк запроса (окно конструктора запросов) с загруженной схемой таблицы «МоиСотрудники».
2. Определите поля запроса: в строку Поле бланка нужно поместить те поля таблицы, которые содержат требуемые сведения, а также те, которые участвуют в условиях отбора; в нашем запросе – это ФИО и остальные поля. Дважды щелкните мышью по полю ФИО в схеме таблицы «МоиСотрудники», и оно появится в первом столбце бланка (поле ФИО можно перетащить мышью на любой столбец или выбрать в выпадающем списке столбца). Аналогичными действиями поместите в следующие столбцы бланка запроса поля Табномер, Отдел, Должность, Начислено.
3. Задайте условие отбора: в строку Условие отбора для поля ФИО введите строку: П* (введенный текст затем автоматически преобразуется в следующий: Like "П*"). Запрос в режиме конструктора примет вид
4. Запустите запрос на выполнение: нажмите кнопку 
или выберите меню Запрос ® Запуск. В результате на экране появится таблица, столбцы которой соответствуют полям запроса (и располагаются в том же порядке), а строки являются записями, удовлетворяющими заданному условию.
Для возврата в режим конструктора запросов можно нажать кнопку 
или выполнить команду Вид ® Конструктор.
5.Сохраните созданный запрос под предлагаемым именем «Запрос1».
Пример 2. Перечислить инженеров второго и третьего отделов.
В данном запросе необходимо задать составное условие отбора: в строку Условие отбора для поля Должность введите слово: Инженер (кавычки будут поставлены автоматически), для поля Отдел введите условие: 2 Or 3 (что означает «второй или третий» отдел)
R Условия отбора, стоящие в одной строке бланка запроса связаны логической операцией «и» (AND), т.е. условие данного запроса можно прочитать так: Должность = «Инженер» И (Отдел = 2 ИЛИ Отдел = 3). Условия, стоящие в разных строках, связаны логической операцией «или» (следующий пример).
Пример 3. Выдать сведения об инженерах третьего отдела или тех сотрудниках, у кого зарплата больше 5000 р.
Условие отбора с операцией «или» в данном запросе касается разных полей, поэтому для поля Начислено частное условие отбора (>5000) поместите в строке «или», расположенной ниже строки Условие отбора
Это условие можно читать так: (Должность = «Инженер» И Отдел = 3) ИЛИ Начислено > 5000.
Пример 4. Представить фамилии тех сотрудников, которым начислено менее 2000 р. и тех, которым начислено более 7000 р.
Есть два способа задать несколько частных условий для одного поля, связанных оператором Or. Можно ввести все условия в одну ячейку строки Условие отбора, соединив их оператором Or. Другой вариант: ввести каждое частное условие в отдельную ячейку строки или .
Задания
1. Перечислить всех сотрудников первого отдела.
2. Определите должности сотрудников, фамилии которых начинаются с буквы «О» или «Л» и зарплата (поле «Начислено») которых не превышает 4500 р.
3.Выдайте фамилии сотрудников первого или второго отделов, должность которых включает строку «бухг», а также сотрудников третьего отдела, зарплата которых превышает 3000 р.
4.Определите, в каких отделах работают сотрудники, зарплата которых лежит в диапазоне от 6000 до 9000 р.
Подсказка: В строке «Условие отбора» для соответствующего поля введите: >=6000 And <=9000 или используйте предикат Between, определяющий диапазон значений: Between 6000 And 9000.
5. Выдайте фамилии и табельный номер сотрудников отдела, начальником которого является Буйный.
Подсказка: В данном запросе будут участвовать обе таблицы - «МоиСотрудники» и «Отделы», добавьте вторую таблицу. Проверьте: таблицы в бланке должны быть связанными; если это не так – создайте связь.
6. Выдайте должности сотрудников отдела, телефон начальника которого 277-88-99 (возьмите любой номер из своей таблицы).
7. Выдайте фамилии инженеров отдела, начальником которого является Бубликов.