A Case Scenario Approach

This chapter covers the most important steps in designing a database. Detailed explanations are given that talk about database design concepts in depth. When designing a database model in the real world, you will also be given a particular scenario. The scenario often will take the form of sample documents and interview notes.

This chapter uses a case scenario to describe the current situation, problems, and changes a fictitious firm is undergoing. All chapters in this book contain a case study at the end to help drive home the ideas behind the chapter. This entire chapter, however, is based on a case scenario to help illustrate the process of database design point by point. It guides you through the process of deducing all entities (tables), attributes ( columns ), and relationships from a hospital scenario. The material is presented in this manner to better illustrate the process of and steps undergone in data modeling.

Case Scenario: Lloyd's Hospital

Lloyd's Hospital is one of the major hospitals in the city of Etmodsirch. It has as many as 1000 new patients each month and approximately 300 employees , including more than 160 doctors , surgeons, professors, and nurses. Lloyd's Hospital currently has at least 2000 beds and maintains highly sophisticated utilities, machinery, and robotics .

Lloyd's Hospital's business goals are in providing the highest quality care to individuals and the community, advancing in care through excellence in medical research, and educating future academic and practice leaders of the health care professions . Lloyd's Hospital is among the prominent hospitals in the city of Etmodsirch.

Lloyd's Hospital also provides diverse training opportunities for nurses and allied health care professionals. Lloyd's Hospital aims to serve the community as a public health advocate and provide support and services that respond to the area's health care needs through health education, health promotion, and access to care.

Because of numerous complaints on the extreme inefficiency of the current complex database, CEO John Patterson has made the decision to acquire a completely different database design. His requirement is to have a new database designed from scratch that will make the different processes that take place in Lloyd's more efficient. He gives you a brief overview of the current situation at Lloyd's:

"Our current patient registry exceeds 200,000 entries, and each entry contains detailed information about our patients. When patients come here for the first time, they are required to register into our catalog, filling in information such as their names and contact addresses. Approximately a thousand new patients register with us in a single month. We need a new registering design that will not only improve information access but will also decrease the time needed to enter the information.

We have 300 employees, most of whom work with a permanent contract, but some work on an hour-to- hour basis. Doctors specialize in different divisions, such as psychology and dermatology. Employees who are new adapt to the environment fairly quickly with the help of senior doctors. Each employee is placed into a single division, such as cardiology or dermatology. An ID card is granted to each employee, showing the division he belongs to. After employees hold a valid staff ID card, they are granted permission into the computer security organizational unit for the division.

The security host for the hospital is a set of organizational units (OU) within a Windows 2000 domain. There is a separate OU for each division of the hospital.

We also have a large selection of medicine available at our internal pharmacy, ranging from high blood pressure tablets to medicine for the common cold. The pharmacy is available to all our doctors, and medicine can be bought by any of our registered patients with an appropriate prescription or through a prescription from another facility or physician . Over-the-counter medications can be purchased by the general public, as well. Some of the medications are government-controlled substances that need to be monitored more closely. All our medicine is kept organized so that it may be found easily when needed. One particular problem exists in that our medicine stock is renewed every two days, making it difficult to maintain this level of organization.

We have a sophisticated array of machinery such as kidney and x-ray machines. These machines are kept in various labs, such as x-ray and blood labs, and can be accessed only by specialized doctors within these labs.

And with the introduction of our new academic facility, we offer different training opportunities for those interested. These training courses cover everything from surgery to medicine. Our educational facility is located within our campus beside the hospital proper and can accommodate numerous trainees."

With this information, you can begin designing a new data model in accordance with the current situation at Lloyd's Hospital. Realizing that this information is somewhat incomplete, you will have to make a few assumptions along the way. But first, you need a little background on relational databases and the modeling of such data. In an ideal situation, sample documents and full descriptions of all procedures that interact with the data would also be used as an aid to the development of the data model.