Créer une présentation
Télécharger la présentation

Télécharger la présentation
## Transformation of an ER Model into a Relational Database Schema

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -

**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 • good implementations come from these and experience/inventiveness • inventiveness requires clear understanding of the relational model**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**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**Entities to Relations**• Start off by representing each entity class as a relation • Add the attributes • Indicate primary key Do it for hospital example**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.**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!**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**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#*)**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**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#*)**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**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**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#*, …)**Lecturer**Module 1..* 1..1 1..1 1..* Problem Case 1 - example**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**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#*)**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.**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**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#*,...)**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#*)**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#*…)**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**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**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.**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**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**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**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**Null foreign key - example**c.f. Simple case 3 - example there’s an alternative**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**Subtype Relationships**We will return to these