Автоматизация процессов, происходящих в отделе прямых продаж ОАО ОТПбанк

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

I.Анализ предметной области и документирование результатов

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

ОТП Банк (до февраля 2008 года Инвестсбербанк) -- c 2006 года банк одной из крупнейших банковских групп Европы «Группы ОТП» (OTP Group), подразделение OTP Bank -- крупнейшего коммерческого банка Венгрии (бывший сбербанк Венгрии). Вместе с новым брендом ОТП Банк получил доступ к финансовым возможностям и опыту европейского материнского банка, что позволило усилить развитие розничного и корпоративного бизнеса в России.

Ориентируясь преимущественно на VIP-клиентов, представительством был создан отел прямых продаж. Люди, принимаемые на работу в этот отдел, обучались и проходили практику, после чего становились агентами прямых продаж.

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

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

Изначально был создан один офис продаж. На данный момент число специалистов прямых продаж может достигать 200 в различные периоды. В связи с этим были открыты 3 дополнительных офиса, что осложнило контроль деятельности специалистов прямых продаж и учёта сдаваемых ими документов. А задержки информации об одобрении заявлений приводили к задержкам начисления бонусных сумм, что становилось основой для конфликтных ситуаций и недоумений среди специалистов отдела прямых продаж. Ещё одной проблемой являлось то, что сами специалисты прямых продаж не могли отслеживать текущее выполнение своего плана, что провоцировало их каждый раз звонить менеджерам и пытаться получить эти сведения у них. Это в свою очередь, отвлекало менеджеров от их главной задачи - помогать агентам с трудно разрешаемыми вопросами, а также работать с недостатками специалистов, помогая им преодолеть различные нестандартные ситуации, которые могли возникать в процессе общения с клиентами.

Выводы.

Проведённый анализ предметной области для отдела прямых продаж ОАО ОТП банка позволяет сделать вывод о том, что разработка базы данных для отдела прямых продаж крайне необходима, поскольку:

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

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

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

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

- внедрение базы данных позволит также хранить все сведения о специалистах прямых продаж систематизировано в электронном формате;

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

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

II. Построение модели данных с использованием CASE-средства AllFusion Erwin Data Modeler 4.1.4

1. Выбор и описание всех структурных элементов ER-модели

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

Одной из наиболее известных моделей данных, используемых для решения подобных задач является модель данных «сущность-связь» (ЕR-модель) П.Чена.

Модель «сущность-связь» («объект-отношение», ЕR-модель) - средство

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

Модель создавалась с учетом двух основных критериев:

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

2) разрыв между возможностями модели и возможностями коммерческих СУБД не должен быть слишком большим, иначе возникают проблемы реализации.

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

Для наглядности описание основных элементов модели можно представить в виде таблиц следующей структуры:

Название сущности	Описание сущности	Название атрибута	Тип атрибута	Описание атрибута
Специалист отдела прямых продаж	Специалист отдела прямых продаж занимается поиском клиентов с последующим оформлением кредитных карт. Имеет обозначенный в часах и указанный в контракте рабочий день. Длительность рабочего дня обсуждается между специалистом и представителем отдела по работе с клиентами при устройстве на работу. Также специалист отдела прямых продаж не имеет чёткого графика работ и выходит на работу только тогда, когда ему это удобно с одним лишь ограничением: количество часов рабочего дня, указанные в контракте, должны отрабатываться в тот день, когда он выходит на работу.	Табельный номер	Number
		ФИО	String
		паспортные данные	String	Указывается серия и номер паспорта, где, когда и кем выдан.
		Телефон	Number	_____
		адрес	String	Адрес проживания
		длительность рабочего дня в часах	Number	Указывается в контракте при принятии специалиста прямых продаж на работу.
		оклад	Number	Размер оклада
		бонус за одобрение золотой карты	Number	Размер бонуса за выпуск золотой карты
		бонус за одобрение серебряной карты	Number	Размер бонуса за выпуск серебряной карты
Специалист кредитного отдела	Занимается рассмотрением поданных заявлений на выпуск кредитных карт. Может одобрить или не одобрить выпуск кредитной карты.	Табельный номер	Number	Описание атрибута
		ФИО	String
Пакет документов	Сдается в конце дня, когда специалист отдела прямых продаж выходил на работу	Шифр	String	Задаётся автоматически и состоит из кода специалиста прямых продаж и даты рабочего дня. Пример: 21.12.1986.GL54
		дата создания	Datetime	_____
		количество карт	Number	Указывается количество заявлений, оформленных на выпуск кредитной карты.
