Mountain View Community Hospital Chapter 5

1
Mountain ViewCommunity HospitalChapter 5

• By Twila Armstrong, Alan Clark, Ben Johnson,
  Bridgette Nathan, Jon Templeton, and Ashley Wilson

2
Introduction
This chapter continues the case with emphasis on
logical design for the relational data model.
The relational data model is most commonly used
in contemporary database applications. The
object of logical design is to translate the
conceptual design into a logical database design
that can be implemented on a chosen database
management system. The resulting databases must
meet user needs for data sharing, flexibility,
and ease of access, which the MVCH has found to
be very satisfying so far.
3
Project Question 1Why will Mountain View
Community Hospital continue to use relational
technology for systems development, despite the
continuing emergence of newer technology?
4

• MVCH will continue to use the relational
  technology because
• The relational data model is most commonly used
  in contemporary database applications.
• The present relational systems are stable and
  support existing operations quite well.
• The present IS staff is trained and experienced
  in using this technology.
• Conversion to newer technology would be costly
  and would entail a number or risks

5
Project Question 2Should Mountain View
community Hospital use normalization in
designingits relational databases? Why or Why
not?
6

• Yes, Mountain View Community Hospital should use
  normalization because it can be a fundamental
  tool to validate and enhance a logical design.
  When normalization is used it can satisfy certain
  constraints and avoid unnecessary data
  duplication.

7

• Data normalization will decompose relations
• with anomalies to bring forth smaller, well
• structured relations. Normalization lets data
• be free of discrepancies that would result
• otherwise when the relations are modified.

8
Project Question 3Why are entity integrity
and referential integrity constraints of
importance to the hospital.

• The entity integrity constraint assures that
  every relation has a primary key, that the data
  values for that primary key are all valid, and
  that every primary key attribute are non-null.
  MVCH can efficiently utilize this rule by making
  all primary key attributes known and each having
  values that apply.
• The referential integrity constraint assures
  consistency among the rows of two relations.
  There are several ways for MVCH to utilize this
  rule. The most efficient way is to place a null
  value in a foreign key.

9
Project Question 4
Who in the hospital should be involved in data
normalization?
Everyone at MVCH who will be using the data
should be advised during the normalization
process, but the Database Analyst should be in
charge of normalization. Also, consulting all
users will ensure that the meanings and uses of
the data are fully understood.
10
Project Exercise 1
Map the E-R diagram to a relational schema, using
the techniques described in the chapter.
11
E-R Diagram From Ch. 3
12
(No Transcript)
13
EER Diagram Ch. 4
14
(No Transcript)
15
Cont. from previous diagram.
16
Project Exercise 2 Diagram the functional
dependencies for each relation.
17
(No Transcript)
18
(No Transcript)
19
(No Transcript)
20
(No Transcript)
21
Project Exercise 3

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

Since all relations are in Second Normal Form
(2NF) and there are no transitive dependencies
that exist, all relations are therefore in Third
Normal Form (3NF). .
22
Project Exercise 4Creating Enterprise Keys
for The E-R Diagram and ERR Diagram

• Enterprise key is a primary key whose value is
  unique across all relations.
• The function of the enterprise key is database
  evolvability merging new relations into a
  database once the database is created.
• By creating an enterprise key from the very
  beginning primary keys never have to be changed,
  which can add cost to most DBMS. The object
  created remains the same and prevents foreign key
  ripple effect.

23
(No Transcript)
24
(No Transcript)
25
(No Transcript)
26
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.
27
CREATE TABLE OBJECT (OID VARCHAR(5) Primary
Key, Object_Type VARCHAR(20)) CREATE TABLE
EMPLOYEE (OID VARCHAR(5) Primary Key,
Employee_NO VARCHAR(5), Employee_Name VARCHAR(2
0), Foreign Key(OID) References OBJECT(OID))
CREATE TABLE WARD (OID VARCHAR(5) Primary Key,
Ward_NO VARCHAR(5), Ward_Name VARCHAR(20),
Employee_OID VARCHAR(5) references
EMPLOYEE(OID), Foreign Key(OID) References
OBJECT(OID))
28
CREATE TABLE ASSIGNED (OID VARCHAR(5) Primary
Key, WARD_OID VARCHAR(5) references WARD(OID),
EMPLOYEE_OID VARCHAR(5) references
EMPLOYEE(OID), Hours NUMBER(4,2), Foreign
Key(OID) References OBJECT(OID)) CREATE TABLE
PHYSICIAN (OID VARCHAR(5) Primary Key,
Physician_ID VARCHAR(5), Physician_Name VARCHA
R(20), Foreign Key(OID) References
OBJECT(OID))
29
CREATE TABLE PATIENT (OID VARCHAR(5) Primary
Key, Patient_No VARCHAR(5), Patient_Name
VARCHAR(20), PHYSICIAN_OID VARCHAR(5)
references PHYSICIAN(OID), Foreign Key(OID)
References OBJECT(OID)) CREATE TABLE
BED (OID VARCHAR(5) Primary Key, Bed_No
VARCHAR(3), Ward_OID VARCHAR(5) references
WARD(OID), Room_No VARCHAR(5),
Patient_OID VARCHAR(5) references PATIENT(OID),
Foreign Key(OID) References OBJECT(OID))
30
CREATE TABLE TREATMENT (OID VARCHAR(5) Primary
Key, Treatment_No NUMBER(5), Treatment _Name
VARCHAR(20), Foreign Key(OID) References
OBJECT(OID)) CREATE TABLE PERFORMS (OID VARCHA
R(5) Primary Key, PATIENT_OID VARCHAR(5)
references PATIENT(OID), PHYSICIAN_OID VARCHAR(5
) references PHYSICIAN(OID), TREATMENT_OID
VARCHAR(5) references TREATMENT(OID),
RESULTS VARCHAR(20), Foreign Key(OID)
References OBJECT(OID))
31
CREATE TABLE ITEM (OID VARCHAR(5) Primary Key,
Item_No VARCHAR(5), Description
VARCHAR(20), Unit_Cost NUMBER(5,2), Foreign
Key(OID) References OBJECT(OID)) CREATE TABLE
CONSUMES (OID VARCHAR(5) Primary Key,
PATIENT_OID VARCHAR(5) references
PATIENT(OID), ITEM_OID VARCHAR(5) references
ITEM(OID), DATE DATE, QUANTITY NUMBER(5),
Foreign Key(OID) References OBJECT(OID))
32
Project Exercise 6

