Question

Westlake Research Hospital

It is imperative that the database be ready before the actual clinical trials begin. The staff at Westlake is anxious to see some results. It is time you show them the logical design of their database. Follow these steps:

1. Review all the requirements and business rules.

2. Define your entities and attributes and the relations that exist between them.

3. Create a logical model using crow’s feet notation in Visio or hand draw it on graph paper if you prefer.

4. Add all the entities and their attributes. You don’t need to worry about data types for now.

5. Identify the key fields for each entity and the foreign keys.

6. Analyze the diagram. Identify which role (i.e., domain, linking, lookup, or weak) each entity plays in your database.

.

Answer 1

I have done review of business rules and then defined the entities and attributes and I have also added the logical model ( ER diagram) and have identified the key vlaues for the different entities.Please find the answer attached below.

Ans )

************ Westlake Research Hospital Database Design ***********

1. REVIEW OF REQUIREMENT AND BUSINESS RULES :

Bussiness Objectives:

Objective 1) To design and develop a database system which is fast and secure and follows the 4 principles of Atomicity,Consistency, Isolation and Durability.

Objective 2) The second objective of this project is to provide an efficient database mangement system for the Research hospital.

Objective 3) Another objective of the  project is to achieve complete data storage and retrieval of the patients,doctors and other staffs in the hospital from this database management system.

Review and Requirement analysis : The given project is to design a hospital management database system with information about Doctors , Nursing staffs and Patients in the  Westlake Research Hospital .In this system for records the design is for DBMS that will have the data of doctors , nursing staff and patients.For each of these entities what variables should be used and it will have one or more keys and a range of values associated with them needs to be decided. The range of operations which will be valid for the data also needs to be reviewed.This design will result into efficient managing and searching of the records of Research Hospital wherein medical data is stored in computers using databases.

2.ENTITIES AND ATTRIBUTES AND THE RELATIONS :

Assumptions involved in the project are: Lets us assume that some doctors hospital staff and patients already a are part of the Research hospital. We assume the doctors, research hospital staff and patients are the 3 major entities of the hospital database. Also, each of these may have one or more keys(compound keys) and a range of values and the range of operations which will be needed for the database management system.

ENTITIES and ATTRIBUTES -->

1. Entity  : DOCTOR

  Attributes  : Doctor's ID ( This will be primary key) , doctors' name, qualification, department_name (foreign_key), timing , room no.

2. Entity  : PATIENT

  Attributes  : Patient's ID(Primary key ), Patient's name,age , gender ,disease, department_name, admit status

3. Entity  : HOSPITAL STAFF/ NURSING STAFF

  Attributes  : Staff_id(primary key), name , qualification ,deparment_name( foreign key),duty_timing

4. Entity  : DEPARTMENT

  Attributes  : Department_name( primary key ), Doctors_id(foreign key) , staff_id (foreign_key)

RELATIONSHIPS ---->

1.WORKS_IN : This is a relationship between the entity DOCTOR and DEPARTMENT.This is a many to one relationship because let us assume that many DOCTORS can work in one DEPARTMENT.

2.HAS :This is the relationship between DEPARTMENT entity and HOSPITAL_STAFF entity and it has many to many relationship because consider a case when a nurse is in Ortho department gets shifted to Dental department and there can be many such staff like sanitaion worker, security staff etc. (note : it is assumed that he/she can manage many department but one at a time).

3.TREATS :This is relationship between the DOCTOR AND the PATIENT entity .This is many to many relationship as more than 1 doctors can treat a patient having different diseases and at the same time many patients can also consult to more than 1 doctor.

4.IS_ADMITTED_IN : This is a relationship between the PATIENT and DEPARTMENT entitiy.This is many to one relation as many patients can be admitted to one department.

3. LOGICAL MODEL OR ER DIAGRAM :

Please find the attached ER diagram which shows the above described schema in terms of ENTITY and RELATIONSHIP involved in the database schema.

4. ADD ALL THE ENTITIES AND ATTRIBUTES

Please find the attached diagram and section 5 & 6 which has all the entities and attributes added.

5. KEY FIELD FOR EACH ENTITY AND FOREING KEY

1. Entity  : DOCTOR

  Attributes  : Doctor's ID ( This will be primary key) and department_name (foreign_key)

2. Entity  : PATIENT

  Attributes  : Patient's ID(Primary key )

3. Entity  : HOSPITAL STAFF/ NURSING STAFF

  Attributes  : Staff_id(primary key) and deparment_name( foreign key)

4. Entity  : DEPARTMENT

  Attributes  : Department_name( primary key ), Doctors_id(foreign key) , staff_id (foreign_key)

6. ANALYSIS OF THE DIAGRAM AND SCHEMA OF THE DATABASE :

Doctors:

The following list gives the Variables and their Range   

1. ID (Identifier) : This can be Varchar and  can have values like ABC01, xyz98 ( It has to be unique as this can be used as Primary key in data base storage)

2. Name : This will be a Varchar ( Can have same values as two people can have same name)

4. Qualification : Varchar ( possible values are BDS / MBBS or DNB)   

3.Specialisation : Varchar ( BUT WILL HAVE ONLY LIMITED VALUES LIKE HEART OR NEURO OR ORTHO etc).

4.Timing : Can range between 9 AM TO 9 PM ( assuming emergency is excluded)  

5.Room No.: This is an integer (This can have limited values based on the no. of rooms present)

Patients:

1.ID(identifier) : This can be int ( thus range of int is the range of identifier) and this is the Primary key.

2.Name :  This will be a Varchar

3.Disease : This will be a Varchar ( States the disease the patient is suffering from)

4.Gender : M/F (char)

5.Admit Status : Adimitted or OPD ( If admitted the duration of stay will be more than OPD)

6.Age : This will have integer value

Department :

1.Department_name: This is the primary key of this table and is varchar in datatype.

2.Doctor_ID : This is the foreign key from the doctor table.

3.Staff_ID : This is the foreign key from the staff table.

Nursing Staff:

1. Unique ID : This can be a Varchar can have values like staff1 or staff22 etc

2.Name : This will be a Varchar   

3.Qualification : Varchar ( Can have vlaues like Bsc)

4.Timing : Can range between 9 AM TO 9 PM ( assuming emergency is excluded)  

5.Department : This can have string values equal to the no of department (like orthopedic, pediatrics etc)