Analyzing and Redesigning Telemarketing Application

Document Sample
Analyzing and Redesigning Telemarketing Application Powered By Docstoc

           Analyzing and Redesigning a Telemarketing
                                                    Kay Jacintha D’Souza

                                                                  purpose and outcome of each call. Furthermore, this provides
 Abstract— This paper describes the procedures involved           an effective means of tracking the performance of agents and
in redesigning an Insurance Agency application, in                customer service representatives, consequently improving
accordance with the design and implementation principles          customer service. Agents can be assigned to a lead through
outlined in the “software factory” course. This involved a        effective assignment pools and campaigns which are
real- time customer and to simulate a working                     predetermined based on priority. Moreover, this application
environment, this analysis was carried out as a team of           also provides for controlled access to lead information to
two. The current application has a number of flaws which          preserve privacy.
include: a poorly designed database, inadequate validation
and disorganized display of content on web pages. The              Problem
customer required that the shortcomings of the existing             The project analyzed the current application, diagnosing the
system be identified and necessary changes be                     root cause of defects in the system and providing effective
incorporated. To provide enhancements and incorporate             solutions. In order to emphasize on the effectiveness of the
additional functionality, code Redesign and Refactoring           design and implementation, a comparison will be carried out
principles were advocated. This removed redundancy,               between the two versions which clearly identifies the latter as
eliminated unused functionality and scaled down                   being more scalable, maintainable and of higher performance.
complexity in design and subsequently implementation.
This project involved extensive interaction with the               Scope
customer in the initial stages of design. The customer,             The project involved extensive database redesign to provide
being non-technical, indecisive and unrealistic with respect      for scalability. Scalability is a basic requirement for any
to deadlines, made this challenging. The applied course not       dynamic data intensive application and hence needed to be
only provided effective guidelines for design issues but also     addressed. However, due to the familiarity of the users to the
provided insights into dealing with changing customer             current interface, changes to the user interface will not be
requirements. This paper elucidates the challenges                incorporated. In addition, the scope does not involve
involved in refactoring code in lieu off the ever-changing        acquiring raw lead information into the system. The existing
customer requirements.                                            application does not contain documentation of any form which
                                                                  may include: user stories, design documentation or in the form
                    I. INTRODUCTION                               of comments in code. During the redesign, documentation at
                                                                  various stages was of importance to have a lucid understanding
 Background                                                       of the system but was not mandatory.
   The use of software in telemarketing applications is
indispensable. The application is not only required to            Assumptions
assimilate information regarding potential clients, also known      It is assumed that the data entered by the agent is accurate.
as leads, but also provide a means of tracking the performance    The user input cannot be controlled but can be restricted by an
of agents. The duties of an agent include: contacting a lead,     extent through validations which will be incorporated in the
providing the best quotes from a set of competitive quotes        application. The assumptions in the already existing system
offered from various companies, keeping track of lead             with respect to the implementation are as follows and will be
information, processing paperwork as well as payments and         assumed in the redesigned system as well:
billing. Without automation, this would be an arduous task.            • Active Server Pages is used to implement the given
In order to provide high quality of customer service and reduce             functionality.
costs it becomes necessary to track agent performance.                 • SQL Server 2000 is used as the database to store the
   The Insurance Agency Software aims at providing a means                  persistent information of the system
of determining eligibility, up-to-date rate information, policy        • Access to the database is provided through VBcript
information, and collecting payment – all in real time. In                  embedded in ASP
addition, it supports automation of calls and call-backs, which        • Validation for the fields in the page is done using
provides a provision for a “Do Not Call” list, and stores the               JavaScript
                                                                       • Federal Do-Not-Call List is accurate and up-to-date

    •    The database that generates quotes are up-to-date and         •    JMeter for testing
         provide accurate information
                                                                   Other Support
 Summary of Current Knowledge                                         Customer interaction was vital in gathering requirements
   Customer Requirements are dynamic. It is necessary,             and proving feedbacks. There was active support and
therefore, necessary to create a modular approach to design        cooperation from team member through the entire project
since this enables, customer requirements to be incorporated       which is very essential for success of a project. Constant
with ease. In addition, most user-interface applications are       guidance in analyzing, designing and implementing the project
prone to storage of erroneous data. This could be prevented to     and in formulating the report was project was provided by the
an extent by providing appropriate validation. It is also a        faculty.
known fact that inadequate relationship in data store could
lead to multiple instances of the same data being persisted.
This could slow down the data access and may also cause
                                                                   I. REVIEW OF LITERATURE
inaccurate data retrieval.
Standards                                                          About Active Server Pages
   The redesigned application would be broad enough to                Active server Pages Technology is widely used due to its
incorporate additional modules with no or little change to the     ease of implementation and use. Active Server Pages is a
existing framework. The comparisons between the two                server side scripting technology that provides dynamic,
versions will be carried out using JMeter which is an              database content to the client with minimal effort [1]. Its low
automated tool for analyzing the performance of web pages.         cost and abundance of support is another appealing feature
In addition, the number of lines of code per functionality is      which compels most companies to invest in it. In cases where,
calculated as a means of evaluating code redesign.                 the requirements are changing at a fast pace, ASP provides
                                                                   scalability and faster deployment. Most applications use
Approach                                                           Active Server Technology to provide a web- to- database
   Customer interaction is fundamental in assimilating             interface. This requires frequent retrieval of large amounts of
information regarding the system. On gaining a fairly good         data. In order to provide an efficient means of accessing data,
understanding of the system, the following approach was used:      ASP provides the notion of a “Session Object” which
     • A detailed analysis of the workflow was conducted, to       preserves the state of the system pertaining to a user through
         understand how the data moves through the system          the entire course of interaction with the system. Data which
     • Customer requirements and complaints with the               changes rarely is a good candidate for caching and hence can
         existing system were collected and taken into             accelerate data retrieval. Besides, the fact that ASP runs in the
         consideration while making recommendations and            same process as the Web Server, makes it faster and more
         finalizing the scope of the project.                      efficient in handling client requests. In addition, dynamic
     • Appropriate user stories were created to identify the       systems may require changing their persistent store and since
         functionality required in each module along with the      ASP works with ODBC compliant databases, migration to
         postconditions, preconditions and validations that        another database would not require changing the underlying
         needed to be taken into account.                          ASP interface. Even in case the architecture of a system is
     • Database redesign was carried out to include                updated to a multi-tier application, dedicated servers can be
         appropriate relationships to facilitate data access and   made to run different sections of an application, which not
         retrieval.                                                only provides load balancing but also attempts to eliminate a
     • The next step involved was coding; user entry               single point of failure. ASP files also provide a means of
         validation was also carried out simultaneously            securing information access form the client. Most html forms
     • Parallel to coding, testing was also done. No               can be accessed via the browser and thus the content of most
         automated tool was used to test the code however.         pages can be viewed which is a threat to privacy.           ASP
     • Documentation for code was written to facilitate            supports Active-X objects, which is used extensively incase of
         support.                                                  the “call-module”. JavaScript embedded in ASP provides a
     • A comparison of the already existing application and        powerful means of performing client-side validation. Thus
         the redesigned application was carried out using          ASP provides an application which is flexible and extendable
         JMeter                                                    and yet gives control over application state and resource
     • The results in the form of graphs were documented.
                                                                   About SQL Server 2000
Materials and Equipment
                                                                      Data storage and retrieval is a common concern in most
 The software and hardware requirements are as follows:
                                                                   large modern corporations. Vast amounts of data have to be
   • PC with Internet Information Services(IIS) installed
                                                                   stored securely and must be capable of supporting a large
        on it.
                                                                   number of users. The exceptional scalability and reliability of
   • PC with SQL Sever installed
                                                                   SQL Server 2000, as well as the fact that it works well with the
   • MicroSoft Front Page as an editor for ASP pages               underlying Microsoft Operating System, makes it the choice
   • Visio for documentation

