Transcript @C=What about Performance? Surely all Those Tables Will Slow

Chapter 4
Subtypes & Supertypes
Author: Graeme C. Simsion and Graham C. Witt
Drug Expenditure Database
Model as Relations (Tables)
• OPERATION (Hospital Number*, Operation Number, Operation
Code*, Surgeon Number*)
• SURGEON (Hospital Number*, Surgeon Number, Surgeon
Specialty)
• OPERATION TYPE (Operation Code, Operation Name,
Procedure Group)
• STANDARD DRUG DOSAGE (Drug Short Name*, Method of
Administration, Size of Dose, Unit of Measure, Method of
Administration, Standard Cost of Dose Cost)
• DRUG (Drug Short Name, Drug Name, Manufacturer)
• HOSPITAL (Hospital Number, Hospital Name, Hospital
Category, Contact Person)
• DRUG ADMINISTRATION (Hospital Number*, Operation
Number*, Drug Short Name*, Method of Administration*, Size of
Dose*, Unit of Measure*, Method of Administration*, Hospital
Number*, Operation Number*, Number of Doses)
Copyright: ©2005 by Elsevier Inc. All rights reserved.
2
The tables
• Put simply, all tables become boxes in
the E-R model
• So let’s do it for our model…
Copyright: ©2005 by Elsevier Inc. All rights reserved.
3
Boxes (Rectangles) for Tables
in our Database
Hospital
Operation
Type
Surgeon
Operation
Drug
Standard
Drug Dosage
Drug
Admin
Copyright: ©2005 by Elsevier Inc. All rights reserved.
4
Representing Foreign Keys
• These are the attributes marked with *
(and may be part of the key)
• Foreign keys are links between tables
• So, we represent these links with lines,
but with special markings!
Copyright: ©2005 by Elsevier Inc. All rights reserved.
5
Drug Expenditure Database Model
as Entity-Relationship Model
Hospital
be
performed
at
be prescribed at
operate
at
be
operated
at by
be
managed
by
Operation
Type
be
classified
by
classify
Surgeon
perform
manage
Operation
Drug
follow
be of
use
be
available
in
Standard
Drug Dosage use
be used in
be used in
be
followed by
Drug
Admin
prescribe
Copyright: ©2005 by Elsevier Inc. All rights reserved.
6
What did we do?
• Each table is a box/rectangle (we call these entities)
• Each link via a foreign key is shown using a line with
some other markings (we’ll get to these). (These are
called relationships)
• Each box has a name that describes what each row
in the underlying table is about
• What do each of these mean?
• This higher level model is called the EntityRelationship Model… it is the architects view of the
database.
Copyright: ©2005 by Elsevier Inc. All rights reserved.
7
Representing Foreign Keys
•
Take the Operation and Surgeon tables. The primary key of Surgeon
(Hospital Number + Surgeon Number) appears in the Operation table
as a foreign key. Draw a line between the two boxes, and indicate the
direction of the link by putting a “crow’s foot” at the foreign key end
(Figure 3.3). You can think of the crow’s foot as an arrow pointing back
to the relevant surgeon for each operation.
Surgeon
Operation
Copyright: ©2005 by Elsevier Inc. All rights reserved.
8
Interpreting the Foreign Key
Link
• If presented only with this diagram, we could deduce
that:
– We want to keep data about Surgeons and Operations
because we have tables for each.
– Each operation can be associated with only one surgeon
(because the key of Surgeon can appear only once in each
row of the Operation table)
– Each surgeon could be associated with many operations
(because there is nothing to stop many rows of the
Operation table containing the same value for the foreign
key of Surgeon)
• In this diagram… the crows foot arrow shows this with the
crows foot at the Operation end of the link and the ‘other’ end
at the Surgeon end.
Copyright: ©2005 by Elsevier Inc. All rights reserved.
9
Verifying the Foreign Key Link
with a Business Specialist
• We could now ask a business specialist,
referring to the diagram: “Is it true that each
operation is performed by one surgeon only?”
• We could also ask, but it is obvious: “Is it true
that each surgeon can perform more than
one operation?”
• This second question is ridiculous here, but
may not be in other models you design!
Copyright: ©2005 by Elsevier Inc. All rights reserved.
10
The Response
• The client in fact confirms that only one surgeon should
be recorded against each operation but offers some
explanation: while more than one surgeon could in
reality participate in an operation, the client is only
interested in recording details of the surgeon who
managed the operation.
– This must be recorded to avoid the question being revisited,
and to specify more precisely what data will be held
– The question “In how many operations did surgeon number 12
at hospital number 18 participate?” cannot now be asked.
• We should also change the name of the Surgeon
Number column in the Operation table to “Managing
Surgeon Number”.
Copyright: ©2005 by Elsevier Inc. All rights reserved.
11
Amending the E-R Model
• The model is annotated so that the response
is kept
manage
Surgeon
Operation
be managed
by
Copyright: ©2005 by Elsevier Inc. All rights reserved.
12
Optionality in Foreign Key
Links
• Suppose our client says “We don’t usually involve a surgeon
when we are treating a patient with a small cut, but we still need
to record whether any drugs were used.”
– In this case, some rows in the Operation table may not contain a
value for Surgeon Number.
– We can show whether the involvement of a surgeon in an operation
is optional or mandatory (see below).
manage
Surgeon
Operation
be managed
by
Each operation must be
managed by a surgeon.
Each surgeon may
manage operations.
manage
Surgeon
Operation
be managed
by
Each operation may be
managed by a surgeon.
Each surgeon may
manage operations.
Copyright: ©2005 by Elsevier Inc. All rights reserved.
13
Drug Expenditure Database Model
(again)
Hospital
be
performed
at
be prescribed at
operate
at
be
operated
at by
be
managed
by
Operation
Type
be
classified
by
classify
Surgeon
perform
manage
Operation
Drug
follow
be of
use
be
available
in
Standard
Drug Dosage use
be used in
be used in
be
followed by
Drug
Admin
Copyright: ©2005 by Elsevier Inc. All rights reserved.
prescribe
14
Verifying the Model
•
Consider the relationship between Operation and
Operation Type: “Are we sure that each operation can be
of only one type?” There are at least two possibilities:
1. Allow only “simple” operation types such as “Gall Bladder Removal”
and “Appendectomy.” In this case, the model would need to be
redesigned, based on the operation type information being a
repeating group within the operation; or
2. Allow complex operation types such as “Combined Gall Bladder
Removal and Appendectomy.”
•
•
Both options are technically workable and the decision may
be made for us by the existence of an external standard.
Option 1 is more elegant, because “List all operations that
involved appendectomies,” will therefore be simpler to
specify and program.
This can be done for all relationships (foreign key links)
Copyright: ©2005 by Elsevier Inc. All rights reserved.
15
Redundant Arrows
• There are arrows from Hospital to Surgeon and from Surgeon to
Operation and one from Operation direct to Hospital.
• Does this third arrow add anything to our knowledge of the business
rules supported by the model?
• No - we know that each operation must be performed at one
hospital, but we know that each operation must be managed by a
surgeon and that each surgeon operates at a hospital (other arrows)
• Action: Remove the redundant link between Operation and
Hospital
• If it were possible for an operation to be recorded without a surgeon
(i.e., if the link to the Surgeon table were optional), we could not
remove the short-cut arrow (from Operation direct to Hospital).
• Figure 3.7 shows this generalized to other situations
Copyright: ©2005 by Elsevier Inc. All rights reserved.
16
The Final E-R Version of our
Database
Hospital
be
operated
at by
operate
at
be
managed
by
Operation
Type
be
classified
by
Surgeon
classify
manage
Operation
Drug
follow
be of
be
available
in
Standard
Drug Dosage use
be
followed by
be used in
Drug
Admin
Copyright: ©2005 by Elsevier Inc. All rights reserved.
17
What did we do again?
• Each table is a box/rectangle (we call these entities)
• Each link via a foreign key is shown using a line with
some other markings (we’ll get to these). (These are
called relationships)
• Each box has a name that describes what each row
in the underlying table is about
• Each relationship is clarified as being optional or
mandatory
• Redundant relationships are removed
• We reconstructed a model that relates to the
database created through normalization.
• We could design the model directly ‘top down’
Copyright: ©2005 by Elsevier Inc. All rights reserved.
18
Our Goal Revisited
• We want a design, made up of sound,
fully normalized tables, that meets our
criteria of completeness, nonredundancy, stability, flexibility,
communication, rule enforcement,
reusability, integration, and elegance—
not a mish-mash of business concepts.
Copyright: ©2005 by Elsevier Inc. All rights reserved.
19
Terminology
• Entity classes – categories of things of interest to
the business: represented by boxes on the diagram,
and generally implemented as tables
• Attributes – what we want to know about entitiy
classes: not usually shown on the diagram and
generally implemented as columns in tables
• Relationships – represented by lines with crows’ feet
(we will drop the term “arrow” now that we are talking
about conceptual models), and generally
implemented through foreign keys.
Copyright: ©2005 by Elsevier Inc. All rights reserved.
20
The Top-Down Approach:
Entity-Relationship Modeling
• Normalization seems to be a very long-winded way of
getting to the model…
• What if we had simply asked the client “What things
do you need to keep things about?”
• Normalization (or the principles of it) can be used to
verify that the model is in fact a ‘good’ one
structurally.
• It also means that we don’t need to start with a very
complex single table with all information in it (recall
the starting table)
• Seeing tables as we have helps us because it shows
what we are creating
Copyright: ©2005 by Elsevier Inc. All rights reserved.
21
Entity-Relationship Modeling
Defined
• E-R Modeling (for short) is the process
of designing appropriate classes of
entity classes, relationships, and
attributes to meet a business problem.
• We cover each of these in the next two
lectures
Copyright: ©2005 by Elsevier Inc. All rights reserved.
22