ЛАБОРАТОРНАЯ РАБОТА 4.
ОСНОВЫРАБОТЫС БАЗОЙ ДАННЫХ.
ЦЕЛЬ РАБОТЫ
Научиться создавать базы данных (БД), манипулировать с ними при помощи запросов в режиме QBE, генерировать формы и отчеты.
ПРАКТИЧЕСКОЕ ЗАДАНИЕ
1. На основе группы атрибутов согласно варианту задания спроектировать две таблицы со справочной информацией и одну таблицу учетного характера, для этого следует:
a) определить типы значений атрибутов и их размерность;
b) определить первичные и внешние ключи в таблицах и необходимые связи между ними для обеспечения целостности БД.
2. Создать файл БД в какой-либо среде (например, MS Access) и сохранить его в своей сетевой папке.
3. Создать таблицы БД, при этом обязательно:
a) при создании справочных таблиц обозначить ключевые поля;
b) при создании учётной таблицы для внешних ключей использовать столбцы подстановок (код и характеризующий его столбец) из справочных таблиц, при этом следует отменить опцию: «скрыть ключевой столбец» и выбрать основной столбец подстановки;
c) просмотреть схему данных со связями между таблицами.
4. Внести данные в таблицы (не менее 10 кортежей в каждой таблице) и убедиться, что система поддержки целостности БД функционирует.
5. Используя язык запросов QBE (язык запросов по образцу) выполнить согласно варианту задания запросы:
a) на удаление, обновление, создание таблицы;
b) на выборку данных из одной или более таблиц;
c) на выборку данных с использованием агрегатных функций;
d) перекрестные запросы.
6. Создать 2 простые формы для каждой справочной таблицы в отдельности на основе Автоформы:
a) Выполнить коррекцию форм при помощи Конструктора;
b) Просмотреть созданные формы по записям;
|
|
c) Ввести новые записи в таблицы через формы согласно заданию;
d) Внести изменения в таблицы через формы согласно заданию;
e) Отсортировать записи в таблицах согласно заданию;
f) Создать и применить фильтр согласно заданию;
g) Изменить простые формы на ленточные и просмотреть их по записям.
Создать 2 составные формы: подчиненную и связанную.
A) В режиме Конструктора отредактировать одну из составных форм и добавить в нее вычисляемое поле согласно заданию, а также украсить это поле рамкой, цветом.
8. Создать сводный отчет по двум таблицам.
a) Предусмотреть в отчете текущие итоги по какой-либо агрегатной функции;
B) Просмотреть отчет и если необходимо, отредактировать его в режиме Конструктора.
9. Добавить в отчет вычисляемое поле согласно заданию;
10. Добавить рисунок в верхний колонтитул и примечание с датой создания отчета и фамилией исполнителя
Просмотреть созданный отчет.
12. Написать отчет о выполнении лабораторной работы, который должен содержать:
a) описание таблиц с указанием типа атрибутов, первичных и внешних ключей;
b) схему связей между таблицами;
c) протокол работы с результатами;
d) краткие выводы.
ТЕОРЕТИЧЕСКОЕ ВВЕДЕНИЕ
База данных - это множество взаимосвязанных единиц данных, организованных по определенным правилам.
СУБД – это программное обеспечение и совокупность языковых средств, предназначенных для создания, сопровождения и использования баз данных.
Реляционная модель данных (РМД) - это набор таблиц или отношений (relation), изменяющихся во времени.
|
|
Элементы РМД:
1) сущность – объект любой природы, данные о котором хранятся в БД;
2) отношение (таблица);
3) схема отношения – строка заголовков таблицы;
4) атрибут – столбец таблицы;
5) кортеж – строка таблицы;
6) первичный ключ – один или несколько атрибутов, однозначно определяющих кортеж;
7) внешний ключ – это атрибут одного отношения, являющийся первичным ключом другого отношения.
Например,
Сотрудники
| ФИО | ОТДЕЛ | ДОЛЖНОСТЬ | ДАТА_РОЖДЕНИЯ |
| ИВАНОВ | АХО | Инженер по ТБ | 23.08.62 |
| ПЕТРОВ | АХО | Зав.складом | 11.02.60 |
| СИДОРОВ | АСУ | Инженер | 24.03.69 |
Первичный ключ Внешний ключ
Ставки
| ДОЛЖНОСТЬ | ОКЛАД |
| Инженер по ТБ | |
| Зав.складом | |
| Инженер |
Первичный ключ
Минимальный набор атрибутов, однозначно определяющих кортеж, называется ключом. Если ключ определяется более чем одним атрибутом, то он называется составным ключом.
В любом отношении может оказаться несколько ключей - потенциальных ключей. Один ключ, выбранный среди потенциальных ключей, называется первичным ключом.
Набор атрибутов одного отношения, являющийся ключом другого (или его же), называется внешним ключом. Внешний ключ используется для установления связи между отношениями.
На отношения в реляционной модели накладываются следующие ограничения:
· все кортежи в отношении должны быть уникальными;
· имена атрибутов отношения должны быть так же уникальными;
· все кортежи отношения должны иметь одинаковую структуру, соответствующую типам атрибутов;
· значения атрибутов должны быть атомарными, то есть не содержать групп значений;
|
|
· порядок следования кортежей не определен.
Обеспечение целостности БД достигается рядом ограничений целостности, включающих: 1)ограничения значений атрибутов; 2)структурные ограничения на кортежи. Первый тип ограничений достигается в СУБД наличием контроля значений атрибутов. Структурные ограничения на кортежи, в свою очередь, предполагают, во-первых, наличие у каждого кортежа отношения первичного ключа, и, во-вторых, требуют обеспечения ссылочной целостности. Ссылочная целостность предполагает наличие для каждого значения внешнего ключа кортежа с таким же первичным ключом.
Например, каждому значению внешнего ключа ДОЛЖНОСТЬ отношения R3 соответствует кортеж с таким же ключом в отношении R4.
| ФИО | ОТДЕЛ | ДОЛЖНОСТЬ | ДОЛЖНОСТЬ | СТАВКА | |
| ИВАНОВ | ИНЖ. | ИНЖ. | |||
| СИДОРОВ | СТ. ИНЖ. | СТ. ИНЖ. | |||
| ПЕТРОВ | ТЕХН. | ТЕХН. |
Очевидно, что нарушение ссылочной целостности приведет к нарушению целостности данных в БД.
Языки запросов QBE и SQL.
Обрабатывать информацию, содержащуюся в таблицах БД можно путем использования запросов или в процессе работы специально разработанной программы – приложения.
Запрос представляет собой инструкцию на отбор записей. Большинство СУБД разрешают использовать запросы следующих видов:
1) запрос-выборка, предназначенный для отбора данных, хранящихся в таблицах, и не изменяющий эти данные;
2) запрос на изменение, предназначенный для изменения или перемещения данных.
К этому виду запросов относятся:
· запросы на добавление записей;
· запросы на удаление записей;
· запросы на обновление данных;
· запросы на создание таблицы.
Для подготовки запросов, как правило, используются два основных языка описания запросов:
1) язык QBE (Query By Exampl e) – язык запросов по образцу;
2) язык SQL (Structured Query Language) –структурированный язык запросов.
Язык QBE предполагает ручное или визуальное формирование запросов.
Язык SQL предполагает программирование запроса.
Типы данных.
Каждый атрибут отношения должен иметь определенный тип данных, который входит в определение самого атрибута. Стандарт языка SQL поддерживает многие типы данных, в том числе:
· CHAR(n) - Символьная строка фиксированной длины n.
· VARCHAR(n) - Символьная строка переменной длины. Здесь необязательный параметр n определяет максимальное число символов.
· NUMERIC(p,s) - Число с общим количеством знаков p и количеством знаков после запятой s.
· INT - Целое число;
· FLOAT(s) - Число с плавающей запятой. Здесь s – число знаков в мантиссе;
· BIT(n) - Двоичная строка фиксированной длины n;
· VARYING(n) - Двоичная строка переменной длины;
· DATE - Содержит поля year, month и day и имеет формат yyyy-mm-dd;
· TIME(s) - Содержит поля hour, minute и second и имеет формат hh:mm:ss. Параметр s определяет количество знаков после запятой в поле second;
ПРИМЕР ВЫПОЛНЕНИЯ РАБОТЫ
В качестве примерарассмотрим отношение, соответствующее варианту:
Таблицы
1. Код_сотрудника, ФИО, Должность, Оклад, Дом_адрес
2. Код_товара, Наименование_товара
3. Код_сотрудника, Код_товара, Цена_приходная, Приход, Дата_поступления
Определим типы значений атрибутов:
Код_сотрудника – INT
ФИО - CHAR(20)
Должность - CHAR(15)
Оклад – FLOAT
Дом_адрес - CHAR(30)
Код_товара – INT
Наименование_товара - CHAR(20)
Цена_приходная - FLOAT
Приход - FLOAT
Дата_поступления – DATE
В 1-ой таблице первичный ключ - Код_сотрудника, во 2-ой таблице - Код_товара, в 3-ей таблице мы имеем составной первичный ключ - Код_сотрудника+Код_товара, каждый атрибут которого является также внешним ключом.
Для обеспечения целостности БД определим связи между атрибутами таблиц:
1) Код_сотрудника(1) ( один ко многим) Код_сотрудника (3)
2) Код_товара(2) ( один ко многим) Код_товара(3)
Запустим СУБД MS Access и создадим файл базы данных. Используя режим Конструктора создадим справочные таблицы Сотрудники и Товары и обозначим в них первичные ключи (кнопка с изображением ключа на панели инструментов).
Далее введем по 10 записей в каждую из справочных таблиц.
Создадим в режиме Конструктора третью таблицу – учетную, используя при этом для столбцов Код_сотрудника и Код_товара тип: Мастер подстановок. Процесс создания подстановки значений для столбца Код_сотрудника из таблицы Сотрудники показан на рисунках 6.4.1 - 6.4.6.
Рис. 6.4.1 Выбор типа столбца подстановки
Рис. 6.4.2 Выбор таблицы, содержащей значения для столбца подстановки
Рис. 6.4.3 Выбор полей, содержащих значения для столбца подстановки
Рис. 6.4.4 Выбор ширины столбцов для подстановки
Рис. 6.4.5 Выбор поля, определяющего столбец подстановки
Рис. 6.4.6 Выбор названия для столбца подстановки
При завершении процесса подстановки СУБД потребует сохранения таблицы, необходимо сохранить её и дать ей название Учетная. Не выходя из режима Конструктора следует выполнить аналогичные действия для создания подстановки значений для столбца Код_товара из таблицы Товары (см. рис. 6.4.7).
Рис. 6.4.7 Создание подстановки для столбца Код_товара
И так далее, надо выполнить все шаги для создания столбца подстановки. Не выходя из режима Конструктора, нужно задать всеоставшиеся атрибуты и их типы, завершить процесс создания таблицы и сохранить таблицу.
Нажав кнопку 
(схема данных) на панели инструментов, можно просмотреть связи между созданными таблицами, при правильных действиях схема будет иметь вид, показанный на рисунке 6.4.8.
Если же при создании таблиц были допущены ошибки (не заданы первичные ключи, не созданы столбцы подстановок и другие), то следует исправить ошибки и создать связи в схеме данных.
Так например, для создания многозначной связи:
Код_сотрудника(1) ( один ко многим) Код_сотрудника (3)
следует мышкой поле Код_сотрудника из таблицы Сотрудники совместить с полем Код_сотрудника из таблицы Учетная, в открывшемся диалоговом окне установить галочку Обеспечение целостности данных и завершить операцию.
Аналогично устанавливается связь:
Код_товара(2) ( один ко многим) Код_товара(3).
Рис. 6.4.8. Схема данных.
Далее следует внести 10 записей в таблицу Учетная.
Выполним запросы, используя язык QBE (язык запросов по образцу).
1. Удалить из базы данных информацию по заданному наименованию товара.
a) В окне базы данных выбрать объект Запросы.
b) Создать запрос в режиме Конструктора.
c) Добавить нужную таблицу, нажать кнопку 
(тип запроса) на панели инструментов и выбрать запрос на удаление.
d) Выбрать поля, необходимые для запроса, и написать условие для удаления (рис. 6.4.9).
e) Нажать кнопку 
(запуск) и выполнить запрос. В результате из таблицы Товары будет удалена одна запись
Замечание: Если в таблице Учетная есть приход по этому виду товара, то удаление может не произойти.
2. Увеличить в два раза оклад сотрудникам с заданной должностью.
Выполним аналогичные действия и выберем тип запроса на обновление (рис. 6.4.10).
Рис. 6.4.9 Запрос 1 на удаление
Рис. 6.4.10 Запрос 2 на обновление
3. Создать новую таблицу данных, в которую занести наименования товаров и их цены, превышающие заданное значение.
Выполним такие же действия и выберем тип запроса на создание таблицы (рис. 6.4.11).
Рис. 6.4.11 Запрос 3 на создание таблицы
4. Выбрать коды и наименования товаров с кодами от 3 до 7.
Рис. 6.4.12 Запрос 4 на выборку значения из определённого интервала
5. Выбрать все сведения о сотрудниках, проживающих в указанном микрорайоне.
Рис. 6.4.13 Запрос 5 на выборку всех полей таблицы
6. Выбрать коды, названия и цены товаров, поступивших в заданном месяце.
Запросы 4, 5, 6 – это запросы на выборку. Используя тип запроса на выборку и формируя те или иные условия выборки, мы получаем требуемые данные (рис. 6.4.12 – 6.4.14).
Рис. 6.4.14 Запрос 6 на выборку временного периода
7. Определить количество кодов товара с ценой, не превышающей заданное значение.
Рис. 6.4.15 Запрос 7 на выборку с использованием групповой операции count()
8. Определить ФИО и должность сотрудника с наибольшим окладом.
Рис. 6.4.16 Запрос 8 на выборку с использованием подзапроса для агрегатной функции
9. Определить название товара и его цену, являющуюся минимальной за весь период.
Рис. 6.4.17 Запрос 9 на выборку с использованием подзапроса для агрегатной функции
10. Определить общую стоимость товаров, оприходованных в заданном месяце.
Рис. 6.4.18 Запрос 10 на выборку с использованием групповой операции Sum()
Запросы 7 - 10 – это тоже запросы на выборку, но при их формировании необходимо использовать агрегатные функции (рис. 6.4.15 - 6.4.18). Чтобы их использовать в запросах, нужно в режиме Конструктора выполнить команду Вид -> Групповые операции.
Иногда бывает, что использование групповых операций невозможно (например, когда в запросе на экран выводятся столбцы, не участвующие в групповых операциях), тогда для агрегатных функций пишутся подзапросы. К таким случаям относятся запросы 8 и 9 (рис. 6.4.16 и 6.4.17)
11. Определить общее количество поступивших товаров в разрезе товаров и сотрудников.
Рис. 6.4.19 Запрос 11 - перекрестный
12. Определить общую стоимость поступивших товаров в разрезе товаров и сотрудников.
Рис. 6.4.20 Запрос 12 – перекрестный
Запросы 11, 12 – перекрестные запросы. При их выполнении следует выбрать тип запроса перекрестный (рис. 6.4.19. и 6.4.20)
Для создания простых форм на основе Автоформы следует:
выбрать объект Формы и нажать кнопку Создать, в открывшемся окне выбрать: Автоформа: в столбец, в качестве источника данных указать нужную таблицу. Форма будет создана автоматически (рис. 6.4.21).
Рис. 6.4.21 Автоформа в столбец
Аналогично можно создать форму для таблицы Товары. Если исходная информация таблиц полностью не просматривается, или пользователя не устраивает дизайн формы, то можно изменить ее в режиме Конструктора (рис. 6.4.22).
Рис. 6.4.22 Форма в режиме Конструктора
Форма служит для удобного ввода, просмотра и редактирования данных. Кроме того, в форме можно сортировать и фильтровать данные. Для этого нужно выделить поле или значение для сортировки или фильтрации и щелкнуть по кнопкам 
(сортировка по возрастанию и убыванию) или по кнопкам 
(установить фильтр, изменить фильтр, отменить фильтр) соответственно. В результате в форме данные будут отсортированы или отфильтрованы.
Чтобы изменить внешний вид формы, например, изменить простую форму на ленточную нужно открыть форму в режиме Конструктора, ищелкнуть по кнопке 
(свойства), в окне свойств формы (рис. 6.4.23) выбрать Режим по умолчанию - > Ленточные формы.
Рис. 6.4.23 Свойства формы
Вид ленточной формы представлен на рисунке 6.4.24.
Рис. 6.4.24 Ленточная форма
Составная форма (на основе двух или более таблиц) может быть подчинённой или связанной, она создается с помощью Мастера по следующим шагам: Формы -> Создание формы с помощью Мастера.
В окне Создание Форм (рис. 6.4.25) выбрать все поля из двух связанных по внешнему ключу таблиц (связанные поля выбрать только один раз), в следующем окне можно выбрать тип формы (связанная или подчинённая), далее - выбрать внешний вид (табличный), стиль и имя формы.
Рис. 6.4.25 Окно Создание форм
Рис. 6.4.26 Связанная форма
В результате получится связанная (рис. 6.4.26) или подчиненная (рис. 6.4.27) форма.
Рис. 6.4.27 Подчиненная форма
Чтобы включить вычисляемое поле в форму или отчет, надо открыть объект в режиме Конструктора, затем, используя Панель элементов (рис. 6.4.28), вставить поле в свободную область формы. Панель элементов открывается автоматически, если же она не открылась, то её можно открыть при помощи кнопки 
.
Рис. 6.4.28 Панель элементов
Поле вставляется вместе с надписью. Для нашего примера вычисляемое поле должно быть: Стоимость товара = (Цена_приходная*Приход). Щелкнув по надписи и затем по кнопке Свойства, зададим в окне свойств Подпись: Стоимость товара (рис. 6.4.29).
Аналогично предыдущим действиям, зададим свойства Данные для вычисляемого поля (рис. 6.4.30). Для этого рекомендуется использовать Построитель выражений (рис. 6.4.31). Он вызывается кнопкой 
. В нем можно для поля написать формулу для вычисления, в которой участвуют поля формы. Завершив процесс конструирования, получим форму с вычислением стоимости товара (рис. 6.4.32).
Рис. 6.4.29 Свойства надписи для вычисляемого поля
Рис. 6.4.30 Свойства вычисляемого поля
Рис. 6.4.31 Построитель выражений
Рис. 6.4.32 Форма с вычисляемым полем
Сводный отчет (см. рис. 6.4.33) составляется с помощью Мастера, используя кнопку Итоги, можно добавить агрегатную функцию. Созданный отчет можно редактировать в режиме Конструктора, в частности добавлять вычисляемые поля, примечания и рисунки.
Рис. 6.4.33. Сводный отчет
ВАРИАНТЫЗАДАНИЙ