ICT616 Data Resources Management

Semester 2, 2014
ASSIGNMENT 1
Assignment Information
You should submit your assignment from the ICT616 LMS site using the Assignment course tool.
Late submissions will be penalised at the rate of 10 marks per day late or part thereof.
The University treats plagiarism, collusion, theft of other students’ work and other forms of dishonesty
in assessment seriously. This is an INDIVIDUAL assignment. Any instances of dishonest in this assessment
will be forwarded immediately to the Faculty Dean. For guidelines on honesty in assessment including
avoiding plagiarism, see: http://www.murdoch.edu.au/teach/plagiarism
SUMMARY
ELK County Maine has been awarded a federal grant to build a modest pediatric medical center. The
grant also paid for the medical education of a pediatrician that will practice at the medical center for at
least five years. The grant included purchasing a medical information system to assist the doctor and the
county in managing the medical center. The county’s Director of Information Systems assigned a
business analyst to explore and report on the hardware and software requirements of the new medical
system. After the business analyst completed her report, she conducted an extensive investigation to
see if an off-the-self software package would meet the functional requirements of the medical center’s
stakeholders. It was determined that no such software package was available. Due to a near term
software development backlog in the county’s Information Systems Department, the county decided to
hire a consultant to design and implement the relational database for the new medical information
system.
ORGANIZATIONAL BACKGROUND
Elkhorn is the county seat of Elk County and is located in north central Maine. Elk County is the largest
county in Maine in terms of geographic size but at the same time is the least populated. Elkhorn has a
population of approximately 9,500 and the county’s total population is about 35,000. With the
exception of Elkhorn, the county’s population is highly geographically dispersed. The main industries in
Elk County are lumber, tourism with several well-known year round resort lodges, and paper products. 1
This case is based on a real pediatric medical practice. However, the geographic location, the names of
patients and their parents, and the general setting has been changed. For the past decade, like many
other rural areas in the United States, Elk County has been experiencing a shortage of medical personnel
and facilities. Several years ago the county received a grant from the Federal Government to fund a new
but modest pediatric medical facility and the medical education of a pediatrician. The County Manager is
responsible for the management of the federal grant. In return for the paid medical education, the
pediatrician signed a contract to practice for a minimum of five years at the new pediatric medical
facility, the Pediatric Medical Center (PMC), located in Elkhorn. The doctor, having just recently
completed medical training, arrived in Elkhorn a little over a month ago and has started to fulfill her
contractual obligation.
DETERMINING THE REQUIREMENTS
The initial budget for the medical center included the purchase of a small client/server based medical
information system to assist in patient billing and medical record keeping. After consulting with the
county’s Director of Information Systems, the County Manager decided to wait until the doctor arrived
before purchasing the system. The Director of Information Systems was unsure of the requirements in
terms of hardware and software and wanted the doctor’s input into the decision. He was uncertain
about the physician’s desired location of individual personal computers (PCs) in the new medical center
and, consequently, the quantity of PCs that needed to be purchased. But even more troubling for the
manager, was his strong belief that he was highly unqualified to determine or evaluate the software
requirements for a pediatric medical system.
Shortly after the doctor arrived, the Director of Information Systems assigned one of the county’s
assistant business analysts to determine the software requirements for the new medical information
system.
He brought the analyst up to speed on the situation at the Pediatric Medical Center and requested that
she find a suitable “off-the-shelf” software package that will satisfy the processing and reporting
requirements of the doctor, the county, the federal grant, the state’s Medical Assistance (MA) program
and the primary local health maintenance organization (HMO), Healthy in America. Medical Assistance is
a statewide program run by Maine’s Department of Public Welfare for low-income families that cannot
afford medical insurance. Medical Assistance works very much like an insurance company where the
doctors that agree to participate in the program accept the state’s published fee schedule for services
provided to Medical Assistance patients. The business analyst knew the first task was to determine the
functional requirements of the new patient billing and medical records system. This was accomplished
by conducting multiple interviews with the doctor, and personnel from the county, the state’s Medical
Assistance Office and Healthy in America. After the interviews were complete and she felt she
understood the requirements of the various parties, the analyst prepared a functional requirements
report. The report was submitted for approval by all the parties she interviewed. A few minor changes
were suggested and incorporated into the report and then a final version of the PMC Functional
Requirements Report was distributed to all concerned parties.
FUNCTIONAL REQUIREMENTS
Below are the specifications contained the Data and Information Requirements section of the PMC
Functional Requirements Report.
Family Information
The PMC medical system needs to track information on the parents whose children are registered with
Pediatric Medical Center and eligible to receive health care services. The new application must be able
to determine the financially responsible head of the household’s name, address, telephone number, and
the name of their insurance carrier (if any. The doctor would like to assign each family a unique
alphanumeric identifier so that they may identify a specific family that is registered with the Pediatric
Medical Center. The identifier will consist of the first 4 letters of the family’s last name with a unique
two digit number added at the end. For example, if there were three families registered with the last
name Smith, the 3 family numbers would be SMIT01, SMIT02, and SMIT03.
Patients
The new system must contain static information on all the patients that are registered with the Pediatric
Medical Center. The doctor needs to know the patient’s name (last name may be different from the
parent responsible for the child). Each patient is assigned a unique medical record number (MRN). A
simple algorithm is used to create this number, which consists of three parts; 1) the first three
characters of the patient’s last name, 2) the first character of the patient’s first name, and 3) two digits
that are assigned sequentially so that the first two components when combined with the third insure
the MRN is unique. For example, the boxer George Forman has five children named George. Their MRNs
would be assigned as follows; FORG20, FORG21, FORG22, FORG23, and FORG24. This assumes that MRN
FORG19 already existed in the database. If the parent’s have any type of medical insurance, including
Medical Assistance, then the parent’s social security number must be tracked. The insurance companies
require that this field be 12 characters long. The first 9 characters are the parent’s specific social security
number with no dashes. A unique 2 digit number is then assigned to each dependent child and
appended to the end of the parent’s social security number, i.e., 999999999-99, to form a unique
identifier that can be used by the insurance companies to identity each child.
Services Performed
The billing application must contain information on all of the medical services available from the
Pediatric Medical Center. This information consists of an industry standard medical services code, a
description of the service performed, the standard fee charged by Pediatric Medical Center for this
specific service (this is the fee charged to parents with no insurance), the fee that will be accepted for
this service by Maine’s Medical Assistance program (MA), and the fee that will be accepted by all of the
other insurance carriers.
Diagnosis Codes – DRG Codes.
The new application needs to support the use of industry standard DRG (Diagnostic Related Group)
codes. These are predefined unique codes where each code corresponds to a specific medical diagnosis
that a physician may make. These codes and their corresponding descriptions may be purchased on a
CDROM, where they are stored in tab delimited text file.
Insurance Carriers
The new application requires the capability to track insurance carriers that have contracts with PMC
including Medical Assistance provided by Maine’s Department of Public Welfare. Elk County has
contracted with the state for PMC to accept Medical Assistance patients. Each insurance company has
been assigned a two character insurance code. Parents that do not have medical insurance with a firm
that has a contract with PMC are considered to be self-insured.
Patient History
The new system must retain a complete patient history. This includes services performed and fees
charged for those services. The doctor wants the capability to override the standard fee schedule. Also,
some fees are subject to frequent change, especially those associated with injections and lab work.
Consequently, the doctor needs to know the fee charged at the time the service was provided. In
addition to tracking historical services, the system must maintain a complete history of the diagnoses
made by the doctor on each patient visit. If a doctor sees a patient multiple times in a single day it will
be recorded in the system as a single visit. The pediatrician may perform multiple services and make
multiple diagnoses on individual patient in a single day. Also a doctor may perform a service without
making a diagnosis, for example: allergy injections given on a biweekly basis or a scheduled
immunization.
Reporting & Query Requirements
The Pediatric Medical Center’s new medical information system must support the following reporting
and query requirements: 1. What services does the doctor perform? Display the service code and
description. Sort by service code. 2. Same report as above but only for laboratory services. 3. What PMC
patients of live in Elkhorn, Maine? Display the family number, the parent’s last and first names in
separate columns, city, state, and phone number. 4. Patient List Report. Display the family number,
parent’s last and first name separated by a comma, patient’s first name, and patient’s last name. Sort by
family number and patient’s first name. Do not repeat the family number or the parent’s last and first
name. 5. Patient Insurance Report. Display the insurance carrier, family number, parent’s last and first
name separated by a comma, patient’s first and last name, and insurance assigned social security
number. Sort by insurance carrier, family number and social security number. Do not repeat the
insurance company, family number, or the parent’s first and last name. 6. Daily Non-Insurance Billings
Report (parents that are self-insured). Display the date of service (formatted as mm/dd/yy), family
number, parent’s last and first names separated by a comma, the patients first and last name, insurance
carrier, service code, description of service and the service fee. The report is to total the service fee by
family and a grand total is to appear at the end of the report. The service fee is to be formatted as
currency. This report is to contain appropriate descriptive column headings (no abbreviations), Report
Title, and Report Footer. The report is to be sorted by family number. The report is to prompt the user
to enter the date the services were performed. 7. Same report as above but this time for all insurance
carriers (parents that have medical insurance). Do not include Medical Assistance patients. 8. Patient
Medical History Report. Display the date of diagnosis (formatted as Mmm dd, yyyy), family number,
parent’s last and first names separated by a comma, the patients first and last name, DRG code, and
diagnosis description. The output is to be sorted by date. This report is to contain appropriate
descriptive column headings (no abbreviations), Report Title, and Report Footer. The report is to be for a
single patient and the user is to be prompted to enter the patient’s MRN. 9. What patients have had the
same services performed on them as another patient? Display family number, patients first and last
names, service code, and service description. This query is to be completely data driven, which means
the only information available to the user running this query is the comparison patient’s MRN. Include
the comparison patient in the output. 10. This report to display the families that have less patients in
their family then the average number of patients per family for the city in which they reside. Display the
family number, parent’s first and last name, the number of patients in the family, city, and the average
number patients per family in a city. Sort the output by city and number of patients in the family.
CURRENT CHALLENGES FACING THE ORGANIZATION
The business analyst began to match up the functional requirements with the features available in
various “off the- shelf” software packages. But to no avail, no standard software package was able to
meet all of the critical requirements. There were lots of standard packages that handled patient billing
and medical histories. A few of the packages even had very good HMO billing and tracking capabilities.
But, the real issue was that no package could handle the integration of state’s requirements for Medical
Assistance billing into a single comprehensive package. This is a significant issue as the county and state
have estimated that approximately 25-30% of the medical center patients will be on Medical Assistance.
The business analyst reviewed her findings with the County Manager and the Director of Information
Systems. They understood the situation and asked the analyst to make a recommendation. She stated
that the only real alternative was for the county to internally develop the software for PMC’s medical
information system. The Director of Information Systems said that his department already had a backlog
of work and it would probably take months to get the software written. Frustrated, the County Manager
exclaimed that the Pediatric Medical Center needed the software yesterday and that the staff was
currently doing everything by hand! The business analyst then stated the project could easily be done
utilizing a standard relational data base management system (RDBMS). The Director of Information
Systems agreed. In order to speed up delivery of the system, the county decided to contract with a
consultant to design and implement the database. In addition, the consultant was to develop the
procedures to populate the database, as well as, write many of the required queries. After the
consultant completed the work, the county’s Information Systems group would then use the tools that
came with the RDBMS to construct the user interface, build the input and query forms, and develop
reports based on the consultant’s queries.

