entity relationship diagrams - star trek style

Post on 18-Dec-2014

641 Views

Category:

Education

0 Downloads

Preview:

Click to see full reader

DESCRIPTION

Entity Relationship Diagrams explained using the Emergency Medical Hologram's medical database (from Star Trek:Voyager) as an example.

TRANSCRIPT

Prescription

Prescription

Prescription

Dr. Lewis Zimmerman459

Patient name: Tom Paris

Stardate: 41153.7

Drug: Tri-Ox Compound

Take twice a day

Dr. Lewis Zimmerman459

Patient name: Tom Paris

Stardate: 41153.7

Drug: Tri-Ox Compound

Take twice a day

Prescription

Dr. Lewis Zimmerman459

Patient name: Tom Paris

Stardate: 41153.7

Drug: Tri-Ox Compound

Take twice a day

Doctor

Patient

Date

Drug

Frequency

Prescriptionnumber

Dr. Lewis Zimmerman459

Patient name: Tom Paris

Stardate: 41153.7

Drug: Tri-Ox Compound

Take twice a day

PrescriptionPrescriptionIDDoctorNamePatientNameDrugName

DateFrequency

PrescriptionPrescriptionIDDoctorNamePatientNameDrugName

DateFrequency

Prescription

ID

Doctor

Name

Patient

Name

Drug

Name Date

Frequency

(per day)

1Dr. Lewis

Zimmerman

Tom

Paris Tri-Ox

42231.2

2

2Dr. Julian

Bashir

Odo Hyronalin 45432.1

3

3Lewis

Zimmerman

Harry

Kim

Tri-Ox 42235.2

7

4Dr. Beverly

Crusher

Lt. Data Hyronalin 45912.3

1

5Dr. Bashir Odo Synaptizine 48796.6

2

6L.

ZimmermanTom

Paris

Priaxate 42765.5

90

7Dr. Lewis

Zimmerman

Tom

Paris

Tri-Ox 42604.4

5

8Dr. L.

Zimmerman

Katherine

Janeway

Hyronalin 45673.4

4

9Dr.

Phlox

T’Pol Tri-Ox 37389.5

3

• Two Problems:– Lots of text repeated over and over (e.g.

“Tri-Ox”, “Tom Paris”) and imagine this for tens of thousands of records, it really eats up computation power

– Slightly different ways of referring to the same thing (e.g. “Dr. Lewis Zimmerman”, “Dr. L. Zimmerman”, “Lewis Zimmerman”)

• Solution:– Create a number of tables that lists all of

the text fields and assigns them a number

• So:– Create a Doctor

Table that lists all of the Doctors and assigns them a number

Doctor

ID

Doctor

Name

1 Dr. Lewis

Zimmerman

2 Dr. Julian

Bashir

3 Dr. Beverly

Crusher

4 Dr.

Phlox

Prescription

ID

Doctor

Name

Patient

Name

Drug

Name Date

Frequency

(per day)

1Dr. Lewis

Zimmerman

Tom

Paris Tri-Ox

42231.2

2

2Dr. Julian

Bashir

Odo Hyronalin 45432.1

3

3Lewis

Zimmerman

Harry

Kim

Tri-Ox 42235.2

7

4Dr. Beverly

Crusher

Lt. Data Hyronalin 45912.3

1

5Dr. Bashir Odo Synaptizine 48796.6

2

6L.

ZimmermanTom

Paris

Priaxate 42765.5

90

7Dr. Lewis

Zimmerman

Tom

Paris

Tri-Ox 42604.4

5

8Dr. L.

Zimmerman

Katherine

Janeway

Hyronalin 45673.4

4

9Dr.

Phlox

T’Pol Tri-Ox 37389.5

3

Prescription

ID

Doctor

ID

Patient

Name

Drug

Name Date

Frequency

(per day)

1 1 Tom

Paris Tri-Ox

42231.2

2

2 2 Odo Hyronalin 45432.1

3

3 1 Harry

Kim

Tri-Ox 42235.2

7

4 2 Lt. Data Hyronalin 45912.3

1

5 2 Odo Synaptizine 48796.6

2

6 1 Tom

Paris

Priaxate 42765.5

90

7 1 Tom

Paris

Tri-Ox 42604.4

5

8 1 Katherine

Janeway

Hyronalin 45673.4

4

9 4 T’Pol Tri-Ox 37389.5

3

• So:– Now create a

Patient Table that lists all of the patients and assigns them a number

Prescription

ID

Patient

Name

1 Tom

Paris

2 Odo

3 Harry

