Разработка и типы запросов к базе данных в СУБД MS Access

МИНИСТЕРСТВО  ОБРАЗОВАНИЯ И НАУКИ

РОССИЙСКОЙ  ФЕДЕРАЦИИ

ГОУ ВПО

ВСЕРОССИЙСКИЙ ЗАОЧНЫЙ

ФИНАНСОВО-ЭКОНОМИЧЕСКИЙ  ИНСТИТУТ

Кафедра «Прикладная информатика»

Факультет. Финансово - кредитный

Специальность Бакалавр экономики

КУРСОВАЯ РАБОТА  
на тему «Разработка и типы запросов  
к базе данных в СУБД MS Access»

по дисциплине Информатика

Студент: Климова Евгения Андреевна

Курс 1 № группы   заочная

Личное дело № 11флб00376

Преподаватель: Никифорова Светлана Владимировна

Москва 2012

Содержание

Разработка  и типы запросов к базе данных в СУБД MS Access

1. Теоретическая часть.

1.1. Понятия о базе данных СУБД MS Access

База данных – это организованная структура, предназначенная для хранения информации. Систему управления базой данных (СУБД) можно определить как комплекс программных средств, предназначенных  для создания структуры базы данных, заполнения её содержимым, редактирования содержимого. Современные СУБД содержат:

Особенностью  СУБД Microsoft Access  является то, что вся информация, относящаяся к одной базе данных, хранится в едином файле с расширением *.mdb.

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

• Разработка и описание структур таблиц данных
• Разработка схемы данных и задание взаимосвязей между таблицами
• Разработка системы запросов к таблицам базы данных
• Разработка экранных форм для ввода и вывода данных
• Разработка системы отчётов по данным
• Решение специфических задач по обработке информации с помощью создания макросов и модулей
• Разработка системы защиты данных, прав и ограничений по доступу

1.2. Понятие запроса

Запрос (query) – это средство выбора необходимой информации из базы данных. Вопрос, сформированный по отношению к базе данных, и есть запрос

Запросы являются мощным средством обработки данных, хранимых в таблицах Access. С помощью запросов можно просматривать, анализировать и изменять данные из нескольких таблиц. Они также используются в качестве источника данных для форм и отчетов. Запросы позволяют вычислять итоговые значения и выводить их в компактном формате, подобном формату электронной таблицы, а также выполнять, вычисления над группами записей. Формирование запросов в СУБД может осуществляться в специальном редакторе; с помощью конструктора или с помощью пошагового мастера формирования запросов. Классификация запросов в СУБД приведена в приложении №1.

1.3. Типы  запросов

В Access можно  создавать следующие типы запросов:

