Outline

• E/R to Relational Mapping
• In-Class Exercise
• Other High Level Models

Introduction

• Last Monday, 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 when 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.

In-class Exercise

• Convert this E/R Diagram from last day to the relational model using the algorithm just presented.
• Post your solution to the In-Class Exercise Thread.

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.

UML Associations

• In UML, a binary relationship between classes is called an association.
• There is no UML equivalent for the E/R concept of a multiway relation.
• The figure above shows how to draw associations in UML - we use an edge between to classes.
• Notice we label the edge with the name of the association and we label the end points of the edge with the degree constraints of the association.
• In the figure above, we have two associations Owns and Stars-in.
• The degree constraints on Owns indicate that at most 1 studio can own 0 or more (what the * means) movies.
• If we don't label an endpoint it is assumed to mean 1..1.
• If we write * alone as an endpoint, it is assumed to mean 0..*.

Self Associations

• An association can have both ends at the same class.
• In which case, it is called a self-association.
• Rather than have a single label for the name of the association, in this case, we usually use two labels to describe the role of each instance of the class participating in the association.
• So the above diagram, represents an association related to movie sequels.
• Two instances of Movies participate in the association: TheOriginal Movies and TheSequel Movies.

Association Classes

• In the E/R model, we allowed relationships to have attributes.
• In UML we can add to association edge a class, called an association class, to contain the attributes of an association to convey similar information.
• In the above, Compensation is an association class used to contain the salary and residual attributes of the StarsIn association.

Subclasses

• In UML, subclasses are represented by rectangles in the same way as classes.
• An edge with an open arrow is then used to indicate what class the subclass is a subclass of.
• To simplify our diagrams we often join the edges of multiple subclasses of the same class into one before drawing the final connection arrow to the parent class.
• So in the above figure, Murder-Mysteries, Cartoons, and Cartoon-Murder-Mysteries.
• Each subclass will have all the properties (attributes and association) of the parent class, but additional properties can be indicated either in the subclasses rectangle or in association going from the subclasses rectangle.

Aggregation and Composition

• An aggregation is a line between two classes that ends in an open diamond at one end.
• The open diamond indicates that end of the relation has 0..1 as a constraint. I.e., the association is many-one.
• In the above, a studio may have many movies. Any movie must have one studio.
• A composition is a line between two classes that ends in an closed diamond at one end.
• The closed diamond indicates that end of the relation has 1..1 as a constraint.
• In the above, each studio must have a president. A president may or may not be a president of a studio.

UML to Relations

We now describe how a UML Diagram can be mapped to a relational schema:

• Classes to Relations. For each class, create a relation whose name is the name of the class and whose attributes are the attributes of the class.
• Associations to Relations. For each association create a relation with the name of the association. The attributes of this new class are the key attributes of the two connected classes. If there is a name collision, do an appropriate renaming. Then add any attributes, renaming as needed, of any association classes for this association.
• Subclasses to Relations. Here we can use any of the techniques we used for mapping Isa relationships in the E/R setting. Perhaps the simplest is the Using Multiple Relations of the form Superclass and Subclass approach.
• Aggregations and Compositions to Relations. We can use the same technique as the many-one mapping from the E/R diagram setting.