for most software applications. As mentioned above, it can be       II. METHODOLOGY
easily interfaced with ASP as well. Besides providing a means
of storage and retrieval, databases have to provide a notion of     Analysis of the Existing System
“relationship” of data. SQL Server uses the concept of                Since this is an already existing system, which needed to be
“Relational Theory”, which originated from the set theory, to       redesigned and implemented, it is of importance to conduct a
maintain relationships between data. Relational Databases           post-mortem analysis of the application in use.
defines a process called “normalization” which prevents
redundant storage of information thus increasing storage              1. System Workflow
effectively. “Scalability” is another feature that SQL Server          The current system comprises of three main users-
excels in. Large number of applications can connect to a            Administrator, Agents and Customer Service Representatives.
single instance of SQL Server, through SQL Server                   As mentioned earlier, the agents and customer service
Components, with hardly any noticeable delays in access. SQL        representatives directly interact with the lead to assimilate
Server is capable of running as a standalone database in the        information and provide assistance in choosing the appropriate
same machine as the application or even supporting                  policy. In addition to customer interaction, the administrator
connections to thousands of users at the same time. This            also monitors user accounts, delegates tasks to agents and
flexibility of SQL Server to dynamically tune the system’s          keeps track of agent performance.
resources to suit a varying load of users makes it popular. The     The information from the lead is not obtained all at once and
ease of installation, deployment, use and support are also some     occurs over a period of time. In the current design, the data
of the factors considered while choosing a database for an          store mechanism is “entity-based”. This means to say that all
application [4].                                                    the data pertaining to a lead is stored in a pre-allocated space.
                                                                    On observation, however, it can be noted that this system is
About Software’s Best Practices                                     more “state-based” and hence the notion of state does play an
  While designing, analyzing, implementing and testing a            important role in data storage as will be discussed
software application, certain guidelines called the “Best           subsequently.
Practices” enhances the quality, performance and provides           This system is triggered when raw data, comprising of names
effective communication strategies. Customer requirements           and phone numbers of “potential leads” are obtained from
are by far the most dynamic part of a system. In order to           vendors. The customer service representatives then expunge
reduce the impact of customer requirements, Iterative               information pertaining to “potential leads” that lack interest in
Development is carried out. This not only ensures a more            purchasing a policy. Every lead is assigned to an agent who is
effective means of meeting deadlines but also provides a            delegated by the administrator. The agent then obtains the
prototype of the application at various stages, that the            details on the policy, that the assigned lead, is interested in
customer may view and evaluate. Managing requirements               purchasing. Information regarding the existing policy and past
needs to be done in order to ensure that the customer               policies, if present, is also entered into the system. In order to
                                                                    generate quotes for the lead, the lead is obligated to answer a
requirements are met at the same time preserving the rights of
                                                                    questionnaire which discloses information pertaining to the
the coder. This can be done through documentation and
                                                                    lead’s medical condition and day-to-day habits. Once the
prioritization. While designing the architecture of the system,
                                                                    tentative quotes are generated, if the lead’s interest in pursuing
it is often good practice to use Component Architectures.           the policy persists, then an appointment with a doctor is
This provides for extensibility and reuse of code and reduces       scheduled to confirm the Lead’s medical fitness. The medical
dependencies that form good design strategies. All the phases       reports are sent to the home office for approval and on based
of software development require documentation of some form.         on the policy purchased; paperwork is processed and sent out
By Modeling Visually, the information can be conveyed in a          to the Lead for consent. The system also provides a provision
more comprehendible form with out having to read large              for storing information regarding payments.             The lead
amounts of documentation. Managing Software Quality, is a           information is preserved for future reference and to provide
key to efficiency of any system. Every system has to be             high quality assistance.
effectively tested before being rolled out. Regression tests
should be carried out wherever appropriate. In the case of web
applications, a performance evaluation can be carried out
using automated tools which is a good measure of the
effectiveness of the tested functionality. All these practices if
implemented appropriately, enables a high quality product to
be rolled out to the customer [5].

                                                    Assign/Unassign                                                                                       for analysis, maintaining user information, tracking
                                                                                                                                                          agent performance.
                                                         View                                                                                     . However, all these modules are premature and have to be

                                                                                                                                                  worked in order to render them applicatory.
                           Login                       Enter/Edit

                                                                                                                          Agency DB                3. Flaws in the System
                                                      QuickQuote                                                                                     On analysis of the system and on eliciting the problems
                                                                                                                                                  faced by the customer, the following flaws seemed to
                                                                                                                                                  significantly affect the working of the system:
                                   «uses»                                                    «uses»

                                                                                              «uses»                                                   • There appears to be inadequate relationships between
                                                     Add/Edit User                            «uses»                                                       the tables of the database resulting in redundant data
                                                                                                                                                           being stored in multiple tables.
                                                        Search                                                                                         • The tables are created based on the page layout which
                       Login                                                                                                         «uses»
                                                                         «uses»                 «uses»                                                     is not an appropriate form of design
                           «uses»                    View/Edit/Add
                                                                                                                          Quote DB
                                                                                                                                                       • Due to inadequacy in relationship between tables, the
   Agent                       «uses»
                                                                                                                                                           queries take a long time to execute, in turn increasing
                                                  Add/Delete Schedule                                            «uses»
                                                                                                                                                           the time to load a page. This causes a lot of
                                                                                                                                                           inconvenience especially during assimilation of lead
                                                   Assign/ UnAssign
                                                                                                                                                           information since the leads are requested to wait for
                                                                                                                                                           that period of time, which is not very customer
                                                                                                                                                       • Due to the entity based approach of the design, a
                                                   View Potential Lead
                                                                                                                                                           number of columns in the tables, pertaining to a lead
                                         «uses»                                                                                                            were often left empty and thus space was often
             «uses»                                                                                                                                        allocated for unused columns. This reduced the
                           Login          «uses»
                                                                                                                                                           efficiency of the system drastically.
                                                       Add Lead
                                                                                                                                                       • Inappropriate validation has lead to the presence of
                                                                                    «uses»                                                                 vast amounts of faulty or inadequate data in the
                                                                                                                                                       • The pages were disorganized and often mislead the
                                                                                                Potential Lead                                             user about the intent of the page. In addition, there
                                                                 Fig 1: Use Case Diagram                                                                   was too much information being displayed on a page
  2. Existing Functionalities
                                                                                                                                                           which often overwhelmed the user.
   The currently used functionalities in the order of relevance
                                                                                                                                                       • The colors and the general layout of the page made it