Заявление на выпуск кредитной карты	Содержит сведения о клиенте: ФИО, паспортные данные, место работы, размер ежемесячного дохода, контактная информация и др. При работе с базой данных указывается только идентификатор заявления - его номер. Специалист кредитного отдела работает с первичным документом - бумажным листом заявления.	номер заявления	Number	Состоит из 8-значного номера. Пример: 00507418
		одобрение	Number	В случае одобрения кредитной карты к выпуску, специалист кредитного отдела ставит единицу в поле одобрения, в противном случае - ноль.
		комментарии	String	В случае, когда карта не одобрена из-за некачественных копий документов, из-за неправильно заполненного документа или из-за недостаточности каких-либо документов, специалист кредитного отдела пишет комментарии для специалиста отдела прямых продаж. При возможности, специалист отдела прямых продаж может доработать заявление и снова сдать его.
План на месяц	Каждый специалист отдела прямых продаж имеет план на месяц, который вычисляется автоматически, в зависимости от указанной в контракте длительности рабочего дня и среднестатистического числа рабочих дней специалиста прямых продаж - 20 дней.	шифр	String	Указывается пользователем
		месяц	String	Указывается пользователем
		количество заявлений	Number	Автоматически вычисляется минимальное количество заявлений, которое должен оформить специалист прямых продаж. Количество заявлений зависит от длительности рабочего дня и среднестатистического числа рабочих дней - 20 дней. По статистике, за 3 часа своей работы, агент прямых продаж находит и оформляет одного клиента. Эти данные также используются при вычислении плана.
		количество одобренных заявлений	Number	Исчисляется автоматически. При этом используются среднестатистические данные по одобрению заявлений - 20%.
Клиент	Клиент, желающий приобрести кредитную карту	код клиента	Number
		ФИО	String
Кредитная карты		номер карты	String	Задается автоматически.
		вид	String	Золотая или серебряная карта в зависимости от желания потребителя
		срок действия	Datetime	Серебряная карта делается на 3 года, золотая на 4 года
		процентная ставка	Number	По серебряной карте ставка - 18%, по золотой карте - 15%

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

Полученные описания основных объектов предметной области в терминах модели «сущность - связь» должны быть представлены диаграммным (графическим) методом. На практике обычно используются два подхода к построению диаграмм «сущность-связь»: ручной метод и с использованием САSЕ-средств. При разработке сложных структур баз данных рациональнее применять второй подход, поскольку использование автоматизированного проектирования, во-первых, существенно снижает временные затраты на разработку модели, а во-вторых, обеспечивает возможность целостного, согласованного восприятия проекта группой разработчиков.

Процесс построения модели «сущность-связь» выполняется в четыре шага:

идентификация представляющих интерес сущностей и связей;

2) идентификация семантической информации о наборах связей

(определение типов связей);

3) определение наборов значений и атрибутов;

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

2. Построение модели данных в нотации П.Чена

Существует несколько правил применения диаграммного метода для построения модели:

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

все связи между сущностями должны быть показаны явно
(разрешены все типы связей - 1:1, 1:М, М:М, а также отношения типизации);

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

На нижеприведённой диаграмме изображены все сущности с их атрибутами и связями с другими сущностями.

3. Построения модели данных средствами ALLFusion ERWin Data Modeler 4.1.4

3.1 Задание базовых параметров системы, необходимых для построения модели данных

Перед началом работы с CASE-средством следует отметить соответствие между общепринятой терминологией наименования этапов моделирования и строящихся моделей данных, и принятыми определениями в ERwin: Концептуальная модель данных - это Логическая модель данных в ERwin, а Логическая или СУБД - ориентированная модель данных общепринятой терминологии - это Физическая модель в ERwin.

Запустим приложение ERwin. В появившемся окне выберем опцию Create a new model - создать новую модель. В следующем окне, которое откроется после щелчка по кнопке ОК, присвоим новой модели статус Logical/Physical с целью обеспечения доступа к разным уровням моделирования в рамках одного проекта, а в области Target Database укажем в поле Database используемое приложение Access Version 2000.

После активации кнопки ОК окна Create Model установим параметры создаваемой модели:

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

Для определения структуры данных модели следует определить правила, обеспечивающие корректное определение объектов, их атрибутов и связей между ними. В ERwin эту задачу можно решить, выбрав соответствующую нотацию (способ описания основных элементов модели). Для этого в окне Model Properties выберем вкладку Notation - определение нотаций. Рекомендуется выбор нотации IDEF1Х, являющейся стандартом для описания структуры данных.