QUESTIONS
Question 1: The DAMA DMBOK textbook describes the following two core activities as part of the Data
Architecture management exercise: “Understanding enterprise information needs” and “Develop and
Maintain the Enterprise Data Model”.
Explain these activities, citing relevant academic sources where appropriate. Your discussion should not
simply contain general definitions but should consider the context of the organization described in the
case study. For instance, how would each of the components in an Enterprise Data Model apply to the
healthcare provider described?
Further – consider what makes this particular industry unique from others. Data management is
constrained by certain requirements such as government regulations, business concerns and legitimate
needs. If any of these areas are relevant to the healthcare industry then these should be mentioned in
your discussion.
All sources used should be referenced appropriately using APA referencing style. Your answer to this
question should be around 1500 words excluding the reference list.
(50 marks)
Question 2: Now focusing on the specific requirements described in the case study. If we were to build a
database to support these needs our first task is to create a suitable data model. This should capture the
information requirements of the proposed system and show all cardinality, keys and be implementable
in a relational database. (25 marks)
The deliverable for this question should be structured as follows:
a)  Table listing the entities and their descriptions (5)
b)  A suitable ERD diagram drawn in Microsoft Visio using the notation described in the lecture (15)
c)  Any comments or assumptions that were made during the development of the model should be
documented in this section as well. (5)