are as mentioned below:
                                                                                                                                                           incoherent for the user.
                                                                                                                                                       • Besides, the pages were resolution dependent and
             •             The user interface is designed to facilitate
                                                                                                                                                           hence changes in resolution produced bizarre
                           assimilation and display of lead information in
                           various stages-referred as “pools”. Each pool display
                                                                                                                                                       • With respect to code design, there were far too many
                           provides flexibility of navigation to view the lead
                                                                                                                                                           pages performing redundant functionality.          The
                           data pertaining to other pools.
                                                                                                                                                           “modular approach” to design was never applied
             •             This application also includes a provision for
                                                                                                                                                       • The code was often auto-generated and hence there
                           automated dialing and keeping track of call details.
                                                                                                                                                           was excessive redundant code which was often not
                           The information pertaining to the agent or customer
                                                                                                                                                           indented, hard to read and no comments explaining
                           service representative who initiated the call, the
                                                                                                                                                           the functionality of each block of code.
                           receiver, as well as call back date and time for
                           following up are stored. If the lead did not approve of
                                                                                                                                                   4. Suggested Recommendations and Solutions
                           receiving any calls in future, the lead’s phone number
                                                                                                                                                    On careful examination of the system, the following
                           was tracked in a “Do Not Call List”.
                                                                                                                                                  amendments to the system are unavoidable to enhance the
             •              In addition, at any point in time, if the lead shows                                                                  performance of the application and to provide for scalability
                           lack of interest in purchasing the policy, his/her                                                                     and flexibility to perform updates:
                           details is not discarded but placed in a “Kiss-Off”
                                                                                                                                                       • The database was required to be redesigned in order
                                                                                                                                                            to incorporate “state-based” alongside an “entity-
             •             The other features of this system include assignment                                                                             based” approach to storage.       Furthermore the
                           of leads to agents. The primary assignment is carried                                                                            database had to be normalized to prevent redundant
                           out by the administrator.                                                                                                        information from being stored.
             •             The administrative modules include: the ability to
                           perform record searches for ease in retrieval of data

     •    In order to promote increased speed in loading of the      taken to minimize the number of auto generated keys unlike
          pages, views have to be created for tables. Views          the prior design. By incorporating the notion of “state-based”
          accelerate data access.                                    storage in addition to the “entity-based” form of storage, many
     •    The display information on a page can be reduced.          columns which were formerly void of data, were avoided.
          The user focus is thus maintained on the intent of the     This “state-based” storage was incorporated, by making the
          page instead of being carried away by large amounts        various “pools” as weak entities of the lead rather than making
          of irrelevant information.                                 them attributes as in the previous design. This helped to avoid
     •    The pages could be designed to be modular which            extraneous allocation of memory for the columns which did
          promotes re-usability and provides flexibility incase      not contain any data. This in turn helped to drastically reduce
          of change which is inevitable in such a dynamic            the time taken to query the data, thus enabling faster data
          system.                                                    access and retrieval which is fundamental to any web-based
     •    Redundant code can be eliminated and                       application.
          documentation in the form of comments provided to
          provide clear understanding of the working of that          2. Web Page Design
          module.                                                       The web pages were designed to incorporate a high degree
                                                                     of modularity and hence changes in the system could easily be
5. Technology Review                                                 made without having to change the entire code. This is one of
  The current application uses Active Server Pages to provide        the primary requirements of managing changes. Repetitive
a web- to- database interface. ASP is fitting in this application    code was avoided by use of “include functions”. For example,
primarily due to the ease and flexibility it provides. It works      the code providing database connectivity, provided through
with ODBC compliant databases and hence migration to                 ActiveX Data Objects(ADO), needed to be available on all
another database would not require changing the underlying           pages and hence was put in an .inc file which was included as
ASP interface. Incase the architecture of this system is             and when required. Changes to the location or data source of
updated to a multi-tier application, dedicated servers can be        the database would hence require changes only in the .inc file
made to run different sections of an application, which not          instead of every file. Thus configuration changes could easily
only provides load balancing but also attempts to eliminate a        be incorporated. In addition, session variables were used to
single point of failure. ASP also supports Active-X objects,         cache information across the pages. But since this system was
which is used extensively incase of the “call-module”.               highly dynamic, excessive use of session variables were
JavaScript embedded in ASP provides a powerful means of              avoided and reduced to a bare minimum. The functionality
performing client-side validation. ASP provides the notion of        was implemented using Visual Basic while JavaScript was
a “Session Object” which enables to preserve the state of the        used for client side validation. The “code logic” was separated
system pertaining to a user through the entire course of             form the html display as much as possible which made the
interaction with the system. The choice of SQL Server for            code more readable and changes to the logic could thus be
database is based on the fact that it works well with the            incorporated with ease. Furthermore, a coding standard with
underlying Microsoft Operating System.            As mentioned       respect to variable declarations and function names were
above, it can be easily interfaced with ASP as well. It has also     followed that promoted peer programming, by imparting
proven to be robust in handling vast amounts of data which is        uniformity. Last but not the least, documentation describing
inevitable in this application. It can thus be inferred that the     the functionality of each sizeable module was provided.
choice of technology is appropriate for this application.            Indentation, also added to enhance the readability.
Redesign of the System                                                3. Deployment
   After gaining a complete understanding of the system and its         The application comprising of Active Server pages were
drawbacks, it was apparent that most of the flaws may be             hosted on the Internet Information Services (IIS) which
primarily attributed to the poorly designed database. The code       provides a highly reliable, manageable and scalable
in the Active Server Pages could also be restructured in order       application. All the .ASP files were organized into a folder
to improve the efficiency and effectiveness of modules of            called ASP and the images were organized in a separate folder.
functionality. The key considerations while redesigning this         The phone application used to perform automated dialing was
application are outlined below:                                      placed in another folder. In order to simulate a remote
                                                                     database for calculation of quotes, a JSP page hosted on
 1. Database Design                                                  TOMCAT server listened to requests and delivered quote
   From Fig 2 it can be clearly inferred that the data is void of    information to the application. In order for the application to
relations. This is clearly a poor design since; the basic purpose    be up and running, it is required that the SQL Server 2000
of storing data in databases rather than files is that it helps to   instance,    hosting the database, be started and the IIS be
maintain relationship between the various entities in the            running. In addition, TOMCAT which hosts the dummy
system. In the redesigned applications, care is taken to sustain     server application should also be up and running.
relations in the data which was made possible through an
effective analysis and creation of Entity-Relationship                3. Documentation
Diagrams. Furthermore, the databases were normalized in                 The existing system was devoid of any documentation. In
order to prevent storage of redundant information. Care was          the existing application, the workflow of the system was not

documented and thus it was necessary to depend on customer                       allocation. In the redesigned database however, this
interaction for gaining understanding of the system. Besides                     is taken care of by introducing weak entities, which is
this, the system did not contain any documentation on the                        created as the data progresses through the system.
design of the system or its implementation. The code lacked                 • Most of databases had auto generated key as the
comments as well. In order to gain a clear understanding of the                  primary key. This has been eliminated in the current
system constant interaction with the customer was required.                      design.
While analyzing the system, the entire flow of the system was               • The column names for the tables had non-descriptive
documented both orally and visually. User stories were                           names. This was corrected by providing intuitive
written which enabled to clearly identify the functionalities to                 names in the redesigned database
be implemented in the system. In addition it defined the post               • The database was normalized to make the design
conditions and preconditions for a given module and the                          more robust. Normalization prevents data storage
validations needed to maintain stability of the system. In order                 and validates relationships.
to model the data of the system, entity relationship diagrams               • In the redesigned database, views were added. Views
were generated.      It is to be noted that although, the                        are proven to increase the efficiency by reducing the
documentation generated was not a part of the customer                           data access time for frequently accessed data.
requirements, it was done in accordance with coding                     To summarize, the database design is more robust and scalable
standards, to provide a lucid view of the system.                       than the existing version. This is key to any application
                                                                        requiring efficient data access, storage and retrieval.
 4. Customer Interaction
   An on-site customer added flavor to this project. It gave an                                                                                                                                                                           PK
                                                                                                                                                                                                                                                   LE AD_DB AS E

                                                                                                                                                                                                                                               au to

                                                                                                                                                                                                                                               P HONE
                                                                                                                                                                                                                                               S TRE ET
                                                                                                                                                                                                                                               CIT Y

opportunity to experience the problems faced in the “industry”.
                                                                                                                                                                                                                                               S TA TE
                                                                                                                                                                                                                                               DAT A _S TA TUS
                                                                                                                                                                                                                                               A GENT_DA T A_ST A TUS
                                                                                                                                                                                                                                               CAMP A IGN_ID
                                                                                                                                                                                                                                               LE A D_SHE ET _ID
                                                                                                                                                                                                                                               M_NA ME
                                                                                                                                                                                                                                               M_A GE
                                                                                                                                                                                                                                               M_HE IGHT
                                                                                                                                                                                                                                               M_WE IGHT

