transformation of an er model into a relational database schema translating to software
TRANSCRIPT
![Page 1: Transformation of an ER Model into a Relational Database Schema Translating to Software](https://reader036.vdocuments.us/reader036/viewer/2022062404/551b2e54550346d41a8b4cff/html5/thumbnails/1.jpg)
Transformation of an ER Model into a Relational Database
Schema
Translating to Software
![Page 2: Transformation of an ER Model into a Relational Database Schema Translating to Software](https://reader036.vdocuments.us/reader036/viewer/2022062404/551b2e54550346d41a8b4cff/html5/thumbnails/2.jpg)
2 into 1 won’t go?
• ER model has 2 major concepts– Entities– Relationships
• Relational model has 1 major concept– Relation (table)
• There are general rules for translation– good implementations come from these and
experience/inventiveness– inventiveness requires clear understanding of
the relational model
![Page 3: Transformation of an ER Model into a Relational Database Schema Translating to Software](https://reader036.vdocuments.us/reader036/viewer/2022062404/551b2e54550346d41a8b4cff/html5/thumbnails/3.jpg)
How we do it
Entities– all become relations (tables)
Relationships– some become relations (tables)– some are implemented by use of PK, FK– some need additional coding
• using DBMS facilities
• using application code if necessary
– we know which by their cardinality signatures
![Page 4: Transformation of an ER Model into a Relational Database Schema Translating to Software](https://reader036.vdocuments.us/reader036/viewer/2022062404/551b2e54550346d41a8b4cff/html5/thumbnails/4.jpg)
Notation
• Primary key attribute(s): underline & bold
• Foreign key attributes: *
• #: Unique attribute indicator– traditional usage, helps identify keys in
simplified examples• A# is the PK of relation A
![Page 5: Transformation of an ER Model into a Relational Database Schema Translating to Software](https://reader036.vdocuments.us/reader036/viewer/2022062404/551b2e54550346d41a8b4cff/html5/thumbnails/5.jpg)
Entities to Relations
• Start off by representing each entity class as a relation
• Add the attributes
• Indicate primary key
Do it for hospital example
![Page 6: Transformation of an ER Model into a Relational Database Schema Translating to Software](https://reader036.vdocuments.us/reader036/viewer/2022062404/551b2e54550346d41a8b4cff/html5/thumbnails/6.jpg)
Hospital Example
PATIENT{P#,PName,PAddress,Dob,Sex}
P_PATIENT{P#}
WARD{W#,WType}
NURSE{N#,Name,Grade}
OPERATION{Op#,Type,Date,Time}
SURGEON{Sname,SAddress,Tel#}
CONSULTANT{Cname,Speciality}
THEATRE{T#,TheatreType}
Attributes addedfor illustration - not all justified by our spec.
![Page 7: Transformation of an ER Model into a Relational Database Schema Translating to Software](https://reader036.vdocuments.us/reader036/viewer/2022062404/551b2e54550346d41a8b4cff/html5/thumbnails/7.jpg)
Relationships to Relations
In lectures we will– Look at 3 simple cardinality signatures
• common
• easy to translate
• no problems
– Look at some problem cases• for illustration
– Look at a comprehensive list of signatures• for revision and exercise
• for completeness
– Return for more later!
![Page 8: Transformation of an ER Model into a Relational Database Schema Translating to Software](https://reader036.vdocuments.us/reader036/viewer/2022062404/551b2e54550346d41a8b4cff/html5/thumbnails/8.jpg)
Simple case 1
A(A#, …)
B(B#, A#*, …)
BA1..1 0..*1..1 0..*
1:NOptional on the “many side”
Rule• Plant the primary key of the one side into the
many side
![Page 9: Transformation of an ER Model into a Relational Database Schema Translating to Software](https://reader036.vdocuments.us/reader036/viewer/2022062404/551b2e54550346d41a8b4cff/html5/thumbnails/9.jpg)
Simple Case 1 - example
OperatorCode Name Tel. …
EOE Eastern & Oriental Express 2272068
DSH Dino Shipping 276922
TourID OperatorCode* Start Visiting …
1 EOE Singapore Bangkok
2 DSH Singapore Bintan
TourOperator
1..1 0..*1..1 0..*
![Page 10: Transformation of an ER Model into a Relational Database Schema Translating to Software](https://reader036.vdocuments.us/reader036/viewer/2022062404/551b2e54550346d41a8b4cff/html5/thumbnails/10.jpg)
Simple case 2
A(A#, …)
B(B#, …)
BA0..* 0..*0..*
N:MOptional on both sides
Rule• Create a relation to represent the relationship• Plant both primary keys in it as the joint primary
key
R(A#*, B#*)
![Page 11: Transformation of an ER Model into a Relational Database Schema Translating to Software](https://reader036.vdocuments.us/reader036/viewer/2022062404/551b2e54550346d41a8b4cff/html5/thumbnails/11.jpg)
Simple Case 2 - example
StudentSID Name Scheme …
1234 John Doe Maths
5678 Jane Black CS
ModuleCode Title …
CS123 Databases
MA111 Hard sums
CS456 Java
ModuleStudent
0..* 0..*0..* 0..*
TakesSID* Code*
5678 CS123
5678 CS456
![Page 12: Transformation of an ER Model into a Relational Database Schema Translating to Software](https://reader036.vdocuments.us/reader036/viewer/2022062404/551b2e54550346d41a8b4cff/html5/thumbnails/12.jpg)
Simple Case 2 - comments
• The existence of a tuple in the “intersection” relation is the relationship instance
• The key is joint because a student can only take a module once– SID as PK would let a student do only 1 module– CODE as PK would let a module have only 1
student
![Page 13: Transformation of an ER Model into a Relational Database Schema Translating to Software](https://reader036.vdocuments.us/reader036/viewer/2022062404/551b2e54550346d41a8b4cff/html5/thumbnails/13.jpg)
Simple case 3
A(A#, …)
B(B#, …)
BA0..1 0..*0..*
1:NOptional on both sides
Rule• Create a relation to represent the relationship• Plant both primary keys in it• Make the many side key the new PK
R(A#*,B#*)
![Page 14: Transformation of an ER Model into a Relational Database Schema Translating to Software](https://reader036.vdocuments.us/reader036/viewer/2022062404/551b2e54550346d41a8b4cff/html5/thumbnails/14.jpg)
Simple Case 3 - example
StudentSID Name Scheme …
1234 John Doe Maths
5678 Jane Black CS
SponsorCo Company …
AI ACME Inc
WT Wiztronics
SC Softco
Sponsoring SID* Co*
1234 WT
5678 AI
StudentSponsor
0..1 0..*0..1 0..*
![Page 15: Transformation of an ER Model into a Relational Database Schema Translating to Software](https://reader036.vdocuments.us/reader036/viewer/2022062404/551b2e54550346d41a8b4cff/html5/thumbnails/15.jpg)
Simple Case 3 - comments
• Again, the existence of a tuple in the “intersection” relation is the relationship instance
• The intersection PK is only one FK (student)– SID is PK so each student can have max 1
Sponsoring– CO not PK, Sponsor could have many
Sponsorings
![Page 16: Transformation of an ER Model into a Relational Database Schema Translating to Software](https://reader036.vdocuments.us/reader036/viewer/2022062404/551b2e54550346d41a8b4cff/html5/thumbnails/16.jpg)
Relationships to RelationsSimple Summary
Bring keys of associated entities together by– If there is a “one” side
• if Mandatory– posting key as foreign key into an existing “host” relation
• if Optional– creating a new relation posting both keys to it
– set PK to implement the multiplicity
» (the entity which can have only 1)
– If there are 2 “many” sides• creating a new relation posting both keys to it
• set both as a joint PK
![Page 17: Transformation of an ER Model into a Relational Database Schema Translating to Software](https://reader036.vdocuments.us/reader036/viewer/2022062404/551b2e54550346d41a8b4cff/html5/thumbnails/17.jpg)
Problem cases
![Page 18: Transformation of an ER Model into a Relational Database Schema Translating to Software](https://reader036.vdocuments.us/reader036/viewer/2022062404/551b2e54550346d41a8b4cff/html5/thumbnails/18.jpg)
Problem case 1
BA1..1 1..*
1:NMandatory on both sides
Situation• 1..* is our problem
– the tell-tale signature
• Can do no better than the optional case– Plant the key of A in B
A(A#, …)
B(B#, A#*, …)
![Page 19: Transformation of an ER Model into a Relational Database Schema Translating to Software](https://reader036.vdocuments.us/reader036/viewer/2022062404/551b2e54550346d41a8b4cff/html5/thumbnails/19.jpg)
Problem Case 1 - example
ModuleName Lecturer*
Databases Hardy
Law Bott
Maths Holstein
Robots Hardy
Lecturer
Alsberg
Bott
Hardy
Holstin
ModuleLecturer
1..1 1..*1..1 1..*
![Page 20: Transformation of an ER Model into a Relational Database Schema Translating to Software](https://reader036.vdocuments.us/reader036/viewer/2022062404/551b2e54550346d41a8b4cff/html5/thumbnails/20.jpg)
Problem case 1 - comments• How can we ensure that every instance of A
is involved in at least one relationship with a B?– i.e. every A# appears in B
• Cannot enforce it• Can check if rule is obeyed (rel. algebra)
A[A#] == B[A#]
• Can query for As not found in B– could query for operators not found in tours
– could list lecturers not teaching
![Page 21: Transformation of an ER Model into a Relational Database Schema Translating to Software](https://reader036.vdocuments.us/reader036/viewer/2022062404/551b2e54550346d41a8b4cff/html5/thumbnails/21.jpg)
Problem case 2
BA0..* 1..*
N:MMandatory on one of the sides
Situation• 1..* again• Can do no better than the optional case
– Plant the key of A and B in a new relation and joint PK
A(A#, …)
B(B#, …) R(A#*, B#*)
![Page 22: Transformation of an ER Model into a Relational Database Schema Translating to Software](https://reader036.vdocuments.us/reader036/viewer/2022062404/551b2e54550346d41a8b4cff/html5/thumbnails/22.jpg)
Problem case 2 - comments
• How can we ensure that every instance of A (every A#) is involved in at least one relationship with a B? (same question)
• Cannot enforce it (same problem!)
• Every A# must appear in R at last once
• Can check if rule is obeyed (rel. algebra)A[A#] == R[A#]
• Can query for As not found in R etc.
![Page 23: Transformation of an ER Model into a Relational Database Schema Translating to Software](https://reader036.vdocuments.us/reader036/viewer/2022062404/551b2e54550346d41a8b4cff/html5/thumbnails/23.jpg)
Problem cases - general
• These cases are the less common ones
• Often the constraints cannot be implemented for all time– modules and students before registration?
• Often left unimplemented– but with a mechanism to list breaches
• a query, run regularly or on demand
• Enforcing participation may just not be important
![Page 24: Transformation of an ER Model into a Relational Database Schema Translating to Software](https://reader036.vdocuments.us/reader036/viewer/2022062404/551b2e54550346d41a8b4cff/html5/thumbnails/24.jpg)
Comprehensive list of signatures
![Page 25: Transformation of an ER Model into a Relational Database Schema Translating to Software](https://reader036.vdocuments.us/reader036/viewer/2022062404/551b2e54550346d41a8b4cff/html5/thumbnails/25.jpg)
1:N Relationships
A(A#,...)B(B#, A#*,...)
A(A#,…)B(B#,…)
R(A#*,B#*)
A(A#,…)B(B#, A#*,…)
A(A#,…)B(B#,...) R(A#*,B#*)
BA
0..1 0..*0..1 0..*
BA
1..1 0..*1..1 0..*
BA
0..1 1..*0..1 1..*
BA
1..1 1..*1..1 1..*
![Page 26: Transformation of an ER Model into a Relational Database Schema Translating to Software](https://reader036.vdocuments.us/reader036/viewer/2022062404/551b2e54550346d41a8b4cff/html5/thumbnails/26.jpg)
Binary (M:N) Relationships
A(A#,…)B(B#,…)
R(A#*,B#*)
A(A#,...)B(B#,...)
A(A#,…)B(B#,…)
A(A#,…)B(B#,...) R(A#*,B#*)
R(A#*,B#*)
R(A#*,B#*)
BA
0..* 0..*0..* 0..*
BA
1..* 0..*1..* 0..*
BA
0..* 1..*0..* 1..*
BA
1..* 1..*1..* 1..*
![Page 27: Transformation of an ER Model into a Relational Database Schema Translating to Software](https://reader036.vdocuments.us/reader036/viewer/2022062404/551b2e54550346d41a8b4cff/html5/thumbnails/27.jpg)
Binary (1:1) Relationships
A(A#,…)B(B#,…)
R(A#*,B#*)or
R(A#*,B#*)
A(A#,…)B(B#, A#*…)
A(A#,…)B(B#, A#*…)
c.f. above A B Not Null &
No DuplicatesNot Null &
No Duplicates
NoDuplicates
NoDuplicates
BA
0..1 0..10..1 0..1
BA
1..1 0..11..1 0..1
BA
0..1 1..10..1 1..1
BA
1..1 1..11..1 1..1
![Page 28: Transformation of an ER Model into a Relational Database Schema Translating to Software](https://reader036.vdocuments.us/reader036/viewer/2022062404/551b2e54550346d41a8b4cff/html5/thumbnails/28.jpg)
Schema semantics
• For the 12 cases there are only 3 different relational schemas
• 1..* is the problem– ensuring minimal participation– (also 1..1)
• ensuring two way participation
• there may be a chicken and egg problem here– do we really want it?
– we may have only one entity really
![Page 29: Transformation of an ER Model into a Relational Database Schema Translating to Software](https://reader036.vdocuments.us/reader036/viewer/2022062404/551b2e54550346d41a8b4cff/html5/thumbnails/29.jpg)
Two alternative ideas
![Page 30: Transformation of an ER Model into a Relational Database Schema Translating to Software](https://reader036.vdocuments.us/reader036/viewer/2022062404/551b2e54550346d41a8b4cff/html5/thumbnails/30.jpg)
Idea 1
N:M and the Relational Model
• Just not supported
• We have always needed a third table
• Is that an entity we missed?– Matter of opinion (“takes” or “Registration”)
• May want to represent N:M on the ER– makes sense to the user
• Any N:M can be decomposed to two 1:N
![Page 31: Transformation of an ER Model into a Relational Database Schema Translating to Software](https://reader036.vdocuments.us/reader036/viewer/2022062404/551b2e54550346d41a8b4cff/html5/thumbnails/31.jpg)
M:N Decomposition
A(A#, …)
B(B#, …)This is exactly the same relational schema as for
the M:N relationship below.R(A#*, B#*, …)
Note: A pair of M:N’s leads to a fan trap.
BA
0..* 0..*0..* 0..*
A R
1..1 0..*
B
1..10..*0..* 1..11..1 0..*
![Page 32: Transformation of an ER Model into a Relational Database Schema Translating to Software](https://reader036.vdocuments.us/reader036/viewer/2022062404/551b2e54550346d41a8b4cff/html5/thumbnails/32.jpg)
Modified ER?
• After the ER model is agreed:– Make systematic changes to move it towards
the relational model• replace N:M
• replace optional 1:N
• C&B, recommend this stage– DB Soln, “Step 1.7”, p147 et.seq.– DB Sys, Chapt. 8
![Page 33: Transformation of an ER Model into a Relational Database Schema Translating to Software](https://reader036.vdocuments.us/reader036/viewer/2022062404/551b2e54550346d41a8b4cff/html5/thumbnails/33.jpg)
Hos
pita
l ER
0..*
1..1treats
0..*
1..1
occupies
0..*
0..1 inWard
0..*
0..1inTheatre
0..*1..1 undergoes
0..*
1..1
located
0..*
1..1
performs
0..*
0..*
assists
0..*
0..1
supervises
Consultant
Surgeon
Operation
Theatre
Nurse
Ward
Patient
P Patient
![Page 34: Transformation of an ER Model into a Relational Database Schema Translating to Software](https://reader036.vdocuments.us/reader036/viewer/2022062404/551b2e54550346d41a8b4cff/html5/thumbnails/34.jpg)
Hos
pita
l ER
0..*
1..1
0..1
1..1
0..*
1..1
0..*
1..1
0..*
1..1treats
0..*
1..1
occupies
0..*1..1 undergoes
0..*
1..1
located
0..*
1..1
performs
0..11..1
0..*
1..1
0..1 1..1
0..*
1..1
Consultant
Surgeon
Operation
TheatreWard
Patient
P Patient
assists
supervises
Nurse inTheatreinWard
![Page 35: Transformation of an ER Model into a Relational Database Schema Translating to Software](https://reader036.vdocuments.us/reader036/viewer/2022062404/551b2e54550346d41a8b4cff/html5/thumbnails/35.jpg)
Idea 2
Null foreign keys for “optional” 1:N
• We have been creating intersection relations
• We can treat it as the mandatory case but give the foreign key no value
• Lots of blanks where there is no relationship
![Page 36: Transformation of an ER Model into a Relational Database Schema Translating to Software](https://reader036.vdocuments.us/reader036/viewer/2022062404/551b2e54550346d41a8b4cff/html5/thumbnails/36.jpg)
Null foreign key - example
StudentSID Name Co* Scheme …
1234 John Doe Maths
5678 Jane Black AI CS
SponsorCo Company …
AI ACME Inc
WT Wiztronics
SC Softco
StudentSponsor
0..1 0..*0..1 0..*
c.f. Simple case 3 - example
there’s an alternative
![Page 37: Transformation of an ER Model into a Relational Database Schema Translating to Software](https://reader036.vdocuments.us/reader036/viewer/2022062404/551b2e54550346d41a8b4cff/html5/thumbnails/37.jpg)
Translation Summary(for now)
• Entities become relations
• Some relationships become relations
• 1..* is hard– i.e. most mandatory participation
• It is not quite a “recipe”– design choices– ingenuity
![Page 38: Transformation of an ER Model into a Relational Database Schema Translating to Software](https://reader036.vdocuments.us/reader036/viewer/2022062404/551b2e54550346d41a8b4cff/html5/thumbnails/38.jpg)
Subtype Relationships
We will return to these