Mountain View Community Hospital

1
Mountain View Community Hospital

• Project Case
• Chapter 5
• Marsha Nebeker
• Patricia Cloninger
• Cliff Mullis
• Phil Amuzu

2
Mountain View Community Hospital

• This presentation continues the MVCH case with
  special emphasis on logical design for the
  relational data model. Although the hospital will
  continue to evaluate newer object-oriented and
  object-relational technology, it is expected that
  relational technology will continue to dominate
  its systems development over the next few years.
  The resulting databases must meet user needs for
  data sharing, flexibility and ease of access.

3
Project Question 1

• Why will Mountain View Community Hospital
  continue to use relational technology for systems
  development, despite the continuing emergence of
  newer technology?

4
Answer

• Mountain View Community Hospital will continue to
  use relational technology for several reason
• The present IS staff is trained and experienced
  in using this technology.
• The present relational systems are stable and
  support existing operations quite well.
• Conversion to newer technology would be costly
  and would entail a number of risks.

5
Project Question 2

• Should Mountain View Community Hospital use
  normalization in designing its relational
  databases? Why or why not?

6
Answer

• Yes Mountain View Community Hospital should use
  normalization in designing its relational
  database. Normalization helps avoid anomalies
  that impair data quality.

7
Project Question 3

• Why are entity integrity and referential
  integrity constraints of importance to the
  hospital?

8
Answer

• Entity integrity and referential integrity are
  important.
• Entity integrity helps assure that two real-world
  entities (such as patient or test) are not
  confused.
• Referential integrity helps assure that one
  real-world entity (such as a test result) is not
  lost or disassociated from its owner entity (such
  as patient).

9
Project Question 4

• Who in the hospital should be involved in data
  normalization?

10
Answer

• All users of data in the organization should be
  consulted during the normalization process to
  ensure that the meaning and usage of data have
  been understood correctly.

11
Marshas Question

• List two of the three reasons why Mountain View
  Community Hospital does not use newer technology
  for their database.

12
Project Exercise 1

• Map the E-R (and/or EER) diagram to a relational
  schema, using the techniques described in this
  chapter. Be sure to underline all primary keys,
  include all necessary foreign keys, and indicate
  referential integrity constraints.

13
E-R Diagram from Chapter 3
D_N_I_C designated Nurse in charge
14
E-R Diagram
15
Project Exercise 1a Answer
16
Project Exercise 1a Answer
17
EER Diagram from Chapter 4
18
Project Exercise 1b AnswerSchema for EER
Diagram (Ex 1 Ch 4)
19
Project Exercise 1b Answer Continued
20
Project Exercise 1b Answercontinued
21
Project Exercise 2

• Diagram the functional dependencies in
• each relation.
• The functional dependencies are diagramed in
  figure 1b.

22
Project Exercise 2 Answer

• Remember that a functional dependency is a
  constraint between two attributes.
• Example Attribute B (Skill) is functional
  dependent on Attribute A (V_Person_ID).

23
Functional Dependencies from the E-R Diagram
(Ex 2, Ch 3)
24
Functional Dependencies from the E-R Diagram (Ex
2, Ch 3) continued
25
Functional Dependencies from the EER Diagram (Ex
1 Ch 4)
26
Functional Dependencies from the EER Diagram (Ex
1 Ch 4)

• continued

27
Functional Dependencies from the EER Diagram (Ex
1 Ch 4)

• continued

28
Project Exercise 3

• If any relation is not in 3NF, decompose that
  relations and revise the relational schema.
• Since there are no transitive dependencies, all
  relations are in Third Normal Form.

29
Project Exercise 4

• Create enterprise keys for all relations and
  redefine all relations.
• An enterprise key is a primary key whose value is
  unique across all relations.
• The purpose of the enterprise keys is to provide
  primary keys that never change. This is done by
  merging new relations into a database once the
  database is created.
• An enterprise key can prevent extensive cost to a
  database by preventing a foreign ripple effect.
  An object that is created remains the same and
  retains its identifier throughout its lifetime.

30
How Enterprise Keys are applied to the relational
data model.

• A supertype named OBJECT for all relations is
  initially applied. It has a subtype discriminator
  (internal system attribute Object_type) that
  indicates which subtype each instance of the
  supertype belongs.
• OID (Object_ID), the object identifier, is a new
  enterprise key attribute placed in each row,
  allowing for all subtypes to have the same
  primary key. An Object_ID has no business
  meaning however, it makes the primary key of a
  relation become a value internal to DS.

