Outline

• Weak Entity Sets
• 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.
• Finally, we talked about how to represent keys, referential integrity, and degree constraints in the model.
• Today, we are going to look at one last component of the E/R model, weak entity sets, before describing how to map the E/R model into the relational model.

Weak Entity Sets

• It is possible for an entity set's key to be composed of attributes, some or all of which belong to another entity set.
• Such an entity set is called a weak entity set and it is represented by a double rectangle.
• The relationship connecting it to the entity which has its key attributes, its supporting entity set, is drawn with a double diamond. It is called the supporting relationship.
• There are two common situations when weak entity sets arise:
  1. Sometimes entity sets fall into a hierarchy based on classification unrelated to the "isa hierarchy" we've previously considered.
     • For example, in taxonomy, a species like humans, is designated by its genus and species name. We could imagine genus as an entity set of which Homo might be a member, species could then be a weak entity set of it. We don't always know the species just from the name. We need the key from genus as well.
     • In the above diagram Crews is a weak entity set, knowing the number of a film crew doesn't tell us which crew it is unless we also know the studio.
  2. Sometimes weak entity sets arise when we try to eliminate multiway relationships.
     • For example, when we eliminate the 3-way Contracts relationship we had last week, the key for the new Contracts entity set come from combining the keys of Star, Studios, and Movies.

Conditions for a Weak Entity Set to Exist

• Conditions on the Weak Entity Set's Key
  1. Zero or more of the entity set's own attributes belong to the key.
  2. There must be at least one key attribute not belonging to the weak entity set itself.
  3. Additional key attributes are reached by many-one relationships (supporting relationships).
• Conditions on Supporting Relationship `R`
  1. `R` must be a binary, many one relationship from the weak entity set ``E to the supporting relationship `F`.
  2. `R` must have referential integrity from `E` to `F`.
  3. The attributes that `F` supplies for the key of `E` must be key attributes of `F`.
  4. If `F` itself is weak, then some or all of the key attributes of `F` supplied to `E` will be key attributes of one or more entity sets `G` to which `F` is connected by a supporting relationship. This holds recursively if `G` too is weak.

Quiz

Which of the following is true?

1. Multiway relationships in E/R Diagrams can be equivalently expressed using binary relationships and extra entity sets.
2. Isa relationships are use to express one-one relationships in E/R Diagrams.
3. Referential integrity constraints in E/R Diagrams are expressed by putting an underline under an attribute's name.

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.

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.