Размещено на http://www.allbest.ru/

Введение

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

Система управления базами данных (СУБД) - программное обеспечение, с помощью которого пользователи могут определять, создавать и поддерживать базу данных, а также осуществлять к ней контролируемый доступ.

СУБД позволяет создать базу данных, вставлять, обновлять, удалять и извлекать информацию из базы данных. Наличие централизованного хранилища всех данных и их описаний позволяет использовать язык DML как общий инструмент организации запросов, который иногда называют языком запросов (query language). Наличие языка запросов позволяет устранить присущие файловым системам ограничения, при которых пользователям приходится иметь дело только с фиксированным набором запросов или постоянно возрастающим количеством программ, что порождает другие, более сложные проблемы управления программным обеспечением. Наиболее распространенным типом непроцедурного языка является язык структурированных запросов (Structured Query Language -- SQL), который в настоящее время определяется специальным стандартом и фактически является обязательным языком для любых реляционных СУБД.

СУБД Microsoft Access -- наиболее широко используемая в среде Microsoft.

Windows реляционная СУБД, Microsoft Access -- типичная СУБД для персональных компьютеров, обеспечивающая хранение, сортировку и поиск данных для множества приложений. В СУБД Access для создания таблиц, запросов, форм и отчетов предусмотрен графический интерфейс пользователя (Graphical User Interface -- GUI); для разработки настраиваемых приложений с базой данных есть инструментальные средства, использующие макроязык Microsoft Access или язык VBA (Microsoft Visual Basic for Applications). Кроме того, в СУБД Access предусмотрены программы, называемые мастерами (Wizards), которые упрощают многие из процессов формирования приложений с базой данных, проводя пользователя через ряд диалоговых окон в запросно-ответном режиме. В СУБД Access предусмотрены также конструкторы (Builders), которые могут помочь пользователю сформировать синтаксически правильные выражения, например операторы и макрокоманды языка SQL.

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

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

Контракт подписывается каждым клиентом по каждой услуге. Он включает: фамилию клиента, название компании клиента, вид услуги, дату подписания, дату начала работ, дату завершения работ, дату оплаты и сумму контракта. Список услуг включает: код услуги, вид услуги. Данные на клиентов включают: имя клиента, фамилию клиента, название компании клиента, город, адрес, номер телефона. В консалтинговом агентстве назначается менеджер проекта по каждому контракту. Данные на менеджеров проекта содержат: Фамилию, имя и отчество работника, номер телефона, дата приема на работу в агенство.

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

- Список клиентов, сгруппированный по городам;

- Отчет по контрактам;

- Список контрактов по отдельной услуге;

- Список контрактов, сгруппированный по виду услуги за прошедший год;

- Список работников, отсортированный в обратном порядке в зависимости от величины суммы контрактов;

- Ежемесячная сумма контрактов на услуги каждого вида;

- Годовой отчет по сумме, полученной за оказанные услуги;

- Запрос на создание архивной таблицы и запрос на исключение данных из основной таблицы контрактов, выполненных 3 года назад и более.



1. Проект БД

1.1 Инфологическая модель (ER-диаграмма)

база данные консалтинговое агентство

В данной работе модель будет строиться на основе метода «Сущность - связь». Этот метод основан на ER-диаграммах. Для ясности дадим некоторые основные определения.

· Сущность - это некоторое множество объектов материального и нематериального мира, информация которых хранится в базе данных.

· Ключ сущности - некоторое множество атрибутов, служащее для определения экземпляров сущности.

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

1.1.1 Описание предметной области

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

Контракт подписывается каждым клиентом по каждой услуге. Он включает: фамилию клиента, название компании клиента, вид услуги, дату подписания, дату начала работ, дату завершения работ, дату оплаты, сумму контракта. Список услуг включает: код услуги, вид услуги. Данные на клиентов включают: имя клиента, фамилию клиента, название компании клиента, город, адрес, номер телефона. В консалтинговом агентстве назначается менеджер проекта по каждому контракту. Данные на менеджеров проекта содержат: фамилию и имя работника, номер телефона.

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

