Mountain View Community Hospital

1
Mountain View Community Hospital

• MIS 2403 800 TR
  October 16, 2003
• Team 6 Chapter 5
• Terri Pool
• Shawn Mueller
• Brandon Guynes

2
Project Case

• This is a continuation of the previous MVCH
  cases. This case involves special emphasis on
  logical design for the relational data model at
  MVCH. It is expected that relational technology
  will continue to dominate MVCHs systems
  development over the next few years, although
  MVCH will continue to evaluate newer
  object-oriented and object-relational technology.

3
Project Exercise 1 Map the E-R (Ch. 3, project
exercise 2) and the EER (Ch. 4, project exercise
1) to a relational schema.
4
Ch. 3 ERD, cont.
5
(No Transcript)
6
Ch. 4 EER, cont.
7
Project Exercise 2 3 2) Diagram the
functional dependencies in each relation. All
functional dependencies are included. 3) If any
relation is not in 3NF, decompose that relation
into 3NF revise the relational schema. All
relations diagrammed are in 3NF.
8
Project Exercise 6
Merge the relations of PE1 into a single set of
3NF relations.
9
(No Transcript)
10
Terris Question What is a synonym and what are
some attributes affected by this situation in the
attempt to merge the Ch. 3 ERD and the Ch.4 EER?
11
Project Exercise Question 4
Create enterprise keys for all relations and
redefine all relations.
12
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 Data Base Management
Systems. An object that is created will remain
the same, and it will retain its identifier
throughout its lifetime.
13
How Enterprise Key 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.

14
Enterprise Key for E-R Diagram
15
(No Transcript)
16
Enterprise Key for EER Diagram
17
(No Transcript)
18
(No Transcript)
19
Project Question 1

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

20
Project Question 1 Answer

• MVCH will continue to use relational technology
  for several reasons
• The current IS personnel at MVCH is trained and
  experienced in using relational technology.
• The current relational systems are stable and
  support existing operations.
• Converting to new technology would involve risks
  and costs.

21
Project Question 2

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

22
Project Question 2 Answer

• Yes, MVCH should use normalization in designing
  its relational database because normalization
  helps avoid anomalies that impair data quality.

23
Shawns Question

• Why is it important for the Mountain View
  Community Hospitals database designers to
  understand why merging relations (also called
  view integration) is important to their logical
  design process?

24
Project Question 3

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

25
Project Question 3 Answer

• Entity integrity and referential integrity are
  important because
• Entity integrity helps assure that two real-world
  entities (such as patient or tests) 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).

26
Project Question 4

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

27
Project Question 4 Answer

• Everyone that uses data at MVCH should be
  consulted during the normalization process to
  ensure that the meaning and usage of data have
  been understood correctly.

28
Project Exercise 5

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

29
Project Exercise 5 Answer
CREATE TABLE Commands for E-R Diagram
30
(No Transcript)
31
(No Transcript)
32
Create Table Commands for EER DIAGRAM
33
(No Transcript)
34
(No Transcript)
35
(No Transcript)
36
Brandons Question

• MVCH wants to create a new relation called
  Critical Care Unit. Create the new relation
  schema for critical care unit.