Обработка данных с помощью запросов

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

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

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

Естественное соединение – возвращает отношение кортежей, которые являются результатом «склеивания» одинаковых значений атрибутов, являющихся общими для двух исходных отношений.

Условие применения — наличие хотя бы одного общего атрибута в отношении А и В.

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

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

Машина JOIN Техобслуживание [Рег номер, Вид работы]

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

Для решения этой задачи целесообразно использовать и операцию проекции.

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

Для реализации запроса использовали все атрибуты таблицы Машина и атрибут Вид работы из таблицы Техобслуживание.

В запросе было создано вычисляемое поле месяц прохождения: Month([Дата]) для выделения месяца из даты прохождения техобслуживания.

В результате был создан запрос Естественное соединение, содержащий все необходимые данные (Приложение Н).

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

Задача. Сформировать сведения о машинах, прошедших техобслуживание 23.05.2011.

Техобслуживание WHERE Дата = 23.05.2011#

Для реализации данной операции реляционной алгебры создали запрос в режиме Конструктора. Для этого выбрали команду Конструктор из диалогового окна Новый запрос. В результате произведенных действий появилось диалоговое окно Добавление таблицы, в котором выбрали таблицу Техобслуживание и нажали кнопку Добавить, затем кнопку Закрыть. Для осуществления выборки в режиме Конструктора указали Условие отбора в режиме параметрического запроса для того, чтобы не создавать запрос каждый раз, как только изменится условие. В строке Условие отбора в поле Дата ввели [Укажите дату техобслуживания].

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

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

Условие совместимости, это совместимость по типу, то есть в пересечении участвуют те таблицы, которые совпадают по составу полей и типам данных для них.

Задача. Определить машины, которые проходили ранее техобслуживание и проходящие техобслуживание.

Техобслуживание INTERSECT Техобслуживание_Вып

Для реализации операции реляционной алгебры Пересечение выбрали команду Конструктор из диалогового окна Новый запрос. В результате произведенных действий появилось диалоговое окно Добавление таблицы. В данном диалоговом окне переключились на вкладку Запросы, последовательно выбрали запросы Техобслуживание и Техобслуживание_Вып, нажали кнопку Закрыть. В окне Конструктора осуществили связывание запроса Техобслуживание с запросом Техобслуживание_Вып посредством перетаскивания поля № техобс из запроса Техобслуживание в запрос Техобслуживание_Вып. Для формирования запроса выбрали все поля из запроса Техобслуживание.

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

Вычитание – возвращает кортеж, принадлежащий первому исходному отношению, но не принадлежащий второму.

Условие совместимости, это совместимость по типу, то есть в пересечении участвуют те таблицы, которые совпадают по составу полей и типам данных для них.

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

Машина MINUS Машина1

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

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

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

Задача. Сформировать список Рег номер машины, Владелец машины.

Для реализации данной операции реляционной алгебры создали запрос в режиме Конструктора. Для этого выбрали команду Конструктор из диалогового окна Новый запрос. В результате произведенных действий появилось диалоговое окно Добавление таблицы, в котором последовательно выбрали таблицу Машина, затем нажали кнопку Закрыть. Из таблицы Машина выбрали поля Рег номер, Владелец. На созданном бланке запроса определили поля, которые будут отображаться в запросе. Для этого определили свойство «Вывод на экран» для каждого поля запроса, посредством проставления или снятия флажка.

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

Декартово произведение (PROJECT) позволяет сформировать новые отношения, содержащие все возможные комбинации кортежей исходных отношений.

Условие применимости: атрибуты исходных отношений различны, повторяющих атрибутов нет.

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

При реализации операции Произведения, выбрали таблицы, несвязанные между собой.

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

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

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

Условие применимости: отношения не должны содержать одинаковых атрибутов.

Задача. Сформировать запрос, содержащий сведения о марках машин в сочетании с использованными запчастями в их ремонте (Название марки машины ввести с клавиатуры).

(Машина[Марка] TIMES Запчасть [Наименование]) WHERE Марка = «ВАЗ 2109»

Для реализации данной операции реляционной алгебры создали запрос в режиме Конструктора. Для этого выбрали команду Конструктор из диалогового окна Новый запрос. В результате произведенных действий появилось диалоговое окно Добавление таблицы, в котором последовательно выбрали таблицы Машина и Запчасть, затем нажали кнопку Закрыть. Из таблицы Машина выбрали поле Марка; из таблицы Запчасть – Наименование. Для задания параметра отбора записей в поле Условие отбора для поля Марка ввели условие «ВАЗ 2109».