Customer interaction was by far the most delicate task and the
                                                                                                                                                                                                                                               M_S MOKE
                                                                                                                                                                                                                                               M_S MOKE D_3Y RS
                                                                                                                                                                                                                                               M_CHOLES T ROL
                                                                                                                                                                                                                                               M_DIS E A SE
                                                                                                                                                                                                                                               M_B P
                                                                                                                                                                                                                                               M_B P _MEDICA T ION
                                                                                                                                                                                                                                               M_DE CLINE D_INS
                                                                                                                                                                                                                                               M_NOTE S
                                                                                                                                                                                                                                               M_F ACE _3
                                                                                                                                                                                                                                               M_F ACE _2

fact that the customer was non-technical and indecisive only
                                                                                                                                                                                                                                               M_F ACE _1
                                                                                                                                                                                                                                               M_Y RS_1
                                                                                                                                                                                                                                               M_Y RS_2
                                                                                                                                                                                                                                               M_Y RS_3
                                                                                                                                                                                                                                               M_COVE RA GE _T Y PE
                                                                                                                                                                                                                                               M_RA TE CLAS S
                                                                                                                                                                                                                                               S POK E _WITH
                                                                                                                                                                                                                                               QUOT E _S POUS E
                                                                                                                                                                                                                                               F_NA ME

made this more complex. In addition, the customer was unable
                                                                                                                                                                                                                                               F_HE IGHT
                                                                                                                                                                                                                                               F_WE IGHT
                                                                                                                                                                                                                                               F_SMOK E
                                                                                                                                                                                                                                               F_SMOK E D_3Y RS
                                                                                                                                                                                                                                               F_CHOLE S TROL
                                                                                                                                                                                                                                               F_DIS EA S E
                                                                                                                                                                                                                                               F_BP _ME DICA TION
                                                                                                                                                                                                                                               F_DE CLINE D_INS
                                                                                                                                                                                                                                               F_NOT E S

to prioritize the requirements. Careful analysis on the
                                                                                                                                                                                                                                               F_FA CE_1
                                                                                                                                                                                                                                               F_FA CE_2
                                                                                                                                                                                                                                               F_FA CE_3
                                                                                                                                                                                                                                               F_YRS _1
                                                                                                                                                                                                                                               F_YRS _2
                                                                                                                                                                                                                                               F_YRS _3
                                                                                                                                                                                                                                               F_COV E RA GE _T YP E
                                                                                                                                                                                                                                               F_RA T ECLA S S
                                                                                                                                                                                                                                               GIV E _QUOTE S _TO
                                                                                                                                                                                                                                               M_COMPA NY1

relevance of functionality to the customer, keeping in mind the
                                                                                                                                                                                                                                               M_COMPA NY2
                                                                                                                                                                                                                                               M_COMPA NY3
                                                                                                                                                                                                                                               M_COMPA NY4
                                                                                                                                                                                                                                               M_COMPA NY5
                                                                                                                                                                                                                                               M_COMPA NY6
                                                                                                                                                                                                                                               M_COMPA NY7
                                                                                                                                                                                                                                               M_COMPA NY8
                                                                                                                                                                                                                                               M_COMPA NY9
                                                                                                                                                                                                                                               M_COMPA NY10
                                                                                                                                                                                                                                               M_COMPA NY11
                                                                                                                                                                                                                                               M_COMPA NY12

deadlines, had to be made. Planning was done on the basis of
                                                                                                                                                                                                                                               M_RA TE CLAS S 1
                                                                                                                                                                                                                                               M_RA TE CLAS S 2
                                                                                                                                                                                                                                               M_RA TE CLAS S 3
                                                                                                                                                                                                                                               M_RA TE CLAS S 4
                                                                                                                                                                                                                                               M_RA TE CLAS S 5
                                                                                                                                                                                                                                               M_RA TE CLAS S 6
                                                                                                                                                                                                                                               M_RA TE CLAS S 7
                                                                                                                                                                                                                                               M_RA TE CLAS S 8
                                                                                                                                                                                                                                               M_RA TE CLAS S 9
                                                                                                                                                                                                                                               M_RA TE CLAS S 10

time and not on monetary basis, unlike in most real-time
                                                                                                                                                                                                                                               M_RA TE CLAS S 11
                                                                                                                                                                                                                                               M_RA TE CLAS S 12
                                                                                                                                                                                                                                               M_A NNUA L1
                                                                                                                                                                                                                                               M_A NNUA L2
                                                                                                                                                                                                                                               M_A NNUA L3
                                                                                                                                                                                                                                               M_A NNUA L4
                                                                                                                                                                                                                                               M_A NNUA L5
                                                                                                                                                                                                                                               M_A NNUA L6
                                                                                                                                                                                                                                               M_A NNUA L7
                                                                                                                                                                                                                                               M_A NNUA L8
                                                                                                                                                                                                                                               M_A NNUA L9

projects. Periodic updates were given to the customer and the
                                                                                                                                                                                                                                               M_A NNUA L10
                                                                                                                                                                                                                                               M_A NNUA L11
                                                                                                                                                                                                                                               M_A NNUA L12
                                                                                                                                                                                                                                               M_P A C1
                                                                                                                                                                                                                                               M_P A C2
                                                                                                                                                                                                                                               M_P A C3
                                                                                                                                                                                                                                               M_P A C4
                                                                                                                                                                                                                                               M_P A C5
                                                                                                                                                                                                                                               M_P A C6
                                                                                                                                                                                                                                               M_P A C7

customer was kept informed of the time estimate to complete a
                                                                                                                                                                                                                                               M_P A C8
                                                                                                                                                                                                                                               M_P A C9
                                                                                                                                                                                                                                               M_P A C10
                                                                                                                                                                                                                                               M_P A C11
                                                                                                                                                                                                                                               M_P A C12
                                                                                                                                                                                                                                               M_Y R1
                                                                                                                                                                                                                                               M_Y R2
                                                                                                                                                                                                                                               M_Y R3
                                                                                                                                                                                                                                               M_Y R4
                                                                                                                                                                                                                                               M_Y R5
                                                                                                                                                                                                                                               M_Y R6

task. Care was taken not to overwhelm the customer with
                                                                                                                                                                                                                                               M_Y R7
                                                                                                                                                                                                                                               M_Y R8
                                                                                                                                                                                                                                               M_Y R9
                                                                                                                                                                                                                                               M_Y R10
                                                                                                                                                                                                                                               M_Y R11
                                                                                                                                                                                                                                               M_Y R12
                                                                                                                                                                                                                                               M_F ACE 1
                                                                                                                                                                                                                                               M_F ACE 2
                                                                                                                                                                                                                                               M_F ACE 3
                                                                                                                                                                                                                                               M_F ACE 4

technicalities but at the same time adequate information on the
                                                                                                                                                                                                                                               M_F ACE 5
                                                                                                                                                                                                                                               M_F ACE 6
                                                                                                                                                                                                                                               M_F ACE 7
                                                                                                                                                                                                                                               M_F ACE 8
                                                                                                                                                                                                                                               M_F ACE 9
                                                                                                                                                                                                                                               M_F ACE 10
                                                                                                                                                                                                                                               M_F ACE 11
                                                                                                                                                                                                                                               M_F ACE 12
                                                                                                                                                                                                                                               M_QUOTE _S E LE CT ED1
                                                                                                                                                                                                                                               M_QUOTE _S E LE CT ED2