Построение модели сопряжено с множественным заданием имен объектов, причем часто повторяющихся. В этой связи для обеспечения семантической идентификации каждого элемента модели рекомендуется задать жесткие правила на возможность/невозможность дублирования имен. Для этого следует выбрать в главном рабочем окне Tools/Names/Model Naming Options…; а затем в экранной форме выбрать закладку Duplicate Names.

Выбор одного из условий позволит сформулировать Вашу стратегию именования имен объектов модели данных; рекомендуется запретить вообще повторение имен для всех объектов модели. Для этого следует отметить флажком пункт Disallow duplicate names.

В качестве дополнительного параметра, позволяющего настроить CASE-средство для работы, является поддержка шрифтов. Для установки шрифта в главном меню выбираем пункт Format, подпункт Default Fonts & Colors. В открывшемся окне во всех вкладках в поле Font укажем тип шрифта ArialCYR и отметим флажком пункт Bold, указывая, что такой шрифт необходимо применять везде. Также можно установить размер шрифта (Font Size) и цвет текста, заливок и линий (Color).

Теперь перейдём к созданию самой модели данных.

3.2 Построение логической модели данных в нотации IDEF1X (уровень сущностей, уровень полной атрибутивной модели данных)

Логическая модель данных в терминах IDEF1Х может быть отражена на следующих уровнях представления:

уровень сущностей,

уровень описаний,

уровень полной атрибутивной модели.

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

Щёлкнем в верхнем меню по пиктограмме «Entity (Сущность)» и затем разместим в рабочем поле первую сущность, щёлкнув ещё раз левой клавишей мышки после наведения курсора на выбранную область рабочего поля. В результате появится объект с именем E/1. Введём вместо обозначения Е/1 своё название сущности - «Специалист отдела прямых продаж».

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

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

Далее, выбрав в том же контекстно-зависимом меню пункт Attributes, добавим поочерёдно атрибуты сущности, используя кнопку New - в открывающемся окне New Attribute вводим название атрибута и его тип:

?<unknown> - неизвестный тип

Blob - объект большого размера

Datetime - дата, время

Number - число

String - текст

Для обозначения атрибута, как ключевого,

необходимо в окне Attributes, выделив имя

этого атрибута, отметить флажком пункт

Primary Key

тогда перед именем атрибута появится

значок «ключик»:

Также в состав диалогового окна Attributes входят следующие кнопки, которые могут нам понадобиться: Rename - переименовать атрибут, Delete - удалить атрибут; и вкладка Definition, в которой можно писать комментарии к выделенному атрибуту. Все созданные атрибуты отображаются в поле Attribute диалогового окна, а после закрытия диалогового окна - и в самой сущности. Сформируем таким образом следующие сущности и их атрибуты: (в таблицах выделены ячейки с ключевыми атрибутами)

Рассмотрим свойства связей и процедуру их создания:

а) Связи устанавливаются между двумя сущностями (бинарные связи), одна из которых

является родительской, а другая - дочерней (Model/Relationships);

б) связь может быть установлена таким образом, что родительская и дочерняя сущности совпадают - рекурсивная связь (или «рыболовный крючок»);

в) связь должна иметь наименование (глагольная конструкция, характеризующая отношение между родительской и дочерней сущностями);

г) связь в модели должна иметь мощность: «один-ко-многим», «многие-ко-многим», «один-к-одному»;

д) связь имеет тип - идентифицирующая связь или неидентифицирующая.

Идентифицирующая связь устанавливается между независимой сущностью (со стороны родительской сущности) и зависимой сущностью (стороны дочернего конца связи); ERwin автоматически преобразует независимую сущность в зависимую. При установлении идентифицирующей сущности первичный ключи родительской сущности автоматически переносится в состав первичного ключа дочерней сущности.

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

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

Неидентифицирующая связь на диаграмме изображается пунктирной линией.

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

Задание соответствующих параметров устанавливается в свойствах связи (Model / Relationships). Иллюстрация этой процедуры выглядит следующим образом:

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

Правила ссылочной целостности представляют собой логические конструкции, которые выражают бизнес-правила использования данных схемы базы данных и представляют собой правила вставки, удаления и замены. При генерации схемы базы данных на основе опций логической модели, задаваемых во вкладке Model / Relationships … Referential Integrity / Actions будут созданы правила декларативной ссылочной целостности, которые должны быть предписаны для каждой связи, и триггеры, обеспечивающие ссылочную целостность.

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

