E/R to relational mapping

E/R to relational mapping • E/R to relational mapping algorithm – overview • Mapping algorithm step 1-7 • Steen Jensen, autumn 2013

E/R to relational mapping algorithm - overview • The next slides shows an algorithm for converting an E/R diagram to a relational model • The algorithm contains 7 steps: • Mapping of regular entity types • Mapping of weak entity types • Mapping of binary 1:1 relationship types • Mapping of binary 1:N relationship types • Mapping of binary M:N relationship types • Mapping of multivalued attributes • Mapping of N-ary relationship types

E/R to relational mapping algorithm – base example The steps will be based upon figure 7.1 from Elmasri chapter 7

E/R to relational mapping algorithm – step 1- mapping of regular entity types • For each regular (strong) entity type create a relation, that includes all simple attributes • Choose one of the attributes as a primary key • See a) in the below figure

E/R to relational mapping algorithm – step 2- mapping of weak entity types • For each weak entity type create a relation, that includes all simple attributes • Include as foreign key the primary key(s) of the relation(s) corresponding to the ownerentity type(s) • The primary key is the combination of the foreign key and the partial key of the weak entity • See b) in the below figure

E/R to relational mapping algorithm – step 3- mapping of binary 1:1 relationship types • Include a foreign key in one of the entities taking part of the relationship (pick either end) • Also include any attributes from the relationship • See the below figure – here the entity type Department is chosen (relationship Manages)

E/R to relational mapping algorithm – step 4- mapping of binary 1:N relationship types • Include a foreign key in the N-side of the relationship • Also include any attributes from the relationship • See the below figure – the relationships Works_for, Controls and Supervision has been implemented)

E/R to relational mapping algorithm – step 5- mapping of binary M:N relationship types • Each M:N relationship is converted into a relation with a combined primary key (the primary key from both participating entities) • Also include any attributes from the relationship • See c) in the below figure – the relationship Works_onhas been implemented

E/R to relational mapping algorithm – step 6- mapping of multivalued attributes • Each multivalued attribute is converted into a relation with a combined primary key: the attribute itself + foreign key • For each value a tuple will exist in the new relation • See d) in the below figure – the multivalued attribute Locations has been implemented

E/R to relational mapping algorithm – step 7- mapping of N-ary relationship types Figure3.17 from chapt. 3 in Elmasri Figure7.4 from chapt. 7 in Elmasri • Each N-ary relationship is converted into a relation with a combined primary key (the primary keys from all participating entities) • Also include any attributes from the relationship

Assignment: Map E/R diagram for Amazon to a relational model Continue with your solution for Amazon – the E/R diagram Follow the steps described in the slides and convert the E/R diagram into a relational model Start today and continue at home, so you can present a solution next time in class – make a Word document with the relational models and upload it to the folder called Relational models for Amazonin studie.rhs.dk

E/R to relational mapping