![Page 1: 1 Copyright © 2004 M. E. Kabay. All rights reserved. Database Design (2) IS 240 – Database Management Lecture #11 – 2004-03-18 Prof. M. E. Kabay, PhD,](https://reader035.vdocuments.us/reader035/viewer/2022062618/551472b4550346494e8b6136/html5/thumbnails/1.jpg)
1 Copyright © 2004 M. E. Kabay. All rights reserved.
Database Design (2)
IS 240 – Database Management
Lecture #11 – 2004-03-18Prof. M. E. Kabay, PhD, CISSP
Norwich University
![Page 2: 1 Copyright © 2004 M. E. Kabay. All rights reserved. Database Design (2) IS 240 – Database Management Lecture #11 – 2004-03-18 Prof. M. E. Kabay, PhD,](https://reader035.vdocuments.us/reader035/viewer/2022062618/551472b4550346494e8b6136/html5/thumbnails/2.jpg)
2 Copyright © 2004 M. E. Kabay. All rights reserved.
Topics
Recursive RelationshipsTernary RelationshipsHomework
![Page 3: 1 Copyright © 2004 M. E. Kabay. All rights reserved. Database Design (2) IS 240 – Database Management Lecture #11 – 2004-03-18 Prof. M. E. Kabay, PhD,](https://reader035.vdocuments.us/reader035/viewer/2022062618/551472b4550346494e8b6136/html5/thumbnails/3.jpg)
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:
![Page 4: 1 Copyright © 2004 M. E. Kabay. All rights reserved. Database Design (2) IS 240 – Database Management Lecture #11 – 2004-03-18 Prof. M. E. Kabay, PhD,](https://reader035.vdocuments.us/reader035/viewer/2022062618/551472b4550346494e8b6136/html5/thumbnails/4.jpg)
4 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:
1:1
Cutting
Plant
![Page 5: 1 Copyright © 2004 M. E. Kabay. All rights reserved. Database Design (2) IS 240 – Database Management Lecture #11 – 2004-03-18 Prof. M. E. Kabay, PhD,](https://reader035.vdocuments.us/reader035/viewer/2022062618/551472b4550346494e8b6136/html5/thumbnails/5.jpg)
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
![Page 6: 1 Copyright © 2004 M. E. Kabay. All rights reserved. Database Design (2) IS 240 – Database Management Lecture #11 – 2004-03-18 Prof. M. E. Kabay, PhD,](https://reader035.vdocuments.us/reader035/viewer/2022062618/551472b4550346494e8b6136/html5/thumbnails/6.jpg)
6 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
1:N
Cutting
Plant
![Page 7: 1 Copyright © 2004 M. E. Kabay. All rights reserved. Database Design (2) IS 240 – Database Management Lecture #11 – 2004-03-18 Prof. M. E. Kabay, PhD,](https://reader035.vdocuments.us/reader035/viewer/2022062618/551472b4550346494e8b6136/html5/thumbnails/7.jpg)
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
![Page 8: 1 Copyright © 2004 M. E. Kabay. All rights reserved. Database Design (2) IS 240 – Database Management Lecture #11 – 2004-03-18 Prof. M. E. Kabay, PhD,](https://reader035.vdocuments.us/reader035/viewer/2022062618/551472b4550346494e8b6136/html5/thumbnails/8.jpg)
8 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
M:N
Cutting
Plant
![Page 9: 1 Copyright © 2004 M. E. Kabay. All rights reserved. Database Design (2) IS 240 – Database Management Lecture #11 – 2004-03-18 Prof. M. E. Kabay, PhD,](https://reader035.vdocuments.us/reader035/viewer/2022062618/551472b4550346494e8b6136/html5/thumbnails/9.jpg)
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
![Page 10: 1 Copyright © 2004 M. E. Kabay. All rights reserved. Database Design (2) IS 240 – Database Management Lecture #11 – 2004-03-18 Prof. M. E. Kabay, PhD,](https://reader035.vdocuments.us/reader035/viewer/2022062618/551472b4550346494e8b6136/html5/thumbnails/10.jpg)
10 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
Plant #1 Plant #11
Plant #2 Plant #21
Plant #11 Plant #111
Source_plant Cutting_plant
![Page 11: 1 Copyright © 2004 M. E. Kabay. All rights reserved. Database Design (2) IS 240 – Database Management Lecture #11 – 2004-03-18 Prof. M. E. Kabay, PhD,](https://reader035.vdocuments.us/reader035/viewer/2022062618/551472b4550346494e8b6136/html5/thumbnails/11.jpg)
11 Copyright © 2004 M. E. Kabay. All rights reserved.
Recursive Relationships (5)
Show the relations to model a1:N recursion – 1 plant, many cuttings
![Page 12: 1 Copyright © 2004 M. E. Kabay. All rights reserved. Database Design (2) IS 240 – Database Management Lecture #11 – 2004-03-18 Prof. M. E. Kabay, PhD,](https://reader035.vdocuments.us/reader035/viewer/2022062618/551472b4550346494e8b6136/html5/thumbnails/12.jpg)
12 Copyright © 2004 M. E. Kabay. All rights reserved.
Recursive Relationships (5*)
Show the relations to model a1:N recursion – 1 plant, many cuttings
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
![Page 13: 1 Copyright © 2004 M. E. Kabay. All rights reserved. Database Design (2) IS 240 – Database Management Lecture #11 – 2004-03-18 Prof. M. E. Kabay, PhD,](https://reader035.vdocuments.us/reader035/viewer/2022062618/551472b4550346494e8b6136/html5/thumbnails/13.jpg)
13 Copyright © 2004 M. E. Kabay. All rights reserved.
Recursive Relationships (6)
Show the relations to model anM:N recursion – many plants can combine
to produce many grafts
![Page 14: 1 Copyright © 2004 M. E. Kabay. All rights reserved. Database Design (2) IS 240 – Database Management Lecture #11 – 2004-03-18 Prof. M. E. Kabay, PhD,](https://reader035.vdocuments.us/reader035/viewer/2022062618/551472b4550346494e8b6136/html5/thumbnails/14.jpg)
14 Copyright © 2004 M. E. Kabay. All rights reserved.
Recursive Relationships (6*)
Show the relations to model anM:N recursion – many plants can combine
to produce many grafts
Plant 1 …
Plant 2 …
Plant 3 …
Plant_ID Other_info
Plant_A Plant_B Date_etc
Plant 1 Plant 2 …
Plant 1 Plant 3 …
Plant 1 Plant 4 …
Plant 2 Plant 4 …
![Page 15: 1 Copyright © 2004 M. E. Kabay. All rights reserved. Database Design (2) IS 240 – Database Management Lecture #11 – 2004-03-18 Prof. M. E. Kabay, PhD,](https://reader035.vdocuments.us/reader035/viewer/2022062618/551472b4550346494e8b6136/html5/thumbnails/15.jpg)
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:
![Page 16: 1 Copyright © 2004 M. E. Kabay. All rights reserved. Database Design (2) IS 240 – Database Management Lecture #11 – 2004-03-18 Prof. M. E. Kabay, PhD,](https://reader035.vdocuments.us/reader035/viewer/2022062618/551472b4550346494e8b6136/html5/thumbnails/16.jpg)
16 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
![Page 17: 1 Copyright © 2004 M. E. Kabay. All rights reserved. Database Design (2) IS 240 – Database Management Lecture #11 – 2004-03-18 Prof. M. E. Kabay, PhD,](https://reader035.vdocuments.us/reader035/viewer/2022062618/551472b4550346494e8b6136/html5/thumbnails/17.jpg)
17 Copyright © 2004 M. E. Kabay. All rights reserved.
Ternary Relationships (2)
Develop relations to model the entity relationships described on the previous slide:
![Page 18: 1 Copyright © 2004 M. E. Kabay. All rights reserved. Database Design (2) IS 240 – Database Management Lecture #11 – 2004-03-18 Prof. M. E. Kabay, PhD,](https://reader035.vdocuments.us/reader035/viewer/2022062618/551472b4550346494e8b6136/html5/thumbnails/18.jpg)
18 Copyright © 2004 M. E. Kabay. All rights reserved.
Ternary Relationships (2*)
Develop relations to model the entity relationships described on the previous slide:
Doctor 1 Info…
Doctor 2 Info…
Doctor 3 Info…
Patient 1 Info…
Patient 2 Info…
Patient 3 Info…
Doc1 Pat1 Date Drug
Doc1 Pat2 Date Drug
Doc1 Pat3 Date Drug
Doc2 Pat1 Date Drug
Doc3 Pat2 Date Drug
Doc4 Pat3 Date Drug
Doc4 Pat3 Date Drug
Doc5 Pat3 Date Drug
…
![Page 19: 1 Copyright © 2004 M. E. Kabay. All rights reserved. Database Design (2) IS 240 – Database Management Lecture #11 – 2004-03-18 Prof. M. E. Kabay, PhD,](https://reader035.vdocuments.us/reader035/viewer/2022062618/551472b4550346494e8b6136/html5/thumbnails/19.jpg)
19 Copyright © 2004 M. E. Kabay. All rights reserved.
Ternary Relationships (3)
For the doctor / patient / prescription relations,
How would you model the prescription-doctor-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
![Page 20: 1 Copyright © 2004 M. E. Kabay. All rights reserved. Database Design (2) IS 240 – Database Management Lecture #11 – 2004-03-18 Prof. M. E. Kabay, PhD,](https://reader035.vdocuments.us/reader035/viewer/2022062618/551472b4550346494e8b6136/html5/thumbnails/20.jpg)
20 Copyright © 2004 M. E. Kabay. All rights reserved.
Ternary Relationships (4)
Draw the relations showing constrained combinations of drugs
Drug 1 Info…
Drug 2 Info…
Drug 3 Info…
Drug 1 Drug 4
Drug 1 Drug 6
Drug 2 Drug 6
Drug 4 Drug 1
Drug 6 Drug 1
Drug 6 Drug 2
First Second
DRUG TABLE
FORBIDDEN COMBINATIONS
Are the duplications a
problem?
![Page 21: 1 Copyright © 2004 M. E. Kabay. All rights reserved. Database Design (2) IS 240 – Database Management Lecture #11 – 2004-03-18 Prof. M. E. Kabay, PhD,](https://reader035.vdocuments.us/reader035/viewer/2022062618/551472b4550346494e8b6136/html5/thumbnails/21.jpg)
21 Copyright © 2004 M. E. Kabay. All rights reserved.
Ternary Relationships (4*)
Draw the relations showing constrained combinations of drugs
![Page 22: 1 Copyright © 2004 M. E. Kabay. All rights reserved. Database Design (2) IS 240 – Database Management Lecture #11 – 2004-03-18 Prof. M. E. Kabay, PhD,](https://reader035.vdocuments.us/reader035/viewer/2022062618/551472b4550346494e8b6136/html5/thumbnails/22.jpg)
22 Copyright © 2004 M. E. Kabay. All rights reserved.
HomeworkFor 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 2004Complete Group I questions 6.15-6.24 for 20
pointsHand them in at the Business Division office by
noonThere 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
![Page 23: 1 Copyright © 2004 M. E. Kabay. All rights reserved. Database Design (2) IS 240 – Database Management Lecture #11 – 2004-03-18 Prof. M. E. Kabay, PhD,](https://reader035.vdocuments.us/reader035/viewer/2022062618/551472b4550346494e8b6136/html5/thumbnails/23.jpg)
23 Copyright © 2004 M. E. Kabay. All rights reserved.
DISCUSSION