Kim

4 Lt. Data

5 Katherine

Janeway

6 T’Pol

Prescription

ID

Doctor

ID

Patient

Name

Drug

Name Date

Frequency

(per day)

1 1 Tom

Paris Tri-Ox

42231.2

2

2 2 Odo Hyronalin 45432.1

3

3 1 Harry

Kim

Tri-Ox 42235.2

7

4 2 Lt. Data Hyronalin 45912.3

1

5 2 Odo Synaptizine 48796.6

2

6 1 Tom

Paris

Priaxate 42765.5

90

7 1 Tom

Paris

Tri-Ox 42604.4

5

8 1 Katherine

Janeway

Hyronalin 45673.4

4

9 4 T’Pol Tri-Ox 37389.5

3

Prescription

ID

Doctor

ID

Patient

ID

Drug

Name Date

Frequency

(per day)

1 1 1 Tri-Ox

42231.2

2

2 2 2 Hyronalin 45432.1

3

3 1 3 Tri-Ox 42235.2

7

4 2 4 Hyronalin 45912.3

1

5 2 2 Synaptizine 48796.6

2

6 1 1 Priaxate 42765.5

90

7 1 1 Tri-Ox 42604.4

5

8 1 5 Hyronalin 45673.4

4

9 4 6 Tri-Ox 37389.5

3

• So:– Now create a

Drug Table that lists all of the drugs and assigns them a number

Drug

ID

Drug

Name

1 Tri-Ox

2 Hyronalin

3 Synaptizine

4 Priaxate

Prescription

ID

Doctor

ID

Patient

ID

Drug

Name Date

Frequency

(per day)

1 1 1 Tri-Ox

42231.2

2

2 2 2 Hyronalin 45432.1

3

3 1 3 Tri-Ox 42235.2

7

4 2 4 Hyronalin 45912.3

1

5 2 2 Synaptizine 48796.6

2

6 1 1 Priaxate 42765.5

90

7 1 1 Tri-Ox 42604.4

5

8 1 5 Hyronalin 45673.4

4

9 4 6 Tri-Ox 37389.5

3

Prescription

ID

Doctor

ID

Patient

ID

Drug

ID Date

Frequency

(per day)

1 1 1 1 42231.2

2

2 2 2 2 45432.1

3

3 1 3 1 42235.2

7

4 2 4 2 45912.3

1

5 2 2 3 48796.6

2

6 1 1 4 42765.5

90

7 1 1 1 42604.4

5

8 1 5 2 45673.4

4

9 4 6 1 37389.5

3

• So what we’ve ended up with is…

Prescription

ID

Doctor

ID

Patient

ID

Drug

ID Date

Frequency

(per day)

1 1 1 1 42231.2 2

2 2 2 2 45432.1 3

3 1 3 1 42235.2 7

4 2 4 2 45912.3 1

5 2 2 3 48796.6 2

6 1 1 4 42765.5 90

7 1 1 1 42604.4 5

8 1 5 2 45673.4 4

9 4 6 1 37389.5 3

Drug

ID

Drug

Name

1 Tri-Ox

2 Hyronalin

3 Synaptizine

4 Priaxate

Prescription

ID

Patient

Name

1 Tom

Paris

2 Odo

3 Harry

Kim

4 Lt. Data

5 Katherine

Janeway

6 T’Pol

Doctor

ID

Doctor

Name

1 Dr. Lewis Zimmerman

2 Dr. Julian Bashir

3 Dr. Beverly Crusher

4 Dr. Phlox

• Note:– Now all of the values in the Prescription

Table are numbers, this is great because the computer will be much faster at processing prescriptions (because it loves dealing with numbers), which is good since the Prescription Table will be added to most frequently.

Prescription

ID

Doctor

ID

Patient

ID

Drug

ID Date

Frequency

(per day)

1 1 1 1 42231.2 2

2 2 2 2 45432.1 3

3 1 3 1 42235.2 7

4 2 4 2 45912.3 1

5 2 2 3 48796.6 2

6 1 1 4 42765.5 90

7 1 1 1 42604.4 5

8 1 5 2 45673.4 4

9 4 6 1 37389.5 3

Drug

ID

Drug

Name

1 Tri-Ox

2 Hyronalin

3 Synaptizine

4 Priaxate

Prescription

ID

Patient

Name

1 Tom

Paris

2 Odo

3 Harry

Kim

4 Lt. Data

5 Katherine

Janeway

6 T’Pol

Doctor

ID

Doctor

Name

1 Dr. Lewis Zimmerman

