ICT616 Data Resources Management
Semester 2, 2018
This is an individual assignment for each student.
You should submit your assignment from the ICT616 LMS site using the Assignment course
Late submissions will be penalised at the rate of 10 marks per day late or part thereof.
You must keep a copy of the final version of your assignment as submitted and be prepared to
provide it on request.
The University treats plagiarism, collusion, theft of other students’ work and other forms of
dishonesty in assessment seriously. Any instances of dishonesty in this assessment will be forwarded
immediately to the Faculty Dean. For guidelines on honesty in assessment including avoiding
plagiarism, see: http://our.murdoch.edu.au/Educational-technologies/Academic-integrity/
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
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. 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 have 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 fulfil 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
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.
Below are the specifications contained in the Data and Information Requirements section of the PMC
Functional Requirements Report.
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.
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 parents have any type of
medical insurance, including Medical Assistance, then the parents 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
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.
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.
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
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. Which
PMC patients 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
Mmmdd, 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.
Q1. (50 marks) 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?
Q2. (50 marks) Consider what makes this particular industry unique from others. Data management is
constrained by certain requirements such as (a) government regulations, (b) business concerns and (c)
legitimate needs. These should be mentioned in your discussion.
To address Q2a you must locate legislation or rules specific to Australia that pertain to the collection,
storage or use of medical or healthcare records. These may consist of general privacy legislation as
well as specific acts relating to healthcare.
All sources used should be referenced appropriately using APA referencing style. Your whole
answer to these questions should be around 1500 words excluding the reference list.
Note: The case study may provide context which will help you to describe the Enterprise Data
Model in Q1. However Q2 is more general in nature and you will find more suitable sources
(likely online) for information about Australian legislation.