Modeling 1 CIS 4365 Entity Relationship Diagrams Part 2 Your Individual Project 2 CIS 4365 Entity Relationship Diagrams We will develop the ERD as a class you must execute complete the project ID: 752849
Download Presentation The PPT/PDF document "Chapter3 : Entity-Relationship" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1
Chapter3:
Entity-Relationship Modeling:
1
CIS
4365 Entity Relationship Diagrams
Part 2Slide2
Your Individual Project2
CIS 4365 Entity Relationship Diagrams
We will develop the ERD as a class; you must execute complete the project
on your own
We will go to the lab to create the tables
I will give you the data to populate the tables
I will help you get you get started with your queries
PLEASE NOTE:
When I say Individual, I mean you should do it by
yourself
You will
not
learn if your friend gives you the answers
I
want you to learn!
I still get paid (not much).
I tried.
If you Won’t Learn, You are wasting your money!Slide3
Your Individual Project3
CIS 4365 Entity Relationship Diagrams
Consider the following:
“You work for a hospital. The Head Administrator has told you that he suspects that Dr. Smith, as well as all of the other physicians which she supervises, has been pre-scribing too much Codeine for their patients. He wants a list of all of Dr. Smith’s patients, their illnesses, and what
drugs
were given to those people. If it looks suspicious, he will later want the same list for each of the physicians Dr. Smith supervises.”
• There is
one additional constraint we will add:
“Each patient can have only one primary physician.”Slide4
Your Individual Project4
CIS 4365 Entity Relationship Diagrams
Simplifying/Elucidating the Problem Semantics:
“A
Physician
Treat
s many
Patient
s.”
Given
“A
Patient is Treated by only one Primary
Physician.”
Given
“Many Patients Suffer many Illnesses.”
Inferred
“Many Illnesses Require many Drugs”
Inferred
“A
Physician
Supervise
s many other
Physician
s.”
Inferred
“A
Physician
is
Supervise
d by another
Physician
.”
Inferred
We can now identify (based in the original Statement) All of the Entities and their Cardinality and, (implicitly), their constraints
“Many
Illness
es are Suffered by many
Patient
s.”
“Many
Drug
s
are Required by many Illnesses.”
Inferred
InferredSlide5
Your Individual Project5
CIS 4365 Entity Relationship Diagrams
1st Relationship:
Physician
Treats
Patient
Given 1 Physician, how many Patients?
Mandatory?
Given 1 Patient, how many Physicians?
Mandatory?Slide6
Your Individual Project6
CIS 4365 Entity Relationship Diagrams
1st Relationship:
Physician
Treats
Patient
Attributes??
Name
Address
Specialty
Name
Address
What is wrong with these attributes??Slide7
Your Individual Project7
CIS 4365 Entity Relationship Diagrams
1st Relationship:
What if, in addition to looking for a physician (or patient) only by name, we look for them by name AND address??
Probably
NOT
a good idea (think: George Foreman)
Solution
: Assign a
UNIQUE
identifier:
Physician
Treats
Patient
PhysID
Address
Specialty
Name
Name
Address
PatIDSlide8
Your Individual Project8
CIS 4365 Entity Relationship Diagrams
1st Relationship:
How do we relate a Physician to their Patients?
We
could
place
the Patient Information (e.g., using the primary key
PatID
) as an attribute in the Physician Entity
Physician
PhysID
Address
Specialty
Name
PatID
a
PatID
b
°°°°
°°
PatID
??
I think we have a problem!!!Slide9
Your Individual Project9
CIS 4365 Entity Relationship Diagrams
1st Relationship:
How do we relate a Physician to their Patients?
Because this is a relational database, we need what is the maximum number of patients a patient can have:
Physician
PhysID
Address
Specialty
Name
PatID
a
PatID
b
°°°°
°°
PatID
??
None? Maybe the physician has no patients presently admitted
One? Maybe they are the President’s Physician
A Million? Maybe they are
ambulance chasers
Let’s Assume the Maximum is 1,000.Slide10
Your Individual Project10
CIS 4365 Entity Relationship Diagrams
1st Relationship:
How do we relate a Physician to their Patients?
If these our field sizes are:
Physician
PhysID
Address
Specialty
Name
PatID
a
PatID
b
°°°°
°°
PatID
??
Field
#Bytes
PhysID
9
Name
30
Address
50
Specialty
30
119
Plus 9 bytes per Patient
That means that we would need to increase the number of bytes needed
FOR EACH RECORD
to:
119+ 1,000 * 9 =
9,119 Bytes
If there are 300 physicians in the
PHYSICIAN
Table, the table
will require 400 * 9,119
=
3,647,600 Bytes
(The vast majority of Patient fields will be NULL)
Versus
the 300 *119 =
35,700 bytes it would be otherwiseSlide11
Your Individual Project11
CIS 4365 Entity Relationship Diagrams
1st Relationship:
How do we relate a Physician to their Patients?
Note that we have seen this situation before:
Specialty
Physician
PhysID
Address
Name
PatID
a
°°°°
°°
PatID
??
Patients
A Multivalued Attribute – Repeating Groups
PhysID
Other
PatID1
PatID2
PatID3
°°°
123456789
°°°
543210987
°°°
°°°
°°°
234567890
°°°
765432109
321098765
°°°
°°°
345678901
°°°
109876543
654321098
210987654
°°°
456789010
°°°
432109878
109876543
987654321
°°°
PatID
Other
987654321
°°°
109876543
°°°
765432109
°°°
654321098
°°°
543210987
°°°
432109876
°°°
321098765
°°°
210987654
°°°
432109878
°°°
Not Good!Slide12
Your Individual Project12
CIS 5365 Entity Relationship Diagrams
1st Relationship:
How do we relate a Physician to their Patients?
Let’s try putting the foreign key in the Patient table
Physician
Treats
Patient
PhysID
Address
Specialty
Name
Name
Address
PatID
Physician
Note that because this is a 1:M relationship, we only have to add 1 additional field to the
Patient
TableSlide13
Your Individual Project13
CIS 5365 Entity Relationship Diagrams
1st Relationship:
How do we relate a Physician to their Patients?
Let’s try putting the foreign key in the Patient table
Notice the storage savings (Assume that table Patient, without the foreign key, require 100 bytes/record, & that there are 5,000 records)
Foreign Key in PHYSICIAN
Foreign Key in
Patient
PHYSICIAN
3,647,600 Bytes
Patient
500,000 Bytes
35,700
Bytes 545,000 Bytes
Both4,147,600 Bytes 580,700 Bytes
A savings of 3,566,900 BytesHow would the tables in this relationship look like?Slide14
Your Individual Project14
CIS 5365 Entity Relationship Diagrams
1st Relationship:
How do we relate a Physician to their Patients?
Physician Table Attributes/Fields
Patient Table Attributes/Fields
PhysID
Name
Specialty
Address
PatID
Name
Address
Physician
Is the field
Physician
actually placed in the
Patient
Table???
YES – See the next slide
1
MSlide15
Your Individual Project15
CIS 5365 Entity Relationship Diagrams
1st Relationship:
PhysID
°°°
123456789
234567890
345678901
456789012
567890123
Physician Table
PatID°°°
Physician255356278456789012344244256234567890399872889
234567890401293648
456789012450192243345678901589021099345678901620896798
567890123730927457456789012801928364123456789822981630
234567890Patient Table
Our first rule is that in a 1:M relationship, the foreign key
ALWAYS
goes on the side of the manySlide16
Your Individual Project16
CIS 5365 Entity Relationship Diagrams
1st Relationship:
What do we do with the composite key
ADDRESS
?
In this case, we simple decompose it into the fields we will use
PhysID
Name
Specialty
Street
City
State
ZipCode
PatID
Name
Street
City
State
ZipCode
Physician
Physician Table Attributes/Fields
Patient Table Attributes/Fields
1
MSlide17
Your Individual Project17
CIS 5365 Entity Relationship Diagrams
1st Relationship:
Our actual tables might appear as:
PhyID
Last
First
Street
City
State
Specialty
234567890
KervorkianJack32 ViscountEl PasoTXEuthanasia345678901Zhivago
Yuri19 Redd Rd
El PasoTXCardiology456789012RebenneckMac65 Piano St.La UnionNM
OrthopedicsPatID LastFirstStreetCityState
Physician255356278 SotomayorSonya12 OregonEl PasoTX456789012
399872889 BushW123 MesaEl PasoTX234567890589021099
MubarackHosni18 HideoutEl PasoTX345678901620896798 Martinez
Susana
7 Mayor St
Anthony
NM
456789012
730927457
Lohan
Lindsay
12 Necklace
El Paso
TX
345678901
We Now Know:
Sonya Sotomayor has Dr.
Rebenneck
as her physician
W. Bush has Dr.
Kervorkian
as his physician
Hosni
Mubarark
has Dr.
Zhivago
as his physician
Susana Martinez
has Dr.
Rebenneck
as her physicianSlide18
Your Individual Project18
CIS 5365 Entity Relationship Diagrams
2nd
Relationship:
Patient
Suffers
Illness
Given 1
Patient,
how many
Illnesses?
Mandatory?
Given 1
Illness,
how many
Patients?
Mandatory?
Note that the
true constraints of a problem are not always stated nor readily inferred ---Slide19
Your Individual Project19
CIS 5365 Entity Relationship Diagrams
2nd
Relationship:
What about the attributes of
Illness
?
Depends ---- let’s keep it
simple:
Illness
IllCode
DescriptionSlide20
Your Individual Project20
CIS 5365 Entity Relationship Diagrams
2nd
Relationship:
How do we relate a Patient to their Illnesses?
Let’s try putting the foreign key in table Patient
Patient
PatID
Address
Physician
Name
IllCode
a
IllCode
b
°°°°
°°
IllCode
??
OOPS!!!Slide21
Your Individual Project21
CIS 5365 Entity Relationship Diagrams
2nd
Relationship:
How do we relate a Patient to their Illnesses?
The let’s try putting the foreign key in table Illness
Illness
IllCode
Description
PatID
a
PatID
b
°°°°
°°
PatID
??
This is as bad as the other relationshi
p
!!Slide22
Your Individual Project22
CIS 5365 Entity Relationship Diagrams
2nd
Relationship:
How do we relate a Patient to their Illnesses?
We’re going to have to find a different solution.
We will have create a separate table to link the two tables together.
What Table??
How about a table called Suffers?
But Suffers is a relationship, NOT a Table!
Why can’t it be both?Slide23
Your Individual Project23
CIS 5365 Entity Relationship Diagrams
2nd
Relationship:
How do we relate a Patient to their Illnesses?
Table
SUFFERS
will become an
ASSOCIATIVE ENTITY
Patient
Suffers
Illness
PatID
Address
Physician
Name
IllCode
Descript.
But how will the tables be related??Slide24
Your Individual Project24
CIS 5365 Entity Relationship Diagrams
2nd
Relationship:
How do we relate a Patient to their Illnesses?
We must use
unique
keys from each of the tables as foreign keys in Table
SUFFERS
PatID
IllCode
We can now link a Patient
With an Illness
Doesn’t every table also need a PRIMARY Key??
Patient
Suffers
Illness
PatID
Address
Physician
Name
IllCode
Descript.Slide25
Your Individual Project25
CIS 5365 Entity Relationship Diagrams
2nd
Relationship:
How do we relate a Patient to their Illnesses?
We can use the two keys
(
PatID
and IllCode)
TOGETHER
as
the
Primary Key
Suffers
PatID
IllCode
This is known as
concatenation
Notice that
BOTH
keys are
primary
keys
AND
foreign
keysSlide26
Your Individual Project26
CIS 5365 Entity Relationship Diagrams
2nd
Relationship:
How do we relate a Patient to their Illnesses?
Concatenation
is the operation of joining two character strings end-to-end.
For example, suppose I have two strings:
A
= “How now”
And
B
= “Brown Cow”
Then:
A + B = “How now Brown Cow”
So the two field are merged together into one field??
No.
But the primary key considered as if they were.
How does all this work??Slide27
Your Individual Project27
CIS 5365 Entity Relationship Diagrams
2nd
Relationship:
How do we relate a Patient to their Illnesses?
Assume that one record in table SUFFER appears as:
Patient
Illness
Suffers
PatID
Name
°°°
°°°
547286579
Clara Schuman
°°°
°°°IllCodeDescription°°°°°°
A896507Broken Left Ulna°°°°°°PatIDIllCode
°°°°°°547286579A896507°°°°°°
We know that Clara Schuman suffered a broken left ulna
There is a problem here!!!Slide28
Your Individual Project28
CIS 5365 Entity Relationship Diagrams
2nd
Relationship:
How do we relate a Patient to their Illnesses?
The problem is that the concatenated primary key is
NOT
unique
Patient
Illness
Suffers
PatID
Name
°°°
°°°
547286579
Clara Schuman°°°°°°IllCodeDescription°°°
°°°A896507Broken Left Ulna°°°°°°
PatIDIllCode°°°°°°547286579
A896507
°°°
°°°
547286579
A896507
What if, in the future, Clara again breaks her left Ulna:
In both cases, the fields are:
547286579
A896507
A violation of Entity Integrity!!!Slide29
Your Individual Project29
CIS 5365 Entity Relationship Diagrams
2nd
Relationship:
How do we relate a Patient to their Illnesses?
We could add a
TIMESTAMP
to the associative Entity:
Suffers
PatID
IllCode
DateSeen
Notice that our concatenated primary key is now:
PatID
+
IllCode
+
DateSeenSlide30
Your Individual Project30
CIS 5365 Entity Relationship Diagrams
2nd
Relationship:
How do we relate a Patient to their Illnesses?
Our tables would appear as:
Patient
Illness
PatID
Name
°°°
°°°
547286579
Clara Schuman
°°°
°°°IllCodeDescription°°°
°°°A896507Broken Left Ulna°°°°°°
PatIDIllCodeDateSeen°°°°°°°°°
547286579A8965078/20/2009°°°°°°°°°547286579A896507
2/16/2011
Suffers
Note that even though Clara broke her arm again, the primary key is
uniqueSlide31
Your Individual Project31
CIS 5365 Entity Relationship Diagrams
Our ERD so far:
Physician
•
PhysID
LastName
FirstName Street
City State Zipcode SpecialtyPatient• PatID
LastName FirstNamex Address~ PhysicianSuffers
• ~ PatID• ~ IllCode
• DateSeenIllness• IllCodeDescription
Treats1 .. *
1
.. *
* .. 1
Attribute Notation:
•
Primary
Key
~
Foreign
Key
x
Composite
Attribute
Aside from using UML, what is different here????
Why is the relationship between Patient-Suffers-Illness 1..* - *..1??Slide32
Your Individual Project32
CIS 5365 Entity Relationship Diagrams
As we have seen, we
CAN NOT
deal with M:M relationships:
Patient
•
PatID
LastName
FirstNamex Address
~ Physician
Initially we had:Illness• IllCodeDescription
Suffers
* .. *
We found out that we could not deal with this situation unless we broke it into an Associative entity:Patient•
PatIDx Address~ Physicianx NameSuffers• ~ PatID
• ~ IllCode• DateSeenIllness• IllCodeDescription
1
.. *
* .. 1Slide33
Your Individual Project33
CIS 5365 Entity Relationship Diagrams
When we consider this relationship:
Patient
•
PatID
x
Address
~ Physician
x
Name
Suffers• ~ PatID• ~ IllCode• DateSeenIllness• IllCode
Description
1
.. *
* .. 1
We should note that it is one we know how to deal with:
In a 1:M relationship, the foreign key goes on the side of the many:Patient• PatIDx
Address~ Physicianx NameSuffers• ~ PatID• ~ IllCode
•
DateSeen
1
M
PatID
can occur only once in
Patient,
but many times in
Suffers
M
Illness
•
IllCode
Description
1
IllCode
can occur many times in
Suffers,
but only once in
IllnessSlide34
Your Individual Project34
CIS 5365 Entity Relationship Diagrams
The actual Tables might appear as:
PatID
Last
First
Street
City
State
Physician
255356278
SotomayorSonya12 OregonEl PasoTX456789012399872889 Bush
W123 MesaEl PasoTX234567890589021099 MubarackHosni
18 HideoutEl Paso
TX345678901620896798 MartinezSusana7 Mayor StAnthonyNM
567890123730927457 LohanLindsay12 NecklaceEl PasoTX456789012
PatIDIllCodeDateSeen399872889C8790131/18/2009730927457B348701
2/8/2011589021099D4046782/10/2011399872889B6671804/19/2010730927457
B3487017/17/2010730927457B66718010/23/2010255356278A8965079/29/2009
IllCode
Description
A507762
Broken Heart
A896507
Broken Left Foot
B348701
Sticky Fingers
B667180
Bad Hair
C879013
Amnesia
D404678
Wounded Ego
F750191
Headache
We Now Know:
Sonya Sotomayor suffered from:
A broken left foot on 2/29/2009
W Bush suffered
from:
Amnesia on 1/19/2009
Bad Hair on 4/19/2010
Hosni Mubarak suffered from:
A Wounded Ego on 2/10/2011
Linsay
Lohan
suffered from:
Sticky Fingers on 7/17/2010
Sticky Fingers (again) on 2/8/2011
Bad Hair on 10/23/2010Slide35
Your Individual Project35
CIS 5365 Entity Relationship Diagrams
3
rd
Relationship:
Illness
Requires
Drugs
Given 1
Illness,
how many
Drugs?
Mandatory?
Given 1
Drug,
how many
Illnesses?
Mandatory?
We already know how this one will come out
We already know that this means that
Requires
will become an
Associative EntitySlide36
Your Individual Project36
CIS 5365 Entity Relationship Diagrams
3
rd
Relationship:
The ERD can be rewritten as:
Illness
•
IllCode
Description
1
.. *
* .. 1
Requires
•
IllCode• DrugCode• DateGiven NumberDrugs• Drugcode
NameX Others
Do we need DateGiven???Slide37
Your Individual Project37
CIS 5365 Entity Relationship Diagrams
3
rd
Relationship:
I think we have a problem!!!
Let’s look at the tables we might generate:
IllCode
Description
A507762
Broken Heart
A896507
Broken Left FootB348701
Sticky FingersC879013
AmnesiaF750191Bad HairIllCodeDrugCodeDateGiven
NoB348701278911018/19/200923C8790131033456712/16/200990
F750191533728202/23/201060B348701103345674/13/201020B348701
5337282010/20/200960A507762722346953/14/2011150B348701
1033456712/23/201030A896507338901121/27/201045
DrugCode
Name
10334567
Thorazine
12337690
Aspirin
27891101
Placebo
27781018
Retin-A
33890112
Codeine
45399034
Ibuprofin
53372820
Laughing Gas
72234695
Vitamin A
Illness
Requires
Drugs
All we know is:
A Broken Heart has been treated with Vitamin A
A Broken Left Foot has been treated with Codeine
Sticky Fingers has been treated with
Thorazine
,
Retin
-A, Laughing Gas & Codeine
Bad Hair has been treated with a Placebo
SO???Slide38
Your Individual Project38
CIS 5365 Entity Relationship Diagrams
3
rd
Relationship:
Let’s consider our old friend Lindsay
Lohan
PatID
Last
First
Street
CityState
Physician730927457LohanLindsay12 NecklaceEl PasoTX345678901
PatID
IllCodeDateSeen730927457B3487012/8/2011730927457B3487017/17/2010730927457
F75019110/23/2010IllCodeDescriptionB348701Sticky Fingers
F750191Bad HairIllCodeDrugCodeDateGivenB34870127891101
8/19/2009B3487015337282010/20/2009B3487013389011212/23/2010F750191
278911016/19/2010DrugCodeName10334567Thorazine
27891101
Placebo
33890112
Codeine
53372820
Laughing Gas
72234695
Vitamin A
Patient
Suffers
Illness
Require
Drugs
We know Lindsay suffers from Sticky Fingers, and that sticky fingers have been treated using
Thorazine
, Codeine, Laughing Gas, and Vitamin A
Which ones were given to Lindsay???Slide39
Your Individual Project39
CIS 5365 Entity Relationship Diagrams
4
th
Relationship:
We
could
try and create a relationship between patient and drug
Patient
Suffers
Illness
Require
Drugs
Is Given
Given one Patient, how many Drugs?
Mandatory?
Given one Drug, how many Patients?
Mandatory?Slide40
Your Individual Project40
CIS 5365 Entity Relationship Diagrams
4
th
Relationship:
The tables might appear as:
Patient
•
PatID
x
Address
~ Physicianx Name
Suffers• ~ PatID• ~ IllCode• DateSeenIllness
• IllCodeDescription
1
.. *
* .. 1Requires• IllCode• DrugCode
• DateGiven NumberDrugs• DrugcodeName
~ PatID
1 *
* 1
1
*
Seems Messy!!
5 Tables
5 Relationships
PatId
and
IllCode
each used in 3 different Tables
All to link a Patient with an Illness and a Drug???Slide41
Your Individual Project41
CIS 5365 Entity Relationship Diagrams
Let’s think this through
Can we have a patient who doesn’t have an illness?
Can we have a patient who need any drugs?
Can there be an illness that is not associated with any Patient?
Can there be a illness that is not associated with any Drug?
Can there be a Drug that is not associated with any Patient?
Can there be a Drug that is not associated with any Illness?
The three entities are
InterrelatedSlide42
Your Individual Project42
CIS 5365 Entity Relationship Diagrams
Developing a
Ternary
relationship
Treatment
Patient
Drug
Illness
We know the three entities involved:
Let’s call the relationship between them
Treatment
Given 1
Patient
and 1
Illness,
how many
Drugs?
Many
Given 1
Patient
and 1
Drug,
how many
Illnesses?
Many
Given 1
Illness
and 1
Drug,
how many
Patients?
Many
A M:M:M ternary relationshipSlide43
Your Individual Project43
CIS 5365 Entity Relationship Diagrams
Developing a
Ternary
relationship
Let’s look at some of the attributes required:
Patient
•
PatID
x
Address
~ Physicianx Name
Illness• IllCodeDescriptionDrugs• Drugcode
Name~ PatID
Treatment• ~ PatID• ~ DrugCode• ~ IllCode
Note that these have NOT changed
* .. 1
* .. 1
* 1
Houston, we have a problem!!!Slide44
Your Individual Project44
CIS 5365 Entity Relationship Diagrams
Developing a
Ternary
relationship
Just as we did in the Associative Entity
Suffers
, We could add a
Time Stamp
(to make the primary key unique):
Treatment• ~ PatID• ~ DrugCode• ~ IllCode
• TDate
Instead of 4-concatenated keys, could we add a single auto-numbered Key?
Yes --
Having a single primary is always niceTreatment
~ PatID~ DrugCode~ IllCodeTDate• TreatID
BUT --
We still have to make sure that a patient treatment is NOT entered twiceSlide45
Your Individual Project45
CIS 5365 Entity Relationship Diagrams
Developing a
Ternary
relationship
Let’s see how the Tables might appear:
PatID
Last
First
Street
City
StatePhysician
255356278SotomayorSonya12 OregonEl PasoTX456789012399872889
BushW
123 MesaEl PasoTX234567890589021099MubarackHosni18 HideoutEl Paso
TX345678901620896798MartinezSusana7 Mayor StAnthonyNM456789012
730927457LohanLindsay12 NecklaceEl PasoTX345678901
IllCodeDescriptionA507762Broken HeartA896507Broken Left FootB348701Sticky Fingers
B667180Bad HairC879013AmnesiaD102234Wounded EgoF750191
Headache
DrugCode
Name
10334567
Thorazine
12337690
Aspirin
27891101
Placebo
27781018
Retin-A
33890112
Codeine
45399034
Ibuprofin
53372820
Laughing Gas
72234695
Vitamin A
PatID
IllCode
DrugCode
TDate
399872889C879013533728201/19/2009730927457B348701
722346956/20/2009730927457B667180277810184/15/2010399872889B667180
278911015/1/2010255356278A507762103345676/10/2010620896798F75019112337690
7/16/2010255356278F7501912789110112/6/2010730927457B348701338901122/8/2010589021099
D102234533728202/10/2011589021099F750191103345672/10/2011
Illness
Treatment
Drugs
Patient
How would this work?Slide46
Your Individual Project46
CIS 5365 Entity Relationship Diagrams
Developing a
Ternary
relationship
Let’s pick on poor Lindsay any more;
Let’s pick on poor W.
PatID
Last
First
Street
CityState
Physician399872889BushW123 MesaEl PasoTX234567890
IllCode
DescriptionB667180Bad HairC879013AmnesiaDrugCodeName
27891101Placebo53372820Laughing GasPatIDIllCodeDrugCode
TDate399872889C879013533728201/19/2009399872889B66718027891101
5/1/2010
Illness
Treatment
Drugs
Patient
Is this an improvement over our previous model??
On January 19, 2009 W. suffered Amnesia and was given Laughing Gas
On May 1, 2010 suffered Bad Hair and was given a PlaceboSlide47
Your Individual Project47
CIS 5365 Entity Relationship Diagrams
Developing a
Ternary
relationship
We know that we eliminated two relationships (
Suffers
and
Requires
) and replaced them with one (
Treatment
).
Let’s look at the size of the old model:Field:PatIDLastFirstStreet
CityState
PhysicianSize (Bytes):9151025202
9
PatientTotal: 90 bytes
IllnessField:IllCodeDescriptionSize (Bytes):
825Total: 33 bytes
Drugs
Field:
DrugCode
Name
Size
(Bytes):
9
25
Total: 34 bytes
157 bytes
The size of these tables remain unchanged.
Field:
PatID
IllCode
DateSeen
Size
(Bytes):
9
8
10
Suffers
Total: 27 bytes
Requires
Field:
IllCode
DrugCode
DateGivenSize (Bytes):
8910Total: 27 bytes 54 bytesSlide48
Your Individual Project48
CIS 5365 Entity Relationship Diagrams
Developing a
Ternary
relationship
Let’s look at the size of the New model:
We know that Tables
Patient
,
Illness
, and
Drugs
will remain unchanged.
The only change will be in the size of the Associative Entities
Field:PatIDIllCodeDrugCodeTDateSize (Bytes):9
8910
Size of Old Model Associative Entities: 54 bytes
Size of New Model Associative Entity: 36 bytes
A savings of only 18 bytes per record, But:
A saving of one Table
A Substantial saving in Metadata
A large savings if we have a large number of records:
If we have 100,000 records,
we save
1,800,000 Bytes
Occam’s Razor
:
"entities must not be multiplied beyond necessity" Slide49
Your Individual Project49
CIS 5365 Entity Relationship Diagrams
We have only one more relationship left:
“You work for a hospital. The Head Administrator has told you that he suspects that Dr. Smith,
as well as all of the other physicians which she supervises,
has been pre-scribing too much Codeine for their patients
.”
We have seen this type of relationship before:
Physician
SupervisesSlide50
Your Individual Project50
CIS 5365 Entity Relationship Diagrams
We have only one more relationship left:
Let’s see how the Table might appear:
Physician
•
PhysID
LastName
FirstName Street
City State Zipcode Specialty~ Supervisor
PhyIDLast
FirstStreetCityStSpecialtySuper.234567890Kervorkian
Jack32 ViscountEl PasoTXEuthanasia345678901ZhivagoYuri19 Redd Rd
El PasoTXCardiology456789012456789012RebenneckMac65 Piano St.
La UnionNMOrthopedics567890123LoveStrange
7 Rio St,El PasoTXPsychiatry234567890678901234McGraw
Phil
12 S.R.
123
Canutio
NM
Oprahology
234567890
Dr. Jack
Kervorkian
Supervises
Dr. Mac
Rebenneck
Supervises
Dr. Strange Love
Dr. Phil McGraw
Dr. Yuri
ZhivagoSlide51
Your Individual Project51
CIS 5365 Entity Relationship Diagrams
We have only one more relationship left:
Let’s see how the Table might appear:
Physician
•
PhysID
LastName
FirstName Street
City State Zipcode Specialty~ Supervisor
PhyIDLast
FirstStreetCityStSpecialtySuper.234567890Kervorkian
Jack32 ViscountEl PasoTXEuthanasia345678901ZhivagoYuri19 Redd Rd
El PasoTXCardiology456789012456789012RebenneckMac65 Piano St.
La UnionNMOrthopedics567890123LoveStrange
7 Rio St,El PasoTXPsychiatry234567890678901234McGraw
Phil
12 S.R.
123
Canutio
NM
Oprahology
234567890
Dr. Jack
Kervorkian
Supervises
Dr. Mac
Rebenneck
Supervises
Dr. Strange Love
Dr. Phil McGraw
Dr. Yuri
ZhivagoSlide52
CIS 5365 Entity Relationship Diagrams
52
Our Final ERD
Supervises
Physician
Treats
Patient
Treatment
Illness
DrugsSlide53
CIS 5365 Entity Relationship Diagrams
53
Our Final ERD
(Using UML)
Physician
•
PhysID
LastName
FirstName Street
City State Zipcode Specialty~ Supervisor
Patient• PatIDx Address~ Physicianx NameIllness
• IllCodeDescription
Drugs• Drugcode Name~ PatIDTreatment
• ~ PatID• ~ DrugCode• ~ IllCode• TDate1*
1**11*Slide54
CIS 5365 Entity Relationship Diagrams
54
Any Questions??