Design and Implementation of Database for Healthcare Information System A project work for master of science degree in Information Technology Southern New Hampshire University

Full text

1
Design and Implementation of Database
for
Healthcare Information System
A project work for master of science degree in
Information Technology
Southern New Hampshire University
Segun Abefe Adeshina

2
Table of Contents
Introduction ............................................................................................................................................... 3
Chapter 1 - Project Design ........................................................................................................................ 5
Problem statement ................................................................................................................................. 5
Stakeholder Needs: Application of Knowledge .................................................................................... 6
Project Communications ................................................................................................................... 6
Stakeholders ...................................................................................................................................... 6
Consultants ........................................................................................................................................ 6
Office managers ................................................................................................................................ 7
Reception .......................................................................................................................................... 7
Project management office (PMO) ................................................................................................... 7
Overview of the Design ........................................................................................................................ 9
Use case diagram .............................................................................................................................. 9
Stages of database design ................................................................................................................ 10
Data dictionary ................................................................................................................................ 14
Design challenges ........................................................................................................................... 16
Data entry ........................................................................................................................................ 17
Chapter 2 – Project Implementation ....................................................................................................... 20
Project Implementation Plan ............................................................................................................... 20
Description of Implementation ....................................................................................................... 20
Implementation Schedule ................................................................................................................ 22
Security and Control ....................................................................................................................... 23
Implementation Support .................................................................................................................. 24
Hardware, Software and Facility requirements............................................................................... 25
Database performance monitoring .................................................................................................. 25
Microsoft Baseline Security Analyzer (MBSA) ............................................................................. 26
Implementation requirements ......................................................................................................... 26
Summary of implementation process .............................................................................................. 27
Post implementation ........................................................................................................................ 28
Chapter 3 – Project Test Plan and Result ................................................................................................ 29
Test Plan .............................................................................................................................................. 29
Planning .......................................................................................................................................... 29
Test Design ...................................................................................................................................... 29
Test Implementation ........................................................................................................................ 30

3
Test Execution ................................................................................................................................. 30
Test Evaluation ................................................................................................................................ 31
Database system testing ...................................................................................................................... 31
Unit Testing ..................................................................................................................................... 31
Black box test execution ................................................................................................................. 31
Data integrity and validity............................................................................................................... 31
White box test execution ................................................................................................................. 32
User acceptance testing ................................................................................................................... 33
Summary of database test ............................................................................................................... 34
Chapter 4 - Recommendations for Enhancements .................................................................................. 36
Successes ............................................................................................................................................. 36
Process quality ................................................................................................................................ 37
Data quality ..................................................................................................................................... 37
Semantic quality .............................................................................................................................. 38
Behavior quality .............................................................................................................................. 38
Challenges ........................................................................................................................................... 39
Modification strategy .......................................................................................................................... 44
Actionable steps .............................................................................................................................. 50
Project management ............................................................................................................................ 53
Recommended future enhancements .................................................................................................. 56
Bibliography............................................................................................................................................ 59
Appendix ................................................................................................................................................. 62
Appendix A - Project Plan ................................................................................................................... 62
Appendix B - Project Resource Cost .................................................................................................. 63
Appendix C - Project Budget control .................................................................................................. 64
Appendix D - Case Study.................................................................................................................... 65
Appendix E - Database System Installation Guide ............................................................................. 66
Introduction

4
Database system is a critical element of enterprise information system. Database is the repository for
the data record use by information systems. Database system must follow a structured planning,
designing, testing and implementation methodology to produce reliable, efficient and secure
information system.
The database system project work is based on a case study of a mid-size healthcare facility.
The database system project work is a prototype database system for a healthcare information system.
The project work used Microsoft environment and tools for the development and testing operations.
Efforts were taken to create a model information system to test and verify the working of the database
system.
Implementing this database system with any enterprise information system will require a modification
of the project strategies and methodology.

5
Chapter 1 - Project Design
Problem statement
A very important element of any project is the identification of the problem to be solved by the
project work.
Manual healthcare record management system cannot provide necessary support for (1)
healthcare business growth, (2) increasing healthcare service consumer base, and (3) healthcare
regulatory requirements.
It is operationally and financially expensive to manage customer records using manual record
management system operations: (1) there is inadequate security for data and information, (2) customer
data and information may be exposed to unauthorized modification, (3) cost of maintaining manual
record management system can be prohibitive, (4) there is inadequate access control to data and (4) the
meaningful use requirements of the new regulation for health care provider will be difficult to complied
with using the current operations.
Monegain, B. (2009) reported that in a research conducted by Parkland Health & Hospital
System and University of Texas Southwestern Medical Center in Dallas involving health care providers
from 41 hospitals and on 167,233 patients, to evaluate the impact of information system over manual
operations in health care delivery, the following are some of the interpretations of the result of the
research: (1) manual operations is responsible for up to 15% in-patient hospital death due to inadequate
health record management, (2) manual operations was responsible for about 9% of death from heart
attack and 55% of death and complications associated with coronary artery because of human error in
prescription ordering, (3) manual processing is responsible for 16% increase in-patient hospitalization
cases because patient cases are not properly tracked, (4) cost of managing test result increase by $110,
cost of order entry increase by $132 and the cost of data management increase by $538.

6
Therefore, the outcome of the research points to the fact that manual record management system
affects has significant effect on the business operations and health care delivery system. This research
work provides a prototype of end-to-end design and implementation of healthcare database system for
integration with the enterprise healthcare information system using a mid-size healthcare facility as a
case study.
Stakeholder Needs: Application of Knowledge
Project Communications
Project communication is an important activity in the planning and execution of the database
system project. An effective communication method was used during the system analysis phase to
collect business and users’ requirements. The communication method used to collect user requirement
was interview. Stakeholders were identified and appropriate interview questions were designed for their
situation.
Stakeholders
The stakeholders are the employees of the organization who will be impacted by the new
database system and who will also decide how the database system will work for the organization.
They define the user requirements, they specify the business and system rules to meet their
requirements for adoption, they will be the people to test and certify the system as fit for the
organization. The stakeholders were engaged from the planning phase of the project and their inputs
provide the business requirement for the database system. The stakeholders for the project are
described below
Consultants
These are the top management. They are the decision maker for the organization. They are the
provider of the financial resources for the project. They are the sponsor of the project. They bear all the

7
risks of the project. They were provided with information on the progress of the project. They are
notified of any significant project risk. The information communicated to them was high level but it
kept them informed on cost, schedule and milestone. They use the information for decision making.
Office managers
They are the operations executives. They will interact with the database for line management
operations. They use the database to prepare report and to perform activity analysis. They need
information on the project status as often as possible. They need information on critical issues affecting
the project. They use this information to monitor project status.
Reception
They are the front desk officers. They will interact with the database system extensively for
their operations. For example, the front desk officer or the reception officer will use the database to
perform the job function of enrolling patient, scheduling appointments and preparing invoice. They
were engaged extensively from planning stage. Their acceptance of the system is very critical. They
need detailed information about the database system during the project. They need information on the
impact of the project on their work and how the new system will integrate in to their present work
Project management office (PMO)
The database system project is one aspect of the information system project of the organization.
The database project team interacted with other project team such as software developer and network
designer. The entire project work was managed by the PMO. Constant and regular information were
shared with the PMO to ensure smooth integration.
Some of the key issues for the project communication operations were (1) what project
information should be collected, (2) when should the information be collected, (3) where should the

8
information be stored, (4) who should be given which information, (5) when should the information be
provided. Table 1 summarizes the communication plan for the project
Information
content
Stakeholder
Information
timing
Communication
medium
Provider of
information
Information
storage
Information
usage
Milestone
Consultant
Every 2
weeks
Electronic mail
and paper print
out
PMO
Project
communication
folder
Making
decision
Report on
project status
Office
manager
Every week
Electronic mail
PMO
Corporate
intranet
Monitor
progress
Project issues
Office
managers
Every week
Internal
memorandum
PMO
Project
management
document folder
Issue
resolution
Escalation
reporting
Consultants
On demand
Project meeting
and printed paper
Project
manager
Project
management
document folder
Make decision
Change
management
document
Consultant,
office
manager,
reception
On demand
Internal
memorandum
PMO
Project
management
document folder
Configuration
and integration
management
Table 1 Project communication plan
The project communication plan in table 1 help keep the project works connected and help keep the
organization informed on the progress of the project work and it was a useful channel for integrating
ongoing feedback in to the project work

9
Overview of the Design
Use case diagram
A use case diagram provides a visual image of the flow of the functional requirement of a
system design, showing how and why choices were made. It shows the factors that affect the system
and the implication of the interactions. It also analyzes the system at a high level for user to easily
relate to the workings of the system (Smartdraw, n.d.) A use case diagram was used to describe the
behavior and working of the database system. It describes the functions and services perform by an
actor or actors and the outcome of the action. Figure 1 shows the use case diagram for the database
system design.