· список клиентов, сгруппированный по городам;

· отчет по контрактам;

· список контрактов по отдельной услуге;

· список контрактов, сгруппированный по виду услуги за прошедший год;

· три самых важных клиента (принесших наибольшую прибыль);

· список работников, отсортированный в обратном порядке в зависимости от величины суммы контрактов;

· ежемесячная сумма контрактов на услуги каждого вида;

· годовой отчет по сумме, полученной за оказанные услуги;

· запрос на создание архивной таблицы и запрос на исключение данных из основной таблицы контрактов, выполненных 3 года назад и более.

1.1.2 Перечислим сущности

· «Данные на клиентов»,

· «Контракты»,

· «Список услуг»,

· «Данные на менеджеров»,

· «Договора»,

· «Инструкции»,

· «Партнеры»,

· «Ресурсы»,

· «Юридические положения».

Определим атрибуты сущности - это поименованная характеристика сущности.

Таблица 1.1.1 Атрибуты сущности Данные на клиентов

Код клиента	Числовой
Имя клиента	Текстовый
Фамилия клиента	Текстовый Текстовый
Отчество
Компания	Текстовый
Город	Текстовый
Адрес	Текстовый
Телефон	Числовой

Таблица 1.1.2 Атрибуты сущности Контракты

Код контракта	Числовой
Фамилия клиента	Текстовый
Имя	Текстовый
Компания клиента	Текстовый
Вид услуги	Текстовый
Дата подписания	Дата
Дата начала работ	Дата
Дата завершения работ	Дата
Дата оплаты	Дата
Сумма контракта	Числовой
Вариант оплаты	Текстовый
Юридическое положение	Текстовый

Таблица 1.1.3 Атрибуты сущности Список услуг

Код услуги	Числовой
Вид услуги	Текстовый
Ответственный	Текстовый
Срок исполнения	Текстовый
Инструкция	Текстовый

Таблица 1.1.4 Атрибуты сущности Партнеры

Код партнера	Счетчик
Технические партнеры	Текстовый
Информационные партнеры	Текстовый
Род оказания услуг	Текстовый Текстовый
Договор

Таблица 1.1.5 Атрибуты сущности Данные на менеджеров

Код менеджера	Числовой
Фамилия	Текстовый
Имя	Текстовый
Отчество	Текстовый
Телефон	Числовой
Дата приема на работу в агентство	Дата

Таблица 1.1.6 Атрибуты сущности Ресурсы

Код ресурса	Числовой
Информационные	Текстовый
Технические	Текстовый

Таблица 1.1.7 Атрибуты сущности Договора

Код договора	Числовой
Наименование договора	Текстовый
Партнер	Текстовый
Дата подписи	Дата
Дата окончания	Дата

Таблица 1.1.8 Атрибуты сущности Компания

Код компании	Числовой
Партнеры	Числовой
Технические ресурсы	Текстовый
Информационные ресурсы	Текстовый
Количество контрактов	Числовой
Менеджер	Текстовый
Юридические основы	Текстовый
контракты	Числовой

Таблица 1.1.9 Атрибуты сущности Инструкции

Код инструкции	Числовой
Номер инструкции	Числовой
Наименование инструкции	Текстовый
Текст инструкции	Текстовый

Таблица 1.1.10 Атрибуты сущности Юридические положения

Код юридического положения	Числовой
Наименование	Текстовый
Текст положения	Текстовый



1.1.3 Покажем связи между сущностями. Дадим определение

· Связь между сущностями - это ассоциация между двумя и более сущностями.

· Класс принадлежности экземпляров сущности: говорят, что сущность обладает обязательным классом принадлежности, если все экземпляры первой сущности (или второй) связаны хотя бы с одним экземпляром второй (или первой) сущности.

