Mountain View Community Hospital

1
Mountain View Community Hospital

• Chapter 5, Team 6
• Joe Lewis
• Justin Smith
• Josh Matheney
• Brad Swink

2
Summary

• We will focus on logical design for the
  relational data model. MVCH will continue to
  evaluate newer object-oriented and
  object-relational technology, however, it is
  expected that relational technology will continue
  to dominate its systems development over the next
  few years. The databases must meet user needs for
  data sharing, ease of access, and flexibility,
  which MVCH has done so far.

3
Project Question 1

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

4
Project Question 1 Answer

• MVCH will continue to use relational technology
  for several reasons
• The present 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 MVCH use normalization in designing its
  relational databases? Why or why not?

6
Project Question 2 Answer

• Yes, MVCH should use normalization in designing
  its relational database to avoid anomalies
  (errors or inconsistencies) that impair data
  quality.

7
Project Question 3

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

8
Project Question 3 Answer

• Entity integrity helps assure that two entities
  (such as patient or tests) are not confused.
• Referential integrity helps assure that one
  entity (such as 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
Project Question 4 Answer

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

11
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.

12
E-R Diagram From Chapter 3
13
Project Exercise Answer 1a
14
(No Transcript)
15
EER Diagram from Ch. 4
16
Project Exercise Answer 1b
To employee
17
From Person
To patient
18
Project Exercise 2

• Diagram the functional dependencies in each
  relation.

19
Project Exercise 2 Answer

• A functional dependency is a constraint between
  two attributes.
• A constraint is a rule that cannot be violated by
  database users.
• The functional dependency of attribute B is
  diagramed in the schemas on the previous slides
  by an arrow that goes from attribute A to
  attribute B.
• Example

20
Functional Dependencies from the E-R Diagram (Ex.
2, Ch. 3)
21
Functional Dependencies from the E-R Diagram (Ex.
2, Ch. 3) continued
22
Functional Dependencies from the E-R Diagram (Ex.
1, Ch. 4)
23
Functional Dependencies from the E-R Diagram (Ex.
1, Ch. 4) continued
24
Project Exercise 3

• Question
• If any relation is not in 3NF, decompose that
  relation and revise the relational schema.
• Answer
• All relations are in 2NF and there are no
  transitive dependencies that exist. All relations
  are in Third Normal Form.

25
Project Exercise 4

• Create enterprise keys for all relations and
  redefine all relations.

26
Creating Enterprise Keys

• 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 have to be changed. This
  is done by merging new relations into a database
  once the database is created.
• Applying an enterprise key prevents a foreign key
  ripple effect, which can add extensive costs to
  the DBMS. An object that is created will remain
  the same, and it will retain its identifier
  throughout its lifetime.

27
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 indicated which subtype each
  instance of the supertype belongs.
• OID (object ID), known as the object identifier,
  is a new enterprise key attribute placed in each
  row. This allows for all subtypes to have the
  same primary key. An object identifier has no
  business meaning. However, it makes the primary
  key of a relation become a value internal to DS.

28
Enterprise key for E-R diagram
29
(No Transcript)
30
Enterprise key for EER diagram
31
(No Transcript)
32
(No Transcript)
33
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.

34
CREATE TABLE Commands for E-R Diagram
35
CREATE TABLE OBJECT (OID VARCHAR2(5)
Primary Key, Object Type VARCHAR2(20)) CREA
TE 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))
36
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))
37
CREATE TABLE PATIENT (OID VARCHAR2(5) Primary
Key, Patient_NO VARCHAR2(5), Patient_Name VARCH
AR2(20), Physician_OID VARCHAR2(5), 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), Pat
ient_OID VARCHAR2(5) references PATIENT
(OID), Foreign Key (OID) References Object
(OID))
38

• 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
  TREATMETN(OID),
• Results VARCHAR2(20),
• Foreign Key (OID) References OBJECT (OID))

39
CREATE TABLE ITEM (OID VARCHAR2(5) Primary
Key, Item_No VARCHAR2(5), Description VARCHAR
2(20), Unit_Cost NUMBER(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(5) references
Item(OID), Date DATE, Quantity Number(5), F
oreign Key (OID) references OBJECT(OID))
40
CREATE TABLE Commands for EER Diagram
41
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(20), State VARCHAR2(2), Zip
VARCHAR2(10), Phone VARCHAR2(14), foreign key
(OID) references OBJECT (OID)) 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))
42
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)) CREATE PATIENT 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)) CREATE TABLE
CARE_CENTER (OID VARCHAR2(5) Primary Key,
Name VARCHAR2(20), Location
VARCHAR2(20), foreign key (OID) references
OBJECT (OID))
43
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)) 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_ID VARCHAR2(5) references
EMPLOYEE (OID), Skill VARCHAR2(10), foreign
key (OID) references OBJECT (OID)) CREATE TABLE
LABORATORY (OID VARCHAR2(5) Primary
Key, Name VARCHAR2(20), Location
VARCHAR2(20), foreign key (OID) references
OBJECT (OID))
44
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)) 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_ID VARCHAR2(5) references PATIENT
(OID), foreign key (OID) references OBJECT
(OID))
45
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) CREATE TABLE VISIT
(OID VARCHAR2(5) Primary Key,
O_PERSON_OID VARCHAR2(5) references
OUTPATIENT, Date Date, Comments
VARCHAR2(50), foreign key (OID) references
OBJECT (OID)
46
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.

47
Project Exercise 6 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.

48
Joes Question

• What is a enterprise key and how does it
  influence the MVCH case?

49
Joshs Question

• What are the three reasons why merging relations
  are important? Give an example of a merging
  relationship for the MVCH case.

50
Justins Question

• Assume MVCH wanted to use CREATE TABLE commands
  to add Nurse to the relational diagram what
  commands would be needed to do this?

51
Brads Question

• Assume that we wish to create a new relation
  called prescription for MVCH. Create the new
  relation schema and create table commands for
  prescription. Assume prescription has the
  following attributes physician_ID, patient_ID,
  prescription_ID, date, comments.

52
This concludes our presentation

• Questions?