лабораторный практикум. Русакова М.С. Лабораторный практикум. Практикум по базам данных самара 2015 министерство образования и науки российской федерации
 Скачать 0.72 Mb.
|
|
ФЕДЕРАЛЬНОЕ ГОСУДАРСТВЕННОЕ БЮДЖЕТНОЕ ОБРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ ВЫСШЕГО ПРОФЕССИОНАЛЬНОГО ОБРАЗОВАНИЯ «САМАРСКИЙ ГОСУДАРСТВЕНЫЙ УНИВЕРСИТЕТ» ЛАБОРАТОРНЫЙ ПРАКТИКУМ ПО БАЗАМ ДАННЫХ САМАРА 2015 МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РОССИЙСКОЙ ФЕДЕРАЦИИ ФЕДЕРАЛЬНОЕ ГОСУДАРСТВЕННОЕ БЮДЖЕТНОЕ ОБРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ ВЫСШЕГО ПРОФЕССИОНАЛЬНОГО ОБРАЗОВАНИЯ «САМАРСКИЙ ГОСУДАРТСВЕННЫЙ УНИВЕРСИТЕТ» ЛАБОРАТОРНЫЙ ПРАКТИКУМ ПО БАЗАМ ДАННЫХ Методические указания САМАРА 2015 УДК 510.2 ББК 22.17 Составители М.С. Русакова, Е.В. Рогачева Рецензент к.ф.-м.н., доц. Луканов А.С. Лабораторный практикум по базам данных: метод. указания / сост. М.С. Русакова, Е.В. Рогачева – Самара, 2015. – 103 с.: ил. В предлагаемом практикуме приведены базовые сведения о работе в Open Office Base, рассмотрены основы работы с сервером баз данных Oracle. В практикуме также содержится ряд лабораторных работ для освоения наиболее часто используемых возможностей Open Office Base, сервера баз данных Oracle. Каждая лабораторная работа снабжена указаниями по выполнению и содержит набор учебных заданий разных уровней сложности. По каждому разделу предусмотрена разработка индивидуального задания. Предназначено для студентов специальностей «Фундаментальная математика и механика», «Прикладная математика и информатика», «Математическое обеспечение и администрирование информационных систем», изучающих данную тему по курсам «Базы данных», «Базы данных и системы управления базами данных» и «Практикум на ЭВМ». Подготовлено на кафедре информатики и вычислительной математики. УДК 510.2 ББК 22.17 М.С. Русакова, Е.В. Рогачева, 2015 3 Часть 1. Введение в СУБД Open Office Base Лабораторная работа №1 Создание таблиц и установление связей Целью этой лабораторной работы является изучение системы управления базами данных Open Office Base. Предполагается, что обучающиеся знакомы с основными понятиями теории баз данных. Создание базы данных Чтобы создать базу данных в Open Office Base (далее будем сокращенно обозначать программу OOB), можно либо воспользоваться мастером создания базы данных, где выбрать пункт «Создать базу», по щелчку на кнопке «Далее» переходите к следующему диалоговому окну, в котором вы можете зарегистрировать базу данных, открыть ее в режиме редактирования и сразу же заказать создание таблиц. После проведенных манипуляций вам будет предложено сохранить базу, после чего можно приступать к созданию таблиц. Если же вам необходимо параллельно с вашей новой базой создать еще одну, то можно воспользоваться пунктом меню «Файл»-> «Создать» -> «Базу данных», после чего будет запущен уже знакомый вам мастер создания базы. Теперь можно переходить к созданию таблиц. В левой части окна OOB можно переключаться между режимами работы с таблицами, запросами, формами и отчетами. Наc интересуют на данном этапе таблицы. В правой верхней части окна перечисляются задачи, которые можно выполнять над таблицей – это создание таблицы в режиме дизайна, создание таблицы при помощи мастера и создание представления. Для начала предлагается выбрать создание таблицы в режиме дизайна. Учебная база данных, которую вы будете разрабатывать, – это фирма. Фирма имеет несколько отделов (допустим, производственный отдел, рекламный отдел, бухгалтерию). В каждом из отделов есть несколько сотрудников, один из которых – начальник. Каждый сотрудник имеет уникальный табельный номер и (неуникальную) должность. Должности соответствует должностной оклад. В каждом отделе есть телефоны (возможно, несколько). Отделы имеют номера (по крайней мере, для внутреннего учета). Создание таблиц Итак, после того, как вы выберете «Создание таблиц в режиме дизайна», в открывшемся окне вы увидите таблицу из трех столбцов: «Имя поля», «Тип поля» и «Описание». Первые два столбца являются обязательными для заполнения. Столбец «Описание» предназначен для «внутренних» нужд пользователя: в нем вы можете поместить комментарий, описание поля, объяснение, почему оно нужно и т. п. Теперь можно заполнять строки, чтобы получить макет таблицы. Какие таблицы потребуются для описания фирмы? Во-первых, это таблица «Сотрудники». В ней будут содержаться данные о сотрудниках: табельный номер, личные данные (фамилия, имя, отчество; почтовый индекс, адрес, домашний телефон; мы ограничимся только ФИО), в каком отделе работает, является ли начальником. Во-вторых, это таблица «Отделы». В этой таблице можно сохранять данные об отделах: их названиях, их начальниках. В-третьих, в таблице «Должности» будут храниться сведения о должностных окладах. Наконец, должна быть таблица «Телефоны», в которой указаны данные о номерах телефонов и о том, к какому отделу они относятся. 4 Замечание 1. О нормализации таблиц говорилось в лекциях. Создайте макет таблицы «Телефоны», имеющий следующие поля: «Номер телефона», тип – текстовый (допустим и числовой; см. замечание 1) и «Номер отдела», тип – числовой. Название поля набирается в столбце «Имя поля», а тип выбирается из выпадающего списка в столбце «Тип данных». Если хотите, добавьте описания. Сделайте поле «Номер телефона» ключевым. Вы можете воспользоваться контекстным меню, которое вызывается нажатием правой клавиши мыши. Проще всего сохранить сделанную работу, просто закрыв окно с таблицей. Назовите эту таблицу «Телефоны». Следующая таблица, которая будет разработана – «Должности». Эта таблица будет играть роль справочника. Ради экономии места в других таблицах лучше использовать номера должностей – разрядов, а не их текстовые названия (к тому же это ускоряет ввод и снижает вероятность ошибок). «Разряд» и будет ключевым полем в этой таблице. Кроме этого, нужны поля «Наименование должности» и «Должностной оклад». Поле «Разряд» будет иметь числовой тип, поле «Наименование должности» – текстовый, поле «Должностной оклад» – числовой, с денежным форматом. Теперь таким же образом создайте таблицы «Отделы» и «Сотрудники». Замечание 2. Тип поля «номер телефона» выбран текстовым, чтобы можно было использовать разделители (черточки) при записи номеров. Если просто выбрать числовой тип, придется записывать телефоны в таком виде: 332211. Замечание 3. Если при создании макета таблицы у вас возникла потребность что-то изменить, исправить, дополнить, это легко сделать. Режим разработки макета таблицы называется режимом конструктора. Двойной щелчок по уже созданной таблице откроет ее для заполнения, а щелчок по кнопке «Редактирование» в окне документа базы данных приведет к открытию именно макета таблицы. Замечание 4. В такой простой базе данных сложно запутаться в названиях полей. ООВ как раз и предполагает работу с достаточно простыми (класса «настольные») базами данных и приветствует одинаковое наименование в таблицах полей, описывающих одно и то же. Так, в нашем случае поле «Номер отдела» будет фигурировать в таблицах «Сотрудники», «Телефоны», «Отделы». Такой подход позволяет несколько автоматизировать создание базы данных Когда вы закончите работу, должны получиться (примерно) следующие макеты: Таблица 1. Сотрудники Имя поля Тип данных Описание Табельный номер Числовой Ключевое поле ФИО Текстовый Разряд Числовой Номер отдела Числовой 5 Таблица 2. Отделы Имя поля Тип данных Описание Номер отдела Числовой Ключевое поле Наименование Текстовый Начальник Числовой Таблица 3. Должности Имя поля Тип данных Описание Разряд Числовой Ключевое поле Наименование Текстовый Оклад Денежный Таблица 4. Телефоны Имя поля Тип данных Описание Номер телефона Текстовый Ключевое поле Номер отдела Числовой Заполнение таблиц Заполнить таблицу очень просто. Дважды щелкните на ярлыке (значке) таблицы в окне базы данных, и перед вами откроется окно заполняемой таблицы. Конечно, с точки зрения опытного разработчика, было бы правильно вначале установить связи между таблицами, разработать форму для ввода, что позволило бы проконтролировать ввод данных. Однако учиться лучше на «живых» данных. Возможно, вам даже следует вооружиться карандашом и бумагой, нарисовать таблицы, и придумать данные, которыми они будут заполнены. Итак, давайте пока ограничимся тремя описанными выше отделами: бухгалтерией, производственным и рекламным отделами. Присвоим им номера: 01, 02 и 03 соответственно. Предположим, что в бухгалтерии работает три сотрудника, в рекламном отделе – четверо, а в производственном – пятеро (итого – двенадцать человек), включая начальников этих отделов. Придумайте сами фамилии, имена и отчества сотрудников фирмы, а также их табельные номера и должности (например, начальник отдела, заместитель начальника отдела, бухгалтер, инженер, менеджер по рекламе, стажер). Теперь осталось разобраться с телефонами. Допустим, что в бухгалтерии и производственном отделе установлено по два телефона, а в рекламном отделе – четыре. Таким образом, нужно придумать восемь номеров телефонов. Заполните каждую таблицу, следя за тем, чтобы данные в них были согласованы. Например, в таблице «Телефоны» не должны появиться несуществующие номера отделов, а в таблице «Сотрудники» – несуществующие должности. 6 Пример заполненных таблиц: Таблица 5. Отделы Номер отдела Наименование Начальник 1 Бухгалтерия 1001 2 Производственный отдел 2006 3 Рекламный отдел 3021 Таблица 6. Должности Разряд Наименование Оклад 6 Начальник отдела 15000 р. 5 Заместитель начальника отдела 14000 р. 4 Бухгалтер 13500 р. 3 Инженер 13000 р. 2 Менеджер по рекламе 12500 р. 1 Стажер 11200 р. Таблица 7. Сотрудники Табельный номер ФИО Разряд Номер отдела 1001 Иванов И.И. 6 1 1005 Петров П.П. 5 1 1012 Сидоров С.С. 4 1 2004 Федоров Ф.Ф. 5 2 2006 Владимиров В.В. 6 2 2009 Павлов П.П. 3 2 2013 Николаев Н.Н. 3 2 2019 Алексеев А.А. 1 2 3011 Васильев В.В. 2 3 3015 Александров А.А. 5 3 3021 Романов Р.Р. 6 3 3023 Кириллов К.К. 1 3 Таблица 8. Телефоны Номер телефона Номер отдела 211-22-33 1 211-95-34 1 211-78-23 2 7 Установка связей между таблицами Следующим шагом будет установка связей между таблицами. Выберите в меню пункт «Сервис» -> «Связи». В результате в главном окне появится еще одно окно – документа схемы данных. Добавьте по очереди все созданные вами таблицы в окно схемы данных. Замечание 1. Обратите внимание: добавленная в схему данных таблица не исчезает из диалогового окна «Добавить»! Если вы поместили несколько экземпляров одной и той же таблицы, удалите лишние, чтобы не загромождать схему данных. Установить связи между таблицами очень легко. Например, чтобы связать таблицы «Отделы» и «Телефоны», достаточно просто перетащить мышкой поле «Отделы: номер отдела» на поле «Телефоны: номер отдела». Когда вы отпустите клавишу мыши, появится новая связь. Можно вызвать диалоговое окно, в котором указаны таблицы и поля, между которыми устанавливается связь. Отметьте «Обновление каскада». Каскадное удаление связанных записей не менее полезно, однако использование его может привести к потере данных, и мы пока не будем им пользоваться. Замечание 2. О типах связей вы можете прочитать самостоятельно. Если вам потребуется изменить связь, проще всего выделить ее, щелкнув по ней мышкой (черная линия станет жирной), и воспользоваться контекстным меню. Рис. 1. Схема связей в БД «Фирма» Теперь, когда таблицы связаны, можно позаботиться о внешнем представлении данных. С базой данных должно быть удобно работать. Одним из средств такого внешнего представления являются формы. Вопросы 1. Как добавить таблицу в схему данных? 2. В чем отличие между таблицей в режиме конструктора и таблицей в режиме заполнения? 3. Как сделать поле таблицы ключевым? 4. Что произойдет, если ни одно поле таблицы пользователь не сделал ключевым? 5. Обязательно ли добавлять описания в макет таблицы? 6. Опишите, как установить и изменить связи между таблицами. 7. Что такое целостность данных? 8. Что такое потенциальный ключ, внешний ключ? 9. Какие вы можете назвать виды связей? Поясните на примере. 10. Где в базе нарушается целостность данных? 11. Где в базе присутствует логически отношение «многие-ко-многим»? Как оно реализовано? 12. Что такое каскадное обновление и каскадное удаление? Приведите пример. 13. Можно ли добавлять/удалять произвольные данные в родительскую таблицу? В дочернюю? 8 Лабораторная работа №2 Создание форм. Ввод данных с помощью форм Создание форм (часть 1) При создании форм лучше всего сначала воспользоваться услугами мастера форм, а затем отредактировать сделанную им заготовку. Переключитесь в окне базы данных на страницу «Формы». Таблицы в базе данных связаны достаточно сложным образом. Поэтому мы не будем сразу строить форму, отображающую все данные. Начнем с простого: построим форму для таблицы «Сотрудники». Щелкните мышкой на пункте «Использовать мастера для создания формы» и в появившемся диалоговом окне «Создание форм» выберите из выпадающего списка «Таблицы и запросы» пункт «Таблица: Сотрудники». Ваш выбор немедленно отразится на списке доступных полей: в нем будут показаны все поля таблицы «Сотрудники». Теперь следует переместить поля, которые будут отображаться в форме, в список выбранных полей. Чтобы переместить в этот список подсвеченное поле, нужно использовать кнопку «>». Кнопка «>>» перемещает в список выбранных полей все поля, отображенные в списке доступных полей. Какие поля нужно отображать? Очевидно, что отобразить придется поля «Табельный номер» и «ФИО»: эти поля присутствуют только в таблице «Сотрудники». Поля «Разряд» и «Номер отдела» используются для связи с таблицами «Должности» и «Отделы» соответственно. Поэтому лучше взять данные непосредственно из этих таблиц. Мастер форм прекрасно справляется с такой работой, и единственная проблема состоит в том, что созданная им форма не слишком удобна для ввода данных. Вот почему чуть позже нам придется призвать на помощь другого мастера. А пока переместите поля «Табельный номер» и «ФИО» в список выбранных полей и нажмите кнопку «Далее». В следующем диалоговом окне предлагается выбрать субформу. Пока нам не нужно использовать подчиненную форму, поэтому пропускаем этот шаг. На следующем шаге расположим элементы управления в соответствии с предлагаемым мастером вариантом их расположения. Выберите, к примеру, вариант «Столбцы – подписи сверху». На следующем этапе мастер предлагает выбрать режим источника данных, в котором можно запретить отображать данные, запретить изменение, добавление и удаление данных. Пока этот шаг тоже пропустим. Перейдя по кнопке «Далее», можно выбрать стиль оформления формы, а затем назвать форму и определить дальнейшие действия – модификация или работа с формой. По умолчанию имя формы совпадает с именем таблицы – «Сотрудники», и в дальнейшем мы будем так и называть эту форму. Вообще говоря, для наших целей следовало бы открыть форму в режиме конструктора, чтобы изменить макет. Однако сейчас подходящий момент посмотреть на результаты (хотя и промежуточные) вашей работы. Выберите пункт «Работа с формой» и нажмите кнопку «Готово». В результате будет открыто окно, в котором отобразится табельный номер и фамилия первого (по счету в таблице «Сотрудники») сотрудника. С помощью навигационной панели «Запись», расположенной внизу формы, можно перемещаться по записям. Следующий шаг: отобразить отделы, в которых сотрудники работают. Нажмите на кнопку «Редактирование» в окне базы данных. На экране появится панель инструментов (если ее нет, то надо зайти в пункт меню «Вид» -> «Панели инструментов» и включить панель «Элементы управления») и макет формы. Принцип работы с объектами, размещенными на форме, интуитивно ясен: их можно растягивать или перемещать с помощью мыши, можно редактировать текст надписей. Вероятно, некоторое время вы потратите на то, чтобы расположить «красиво» уже имеющиеся поля. Когда вы 9 закончите, обратитесь к панели инструментов. Она снабжена всплывающими подсказками: задержите мышку на пару секунд над кнопкой, и она сообщит вам о своем назначении. Из доступных элементов для наших целей (отобразить названия отделов) подходят два: «Поле» и «Список». Первый элемент является более подходящим, если форма используется в информационных целях. Если же разрабатывается форма для ввода данных, лучше применить список. Это позволит выбирать нужный отдел по названию из выпадающего списка. Щелкните по компоненту «Список» мышкой и переместите ее к окну макета формы. Указатель мыши примет форму компонента с плюсом в верхнем левом углу. Теперь растяните компонент в области формы – тут же появится первое диалоговое окно мастера создания полей со списком. В этом окне нужно выбрать таблицу, на основе которой будут переданы данные в форму. Наш случай очевиден: мы хотим использовать значения из таблицы «Отделы». Поэтому выбираем первый ответ и нажимаем на кнопку «Далее». На следующем шаге требуется выбрать поля, которые станут значениями поля со списком. Нам понадобится отобразить «Наименование», поэтому выберите его и нажмите кнопку «Далее». Для связи данных на следующем шаге необходимо указать связанные поля из таблицы значений (поле «Номер отдела» таблицы «Сотрудники») и поля из таблицы списка (поле «Номер отдела» из таблицы «Отделы»). После этого можно нажимать на кнопку «Готово» и смотреть, что получилось. Теперь на вашей форме уже не два, а три поля, и при перемещении по записям отображается отдел, в котором работает тот или иной сотрудник. Как вы заметите, список у нас никак не именуется, для того, чтобы его как-то назвать, можно опять перейти в режим конструктора формы, поставить метку (с той же самой панели элементов управления) и поэкспериментировать с цветами и выравниванием. В точности так же можно добавить еще один список, который будет отображать должность сотрудника. В качестве выбранных полей из таблицы «Должности» следует использовать «Наименование», связанными полями будут «Разряд» из таблицы «Сотрудники» и «Разряд» из таблицы «Должности». Выполните это самостоятельно. Создание форм (часть 2) Итак, построенная вами форма «Сотрудники» показывает данные о сотрудниках, используя для этого таблицы «Сотрудники», «Отделы» и «Должности». Обратите внимание, что таблицы «Отделы» и «Должности» связаны с таблицей «Сотрудники» отношением «один-ко-многим». Осталась одна незадействованная таблица – «Телефоны». Она напрямую не связана с таблицей «Сотрудники», а связана только с таблицей «Отделы». Вместе с тем при отображении данных о сотруднике было бы удобно видеть номера телефонов, по которым с ним можно связаться. Будем предполагать, что сотрудника некоторого отдела могут пригласить к любому из телефонов этого отдела – и тогда это типичный пример связи «многие-ко-многим», которая вполне очевидно и естественно разбивается на две связи «один-ко- многим» (связи между таблицами «Отделы» и «Сотрудники», а также между таблицами «Отделы» и «Телефоны»). Как представить данные, связанные отношением «многие-ко-многим», на форме? Одна из возможностей состоит в том, чтобы использовать так называемые подчиненные формы. Подчиненная форма – это форма, которая находится внутри другой формы, называемой главной или первичной. Такие формы, как правило, применяются для вывода данных из таблиц, связанных отношением «один-ко- многим». При этом в подчиненной форме выводятся записи, связанные с текущей записью в главной форме. 10 Оставим на время форму «Сотрудники» и создадим форму, выводящую данные об отделах и телефонах, установленных в них. Вновь выберите пункт «Использовать мастера для создания формы» и выберите из таблицы «Отделы» поля «Название отдела» и «Номер отдела» для отображения. На следующем шаге отметьте галочкой «Добавить субформу» и проверьте, чтобы по умолчанию была «Субформа, основанная на выбранных полях». В субформе будем отображать номера телефонов, поэтому выбираем таблицу «Телефоны» и перемещаем в форму все поля этой таблицы. Затем необходимо обозначить поля, которые будут объединять (согласовывать) данные в ваших формах. Ими будут поле «Номер отдела» таблицы «Телефоны» и «Номер отдела» таблицы «Отделы». После этого появится уже знакомое вам диалоговое окно, предлагающее расположить управляющие элементы, только теперь можно выбрать расположение головной формы блоками, а субформы – как лист данных. Следующие шаги, связанные с установкой источников данных, выбором стиля формы и выбором имени формы, вам уже должны быть хорошо знакомы. Выполните их и просмотрите полученный результат. Обратите внимание, что при навигации по записям головной формы в субформе происходит автоматическая смена номеров телефонов. При этом вы можете добавлять новые номера (попробуйте это сделать, например, для второго отдела). Однако подчиненную форму можно создать не только в режиме мастера при создании главной. Проиллюстрируем создание подчиненной формы для формы «Сотрудники» – разместим номера телефонов на главной форме и заставим их согласованно изменяться. Откройте форму «Сотрудники в режиме редактирования». Нам понадобится для работы панель инструментов «Дизайн формы» – она должна быть отображена внизу окна (если ее нет, то включите ее в пункте меню «Вид» -> «Панели инструментов»). Щелчком по кнопке «Навигатор форм» откройте окно навигатора. В нем отображены все формы (в нашем случае пока это одна главная MainForm) и элементы управления, расположенные на форме. Выберите главную форму и из контекстного меню создайте новую форму, которую мы будем делать подчиненной. По умолчанию имя вновь созданной формы – Standart, но можете переименовать ее, например, в SubForm. Пока на ней еще ничего нет, но для размещения здесь номеров телефонов надо правильно прописать свойства формы. Для этого из контекстного меню надо открыть окно «Свойства формы» для нашей субформы и на закладке «Данные» прописать следующие свойства: «Тип содержимого источника» – «Таблица», «Содержимое» – выбираем таблицу «Телефоны», «Связь с главным полем» – делаем щелчок по кнопке с многоточием и в новом диалоговом окне «Link Fields» выбираем поле связи «Номер отдела» для таблиц «Телефоны» и «Сотрудники». Теперь на нашу субформу можно размещать номера телефонов. Но среди управляющих элементов нет способа отображения в виде листа данных, поэтому на панели инструментов «Элементы управления» надо сначала запустить «Дополнительные элементы управления» (при этом проверить, чтобы был активен «Мастер» – иначе при добавлении нового компонента на форму не будет запускаться мастер обработки и придется вручную прописывать все свойства, для которых кое-где надо быть неплохо знакомым с языком запросов…). На дополнительной панели управляющих элементов теперь можно выбрать «Таблицу», и поместить ее на субформу (проверьте, чтобы в навигаторе была активна субформа, а не главная форма). Мастер предложит вам выбрать поля таблицы «Телефоны» для отображения, нам нужны только их номера, поэтому выделяем одно поле и щелкаем по кнопке «Готово». Наша субформа создана. Посмотрите форму «Сотрудники» в режиме работы и попробуйте добавить в нее новые данные (например, поставить еще один телефон). Итак, с помощью формы «Сотрудники» теперь можно получить полные сведения о любом из сотрудников фирмы. 11 Ввод данных с помощью форм Вероятно, вы уже попробовали ввести какие-нибудь новые данные (или отредактировать старые) с помощью созданной формы. Если нет, то откройте форму «Сотрудники» в обычном режиме отображения и попробуйте добавить нового сотрудника (допустим, стажера Никитина Н.Н. с табельным номером 3078) в рекламный отдел, а также установить в рекламном отделе новый телефон (например, 211-70-98; вероятно, вы уже обратили внимание, что в рекламном отделе соблюдается правило: каждому сотруднику – по телефону). Чтобы это сделать, перейдите к первой пустой записи в таблице «Сотрудники». Заполните вручную пустые поля «Табельный номер» и «ФИО», а отдел и должность нового сотрудника выберите из выпадающих списков. Таблица телефонов в этот момент пуста, и добавить новый номер телефона для рекламного отдела пока нельзя. Чтобы это сделать, щелкните по кнопке «Сохранить запись», чтобы сведения о новом сотруднике были внесены в таблицы, а затем допишите в конец выводимого списка телефонов новый номер. А что делать, если в фирме будет создан новый отдел или введена новая должность? Например, принято решение развернуть локальную компьютерную сеть, для обслуживания которой потребуется создать отдел автоматизации, в котором будут работать системный администратор, инженер по обслуживанию аппаратуры и программист? Форма «Сотрудники» пока не предоставляет возможности создавать новые отделы и новые должности. Впрочем, добиться этого не так уж сложно. Вопросы и задания 1. Поясните, какие поля и почему были выбраны для отображения в форме. Как создать поле со списком и для чего оно нужно? 2. Назовите известные вам виды связей между таблицами. Почему возникла необходимость использовать подчиненную форму для отображения телефонов отделов? 3. Создайте формы для введения новых сведений об отделе и должности. 4. Разработайте общую форму, которая позволяла бы не только отображать все необходимые данные о сотруднике, но и вводить новые отделы, должности, новых сотрудников, телефоны. 12 Лабораторная работа №3 Создание и обработка запросов Разработка и использование запросов (часть 1, вводная) В разрабатываемой нами базе данных есть большой недостаток: при добавлении сотрудников через форму в таблице «Отделы» не обновляется поле «Начальник отдела». Почему – понять не так уж сложно. Действительно, не существует ни прямой, ни косвенной связи между данными о разряде сотрудника, номере отдела, в котором он работает, и полем «Начальник». Конечно, было бы удобно, если бы при выборе из выпадающего списка должности «Начальник отдела» табельный номер указанного сотрудника сразу бы заносился в таблицу «Отделы». Однако такая операция требует некоторых навыков программирования, а мы сознательно ограничиваем рассмотрение простейшими приемами работы с базами данных. Поэтому назначать начальника пришлось бы с помощью дополнительного запроса на обновление данных. К сожалению, ООВ не поддерживает запросы на обновление, вставку и удаление данных. Несмотря на то что ООВ во многом является аналогом Access, она проигрывает последнему как по части возможности работы с формой, широте формирования запросов, и совсем уж плохо дела обстоят с отчетами. ООВ предусматривает возможность составления запросов на специальном языке запросов SQL (Standard Query Language), который является общепринятым стандартом в мире баз данных. Поэтому можете самостоятельно попробовать написать вышеперечисленные виды запросов и убедиться в справедливости этого утверждения. Пока же остановимся подробнее на средствах визуального проектирования запросов, не требующих знания какого-либо языка. Конечно, эти средства не отменяют необходимости четко понимать логику запроса. Создание и использование запросов (часть 2, запрос-выборка) Поставим задачу отобразить интересующие нас сведения в следующем формате: |наименование отдела |ФИО сотрудника – начальника отдела|. Это можно сделать с помощью запроса-выборки. Переключитесь на страницу «Запросы» окна базы данных и выберите пункт «Создать запрос в режиме дизайна». Добавьте в новый запрос таблицы «Отделы» и «Сотрудники». В графу «Поле» первой колонки поместите поле «Отделы: наименование» (для этого достаточно сделать двойной щелчок мышью по соответствующему полю в таблице), в графу «Имя таблицы» – «Отделы». Условие отбора должно логически выглядеть как [Отделы]![Начальник] = [Сотрудник]![Табельный номер] Чтобы это условие выполнилось, необходимо в окне конструктора запроса проставить связь между полями «Начальник отдела» таблицы «Отделы» и «Табельный номер» таблицы «Сотрудники» (аналогично тому, как вы это делали в окне схемы данных при проставлении связей между таблицами). Закройте запрос и сохраните его как «Список начальников». Чтобы просмотреть результаты выполнения этого запроса, дважды щелкните по его значку в окне базы данных. Список начальников отделов будет выдан в виде простой таблицы. 13 Замечание 1. Просмотреть результаты запроса, не сохраняя его, можно с помощью кнопки «Выполнить запрос» на панели инструментов. Если вы выберете кнопку «Вкл./выкл. вид дизайна», то сможете посмотреть текст запроса на SQL. Теперь создадим форму на основе только что созданного запроса. Переключитесь на страницу «Формы» окна базы данных и нажмите на кнопку «Создать». Поскольку форма будет создаваться на основе только одного запроса, воспользуйтесь мастером. Сохраните созданную форму под именем «Начальники отделов». Эта форма будет использоваться исключительно в информационных целях. Поэтому хорошим решением будет запретить в соответствующем диалоговом окне мастера как редактирование, так и добавление, удаление данных, отображаемых в этой форме. Замечание 2. Если этого не сделать, то пользователь сможет ввести в форму новый отдел, но не сможет указать его начальника. Такой способ создания новых отделов нельзя признать удачным, потому его и следует исключить. Создание и использование запросов (часть 3, запрос с параметрами) Те запросы, которые были вами созданы, в некотором смысле «статичны». Чтобы изменить характер или отображение информации с помощью этих запросов, нужно редактировать их непосредственно. Это не всегда удобно. ООВ позволяет создавать и использовать запросы, в которых можно изменять некоторые параметры. Например, пользователь хочет узнать размер зарплаты какого-то конкретного служащего. Ему не нужны сведения обо всех, которые можно получить с помощью стандартного запроса- выборки. Замечание 1. Да, конечно, в нашем случае оклад жестко привязан к разрядной сетке. Но со временем у сотрудников могут появиться персональные надбавки, льготы и т. п. Попробуйте дополнить базу данных такой информацией в качестве дополнительного упражнения. Чтобы создать запрос с параметром, создайте сначала обычный запрос-выборку, содержащий следующие данные: ФИО сотрудника и его оклад. Затем в той колонке, в графе «Поле» которой размещено «ФИО», поместите в графе «Критерий» имя параметра, предваренное двоеточием, например, :Fam. Сохраните запрос под именем «Запрос об окладе» и выполните его, дважды щелкнув по значку запроса в окне базы данных. При этом появится диалоговое окно «Ввод параметра» с приглашением «FIO» и пустой строкой, в которой пользователь и должен напечатать данные сотрудника. Напечатайте в этой строке, например, «Филиппов Ф.Ф.», нажмите на «ОК» – и Вы сразу же увидите, что оклад этого сотрудника составляет 13700 р. в месяц. Замечание 2. Фамилию сотрудника, разумеется, надо печатать без кавычек У разработанного запроса есть следующий недостаток. Введенные фамилия и инициалы в точности должны совпадать с теми, что содержатся в базе данных. В противном случае никакой информации не выдается. Попробуйте, например, напечатать в строке запроса «Иванов» (без инициалов) – и, нажав на «ОК», Вы увидите пустую таблицу. В принципе, избежать такой неприятности можно было бы, использовав оператор Like. Если в графе «Критерий» под полем «ФИО» прописать Like ‘Ф*’, то при выполнении запроса будут выведены данные обо всех сотрудниках, чья фамилия начинается с литеры «Ф». Попробуйте самостоятельно написать такой запрос. 14 Создание и использование запросов (часть 4, групповые операции) Достаточно часто возникает необходимость в вычислении каких-либо итоговых величин. Например, нужно узнать величину среднего оклада по отделу или же выяснить, каков фонд заработной платы данного отдела или фирмы в целом. Наконец, можно подсчитать, сколько телефонов приходится на одного сотрудника в данном отделе. В данном пункте мы ограничимся созданием запроса о фонде заработной платы по отделам. Фактически задача сводится к вычислению сумм по группам записей. Выберите на странице «Запросы» окна базы данных пункт «Создание запроса в режиме дизайна» и добавьте в бланк нового запроса таблицы «Отделы», «Должности» и «Сотрудники». Теперь можно приступать к заполнению колонок. В графе «Поле» первой колонки поместите поле «Отделы: наименование». В графе «Имя таблицы» автоматически появится имя таблицы «Отделы», а в графе «Функция» нужно выбрать команду «Group». Вторая колонка будет содержать собственно вычисляемое выражение. Графу «Поле» нужно заполнить по следующему образцу: выбираем в нее поле «Оклад» таблицы должности, а в графе «Функция» выберите «Sum». Вот собственно и все – запрос готов. Замечание 2. Помимо функции Sum существует еще ряд функций, наименования которых можно посмотреть в выпадающем списке графы «Функции». Просмотрите результаты запроса, и если он корректно работает, сохраните его. Вопросы и задания 1. Как можно увидеть текст SQL-запроса, который генерирует ООВ при создании запроса «по образцу» (т.е. с помощью бланка)? Прочитайте этот текст и прокомментируйте его. 2. Имеет ли значение регистр символов в запросе «Запрос об окладе»? Почему? 3. Как запретить ввод в поля формы? 4. Какие групповые операции возможны в ООВ? Опишите их. 5. Создайте запрос с параметром, вычисляющий среднюю величину оклада в данном отделе (название отдела вводит пользователь). 15 Лабораторная работа №4 Генерация отчетов Генерация отчетов (часть 1) Создание отчетов в ООВ возможно с использованием мастера. После создания отчета доступна последующая коррекция макета, но возможные действия относятся более к оформлению макета, чем к определению его структуры и содержания. В данной работе будут создаваться только простые отчеты. ООВ позволяет создавать отчет либо на основе одного запроса, либо на основе одной таблицы. Если вам необходимо вывести данные из нескольких таблиц, то перед созданием отчета предварительно придется написать запрос, выводящий необходимые данные. Выберите пункт «Использовать мастер для создания отчета» и в качестве основы для отчета запрос «Список начальников». В списке доступных полей вы увидите два поля: «Отделы: наименование» и «Сотрудники: ФИО». Переместите оба поля в список выбранных полей и нажмите на кнопку «Далее». Следующее диалоговое окно предлагает выбрать названия меток для выбранных полей. Их, естественно, вы пропечатываете сами, но по умолчанию названия меток совпадают с именами полей. Следующий шаг «Структура» необходим для создания отчета с группировкой данных. Пока нас не интересует эта возможность, поэтому переходим далее. Шаг «Выбора стиля» содержит варианты оформления разметки данных и разметки колонтитулов. Также, если это необходимо, есть возможность выбирать ориентацию страницы – книжную или альбомную. Наконец, на последнем шаге предлагается на выбор создание статического и динамического отчетов (нам нужен динамический, чтобы любое изменение в данных базы отображалось в отчете) и варианты работы: модификация отчета или его просмотр. Выберите просмотр отчета и оцените результаты своей работы и работы ООВ. Переключитесь в режим редактирования макета отчета и попробуйте самостоятельно поварьировать его внешний вид. К примеру, попробуйте изменить названия меток, цвета шрифта, формат даты и номеров страниц. Генерация отчетов (часть 2) Теперь, когда у вас появился некоторый опыт в создании отчетов, перейдем к разработке отчетов с группировкой данных. Для этого создадим отчет, содержащий список сотрудников фирмы, сгруппированный по отделам. Напишите соответствующий запрос и начните создавать на его основе отчет. В диалоговом окне «Структура» следует добавить группировку по полю «Отделы: наименование». Для этого в списке в левой части диалогового окна нужно выделить это поле и нажать на кнопку «>». Завершите создание отчета самостоятельно. Вопросы и задания 1. Разработайте усовершенствованную версию отчета «Сотрудники фирмы». В новом отчете дополнительно должны указываться должности сотрудников, а фамилии сотрудников должны быть расположены в порядке, соответствующем старшинству должностей (старшей считается должность начальника, далее – заместитель начальника и т. д. Подсказка: воспользуйтесь номерами разрядов). Если сотрудники занимают одинаковые должности, их фамилии должны располагаться в алфавитном порядке (воспользуйтесь сортировкой данных). 2. Как можно изменить порядок сортировки значений того или иного поля в готовом отчете? 16 Лабораторная работа №5 Индивидуальные задания Задания для самостоятельного выполнения В индивидуальных заданиях предполагается самостоятельная разработка базы данных средствами Open Office Base. Должны быть созданы (и заполнены) таблицы, формы для ввода и редактирования данных, наиболее «вероятные» запросы и отчеты (с точки зрения пользователя). В самом задании указаны некоторые возможности, которые должна предоставлять разработанная база данных. Реализация этих возможностей обязательна. При этом подразумевается, что данные можно добавлять и редактировать. Удаление данных (разумеется, согласованное) не требуется в обязательном порядке ни в одном из вариантов; реализация такой возможности приветствуется. При сдаче задания необходимо продемонстрировать схему данных (и прокомментировать ее), показать, как можно внести новую информацию в базу данных и отредактировать уже имеющиеся записи, представить имеющиеся запросы и отчеты и создать запрос (или отчет), предложенный преподавателем. 1. Разработайте базу данных «Библиотека». Она должна содержать данные о книгах, их авторах, месте и времени издания. У книги может быть несколько авторов, автор же может написать несколько книг. В библиотеке может быть несколько экземпляров одной книги (с разными инвентарными номерами), при этом какие-то из них находятся «на стеллажах», а какие-то «на руках у читателей». База данных позволяет узнать, есть ли такая книга в библиотеке вообще и может ли она быть выдана в данный момент. 2. Разработайте базу данных «Склад». На складе хранятся товары бытового назначения, которые поступают от поставщиков и распределяются по сети магазинов. Один поставщик может поставлять несколько товаров (допустим, утюги, электрические чайники и т. п.). В то же время товар одного наименования может поставляться разными поставщиками. (Предполагаем, что товары производятся малоизвестными фирмами и их цена определяется исключительно функциональностью, а не «громким именем»). База данных позволяет «заказать» товар у поставщика, «отгрузить» товар в магазин (в том случае, если товар в запрашиваемом количестве присутствует на складе). 3. Разработайте базу данных «Издательство». Издательство работает с авторами, заказывая им книги по разным темам. При этом книги по одной теме могут быть заказаны нескольким авторам. В то же время один автор может работать над книгами сразу по нескольким темам. Книги состоят из глав. Завершив главу, автор «отсылает» ее в издательство. Когда книга готова более чем на 70%, издательство помещает ее в свой тематический план. 4. Разработайте базу данных «Магазин». Магазин продает товары бытового назначения (впрочем, род товаров вы можете определить самостоятельно). Покупатель может приобрести несколько товаров одновременно, при этом «выбивается» единый чек. Поскольку на проданный товар существует гарантия, необходимо обеспечить хранение сведений о товаре на протяжении всего гарантийного срока. В течение этого срока покупатель может обратиться в магазин с просьбой о замене некачественного товара. После проверки, действительно ли товар куплен в этом магазине (по номеру чека) и гарантия на него действует, товар должен быть заменен. Напомним, что запас товаров в магазине должен пополняться по мере их продажи. 17 5. Разработайте базу данных «Подписка». Почтовое отделение проводит подписку на газеты и журналы. Каждый подписчик может оформить подписку на несколько изданий одновременно. Разумеется, одно издание может быть выписано несколькими подписчиками. Будем считать, что подписка может быть оформлена на срок от 2 до 12 месяцев. База данных позволяет узнать, сколько времени осталось до окончания действия подписки (и, быть может, напомнить о том, что ее следует продлить). Среди подписных изданий могут появляться новые, а некоторые существующие могут прекратить выпуск. В последнем случае об этом следует известить подписчиков. 6. Разработайте базу данных «Фонотека». В ней должны содержаться сведения об авторах и исполнителях музыкальных произведений, а также носителях, на которых они записаны (кассеты, диски различных форматов и т. п.). Одно и то же музыкальное произведение может быть записано в исполнении различных музыкантов. В то же время музыкант может исполнять различные произведения разных авторов. База данных позволяет узнать о наличии в фонотеке той или иной записи (поиск можно вести как по названию произведения, так и по фамилии автора или исполнителя), прослушать выбранную запись (мы не будем обсуждать техническую реализацию этой функции), а также составить различные рейтинги (по числу обращений). 7. Разработайте базу данных «Отдел кадров предприятия». В ней должны храниться сведения о сотрудниках, их перемещениях по службе, изменениях в оплате труда (в том числе доплатах за стаж), об отпусках. Структурной единицей предприятия считается отдел. Сотрудник может работать только в одном отделе. Одновременно в отпуске может находиться не более 30% списочного состава отдела, при этом «руководящий состав» должен уходить в отпуска строго по очереди (заместитель начальника отдела не может уйти в отпуск, пока из отпуска не вернется начальник). Сотрудник может переходить из одного отдела в другой, при этом стаж должен сохраняться. База данных позволяет узнать различные сведения о сотруднике, в том числе – может ли он уйти в отпуск в данные сроки. 8. Разработайте базу данных «Турнир». В турнире участвует несколько (спортивных) команд, причем каждая команда в течение турнира должна сыграть со всеми остальными не менее 2 раз. Турнир проводится в несколько туров (посчитайте, сколько). Игры одного тура должны проводиться в один день. За победу команде присуждается 3 очка, за ничью – 1 очко, за поражение – 0 очков. База данных позволяет узнать статистику (число результативных игр и ничьих) для каждого тура или для каждой команды (число очков, побед, поражений, ничьих). Будем считать, что расписание турнира составляется вручную; база данных должна дать ответ: сколько игр данная команда провела с тем или иным соперником. (Возможно, вам будет удобно разделить турнир на циклы, полагая, что в течение цикла каждая команда должна ровно один раз сыграть со всеми остальными). 9. Разработайте базу данных «Хобби». База данных должна содержать сведения о людях и их увлечениях. Каждый человек может иметь несколько увлечений. Несколько людей могут увлекаться одним и тем же. Список увлечений должен быть расширяемым. Следует хранить информацию об образовании и / или профессии каждого человека, его возрасте, степени увлеченности предметом (глубине знаний в этой области). База данных позволяет найти человека, который наиболее подробно может ответить на некоторый вопрос. Вопрос определяется предметными областями (и может, вообще говоря, относиться к нескольким предметным областям сразу). 18 10. Разработайте базу данных «Меню». База данных должна содержать кулинарные рецепты различных блюд в разных категориях, например, «Закуска», «Горячее блюдо», «Десерт» и т. п. Рецепт каждого блюда приводится в расчете на одну порцию, указывается также длительность изготовления. База данных позволяет узнать, какие продукты и в каком количестве потребуются, согласно выбранному меню, на день (завтрак, обед, ужин) или на праздничный ужин, а также то, сколько времени потребуется на приготовление (возможно, временем приготовления, допустим, обеда можно считать время приготовления того блюда, у которого оно наибольшее). 11. Справочник туриста. Турагенства и предлагаемые услуги: страна, город (или маршрут круиза), условия проживания и проезда, экскурсионное обслуживание, сервис принимающей стороны, стоимость путевки. 12. Салон видеопроката. База видеофильмов: название, студия, жанр, год выпуска, режиссер, исполнители главных ролей, краткое содержание, субъективная оценка фильма. Факт наличия фильма в видеотеке. Оформление выдачи и возврата кассеты. 13. Ежедневник. База намечаемых мероприятий – дата, время и протяженность, место проведения. Автоматическое напоминание ближайшего дела: по текущей дате и времени; удаление вчерашних дел либо перенос на будущее. Анализ «накладок» – пересечений планируемых дел. Просмотр дел на завтра, послезавтра и так далее. 14. Бюро знакомств. База потенциальных женихов и невест: пол, регистрационный номер, дата регистрации, сведения о себе, требования к партнеру. Выбор подмножества подходящих кандидатур, подготовка встреч (формирование приглашения для знакомства). Перенос в архив пар, решивших свои семейные проблемы, удаление клиентов, отказавшихся от услуг. 15. Записная книжка. Анкетные данные, адреса, телефоны, место работы или учебы, должность знакомых, коллег и родственников, характер знакомства, деловые качества и так далее. Автоматическое формирование поздравления с днем рождения (по текущей дате). Упорядочение по алфавиту и по дате последней корректировки. Поиск по произвольному шаблону. 16. Справочник коммерческих банков. Наименование, адрес, статус (форма собственности), условия хранения средств на лицевом счете (годовые проценты на различных видах вкладов). Выбор банка с наибольшим процентом для заданного типа вклада. 17. Автосалон. База новых и подержанных отечественных и зарубежных автомобилей: марка, год выпуска, технические характеристики, особенности исполнения, техническое состояние, запрашиваемая цена. База покупателей: контактные координаты, требования к марке, техническим характеристикам и техническому состоянию, финансовые возможности. Автоматизация подбора вариантов для покупателя, формирование заявки для поставщиков и перегонщиков. 18. Крылатые фразы. Справочник пословиц, поговорок, афоризмов, каламбуров, других словесных курьезов. Классификация по авторам и источникам, поиск по темам и ключевым словам. 19. Справочник астронома. Для каждой из зарегистрированных звезд известны: название, созвездие, видимая звездная величина, расстояние, координаты на небосклоне. Поиск звезд заданного созвездия, самых ярких звезд, видимых звезд и созвездий в заданной точке земного шара в заданное время. 20. Ломбард. База хранимых товаров и недвижимости: анкетные данные клиента; наименование товара; оценочная стоимость; сумма, выданная под залог; дата сдачи; срок хранения. Операции приема товара, возврата, продажи по истечении срока хранения. 19 |