Задание условий для правильной ссылочной целостности в Erwin:

В приложении Erwin для построения связей существуют следующие пиктограммы:

- связь один-ко-многим

- связь многие-ко-многим

- неидентифицирующая связь

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

Изменение параметров связи можно осуществлять через контекстно-зависимое меню, вызываемое щелчком правой кнопки мыши по построенной связи.

Для отображения на экране свойств связей, наименований сущностей и атрибутов выполняется в Format/ Entities Display, Format/ Relationships Display…Verb Phrase, Cardinality, Referential Integrity),

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

Последовательность действий:

1) вызовем рабочее окно заполнения свойств атрибута Model/Attributes;

2) в области Domain выберем кнопку дополнительных свойств...;

3) построим макрокоманду: %Lower(%OwnerEntity%AttDomain)

4) выберем режим New для задания нового атрибута;

5) производим ввод имен атрибутов в окно Attribute Name

Особый интерес представляют идентификаторы объекта (сущности) или так называемые первичные ключи. Атрибут, являющийся первичным ключом, помещается в верхней части прямоугольника, графически отображающего сущность; в экранной форме свойств атрибута для первичного ключа указывается принадлежность к Primary Key; дополнительный графический акцент может быть сделан после выполнения следующей операции: Format/Entity Display/Primary Key Designator

В поддерживаемой нотации IDEF1X осуществляется миграция первичного ключа сущности в те объекты, между которыми установлена связь (за исключением явной связи «многие-ко-многим»). Местоположение мигрирующего ключа или внешнего ключа определяется свойствами связи. Для идентифицирующей связи перенос ключа осуществляется в часть первичного ключа зависимой сущности в случае неидентифицирующей связи - внешний ключ относится в часть описательных атрибутов. Для отображения внешних ключей необходимо выполнить следующие действия: Format/Entity Display/Foreign Key Designator.

Ниже приведена построенная в Erwin модель

3.3 Подготовка отчётов по модели средствами Report Builder

Для анализа построенной модели следует документировать все ее компоненты. Для этого в AllFusion Erwin Data Modeler 4.1.4 используется функция построителя отчетов. Для того, чтобы вызвать мастера построителя отчётов выберем в меню пункт Tools / Report Builder.

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

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

Выбрав вкладку Section можно задать генеральные параметры секции: имя, размер шрифта заголовка, цвет и пр. Во вкладке Property Tree выбираются необходимые для отображения данные: имена, комментарии и т.д. Указав все параметры, необходимо закрыть меню. В правой части мастера построения отчётов будут отображены заданные параметры:

Окно мастера построения отчётов:

Аналогично создаются другие секции отчёта.

После создания всех необходимых секций и задания их параметров в меню мастера построения отчётов выбирается пункт File / Run. В результате будет создан отчёт:

Титульная страница:

Описание сущностей в отчёте:

Описание атрибутов в отчёте выглядит следующим образом:

III. Построение нормализованной (до 3 НФ) реляционной модели предметной области

1. Трансформация концептуальной модели данных в реляционную модель

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

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

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

II. На уровне физической, в нашем случае реляционной модели, создать дополнительную таблицу, связанную с родительскими таблицами связью типа «1 :М».

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

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

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

Специалист отдела прямых продаж (Код специалиста прямых продаж, …)

Пакет документов (Шифр пакета, Код специалиста прямых продаж, …)

Заявление на выпуск кредитной карты (Номер заявления, Код специалиста кредитного отдела…)

Специалист кредитного отдела (Код специалиста кредитного отдела, …)

План на месяц (Название месяца, Код специалиста прямых продаж, …)

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

Для перехода в приложении ERwin на физический уровень необходимо в меню выбрать опцию перехода на физический уровень:

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

2.Создание системы индексов

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

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

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

Определим основные параметры, задаваемые для расчета размера каждой таблицы базы данных:

начальное количество строк реляционных таблиц;

максимально возможное количество строк;

планируемый рост числа строк в месяц.

Параметры, задаваемые для отдельных атрибутов таблицы:

длина поля (для тех типов данных, где выполнение той функции возможно);

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

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

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

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

V. Построение схемы базы данных в СУБД MS Access 2000, используя процедуру прямого проектирования в AllFusio ERwin Data Modeler 4.1.4

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

