Mountain View Community Hospital

1
Mountain View Community Hospital

• Chapter 6
• Team 7
• Team Presenters
• Kathryn Porter
• Siau-Whei Liouh
• Mitch Trotter
• Josh Melton
• Josh Michaelis

2
Introduction

• We have been developing a database for
• Mountain View Community Hospital
• throughout the preceding chapters. In this
• project case we will make some physical
• database design decisions based on the
• conceptual and relational database developed
• in Chapters 3, 4, and 5.

3
Introduction (Cont.)

• Mountain View Community hospital has
• decided to use a relational database
• management system (DBMS), Oracle, for this
• database.

4
Project question 1

• What additional kinds of information, besides the
  3NF relations you developed in the Project Case
  in Chapter 5 for the conceptual database of
  Chapter 3, do you need to do physical database
  design for this database?

5
Project Question 1 - Answer

• The additional requirements is as follows
• Normalized relations, including volume estimates
  (data volume and frequency-of-use statistics,
  representing them by adding notation to the EER
  diagram.)
• Definitions of each attribute
• Descriptions of where and when data are used
  entered, retrieved, deleted, and updated
  (including frequencies)
• Expectations or requirements for response time
  and data security, backup, recovery, retentions,
  and integrity

6
Project Question 1 Answer cont.

• Descriptions of the technologies (database
  management systems) used for implementing the
  database. The efficient use of secondary storage
  is influenced both by the size of physical record
  and structure of secondary storage. Hence we need
  to know the page size, whether a physical record
  is allowed to span two pages, and the blocking
  factor, etc.

7
Project Question 2

• Are there opportunities for horizontal or
  vertical partitioning of this database?
• If you are not sure, what other information would
  you need to answer this question with greater
  certainty?

8
Project Question 2- Answer

• Yes, if the database files are distributed across
  multiple machines, there are opportunities for
  both horizontal and vertical partitioning.
• Horizontal partitioning distributes the rows of a
  table into several separate files, based upon
  common column values.
• Vertical partitioning distributes the columns of
  a table into several separate physical records,
  repeating the primary key in each of the records.
• For data that is needed to be viewed together,
  the SQL UNION operator may be used to combine
  separate tables with similar structures into one
  table.

9
Project Question 3

• Case Figure 1 shows an initial composite usage
  map for a portion of the Mountain View Community
  Hospital database. Since the usage map was
  developed, a few assumptions about the use of the
  data have changed

10
Project Question 3 (cont)

• There is an average of 12 (rather than 10) item
  consumptions per patient.
• There is an average of 40(rather than 30) times
  per hour that performance data are accessed for
  patients, and each time, the corresponding
  treatment data are also accessed.
• Draw a new version of Figure 1 reflecting this
  new information.

11
Project Question 3 (example)
12
Project exercise 1

