EntityRelationship Model

1
Entity-Relationship Model
2
Database Modeling

• The process of designing a database begins with
• an analysis of what information the database must
  hold, and
  
• the relationships among components of that
  information.
  
• The structure of the database, called the
  database schema is specified in a suitable
  notation for expressing designs.
  
• We will use the Entity-Relationship (E/R) model,
  which is graphical in the nature.

Ideas
E/R design
Relational schema
Relational DBMS
Mechanical process
Abstract design
Concrete design
3
Elements of the E/R Model

• Entities are like objects in OO programming.
  Entity sets are like the classes.
  
• However, E/R is a static model, so there are no
  methods associated with the entities.

Movies

• Attributes are similar to the attributes in OO
  programming.
  
• However, they are atomic, i.e. only numbers and
  strings are allowed.
  

Title
Stars-in

• Relationships are connections among two or more
  entity sets.

4
Example
length
filmType
year
name
address
title
Movies
Stars-In
Stars
Owns
5
Visualizing E/R Relationships

• Its often helpful (for us) to represent E/R
  relationships by a table, each row representing a
  list (it can be pair) of entities participating
  in the relationship.
• For instance the Stars-in relationship could be
  thought of as a table
  
• Movies Stars
• Basic Instinct Sharon Stone.
• Total Recall Arnold Schwarzeneger.
• Total Recall Sharon Stone.
• This table is called the relationship set for the
  relationship.
  
• The members of the relationship set are the rows
  of the table.

6
Multiplicity of Relationships

• A relationship R is many-one from E to F
• if any member of E can be associated to at most
  one member of F.
  
• Equivalently, we say that R is one-many from F to
  E.

• Arrows indicate the multiplicity of a
  relationship.
  
• Arrow means at most one. It does not guarantee
  the existence of an entity of the set pointed to.

7
Multiplicity (Cont.)

• If a relationship R is both many-one from E to F
  and many-one from F to E, then we say that R is
  one-one
  
• Arrows pointing to both E and F.

Studios
Presidents
Runs

• If a relationship R is neither many-one from E to
  F or from F to E, then we say R is many-many.

Movies
Stars
Stars-In
8
Sometimes binary relationships arent enough!
9
Relating studios, movies and stars

• Suppose that we want to know Which stars a
  studio is paying for a specific movie?
  
• As long as a movie is produced by a single studio
  (having the arrow) the previous organization
  works very well. Why?
  
• Because we can relate a studio s through a
  specific movie m with the stars sti forming valid
  triples
  
• (s, m, st1), (s, m, st2),, (s, m, stk)
• Suppose now that movies can be produced (and
  owned) by not just a single studio but, by some
  studios (no arrow).
  
• Now if we try to create the above triples they
  can be invalid.

10
Why the triples can be invalid?

• Suppose studio s1 is paying a star st1 for the
  movie m
  
• and
• studio s2 is paying st2 for the same movie m.
• Then we will have the following triples
• (s1, m, st1) (s1, m, st2) (s2, m, st1) (s2, m,
  st2)
  
• The second and the third triples arent valid.
• If we consider the collection of all the valid
  triples, it is nothing else but a three way
  relationship between Studios, Movies and Stars

11
Solution Three-way relationship

• Relationship Contracts involves a studio, a star,
  and a movie.
  
• It says that a studio has contracted a particular
  star in a particular movie.
  
• Arrow pointing to Studios means
• If we select one entity from Stars and one entity
  from Movies, these entities (together) are
  related to at most one entity in Studios.

12
Roles in a relationship
13
Another Example
Married
Drinkers
14
Another Example
Relationship Set Buddy1 Buddy2 Bob
Ann Joe Sue Ann Bob Joe
Moe

Buddies
1
2
Drinkers
15
Attributes on Relationships
We wish to record the salary associated with a
contract. Cant associate it with the star
A star might get different salaries for
different movies. Doesnt make sense to associat
e it with a studio or with a movie.
However, its appropriate to associate a salary
with the (star, movie, studio) triple in the
relationship set for the Contracts relationship.
16
Bars-Beer-Drinkers (BBD) Example

• Bars sell some beers.
• Drinkers like some beers.
• Drinkers frequent some bars.
• What would be the E/R diagram?

17
Bars-Beer-Drinkers (BBD) Example
manf
name
name
addr
Beers
Bars
Sells
Bars sell some beers.
license
Drinkers like some beers.
Likes
Frequents
Note license beer, full, none
Drinkers frequent some bars.
Drinkers
addr
name
Why we need it?
18
BBD Multiway Relationship

• Suppose that drinkers will only drink certain
  beers at certain bars.
  
• Our three binary relationships Likes, Sells, and
  Frequents do not allow us to make this
  distinction. But a 3-way relationship would.

19
BBD Multiway Relationship
name
addr
name
manf
Bars
Beers
license
Preferences
Drinkers
name
addr
20
A Typical Relationship Set
Bar Drinker Beer Joes Bar Ann Miller Sues Ba
r Ann Bud Sues Bar Ann Petes Ale Joes Bar B
ob Bud Joes Bar Bob Miller Joes Bar Cal Mil
ler Sues Bar Cal Bud Lite
21
Multiple Relationships Between Two Entity Sets
22
Exactly one Multiplicity