В этой связи, применение САSЕ-средств позволяет, указав наименование выбранной СУБД, сгенерировать системный каталог базы данных в конкретной среде и подготовить «поле» для работы программистов, которые будут осуществлять разработку приложений и пользовательских запросов к базе данных. Эта процедура носит название прямого проектирования (Forward Engineer / Shema Generation) и выполняется только на физическом уровне. Для осуществление этой процедуры необходимо:

создать в выбранной СУБД пустую базу данных;

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

выбрать базовую СУБД - Database / Choose Database,

- указать на выполнение процедуры прямого проектирования - Тооls / Forward Engineer / Shema Generation;

- произвести выбор элементов модели данных (таблиц, представлений, отношений, индексов и т.д.), которые должны быть перенесены в системный каталог базы данных;

- выбрать опцию Generate, где User Name - admin, Database - адрес пустой базы данных;

- указать операцию Connect,

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

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

Ниже приведены окна генерирования модели данных и схема данных, созданная в приложении Access.

Окно установления параметров:

Окно завершения генерирования:

Структура базы данных, полученная методом прямого проектирования и схема данных в приложении MS

VI. Формулировка запросов к базе данных в рамках выделенных функций предметной области

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

Для создания запросов на добавление/удаление/обновление используется конструктор запросов. Сначала добавляем нужную таблицу:

Выбираем в главном верхнем меню «Запрос - Добавление», «Удаление» или «Обновление» в зависимости от типа запроса, который делаем.

Запрос 1. На добавление

Указываем поочерёдно поля таблицы для добавления и к каждому полю записываем в квадратных скобках сообщение, которое будет видеть пользователь при добавлении данных, например запрос для таблицы 1 будет выглядеть в режиме конструктора следующим образом:

В режиме SQL запрос будет записан следующим образом:

INSERT INTO [1- СОП] ( TabSOP, FioSOP, Pasp, Tel, Adr, RabDen, Okl, BonSer, BonZol )

SELECT

[Введите табельный номе сотрудника отделка продаж] AS Выражение1,

[Введите ФИО] AS Выражение2,

[Введите паспортные данные] AS Выражение3,

[Введите телефон] AS Выражение4,

[Введите адрес] AS Выражение5,

[Введите продолжительность рабочего дня, в часах] AS Выражение6,

[Введите оклад] AS Выражение7,

[Введите бонус за серебряную карту] AS Выражение8,

[Введите бонус за золотую карту] AS Выражение9

FROM [1- СОП];

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

И т.д. у пользователя спрашиваются значения для всех полей таблицы.

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

Для добавления записи не обращаем внимания на данное сообщение и нажимаем ДА.

В результате в таблицу 1 добавляется новая запись:

Запрос 2. На обновление

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

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

В режиме SQL будет записано:

UPDATE [1- СОП] SET

[1- СОП].TabSOP = [Введите табельный номер сотрудника отдела продаж, по которому надо изменить данные],

[1- СОП].FioSOP = [Введите фамилию], [1- СОП].Pasp = [Введите паспортные данные],

[1- СОП].Tel = [Введите телефон],

[1- СОП].Adr = [Введите адрес],

[1- СОП].RabDen = [Введите длительность рабочего дня, в часах],

[1- СОП].Okl = [Введите оклад],

[1- СОП].BonSer = [Введите бонус за серебряную карту],

[1- СОП].BonZol = [Введите бонус за золотую карту];

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

Предположим, сотрудник Абрамцева вышла замуж и поменяла фамилию на Сальникова.

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

Запрос 3. На удаление

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



В режиме SQL будет записано:

DELETE [1- СОП].TabSOP

FROM [1- СОП]

WHERE ((([1- СОП].TabSOP)=[Введите табельный номер сотрудника отдела продаж, по которому необходимо удалить данные]));

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

Результат - Сальникова удалена из базы данных сотрудников отдела продаж.

Аналогично можно было сделать удаление записи по ФИО.

Запрос 4. Процент одобрения

Создаём два вспомогательных запроса:

4.1. Сперва запрос, считающий кол-во одобренных заявлений:

Запрос в режиме конструктора:



Режим SQL:

SELECT [1- СОП].TabSOP, [1- СОП].FioSOP, Count([4- Заявление].NomZ) AS [Кол-во одобренных заявлений]

FROM ([1- СОП] INNER JOIN [3- Пакет] ON [1- СОП].TabSOP=[3- Пакет].TabSOP) INNER JOIN [4- Заявление] ON [3- Пакет].ShifrPak=[4- Заявление].ShifrPak

