Database Design:

The Continental Palm Hospital Case

In this case, you need to design the physical data model (Physical ERD) of a database for the continental palm hospital. Now that your data storage design is complete you need to develop the Data Flow Diagram (DFD and ensure it is Balanced as discussed in class and in your slides.

For your physical ERD, make sure that you have ALL of the following considered:

Types of Files (Master, Look-Up, Transaction, Audit Historical).

Entities, Relationships and Attributes

Any constraints that you couldnt express using the model should be clearly written down in plain English

The Continental Palm Hospital (CPH) is considering creating a database to keep track of all aspects of the hospital. Impressed by your MIS knowledge and skills, the management of CPH has decided to hire you to design a conceptual schema first.

CPH wants to store the information about employees, patients, wards, beds, and treatments in the database.

Each employee has a SSN, name, and salary. For some types of employees, additional information should be recorded. For a doctor, the highest degree obtained and where and when the degree is obtained should be recorded. For a nurse, the level of skill (1 to 5) should be recorded.

Each patient has a SSN, a name, an address (street, city, state, and zip), and several contacts (name and phone number). The patients disease should also be recorded.

CPH has several wards with different functions. Each ward has a number, name, main function, and number of beds.

Each ward has several beds that may contain patients. Beds are numbered within the ward. Two wards could both have a bed 123. The patient will be placed in a bed in a ward relevant to her disease.

Several nurses work in each ward and one of them is in charge of the ward. The nurse in charge of a ward must have a level of at least 4. There must be at least the same number of nurses as the number of beds in any ward.

The patient will be treated by some doctors with various treatments. Each treatment will begin at a specified date and place, and will be associated with a fee. A unique treatment number is assigned to each treatment. A doctor can not treat more than 20 patients on any single day.