31
Enterprise Key for E-R Diagram
32
Enterprise Key for E-R Diagram continued
33
Enterprise Key for EER Diagram
34
Enterprise Key for EER Diagram continued
35
Pattys Question

• What is an enterprise key and what is its
  purpose? Using the Enterprise Key for EER diagram
  of MVCH, slides 33 and 34, show any two schema
  rows with an enterprise key.

36
Project Exercise 5

• Write CREATE TABLE commands for each relation for
  your answer to Project Exercise 4. Make
  reasonable assumptions concerning the data type
  for each attribute in each of the relations.

37
CREATE TABLE Commands for E-R DIAGRAM

• CREATE TABLE OBJECT
• (OID VARCHAR2(5) Primary Key,
• Object_Type VARCHAR2(20)
• CREATE TABLE EMPLOYEE
• (OID VARCHAR2(5) Primary Key,
• Employee_No VARCHAR2(5),
• Employee_Name VARCHAR2(20),
• Foreign Key (OID) References Object(OID))
• CREATE TABLE WARD
• (OID VARCHAR2(5)Primary Key,
• Ward_No VARCHAR2(5),
• Ward_Name VARCHAR2(20),
• Employee_OID VARCHAR2(5) references
  EMPLOYEE(OID),
• Foreign Key (OID) References OBJECT(OID))

38

• CREATE TABLE ASSIGNED
• (OID VARCHAR2(5) Primary Key,
• Ward_OID VARCHAR2(5) references WARD(OID),
• Employee_OID VARCHAR2(5) references
  EMPLOYEE(OID),
• Hours NUMBER(4,2),
• Foreign Key (OID) References OBJECT(OID))
• CREATE TABLE PHYSICIAN
• (OID VARCHAR2(5) Primary Key,
• Physician_ID VARCHAR2(5),
• Physician_Name VARCHAR2(20),
• Foreign Key (OID) References OBJECT(OID))

39

• CREATE TABLE PATIENT
• (OID VARCHAR2(5) Primary Key,
• Patient_No VARCHAR2(5),
• Patient_Name VARCHAR2(20),
• PHYSICIAN_OID VARCHAR2(5) references
  PHYSICIAN(OID),
• Foreign Key (OID) References OBJECT(OID))
• CREATE TABLE BED
• (OID VARCHAR2(5) Primary Key,
• Bed_No VARCHAR2(3),
• Ward_OID VARCHAR2(5), references WARD(OID),
• Room_No VARCHAR2(5),
• Patient_OID VARCHAR2(5) references
  PATIENT(OID),
• Foreign Key (OID) References OBJECT(OID))

40

• CREATE TABLE TREATMENT
• (OID VARCHAR2(5) Primary Key,
• Treatment_No NUMBER(5),
• Treatment_Name VARCHAR2(20),
• Foreign Key (OID) References OBJECT (OID))
• CREATE TABLE PERFORMS
• (OID VARCHAR2(5) Primary Key,
• PATIENT_OID VARCHAR2(5) references PATIENT(OID),
• PHYSICIAN_OID VARCHAR2(5) references
  PHYSICIAN(OID),
• TREATMENT_OID VARCHAR2(5) references
  TREATMENT(OID),
• RESULTS VARCHAR2(20),
• Foreign Key (OID) References OBJECT(OID))

41

• CREATE TABLE ITEM
• (OID VARCHAR2(5) Primary Key,
• Item_No VARCHAR2(5),
• Description VARCHAR2(20),
• Unit_Cost VARCHAR(5,2),
• Foreign Key (OID) References OBJECT(OID))
• CREATE TABLE CONSUMES
• (OID VARCHAR2(5) Primary Key,
• PATIENT_OID VARCHAR2(5) references PATIENT(OID),
• ITEM_OID VARCHAR2(20) references ITEM(OID),
• DATE DATE,
• QUANTITY NUMBER(5),
• Foreign Key (OID) References OBJECT(OID))

42
CREATE TABLE Commands for EER DIAGRAM

• CREATE TABLE OBJECT
• (OID VARCHAR2(5) Primary Key,
• Object_Type VARCHAR2(20)
• CREATE TABLE PERSON
• (OID VARCHAR2(5) Primary Key,
• Person_ID VARCHAR2(5),
• Name VARCHAR2(20),
• Address VARCHAR2(30),
• Birth_Date Date,
• City VARCHAR2(5),
• State VARCHAR2(2),
• Zip VARCHAR2(10),
• Phone VARCHAR2(14),
• Foreign Key (OID) References OBJECT(OID))

43

• CREATE TABLE PHYSICIAN
• (OID VARCHAR2(5)Primary Key,
• PH_PERSON_OID VARCHAR2(5) references
  PERSON(OID),
• Pager_No VARCHAR2(14),
• Specialty VARCHAR2(20),
• foreign key (OID) references OBJECT(OID))
• CREATE TABLE PATIENT
• (OID VARCHAR2(5) Primary Key,
• PA_PERSON_OID VARCHAR2(5) references
  PERSON(OID),
• Contact_Date Date,
• PH_OID VARCHAR2(5) references Physician (OID),
• foreign key (OID) References OBJECT(OID))

44

• CREATE TABLE VOLUNTEER
• (OID VARCHAR2(5) Primary Key,
• V_PERSON_OID VARCHAR2(5) references PERSON(OID),
• Skill VARCHAR2(20),
• foreign key (OID) references OBJECT(OID))
• CREATE TABLE EMPLOYEE
• (OID VARCHAR2(5) Primary Key,
• E_PERSON_OID VARCHAR2(5) references PERSON(OID),
• Date_Hired Date,
• foreign key (OID) references OBJECT(OID))

45

• CREATE TABLE CARE_CENTER
• (OID VARCHAR2(5) Primary Key,
• Name VARCHAR2(20),
• Location VARCHAR2(20),
• foreign key (OID) references OBJECT(OID))
• CREATE TABLE NURSE
• (OID VARCHAR2(5) Primary Key,
• N_PERSON_OID VARCHAR2(5) references
  EMPLOYEE(OID),
• Certificate VARCHAR2(2),
• CARE_OID VARCHAR2(5) references
  CARE_CENTER(OID),
• foreign key (OID) references OBJECT (OID))

46

• CREATE TABLE STAFF
• (OID VARCHAR2(5) Primary Key,
• S_PERSON_OID VARCHAR2(5) references
  EMPLOYEE(OID),
• Job_Class Number(2),
• foreign key (OID) references OBJECT(OID)
• CREATE TABLE TECHNICIAN
• (OID VARCHAR2(5) Primary Key,
• T_PERSON_OID VARCHAR2(5) references
  EMPLOYEE(OID),
• Skill VARCHAR2(10),
• foreign key (OID) references OBJECT(OID)

47

• CREATE TABLE LABORATORY
• (OID VARCHAR2(5) Primary Key,
• Name VARCHAR2(20),
• Location VARCHAR2(20),
• foreign key (OID) references OBJECT(OID))
• CREATE TABLE LAB_ASSIGN
• (OID VARCHAR2(5) Primary Key,
• TECH_OID VARCHAR2(5) references
  TECHNICIAN(OID),
• LAB_OID VARCHAR2(5) references LABORATORY(OID),
• foreign key (OID) references OBJECT(OID))

48

• CREATE TABLE RESIDENT
• (OID VARCHAR2(5) Primary Key,
• R_PERSON_OID VARCHAR2(5) references
  PATIENT(OID),
• Date_Admitted Date,
• foreign key (OID) references OBJECT(OID))
• CREATE TABLE OUTPATIENT
• (OID VARCHAR2(5) Primary Key,
• O_PERSON_OID VARCHAR2(5) references
  PATIENT(OID),
• foreign key (OID) references OBJECT(OID))

49

• CREATE TABLE BED
• (OID VARCHAR2(5) Primary Key,
• Bed_No VARCHAR2(5),
• Room_No VARCHAR2(5),
• RES_OID VARCHAR2(5) references RESIDENT(OID),
• foreign key (OID) references OBJECT(OID))
• CREAT TABLE VISIT
• (OID VARCHAR2(5) Primary Key,
• O_PERSON_OID VARCHAR2(5) references
  OUTPATIENT(OID),
• Date Date,
• Comments VARCHAR2(50),
• foreign key (OID) references OBJECT(OID))

50
Project Exercise 6

• If you develop a logical design for the E-R
  diagram for both chapters, merge the relations
  into a single set of 3NF relations.

51
Answer

• You can use this exercise (or a selected subset)
  to illustrate the problems of merging relations
  described in the chapter. You can also use this
  exercise to anticipate the design of a data
  warehouse that consolidates user views (see
  Chapter 11).

52
Marsha and Pattys Question

• Create Table command for Person in the EER
  diagram.

53
Conclusion

• This concludes our presentation.
• Are there any questions.