• запрос на выборку - при его выполнении данные, удовлетворяющие условиям отбора, выбираются из одной или из нескольких таблиц и выводятся в определенном порядке.;
• запрос с параметрами это запрос, при выполнении которого в его диалоговом окне пользователю выдается приглашение ввести данные, на основе которых будет выполняться запрос. Например, часто требуются данные о том, какие дисциплины ведут преподаватели. Чтобы не создавать отдельные запросы по каждому преподавателю, можно создать один запрос с параметрами, где в качестве параметра будет использоваться фамилия преподавателя. При каждом вызове этого запроса вам будет предложено ввести фамилию преподавателя, а затем на экран будут выведены все поля, которые вы указали в запросе, например фамилия, имя, отчество преподавателя и читаемая им дисциплина.
• перекрестный запрос – это отображение результатов статистических расчетов (такие, как суммы, количество записей, средние значения), выполненных по данным из одного поля таблицы. Эти результаты группируются по двум наборам данных, один из которых расположен в левом столбце таблицы, а второй - в верхней строке ;
• запрос на изменение - это запрос, который за одну операцию вносит изменения в несколько записей. Существует четыре типа запросов на изменение; на удаление, обновление и добавление записей, а также на создание таблицы.;
• запросы SQL - это запрос, создаваемый при помощи инструкций SQL. Этот тип запросов довольно сложен для начинающих пользователей и используется обычно опытными пользователями, имеющими навыки программирования и общения с серверами баз данных (запросы на объединение, запросы к серверу, управляю

1.4. Способы создания запросов

Запросы на выборку в приложении Access создаются двумя основными способами: с помощью конструктора запросов и мастера запросов. (рис. 1)

Рис. 1. Контекстная вкладка Создание – Запросы.

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

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

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

3.Добавьте в  запросы условия сортировки, фильтрации  и другие условия отбора.

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

Возникает  вопрос: как создавать запросы с параметрами  и запросы на изменение, если при  создании запроса они явно не указаны? Следует отметить, что основой для всех этих запросов является запрос на выборку, т.е. сначала необходимо определить набор данных, с которым хотите работать. Затем для созданного запроса на выборку надо перейти в режим конструктора. Задание параметров производится в строке Условия отбора для соответствующих полей. Для доступа к запросам на изменение надо открыть пункт меню Запрос - в открывшемся списке вы увидите все виды запросов на изменение.

Для сохранения запроса следует выполнить следующие  действия. Выполните команду Файл/Сохранить или щелкните по кнопке Сохранить на панели инструментов. Если вы впервые сохраняете запрос, введите новое имя запроса в диалоговом окне "Сохранение".

Из всех запросов, наиболее распространенным, является запрос на выборку. Запросы на выборку используются для отбора нужной пользователю информации, содержащейся в таблицах. Они создаются только для связанных таблиц. Рассмотрим создание запроса на выборку с помощью Мастера и с помощью Конструктора.

1.4. Создание  запроса на выборку с помощью  Мастера

При создании запроса необходимо определить:

• Поля в базе данных, по которым будет идти поиск информации
• Предмет поиска в базе данных
• Перечень полей в результате выполнения запроса

Для создания запроса  надо в окне базы данных выбрать  вкладку Запросы и щелкнуть по кнопке Создать. Откроется окно "Новый запрос"  В окне появится один из пунктов: Простой запрос, Перекрестный запрос, Повторяющиеся записи, Записи без подчиненных.(рис.3)

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

При выборе одной из записей  появится окно Создание простых запросов. (рис.4)

Рис.3. Окно Новый запрос.                 Рис.4. Окно создание простых 
                                                                                запросов.

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

Затем в окне Мастера надо выбрать подробный  или итоговый отчет и щелкнуть на кнопке Далее. После этого необходимо задать имя запроса и выбрать один из вариантов дальнейшего действия: Открыть запрос для просмотра данных или Изменить макет запроса и нажать кнопку Готово. В результате чего получите готовый запрос.

1.5. Создание запроса на выборку с помощью Конструктора

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

С помощью конструктора можно создать следующие виды запросов:

• Простой
• По условию
• Параметрические
• Итоговые
• С вычисляемыми полями

Чтобы вызвать  Конструктор запросов, необходимо На вкладке Создание в группе Запросы нажмите кнопку Конструктор запросов. Откроется диалоговое окно Добавление таблицы. (рис.5)

.

Рис.5. Окно Конструктор запросов и диалоговое окно Добавление таблицы.

В окне Добавление таблицы следует выбрать таблицу – источник или несколько таблиц из представленного списка таблиц, на основе которых будет проводиться выбор данных, и щелкнуть на кнопке Добавить. После этого закрыть окно Добавление таблицы, окно «Запрос: запрос на выборку» станет активным.

Окно Конструктора состоит из двух частей – верхней  и нижней. В верхней части окна размещается схема данных запроса, которая содержит список таблиц – источников и отражает связь между ними. (Рис.6)

Рис.6. Бланк запроса на выборку.

В нижней части  окна находится Бланк построения запроса, в котором каждая строка выполняет определенную функцию:

• Поле – указывает имена полей, которые участвуют в запросе
• Имя таблицы – имя таблицы, с которой выбрано это поле
• Сортировка – указывает тип сортировки
• Вывод на экран – устанавливает флажок просмотра поля на экране
• Условия отбора  - задаются критерии поиска
• Или – задаются дополнительные критерии отбора

 В окне  «Запрос: запрос на выборку» с помощью инструментов формируем запрос:

• Выбрать таблицу – источник, из которой производится выборка записей.
• Переместить имена полей с источника в Бланк запроса.
• Задать принцип сортировки. Курсор мыши переместить в строку Сортировка для любого поля, появится кнопка открытия списка режимов сортировки: по возрастанию и по убыванию. Например, установить в поле Фамилия режим сортировки – по возрастанию.
• В строке вывод на экран автоматически устанавливается флажок просмотра найденной информации в поле.
• В строке "Условия" отбора и строке "Или" необходимо ввести условия ограниченного поиска – критерии поиска. Например, в поле образование  - "высшее", т.е. отображать все фамилии студентов, которые имеют высшее образование.
• После завершения формирования запроса закрыть окно Запрос на выборку. Откроется окно диалога Сохранить – ответить Да (ввести имя созданного запроса, например, Образец запроса в режиме Конструктор) и щелкнуть ОК и вернуться в окно базы данных.

Заполненный бланк  запроса на выборку представлен на рис.7.

Рис.7. Заполненный бланк запроса на выборку.

Чтобы открыть запрос из окна базы данных, необходимо выделить имя запроса и щелкнуть кнопку Открыть, на экране появится окно запрос на выборку с требуемым именем (рис8).

Рис.8. Открытый запрос на выборку.

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

Параметрические запросы

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

Последовательность  создания параметрического запроса:

• Создать запрос в режиме конструктора или открыть существующий запрос в режиме конструктора, например «Образец запроса в режиме Конструктор».
• В Бланк запроса в строке Условия отбора ввести условие отбора в виде приглашения в квадратных скобках, например [Введите фамилию]
• Закрыть окно Запрос на выборку, на вопрос о сохранении изменения ответить – Да. Вернуться в окно базы данных, где созданный запрос будет выделен.
• Выполнить запрос, щелкнув по кнопке: Открыть. В появившемся на экране окне диалога «Введите значение параметра» надо ввести, например фамилию студента, информацию об успеваемости которого необходимо получить, выполнить щелчок по кнопке ОК.

2. Практическая  часть

2.1. Постановка  задачи 

2.1.1. Цель решения задачи 

Цель решения  данной задачи состоит в расчете ежемесячного платежа по кредиту клиентом   банка Ивановым А.А. Ежемесячное погашение кредита осуществляется равными (аннуитетными) платежами. Этот вид расчета дает возможность банку получения более высоких доходов по процентам, а клиенту обеспечивает удобства при расчетах. Очень удобно и не хлопотно ежемесячно платить одну и ту же сумму в погашение кредита и процентов, которую легко запомнить и, кроме того, не нужно ежемесячно встречаться с консультантом банка для выяснения очередной суммы платежа

Задача, которая будет решаться в программной среде MS Excel ежемесячно, называется «Платежи по кредиту клиента Иванова А.А. банка «Акцепт+» за 2010 г.». .

2.1.2. Условие задачи 

Входной оперативной информацией служит документ «Платежи по кредиту клиента Иванова А.А. банка «Акцепт+» за 2010 г»., содержащая следующие реквизиты (условная форма): годовая процентная ставка, срок выдачи кредита, сумма кредита, номер платежа, дата платежа.

В результате следует  получить документ со следующими реквизитами по месяцам: номер платежа, дата платежа, текущий остаток по кредиту, сумму процентов, погашение основного долга и платеж по основному долгу

Кроме того, информацию о  платежах по кредиту , необходимо представить в виде гистограммы.

В технологии необходимо использовать таблицу для организации ввода и контроля исходных данных, а также для организации процессов расчета функции ОКРУГЛ(), ЕСЛИ() и др.

2.2. Компьютерная  модель решения задачи 

2.2.1. Информационная модель  решения задачи 

Информационная  модель, отражающая взаимосвязь исходных и результирующих документов, приведена на рис. 9

Рис.9. Информационная графовая модель взаимосвязи исходных и результативных данных

p-годовая процентная ставка; n-количество месяцев, на которые выдан кредит; S-первоначальная сумма выданного кредита; J-процентная ставка в месяц; D-погашение основного долга; S _n-остаток по кредиту на период; К_n-платеж по кредиту на период; P _n-сумма процентов на период.

2.2.2. Аналитическая модель  решения задачи 

Для получения документа «Платежи по кредиту клиента Иванова А.А. банка «Акцепт+» за 2010 г» необходимо рассчитать следующие показатели:

• процентная ставка в месяц;
• сумму погашения основного долга по месяцам;
• общую сумму погашения основного долга за год;
• сумма текущего остатка по кредиту по месяцам.
• сумма процентов по месяцам;
• общую сумму процентов за год;
• платеж по кредиту по месяцам;
• платеж по кредиту за год;

Расчеты выполняются  по следующим формулам:

где

2.2.3. Технология решения  задачи MS Excel

Решение задачи средствами MS Excel

1. Вызовите Excel:

• выполните команды Пуск → Программы MS→ Excel

2. Переименуйте «Лист 1» в ««Платежи по кредиту»:

• установите курсор мыши на ярлык  «Лист 1» (нижняя часть экрана) и нажмите правую кнопку мыши;

• выберите в контекстном меню команду «Переименовать» и нажмите левую кнопку мыши;

• наберите на клавиатуре ««Платежи по кредиту»;

• нажмите клавишу «Enter».

3. Введите заголовок таблицы «Платежи по кредиту клиента Иванова А.А. банка «Акцепт+» за 2010 г.»:

• сделайте ячейку A1 активной (установите курсор мыши на пересечение столбца A и строки 1 и нажмите левую кнопку мыши);

• наберите на клавиатуре «Платежи по кредиту»;

• нажмите кнопку в строке формул     (установите курсор мыши на эту кнопку и нажмите левую кнопку мыши).

4. Отформатируйте заголовок:

• выделите ячейки A1÷F1 (сделайте активной ячейку A1, затем нажмите левую кнопку мыши и, не отпуская ее, перемесите курсор на ячейку F1) (рис.10);

• на панели инструментов в закладке «Главная» выберите раздел «Выравнивание» и нажмите кнопку .

Рис. 10. Пример выделения группы ячеек

5. Введите строки  «Годовая процентная ставка», «Кредит выдан на», «Сумма кредита, руб.», соответственно в ячейки А2÷А4

• выделите действия аналогичные  п3 и п.4;

6. Введите в ячейки С2÷С4 информацию, соответствующую строкам «Годовая процентная ставка», «Кредит выдан на», «Сумма кредита, руб.» 

• выделите ячейки С2÷F2;

• выполните команду «Выравнивание» в разделе «Формат ячеек» меню «Главная» на панели инструментов;

• выберите закладку «Выравнивание»;

• в группе опций «Отображение» установите флажок опции «объединение ячеек» (рис.11);

• нажмите кнопку «OK».

• выделите ячейку С2 и потяните за правый нижний край до ячейки С4

• введите в ячейки С2÷С4 информацию, представленную на рис. 12.

Рис. 11. Задание объединение ячеек.

Рис. 12. Имена строк таблицы

7.Отформатируйте ячейки A6÷F6 под ввод длинных заголовков:

• выделите ячейки A6÷F6;

• выполните команду «Выравнивание» в разделе «Формат ячеек» меню «Главная» на панели инструментов;

• выберите закладку «Выравнивание»;

• в группе опций «Отображение» установите флажок опции «переносить по словам»;

6. Введите в ячейки A6÷F6 информацию, представленную на рис. 13.

Рис. 13. Имена полей таблицы «Справочник поставщика

8. Отформатируйте ячейки A3÷B20 для ввода текстовых символов:

• выделите ячейки A3÷B20;

• на панели инструментов в меню «Главная» выберите «Ячейки», где в пункте «Формат» выполните команду «Формат ячеек»;

• выберите закладку «Число»;

• выберите формат «Текстовый» (рис. 14);

9. Отформатируйте ячейки С3÷F20 для ввода текстовых символов:

• выделите ячейки A3÷B20;

• на панели инструментов в меню «Главная» выберите «Ячейки», где в пункте «Формат» выполните команду «Формат ячеек»;

• выберите закладку «Число»;

• выберите формат «Числовой»

Рис.14 Выбор формата ячеек

10. Введите информацию, приведенную на рис.15

Рис.15. Заполнение строк «номер платежа» и «Дата платежа».

11.Заполните столбцы «Текущий остаток по кредиту», « Сумма процентов», «Погашение основного долга», «Платежи по кредиту» для января 2010  года. (для этого рассчитаем,  процентную ставку. В ячейку А5 введем «процентная ставка», в ячейку С5 введите формулу =C2/12)

• сделайте активной ячейку Е7, введите формулу =ОКРУГЛ(C$4/12;0);

• введите в ячейку С7 число 250000;
• сделайте активной ячейку D7, введите формулу =ОКРУГЛ (C7*C5;0)
• сделайте активной ячейку F7 введите формулу =ОКРУГЛ (D7+E7$0).

12.Заполним столбцы «Текущий остаток по кредиту», «Сумма процентов», «Погашение основного долга», «Платежи по кредиту» для февраля 2010  года.

• сделайте активной ячейку С8 , введите формулу  
  =ОКРУГЛ (C7-E7;0);
• сделайте активной ячейку D8 , введите формулу  
  =ОКРУГЛ (C8*C$5;0);
• сделайте активной ячейку F8 , введите формулу  
  =ОКРУГЛ (D8+E8;0);

13. Заполните столбец «Погашение основного долга» (так как, ежемесячное погашение кредита осуществляется равными (аннуитетными) платежами,погашение основного долга в каждом месяце будет равной)

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

14.Заполните показатели по столбцам за остальные месяцы.

• выделите ячейки С8 ÷F8 и курсором за правый нижний край ячейки протяните значение до С18 ÷F18. Данные заполнятся автоматически (рис.16)

Рис. 16 Автоматическое заполнение столбцов.

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

• сделайте активной ячейку D19
• выполните команды Формулы →Библиотека функций → ∑ Автосумма.
• выделите ячейку D19 и протяните курсором за правый нижний край ячейки значение до F19. Данные заполнятся автоматически.

16. Для того чтобы итоговая сумма погашения основного долга равнялась сумме выданного кредита, используйте функцию ЕСЛИ() для отражения остатков по платежу в последнем платеже. Сумма последнего платежа по погашению основного долга будет больше, чем платежи за предыдущие месяцы. Для этого выполните следующие действия:

• сделайте активной ячейку Е19
• выполните команды Формулы →Библиотека функций→Логические →ЕСЛИ()
• введите информацию – E19<=250000 в поле «Лог_выражение»;
• введите информацию – ОКРУГЛ(20833.33;0) в поле «Значение_если_ложь»
• введите информацию – ОКРУГЛ(20833.33;0)+250000-E19
• в поле «Значение_если_истина» (рис. 17);
• нажмите кнопку «OK»;

Рис. 17. Вид второго окна мастера функций

19. Представьте  наглядно результаты расчетов, создав  гистограмму по данным таблицы:

• выделите диапазон A6÷F19;

• выберите команду  «Гистограмма» в разделе «Диаграммы» меню «Вставка»;

• в разделе «Коническая» выберите «Коническая с группировкой» (рис. 18).

Рис. 18. Созданная гистограмма

20. Отобразите в данной гистограмме только платежи по кредиту

• сделайте активной гистограмму (подведите курсор к гистограмме и щелкнете правой кнопкой мыши );
• выберите в открывающем окне «Выберите данные»;
• удалите из «элементов легенды (ряды)» все показатели, кроме «Платеж по кредиту» и нажмем «ОК» (рис .19)