GROUP BY [1- СОП].TabSOP, [1- СОП].FioSOP, [4- Заявление].Odobr

HAVING ((([4- Заявление].Odobr)="Да"));

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

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

Запрос в режиме конструктора:

Режим SQL:

SELECT [1- СОП].TabSOP, [1- СОП].FioSOP, Sum([3- Пакет].KolZ) AS [Всего сдано заявлений], Sum([Вспомогательный запрос: Кол-во одобренных заявлений по СОП].[Кол-во одобренных заявлений]) AS [Всего одобренных заявлений]

FROM (([1- СОП] INNER JOIN [Вспомогательный запрос: Кол-во одобренных заявлений по СОП] ON [1- СОП].TabSOP=[Вспомогательный запрос: Кол-во одобренных заявлений по СОП].TabSOP) INNER JOIN [3- Пакет] ON [1- СОП].TabSOP=[3- Пакет].TabSOP) INNER JOIN [4- Заявление] ON [3- Пакет].ShifrPak=[4- Заявление].ShifrPak

GROUP BY [1- СОП].TabSOP, [1- СОП].FioSOP;

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

4.3 Наконец, на базе второго вспомогательного запроса создаём окончательный запрос:

Запрос в режиме конструктора:

Строим выражение «процент одобрения»:



В свойствах вычисляемого поля указываем тип данных:

Режим SQL:

SELECT [Кол-во сданных и одобренных заявлений по СОП].TabSOP, [Кол-во сданных и одобренных заявлений по СОП].FioSOP, [Кол-во сданных и одобренных заявлений по СОП].[Всего сдано заявлений], [Кол-во сданных и одобренных заявлений по СОП].[Всего одобренных заявлений], [Кол-во сданных и одобренных заявлений по СОП]![Всего одобренных заявлений]/[Кол-во сданных и одобренных заявлений по СОП]![Всего сдано заявлений] AS [Процент одобренных заявлений]

FROM [Кол-во сданных и одобренных заявлений по СОП];

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

Запрос 5. Заработная плата СОП

Создаём три вспомогательных запроса:

2.1. Запрос, определяющий, какие карты были выданы в данном месяце.

Например, построим запрос для января, тогда в последнем поле «номер месяца», ставим условие отбора =”1”, при необходимости посчитать заработную плату за февраль, будем менять условие на номер месяца =”2” и т.д.

Запрос в режиме конструктора:

Режим SQL:

SELECT [1- СОП].TabSOP, [4- Заявление].NomZ, [6- Кредитная карта].Vid, Month([3- Пакет]!Data) AS [Номер месяца]

FROM ([5- Клиент] INNER JOIN (([1- СОП] INNER JOIN [3- Пакет] ON [1- СОП].TabSOP = [3- Пакет].TabSOP) INNER JOIN [4- Заявление] ON [3- Пакет].ShifrPak = [4- Заявление].ShifrPak) ON [5- Клиент].KodKlient = [4- Заявление].KodKlient) INNER JOIN [6- Кредитная карта] ON [5- Клиент].KodKlient = [6- Кредитная карта].KodKlient

WHERE (((Month([3- Пакет]![Data]))="1"));

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

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

Запросы в режиме конструктора:

Режим SQL:

SELECT [21- Вспомогательный: карты, выданные в январе].TabSOP, Count([21- Вспомогательный: карты, выданные в январе].NomZ) AS [Кол-во золотых карт по заявлениям от данного сотрудника]

FROM [21- Вспомогательный: карты, выданные в январе]

GROUP BY [21- Вспомогательный: карты, выданные в январе].TabSOP, [21- Вспомогательный: карты, выданные в январе].Vid

HAVING ((([21- Вспомогательный: карты, выданные в январе].Vid)="Золотая"));

SELECT [21- Вспомогательный: карты, выданные в январе].TabSOP, Count([21- Вспомогательный: карты, выданные в январе].NomZ) AS [Кол-во серебряных карт по заявлениям от данного сотрудника]

FROM [21- Вспомогательный: карты, выданные в январе]

GROUP BY [21- Вспомогательный: карты, выданные в январе].TabSOP, [21- Вспомогательный: карты, выданные в январе].Vid

HAVING ((([21- Вспомогательный: карты, выданные в январе].Vid)="Серебряная"));

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

Наконец создаём окончательный запрос, использующий результаты запросов 2.2 и 2.3

Запрос в режиме конструктора:

Вычисление заработной платы в построителе выражений по формуле:

