Проектирование базы данных агентства по оказанию маркетинговых услуг

Задание на курсовую работу

 

Проект №6. Консалтинговое агентство.

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

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

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

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

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

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

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

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

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

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

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

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

Введение

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

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

Существует несколько видов СУБД. Наиболее известными и популярными СУБД являются Access, FoxPro и Paradox. Данная работа выполнена в Access.

Один из основных типов моделей данных – реляционная база данных.

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

В настоящее время реляционный подход к построению баз данных является наиболее распространенным. Достоинства такого подхода:

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

·  наличие простого и в то же время мощного математического аппарата, опирающегося на теорию множеств;

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

 

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.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. Реализация в MSACCESS 2.1. Создание таблиц и схем данных

 

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

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

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

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

Рис. 2.3 Данные таблицы «Данные на клиентов»

Рис. 2.4 Данные таблицы «Контракты»

Рис. 2.5 Данные таблицы «Список услуг »

Рис. 2.6 Данные таблицы «Данные на менеджеров»