Разработка базы данных Access

Содержание

 

 

 

 

 

                                   Введение

Данная курсовая работа нацелена на разработку реляционных моделей  БД экономического направления, с помощью пакета ERWin и Microsoft Access.

Цель данной работы: Рассмотреть  процесс моделирования  и реализации учебной базы данных при помощи достаточно простых и  удобных инструментов: All Fusion ERwin Data modeler, Microsoft Office Access и Microsoft Оffice Excel.

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

Microsoft Access является системой управления реляционными базами данных. Реляционная база данных (relational database) – это такая база данных (БД), записи которой можно сравнивать друг с другом и другими источниками данных и анализировать в целях создания  динамического источника данных. Можно легко создавать формы, которые помогут вводить данные в базу, не задумываясь о работе программы управления этой БД, просматривать  введенные  данные  в  удобном  виде,  создавать  запросы  для  отбора  информации, удовлетворяющей определенным условиям и формировать отчеты.

      

 

1 Разработка структуры  базы данных

1.1 Разработка логической и физической  моделей

Разработка физической и логической модели данных будет осуществлена при  помощи программы Erwin Data Modeler, которая реализует методологию IDEF1X.

Методология IDEF1X – один из подходов к семантическому моделированию данных, основанный на концепции Сущность – Отношение.

«Сущность» представляет множество  реальных или абстрактных предметов (людей, объектов, мест, событий, состояний, идей, пар предметов и т.д.), обладающих общими атрибутами или характеристиками. Отдельный элемент этого множества называется «экземпляром сущности».

Отношение связи, называемое также  «отношение родитель - потомок», - это  связь между сущностями, при которой  каждый экземпляр одной сущности, ассоциирован с произвольным (в том  числе нулевым) количеством экземпляров другой сущности, называемой сущностью – потомком, а каждый экземпляр сущности – потомка ассоциирован в точности с одним экземпляром сущности – родителя.

Если экземпляр сущности – потомка  однозначно определяется своей связью с сущностью – родителем, то отношение называется «идентифицирующим отношением». В противном случае отношение называется не идентифицирующим.

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

 

 

Разработанная логическая модель представлена на рисунке 1.

Рисунок 1- Логическая модель базы данных

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

При этом физическая модель разрабатывается  с учетом конкретной реализации СУБД. В данной работе была использована СУБД Microsoft Access.

Полученная физическая модель представлена на рисунке 2.

Рисунок 2- Физическая модель базы данных

1.2 Получение структуры базы  данных в Access

При помощи инструмента Forward Engineer (прямое проектирование),

из физической модели данных Erwin была получена реализация базы данных в MS Office Access:

Рисунок 3- Схема базы данных «Автосалон» в СУБД MS Access

База данных содержит 7 таблиц:

• Таблица «clients» содержит список клиентов автосалона;
• Таблица «credits» содержит список выдаваемых кредитов;
• Таблица «tpk» (товары по кредитам) содержит список товаров, приобретаемых в рамках какого-либо конкретного кредита (связь кредитов с товарами);
• Таблица «packets» содержит список кредитных предложений от различных банков;
• Таблица «banks» содержит список банков, сотрудничающих с автосалоном
• Таблица «tovari» содержит перечень товаров;
• Таблица «pays» - оплата кредитов по датам

На этапе моделирования данная структура была проверена на соответствие нормальным формам (таблица tpk является следствием данной проверки).

 

 

 

2 Разработка базы данных Access

           2.1 Разработка форм данных

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

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

Частным случаем форм являются также так называемые «кнопочные формы», которые содержат только кнопки (элементы управления). С их помощью открываются другие формы или отчеты базы данных или выполняются другие действия.

Создавать формы можно различными способами — с помощью мастеров, по шаблонам или в режиме конструктора.

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

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

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

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

В базе данных реализовано 7 форм для работы с созданными таблицами и 1 главная форма, отображающаяся при обращении к БД.

         Форма «banks» позволяет редактировать справочник банков. Экранная копия формы представлена на рисунке 4.

Рисунок 4 - Форма «banks»

Форма «clients» содержит список клиентов. Экранная копия формы представлена на рисунке 5.

Рисунок 5- Форма «clients»

Форма «Credits» содержит информацию о выданных кредитах. Экранная копия формы представлена на рисунке 6.

Рисунок 6 - Форма «credits»

Форма «packets» содержит список кредитных предложений от банков. Экранная копия формы представлена на рисунке 7.

Рисунок 7- Форма «packets»

Форма «tovari» содержит список товаров фирмы. Экранная копия формы приведена на рисунке 8.

Рисунок 8 - Форма «tovari»

2.2 Разработка запросов

Запросы используются для представления, просмотра и изменения данных самыми различными способами. Можно  считать, что запрос — это просто вопрос, который пользователь задает базе данных. Сколько вопросов, столько и ответов. Запрос должен «уметь» формализовать любой правомерный вопрос к данным, хранящимся в базе, наравне с таблицами (чаще даже вместо них), запросы используются в качестве основы для построения форм, отчетов, списков и т.д.