В результате был создан запрос Тэта-соединение, содержащий все необходимые данные (Приложение Н).

Объединение – возвращает отношения, содержащие кортежи, которые принадлежат или первому исходному отношению или второму или обоим одновременно.

Условие применения – А и В должны быть совместимы по типу, т.е. одинаковая степень, одинаковые имена атрибутов и одинаковые атрибуты определяются на одинаковых доменах.

Задача. Сформировать список всех машин, проходивших и проходящих ремонт в автомастерской.

Машина UNION Машина1

Для реализации данной операции реляционной алгебры создали запрос в режиме Конструктора. Для этого выбрали команду Конструктор из диалогового окна Новый запрос. В результате произведенных действий появилось диалоговое окно Добавление таблицы, в котором нажали кнопку Закрыть. Затем выбрали команду меню Запрос/Запрос SQL/ Объединение. В появившемся диалоговом окне ввели SQL код на объединение двух таблиц.

В диалоговом окне введён следующий SQL-запрос:

SELECT [Марка] from [Машина] UNION SELECT [Марка] from [Машина1].

В результате был создан запрос Объединение, содержащий все необходимые данные (Приложение Н).

Работа с отчетами

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

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

Окно конструктора отчета состоит из следующих функциональных элементов:

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

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

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

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

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

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

В Докладной записке содержатся сведения о проведенном ремонте машин. При создании отчёта использовали таблицу Ремонт. Сведения, представленные в докладной записки, могут понадобиться руководящему составу.

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

Результаты работы с отчётами приведены в Приложении П.

Безопасность данных

На главной кнопочной форме, которая предназначена для конечного пользователя, расположены четыре кнопки: Отчёты, Формы, Запросы, Выход.

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

Пароль в созданной БД «Автомастерская» - 1.

Руководство пользователю

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

Чтобы приступить к работе с системой, необходимо запустить приложение Access, далее открыть файл Автомастерская.

В появившемся диалоговом окне нужно ввести пароль – 1, после чего появляется главная кнопочная форма.

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

Форма содержит Отчёты, Запросы, Формы и Выход.

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

Формы. При нажатии на кнопку Формы открывается новая страница Главной кнопочной формы, где можно выбрать одну из форм путем нажатия на кнопку, таким образом, можно открыть форму Машина, Ремонт, Запчасть, Техобслуживание, Используются, Поступает. Так же на странице есть кнопка Назад, которая позволяет вернуться в Главную кнопочную форму.

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

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

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

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

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

Форма Используются

Артикул и Код услуги – внешние ключи, их заполнение обязательно, но возможны повторяющиеся значения. Значения выбираются из раскрывающегося списка.

В поле Дата с клавиатуры вводится текущая дата проведения ремонта машины.

При наведении курсора на поле появляется всплывающая подсказка: «Введите текущую дату проведения ремонта машины». Также для облегчения работы конечного пользователя задали Значение по умолчанию: =Date(), т.е. равно текущей дате.

В поле Количество с помощью клавиатуры указывается количество использованных запчастей. При наведении курсора на поле появляется всплывающая подсказка: «Введите количество использованных запчастей». Данное поле содержит условие – количество использованных запчастей не может быть меньше 1. В случае, если конечный пользователь введёт число, не соответствующее заданному условию, появится сообщение об ошибки: Недопустимое значение! Количество запчастей не может быть меньше одного!

Также для облегчения работы конечного пользователя для поля Количество задали Значение по умолчанию:=2.

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

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

Форма Поступает

Код услуги и Рег номер – внешние ключи, их заполнение обязательно, но возможны повторяющиеся значения. Значения выбираются из раскрывающегося списка.

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

Также для облегчения работы конечного пользователя задали Значение по умолчанию: =Date(), т.е. равно текущей дате.

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

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

Форма Запчать

Поле Артикул – обязательное для заполнения и значение поля не может повторяться, т.к. данное поле - первичный ключ. С клавиатуры необходимо ввести текстовые данные. При наведении курсора на поле появляется всплывающая подсказка: «Введите в артикуле запчасти не более 15 символов».

На данные в этом поле стоят ограничения, то есть количество символов в артикуле не должно быть больше 15 символов. В случае, если конечный пользователь введёт данные, не соответствующее заданному условию, появится сообщение об ошибки: Неверное значение! Количество символов в артикуле должно быть не больше пятнадцати!

