You are a database developer for a certain hospital and your assignment is to develop a database. You conduct interviews with a number of hospital administrators and staff to identify the key entity types for the hospital. After a short time you have identified the following entity types:

Care center a treatment center within the hospital. Examples of care centers are maternity and emergency care. Each care center has a care center ID (identifier) and a name.

Patient a person who is either admitted to the hospital or is registered in an outpatient program. Each patient has a patient number (identifier) and name.

Physician a member of the hospital medical staff who may admit patients to the hospital and who may administer medical treatments. Each physician has a physician ID (identifier) and name.

Bed a hospital bed that may be assigned to patient who is admitted to the hospital. Each bed has a bed number (identifier), description, and unit cost.

Item any medical or surgical item that may be used in treating a patient. Each item has an item number (identifier), description, and unit cost.

Employee any person employed as part of the hospital staff. Each employee has an employee number and name.

Treatment any test or procedure performed by a physician on behalf of a patient. Each treatment has a treatment ID that consists of two parts: treatment number and treatment name.

You next recorded the following information concerning relationship:

Each hospital employee is assigned to work in one or more care centers. Each care center has at least one employee, and may have any number of employees. The hospital records the number of hours per week that a given employee works in a particular care center.

Each care center has exactly one employee who is designated nurse in charge for that care center.

A given patient may or may not be assigned to a bed (since some patients are outpatients). A bed may or may not be assigned to a patient.

A patient must be referred to the hospital by exactly one physician. A physician may refer any number of patients, or may not refer any patients.

Physicians may perform any number of treatments on behalf of any number of patients, or may not perform any treatments. A patient may have treatments performed by any number of physicians, for each treatment performed on behalf of a given patient by a particular physician, the hospital records the following information: treatment date, treatment time, and results.

A patient may optionally consume any number of items. A given item may be consumed by one or more patients, or may not be consumed. For each item consumed by a patient, the hospital records the following: date, time, quantity, and total cost.

Task:

Develop a logical design for the above conceptual design.

Map the E-R diagram to a relational schema.

Diagram the functional dependencies in each relation.

If any relation is not in 3NF, decompose that relation into 3NF relations and revise the relational schema.