• What are some problems that may occur while
  trying to merge relations?

33
Project Exercise 6

• Redundancy- this is when different relations
  refer to the same entities
• Synonyms this occurs when two (or more)
  attributes having different names but the same
  meaning
• Homonym this is when an attribute may have more
  than one meaning
• Transitive Dependencies is when a functional
  dependency occurs between two (or more) nonkey
  attributes.
• Supertype/Subtype Relationship these are
  relationships that may be hidden in user views or
  relations

34
Example of Redundancy

• Suppose you have
• EMPLOYEE1 (Employee_ID, Name, Address, Phone)
• And you have
• EMPLOYEE2 (Employee_ID, Name Address, Jobcode,
  No_Years)
• Since these two relations have have the same
  primary key (Employee_ID), they are likely
  describing the same entity and can be merged
  together.
• Merging Results
• EMPLOYEE (Employee_ID, Name, Address, Phone,
  Jobcode, No_Years)

35
Example of Synonyms

• Consider the following relations
• STUDENT1 (Student_ID, Name)
• STUDENT2 (Matriculation_No, Name, Address)
• In this case, both Student_ID and
  Matriculation_No are synonyms for a persons SSN
  and are identical attributes.
• Merging results
• STUDENT (SSN, Name, Address)

36
Example of Homonyms

• Consider the following example
• STUDENT1 (Student_ID, Name, Address)
• STUDENT2 (Student_ID, Name, Phone_No, Address)
• With further research, it might be noticed that
  the address for STUDENT1 is for campus address
  and the address for STUDENT2 is for permanent (or
  home) address.
• Merging Results
• STUDENT (Student_ID, Name, Phone_No,
  Campus_Address, Permanent_Address)

37
Example of Transitive Dependencies

• When two 3NF relations are merged to form a
  single relation, transitive dependencies may
  result
• STUDENT1 (Student_ID, Major)
• STUDENT2 (Student_ID, Advisor)
• At first glance, it may look like problem of
  redundancy but what is Advisor is functionally
  dependant on Major
• Major ? Advisor
• Results
• STUDENT (Student_ID, M a j o r)
• MAJOR ADVISOR (Major, Advisor)
• Major becomes a foreign key STUDENT.

38
Example of Supertype/Subtype Relationships

• Suppose you have the following hospital relations
  (for example MVCH)
• PATIENT1 (Patient_ID, Name, Address)
• PATIENT2 (Patient_ID, Room_No)
• At first glance, it looks like these two
  relations can be merged into a single PATIENT
  relation (redundancy). However, there are
  suspensions that there are two different types of
  patients resident patients and outpatients.
  PATIENT1 actually contains attribute common to
  all patients. PARIENT2 contains an attribute
  (Room_No) that is a characteristic only of
  resident patients.
• In this situation, there should be
  supertype/subtype relationships for these
  entities
• PATIENT (Patient_ID, Name, Address)
• RESIDENT PATIENT (Patient_ID, Room_No)
• OUTPATIENT (Patient_ID, Date_Treated)

39
Student Questions
40
Bridgettes Question

• Why would it important for MVCH to understand how
  to merge relations?

41
Alans Question

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

42
Jons Question
MVCH has many relations between its numerous
entities. List the 6 properties of relations as
addressed in this chapter.
43
Bens Question

• What are transitive dependencies and how do they
  apply to relational model?

44
Twilas Question
What purpose does the enterprise key serve with
the MVCH case?
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 (an 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.
45
Ashleys Question

• What is normal form and what steps would Mountain
  Valley Community hospital use in normalization?
• Normal form is a state of relation that results
  from applying simple rules regarding functional
  dependencies.
• 1.)First normal form (INF) Contains no
  multi-valued attributes so there is a single
  value at the intersection of each row and column
  of the table.