В поле Цена нужно ввести с клавиатуры цену запчасти. На данные в этом поле стоят ограничения, то есть цена запчасти должна быть больше нуля. В случае, если конечный пользователь введёт данные, не соответствующее заданному условию, появится сообщение об ошибки: Недопустимое значение! Цена запчасти должна быть больше нуля!

Форма Машина

Поле Рег номер — обязательное для заполнения и значение поля не может повторяться, т.к. данное поле – первичный ключ. С клавиатуры необходимо ввести текстовые данные.

В поле Владелец указываются инициалы и фамилию владельца машины. При наведении курсора на поле появляется всплывающая подсказка: «Введите инициалы и фамилию владельца машины».

В поле Марка из списка выбирается марка машины. При наведении курсора на поле появляется всплывающая подсказка: «Выберите из списка марку машины».

В поле Цвет с клавиатуры указывается цвет машины. При наведении курсора на поле появляется всплывающая подсказка: «Введите цвет машины». Также для облегчения работы конечного пользователя задали Значение по умолчанию: Мокрый асфальт.

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

Кнопка Сведения о техобслуживании позволяет перейти к форме Техобслуживание и просмотреть все сведения о проходящих техобслуживание машинах.

Кнопка Номер техобслуживание позволяет перейти к форме Техобслуживание и просмотреть только одну запись.

Форма Ремонт

Поле Код услуги – обязательное для заполнения и значение поля не может повторяться, т.к. данное поле - первичный ключ. С клавиатуры необходимо ввести числовые данные. При наведении курсора на поле появляется всплывающая подсказка: «Введите код услуги ремонта машины».

В поле Вид работы с клавиатуры указывается вид работы при проведении ремонта. При наведении курсора на поле появляется всплывающая подсказка: «Введите вид работы ремонта машины».

В поле Стоимость с клавиатуры указывается стоимость ремонта машины. При наведении курсора на поле появляется всплывающая подсказка: «Введите стоимость ремонта машины». На данные в этом поле стоят ограничения, то есть стоимость ремонта должна быть больше нуля. В случае, если конечный пользователь введёт данные, не соответствующее заданному условию, появится сообщение об ошибки: Недопустимое значение! Стоимость ремонта должна быть больше нуля!

Форма Техобслуживание

Поле № техобс – обязательное для заполнения и значение поля не может повторяться, т.к. данное поле - первичный ключ. С клавиатуры необходимо ввести числовые данные. При наведении курсора на поле появляется всплывающая подсказка: «Введите номер техобслуживания».

В поле Рег номер указывается регистрационный номер машины. Значение выбирается из раскрывающегося списка (текстовое значение). При наведении курсора на поле появляется всплывающая подсказка: «Выберите из списка регистрационный номер машины».

В поле Дата цифровым способом с клавиатуры указывается дата проведения ремонта машины. При наведении курсора на поле появляется всплывающая подсказка: «Введите дату проведения техобслуживания».

На данные в этом поле стоят ограничения, то есть данные о прохождении техобслуживания, в том числе и дата, должны вноситься, не позднее трехдневного срока, в противном случае, выводится сообщение об ошибке: Недопустимое значение даты! Дату прохождения техобслуживания нужно ввести не позже двухдневного срока с момента прохождения.

В поле Вид работы с клавиатуры указывается вид работы техобслуживания. При наведении курсора на поле появляется всплывающая подсказка: «Введите вид работы техобслуживания».

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

Перемещаться между полями форм можно с помощью мыши или клавиши Tab. В форме Машина, Техобслуживание и Используются изменили последовательность перехода для удобства работы конечного пользователя.

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

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

С этой страницы можно вернуться на страницу Запрос с помощью кнопки Назад.

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

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

В запросе Тэта – соединение в диалоговом окне необходимо ввести марку машины.

С этой страницы можно вернуться на страницу Запрос с помощью кнопки Назад.

Выход. При нажатии на эту кнопку пользователь автоматически выходит из БД «Автомастерская».

Заключение

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

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

Список использованной литературы

1. И.В. Усманова, Н.Н. Надеева, М.А. Катышева Использование компонентов MS Access для создания баз данных систем документооборота. Методические указания к лабораторным работам, часть 1-2, 2004.

2. К.Дж. Дейт. «Введение в системы баз данных, 8-е издание»: Пер. с англ. – М.: Издательский дом «Вильямс», 2005 – 1328 с.

 

Приложение А

(обязательное)