10
Fig 1 Use case diagram
The actors in this database project are the consultants, the patients and the reception officer. In the
database use case the patient make a request for service, the reception register the patient, the reception
add the patient to the appointment list by scheduling the patient for a consultant, the consultant receive
notification from the schedule list, the consultant provide the service to the patient, the consultant
prepare service bill and update the system, the reception track and check the service record and prepare
invoice bill payment.
Stages of database design
According to Washington State University (2008) database design has 4 stages (1) define the objective,
(2) design the data structure, (3) define the relationship and (4) define and implement business rules.
Define the objective: This is the phase where the work of the database is defined. It specifies the scope
of the database system. As stated earlier the objective of the database system is to ensure customer data
are properly collected, efficiently processed, securely stored and retrievable by authorized users for
operations. The database will make customer record available in real time for subsequent use to provide
services to customer. The database system will interface with a front-end GUI application to provide
user friendly platform for operations
Design the data structures (tables, fields):
The database conceptual design describes the entity that will make up the database system. The data
requirements of the entities are also identified and the candidate keys for possible relationship among
the entities are identified for the database design (WiseGeek. n.d.) Figure 2 is the conceptual design for

11
the database system. It shows the following database system entities: the consultant, the reception,
login, bill, billpayment, appointment, and patient.
Fig 2 Database system conceptual design
Each of the entity will be a table structure with the entity as the record. Each of the entity has
attributes which are the items in the lower part of each of the entity diagram. The attributes will collect
and hold the data which is the object of the database design and are responsible for the operations of
the database system.
Define the relationship
The logical design was developed from the conceptual design. The logical design extends and