• In Project Exercise 5 from Chapter 5, you wrote
  CREATE TABLE commands for each relation in the
  logical database for the Mountain View Community
  Hospital conceptual database of Chapter 3.
  However, you did so not fully understanding the
  physical database design choices you might have
  available to you in Oracle (or whatever DBMS you
  are using for this project(. Reconsider you
  previous CREATE TABLE commands in answering the
  following questions

13
Question A

• Would you choose different data types for any
  fields? Why?

14
Question A -- Answer

• Yes, we would. A choice like SMALLINT instead of
  INTEGER data type would help in minimizing
  storage space. VARCHAR instead of CHAR would
  dynamically allocate memory space for the
  adjacent fields, and again will help in
  minimizing storage space.

15
Question B

• Are any field candidates for coding? If so, what
  coding scheme would you use for each of these
  fields?

16
Question B -- Answer

• Yes. In the PATIENT_CHARGES table in the next
  slide, the ITEM_DESCRIPTION field has a limited
  number of possible values. By creating a code or
  translation table, each field value can be
  replaced by a code, a cross-reference to the
  look-up table, similar to a foreign key.

17
Patient charges
18
Code look-up table
19
Question C

• Which fields may take on a null value?

20
Question C -- Answer

• Any fields except for the primary key fields and
  required fields.

21
Question D

• Suppose the dates of performing a treatment were
  not entered. What procedures would you use for
  handling these missing data? Can you and should
  you use a default value for this field? Why or
  why not?

22
Question D -- Answer

• The date of a treatment on a patient is of
  great importance as a reference for the purposes
  of consequent diagnosis or treatment procedures
  and for risk management of potential liability.
  Therefore, it may be a good idea to substitute an
  estimate of the missing value, and warn the user
  of this substitution. In this case, a preferable
  option is to track missing data so that special
  reports and other system elements cause people to
  quickly resolve unknown values.

23
Project Exercise 2

• In Project Question 2, you were asked to identify
  opportunities for data partitioning. Besides
  partitioning, do you see other opportunities for
  denormalization of the relations for this
  database?
• If not, why not? If yes, where and how might you
  denormalize?

24
Project Exercise 2- Answer

• PATIENT and TREATMENT records could be
  denormalized by including information about
  treatments received as a column(s) in the PATIENT
  table.
• When this table is accessed, the TREATMENT data
  will show up without any further access to
  secondary memory.
• However, each time that we need to find
  information that would associate the PHYSICIAN
  with a TREATMENT performed by him or her, both
  the PHYSICIAN and PATIENT tables would still need
  to be accessed.

25
Project Exercise 2- Example
Normalized
PATIENT
Patient_No
Name
Address
Physician_ID
PERFORMANCE
Patient_No
Physician_ID
Treatment_No
Results
PHYSICIAN
TREATMENT
Physician_ID
Name
Treatment_No
Treatment_Name
Date
Denormalized
PATIENT_TREATMENT
Patient_No
Name
Address
Physician_ID
Treatment_No
Treatment_Name
Date
26
Project Exercise 3

• Referring to the updated composite usage map
  created for Project Question 3, do you see any
  opportunities for clustering rows from two or
  more tables? Why or why not?

27
Project Exercise 3 Answer

• Clustering reduces the time to access related
  records compared to the normal allocation of
  different files to different areas of a disk.
• Clustering records is best used when the records
  are fairly static. Since the number of accesses
  per hour for each of the tables are represented
  by relatively large numbers, updates and deletes
  are most likely to happen often for all records.
• The patient records are the most dynamic. Items,
  physicians, and treatments are much more static
  data, so one could consider clustering them with
  the patient record.

28
Project Exercise 4

• Write CREATE INDEX commands for the
• primary key indexes of each table in the
• Mountain View Community Hospital
• database.

29
Project Exercise 4- Answer

• PATIENT
• CREATE UNIQUE INDEX PATINDEX ON
  PATIENT(PATENT_NO, PHYSICIAN_ID)
• CONSUMPTION
• CREATE UNIQUE INDEX CONSINDEX ON CONSUMPTION
  (ITEM_NO, PATIENT_NO, DATE)
• ITEM
• CREATE UNIQUE INDEX ITEMINDEX ON ITEM (ITEM_NO)

30
Project Exercise 4- Answer (Cont.)

• PERFORMANCE
• CREATE UNIQUE INDEX PERINDEX ON PERFORMANCE
  (PATIENT_NO, PHYSICIAN_ID, TREATMENT_NO)
• TREATMENT
• CREATE UNIQUE INDEX TREATINDEX ON TREATMENT
  (TREATMENT_NO)
• PHYSICIAN
• CREATE UNIQE INDEX PHYSINDEX ON PHYSICIAN
  (PHYSICIAN_ID)

31
Project Exercise 5

• Consider the following query against the MVCH
  database For each treatment performance in the
  past two weeks, list in order by treatment ID and
  for each ID by date in reverse chronological
  order, the physicians performing each treatment
  (grouped by treatment) and the number of times
  this physician performed that treatment that day.
  Create secondary key indexes to optimize the
  performance of this query. Make any assumptions
  you need to answer this question.

32
Project Exercise 5 Answer

• Indexes will be created on the TREATMENT_DATE
  attribute. Assumptions Since Physician_ID and
  Treatment_ID are primary keys, indexes on them
  are created at the time of the CREATE_TABLE
  command executions.

33
Siau-Wheis Question

• What is the primary goal of physical database
  design? And why it is important to Mountain View
  Community Hospital?

34
Kathryns Question

• How are there opportunities for data partitioning
  within MVCH, and what would be some advantages
  and disadvantages of data partitioning?

35
Mitchs Question

• Update the MVCH composite usage map from
  Project Question 3 to reflect the following
  changed assumptions.
• There is an average of 14(rather than 12) item
  consumptions per patient.
• There is an average of 50(rather than 40) times
  per hour the performance data is accessed for
  patients, and each time, the corresponding
  treatment data are also accessed.

36
Josh Meltons Question

• Describe each element of the following
• Mountain View Community Hospital
• CREATE INDEX command for the ITEM
• table
• CREATE UNIQUE INDEX ITEMINDEX ON ITEM (ITEM_NO)

37
Josh Michaeliss Question

• Indexes can be created for a primary or
  secondary key or both?