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

Download this article: JISE - Volume 14 Number 3, Page 275.pdf

Recommended Citation: Ballenger, R. M. (2003). Teaching Case: A Database Design and Development Case: Elk County Pediatric Medical Center. Journal of Information Systems Education, 14(3), 275-284.