consequences of a decision was explained to the customer.
                                                                                                                                                                                                                                               M_QUOTE _S E LE CT ED3
                                                                                                                                                                                                                                               M_QUOTE _S E LE CT ED4
                                                                                                                                                                                                                                               M_QUOTE _S E LE CT ED5
                                                                                                                                                                                                                                               M_QUOTE _S E LE CT ED6
                                                                                                                                                                                                                                               M_QUOTE _S E LE CT ED7
                                                                                                                                                                                                                                               M_QUOTE _S E LE CT ED8
                                                                                                                       QUOT E S2                                                                                                               M_QUOTE _S E LE CT ED9
                                                                                                                                                                                                                                               M_QUOTE _S E LE CT ED10
                                                                                                                                                                                                                                               M_COMPA NY_COMP E TE
                                                                                                                                                                                                                                               M_RA TE CLAS S _COMPE T E
                                                                                                                        ph on e                                                                                                                M_A NNUA L_COMPE T E
                                                                                                                        name                                                                                                                   M_P A C_COMP ET E
                                                                                                                        sex                                                                                                                    M_Y RS_COMP ET E
                                                                                                                        smoke                                                                                                                  M_COMPE T E_FA CE
                                                                                                                        dob                                                                                                                    M_P OLICY _T Y PE
                                                                                                                        ratecl ass                                                                                                             M_Y RS_LEF T
                                                                                                                        mode                                                                                                                   F_COMP A NY 1
                                                                                                                        rate                                                                                                                   F_COMP A NY 2
                                                                                                                        yrs                                                                                                                    F_COMP A NY 3
                                                                                                                        face                                                                                                                   F_COMP A NY 4
                                                                                                                        pac                                                                                                                    F_COMP A NY 5

                                                                                                                        ANNUAL                                                                                                                 F_COMP A NY 6
                                                                                                                        sel ected                                                                                                              F_COMP A NY 7
                                                                                                                        Age                                                                                                                    F_COMP A NY 8
                                                                                                                        Gurantee                                                                                                               F_COMP A NY 9
                                                                                                                        company1                                                                                                               F_COMP A NY 10
                                                                                                                        company2                                                                                                               F_COMP A NY 11
                                                                                                                        company3                                                                                                               F_COMP A NY 12
                                                                                                                        company4                                                                                                               F_RA T ECLA S S1
                                                                                                                        company5                                                                                                               F_RA T ECLA S S2
                                                                                                                        company6                                                                                                               F_RA T ECLA S S3
                                                                                                                        company7                                                                                                               F_RA T ECLA S S4
                                                                                                                        company8                                                                                                               F_RA T ECLA S S5
                                                                                A DMIN_DB AS E                          company9                                                                                                               F_RA T ECLA S S6
                                                                                                                        company10                                                                                                              F_RA T ECLA S S7
                                                                                                                        rate1                                                                                                                  F_RA T ECLA S S8
                                                                                                                        rate2                                                                                                                  F_RA T ECLA S S9
                                                                          au to                                         rate3                                                                                                                  F_RA T ECLA S S10
                                                                          P HONE                                        rate4                                                                                                                  F_RA T ECLA S S11

Comparison of the Database Design
                                                                          NA ME                                         rate5                                                                                                                  F_RA T ECLA S S12
                                                                          A GE                                          rate6                                                                                                                  F_ANNUAL1
                                                                          SEX                                           rate7                                                                                                                  F_ANNUAL2
                                                                          DOB                                           rate8                                                                                                                  F_ANNUAL3
                                                                          DT_OF _E X AM                                 rate9                                                                                                                  F_ANNUAL4
                                                                          T IME _OF _E XA M                             rate10                                                                                                                 F_ANNUAL5
                                                                          P LA CE _OF _E X AM                           transfer1              meddbas e                                                                                       F_ANNUAL6
                                                                          S TRE ET                                      transfer2                                                                                     tempT able               F_ANNUAL7
                                                                          CIT Y                                         transfer3                                                                                                              F_ANNUAL8
                                                                          S TA TE                      newdata          transfer4                                                                                                              F_ANNUAL9
                                                                          Z IP                                          transfer5                Col001                                                                                        F_ANNUAL10
                                                                          P HONE _RE S                                  transfer6                Col002                                                            PHONE                       F_ANNUAL11
                                                                          P HONE _OF F                                  transfer7                Col003                                                            NA ME                       F_ANNUAL12

  The difference in the design of the existing system and the
                                                                          P HONE _DA Y                  Col 001         transfer8                Col004                                                            ST REE T                    F_PA C1
                                                                          P HONE _CE LL                 Col 002         transfer9                Col005                                                            CITY                        F_PA C2
                                                                          E MAIL                        Col 003         transfer10               Col006                                                            ST A TE                     F_PA C3
                                                                          F AX                          Col 004         product1                 Col007                                                            ZIP                         F_PA C4
                                                                          COMPA NY                      Col 005         product2                 Col008                                                            DA T A_ST AT US             F_PA C5
                                                                          F ACE                         Col 006         product3                 Col009                                                            AS S IGNME NT _DA TE        F_PA C6
                                                                          P REMIUM                      Col 007         product4                 Col010                                                            CS R_NAME                   F_PA C7
                                                                          MODE                          Col 008         product5                 Col011                                                            DA T A_ID                   F_PA C8
                                                                          CE LL                         Col 009         product6                 Col012                                                            LA ST _DIA LE D             F_PA C9
                                                                          RE PLA CE MENT                Col 010         product7                 Col013                                                            CA MP AIGN_ID               F_PA C10

redesigned system is apparent from the figures shown below.
                                                                                                                                                                      T rialA pp
                                                                          K IT _S E NT                  Col 011         product8                 Col014                                                            TIMES _DIALED               F_PA C11
                                                                          DT_KIT_SE NT                  Col 012         product9                 Col015                                                            AGE NT_ID                   F_PA C12
                                                                          ME D_ORD                      Col 013         product10                Col016                                                            vendor_i d                  F_YRS 1
                                                                          DT_ME D_ORD                   Col 014         auto                     Col017            Phone                                           Typeofdata                  F_YRS 2
                                                                          ME D_CONF                                                              Col018            M_Decl ined_i ns                                SCF                         F_YRS 3
                                                                          DT_ME D_CONF                                                           Col019            m_Desease                                       CB Date                     F_YRS 4
                                                                          F AX _CONF                                                             Col020            M_pendi ng_app                                  CB P urpos e                F_YRS 5
                                                                          DT_FA X_CONF                                                           Col021            M_DUI                                           CB T ime                    F_YRS 6
                                                                          DT_ME D_COMPLET E                                                      Col022            M_A lcohal                                      Purc has eDate              F_YRS 7
                                                                          ME D_RCV D                                                             Col023            M_US_Ci ti zen                                  C                           F_YRS 8