Класс принадлежности может быть обязательным или необязательным.

0 (необязательный) - если каждый экземпляр сущности не связан ни с одним экземпляром другой сущности;

1 (обязательный) - если каждый экземпляр сущности связан хотя бы с одним экземпляром другой сущности.

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

1.1.4 Преобразуем сущность в отношение или набор отношений, между которыми установим связи

Отношение представляет собой таблицу. Таблица имеет столбцы (поля) и строки (записи).

Преобразования сущностей в совокупность отношений:

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

2. Для каждой сущности, имеющей связь с другими сущностями как «один-ко-многим» или «один-к-одному», укажем один столбец в качестве первичного ключа.

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

4. Зададим первичный ключ для каждой сущности, выступающей во взаимоотношениях как «многие-к-одному».

Выполним выше перечисленные действия для данного проекта.

1. Создадим четыре таблицы с полями, соответствующими атрибутам сущностей.

2. Создадим еще две таблицы для сущностей с классом принадлежности 0 («Контракты» - «Список услуг» и «Контракты» - «Данные на менеджеров»).

3. Зададим первичные ключи для таблиц «Данные на клиентов» и «Контракты», выступающих в связи «один-к-одному», и для таблиц «Список услуг» и «Данные на менеджеров», выступающих в связи «один-ко-многим» с таблицей «Контракты».

· Первичный ключ - это поле или минимальный набор полей,

однозначно определяющий каждую строку таблицы.

Первичные ключи служат идентификаторами кортежей (строк в таблице), для ускорения работы со строками таблицы, связывания таблиц.

Таблица «Данные на клиентов» имеет в своем составе уникальное для каждой строки поле - Код клиента. В таблице «Контракты» в качестве первичного ключа также выступает поле Код контракта. В таблице «Список услуг» первичным ключом будет поле Код услуги, а в таблице «Данные на менеджеров» - Код менеджера.

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

1.2 Физическая модель

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

Для повышения производительности реляционные СУБД используют специальные объекты, называемые индексами. При описании структуры таблицы задаются индексы с помощью свойства полей, называемого индексированным полем. Индексированное поле может принимать три значения:

1. неиндексированное;

2. допускаются совпадения;

3. совпадения не допускаются.

Индекс содержит набор записей из двух элементов: {значение ключевого поля; указатель на соответствующую запись в таблице}. Индекс упорядочен по значению ключевого поля, что позволяет системе быстро находить нужные значения. В реляционных СУБД таблицы всегда индексируются по полю/полям первичного ключа. В Access индексированные поля не обязательно ключевые. Считается нормой, если таблица имеет хотя бы одно ключевое поле.

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



Таблица 1.2 Индексированные поля

Таблица	Индексированное поле
Данные на клиентов	Код клиента
Контракты	Код контракта
Услуги	Код услуги
Данные на менеджеров	Код менеджера
Договора	Код договора
Партнеры	Код партнера
Ресурсы	Код ресурса
Инструкции	Код инструкции
Компании	Код компании
Юридические положения	Код юр положения



2. Реализация в MS ACCESS

2.1 Создание таблиц и схем данных

Создадим структуру всех таблиц в режиме Конструктора таблиц. На рис. 2.1 представлено окно конструктора с описанием таблицы Данные на клиентов. После создания полей таблицы в соответствии можно просмотреть созданные системой индексы (рис. 2.2). Т.к. наша база не большая, то не будем создавать индексы для всех полей.

Рис. 2.1 Окно конструктора

Рис. 2.2 Окно индексы

Аналогично создадим остальные таблицы.

2.2 Создание связей между таблицами

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

Рис. 2.7 Схема данных

2.3 Разработка запросов к базе данных

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

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

Создадим запросы, заданные в проекте:

1. Список клиентов, сгруппированный по городам