2 Dr. Julian Bashir

3 Dr. Beverly Crusher

4 Dr. Phlox

Prescription

ID

Doctor

ID

Patient

ID

Drug

ID Date

Frequency

(per day)

1 1 1 1 42231.2 2

2 2 2 2 45432.1 3

3 1 3 1 42235.2 7

4 2 4 2 45912.3 1

5 2 2 3 48796.6 2

6 1 1 4 42765.5 90

7 1 1 1 42604.4 5

8 1 5 2 45673.4 4

9 4 6 1 37389.5 3

Drug

ID

Drug

Name

1 Tri-Ox

2 Hyronalin

3 Synaptizine

4 Priaxate

Prescription

ID

Patient

Name

1 Tom

Paris

2 Odo

3 Harry

Kim

4 Lt. Data

5 Katherine

Janeway

6 T’Pol

Doctor

ID

Doctor

Name

1 Dr. Lewis Zimmerman

2 Dr. Julian Bashir

3 Dr. Beverly Crusher

4 Dr. Phlox

Prescription

ID

Doctor

ID

Patient

ID

Drug

ID Date

Frequency

(per day)

1 1 1 1 42231.2 2

2 2 2 2 45432.1 3

3 1 3 1 42235.2 7

4 2 4 2 45912.3 1

5 2 2 3 48796.6 2

6 1 1 4 42765.5 90

7 1 1 1 42604.4 5

8 1 5 2 45673.4 4

9 4 6 1 37389.5 3

Drug

ID

Drug

Name

1 Tri-Ox

2 Hyronalin

3 Synaptizine

4 Priaxate

Prescription

ID

Patient

Name

1 Tom

Paris

2 Odo

3 Harry

Kim

4 Lt. Data

5 Katherine

Janeway

6 T’Pol

Doctor

ID

Doctor

Name

1 Dr. Lewis Zimmerman

2 Dr. Julian Bashir

3 Dr. Beverly Crusher

4 Dr. Phlox

• Also:– If a drug name is changed, e.g. “Tri-Ox” to

“Tricordrazine”, then you only need to change it once and it is done for all of the prescriptions. And this eliminates the possibility of some being changed and some not, since the DrugName now only occurs in one place in the whole database.

Prescription

ID

Doctor

ID

Patient

ID

Drug

ID Date

Frequency

(per day)

1 1 1 1 42231.2 2

2 2 2 2 45432.1 3

3 1 3 1 42235.2 7

4 2 4 2 45912.3 1

5 2 2 3 48796.6 2

6 1 1 4 42765.5 90

7 1 1 1 42604.4 5

8 1 5 2 45673.4 4

9 4 6 1 37389.5 3

Drug

ID

Drug

Name

1 Tri-Ox

2 Hyronalin

3 Synaptizine

4 Priaxate

Prescription

ID

Patient

Name

1 Tom

Paris

2 Odo

3 Harry

Kim

4 Lt. Data

5 Katherine

Janeway

6 T’Pol

Doctor

ID

Doctor

Name

1 Dr. Lewis Zimmerman

2 Dr. Julian Bashir

3 Dr. Beverly Crusher

4 Dr. Phlox

Prescription

ID

Doctor

ID

Patient

ID

Drug

ID Date

Frequency

(per day)

1 1 1 1 42231.2 2

2 2 2 2 45432.1 3

3 1 3 1 42235.2 7

4 2 4 2 45912.3 1

5 2 2 3 48796.6 2

6 1 1 4 42765.5 90

7 1 1 1 42604.4 5

8 1 5 2 45673.4 4

9 4 6 1 37389.5 3

Drug

ID

Drug

Name

1 Tri-Ox

2 Hyronalin

3 Synaptizine

4 Priaxate

Prescription

ID

Patient

Name

1 Tom

Paris

2 Odo

3 Harry

Kim

4 Lt. Data

5 Katherine

Janeway

6 T’Pol

Doctor

ID

Doctor

Name

1 Dr. Lewis Zimmerman

2 Dr. Julian Bashir

3 Dr. Beverly Crusher

4 Dr. Phlox

• Compare this with the original design where we would have to change every occurrence of the drug name.

Prescription

ID

Doctor

Name

Patient

Name

Drug

Name Date

Frequency

(per day)

1Dr. Lewis

Zimmerman

Tom

Paris Tri-Ox

42231.2

2

2Dr. Julian

Bashir

Odo Hyronalin 45432.1

3

3Lewis

Zimmerman

Harry

Kim

Tri-Ox 42235.2

7

4Dr. Beverly