They can be briefly outlined as follows:
                                                                          DT_ME D_RCVD                                                           Col024            M_travel                                                                    F_YRS 9
                                                                          A P P_RCV D                                                            Col025            m_haz ar_oc cup                                                             F_YRS 10
                                                                          DT_AP P _RCV D                                                         Col026            m_Occupation                                                                F_YRS 11
                                                                          A GENT _DAT A _S TA TUS                                                Col027            m_income                                                                    F_YRS 12
                                                                          CA MP A IGN_ID                                                         Col028            m_haz ar_sport                                                              F_FA CE1
                                                                          A GENT _ID                                   A GE NT _DBA S E          Col029            m_B orn                                                                     F_FA CE2
                                                                          A DMIN_REP _ID               QUOT ES                                   Col030            m_cov erage                                                                 F_FA CE3
                                                                          DT_ADMINME D_P ROCE SS E D                                             Col031            m_B enefi ci ary                                                            F_FA CE4
                                                                          DT_SA LE _ME D                                                         Col032            m_day _ph                                                                   F_FA CE5

    • The existing database design can be stated as “non-
                                                                          A P P_COMP LE TE                              AGE NT_ID                Col033            m_fax                                                                       F_FA CE6
                                                                          DT_AP P _COMPLET E            phone           A_NAME                   Col034            m_E mail                                        DAT ADE TA ILS              F_FA CE7
                                                                          DT_AP P _P ROCE SS E D        name            A_EMA IL                 Col035            m_repl ace                                                                  F_FA CE8
                                                                          A P P_RE P                    sex             A_US E RNAME             Col036            m_Company                                                                   F_FA CE9
                                                                          A P S_ORDE RE D               smoke           A_PAS S WORD             Col037            m_amount                                                                    F_FA CE10
                                                                          DT_AP S _ORDE RED             dob             A_US E RTY PE            Col038            m_P ayment                                      DAT A_ID                    F_FA CE11
                                                                          A P S_RCV D                   ratecl as s     CA MP AIGN_ID            Col039            M_Co                                            V_ID                        F_FA CE12
                                                                          DT_AP S _RCV D                mode            SA LA RY                 Col040            M_Clas s                                        DA T AT YP E                F_QUOT E_SE LE CTE D1
                                                                          DT_POLICY _IS S UED           company         JOIN_DT                  Col041            M_Face                                          DT _P URCHAS E D            F_QUOT E_SE LE CTE D2
                                                                          DT_POLICY _RCV D              rate                                                       M_Y rs                                          DT _S TA RTE D              F_QUOT E_SE LE CTE D3
                                                                          DT_UR_P ROCE S SE D           yrs                                                        M_P re                                          DT _E NDE D                 F_QUOT E_SE LE CTE D4

         relational”, that is to say, the entities are not related to
                                                                          DT_PLCY _P ROCE S SE D        face                                                       f_Decli ned_i ns                                RE ORDE R                   F_QUOT E_SE LE CTE D5
                                                                          P LCY_RE P                    pac                                                        f_Deseas e                                      DE S CRIP TION              F_QUOT E_SE LE CTE D6
                                                                          UR_RE P                       A NNUA L                                                   f_pending_app                                   COMME NTS                   F_QUOT E_SE LE CTE D7
                                                                          DT_MA ILE D_CLIE NT           selected                                                   f_DUI                                           PRICE                       F_QUOT E_SE LE CTE D8
                                                                          DT_FOLLOW UP1                                                                            f_Al cohal                                                                  F_QUOT E_SE LE CTE D9
                                                                          DT_FOLLOW UP2                                                                            f_US _Ci ti zen                                                             F_QUOT E_SE LE CTE D10
                                                                          DT_FOLLOW UP3                                P ool _A rangements                         f_trav el                                                                   F_COMP A NY _COMP ET E
                                                                          DT_VE RBA L_AP P ROV A L                                                                 f_hazar_occ up                                                              F_RA T ECLA S S_COMP E TE
                                                                          DT_CHE CK _RCV D                                                                         f_Oc cupati on                                                              F_ANNUAL_COMP E TE
                                                                          A MT_RCV D                                                                               f_inc ome                                                                   F_PA C_COMPE T E

         each other. This is not an effective design, in the
                                                                          DT_MA ILE D_HO                                id                     CAMP A IGNCSR       f_hazar_sport                                                               F_YRS _COMPE T E
                                                                          P OLICY _NO                                   username                                   f_Born                                                                      F_FA CE_COMP E TE
                                                                          DT_PA ID_PROCES S ED                          Pool                                       f_coverage                                                                  F_POLICY TY P E_COMP E TE
                                                                          P A ID_RE P                                   StrFi l ter                                f_Benefic iary                                    F_namedbas e              F_YRS LE FT _COMPE T E
                                                                          DT_PA Y MENT _RECV D                          StrS ort                 CA MP _ID         f_day_ph                                                                    QUOT E _NOTE S
                                                                          A MT_P A ID                                   arangement_name          US E RNA ME       f_fax                                                                       CB
                                                                          NOTE S                                                                                   f_Emai l                                                                    CBDA TE
                                                                          CB DA TE                     QUOTE S 1                                                   f_repl ace                                      PHONE                       DECISIONMAK E R
                                                                          CB TIME                                                                                  f_Company                                       NA ME                       LE A DDA TE
                                                                          MOOD                                                                                     f_amount                                        ST REE T                    OCCUP AT ION

         sense that it tends to prevent scalability of the
                                                                          F OLLOWUP                                                                                f_Payment                                       CITY                        HOBB IES
                                                                          chil drens_names              p ho ne                                                    f_Co                                            ST A TE                     NUMB E R_OF_CHILDRE N
                                                                          DA TA _S TA TUS               name                                                       f_Cl ass                                        ZIP                         CHILDRENS _NAME S
                                                                          F OLLOWUP _REP                sex                                                        f_Fac e                                         DA T A_ST AT US             A GE_CHA NGE
                                                                          DT_FOLLOW UP                  smoke          CA MP AIGN_DBA S E                          f_Yrs                                           DA T A_ID                   P RE MIUM_DUE
                                                                          DT_FOLLOW UP_COMP             dob                                                        f_Pre                                           LA ST _DIA LE D             E ST _P remium
                                                                          CONS E RVE _RE P              ratecl as s                                                m_pl ace                                        AGE NT_NA ME                P RE MIUM
                                                                          DT_CONS ERV E                 mode                                                       f_place                                         TY P EOF DA T A             A GENT_ID
                                                                          DT_MA ILE D_HO_pd             rate            CAMP AIGN_ID            F MPQUOTE          m_E xam_Date                                    VE NDOR_ID                  DAT A _ID

         application. In the redesigned database, all entities
                                                                          DT_AP P ROV E D               yrs             CA MP _S TA RTDA TE                        f_Ex am_Date                                    TIMES _DIALED               A GENT_CS R_ID
                                                                          ME D_RE P                     face            CA MP _E NDDA TE                           m_E xam_ti me                                   CA MP AIGN_ID               MOOD
                                                                          DT_BLOOD_RCV D                pac             CA MP _DES C                               f_Ex am_time                                    CS R_NAME                   QUOT E DA T E
                                                                          P RODUCT                      A NNUA L        CA MP _INS T             PHONE             m_wife                                          CS R_CURRE NT _LOC          P RIORIT Y
                                                                          DT_CO_RCV                     selected        CA MP _S CRIP T          NA ME             f_wi fe                                         AS S IGNME NT _DA TE        A SS IGN
                                                                          A MT_CO_RCV                   A ge            LE ADS HEE T_ID          AGE                                                                                           M_GIVE _QUOTE _T O
                                                                          CO_P ROJ                      Gurantee        CA MP _NAME              SE X                                                                                          F_GIV E _QUOT E _TO
                                                                          A S SIGN                      company 1       acti ve                  SMOK E                                                                                        NEW RE P LA CE
                                                                          DT_AS S IGN                   company 2       di al ingti me           DOB                                                                                           F_NE WRE P LA CE
                                                                          A GENT _A SS IGN_ID           company 3       di al inglocati on       RA T ECLA S S                                                                                 ME D_RE P

         are related and the “pools” are treated as weak
                                                                          NS _Rep                       company 4       creati ondt              MODE                                                                                          DAT E _S ALE _ME DICA L
                                                                          Overri des                    company 5                                YRS                                                                                           TIME _S ALE_MEDICA L
                                                                          ORDE R_ME D_RE P              company 6                                COMP A NY 1                                                                                   M_E X AM_DT
                                                                          K IT _RE P                    company 7                                COMP A NY 2                                                                                   M_E X AM_T IME
                                                                          DNC_RE P                      company 8                                COMP A NY 3                                                                                   F_EX A M_DT
                                                                          RE P                          company 9                                COMP A NY 4     DIA LHIS T ORY LE A D_DB A S E                    LOGIN_DB A SE               F_EX A M_TIME
                                                                          IP                            company 10                               COMP A NY 5                                                                                   M_P LA CE
                                                                          RD                            rate1                                    COMP A NY 6                                                                                   F_PLACE
                                                                          A DMIN_REP                    rate2                                    COMP A NY 7                                                                                   M_OF F_P H
                                                                          HO_RCV D                      rate3                                    COMP A NY 8       DIAL_L E AD_ID                                  LOGIN_ID                    F_OFF _P H

         entities. This enables additional “pools” to be added
                                                                          DNC_date                      rate4                                    COMP A NY 9       PHONE                                           LOGIN_DT TIME               F_DA Y _P H
                                                                          DT_ME D_A P RV                rate5                                    COMP A NY 10      NAME                                            AGE NT_ID                   M_DA Y_PH
                                                                          ME D_AP RV _REP               rate6             NA ME _DB A SE         RA T E1           DAT A_S T AT US                                 LOGOUT _DTT IME             M_ME D_NOT E S
                                                                                                        rate7                                    RA T E2           AGE NT_DATA_S T AT US                                                       F_MED_NOTE S
                                                                                                        rate8                                    RA T E3           AGE NT_ID                                                                   cbti me
                                                                                                        rate9                                    RA T E4           DAT A_ID                                                                    prequotedate
                                                                                                        rate10          PHONE                    RA T E5           CA MP AGIN_ID                                                               K ODA TE
                                                                                                        transfer1       NA ME                    RA T E6           Di al Date                                                                  K OTIME
                                                                                                        transfer2       ST REE T                 RA T E7           Contacted                                                                   M_S MOKE _T IME
                                                                                                        transfer3       CIT Y                    RA T E8                                                                                       F_SMOK E _TIME

         with ease without having to modify any of the
                                                                                                        transfer4       ST A TE                  RA T E9                                                                                       CSR_NA ME
                                                                                                        transfer5       ZIP                      RA T E10                                                                                      FOLLOWUP
                                                                                                        transfer6       DA T A_ST A TUS          yr1                                                                                           Las tCal l
                                                                                                        transfer7       AS S IGNME NT _DA TE     yr2                                                                                           Dt_As sign
                                                                        DIALHIS TORY _NA ME _DB AS E    transfer8       CS R_NA ME               yr3                                                                                           A DS HIS T ORY
                                                                                                        transfer9       DA T A_ID                yr4                                                                                           S CHE DULE
                                                                                                        transfer10      LA ST _DIALED            yr5                                              US ERMA S TE R                               DT_F OLLOW UP
                                                                                                        product1        CA MP AIGN_ID            face1                                                                                         DT_F OLLOW UP _COMP         A ss ignHi storyLead_DBase
                                                                          DIAL _NAME _DBAS E _ID        product2        TIMES _DIA LE D          face2                                                                                         MODE
                                                                          P HONE                        product3        AGE NT_ID                face3                                                                                         Quote_Rep

         existing databases.
                                                                          NA ME                         product4        vendor_i d               face4                                            Usern ame                                    NS_Rep
                                                                          DA TA _S TA TUS               product5        Typeofdata               face5            NOTE S                          P ASS WORD                                   Las tContact                  Campaign_ID
                                                                          A GENT _DAT A _S TA TUS       product6        SCF                      pac 1                                            F irstname                                   Las tContactT i me            A ssi gn_ID
                                                                          A GENT _ID                    product7        CB Date                  pac 2                                            Lastname                                     NumberOfContacts              P hone
                                                                          CA MP A IGN_ID                product8        CB P urpose              pac 3                                            E mai lA ddress                              Cal lP urpose                 A gent_id
                                                                          di al date                    product9        CB T ime                 pac 4             PHONE                          Usertype                                     DNC_RE P                      Dt_ass igned
                                                                          contacted                     product10       Purchas eDate            pac 5             NOT ES                         CS R_Current_Loc                             DNC_Date                      dt_Unassi gned

    • Due to the existing design, a majority of the fields
         will be empty at the initial phases of the data flow.
         Incase of vast amounts of data, this could lead to
         decrease in performance due to wasted memory
                                                                                                                      Fig 2: Current Application Database Design


                                                                                                                                           PK usrName


        Call                   Campaign                                                                                                                                                                               Lead                                                                                                                                      CSRMeta

