E/R Diagram

• An E/R diagram
  • is a semantic data modeling tool
  • is a high-level graphical reresentation of data
  • contains three main element types:
    • entity sets - rectangles
    • attributes - ovals
    • relationships - diamonds
• Each line joins an entity set to its attributes, or joins a relationship to their participating entity sets.

Examples

• Movies, stars, and studios

  

• Students applying to colleges. (Draw your own diagram here.)

Terms

• Entity vs Entity set (Cf. Object vs class in OO programming)
• Entity set instance e.g. {Basic Instinct, Total Recall} is an instance of the Movies entity set.

• Relationship vs Relationship set e.g. Stars-in is a relationsip between the Movies and Stars entities, and the following is an example of a relationship set:

  Movies	Stars
  Basic Instinct	Sharon Stone
  Total Recall	Arnold Schwarzenegger
  Total Recall	Sharon Stone

• Degree of a relationship: unary, binary, ternary, \(k\)-ary. A \(k\)-ary relationship for \(k>2\) is called a multiway relationship.

• A recursive relationship occurs when the same entity set participates in a relationship more than once.

Terms

• Multiplicity of binary relationship:
  • many-many
  • many-one
  • one-one
• The “one” entity set in the relationship has an arrow entering it. This diagram shows a one-one relationship.
• “one” means “at most one”
• Roles need to be specified in a recursive relationship.
  

Multiway relationships

• A three-way relationship generalizing the many-one binary relationship: for a particular star and movie, there’s only one studio with which the star has contracted for that movie.
  
  
• A four-way, recursive relationship: for any 4-tuple (studio1, studio2, star, movie) in the relationship, studio2 contracts studio1 for the use of studio1’s star by studio2 for the movie.
  
  Note that this diagram does not specify all functional dependencies!

Attributes on relationships

• An example relationship with attributes:
  

Eliminating attributes on relationships

• An attribute on a relationship can be eliminated by introducing a new entity having that attribute and participating in that relationship.
• Eliminating the salary attribute on the Contracts relationship of the diagram on last slide results in this diagram:
  

Converting k-way relationships to k binary relationships

• A multiway \(k\)-ary relationship can be converted to \(k\) binary relationships by introducing a new entity set representing all the tuples of the relationship set, each edge incident to the elminated diamond is now connected to the newly created rectangle through a new binary relationship.
• So this diagram
  
  can be converted into this diagram
  

Subclasses

• Member of some subset A of an entity set B may have special attributes that other members of B do not have. In such a case we say that A is a subclass of B, or A isa B. Instead of a diamond, the isa relationship is drawn as a triangle with its base on the subclass and pointing to the superclass, e.g.
  
• Every isa relationship is one-one even though no arrowheads are drawn in the diagram.
• Each collection of the isa relationships must form a tree with one root.
• Each entity in the isa relationship has a component in each class starting from its deepest class and going up all the way to the root.
• It’s possible for an entity to have more than one deepest class.

Design Principles

1. The design must be faithful to specifications. The designer needs to know about the real world she is modeling. Which of these is faithful?

   

Design Principles

2. Redundancy should be avoided. What’s wrong?

   

Design Principles

3. Choose a simple design whenever possible.
4. Choose the right relationships. If a relationship can be deduced from existing relationships, do not add it to the design.
5. Picking the right kind of element. A common decision to be made is choosing between using attributes and using entity set/relationship combinations. You should use an entity set if at least one of the following conditions hold: (i) it has at least one nonkey attribute, or (ii) it is the “many” in a many-one or many-many relationship.

Primary key constraints

• All attributes of a primary key are underlined.
  
• Note that the primary key of an entity with an isa relationship is at the root.

Referential integrety and cardinality constraints

• Let R be a relationahip from E to F. A rounded arrowhead pointing to F indicates not only that R is many-one from E to F, but also that the entity of set F related to a given entity of set E is required to exist. (Referential Integrity!)
  
  
  
• Degree constraints like “\(\le\) 10” can be used instead of the arrows.
  
  

Weak Entity

• A weak entity set’s key is composed of attributes, some or all of which belong to another entity set.
• Examples: Kids of a college employee can also get a health insurance through the employee parent. A movie studio can have many crews.
  
  A species is fully specified only when its genus is also given
  
  
• A weak entity set is represented by a double rectangle; its relationship to the supporting entity sets is a double diamond.

Converting an ERD to relational database schema

• First assume there is no weak entities and no isa relationships.
• Basic Algorithm:
  • Turn each entity set into a relation with the same name and same set of attributes.
  • Replace a relationship by a relation whose set of attributes consists of the keys for the connected entity sets, and the attributes of the relationship itself, if any.

Combining relations

• There is one modification to the basic algorithm. When there is an entity set \(E\) with a many-one relationship \(R\) from \(E\) to \(F\), it’s better to combine the schema for \(E\) and \(R\) into one schemata.
• This combined schemata consists of
  1. All attributes of \(E\).
  2. The key attributes of \(F\).
  3. Any attributes belonging to relationship \(R\).
• For any entity \(e\) of \(E\) that is not related to any entity of \(F\), the attributes of types (2) and (3) will have null values in the tuple for \(e\).

Example combining relations

• In the diagram

  

we can combine the relations Movies(title, year, length, genre) and Owns(title, year, studioName) into one relation Movies(title, year, length, genre, studioName).

Weak entity sets

• Construct for a weak entity \(W\) a relation whose schemata consists of:
  1. All attributes of \(W\).
  2. All attributes of supporting relationships of \(W\).
  3. For each supporting relationship of \(W\), say a many-one relationship from \(W\) to entity set \(E\), all the key attributes of \(E\).

Rename attributes, if necessary, to avoid name conflicts.

• Do not construct a relation for any supporting relationship for \(W\).

• E.g., the diagram
  
  converts to only two relations

  Studios(name, addr)
  Crews(number, studioName, crewChief)

Converting subclasses structures to relations

• Three approaches
  • E/R viewpoint
  • OO programming viewpoint
  • use null values

Example conversion

• For this diagram the E/R viewpoint gives

  Movies(title, year, length, genre)
  MurderMysteries(title, year, weapon)
  Cartoons(title, year)

• OO viewpoint gives

  Movies(title, year, length, genre)
  MoviesC(title, year, length, genre)
  MurderMM(title, year, length, genre, weapon)
  MurderCMM(title, year, length, genre, weapon)

• Using null values gives

  Movies(title, year, length, genre, weapon)