Оклад + Бонус за одобрение серебряной карты * Кол-во серебряных карт + Бонус за одобрение золотой карты * Кол-во золотых карт:

Режим SQL:

SELECT [1- СОП].TabSOP, [1- СОП].FioSOP, [1- СОП]!Okl+[1- СОП]!BonSer*[23- Вспомогательный: Кол-во серебряных в январе по СОП]![Кол-во серебряных карт по заявлениям от данного сотрудника]+[1- СОП]!BonZol*[22- Вспомогательный: Кол-во золотых в январе по СОП]![Кол-во золотых карт по заявлениям от данного сотрудника] AS [Заработная плата сотрудника, руб]

FROM ([1- СОП] INNER JOIN [22- Вспомогательный: Кол-во золотых в январе по СОП] ON [1- СОП].TabSOP=[22- Вспомогательный: Кол-во золотых в январе по СОП].TabSOP) INNER JOIN [23- Вспомогательный: Кол-во серебряных в январе по СОП] ON [1- СОП].TabSOP=[23- Вспомогательный: Кол-во серебряных в январе по СОП].TabSOP;

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

Запрос 6. Получил ли конкретный клиент карту?

Запрос в режиме конструктора:



Режим SQL:

SELECT [5- Клиент].FioKlient, [4- Заявление].Odobr

FROM [5- Клиент] INNER JOIN [4- Заявление] ON [5- Клиент].KodKlient=[4- Заявление].KodKlient

WHERE ((([5- Клиент].FioKlient)=[Введите ФИО клиента]));

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

Запрос 7. Список клиентов, получивших карты

Запрос в режиме конструктора:

Режим SQL:

SELECT [5- Клиент].FioKlient, [6- Кредитная карта].NomKart, [6- Кредитная карта].Vid, [6- Кредитная карта].Srok, [6- Кредитная карта].Stavka

FROM [5- Клиент] INNER JOIN [6- Кредитная карта] ON [5- Клиент].KodKlient = [6- Кредитная карта].KodKlient;

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

Запрос 8. Привязка клиентов к СОП

Запрос в режиме конструктора:

Режим SQL:

SELECT [5- Клиент].KodKlient, [5- Клиент].FioKlient, [1- СОП].TabSOP, [1- СОП].FioSOP

FROM [5- Клиент] INNER JOIN (([1- СОП] INNER JOIN [3- Пакет] ON [1- СОП].TabSOP = [3- Пакет].TabSOP) INNER JOIN [4- Заявление] ON [3- Пакет].ShifrPak = [4- Заявление].ShifrPak) ON [5- Клиент].KodKlient = [4- Заявление].KodKlient

WHERE ((([3- Пакет].ShifrPak)=[4- Заявление]![ShifrPak]));

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



Запрос 9. Внесение сведений об одобрении заявлений и комментариев к ним

В данном случае используется принцип построения запроса на обновление записи:

Запрос в режиме конструктора:

Режим SQL:

UPDATE [4- Заявление] SET [4- Заявление].NomZ = [Введите номер заявления для внесения сведений об одобрении], [4- Заявление].Odobr = [Введите статус одобрения Да/Нет], [4- Заявление].KomZ = [Комментарий (необязательное поле)];

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

Допустим, что заявление 6002 только что рассмотрено специалистом отдела кредитования и он хочет внести сведения об одобрении карты и при необходимости вписать свои комментарии:

Вид таблицы до выполнения запроса:



Выполнение запроса:

Таблица после добавления сведений:

VII Формирование отчетов

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

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

Используем мастер создания отчётов:

Выбираем нужные поля:

Уровни группировки:

Указываем, какие итоги надо вывести:

В конструкторе редактируем названия полей, цвет, размер шрифта при необходимости. В итоге получаем отчёт вида:

Варианты отчётов:

В процессе создания отчёта можно также использовать созданные ранее запросы, например, запрос по проценту одобрения, и тогда получаем отчёт вида:

Или на базе запроса по вычислению заработной платы можно создать отчёт с подведением итогов по заработным платам в январе:

VIII. Разработка системы пользовательского интерфейса информационной системы

Критерии качественности интерфейса

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

помогает эффективно решать поставленные перед пользователя задачи;

органично вписывается в бизнес-процессы;

удобен и прост в применении;

обеспечивает стабильную и предсказуемую работу системы.

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

Например, для просмотра таблицы 1 кнопка создаётся следующим образом:

- выбираем кнопку на панели инструментов:

- размещаем её на форме, в результате чего открывается диалоговое окно для выбора действия. Выбираем «Работа с формой - открыть форму»