PK,U1 callID               PK,U1 cmpgnID                                                                                                                                                                       PK     ldPhn                                                                                                                              PK,FK1 CSRID
PK,FK1 callerID            PK,FK1 adminID                                                                                                                                                                      PK     ldNmFrst
                                                                                                                                                                                                               PK     ldNmLst                                                                                                                                   csrCurrentLocation
        callDt                    cmpgnName
        callTimeStart             cmpgnDtStart                                                                                                                                                                 U1 ldID
        callTimeEnd               cmpgnDtEnd                                                                                                                                                                   FK1 agentID
        callDuration                                                                                                                                                                                           FK2 csrID
        ldPhn                                                                                                                                                                                                      ldSex
        callReceiver                                                                                                                                                                                               ldSpouseID
        callPurpose                                                                                                                                                                                                ldOffSt
        callResult                                                                                                                                                                                                 ldOffCity
                                                                      AgentCamp                                                                                                                                    ldOffZip
                                                                 PK,FK1 campaignID                                                                                                                                 ldResSt
                                                                 PK,FK2 agentID                                                                                                                                    ldResCity
      CallBack                                                                                                                                                                                                     ldResState
        callbackID                                                                                                                                                                                                 ldPurpose
      callbackDt                                                                                                                                                                                                   ldDtAssign
      ldID                                                                                                                                                                                                         ldDtCreation
      ldStatus                                                                                                                                                                                                     ldOffPhn
  FK1 callID

                                                                             PolicyDelForm                  TrialApp                KO                         PreQuote                  SelectedQuote                UWForm               PaidForm               DNC               QUOTE            LeadMeta                  MedForm

                                                                        PK,FK1 ldID                   PK     trialAppID             koID                  PK,FK1 ldID                PK     selectionID       PK,FK1 ldID               PK     paidID               dncID      PK    quoteID    PK,FK1 ldID              PK     medFormID
                                                                        PK     DTMailed               PK,FK1 ldID                                         PK     quoteAmnt           PK,FK1 leadID            PK     uwID               PK,FK1 ldID                                             PK     ldDOB             PK,FK1 ldID
                                                                                                                                FK1 ldID                  PK     years                                                                                             phnNo       FK1 leadID
                                                                                  followUpDT1                 dtExam                ldPhnNo                                                      cmpnyName              APSOrderDT             Product         FK1 ldID                                ldWght                   medOrdBy
                                                                                  followUpDT2                 examPlace                                           serialNo                       numberYrs              APSRecdDT              comission           entryDt                             ldHt                     kitSentBy
                                                                                  verbalApprovDT              declInsur                                                                          amntMnth               DTtoHO                 Notes                                                   isSmoke                  DTKitSent
                                                                                  chkRecdDT                   disease                                                                            amntYear               DTPlcyRecd                                                                     smoke3                   DTMedOrd
                                                                                  mailedToHODT                pendingApp                                                                         rateClass              DTPlcyIssue                                                                    cholestrolLevel          DTMedConf
                                                                                  amntRecd                    DUI                                                                                faceValue              requirements                                             QuoteInfo             isCancer                 DTFaxRecd
                                                                                  plcyNo                      alcohol                                                                            mode                   Notes                                                                          isDiabetic               DTMedCompl
                                                                                  Notes                       citizen                                                                                                                                                        PK,FK1 quoteID            isHrtTrubl               DTMedRecd
                                                                                  followUpDT3                 foreignTravel                                                                                                                                                  PK     cmpnyName          bpLevel                  adminRep
                                                                                                              occupation           PK,FK1 campaignID                                                                                                                         PK     amntYrs            isBPMed                  Notes
                                                                                                              approxIncome         PK,FK2 ldID                                                                                                                               PK     amntMnth           isInsDecline             reSched_CB_RC
                                                                                                              dangerSports                                                                                                                                                   PK     rateClass          dsrdQteAmt1              DTReSched
                                                                                                              birthState                      phoneNo                                                                                                                                                  dsrdQteAmt2              DTAppRecd
                                                                                                              coverage             FK3        agentID                               AssignPool                         ApplnForm                                                                       dsrdQteAmt3              DTAppCompl
                                                                                                              beneficiary                                                                                                                                                                              dsrdYrs1
                                                                                                              dayphone                                                        PK     assignID                   PK     applnFormID                                                                     dsrdYrs2
                                                                                                              Fax                                                             PK,FK1 ldID                       PK,FK1 ldID                                                                            dsrdYrs3
                                                                                                              oldPlcyPaymnt                                                                                                                                                                            dsrdRateClass
                                                                                                              eMail                                                           FK2      assignBy                 FK2       agentID
                                                                                                              hazardOcc                                                       FK3      assignTo                           compDecided
                                                                                                              spouseBenefit                                                            assignDt                           premDecided
                                                                                                              oldCmpny                                                                 ldStatus                           modeDecided
                                                                                                              oldAmnt                                                                  lastAssignd                        Notes

                                                                                                                                Fig. 3 Database Design for Redesigned Application



                        User                       Assignment                                                                                                                                                                                                                                                                                   Record
                        Master.asp                 Menu.asp                                                                                     PoolMenu.asp                                                                                                                                                                                    Search.asp

                                                                  Lead                             Quote                  Schedule                      Application                                Under                         Policy                 Paid
                                    AssignRole.asp                                                                        Medical                                                                  Writing                                                                                                                                      Search
                                    (replaces 7 files)                                                                                                                                                                                                                                                                                          Engine.asp

                                                                                                         MainPools.asp (replaces 7 files from the older version)

                                    Leadsheetplus.asp                    Prequote                                               Schedule                                                                  Policy                                           Application                          Paid.asp                        Under
                                                                         Worksheet.asp                                          MedicalForm.asp                                                           Delivery.asp                                     Form.asp                                                             Writing.asp

               LeadSheetplus.asp                      PreQuote                    Schedule                                    Trial                            Order                                         Application                      Under                          Policy                       Paid.asp                   Cb.asp       Phone
                                                      WorkSheet.asp               Medical.asp                                 App.asp                          Medical.asp                                   Form.asp                         Writing.asp                    Delivery.asp                                                         Control.asp

                                                                                                                IIS                                                                                                                                                                                TOMCAT

                                             PreQuote                                              RunQuote.asp                                                              RedBar.asp                                                      Quick                                              Template.jsp
                                             WorkSheet.asp                                                                                                                                                                                   Quote.asp

                                                        Fig. 4 Code Design

                                                                      Although, Extreme Programming’s Practices of customer
  B. Relative Comparison of Code Implementation
                                                                      interaction along-side implementation was advocated, it was