Есть два основных режима представления запросов: режим таблицы, в котором полученные в результате выполнения запроса данные отображаются в виде таблицы, и режим конструктора (внешне напоминающий уже знакомый вам расширенный фильтр, позволяющий создавать и редактировать структуру запроса. Еще один режим (режим SOL) позволяет записывать или редактировать запрос (если он уже существует) на языке программирования реляционных баз данных. Можно сказать, что режим конструктора позволяет «составлять» запрос SQL с помощью интерактивных инструментов и параметров, о режим SQL — непосредственно. Кроме этого имеются режимы сводной таблицы и сводной диаграммы, которые используются обычно для анализа отобранных в результате запроса данных.

Любой запрос, существующий в базе данных Access, относится к одному из следующих типов:

• запросы на выборку; запросы с параметрами; перекрестные запросы;
• запросы на изменение (обновление, объединение, создание, добавление, удаление);
• запросы управления.

Запросы на выборку составляют большую часть запросов, имеющихся в любой базе данных. Этот тип запроса можно назвать «расширенным фильтром по всем данным базы. Запросы на выборку создают новые, удобные для пользователя «таблицы», данные в которых обновляются при любом вызове запроса. Дополнительно эти запросы позволяют группировать записи, вычислять суммы, средние значения, подсчитывать количество записей, а также находить другие типы итоговых значений. Итак, на рисунке 9 создаем запрос на выборку товаров, цена которых больше 300000 руб. и наименование которых начинается на букву А.

                     Рисунок 9- Создание запроса- выборки

 

            

 

                    

                      Рисунок 10 -Результат запроса  выборки

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

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

Запросы на изменение могут быть четырех  видов: на удаление записи, на обновление записи, на добавление записи и на создание таблицы:

• Запрос на удаление записи. С его помощью можно удалять только всю запись, а не отдельные ее поля. Запрос на удаление удаляет группу записей из одной или нескольких таблиц в соответствии с установленными условиями отбора.
• Запрос на обновление записи. Запрос на обновление вносит изменения в группу записей одной или нескольких таблиц. С- его помощью можно, например, к названию документа добавить слово «(книга)», если количество страниц в документе превышает 200. Еще один часто используемый пример — это повышение цен на товары (или, наоборот уменьшение) на определенное количество процентов.
• Запрос на добавление записи. Запрос на добавление добавляет группу записей из одной или нескольких таблиц в конец одной или нескольких таблиц. Например, если нужно создать общую таблицу из фамилий авторов и редакторов, сначала создайте таблицу Список фамилий, а затем с помощью запроса на добавление записей заполните ее поля из таблиц Авторы и Редакторы.
• Запрос на создание таблицы. При выполнении запроса на создание таблицы новая таблица строится на основе всех или части данных из одной или нескольких таблиц базы. Этот запрос удобно применять для создания таблицы для экспорта данных или при создании архивной таблицы, содержащей «старые» записи.

 

 

                  Рисунок 11 - создание таблицы с помощью запроса

 

 

        Рисунок 12 - результат запроса-действия

Любой запрос можно создать с  нуля в режиме Конструктора. При этом необходимо будет выбрать таблицы, на которых будет основан создаваемый запрос, затем указать желаемые поля и их свойства для запроса (условия, группировки и т.п.).

 

 

 

 

 

 

                             

                             Рисунок 13 - итоговый запрос

 

   

                               Рисунок 14 - Результат итогового  запроса

 

              

 

Для наглядности результаты представлены в виде пузырьковой диаграммы, осью X которой является дата, Осью значений Y – величина  задолженности, а величиной пузырька – величина вносимой за кредит суммы (рисунок 15).

Рисунок 15 - Диаграмма «Оплаты по кредитам»

2.3 Разработка отчетов

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

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

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

Создавать отчеты в базе данных Access можно четырьмя различными способами:

• сохранением другого объекта базы данных как отчета;
• с помощью автоотчета на основе таблицы или запроса;
• с помощью мастера отчетов;
• в режиме конструктора (и/или макета) отчета.

С помощью мастера отчетов  выведем список всех кредитов. В отчете использована группировка по датам. При помощи итогов рассчитана сумма всех кредитов.

                          Рисунок 18 - Экранная копия отчета

          Как мы убедились,мастер создания отчетов — это более образованный и внимательный собеседник, который готов учесть ваши предложения по выбору полей, настроек группирования записей, внешнему виду и стилю оформления отчета. Мастер работает с полями нескольких таблиц или запросов и создает отчеты с более сложной структурой, в которой допускается группировка записей по некоторым логическим категориям. Как и в случае с мастером создания форм, мастер отчетов позволяет быстро создать структуру отчета, которую потом можно доработать в режиме конструктора.