Outline

• E/R to Relational Mapping
• Quiz
• Other High Level Models

Introduction

• Last week, we were describing the E/R Model as a high level data model suitable for quickly prototyping data before mapping data to the relational model.
• After describing the basic building blocks of this model and diagrams based on this model: attributes (ovals), entity sets (rectangles), relationships (diamonds), we looked at how we could represent subclassing in this model using isa relationships.
• We then talked about design principle to use when making E/R-diagrams: faithfulness to underlying data, simplicity, when to make something an attribute versus an entity, etc.
• We talked about how to represent keys, referential integrity, and degree constraints in the model.
• Finally, we looked weak entity sets which might arise when one has modeling taxonomy like relationship or whene converting multi-way relationships to binary ones.
• Today, we look at how to map E/R diagrams into the relational model.

E/R To Relational Mapping

• Roughly, to convert an E/R diagram to a relational database schema, we:
  • Turn each entity set into a relation with the same set of attributes, and
  • Replace each relationship by a relation whose attributes are the keys for the connected entity sets.
• To fully specify the algorithm, however, we need to specify in more detail how we handle:
  1. Weak Entity Sets.
  2. "Isa" relationships and subclasses.
  3. Simplifications in the schema that can be had when a relationship is many-one or one-one.
• We now discuss the above as a algorithm in a sequence of steps.

Step 1 - Mapping Entity Sets

• For each entity set `E` in the ER diagram, create a relation `R_E` in our schema that includes all the simple attributes of E.
• Next choose one of the key attributes of `E` to be a primary key of `R_E`.
• For example, our Movies entity set would map to the relation `R_{mbox(Movies)}`(title, year, length, genre)
• We could then choose the title, year key of our entity set to be the primary key of the `R_{mbox(Movies)}` relation.

Step 2 - Mapping Weak Entity Sets

• For each weak entity set `W` with supporting entity set `E` create a new relation `R_W` that includes all the attributes of `W` as attributes of `R_W`.
• In addition include a foreign key reference to the key of the translation `R_E` of `E`.
• The key of `R_W` will be the key of foreign key together with the mapped partial key from `W`.

Step 3 - Mapping One-One relationships

• For each binary 1:1 relationship `R` in the ER Schema, identify the relations `S` and `T` that correspond to the entity sets participating in `R`.
• There are three possible approaches to handle this relationship:
  1. The foreign key approach: Choose one of the relations, say `S`, and include in `S` a foreign key reference to the primary key of `T`. (Favor `S` over `T` if its corresponding entity participated totally (with referential integrity) in the relationship.)
  2. Merged relation approach: When both relations correspond to entities that participated totally in the relationship, one can just merge the two relations into one.
  3. Relationship relation approach: Set up a new relation with for the purpose of cross referencing the primary keys of tables `S` and `T`.

Step 4 - Mapping Many-One relationships

• For each many-one binary relationship `R`, identify the relations `S` and `T` corresponding to the entity sets in this relationship.
• Let `S` be the many-side of the relationship.
• Then include a foreign key reference to `R` in `S`.

Step 5 - Mapping Many-many relationships

• For each binary many many relationship `R`, create a new relation `R_R` to represent `R`.
• Include as foreign keys of this relation the primary keys of the relations corresponding to the two participating entities.
• Also add as attributes simple attributes of `R` as in the entity case.

Step 6 - Mapping Multiway relationships

• For each `n`-ary relationship `R` with `n>2`, create a new relation `R_R` to represent `R`.
• Include as foreign key attributes in `R_R` the primary keys of the relations that correspond to the participating entities .
• The primary key of `R_R` is the combination of these foreign keys.

Step 7 - Mapping Isa Relationships

• There are several different ways one could map subclasses `{S_1,...,S_n}` of some entity set `C` with attributes `{k mbox{(the key)}, a_1, ..., a_m}`:
  • Using Multiple Relations of the Form Superclass and Subclass. Create a relation `R_C` for `C` with attributes `{k, a_1, ..., a_m}` and a relation `R_{S_i}` for each `i` with attributes `{k} cup {mbox(attributes of) S_i}`.
  • Using Multiple Relations with Subclass Relations Only. Create `R_{S_i}`s as above but don't create an `R_C`. This works if all members of the root entity set belong to at least one subclass.
  • Creating a Single Relation With One Type Attribute. Let `R_C` be a new relation with attribute `{k, a_1,..., a_m}cup{mbox(attributes of )S_1}cup...cup{mbox(attributes of )S_n} cup{t}`. Here `t` is a string of the form "C" or "`S_i`" for some `i`. This works only if subclassing is into disjoint subclasses.
  • Single Relation With Multiple Type Attributes. Same as above except rather than adding a single `t`, we now add boolean flag types `t_1, ..., t_n` one for each `S_i` which indicates which `S_i` are relevant for a particular tuple.

Quiz

Which of the following is true?

1. A Weak Entity Set's key can be determined from its partial key attributes and the key attributes of the entity sets it participates in supporting relationships with.
2. All E/R diagram relationships are binary.
3. It is never possible to simplify an E/R diagram by replacing entity sets with attributes.

Unified Modeling Language

• We are now going to look at some other high level models which might be used instead of E/R diagrams.
• One of the most famous of these, UML (Uniform Modeling Language), was originally developed as a graphical notation for describing software designs in an object oriented style.
• UML offers much the same capabilities as the E/R model, with the exception of not having multiway relationships.
• In UML entity sets are treated as classes with methods as well as data.
• Below is a short table comparing UML to the E/R Model.

  UML              | E/R Model
  --------------------------------
  Class            | Entity set 
  Association      | Binary relationship 
  Association Class| Attributes on a relationship S
  Subclass         | Isa hierarchy 
  Aggregation      | Many-one relationship 
  Composition      | Many-one relationship 
                   |   with referential integrity
  
  

UML Classes

• A class in UML is similar to an entity set in the E/R model.
• The notation for a class is a box, with up to two dividing lines.
• Above the first list is the name of class, after this line is a list of attributes with keys labeled with PK, below a second line are listed any methods.
• An example of a UML class appears above, next day we will continue with UML.