- выбираем форму для нужной таблицы (формы для таблиц созданы ранее в пункте 4)

- выбираем способ открытия формы:

- выбираем рисунок дл кнопки:

- делаем подпись к кнопке с помощью метки:

Повторяем действия для всех таблиц.

Далее создаём кнопки для выполнения запросов на добавление/обновление(изменение) записи. Данные кнопки создаются аналогично, но выбирается раздел «Разное - Выполнить запрос»

Для создания кнопки на удаление записи требуется сначала создать макрос на выполнение запроса, т.к. создать кнопку так же, как и для выполнения запроса на добавление/обновление не удастся: Access не отображает запросы на удаление в окне выбора запроса, который надо выполнить во время нажатия на данную кнопку:

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

Записываем, что надо открыть запрос, а внизу выбираем нужный запрос:

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

Аналогично были созданы кнопки для добавления, изменения и удаления записей по всем таблицам. Также на форме были помещены кнопки выхода из приложения и перехода на форму «МЕНЮ», на которой будут размещены основные разделы базы данных.

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

Для удобства пользователя форма была создана в виде таблицы, границы которой были нарисованы с помощью элемента «Линия».

Создание формы для запросов:

Форму создаём аналогично форме для работы с данными

Создание формы для отчётов:

Форму создаём аналогично форме для работы с данными, однако на этот раз располагаем кнопки, для которых выбираем действие «Просмотр отчёта»:

Выбирая далее нужный отчёт для каждой кнопки, получаем форму для просмотра отчётов:

Создание формы управления проектом.

Размещаем на обобщённой форме кнопки с переходом к трём ранее созданным формам:

- форме данных

- форме запросов

- форме отчётов

а также кнопку выхода из приложения:

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

Выбираем форму МЕНЮ:

Теперь при открытии базы данных автоматически будет запускаться форма с меню.

Создание базы данных завершено.

IX. Защита базы данных паролем

Способ установления паролей описан на сайте:

http://office.microsoft.com/ru-ru/access-help/HP005188275.aspx.

Частичную защиту базы данных обеспечивает добавление пароля, ограничивающего пользователей, которые могут открывать базу данных. Microsoft Access хранит пароль базы данных в незашифрованном виде. Если это нарушает безопасность защищаемой паролем базы данных, не следует использовать пароль. Вместо этого необходимо определить защиту на уровне пользователей (Защита на уровне пользователей. При активизации защиты на уровне пользователей в базе данных Microsoft Access администратор базы данных или владелец объекта предоставляет определенные разрешения отдельным пользователям и группам пользователей на следующие объекты: таблицы, запросы, формы, отчеты и макросы.).

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

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

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

Сделайте резервную копию базы данных и сохраните ее в надежном месте.

В меню Файл выберите команду Открыть.

Щелкните стрелку справа от поля Папка, выберите диск и папку, в которой расположен файл базы данных Microsoft Access (База данных Microsoft Access. Совокупность данных и объектов (например, таблиц, запросов или форм), относящихся к определенной задаче.), и щелкните значок этого файла.

Щелкните стрелку справа от кнопки Открыть, выберите вариант Монопольно и откройте базу данных.

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

Введите пароль в поле Password.

Используйте надежные пароли, представляющие собой сочетание прописных и строчных букв, цифр и символов. Пароли, не содержащие набор таких элементов, являются ненадежными. Надежный пароль: Y6dh!et5. Ненадежный пароль: House27. Пароли должны состоять не менее чем из 8 знаков. Рекомендуется использовать фразу-пароль, состоящую из 14 или более знаков. Дополнительные сведения см. в разделе Защита личных сведений с помощью надежных паролей.

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

Имена учетных записей могут иметь длину от 1 до 20 знаков и могут состоять из букв алфавита, цифр, пробелов и знаков из расширенных наборов, за исключением следующих:

знаки " \ [ ] : | < > + = ; , . ? *

пробелы в начале имени;

управляющие знаки (с кодами ASCII от 10 до 31).

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

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

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

Вводим пароль для созданной базы данных «Лида»

Заключение

По итогам проведённой работы можно сказать, что модель достаточно адекватно отражает все процессы, происходящие в отделе прямых продаж ОАО ОТПбанк. При этом стоит отметить, что построенная модель позволяет в дальнейшем решить все проблемы, описанные в пункте 1 главы I:

- автоматизировано отслеживание сдачи дневных отчётов о проделанной работе; предметный запрос модель данный

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