Для создания запроса с помощью Конструктора в окне базы данных выберем команду Создание запроса в режиме конструктора. Выберем таблицу «Данные на клиентов». Для построения запроса выберем поля Город и Фамилия клиента. Для поля Город применим сортировку по возрастанию. Запрос в режиме конструктора показан на рисунке 2.9.

Рис. 2.9 Режим конструктора

2. Список контрактов по отдельной услуге

Для создания запроса с помощью режима Конструктора в окне базы данных выберем команду Создание запроса в режиме конструктора. Выберем таблицу «Список услуг». Для построения запроса выберем поля Город и Фамилия клиента. Для поля Город применим сортировку по возрастанию.

Рис. 2.11 Режим конструктора

3. Список контрактов, сгруппированный по виду услуги за прошедший год

Создадим запрос с помощью Конструктора. Для построения запроса выберем поля Вид услуги из таблицы «Список услуг», Код контракта, Фамилия клиента, Название компании, Сумма контракта, Дата подписания. Для поля Вид услуги применим сортировку по возрастанию. Для поля Дата подписания введем условие отбора «**.**.2007», так как необходимо выбрать контракты, подписанные за прошлый год.

Результат выполнения запроса

4. Три самых важных клиента

Режим конструктора

SQL-реализация запроса

Результат выполнения запроса

5. Список работников, отсортированный в обратном порядке в зависимости от величины суммы контрактов

Режим конструктора

6. Ежемесячная сумма контрактов на услуги каждого вида

Режим конструктора

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

Cначала создадим запрос на выборку:

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

8. Запрос на исключение данных из основной таблицы контрактов, выполненных 3 года назад и более

Для избегания ошибок создадим копию основной таблицы «Контракты» - «Контракты копия».

Режим конструктора

SQL-реализация запроса

2.4 Стандартные формы и отчеты

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

Формы являются важнейшим средством создания интерфейса пользователя при работе с базами данных Access.

Можно создавать формы со многими целями:

1. Ввод и редактирование данных.

2. Управление ходом выполнения программы.

3. Вывод сообщений о ходе работы

4. Печать информации из базы данных.

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

Форма в режиме конструктора

Режим формы

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

Отчет имеет вид, аналогичный форме, но его можно печатать.

Создадим отчеты:

· Отчет по контрактам.

· Годовой отчет по сумме, полученной за оказанные услуги.

1. Отчет по контрактам

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

2. Годовой отчет по сумме, полученной за оказанные услуги

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

3. Анализ данных

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

Конструктор запроса «общая сумма прибыли»

Запрос 4. Сумма контрактов по определенной услуге

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



Заключение

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

MS Access в настоящее время является одной из самых популярных среди настольных программных систем управления базами данных. Среди причин такой популярности следует отметить:

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

- глубоко развитые возможности интеграции с другими программными продуктами, входящими в состав МS Office, а также с любыми программными продуктами, поддерживающими технологию OLE.

- богатый набор визуальных средств разработки.

MS Access - высокопроизводительная система управления реляционными базами данных, которая входит в состав профессиональной версии интегрированного пакета Microsoft Office.

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

1. Долбков В.И. Курс лекций «Базы данных».

2. Учебно-методическое пособие по курсу «БАЗЫ ДАННЫХ»: Методология проектирования баз данных средствами MS ACCESS

3. Михеева В., Харитонова И. Microsoft Access 2000- СПб: БХВ - Санкт-Петербург, 2000. - 508 с.

4. Методические указания к лабораторным работам по курсу «Базы данных», 2005

5. Бекаревич Ю., Пушкина Н. Самоучитель Microsoft Access 2000. - СПб.: БХВ - Санкт-Петербург, 2007.

6. Епанешников А., Епанешников В. Практика создания приложений в ACCESS 2007 Диалог-МИФИ, 2009

7. Лазарев И.П. «Microsoft Access для чайников». - СПб.; Притер, 2004

8. Методология проектирования баз данных средствами MS Access: Учебно-методическое пособие по курсу «Базы данных», 2005

Размещено на Allbest.ru

...