Transcript Database Design (2)

Database
Design (2)
IS 240 – Database Management
Lecture #11 – 2004-03-18
Prof. M. E. Kabay, PhD, CISSP
Norwich University
[email protected]
1
Copyright © 2004 M. E. Kabay. All rights reserved.
Topics
 Recursive Relationships
 Ternary Relationships
 Homework
2
Copyright © 2004 M. E. Kabay. All rights reserved.
Recursive Relationships (1)
 Suppose a plant can originate from a single
cutting or can produce a single cutting.
Diagram this entity here using the E-R
notation:
3
Copyright © 2004 M. E. Kabay. All rights reserved.
Recursive Relationships (1*)
 Suppose a plant can originate from a single
cutting or can produce a single cutting.
Diagram this entity here using the E-R
notation:
Cutting
1:1
4
Plant
Copyright © 2004 M. E. Kabay. All rights reserved.
Recursive Relationships (2)
 What if a plant can produce several cuttings?
Change the diagram to suit this constraint
5
Copyright © 2004 M. E. Kabay. All rights reserved.
Recursive Relationships (2*)
 What if a plant can produce several cuttings?
Change the diagram to suit this constraint
Cutting
1:N
6
Plant
Copyright © 2004 M. E. Kabay. All rights reserved.
Recursive Relationships (3)
 Suppose several plants can have cuttings
combined into grafts to produce several other
plants.
Draw an E-R diagram to represent this
situation
7
Copyright © 2004 M. E. Kabay. All rights reserved.
Recursive Relationships (3*)
 Suppose several plants can have cuttings
combined into grafts to produce several other
plants.
Draw an E-R diagram to represent this
situation
Cutting
M:N
8
Plant
Copyright © 2004 M. E. Kabay. All rights reserved.
Recursive Relationships (4)
 Now show relations that will model the ER
diagrams you developed for the previous
slides starting with:
1:1 recursion – 1 plant, 1 cutting
9
Copyright © 2004 M. E. Kabay. All rights reserved.
Recursive Relationships (4*)
 Now show relations that will model the ER
diagrams you developed for the previous
slides starting with:
1:1 recursion – 1 plant, 1 cutting
10
Source_plant
Cutting_plant
Plant #1
Plant #11
Plant #2
Plant #21
Plant #11
Plant #111
Copyright © 2004 M. E. Kabay. All rights reserved.
Recursive Relationships (5)
Show the relations to model a
1:N recursion – 1 plant, many cuttings
11
Copyright © 2004 M. E. Kabay. All rights reserved.
Recursive Relationships (5*)
Show the relations to model a
1:N recursion – 1 plant, many cuttings
12
Source_plant
Cutting_plant
Plant #1
Plant #11
Plant #1
Plant #12
Plant #2
Plant #21
Plant #3
Plant #31
Plant #3
Plant #32
Plant #31
Plant #311
Copyright © 2004 M. E. Kabay. All rights reserved.
Recursive Relationships (6)
Show the relations to model an
M:N recursion – many plants can combine
to produce many grafts
13
Copyright © 2004 M. E. Kabay. All rights reserved.
Recursive Relationships (6*)
Show the relations to model an
M:N recursion – many plants can combine
to produce many grafts
Plant_A
Plant_ID
Plant 1
Plant 2
Plant 3
14
Other_info
…
…
…
Plant_B Date_etc
Plant 1
Plant 2
…
Plant 1
Plant 3
…
Plant 1
Plant 4
…
Plant 2
Plant 4
…
Copyright © 2004 M. E. Kabay. All rights reserved.
Ternary Relationships (1)
 Think about
the doctor(s) who prescribe(s) something,
the prescription(s), and
the patient(s) who receive(s) the prescription(s)
 Use E-R diagrams to describe this situation:
15
Copyright © 2004 M. E. Kabay. All rights reserved.
Ternary Relationships (1*)
 Think about
the doctor(s) who prescribe(s) something,
the prescription(s), and
the patient(s) who receive(s) the prescription(s)
 Use E-R diagrams to describe this situation:
M:N
16
Copyright © 2004 M. E. Kabay. All rights reserved.
Ternary Relationships (2)
 Develop relations to model the entity
relationships described on the previous slide:
17
Copyright © 2004 M. E. Kabay. All rights reserved.
Ternary Relationships (2*)
 Develop relations to model the entity
relationships described on the previous slide:
Doc1
Pat1
Date
Drug
Info…
Doc1
Pat2
Date
Drug
Doctor 2
Info…
Doc1
Pat3
Date
Drug
Doc2
Pat1
Date
Drug
Doctor 3
Info…
Doc3
Pat2
Date
Drug
Doc4
Pat3
Date
Drug
Doc4
Pat3
Date
Drug
Doc5
Pat3
Date
Drug
Doctor 1
18
Copyright © 2004 M. E. Kabay. All rights reserved.
Patient 1
Info…
Patient 2
Info…
Patient 3
Info…
Ternary Relationships (3)
For the doctor / patient / prescription
relations,
 How would you model the prescriptiondoctor-patient relationship if you added the
constraint that some drugs must never be
prescribed at the same time?
Show the relations on the next page
19
Copyright © 2004 M. E. Kabay. All rights reserved.
Ternary Relationships (4)
 Draw the relations showing constrained
combinations of drugs
FORBIDDEN COMBINATIONS
Are the
duplications a
problem?
DRUG TABLE
Drug 1
Drug 2
Drug 3
20
Info…
Info…
Info…
First
Second
Drug 1
Drug 4
Drug 1
Drug 2
Drug 4
Drug 6
Drug 6
Drug 6
Drug 1
Drug 1
Drug 6
Drug 2
Copyright © 2004 M. E. Kabay. All rights reserved.
Ternary Relationships (4*)
 Draw the relations showing constrained
combinations of drugs
21
Copyright © 2004 M. E. Kabay. All rights reserved.
Homework
 For Tuesday 23 Mar 2004
Review Chapter Six material pp. 151-165 in
detail
Finish reading/reciting the rest of Chapter Six to
complete your preparation for our next course
meeting
 For Thursday 25 Mar 2004
Complete Group I questions 6.15-6.24 for 20
points
Hand them in at the Business Division office by
noon
There will be no class that day – come to the
E-ProtectIT Conference in Milano if you can.
See http://www.e-protectit.org for details
22
Copyright © 2004 M. E. Kabay. All rights reserved.
DISCUSSION
23
Copyright © 2004 M. E. Kabay. All rights reserved.