12
establishes the relationship between the entities and organizes the conceptual design into a meaningful
design for the database system project (WiseGeek. (n.d.)
Fig 3 Database system logical design
Figure 3 is the logical design for the database system. It shows the relationships between the entities.
The following relationships are defined: consultant-appointment, consultant-login, consultant-bill,
reception-login,bill-billpayment, patient-appointment, patient-billpayment, reception-billpayment.

13
The interpretation of the relationship is defined based on business rule.
Define and implement business rules
Database business rules define the conditions that database entities must comply with. Business rule
reflect how the organization perform its business operations and which the database design must
conform with. Business rules define the characteristic of the data that will be manipulated by the
business operation to achieve the design objectives (eTutorial, n.d.) The following are the list of the
business rules for the database design:
A patient must register before the patient can be schedule for appointment
A patient must have received consultation before a bill can be generated
A patient can have zero, 1 or several bill payments
A patient can have zero, 1 or several appointments
A reception officer can be attached to 1 or several patients
A reception can process zero, 1 or several bill payments
A reception can have 1 and only 1 login
A consultant can attend to 1 or several appointments
A consultant can prepare 1 or several bills
A consultant can have 1 and only 1 login
A bill payment can be for 1 or several bills
An appointment can belong to 1 and only 1 patient

14
Data dictionary
Data dictionary is a collection of data and information about the entities in the conceptual
design. Data dictionary define additional business rules for the database design. The data and
information in data dictionary help in the implementation phase to establish the conditions that the
database must fulfill when recording data. Tables 2 - 8 show the data dictionary for each of the database
entities design shown in figure 3
Column Name
Description
Data Type
Length
Allow
Null
LoginID
User’s unique system
identification
Alphanumeric
10
No
ReceptionID
Reception unique identification
number
Number
12
No
ConsultantID
Consultant unique identification
number
Number
12
No
Table 2 Login table
Column Name
Description
Data Type
Length
Allow
Null
PatientID
Patient unique identification
number
Number
12
No
Name
Patient first and last name
Alphanumeric
15
No
Address
Patient home address
Alphanumeric
30
No
Phone no
Patient phone number
Number
10
Yes
Table 3 Patient table
Column Name
Description
Data Type
Length
Allow
Null
ReceptionID
Reception unique identification
number
Number
12
No
Name
Reception first and last name
Alphanumeric
15
No
Address
Reception home address
Alphanumeric
30
No
Phone no
Reception phone number
Number
10
No
Table 4 Reception table
Column Name
Description
Data Type
Length
Allow
Null
ConsultantID
Consultant unique identification
Number
12
No

15
number
Name
Reception first and last name
Alphanumeric
15
No
Address
Reception home address
Alphanumeric
30
No
Phone no
Reception phone number
Number
10
No
Table 5 Consultant table
Column Name
Description
Data Type
Length
Allow
Null
AppointmentID
Appointment unique
identification number
Number
12
No
ConsultantID
Consultant unique identification
number
Number
12
No
PatientID
Patient unique identification
number
Number
12
No
Date
Appointment date
Date
10
No
Time
Appointment time
Time
10
No
Table 6 Appointment table
Column Name
Description
Data Type
Length
Allow
Null
BillPaymentID
Bill unique identification
number
Number
12
No
PatientID
Patient unique identification
number
Number
12
No
BillAmount
Bill amount
Money
10
No
Description
Details of the bill payment
amount
Alphanumeric
50
No
Table 7 BillPayment table
Column Name
Description
Data Type
Length
Allow
Null
BillID
Bill unique identification
number
Number
12
No
ConsultantID
Consultant unique identification
number
Number
12
No
Amount
Bill amount
Money
10
No
Table 8 Bill table

16
Design success
The major successes recorded thus far in the design of the database system are the milestones
achieved thus far. The milestones are (1) The system requirement were completed and the project was
properly scoped and it was approved by the management, (2) Stakeholders were properly identified and
their need adequately documented which was used to developed the communication plan and the
communication plan was approved by the management, (3) The database design was completed and it
captured all the functionalities for the proposed database system, a use case was use to demonstrate the
working of the database system and the design was approved and (4) Hardware implementation
successes: Hard disk requirement of 6GB was met. Internet software requirement of internet explorer 7
or later was met; the explorer currently in use is IE9. .Net framework installed was 4.0 which is more
than the minimum requirements of 3.5
Design challenges
Some of the challenges encountered during the project implementation are described below
Operating system platform
The operating system platform currently in use by the organization is Linux operating system.
However, the database management system selected for the database system project is MSSQL Server
2012. MSSQL Server 2012 is not compatible with Linux operating system. This was a serious
challenge during the project. MSSQL Server 2012 is only compatible with Windows Vistas O/S or later
(Microsoft, n.d)
Network system challenges
Bandwidth requirement for the enterprise which will equally support database system was a challenge.
The following features were included in the MSSQL server 2012: (1) Business intelligence which
provide advance functionality for data analysis and presentation, it also allows user to define program

17
to manipulate data and (2) Always-On which provide business continuity functionality by mirroring the
database server. However, these functionalities require significant bandwidth size. The bandwidth size
in the organization is 5MB. During the implementation testing some delay was noticed in the network
communication.
Data entry
Data conversion was a big challenge. The implementation requires an extended period of data entry.
After the first stage of data entry, 55% of the data records contained errors. A second stage of data
cleaning up has to be done on the data entry which extended the project schedule. After the second
stage additional days were used for data sanitization for the data to be compliant with regulatory
requirements.
Develop a modification strategy for the design
To keep the project on track within the approved scope, budget and schedule some
modifications were introduced to the project design to fit in to the project work. The modifications
were excluded from the current design but are included in the master design and are recommended for
future implementation.
The initial plan was to integrate the database system to a payment system. However there were
2 major challenges that made it impossible to include the payment system in the project: (1) the
additional hardware requirement were not included in the budget plan and thus will require extra
budgetary approval which the organization is not ready to accommodate in the current financial year
operations and (2) the entire information system will have to be assessed and certified as comply with
PCIDSS by a qualified security assessor (QSA), this will require additional budgetary approval because
the organization must engage a QSA company and pay for the consulting service by the QSA.

18
An important element of a healthcare delivery database system is the health insurance provider.
Initially it was planned that the health insurance provider will be included in the current design.
However, the inability to engage this stakeholder group effectively due during requirement scoping
makes it impossible to have adequate requirement documentation for inclusion in the final design. The
design was modified to exclude the health insurance provider.
Figure 4 was the initial use case design for the database system which included the payment
subsystem and the health care insurance provider.
Fig 4 Database system use case design (Initial)
Figure 5 is the modified use case design for the database system which excluded the payment

19
subsystem and the health care insurance provider.
Fig 5 Database system use case design (Modified)

20
Chapter 2 – Project Implementation
Project Implementation Plan
The implementation plan is based on the implementation guidelines of the government of
Maryland (Maryland.gov, nd) and Project Management Document (Project ManagementDoc, n.d.)
Purpose
The implementation plan communicates the activities necessary for the installation of the
database system project. The plan describes how the database system will be made operational within
the information system infrastructure of the group. The purpose of the plan is to make sure that project
stakeholders understand the requirements and tasks involve in installing the database system
successfully. All implementation will be through authorized change request. All changes not fully
included in the plan will be implemented through separate change management request.
Description of Implementation
The database system will be implemented within the information system to replace the current
inadequate and unsecure manual data management and processing. The implementation plan is one of
the activities in the project management plan document.
The implementation plans take the database logical design and build a physical design with it. The
implementation starts with the preparation of the database server. The server will hold the database
management system. The database management system to be implemented is MSSQL server 2012. The
database server is verified that it meets the specification. The require specifications are 2.0GHz
processor speed, 64-bit x64 architecture, 512GB RAM for fast data fetching and processing and NTFS
file system. .Net framework 4.0 will be used for database engine, data replication and data services.

21
Windows 2012 operating system will be used with TCP/IP supported network software. 6GB is
required for the installation however for growth and scalability a higher disk space is recommended. A
DVD drive for the installations from disc. The installation will use Super-VGA 800x600 resolutions
monitor to have good visual for the MSSQL interface graphic (Microsoft, 2017)
After installation and configuration of the DBMS the test data will be inserted and loaded. The
current data will be cleaned-up, formatted and loaded into the database system. The database system is
tested for integrity and functionality. After testing and verification of the operations of the database
system, IT support staff will be trained on the operations of the database system.
After completing the test and technical training, the system will be operated in parallel with the
manual operations for 5 days to enable staff to gradually migrate to using the new database system and
to manage the risk of any failure.
Once the IT management approves the operations and the user acceptance requirements are
verified, the manual process will be discontinued and the new database system will be the only
operational system for database management and operations. Upon confirmation of the acceptance
requirement by users and management the implementation phase is deemed completed
Contact
The database system project is an integral part of the enterprise information system
implementation. The project involves integration with other enterprise architecture. The integrations are
database connectivity with the front-end application system, database interface with network platform
to send and receive data and communication, database system interact with the security system to
protect confidentiality and integrity of data and records. These multi levels implementation demands
interaction with the provider of other services. The implementation phase will comply with the

22
approved communication plan to ensure achievement of project objectives. The contacts for the project
are (1) front end application developer, (2) network system engineer, (3) system security engineer, (4)
system administrator and (5) solution architect
Major Activities
The database system project team developed list of major activities to be completed for the
successful implementation of the database. These activities are subset and within the project scope. The
major activities for the implementation of the database system project including the responsibilities are
as follows: (1) Completion of the database design, this will be done in conjunction with the IT
department, it involves building the conceptual design for the entities and their relationship, (2)
Completion of database testing, this will be done in conjunction with IT department and the user group.
Testing will be done on a test bed in test environment, (3) Completion of training for IT department,
this is the transfer of knowledge for the database management after the project work, (4) Confirmation
of database operation, this involve loading the data on the database and verification of process that it
meet requirement for data, performance and security, (5) Go live for the new database system, this is
making the database operation becoming the working tool for the organization, and finally (6)
Acceptance of the new database system by the user and integration into the working of the database
system as part of the enterprise information system.
Implementation Schedule
The two tasks in the implementation plan are change management and installation. Change
management process is developed for the database system project to manage and control the
implementation procedures to ensure that database system meet all project plan metrics. The change

23
management process actually begins with business and user requirement gathering; however the
implementation plan ensure the final installation of the system can be traced back to the user and
business requirements (University of North Carolina Greensboro, 2006) Installation process guide is
shown in appendix A. It involves configuration and installion of the database software on the database
server. All the specifications are fully contained in the installation guide.
Security and Control
The information security control environment is managed by the information security policy
and procedures. The organization’s security control operations comply with the requirement of health
information protection accountability act (HIPAA). The database system development and design
comply with the requirements of the policy and procedures. The policy and guidelines on information
security management will be enforced during the implementation of the database system. The policy
requires that database system must have access control; this will be implemented through
authentication and authorization controls using identification/password and role-based access system,
least privilege technology. The database will comply with the requirement that database system must
not interact directly with the internet, the database system will be segmented and placed behind a
firewall to control data access as shown in figure 1. Other security control requirements such as system
hardening, data validation and anti-virus system will be implemented at enterprise level. IT security
management will be engaged in the design, testing, and implementation stages of the database system
project. IT security management will be responsible for security and control operations of the database
system.
The database system will integrate with other information system infrastructure as shown in fig 6

24
Fig 6 System architecture for the database system
Implementation Support
The project management office (PMO) will be responsible for the project implementation.
However, the IT department will be engaged for implementation support. PMO will design, test and
implement the database system but will be guided by the inputs and feedbacks from IT department. The
IT department will provide the test environment and test data. The IT department will manage,
administer and validate the data migration process. IT department will be directly responsible for the
user acceptance test and user training on the new database system. PMO will coordinate the two
activities.

25
Hardware, Software and Facility requirements
The database system project will require new hardware and software for the implementation
process. A room facility will be dedicated and designated as the data center/server room where the
hardware and software for the database system will be kept and to provide physical security as required
by HIPPA. The only hardware requirements are two enterprise database servers. The software
requirements for the implementation are Windows 2012 network operating system which will be
installed on the database server for managing the hardware system and the database system and
MSSQL server 2012 database management system.
Database performance monitoring
IT department will be responsible for the database performance monitoring. The reason for the
performance monitoring is to ensure that the database system continue to meet the system performance
objectives. To help achieve this objective, monitoring functionality will be added and enabled on the
database system environment. The monitoring functionality will collect performance data in real-time
and generate operations and management report for review and decision making. In the event that
database system performance monitoring shows that performance has reduced below the defined and
acceptable threshold, the issue will be escalated to management who will approve an investigation to
determine the root cause and identify corrective measures to return to normal performance. This is part
of the organizational policy for incident resolution.

26
Microsoft Baseline Security Analyzer (MBSA)
The platform for the database system will be Microsoft systems both server and clients. MBSA
is a security vulnerability scanner for conducting risk assessment on Microsoft-based computers,
computer systems, networks or applications for vulnerability and weaknesses. MBSA scan and audit
systems to identify missing security patches, weak passwords, weak web server configurations, weak
internet explorer configurations and other Microsoft Windows components (Jamaal Green and Angela
Richardson, 2011) MBSA will be used to review and evalute vulneability in the database server and
system. it will be used to verify network configuration in the database server network operating system
and the password system. This operation will provide assurance that the database server and the
database system installed on it are secured.
Implementation requirements
The implementation requirements for the database system project are listed below
Hardware/Software:
Enterprise database for server for testing operation
Enterprise database server for production operations
Microsoft SQL Server 2012
Personnel:
IT manager – PMO team member
IT security officer – PMO team member
User group representative – PMO team member
Facilities:
A dedicated room for project administration and operations

27
Summary of implementation process
Figure 7 is the summary of the implementation process. The process is divided into 4 phases the
planning stage, the analysis stage, the conversion stage and the migration stage. The planning stage
involve identification of implementation requirements including the hardware, software and facility
needed for the implementation, the implementation team will also be identified and their
responsibilities will be communicated to them. The analysis stage involves identification of current
location of the data to be migrated, this is the manual records and the new location for the data which is
the database system. Data cleansing will be performed in the stage to make the data conform to the
system requirements such as data format and data type. The data test plan will also be created in the
stage. Data conversion stage involve preparing the conversion environment, this will involve
transcribing data into an excel sheet, the data in the excel document will be validated for accuracy and
integrity. Migration stage involves moving the data into the system. The functionality in the new
database system to upload the data from excel will be utilized in addition to manual insertion for data
input, the data will be tested and when the system integrity is achieved the implementation is deemed
completed.

28
Fig 7 Implementation process diagram (Adapted from Key Management Group)
Post implementation
Upon completion of the database system implementation, implementation will be verified.
Access to database by the users will be verified, user will be required to log in from the front-end
application and perform their usual job function. This step will verify that user can access the system
for their daily task and that proper privileges were available for work. IT manager will be required to
log in to the database both from the front-end application and directly to the database. This is to verify
that IT manager can perform their daily system operations and that they can carry out database
maintenance including troubleshooting, querying and tuning for performance enhancement and
support. All the verification activities will be documented and agree to by all parties. The output
document will be part of the final documentation for implementation completion and confirmation that
implementation objectives were successfully met.

29
Chapter 3 – Project Test Plan and Result
Test Plan
The test procedures described in this section is based on the database testing procedures
developed by Guru (n.d) and Key Management Group (n.d)
Planning
During the planning for database test the objective of the test will be defined. Some of the
objectives of the test are (1) the system must provide security and control for user access, (2) the
system must complete operations within a define time and (3 the system must process related
operations. Risk associated with the test plan will be evaluated and documented. Some of the risk
assessment for the test are (1) probability that the test will fail, (2) probability that test data will not
meet test requirements and (3) probability that the system will not respond to the test and thus halt the
test altogether. The test plan will identify test resources. Test resources are the people that will execute
and manage the test operations, information system that will be used to conduct the test. Project
management office will be responsible for the test and test environment which replicate the database
system will be used for the test. Test schedule will be developed and communicated with the
stakeholders as part of the communication plan (Tutorialspoint, n.d)
Test Design
Database system test will be designed based n the organization workload operations. Current
workload will be analyzed; this will be the basis of workload schedule. An online database test data
generator will used to create the test data and will be loaded onto the database system for the test. Some
of the test cases that will be conducted are (1) Performance test, performance test will evaluate time
taken to access data in a row in a table, time taken to execute queries and time taken to execute queries
that combine different tables, (2) Data quality test, this test will evaluate whether data records are

30
inserted in the correct field, row and table, (3) Structural test, structural test will evaluate whether
tables are properly created and indexed within the database, (4) Functional test, this test will evaluate
the correct operations of the stored procedures and trigger. Stored procedures and triggers are
embedded code in the database that execute when certain conditions occurs, (5) Access control and
security test, this test will evaluate database security control features including system logging, data
access restriction, privilege assignment and (6) Relationship test, this test will evaluate whether the
referential integrity is operational and that is correct. It will test primary key operations and foreign key
operations. It will evaluate cascading operations. Test script will be created for the design test and
review by the stakeholders (University of Houston–Clear Lake, 2001)
Test Implementation
During implementation of the test the test design will be executed. The test environment will be
set up and the test data will be uploaded on the database system. Special case test will be implemented
in addition with the planned test. Test scripts will be loaded along with the test data.
Test Execution
During test execution the planned test will be run on the database system and the output will be
monitored. The system output with correct data and script will be monitored and recorded. The system
will also be loaded with incorrect data and script and the output will be documented. Time of execution
for all tests will be documented. How system handle execution failure will be monitored and
documented. The result of the execution will be verified and exception handling will be investigated

31
Test Evaluation
The test will be concluded with test evaluation operations. The test coverage will be reviewed.
The data and script coverage will be reviewed. Program codes used in functionality test will be
reviewed. The success or failure of the test will be evaluated and reviewed. And finally test evaluation
report will be developed and distributed for final review using the communication plan platform
Database system testing
Unit Testing
Unit test will focus primarily on the database as a stand-alone system and both black box and
white box testing will be conducted on the database system
Black box test execution
The objective of the black box testing is to verify how the database behaves when interacting
with the user and user data. User interacts with the database through the data record that is feed into the
database and the data receive as output from the database.
Data integrity and validity
The database system will store healthcare center data, consultant data, patient data, employee
data, demographic data and system control data. The data integrity test will check the correctness of the
stored data. It will evaluate (1) data creation, the INSERT command will be used to insert data in
several tables and the table will be checked with SELECT command to verify they were actually
inserted , (2) data modification, DELETE command will be applied on the database and SELECT
command will be applied to verify the deleted records were actually deleted, UPDATE command will
be applied on the database and SELECT command will be used to verified the updated records,
ALTER command will be applied to the database table and SELECT command will be used to verify

32
the altered table structure , (3) data deletion, (4) if blank or null value can be added to or deleted from
the database in line with the functional design, INSERT command will be applied but allowing a null
value in the argument and the response of the database will be monitored (5) if data is stored if it is
actually stored fully, (6) how database handle inconsistence data in formatted field, INSERT command
will be applied to the database with correct value for the fields and wrong data type, INSERT command
will be applied to the database table with first column field value substituted for 2nd column and then
and 3rd column (7) how database combine or join data from different tables, JOIN command will be
applied to (i) 2 existing tables, (ii) one existing table and a non-existing table, (iii) 2 non-existing
tables; JOIN command will be applied to 2 existing tables but with a non-existing field in one of the
table and (8) how database handle input data error, INSERT command will be applied to the database
with special characters as the entry values (Microsoft, 2000)
White box test execution
Stored procedures and views and triggers testing
Database views and stored procedures are program codes embedded in database system for
routine database tasks. Views and Stored procedures may be executed by users or designed to be
executed by an application or by itself under specified conditions. Triggers and Views and Stored
procedures testing will verify (1) if codes are complete and well structure for the function it was
designed to perform, (2) if there is any incomplete path in the code, (3) if code flow line and structure
reflect the function requirements, (4) if the output is what is expected, and (5) if the code structure
syntax is correct, (6) handling of error and execution exception, (7) the loop control in the code, (8) if
stored procedure code impacted the tables, (9) if triggers update database correctly, (10) if TRIM
operation is properly included to check data input variation for conformance,

33
Referential integrity test
Database system is a collection of tables and their relationship. One of the benefits of database
system is that the tables holding the data records can be normalized for efficient data processing and
management. When database is normalized their relationship increases. Referential integrity ensures
that the relationships among database tables are maintained. Referential analysis is a test to evaluate
database referential integrity. Referential analysis test will (1) verify if primary keys are properly
assigned, (2) verify if foreign keys are properly assigned, (3) verify if correct relationship exist between
data records, (4) verify if correct relationship exist between database fields, (5) verify if there is a
parent object and associated child object, (6) verify if parent object can exist without the child object,
(7) verify if the child object can exist without the parent object, and ( 8) verify if join queries can
combine data records from different tables (Oracle, n.d)
Recovery Testing
The database system will encounter problem when in operations. Recovery testing is designed
to evaluate how database recover from problem situations. The recovery test will verify (1) if system
can recovers from unexpected conditions such as shutting down and restart, (2) how long it takes to
recover, (3) what recovery point is achieve after resumption, (4) if the data recovery are correct, and (5)
if recovery require administrator intervention or auto-recovery operations (Tutorialspoint, n.d)
User acceptance testing
User acceptance testing will be conducted as an enterprise solution within the infrastructure. It
will test the information system end to end. The test will be conducted in the production environment.
The test script to be used will be sanitized live data. The criteria for the test will be the business
requirements and functions. The test will be executed by the user group with the data supplied by them.
It will evaluate (1) how database accept user data, correct data, wrong data, mixed correct and wrong

34
data, (2) how database respond to user inputs, the response produce, how the response was produce,
(3) if database response within approved response time, (4) if database reject error input, does the
database produce bad statement for error input, does it freeze the system due to error input (5) if
database process data correctly, (6) if database operates under approved concurrent users, how many
user can work on the database concurrently, what happen when user exceed the limit identified, does it
freeze the database, does it produce error message that limit is exceeded, (Microsoft, 2000)
Summary of database test
This test model described in this section is based on the database testing model developed by
Agarwal (2017)
General Test
Verify database naming convention and parameters for application connectivity
Verify activity logging in the database
Verify storage allocation for operational growth
Verify naming, existence and functionality of database tables, columns, and data type
Verify existence and functionality of Null value
Verify existence and functionality of primary and foreign and corresponding indexes
Functional Test
Verify data insertion, manipulation and retrieval operations
Verify the existence of triggers and operations or event that affect the triggers
Verify program codes in stored procedures, their use and impact on database
Verify database views, their use and impact on database
Verify data flow into database and from database

35
Non-Functional Test
Verify effect of wrong data input
Verify effect of SQL injection attack on database
Verify how exceptions are handled
Verify if error message is meaningful for recovery operations

36
Chapter 4 - Recommendations for Enhancements
Successes
International Project Leadership Academy (2017) describe project success as several layers of
achievement. The association developed a guide to measure project success as shown in fig 8.
Fig 8 Project success layers
According to the guide the requirements for a project success depends on several factors. These factors
are used to evaluate the successes in the database system project.
Achieve the desire outcome or objectives
The database system will meet the project objective if (1) patient data can be collected very
easily with a simple click, (2) patient data is processed effeciently without human intervention or

37
manual manipulation, (3) patient data is securely stored and protected from unauthorized access.
Meet organization and market needs
Database system project work will meet organizational and market needs if patient data and
information can be (1) added into the system, (2) deleted as needed, (3) updated when required, (4)
retreived and viewed by authorized employees and (5) processed based on service request such as
appointment and consultation scheduling which are essentially the health care industry service
operations. In addition, the database system must support the enterprise information system to provide
meanigful use and ensure data security and meet regulatory and contractual requirements.
Meet quality requirements and satisfy quality expectations
According to Hoxmeier (1997) databse quality can be measured with 4 criterias
Process quality: The design and implementation of the database system used a structured waterfall
analysis and design methodology which ensure the development incorporates all design processes
including conceptual and logical designs. Implementation also adopted a structured review and testing,
white box and black box were conducted on the product which guanrante quality database system for
data insertion, data retrieval and referential integrity operations. The same data collected and process is
available for all system user for operational purposes.
Data quality: Business rules, system and compliance requirements were accurately identified and
captured in the data preparations and implementation. Primary and foreign keys were included in the
design to represent business rules and system requirements. Data were successfully cleansed to
conform to business rules. Consultants now have and works with accurate patient data which eliminate

38
misplaced diagnosis and lost prescription
Semantic quality: Data semantic used conform with database naming convention best practices. For
example ID is used to represent unique identification of database object and simple convention such as
CustomerName and BillAmount were adopted which can make future enhancement very easy for
developers.
Behavior quality: The database system produced high quality behavior under different circumstances,
the database system produce accurate and consistence result in all circumstance.for example the error
messages produce under regression testing does not expose the internal workings of the database but
produces acurate and complete information describing the error and the root cause of the error.
Database error are accurate and relates to the problem the database operations encoutered
Deliver what was agreed
The database system completed and delivers the original project scope and was implemented
through approved and documented change management. A working database system that solves the
problem statement was delivered. It is now very easy to book appointments, manipulate consultation
appointments, check appointment list, eliminate errors and missed appointments and increased
interaction between health care provider and health care services consumers
Execute project on schedule and on budget
The database system cannot be completed on schedule due to some challenges with the project
portfolio implementation, many integrations and testing could not be done because there is no existing

39
information system platform to conduct the test and the application developers are still working on the
frontend application. The database project incurred extra budgetary cost because of some unexpected
project management work during planning and database installation. This is shown in appendix C
Challenges
Project management involves the interplay of many unknown variables and which bring
together combination of many resources but which must be coordinated to achieve project objective.
Some of the challenges encountered during the database project are described below
Policy document: At the start of the project work there was the need to understand the business
operating environment; however, it was difficult to locate the group policy and procedure document.
When it was made available it does not provide expected information for the analysis phase of the
project. It was challenging understanding the organizational structure and the process flow. Policy
statements development took the better part of the start of the project. Being part of the process of
developing policy and procedure was a challenging experience. Much of the early project works were
suspended until appropriate and guiding policies were developed and documented. Bringing the
member of staff to work together on the policy development was also very challenging because there
has never been a forum that bring the employee together for a structure operation before this time.
Lesson learned: A comprehensive risk assessment should have been conducted on the environment to
identify and understand the full status of the organization and requirement should have been made for
adequate policy guidelines on business operations for the group. PMO would not have been involved in
policy development which is an internal operation
Project champion: Identifying project champion who can influence both the employees and the

40
executive was very difficult. There was a general apathy during the familiarization meetings. Many
expressed the concerns on whether the database system will take over their job thus creating negative
attitude towards the project. It was very challenging changing this attitude and selecting a project
champion.
Lesson learned: There should have been extensive engagements and communication with the
stakeholders and users in particular well ahead of the start of the project. These engagements would
have provided a feedback on the feeling of the users and employee and the PMO would have
adequately addressed the concerns before setting project schedules
Perceived Value: Another challenge encountered during the project work was the multiple
expectations of perceived value. Each and every employee wanted to know how the project will meet
their respective goals and values. Almost all the initial user requirements from each department were
conflicting and contradictory. Some departments made a point that their support for the project is
subject to full implementation of their specific user requirements. During the interview process it was
clear that employee did not share the strategic goals of the project but were concerned about their
personal gain. Employee involvement, analysis and reconciliation of ideas and opinions was very
challenging during the project work (Feldman et al, 2014)
Lesson learned: There should have been an engagement with the stakeholders where the
organizational requirement and value are properly explained and discussed, stakeholders would have
been giving proper orientation about what the project is and it is not, and what is expected of the
stakeholders
Project port-folio: The database system project is only one of the information system project port-

41
folio of the group at the time. All the projects are connected and are interrelated in every respect. There
was no information technology platform within the group. Therefore, the database project and all other
information system project were built from scratch. This environment presents many challenges. The
entire project relies on each other. The database project depends on the software system, the network
operations, hardware infrastructure and the operating system architecture. These multiple reliance
makes the project implementation difficult because many task schedules were delayed or postponed
indefinitely; conducting integration test with the other information systems was very challenging. For
example, at one time the final application program interface (API) structure required for the database
design had to be modified to for integration. At another time the system was not responding and it was
identified that the network bandwidth size was not able to carry the data traffic across the information
system because inadequate estimation was made in the bandwidth specification.
Lesson learned: Agile methodology should have been used from the start of the database project to
allow for continuous refinement and modification of the database system in response to changes in
other IT projects.
Selecting the database software: During the planning phase of the project the process of selecting the
database system software for the project proved to be a challenge because different stakeholders were
arguing based on their background: director of finance was pushing for less expensive selection while
the director of IT was pushing for product with excellent support, head of risk was in support of the
most secure database system software.
Lesson learned: The lesson learned is that the decision for selecting the database project solution must
not be based on personal opinion or preference. The selection process must start with team
identification, the team must have relevant experience on the project, the team should include member

42
from various background. The team must clearly define the objective of the solution which must be
based on the business requirement and then the solution must be collectively ranked based on features,
cost, support, future growth and available internal skill or requirement for acquiring additional skill.
The decision must be jointly agreed, documented and approved (Blum Shapiro Consulting, n.d)
Support from the executive: The initial understanding of the executive support was limited to
obtaining their approval to proceed with project works. However, the many challenges encountered
during the project work demonstrated that much more are needed from the executive. For example,
many important stakeholder meetings were not attended as expected because employee were not
motivated if they realized that management will not be attending the meeting, this attitude significantly
affected many project decisions and thus impacted project schedule and cost.
Lesson learned: The lesson learned is that executive must be involved in every aspect of the project.
For example, executives must be included in all project communications, and PMO must obtain the
understanding of the executive to motivate employee in every project communication, this will carry
the respect that the executive are in the loop of the project meeting. It is also important that constant
reminder must be sent out to project attendees because functional staffs that are drafted into the project
could be engaged in other important activities relating to the current business operations.
Project work definition: The project work was defined in high level terms; this is because many of the
works are derivative of the project portfolio plan. This creates many project problems because many
activities were rework when more details of the project task were made known. For example, some of

43
the dependencies for the database system such as ASP.NET framework was incompatible with some of
the health application software data structure from the other projects.
Lesson learned: Lesson learnt is that each work breakdown structure in the project portfolio must be
aligned properly and all integration issues must be identified and resolved. PMO must continuously
review task integration and compatibility across multiple interrelated project works. It is important to
fully understand dependencies among project work streams during project planning to ensure the right
resources are identified, procured and allocated for project work. Continuous monitoring is very critical
for multi-project work interdependencies (Prinzo, 2011)
Test environment: Because there was no existing information system infrastructure and each of the
project works were interdependent but separate projects it was a challenge to set up test environment
and thus resulted in many delays and uncertainty in project progress.
Lesson learned: The lesson learned is that project testing work should be assigned to a team member as
the task owner. The owner could explore available external or internal resources which could be unknown
to the project planning and can successfully establish an environment that can be used for the testing.
The team member responsible for the testing operation must document who will need the test
environment, when it will be needed, what test is to be done and why test is required. For example, during
a research for how to conduct a particular black box test an alternative approach was identified: using a
virtualized test environment. Virtual test environment removes many concerns about software testing
requirements. Virtual test environment creates a typical instances and parameters identical to the

44
production environment. One major benefit of virtual test environment is the ability to perform several
case scenarios until the best fit is obtained (Simon, 2015)
Modification strategy
The database system project as part of the project portfolio of the group is an organizational
change. This change affects the management and the employees. For one thing it brings a change to the
business processes and operations. The projects also affect the services provided by the group.
However, the changes introduce by the adoption of the technology are still evolving (Gilbert, 2009).
For example, the encryption technology implemented is AES 256; in future modification there will be
the need to upgrade the encryption to AES 512 which is a more secure technology, this technology
cannot be implemented at this time because the processing overhead will be too high for the database
management system installed. Therefore, this is the beginning of many changes to come on the product
of the project work. To prepare for this evolution a modification strategy is adopted to manage future
modification and changes. The modification strategy to be adopted is the modified change management
life cycle developed by TwentyEighty Strategic Execution (2015). The model is shown in figure 9.

45
Fig 9 Change management life cycle
The model in fig 9 shows the process flow for the modification strategy. At a point in time the system
will be in a particular state known as the current state, when in the current state a need will arise to
achieve a desire state which is a new state, this need will be identified and compare with the current
state and the gap analysis documented. This documentation will provide the basis for engaging the
stakeholders and for requesting the resources and to prepare the change management operations. When
the resources are all available and an approval obtained the change will be implemented and the desire
state is achieved. From the model if the implemented change does not meet the desire state the process
will be refined and the change implementation will be done again until the desire state is finally
achieved.
The model has 3 phases and the each of the phases has its unique actionable processes which take the
implemented system from a particular state and change into the desire future state.
Phase 1: Identify the Change
The basic requirement for continuous and successful change management process is the identification
of the need to adopt a particular change. Some of the events that triggers the need for a change are
Inadequacy in the system: The current design and implementation is a development from the scratch.

46
This is the first time the group will implement information system for its service delivery. The current
system is developed with limited knowledge and resources and with the objectives of solving current
problems. During the course of usage many inadequacies will be identified and this will provide the
basis for a modification or a change. Therefore, users will be encouraged to identify any issues with the
adoption of the technology and provide feedback on how they expect the system to operate.
Change in customer value: The database system is implemented to make customer service delivery
efficient. However due to some limitations, little or no customer inputs were incorporated in the system
project, largely the requirement for the project were what the organization perceived to be value
proposition for the customer, such as accurate billing and efficient enrolment. Customer might notice a
break in their expected value chain such as availability self-service or ability to pay bill online.
Feedbacks from customers will provide a basis for change in business model and system modification.
New service channel: The system may be modified if the group identifies other areas of providing
services and generating revenue. For example, the system can be modified to integrate with pharmacy
stores to send an alert for medication delivery immediately after consultation. Medical counseling is
another service that can be implemented on the system which can be providing articles, webinar and
video conferencing services to manage customer health care need. These services will need a strategic
modification and expansion to the current system.
Response to competition: The information system delivers customer service to create a competitive
advantage. Customer experience and testimonials with the new system will attract new customers to the
organization. However, if a competitor introduced an improved service delivery system the group will
have to identify this as a threat that can take away its customers. The group must respond to advances
in health care delivery system to the extent that its capabilities can accommodate. This requires
modification and enhancement to the system.

47
Regulatory climate: Regulation is another factor that can trigger identification of the need to modified
current system. The uniqueness of this factor is that it is a compulsory requirement which the group
must implement otherwise the group could lose its operating license. However, compliance with
regulatory modification is a value proposition because it will demonstrate that the group meets the
minimum requirement to operate within the society. The group must always scan the regulatory
environment so as to always be ready to comply with its requirement. For example, the current
implementation only installed the basic access control requirements of the HIPAA regulatory
requirement. Other requirements must be incorporated as future modification such as central logging
(Hill, n.d)
Communicating the need for change
Identification of needed change must be completed and documented properly because it
provides the basis for communicating the change to employees and stakeholders. The current
communicating plan used for the project management can be adopted but will be extended to include
new operations and stakeholders. Eisenhauer (n.d) recommended an effective strategy on
communicating a change to the organization. First the group must prepare for the communication by
planning for (1) the right message to be delivered, the change message must be clear and the objective
adequately described and explained, (2) the right format to deliver the message, the group must choose
combination of several communication channels such as intranet, collaboration platforms and personal
engagement this will depend on many factors including which medium stakeholders are used to or will
prefer, (3) the right time must be decided for the communication, the group should use a time when
there are no organizational problem, a time when employee are in the right frame of mind on their job
could be appropriate to discuss a change and (4) the right communicator must deliver the message,

48
while it is much better and appropriate that a change message should be communicated by senior
management, any employee with considerable influence within the organization can be assigned to
deliver the message as well.
In addition, Eisenhauer (n.d) suggested 4 phases that organization can deliver a matching
communication to employee. (1) Denial stage, when a change is communicated employee will be in
shock or denial because it will surprise them that the comfort zone is about to be disrupted, at this stage
employee need as much information as available to continue to process the communication,
information to be provided at this stage include the objective of the change, impact of the change and
where to get help and clarifications on the propose change (2) Resistance stage, at this stage employee
will start to express their fear and feedback will be coming in, this stage is important because it provide
opportunity to understand employee feeling about the change and possibly their condition for adopting
the change, proper feedback management, expectation management and employee support must be
available at this stage, (3) Exploration stage, at this stage communication must be designed to provide
direction for employees, the group must take advantage of this exploration stage by assigning roles and
responsibilities and provide change management productivity tools such as Microsoft Project which
employee can use to track engagements and (4) Commitment stage, this stage rest squarely on the
management, having obtain appreciable commitment from the employee, management must sustain
that commitment by constantly reaching out and communicating their belief and commitment to the
adoption of the change by the employee; management can convene staff meeting to express
appreciation for the adoption of the change, they can provide progress report on the change and
emphasize more work that need to be done and the expected benefits ahead
Phase 2: Engage the People
Identification of the need for a change and communication of the requirement and process for

49
the change prepares the employee for the subsequent engagement. The group should engage the
employee in the planning for the response to the change. The group should select head of units and
change champions and provide empowerment to make the change progresses into reality. The group
must emphasize the problem the change will solve and the benefit to each and every member of the
organization. Management must develop change management program using bottom-up approach. The
project management plan must incorporate the feedbacks from employee and be reflected in the change
program task lists. Some methods that can be used to engage employee are (1) a workshop that bring
stakeholders together for brainstorming session, this session must be designed to draw out questions
from the employee and management must prepare appropriate responses, this engagement must be
documented. This platform will provide the avenue to make employee contribute their ideas for the
change management deliverables
Employee contributions must be synthesized and aligned with the change objectives and then a
proper management plan must be developed to adapt the change to the information system. This
approach will enable the group to turn employee reaction to a positive change program. With this
positive adoption of the change initiative, executive management should develop the implementation
strategy that will transform to information system upgrades and improvement. The PMO must include
internal employee and especially user representative to help manage the impact at every level of the
organization. This strategy must be flexible to make it very easy for employee to adopt it and adapt it to
their job function and to the work environment (TwentyEighty Strategy Execution, 2015) For example
to implement a regulatory change the group can obtain a copy of the regulation and share with the
employee from which ideas for change implementation may be obtained and adoption program will be
shared with employee (Gilbert, 2009)

50
Phase 3: Implement the Change
Management must translate the changes developed during the Identify and Engage phases into
strategy that will take the group to the desired future state. Management must continue to involve
employees in the implementation phase; this is because this is the stage that will actually put the
change management into operations. Employee must be involved in the creation and implementation of
enterprise information system changes. Employee will use their experience to implement the
technology change to improve the current processes and achieve the desire result. During
implementation of the changes, management must continue to communicate with employees the
objective of the changes and the expected result; this is to ensure that the change management process
is fully and completely installed. Towards the end of the implementation phase, management must
begin to review job functions and responsibilities to ensure that the right people are assigned to manage
the changes implemented. This review will include job functions to be removed, job responsibilities to
be modified and new responsibilities to be created.
Actionable steps
Kern (2003) recommend the following actionable steps for implemeting change management
operations:
Step 1: Definition of change management process and practices
Change management processes and practices will be defined in a change management program. This
program will specifically define the procedures for change operations such as who can request for a
change, how change request will be processed and back-out procedure for failed change. The program
will also define roles and responsibilities for change operations such as who can approve change
request, who will implement approved change and who will review implemented change management.

51
There will also be metric for change such as criteria for successful change or failed change. Change
management will require management tool, the tool will manage the change from creation to archival.
For example, a change request to enhance the database system with an analytics module or upgrade the
encryption system
Step 2: Receiving a change requests
Change request will be received through the change management tool. Change requester will create a
change in the change management tool which could be a manual form or an automated tool such as
SLAM change management software. Everyone that should be aware of the change or that should take
action on the change will receive the change request either through automated alert or notification or
physical submission. The change request must have a date and time for tracking and management. For
example, the following stakeholders should receive the change request for database analytics
enhancement, appointed CIO, DBA, IT manager, Network administrator, and System administrator,
they will be required to make contribution on critical success factor for the change
.
Step 3: Plan for and prioritize change request
There will be adequate planning for change request which include allocating resources both human and
information system for the implementation of the change request. There will also be a plan for the
possible impact of the change on the information system and this will necessitate definition of roll back
plan should change management fail to proceed as planned. Change request will be prioritized based on
organizational need and risk assessment. Planned, emergency and routine changes will have different
prioritization.

52
Step 4: Implementation and monitoring
based on prioritization change request will be implemented. During implementation the change
operations will be monitored to ensure the objective is been achieved and if not there can be
modification or outright stoppage and roll back operations can be activated
Step 5: Evaluation and reporting on changes implemented
Successful change implementation will be reviewed and evaluated by comparing planned objectives
with the result of the change implemented. Change implementer will prepare a report on the
implementation operations success factors for the change implementation and lessons learned during
the change operations. This will provide a feedback to stakeholders on how to improve the change
management operations. Change management operation manager will analyze the trend in change
management operations and also prepare a report for management to review with a view to change
management resources improvement
Step 6: Document change operations
Depending on the change management tool applied change management request and approval
document and the associated post implementation reports will form the change management
documentation. All activities before the change was implemented, all activities during the change
implementation and all activities after the change was implemented will be documented to provide a
guide for managing future change operations and to improve on change management policy and
procedures.

53
Step 7: Communicating change benefits
Change management operations manager must inform stakeholders on the impact of the change and
how the improvement or modification or enhancement the change introduced so as to adjust
information system operation to support the change. Internal memo will be used to communicate the
benefits of the change operations and expectation from stakeholders to support the change
Project management
Management of project potential issues is a critical task in project management. For one thing
the outcome of the project is unknown and uncertain and all the project variables are mere estimates
that may or may not be realized. Project potential issues must be managed adequately to increase the
chances of project success. Project potential issues are events and problems that may occur during
project implementation. There must be adequate planning and control in place to manage project
potential issues so that the issues will not become project risks which could become expensive to
managed or affect the project negatively. Once the project potential issues are identified it will be
documented and appropriate control strategy will be developed to manage it. Control strategy for
project potential issues must be included in the project scope and requirement so that it will obtain the
same approval and work alongside the project plan. Thus, whenever the project potential issues are
identified, control strategy can be initiated and implemented immediately. Project potential issues must
be tracked before and during project implementation, this will provide opportunity to identify the issues
as they occur and can trigger management action (Ferris, 2011)
Wallace, S. (2016) recommended a project issue management model shown in fig 10

54
Fig 10 Issue management process (adapted from EPMBOOK)
Figure 10 shows that participant or stakeholders will be responsible for identifying the issues and the
PMO will capture the issues in the project management tool. PMO will review the issues and request
participant to assess the issues, some activities could be approval or change management request. After
the assessment PMO will be required to decide the best way to resolve the issues, this may require that
external parties should be involved such as requirement for external purchases or consulting services.
PMO will continue to review the resolution while liaising with the participant until all stakeholders
agree that the issues are resolved and the issues are closed (Wallace, 2016)
Project potential issues with database system project are essentially technical in nature. The potential
issues with database system project are described below with reference to Mar (2016)

55
• There is no information technology skill set because there was no IT department this will affect
the operation of the database system because it will be difficult to manage the database system
without an IT department. After completing and handling over the database project require
system administration task such as database tuning, troubleshooting server error and data
backup will be impossible without an establish IT department
• There is no information system infrastructure which could affect database system testing and
change requests. Because there is no existing information system infrastructure conducting
many routine system maintenances such as database patch management operation, testing
upgrades before deployment, and testing new technology before implementation will be
impossible because there is no dedicated and full information test environment
• Many user requirements have been ignored or postponed because of the strict application of the
stepwise traditional waterfall development methodology. This action portends potential issues
because user need may not have been fully implemented. However agile methodology was
introduced to allow continuous refinement of the database design to meet the requirement of the
proposed information system infrastructure. Nevertheless, many user requirements might have
been missed which could raise issues in future.
• Software code that will interface with the database system have not been verified and could
present problem for the database, for example the application system have not been tested for
SQL injection vulnerability because the database system could be attacked with SQL injection
through un-validated input from the software
• Because of the changing needs for the information system infrastructure the vendor for supply
of security appliances such as firewall and IDS have indicated that there will be some delay in

56
delivering supply, this will affect database system because using the system without the security
appliances will expose the database system to internet risks.
• The plan cutover strategy from manual to automation using parallel method will introduced
some problem because the 2 databases (manual record and user input data record) will have to
be reconciled because it will be difficult to know which is correct because both could have
experience input error
Recommended future enhancements
The database system will continue to evolve and become better. It will continue to increase in
usefulness and application to the operation of the group. Some future enhancements that can be added
to the database system are enumerated below
Payment gateway
The database system currently prepares customer bill for health care services provided, the bill is
printed out as an invoice and the customer take the bill and make payment through a separate channel.
As an enhancement the database can be integrated with a payment gateway. The payment gateway will
extend the services beyond printing out the bill. The payment gateway will require that the database
collect data from a POS application on a POS device and the front-end application will be modified to
include a cart and checkout modules. The database will be modified with capabilities to collect, process
and store financial data such as bank ID, payment card PAN, expiry date, CVV. The database will also
have the capability to send the financial data to a payment card processor for processing and settlement
operations (Verifi, n.d)
Database schema for the payment gateway will look like the data structure in table 14

57
Date
Transaction
code
Customer
name
PAN
CVV
Expiry
date
Bank
ID
Processor
ID
ISO
message
code
Description
Table 14 Payment gateway database schema
Accounting and Payroll
Accounting and payroll processing is about data. The database system can be enhanced to process and
manage the accounting and payroll operations. The enhancement will require uploading employee data
and daily attendance record, this will help to manage employee information, define emolument,
calculate deduction, calculate salary and wage, generate pay-slip, manage and control employee
attendance (XIPHIAS, n.d)
The database schema for the accounting and payroll functionality will look like the data structure in
table 15
Date
Employee
ID
Employee
name
Employee
hire date
Employee
grade
Employee
attendance
rate
Employee
dept.
Salary
level
Employee
pay
Table 15 Payroll database schema
Data analytic
Data record within the database system will continue to grow. The data will provide information for
decision making. The database system can be enhanced to leverage the pattern matching technology of
the data analytic features. The data analytic functionality in MSSQL can use the data records to
generate trend analysis on patient, medication and surgery. The data analytic can generate charts and
graphs to analyze test results and to show trends analysis in patient’s condition. Monitoring change and
trends can assist management to develop strategic plan to manage operations efficiently and profitably
(Axxyr, n.d)

58
Pharmacy module
The database system can be integrated with the pharmacy processing operations. The database system
will have to be updated with pharmacy inventory operations. The data structure of the pharmacy
database schema will look like table 16. The data analytic system can be applied to the pharmacy data
record to perform analysis such as drug combination, drug sensitivity level and interaction and drug
conflicts. The database system receives consultant prescription which search through the stock and
indicate availability or not, if available the system request for the drug using first-in first-out processing
(ACGIL, n.d)
Batch
no
Manufacture
date
Drug name
Delivery
date
Description
Classification
Expiry date
Fig 16 Pharmacy database schema

59
Bibliography
Adeshina et al. (2016). IT 510 Final Project_System Proposal Document. Term Project, Southern New
Hampshire University, IT.
Elmasri, N. (2004). Main Phases of Database Design. Retrieved Apr 24, 2017, from AMK:
http://www2.amk.fi/digma.fi/www.amk.fi/opintojaksot/0303011/1146161367915/11461616806
73/1146161874577/1146161968355.html
eTutorial. (n.d.). What Are Business Rules? Retrieved Apr 26, 2017, from eTutorial:
http://etutorials.org/SQL/Database+design+for+mere+mortals/Part+II+The+Design+Process/Ch
apter+11.+Business+Rules/What+Are+Business+Rules/
Microsoft. (n.d.). Hardware and Software Requirements for Installing SQL Server 2012. Retrieved Apr
27, 2017, from Microsoft: https://technet.microsoft.com/en-
us/library/ms143506(v=sql.110).aspx
Monegain, B. (2009, Jan 27). Texas hospital study points to benefits of automation. Retrieved Apr 25,
2017, from HealthCare IT News: http://www.healthcareitnews.com/news/texas-hospital-study-
points-benefits-automation?single-page=true
Smartdraw. (n.d.). Use Case Diagram. Retrieved Apr 26, 2017, from Smartdraw:
https://www.smartdraw.com/use-case-diagram/
Washington State University. (2008). Understanding the Database Design Process. Retrieved Apr 26,
2017, from Washington State University:
http://www.public.wsu.edu/~arola/356/spring09/meloni_15.pdf
WiseGeek. (n.d.). What is conceptual database design? Retrieved Apr 26, 2017, from WiseGeek:
http://www.wisegeek.com/what-is-conceptual-database-design.htm
Adeshina, S. (2017). IT 650 Final Project.
Agarwal, M. (2017, Feb 26). What is Database Testing, How to do it and write test cases? Retrieved
May 4, 2017, from Teachbeamers: http://www.techbeamers.com/database-testing/
Databasetestdata. (n.d.). Generate test data for your database. Retrieved May 1, 2017, from
Databasetestdata: http://www.databasetestdata.com/
Guru. (n.d.). Database(Data) Testing Tutorial with Sample TestCases . Retrieved May 6, 2017, from
Guru: http://www.guru99.com/data-testing.html
Key Management Group. (n.d.). Legacy System Data Conversion And Migration. Retrieved May 6,
2017, from Key Management Group: http://kmgus.com/Docs/White-Paper/data_migration.pdf
Maryland.gov. (n.d.). IMPLEMENTATION PLAN. Retrieved Apr 29, 2017, from Maryland.gov:
https://view.officeapps.live.com/op/view.aspx?src=http%3A%2F%2Fdoit.maryland.gov%2FSD
LC%2FDocuments%2Fimpl_plan.doc
Microsoft. (2000, Jul). Testing Process. Retrieved May 1, 2017, from Microsoft:
https://msdn.microsoft.com/en-us/library/ms978235.aspx
Microsoft. (2017, Mar 24). Hardware and Software Requirements for Installing SQL Server. Retrieved
Apr 30, 2017, from Microsoft: https://docs.microsoft.com/en-us/sql/sql-server/install/hardware-
and-software-requirements-for-installing-sql-server
Microsoft. (n.d.). Hardware and Software Requirements for Installing SQL Server 2012. Retrieved Apr
27, 2017, from Microsoft: https://technet.microsoft.com/en-
us/library/ms143506(v=sql.110).aspx
Oracle. (n.d.). Understanding Data Quality Management. Retrieved May 1, 2017, from Oracle:
https://docs.oracle.com/cd/B31080_01/doc/owb.102/b28223/concept_data_quality.htm
Project Manageent Doc. (n.d.). Implementation and Migration Plan. Retrieved Apr 29, 2017, from
Project Manageent Doc:

60
https://view.officeapps.live.com/op/view.aspx?src=http%3A%2F%2Fwww.projectmanagement
docs.com%2Ftemplate%2FImplementation-and-Migration-Plan.doc
Sam Houston State University. (2000). Database Systems: Design, Implementation, and Management.
Retrieved Apr 29, 2017, from Sam Houston State University:
http://www.shsu.edu/~csc_tjm/summer2000/cs334/Chapter08/Chap08-97.html
Tutorialspoint. (n.d.). ETL Testing - Quick Guide. Retrieved May 6, 2017, from Tutorialspoint:
https://www.tutorialspoint.com/etl_testing/etl_testing_quick_guide.htm
University of Houston–Clear Lake. (2001). Course Registration SystemTest Planfor theArchitectural
Prototype. Retrieved Apr 29, 2017, from University of Houston–Clear Lake:
http://sce.uhcl.edu/helm/RUP_course_example/courseregistrationproject/artifacts/test/plans/test
_plan_arch.htm
University of North Carolina Greensboro. (2006, May 1). Change Management Process. Retrieved Apr
30, 2017, from University of North Carolina Greensboro:
https://its.uncg.edu/Service_Management/Change/ChangeManagementProcess.pdf
Wesley et al. (2009, Apr 27). IMPLEMENTATION AND TESTING REPORT. Retrieved Apr 29, 2017,
from University of North Carolina Wilmington:
http://people.uncw.edu/simmondsd/documents/450_Implementation%20and%20Testing%20Re
port.pdf
Blum Shapiro Consulting. (n.d.). Software Selection Best Practices . Retrieved May 19, 2017, from
Blum Shapiro Consulting: http://www.blumshapiro.com/kbarticle/software-selection-best-
practices
Eisenhauer, T. (n.d.). How to Communicate During Organizational Change. Retrieved May 17, 2017,
from Axero: https://axerosolutions.com/blogs/timeisenhauer/pulse/383/how-to-communicate-
during-organizational-change
Farooq, B. (2012, Jun 23). Basit's SQL Server Tips. Retrieved Apr 30, 2017, from Sqlservercentral:
http://www.sqlservercentral.com/blogs/basits-sql-server-tips/2012/06/23/sql-server-2012-
installation-guide/
Feldman et al. (2014). Health Information Exchange Implementation: Lessons Learned and Critical
Success Factors . Retrieved May 13, 2017, from JMIR Med Inform :
http://medinform.jmir.org/2014/2/e19/
Ferris, B. (2011, Jul 8). Issues and Risks in Project Management . Retrieved May 18, 2017, from
CobaltPM: http://www.cobaltpm.com/issue-and-risk-management/
Gilbert, J. (2009, Apr 14). The Change Management Life Cycle; Involve Your People To Ensure
Success. Retrieved May 16, 2017, from BATimes: https://www.batimes.com/articles/the-
change-management-life-cycle-involve-your-people-to-ensure-success.html
Hill, B. (n.d.). What Is a Business Model Change? Retrieved May 16, 2017, from Chron:
http://smallbusiness.chron.com/business-model-change-3868.html
Hoxmeier, J. A. (1997). A Framework for Assessing Database Quality. Retrieved May 13, 2017, from
Colorado State University: http://osm7.cs.byu.edu/ER97/workshop4/jh.html
International Project Leadership Academy. (2017, Feb 21). What is Project Success? Retrieved May 13,
2017, from International Project Leadership Academy: http://calleam.com/WTPF/?p=3501
Mar, A. (2016, Jun 27). 130 Project Risks (List). Retrieved May 18, 2017, from Simplicable:
http://management.simplicable.com/management/new/130-project-risks
Prinzo, R. (2011, Jun 6). Project Management: 6 Best Practices for Ensuring Software Implementation
Success. Retrieved May 19, 2017, from CIO: http://www.cio.com/article/2407455/project-
management/project-management--6-best-practices-for-ensuring-software-implementation-

61
success.html
Project Management Doc. (n.d.). Lesson Learned. Retrieved May 13, 2017, from Project Management
Doc: http://www.projectmanagementdocs.com/project-closing-templates/lessons-
learned.html#axzz4gyjBy8tk
Rosenblatt, H. J. (2013). Systems Analysis and Design. In H. J. Rosenblatt, Systems Analysis and
Design (10 ed., pp. 62-65). Course Technology.
Simon. (2015, Apr 13). Test Environment Management: Best Practices. Retrieved May 19, 2017, from
Lean Testing: https://leantesting.com/test-environment-management-best-practices/
TwentyEighty Strategy Execution, Inc. (2015). The Change Management Life Cycle. Retrieved May
16, 2017, from TwentyEighty Strategy Execution, Inc: http://www.esi-
intl.co.uk/resource_centre/white_papers/the%20change%20management%20life%20cycle.pdf
Wallace, S. (2016). Issue Management. Retrieved May 18, 2017, from EPMBOOK:
http://www.epmbook.com/issues.htm
Kern, H. (2003, Dec 1). Implement change management with these six steps. Retrieved May 28, 2017,
from Tech Republic: http://www.techrepublic.com/article/implement-change-management-with-these-
six-steps/
ACGIL. (n.d.). Pharamacy Management System. Retrieved May 30, 2017, from ACGIL:
http://www.acgil.com/products/pharmacy-management.htm
Axxyr. (n.d.). HOSPITAL MANAGEMENT SYSTEM. Retrieved May 29, 2017, from Axxyr:
http://www.healthachieve.com/plan-your-
visit/Documents/Axxyr%20Hospital%20Management%20System.pdf
Verifi. (n.d.). Global Payment Gateway. Retrieved May 30, 2017, from Verifi:
http://www.verifi.com/secure-global-payment-gateway/
XIPHIAS. (n.d.). Payroll processing. Retrieved May 30, 2017, from XIPHIAS:
https://www.xiphiastec.com/XIPHIAS-pdf/Payroll.pdf
Jamaal Green and Angela Richardson. (2011, Nov 16). Microsoft Baseline Security Analyzer (MBSA) .
Retrieved May 27, 2017, from FAMU: http://www.famu.edu/cis/project3.pdf

62
Appendix
Appendix A - Project Plan
The project plan captures and documents the database system project planning, decisions, assumptions
to guide project implementation and facilitate project communication among project stakeholders; it
shows the resources, the cost, the scope, the baseline and the schedule. The project plan also shows the
project timelines as shown in fig 1
Fig 1 Project plan

63
Appendix B - Project Resource Cost
Project resource cost shows the database project resources and the project work require and the cost of
the work require based on the applicable rate per hour, this is shown in table 1
Project Resources
Actual Work (Hours)
Actual Cost ($)
Standard Rate/hour
($)
Project Manager
255
3,825.00
15.00
IT Director
40
1,200.00
30.00
Sponsor
16
560.00
35.00
User Manager
180
2,160.00
12.00
Table 1 Resource cost

64
Appendix C - Project Budget control
The project budget variance shows the plan project work and the budgeted cost for each of the task,
during the project the actual cost were tracked and documented and it is used to prepared the budget
variance shown in the table, PMO will take proper action to ensure the variances are resolved, this is
shown in table 2
Project Work
Budgeted Cost
($)
Actual Cost
($)
Cost Variance ($)
Plan for system requirement
gathering
1,500.00
1,616.00
(116.00)
Execute database project plan
1,300.00
1,272.00
28.00
Create change control document
100.00
120.00
20.00
Install database
350.00
360.00
(10.00)
Close project
1,200.00
1,192.00
8.00
Table 2 budget variance

65
Appendix D - Case Study
The project work is based on the case study developed by Rosenblatt, (2013), the project reference the
case study shown in fig 2
Fig 2 case study

66
Appendix E - Database System Installation Guide
The installation process for Microsoft MSSQL Server 2012 recommended by Farooq (2012) is
described below
1. Insert the SQL Server installation disc. Select the root folder, double-click the Setup.exe
2. Select planning
3. Click New SQL Server stand-alone installation
4. Setup process begins
5. System verify setup support rules for installation
6. Set up completes
7. Click OK
8. Enter product key
9. Click Next
10. Accept the license agreement by checking the box
11. Click Next
12. Click Install
13. Installation in progress
14. Click Next
15. Select SQL Server Feature Installation
16. Click Next
17. Click Select All button
18. Verify the directories
19. Click Next
20. Click Next
21. Select Named instance
22. Assign an instance name
23. Click Next
24. Click Next
25. Select service account tab and specify service startup and authentication.
26. Specify separate service account as below
27. Select mixed mode
28. Specify password
29. Click Next
30. Click Data Directories tab and specify default database, log, backup, and tempdb locations
31. Click FILESTREAM tab and enable the features as shown above
32. Click Next
33. Click Next to continue to Reporting Services Configuration page
34. Specify the kind of Reporting Services installation to create
35. Click Next to proceed to Distributed Replay Controller page
36. Specify the users you want to grant administrative permissions to for the Distributed Replay
controller service
37. Click Next to continue:

67
38. Click Next to advance to Error Reporting page
39. Click Next
40. System perform configuration checking
41. Correct any errors reported in the Installation Rules screen
42. Click on Next to advance to Ready to install page
43. Click Install button to start SQL Server 2012 installation
44. Installation progress
45. Installation completes
46. Click on Next to Complete page
47. Click on the link to review the installation log if appropriate
48. Click Close.
49. This finalizes the installation process for SQL Server 2012