A database design and development case: Elk County Pediatric Medical Center

Journal of Information Systems Education, Fall 2003 by Ballenger, Robert M

ABSTRACT

This case provides a real-world end-of-semester project-oriented case study for students enrolled in an introductory database management course. The case consists of a business scenario to provide background information on the need for the application and some of the unique operating characteristics of the Elk County Pediatric Medical Center. In addition, narrative information regarding the functional requirements of the medical center is included along with sample data: parents, patients, services performed, diagnosis codes, insurance carriers, and patient history. The case provides sufficient information to design a moderately complex database for the medical clinic. The functional requirements will force students to resolve numerous many-to-many relationships. In addition, several entities have compound unique identifiers resulting in tables with composite primary and foreign keys. The case provides sufficient real-world data to operationalize the database design into a physical database, populate it with data, and then write a series of queries that satisfy the stated reporting requirements of the medical center. The queries vary considerably in terms of complexity, from s impie straightforward queries to others that are quite complex and require multiple sub-queries. Several queries are dependent upon parameters entered at runtime. Some queries all students should be able to answer, while others require critical thinking skills to solve. The case was written so that creation of the physical database and queries were not dependent on the student's database management software. Teaching notes containing suggested instructions, a possible entity-relationship model, the resulting physical database, and the solution to the queries are also provided.

Keywords: Database design, database modeling, database development, systems analysis and design, systems development, case

1. CASE 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 a vailable. 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.

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

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