1 / 38

Transformation of an ER Model into a Relational Database Schema

Transformation of an ER Model into a Relational Database Schema. Translating to Software. 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

avidan
Download Presentation

Transformation of an ER Model into a Relational Database Schema

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

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

More Related