Crusher

Lt. Data Hyronalin 45912.3

1

5Dr. Bashir Odo Synaptizine 48796.6

2

6L.

ZimmermanTom

Paris

Priaxate 42765.5

90

7Dr. Lewis

Zimmerman

Tom

Paris

Tri-Ox 42604.4

5

8Dr. L.

Zimmerman

Katherine

Janeway

Hyronalin 45673.4

4

9Dr.

Phlox

T’Pol Tri-Ox 37389.5

3

Prescription

ID

Doctor

Name

Patient

Name

Drug

Name Date

Frequency

(per day)

1Dr. Lewis

Zimmerman

Tom

Paris Tri-Ox

42231.2

2

2Dr. Julian

Bashir

Odo Hyronalin 45432.1

3

3Lewis

Zimmerman

Harry

Kim

Tri-Ox 42235.2

7

4Dr. Beverly

Crusher

Lt. Data Hyronalin 45912.3

1

5Dr. Bashir Odo Synaptizine 48796.6

2

6L.

ZimmermanTom

Paris

Priaxate 42765.5

90

7Dr. Lewis

Zimmerman

Tom

Paris

Tri-Ox 42604.4

5

8Dr. L.

Zimmerman

Katherine

Janeway

Hyronalin 45673.4

4

9Dr.

Phlox

T’Pol Tri-Ox 37389.5

3

• And what if we forgot to change of them ???

Prescription

ID

Doctor

Name

Patient

Name

Drug

Name Date

Frequency

(per day)

1Dr. Lewis

Zimmerman

Tom

Paris Tri-Ox

42231.2

2

2Dr. Julian

Bashir

Odo Hyronalin 45432.1

3

3Lewis

Zimmerman

Harry

Kim

Tri-Ox 42235.2

7

4Dr. Beverly

Crusher

Lt. Data Hyronalin 45912.3

1

5Dr. Bashir Odo Synaptizine 48796.6

2

6L.

ZimmermanTom

Paris

Priaxate 42765.5

90

7Dr. Lewis

Zimmerman

Tom

Paris

Tri-Ox 42604.4

5

8Dr. L.

Zimmerman

Katherine

Janeway

Hyronalin 45673.4

4

9Dr.

Phlox

T’Pol Tri-Ox 37389.5

3

• Ooops

• that’s going to cause problems, the integrity of the data is compromised.

• So it’s better to separate things out into individual tables.

Prescription

ID

Doctor

ID

Patient

ID

Drug

ID Date

Frequency

(per day)

1 1 1 1 42231.2 2

2 2 2 2 45432.1 3

3 1 3 1 42235.2 7

4 2 4 2 45912.3 1

5 2 2 3 48796.6 2

6 1 1 4 42765.5 90

7 1 1 1 42604.4 5

8 1 5 2 45673.4 4

9 4 6 1 37389.5 3

Drug

ID

Drug

Name

1 Tri-Ox

2 Hyronalin

3 Synaptizine

4 Priaxate

Prescription

ID

Patient

Name

1 Tom

Paris

2 Odo

3 Harry

Kim

4 Lt. Data

5 Katherine

Janeway

6 T’Pol

Doctor

ID

Doctor

Name

1 Dr. Lewis Zimmerman

2 Dr. Julian Bashir

3 Dr. Beverly Crusher

4 Dr. Phlox

PrescriptionID

DoctorID

PatientID

DrugID

Date

Frequency

Prescription

Insertnew

prescriptionrecord

Prescription

Insertnew

prescriptionrecord

Drug

Check if the drug on the prescriptionis a real and existing drug

Prescription

Drug

Insertnew

prescriptionrecord

Patient

Check if the patient on the prescriptionis a real and existing patient

Prescription

Drug

Insertnew

prescriptionrecord

Patient

Check if the doctor on the Prescription

is a real and existing doctor

Doctor

Prescription

Drug

Patient

Doctor

Prescription

Drug

Patient

Doctor

Prescription

Drug

Patient

Doctor

PrescriptionIDDoctorIDPatientIDDrugID

DateFrequency

DrugIDDrugName

RecommendedDosageSide-Effects

PatientIDPatientName

PatientAddressSympthomsOccupation

DoctorIDDoctorName

Qualifications

Prescription

Drug

Patient

Doctor

PrescriptionIDDoctorIDPatientIDDrugID

DateFrequency

DrugIDDrugName

RecommendedDosageSide-Effects

DoctorIDDoctorName

Qualifications

PatientIDPatientName

PatientAddressSympthomsOccupation

top related