Download
transformation of an er model into a relational database schema n.
Skip this Video
Loading SlideShow in 5 Seconds..
Transformation of an ER Model into a Relational Database Schema PowerPoint Presentation
Download Presentation
Transformation of an ER Model into a Relational Database Schema

Transformation of an ER Model into a Relational Database Schema

135 Vues Download Presentation
Télécharger la présentation

Transformation of an ER Model into a Relational Database Schema

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. Transformation of an ER Model into a Relational Database Schema Translating to Software

  2. 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

  3. 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

  4. 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

  5. Entities to Relations • Start off by representing each entity class as a relation • Add the attributes • Indicate primary key Do it for hospital example

  6. 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 added for illustration - not all justified by our spec.

  7. 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!

  8. A(A#, …) B(B#, A#*, …) 1:N Optional on the “many side” A B 1..1 1..1 0..* 0..* Simple case 1 Rule • Plant the primary key of the one side into the many side

  9. Simple Case 1 - example

  10. A(A#, …) B(B#, …) N:M Optional on both sides A B 0..* 0..* 0..* Simple case 2 Rule • Create a relation to represent the relationship • Plant both primary keys in it as the joint primary key R(A#*, B#*)

  11. Simple Case 2 - example

  12. 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

  13. A(A#, …) B(B#, …) 1:N Optional on both sides A B 0..1 0..* 0..* Simple case 3 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#*)

  14. Simple Case 3 - example

  15. 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

  16. 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

  17. Problem cases

  18. 1:N Mandatory on both sides A B 1..* 1..1 Problem case 1 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#*, …)

  19. Lecturer Module 1..* 1..1 1..1 1..* Problem Case 1 - example

  20. 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

  21. N:M Mandatory on one of the sides A(A#, …) B(B#, …) A B 1..* 0..* Problem case 2 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 R(A#*, B#*)

  22. 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.

  23. 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

  24. Comprehensive list of signatures

  25. A B 1..* 0..1 0..1 1..* A B 1..* 1..1 1..1 1..* 1:N Relationships A(A#,…) B(B#,…) R(A#*,B#*) A(A#,…) B(B#, A#*,…) A(A#,…) B(B#,...) R(A#*,B#*) A(A#,...) B(B#, A#*,...)

  26. A B 1..* 1..* 0..* 0..* A B 1..* 0..* 0..* 1..* A B 1..* 1..* 1..* 1..* Binary (M:N) Relationships A(A#,…) B(B#,…) R(A#*,B#*) A(A#,…) B(B#,…) R(A#*,B#*) A(A#,…) B(B#,...) R(A#*,B#*) A(A#,...) B(B#,...) R(A#*,B#*)

  27. No Duplicates No Duplicates Not Null & No Duplicates Not Null & No Duplicates A B 1..1 1..1 1..1 1..1 Binary (1:1) Relationships R(A#*,B#*) or R(A#*,B#*) A(A#,…) B(B#,…) A(A#,…) B(B#, A#*…) c.f. above A  B A(A#,…) B(B#, A#*…)

  28. 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

  29. Two alternative ideas

  30. Idea 1N: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

  31. 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.

  32. 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

  33. Consultant 1..1 0..1 treats 0..* Surgeon supervises 0..* 0..* P Patient 1..1 performs assists 0..* Patient Operation 1..1 undergoes 0..* 0..* 0..* 0..* occupies located 1..1 1..1 Ward Theatre 0..1 0..1 inWard inTheatre 0..* 0..* Nurse Hospital ER

  34. Consultant 1..1 1..1 treats 0..* 0..* supervises 0..1 Surgeon 1..1 P Patient 1..1 1..1 0..* assists performs 0..* 0..* 1..1 Patient Operation 1..1 undergoes 0..* 0..* 0..* occupies located 1..1 1..1 Ward Theatre 1..1 1..1 0..* 0..* inTheatre 1..1 0..1 inWard 0..1 1..1 Nurse Hospital ER

  35. Idea 2Null 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

  36. Null foreign key - example c.f. Simple case 3 - example there’s an alternative

  37. 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

  38. Subtype Relationships We will return to these