The coding, being done in a team of two, required that                not an effortless task. The reasons why XP was not all that
coding standards be maintained. This helped to increase the           effective are listed below:
performance of the system as below:                                        • XP requires that the customer clearly identify his
    • The use of standard naming conventions enabled                            requirements. As mentioned above, the customer
         effective communication between the developers.                        was highly indecisive and hence failed to explicitly
         This enabled to drastically reduce development                         state his needs.
         time. The project was estimated by experts to be                  • The customer was not able to prioritize tasks. XP
         developed in four months at an average. After                          states that for the benefit of the coders, it is
         drastic changes in design and outlook the                              necessary that the customer clearly specifies order
         redesigned application took two months.                                of priority for tasks.
    • Number of lines of code is an effective metric for                   • Moreover, the deadlines set by the customer were
         measuring software quality. The number of lines                        highly unrealistic. This arose due to the fact that
         of code has drastically reduced by providing more                      the customer failed to understand the amount of
         “function oriented approach”.        To quote an                       work involved to perform a task.
         example, a file, lead.asp (475 lines) replaced 7             Thus it can be inferred that XP introduces a lot of flexibility
         files of 915 lines each. Thus                                to the customer, however, responding to sudden changes is
         (915*7)-475=5390 lines of extra code were                    often confusing, demoralizing and not a sound business
         prevalent.                                                   practice.
    • A component based architecture was used which
         enabled reusability. The existing version did not            In a nutshell, it can be concluded that a combination of
         support reusability. It was more procedural based.           Software’s Best Practices and Extreme Programming
         An instance of this would be implementation of               Practices provide effective results in terms of quality of
         “call-backs”. This was implemented as a module               software and communication[6].
         and was called when required and hence prevented
         redundancy of code.
    • Documentation in code helped to make the code                     D. Performance Comparison
         more readable and enhanced communication                       The graphs indicate that the performance of the
         between developers. The code was well structured             redesigned system is better than the current application.
         and indented and each developer found the other’s            The graphs are a plot of the sample of request versus the
         code self-explanatory.                                       round trip time taken for the request. From these
    Coding standards are thus an integral part of any                 parameters, two vital metrics can be obtained: throughput
    implementation that not only increases the quality of             and average time. Throughput is defined as the number of
    code in terms of functionality but also provides a means          requests handled by the server per minute. The analysis was
    of providing effective communication between                      conducted for one of the ASP files, lead.asp, and the results
    developers.                                                       were recorded as shown. The throughput was higher for the
                                                                      redesigned application and the average time was lower.
  C. Customer Interaction Using Extreme Programming
                                                                      This indicated a performance improvement.
   Extreme Programming focuses on developer interests’
along-side “rich customer communication”. Implementing
Extreme Programming’s Principles with regards to customer
interaction provided the following advantages:
     • The on-site customer was available to provide any
          clarifications we had with the system. Although
          the customer was not “geographically” on-site, it
          was insignificant due to the constant interaction
          and support provided.
     • Provided an opportunity to learn the “planning
          game”, advocated by XP. This involved assisting
          the customer in prioritizing tasks and setting
          realistic deadlines. In this way, milestones for                          Fig. 5 Graph for Redesigned Application
          deliverables were set.
     • Last but not least, this also imparted effective
          communication skills and stressed on a more
          person-to-person based communication.

                                                                the generation of quotes could be exposed as a web service
                                                                which could be accessed by other applications. In addition,
                                                                ASP .NET could be used as a framework for development
                                                                since this provides enhanced performance, scalability and
                                                                reliability as compared to the ASP Technology.

                                                                [1]   Alan Mendelevich. Ten Reasons to Develop in ASP. Available:
                                                                [2]   Alan Saldhana. Why should I use ASP. Available:
                                                                [3]   Len Cardinal, George Reilly (2000, April). 25+ ASP Tips to Improve
                                                                      Perfomance and Style. Available:
                                                                [4]   Anonymous. Relational Database Components. Available:
IV. FUTURE RECOMMENDATIONS                                      [5]   Harry Kohnemann. Software Development Best Practices. Available:
The application at present generates tentative quotes and for   [6]   Harry Kohnemann. Introduction to Extreme Programming.
the lead, and in the current application, this is done by             Available:
accessing a commercial database. The means by which
quotes are generated are relatively the same for most
insurance agency applications. As a part of the future work,

Shared By: