Jyotsna Kotur

Document Sample
Jyotsna Kotur Powered By Docstoc
					Database Design Course Project   Valley Greek Community Hospital             Jyotsna Kotur
                                                                       Sumanth Govindappa


                                                                   Jyotsna Kotur
                                                                   Sumanth Govindappa

                     Valley Creek Community Hospital Database

a. Project Description
As a large service organization, Valley Creek Community Hospital depends on a
large number of persons for its continued success. There are four groups of
people on whom the hospital is most dependent: employees, physicians, patients
and volunteers. Of course some common attributes are shared by all of these
persons: Person_ID (identifier), Name, Address, City/State/Zip, Birth_Date, and
Phone. Each of the four groups has at least one unique attribute of its own.
Employees have a Date_Hired, volunteers have a Skill, Physicians have a
Specialty and Pager#, and patients have a Contact_Date.

Additional personnel in the hospital community do not belong to one of these
four groups (their numbers are relatively small). However, a particular person
may belong to two (or more) of these groups at any given time (for example,
Patient and Volunteer).

Each patient has one (and only one) physician responsible for that patient. A
given physician may not be responsible for a patient at a given time or may be
responsible for one or more patients. Patients are divided into two groups:
resident and outpatient. Each resident has a Date_Admitted attribute. Each
outpatient is scheduled for zero or more visits. The entity visit has two
attributes: Date (partial identifier) and Comments. Notice that an instance of
visit cannot exist without an outpatient owner entity.

Employees are subdivided into three groups: nurse, staff, and technician. Only
nurses have the attribute Certificate, which indicates the qualification. Only staff
has the attribute Job_Class, and only technicians have the attribute skill. Each
nurse is assigned to one (and only one) care center. Examples of care centers are
Maternity, Emergency, and Cardiology. Attributes of the care center are Name
(identifier) and Location. A care center may have one or more nurses assigned to
it. Also for each care center, one of the nurses assigned to that care center is
appointed nurse_in_charge. A nurse cannot be appointed nurse_in_charge of a
care center unless she or he has an RN certificate.

Each technician is assigned to one or more laboratories. Attributes of laboratory
include Name (identifier) and Location. A laboratory must have at least one
technician assigned to it and may have any number of technicians assigned.




                                               1
Database Design Course Project   Valley Greek Community Hospital              Jyotsna Kotur
                                                                        Sumanth Govindappa


There may be no beds assigned to a care center, or a care center may have one or
more beds (up to any number) assigned to it. The only attribute of bed is Bed_Id
(identifier). Bed_Id is a composite attribute, with components Bed# and Room#.
Each resident patient must be assigned to a bed. A bed may or may not have a
resident patient assigned to it at a given time.




b. Project Questions
    1. yes, it is important to model the superclass/ subclass relationships in such
       databases due to the significance each subset like physicians have amoung
       entities like persons , such subsets by themselves should be represented as
       separate entity. They hold common attributes with other entities like
       employee, patient and volunteer, these attributes are grouped in person
       entity. Each of these subsets has superclass/subclass relationship with the
       person entity. If superclass/subclass relationships are not modeled the
       common attributes has to be part of the subclasses. This makes the design
       little harder to maintain. Hence modeling superclass/subclass
       relationships are necessary.



    2.
                 For the resident patient’s we can maintain the discharged date – the
                  day when the patient is discharged
                 We can maintain the laboratory usage by the patients, information
                  like which patient used which laboratory on what date.
                 We could add business rules for maintaining the billing system like
                  the visit charges for a outpatient, bed and room charges for resident
                  patient
                 We could maintain the physicians consultation timings and the
                  physicians appointments
                 Physicians may belong to different care centers like maternity,
                  cardiology and emergency.



    3. We have used a RDBS like oracle for the following advantages
          Possible to design complex data storage and retrieval systems with
            ease
          Avoids redundant data
          Provides for central storage with security


                                               2
Database Design Course Project   Valley Greek Community Hospital              Jyotsna Kotur
                                                                        Sumanth Govindappa


                 Support for very large databases
                 Automatic optimization of searching (when possible)
                 RDBMS have a simple view of the database that conforms to much
                  of the data used in businesses.
                 Standard query language (SQL)
                 Concurrency Control and Transaction Management
                 Backup and Recovery
                 Supports multiple users




c. EER Diagram - (II a)
Identification of Entities and their attributes

According to the requirements given we can identify the following entities

    1. Person - generalized entity with the common attributes person_id, name,
       address, City/State/Zip, birth_date and phone#

    2. Employee with attribute date_hired

    An employee can be classified under 3 sub entities as below
         a. Nurse with attributes certificate and carecenter
         b. Staff with attribute job_class
         c. Technician with simple attribute skill and multiple attribute
            laboratory(1 or more)

    3. Physician with attributes specialty, pager# and multivalued attribute
       patient(0 or more)

    4. Patient with attributes contact_date and physician_responsible
       Patients can be classified under 2 sub entities as below

             a. Resident_Patient with attributes date_admitted and bed
             b. Outpatient with multivalued attribute visit

    5. Volunteer with attributes skill

    6. Visit with attributes date, comments and outpatient, visit is a weak entity
       type and the owner entity for this is outpatient




                                               3
Database Design Course Project        Valley Greek Community Hospital            Jyotsna Kotur
                                                                           Sumanth Govindappa


    7. Care Center with simple attributes name, location, nurse_in_charge and
       multivalued attributes nurse (1 or more), bed(0 or more)

    8. Bed with composite attribute bed_id with individual components bed#,
       room# and simple attributes care center and resident_patient ( 0 or 1)

    9. Laboratory with simple attributes name, location and multivalued attribute
       technician(1or more)

                                              PERSON
     person_id, name, address(localaddress,City,State,Zip), birth_date and phone#

                                            EMPLOYEE
                                              date_hired

                                               NURSE
                                        certificate, carecenter

                                                STAFF
                                               job_class

                                          TECHNICIAN
                                          skill, {laboratory}

                                            PHYSICIAN
                                     Speciality, pager#, {patient}

                                              PATIENT
                                 contact_date, physician_responsible

                                     RESIDENT_PATIENT
                                         date_admitted, bed

                                          OUTPATIENT
                                                 {visit}

                                           VOLUNTEER
                                                  Skill

                                                VISIT
                                     date, comments, outpatient

                                          CARECENTER
                          name, location, nurse_in_charge,{nurse},{ bed}



                                                    4
Database Design Course Project     Valley Greek Community Hospital               Jyotsna Kotur
                                                                           Sumanth Govindappa


                                               BED
                       bed_id(bed#, room#), carecenter, resident_patient

                                      LABORATORY
                                  name, location,{technician}

       figure 1. Preliminary design for Valley Creek Community Hospital Database


Identification of relationship types.

By changing the attributes that represent relationships into relationship types.
We get the following relationship types.

1.responsible – 1:N relationship type between physician and patient. patient’s
participation is total and physician’s partcipation is partial.

2. assigned_nurses – 1:N relationship type between carecentre and nurse. Both
carecentre’s and nurse’s participation is total.

3. nurse_in_charge – 1:1 relationship type between carecentre and nurse.
carecentre’s participation is total and nurse’s participation is partial.

4. assigned_beds – 1:N relationship type between carecentre and bed. Carecentre’s
participation is partial and the bed’s participation is total. (Assumption : Each
bed must belong to a care center)

5. assigned_residentpatient – 1:1 relationship type between resident_patient and
bed. bed’s participation is partial and resident_patient’s participation is total.

6. assigned_technicians – M:N relationship type between laboratory and
technician. Both laboratory’s and technicians’s participation is total.

7. outpatient_visits – 1:N relationship type between visit and outpatient. This is an
identifying relationship for weak entity visit. visit’s participation is total and
outpatient’s participation is partial.

SuperClass/Subclass relationships

1. Person entity is specialized into {Employee, Physicians, Patient, Volunteer}
   subclass entities each with specialized attributes and relations, person
   participation is not total because its given in requirements that additional
   personals exists apart from these four subclass entities. It’s also an overlay
   specialization as one person can belong to one or more subclass entities.


                                                 5
Database Design Course Project   Valley Greek Community Hospital               Jyotsna Kotur
                                                                         Sumanth Govindappa



2. Employee entity is specialized into {Nurse, staff, Technician}. It’s a disjoint
   specialization as employee can be any one of the later.

3. Patient entity is specialized into {Residentpatient, Outpatient}. It’s a disjoint
   specialization as a patient can be either resident patient or outpatient.

Conceptual Design

Conceptual design is shown below using the Enhanced Entity Relationship model




                                               6
Database Design Course Project   Valley Greek Community Hospital         Jyotsna Kotur
                                                                   Sumanth Govindappa




d. Relational Schema after Normalization – (III a)




                                               7
Database Design Course Project             Valley Greek Community Hospital               Jyotsna Kotur
                                                                                   Sumanth Govindappa

PERSON
Person_id       Name        Local Address           City       State     Zip   Birth_date   Phone#

STAFF
Staff_id          Job_class


EMPLOYEE
Emp_id               Date_hired


PHYSICIAN
Physician_id        Pager#         speciality


PATIENT
Patient_id          Contact_date        Physician_id


 RESIDENT_PATIENT
Resident_Patient_id Date_admitted               Room#          Bed#


OUTPATIENT
Outpatient_id

VISIT`
Outpatient_id           Date         Comments



NURSE
Nurse_id        Certificate        Care_center_name


NURSE_IN_CHARGE
Nurse_in_charge_id


CARE_CENTER
Name    Location               Nurse_in_charge_id


BED
Room#        Bed#      Care_center_name



VOLUNTEER
Volunteer_id        skill


TECHNICIAN
Technician_id          Skill


ASSIGNED_TECHNICIAN
Technician_id Lab_Name


LABORATORY
Name    Location




                                                           8
Database Design Course Project      Valley Greek Community Hospital                      Jyotsna Kotur
                                                                                   Sumanth Govindappa


e. SQL Statements
III c – Creation of Tables with primary key and foreign key

    1.   create table person(person_id INT NOT NULL,name varchar(100) NOT
         NULL,local_Address varchar(200),city varchar(100),state varchar(100),zip INT,birth_date
         DATE,phone int,primary key(person_id));

    2.   create table employee(emp_id int not null,date_hired date, primary key(emp_id), foreign
         key(emp_id) references person(person_id));

    3.   create table physician(physician_id int not null,pager_no int,speciality char(20),primary
         key(physician_id), foreign key(physician_id) references person(person_id));

    4.   create table patient(patient_id int not null,contact_date date,physician_responsible int
         not null,primary key(patient_id), foreign key(patient_id) references
         person(person_id),foreign key(physician_responsible) references
         physician(physician_id));

    5.   create table volunteer(volunteer_id int not null,skill char(20) not null,primary
         key(volunteer_id), foreign key(volunteer_id) references person(person_id));

    6.   create table outpatient(outpatient_id int not null,primary key(outpatient_id), foreign
         key(outpatient_id) references patient(patient_id));

    7.   create table care_center(name char(30) not null,location
         varchar(100),cnurse_in_charge_id int not null, primary key(name));

    8.   create table bed(bed_no int not null,room_no int not null, bcare_center_name
         char(30),primary key(bed_no,room_no), foreign key(bcare_center_name) references
         care_center(name));

    9.   create table Resident_patient(Resident_patient_id int not null,date_admitted
         date,rbed_no int not null,rroom_no int not null,primary key(resident_patient_id), foreign
         key(resident_patient_id) references patient(patient_id),foreign key(rbed_no,rroom_no)
         references bed(bed_no,room_no));

    10. create table nurse(nurse_id int not null,certificate char(30), ncare_center_name char(30),
        primary key(nurse_id), foreign key(ncare_center_name) references
        care_center(name),foreign key(nurse_id) references employee(emp_id));

    11. create table nurse_in_charge(nurse_in_charge_id int not null,primary
        key(nurse_in_charge_id),foreign key(nurse_in_charge_id) references nurse(nurse_id));

    12. create table technician(technician_id int not null,tech_skill char(30), primary
        key(technician_id), foreign key(technician_id) references employee(emp_id));

    13. create table staff(staff_id INT not null,job_class char(20),primary key(staff_id), foreign
        key(staff_id) references employee(emp_id));

    14. create table laboratory(name char(30) not null,location varchar(100), primary key(name));


                                                  9
Database Design Course Project      Valley Greek Community Hospital                       Jyotsna Kotur
                                                                                    Sumanth Govindappa



    15. create table assigned_technician(atech_id int not null,alab_name char(30) not
        null,primary key(atech_id,alab_name), foreign key(atech_id) references
        technician(technician_id), foreign key(alab_name) references laboratory(name));

    16. create table visit(voutpatient_id int not null,visit_date date, comments varchar(1000),
        primary key(voutpatient_id,visit_date), foreign key(voutpatient_id) references
        outpatient(outpatient_id));

    17. ALTER TABLE care_center ADD CONSTRAINT fk foreign key(cnurse_in_charge_id)
        references nurse_in_charge(nurse_in_charge_id);

III e – Creation of views

    1.   create view emphired as select name,date_hired from person,employee where
         person_id=emp_id;

    2.   create view NursesInCharge as select care_center.name as CareCenter,person.name as
         NurseName,phone from person,care_center where
         cnurse_in_charge_id=person_id;

    3.   create view goodTechnician as select * from technician where tech_skill is not null;

    4.   Create view careCenterBeds as select B.care_center.name as Care_center,
         count(B.bed_no) as Total_Beds, count(R.rbed_no) as Assigned_Beds, Total_Beds -
         Assigned_Beds from bed as B left outer join Resident_Patient as R on B(bed_no,room_no)
         = R(rbed_no,rroom_no) group by B.care_center.name

    5.   create view OutpatientsNotVisited as select outpatient_id from outpatient left outer join
         visit on outpatient_id=voutpatient_id where date is null;
         else
         create view OutpatientsNotVisited as select outpatient_id as Patient from outpatient
         except select distinct voutpatient_id as Patient from visit

III f – Queries

    1.   select staff_id,name,job_class from staff,person where staff_id=person_id group by
         job_class;

    2.   select * from volunteer where skill Is NULL;

    3.   select patient_id from patient,volunteer where patient_id=volunteer_id;

    4.   select person_id,name from vist,person where voutpatient_id=person_id group by
         person_id,name having count(*) = 1;

    5.   select skill, count(id) From technician As T(id,skill) natural outer join volunteer As
         E(id,skill) group by skill;

    6.   select Care_center from careCenterBeds where Total_Beds=Assigned_Beds;




                                                  10
Database Design Course Project       Valley Greek Community Hospital                   Jyotsna Kotur
                                                                                 Sumanth Govindappa


    7.     select nurse_in_charge_id from nurse_in_charge As N Left outer join care_center As C on
           N.nurse_in_charge_id = C.cnurse_in_charge_id where C.name is NULL;

    8.     select nurse_id from nurse where Exists ( select * from care_center where
           C.cnurse_in_charge_id = nurse_id and care_center.name = ncare_center_name );

    9.     select name from laboratory where not exists (select atech_id as id from
           assigned_technician where alab_name=name INTERSECT select technician_id as id from
           technician where tech_skill is NULL)

    10. select Resident_patient_id from Resident_patient where date_admitted > All (select
        date_hired from employee);

    11. select patient_id from patient, Resident_patient where patient_id=Resident_patient_id
        AND date_admitted BETWEEN contact_date and (contact_date + 7days);

    12. select outpatient_id from patient,visit where voutpatient_id=patient_id and
        contact_date-visit_date >7 except where (select outpatient_id from
        patient,visit where voutpatient_id=patient_id and contact_date-visit_date <=7) union
        OutpatientsNotVisited

    13.


    14. select Physician-id from physician, (patient left outer join Resident_patient on
        patient_id=Resident_patient) left outer join outpatient on patient_id=outpatient_id
        where physician_id=physician_responsible Group by Physician-id having
        count(outpatient_id) > count(Resident_patient)

    15. Query cannot be executed with the current database design




f. Dependency diagram – (III b)

PERSON
Person_id       Name     Local Address      City        State      Zip      Birth_date   Phone#



STAFF
Staff_id          Job_class




EMPLOYEE
Emp_id              Date_hired




                                                   11
Database Design Course Project            Valley Greek Community Hospital         Jyotsna Kotur
                                                                            Sumanth Govindappa

PHYSICIAN
Physician_id        Pager#        speciality




PATIENT
Patient_id          Contact_date       Physician_id




RESIDENT_PATIENT
Resident_Patient_id Date_admitted              Room#        Bed#




OUTPATIENT
Outpatient_id



VISIT`
Outpatient_id           Date        Comments




NURSE
Nurse_id        Certificate       Care_center_name




NURSE_IN_CHARGE
Nurse_in_charge_id



CARE_CENTER
Name    Location              Nurse_in_charge_id




BED
Room#        Bed#      Care_center_name




VOLUNTEER
Volunteer_id        skill




                                                       12
Database Design Course Project   Valley Greek Community Hospital         Jyotsna Kotur
                                                                   Sumanth Govindappa




TECHNICIAN
Technician_id         Skill



ASSIGNED_TECHNICIAN
Technician_id Lab_Name




LABORATORY
Name    Location




                                              13