ICT616 Data Resources Management- Punjab

MURDOCH UNIVERSITY

ICT616 Data Resources Management

Semester 2, 2018

ASSIGNMENT 1

Case study

Assignment Information

This is an individual assignment for each student.

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.

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/

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. 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

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 in 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 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

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. 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.

QUESTIONS

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.