1 / 11

The transformation of an ER or EER model into a relational model

The transformation of an ER or EER model into a relational model. Transforming an ER model into a relational model. ER model - not supported directly by any DBMS needs to be translated into a model that is supported by DBMSs transformation / mapping process non-deterministic

valentine
Download Presentation

The transformation of an ER or EER model into a relational model

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. The transformation of an ER or EER model into a relational model

  2. Transforming an ER model into a relational model • ER model - not supported directly by any DBMS • needs to be translated into a model that is supported by DBMSs • transformation / mapping process • non-deterministic • certain ER configurations can be expressed in more than one way in the relational model • certain aspects related to the relational model are not specified in the ER model • transformation guidelines (rules) • some approaches propose two phases • ER model to translatable ER model (this can be optional/implicit) • translatable ER model into relational model

  3. ER model  relational modelentities • strong entity  base relation • simple, single valued base attributes - ok • composite - flatten • multi-valued - introduce entity and link with 1:M relationship • calculated attributes - eliminate • weak entity  base relation • primary key contains part of the strong entity primary key • foreign key referencing the base relation corresponding to the strong entity • how is the “weak entity” information modelled in the relational model? • cannot have SET NULL or SET DEFAULT

  4. ER model  relational modelbinary relationships • one to one • one partial and one total participation  foreign key from the base relation corresponding to the entity with partial participation to the one with total participation • two partial participations  one foreign key from one base relation to another • two total participations  as above or merge • why would you want to keep them separate? • one to many • FK from the “many entity” relationship to the “one entity” relationship • does it matter whether the “many entity” is weak or strong?

  5. ER model  relational modelbinary relationships • many to many • no attributes • relationship  relation consisting of two foreign keys, one for each relation corresponding to the two entities involved • the two foreign keys  primary key • alternatively, change the ER model • with attributes • transform relationship into entity • implement the two resulting 1:M relationships

  6. ER model  relational modelbinary relationships • Activity: how are the participation constraints represented? e.g.: Student Student No No 0..1 1 takes takes 1..* 0..* Project Project Title Supervisor Title Supervisor

  7. ER model  relational modelcomplex relationships • with attributes • transform into entity • no attributes • relationship  relation consisting of foreign keys, one for each relation corresponding to the entities involved • alternatively, transform complex relationship into binary relationships

  8. ER model  relational modelrecursive relationships • introduce new entity Staff Staff staff manager staff manager 1 1 0..1 0..* natIns natIns HasAllocation IsAllocatedTo Allocation 0..1 0..* IsManaged

  9. ER model  relational modelrecursive relationships • Activity: what is the difference between (a) and (b)? (a) (b)

  10. EER model  relational model • Activity: translate type hierarchies

  11. Conclusion • conceptual model  logical model • is the resulting logical model the model that is going to be implemented? • logical design  expressing further constraints on data • functional dependencies • multi-valued dependencies • join dependencies • if they are not correctly expressed - the logical model can lead to update anomalies

More Related