1

1
The E-R Model
CS 157A Lecture 4

• Prof. Sin-Min Lee
• Department of Computer Science

2
(No Transcript)
3
(No Transcript)
4
(No Transcript)
5
(No Transcript)
6
Entity-Relationship (E-R) Model
COURSE
CTITILE
PROF
SCHED
ROOM
COURSE
ENROLL
GRADE
STUDENT
SNAME
CREDITS
STUID
MAJOR
7
Class Hierarchies
Sometimes its natural to classify the
entities in an entity set into subclass, then we
will use Class Hierarchies.

name
lot
ssn
Employees
Hourly wage
ISA
Hours worked
contractid
Hourly Emps
Contract Emps
8
Aggregation
A relationship set is an association
between entity sets. Sometimes we have to model
a relationship between a collection of entities
and relationships, then we need to use
aggregation.
name
ssn
lot
Employees
monitors
until
Started-on
dname
since
pid
pbudget
did
budget
Sponsors
Department
Projects
9
Conceptual design using the ER model

• Should a concept be modeled as an entity or an
  attribute ?
• Should a concept be modeled as an entity or a
  relationship ?
• What are the relationships ? Should we use binary
  or ternary relationships ?
• Should we use aggregation ?

10
UNARY RELATIONSHIP

• Unary relationship is an associate of 1 entity
  set.
• Note an entity set is a group of
  related entities.

Dr. Lee
Student
Entity set Relation
Attribute
11
BINARY RELATIONSHIP

• Binary relationship is an associate of 2
  entities sets .

Works_In
Employees
Department
ssn
name
lot
budget
dname
did
Entity set Relation Attribute
12
TERNARY RELATIONSHIP
Ternary relationship is an associate of 3
entities sets .
Works_In
Employees
Department
ssn
name
lot
did
budget
dname
Locations
cap
add
Entity set Relation
Attribute
13
QUARTERY RELATIONSHIP
Quartery relationship is an associate of 4
entities sets.
Product
Works_In
Employees
Department
Locations
Entity set Relation Attribute
14
(No Transcript)
15
Additional features of the ER model

• In the ER model allow us to draw important
  distinctions about the data.
• One of the features of the ER model is
• Key Constraints
• Note A key constraints is also known as
  Primary key.
• A primary key is a candidate
  key selected to uniquely identify all other
  attribute values in any given row, can not
  contain null entries.

16
An example of Key Constraints on Manage
name
Since
dname
budget
did
ssn
lot
Manages
Department
Employees
ssn Social Security Number. did Department
id.
17
(No Transcript)
18
Weak Entities
name
cost
Pname ---------
age
ssn
lot
Policy
Department
Employees
A weak entity is an entity of which is
existence depends on other entities.
19
(No Transcript)
20
(No Transcript)
21
Entity versus Attribute
from
to
name
dname
budget
did
ssn
lot
Works-In2
Department
Employees
Entity set Relation Attribute
22
Entity versus Relationship
dname
name
did
budget
ssn
lot
Manages
Department
Employees
since
apptnum
Mgr-Appts
dbudget
23
Binary versus Ternary Relationship
name
Pname ---------
age
ssn
lot
Covers
Dependents
Employees
Policies
policyid
cost
24
Using a Ternary Relationship instead of
Aggregation
Started-on
dname
did
budget
pid
pbudget
Sponsors
Departments
Projects
Employees
lot
ssn
name
25
ONE TO MANY RELATIONSHIP

customer-name
customer-street
loan-number
amount
customer-city
customer-id
borrower
loan
customer
26
MANY TO ONE RELATIONSHIP

customer-name
customer-street
loan-number
amount
customer-city
customer-id
borrower
loan
customer
27
ONE TO ONE RELATIONSHIP

customer-name
customer-street
loan-number
amount
customer-city
customer-id
borrower
loan
customer
28
E-R Diagram with Composite, Multivalued, and
Derived Attributes

street-name
middle-name
Street-number
apartment-number
last-name
first-name
street
name
city
customer-id
address
state
Customer
zip-code
age
date-of-birth
phone-number
29
WEAK ENTITY SETS

• An entity set may not have sufficient attributes
  to form a primary key. Such an entity set is
  termed a weak entity set. An entity set that has
  a primary key is termed a strong entity set.
• For a weak entity set to be meaningful, it must
  be associated with another entity set, called the
  identifying or owner entity set. The
  relationship associating the weak entity set with
  the identifying entity set is called the
  identifying relationship.
• The identifying relationship is many to one from
  the weak entity set to the identifying entity
  set, and the participation of the weak entity set
  in the relationship is total.

30
DISCRIMINATOR

• The discriminator of a weak entity set is a set
  of attributes that allows this distinction to be
  made. For example, the discriminator of a weak
  entity set payment is the attribute
  payment-number, since, for each loan a payment
  number uniquely identifies one single payment for
  that loan. The discriminator of a weak entity
  set is also called the partial key of the entity
  set.
• Note although each payment entity is distinct,
  payments for different loans may share the same
  payment-number. Thus, payment entity set does
  not have a primary key it is a weak entity set.
• The primary key of a weak entity set is formed
  by the primary key of the identifying entity set,
  plus the weak entity sets discriminator.

31
E-R DIAGRAM WITH A WEAK ENTITY SET

• In a E-R diagrams, a doubly outlined box
  indicates a weak entity set, and a doubly
  outlined diamond indicates the corresponding
  identifying relationship. We underline the
  discriminator of a weak entity set with a dashed
  line.

payment-date
amount
loan-number
payment-number
payment-amount
loan-payment
loan
payment
E-R diagram with a weak entity set
32
(No Transcript)
33
(No Transcript)
34
(No Transcript)
35
(No Transcript)
36
Entity/Relationship Model

• Diagrams to represent designs.
• Entity like object, thing.
• Entity set like class set of similar
  entities/objects.
• Attribute property of entities in an entity
  set, similar to fields of a struct.
• In diagrams, entity set ? rectangleattribute ?
  oval.

name
phone
ID
Students
height
37
Relationships

• Connect two or more entity sets.
• Represented by diamonds.

Taking
Students
Courses
38
Relationship Set

• Think of the value of a relationship set as a
  table.
• One column for each of the connected entity sets.
• One row for each list of entities, one from each
  set, that are connected by the relationship.
• Students Courses
• Sally CS180
• Sally CS111
• Joe CS180

39
Multiway Relationships

• Usually binary relationships (connecting two
  E.S.) suffice.
• However, there are some cases where three or more
  E.S. must be connected by one relationship.
• Example relationship among students, courses,
  TA's (and graders).
• Possibly, this E/R diagram is OK

Taking
Students
Courses
Assisting
TA/Graders
40

• Works in CS180, because each TA (or grader) is a
  TA of all students. Connection student-TA is
  only via the course.
• But what if students were divided into sections,
  each headed by a TA?
• Then, a student in CS180 would be related to only
  one of the TA's for CS180. Which one?
• Need a 3-way relationship to tell.

41
Courses
Enrolls
Students
TAs

• Students Courses TAs
• Ann CS180 Jan
• Sue CS180 Pat
• Bob CS180 Jan

42
Beers-Bars-Drinkers Example

• Our running example for the course.

name
addr
license
Frequents
Serves
Bars
Likes
Beers
Drinkers
name
manf
name
addr
43
Multiplicity of Relationships
Many-many
Many-one
One-one

• Representation of Many-One
• E/R arrow pointing to one.
• Rounded arrow exactly one.

44
ExampleDrinkers Have Favorite Beers
name
addr
license
Frequents
Serves
Bars
Likes
Beers
Drinkers
Favorite
name
manf
name
addr
45
One-One Relationships

• Put arrows in both directions.
• Design Issue
• Is the rounded arrow justified?
• Design Issue
• Here, manufacturer is an E.S.
• In earlier diagrams it is an attribute.
• Which is right?

Best-seller
Manfs
Beers
46
Attributes on Relationships
price
Sells
Bars
Beers

• Shorthand for 3-way relationship

price
Prices
Sells
Bars
Beers
47

• A true 3-way relationship.
• Price depends jointly on beer and bar.
• Notice arrow convention for multiway
  relationships all other E.S. determine one of
  these.
• Not sufficiently general to express any
  possibility.
• However, if price, say, depended only on the
  beer, then we could use two 2-way relationships
  price-beer and beer-bar.
• Or better just make price an attribute of beer.

48
Converting Multiway to 2-Way

• Baroque in E/R, but necessary in certain
  object-oriented models.
• Create a new connecting E.S. to represent rows of
  a relationship set.
• E.g., (Joe's Bar, Bud, 2.50) for the Sells
  relationship.
• Many-one relationships from the connecting E.S.
  to the others.

BBP
The-Bar
The-Beer
The-Price
Bars
Beers
Price
49
Roles

• Sometimes an E.S. participates more than once in
  a relationship.
• Label edges with roles to distinguish.

Husband Wife d1 d2 d3 d4
Married
husband
wife
Drinkers
50

Buddy1 Buddy2 d1 d2 d1 d3 d2 d1 d2 d4
Buddies
1
2
Drinkers

• Notice Buddies is symmetric, Married not.
• No way to say symmetric in E/R.
• Design Question
• Should we replace husband and wife by one
  relationship spouse?

51
More Design Issues

• 1. Subclasses.
• 2. Keys.
• 3. Weak entity sets. (Next class.)

52
Subclasses

• Subclass special case fewer entities more
  properties.
• Example Ales are a kind of beer. In addition to
  the properties ( attributes and relationships)
  of beers, there is a color attribute for ales.

53
E/R Subclasses

• Assume subclasses form a tree (no multiple
  inheritance).
• isa triangles indicate the subclass relation.

Beers
name
manf
isa
Ales
color
54
Different Subclass Viewpoints

• 1. E/R viewpoint An entity has a component in
  each entity set to which it logically belongs.
• Its properties are the union of the properties of
  these E.S.
• 2. Contrasts with object-oriented viewpoint An
  object (entity) belongs to exactly one class.
• It inherits propertiesof its superclasses.

Beers
name
manf
isa
Petes Ale
Ales
color
55
Multiple Inheritance

• Theoretically, an E.S. could be a subclass of
  several other entity sets.

name
manf
name
manf
Beers
Wines
isa
isa
Grape Beers
56
Problems

• How should conflicts be resolved?
• Example manf means vintner for wines, bottler
  for beers. What does manf mean for grape beers?
• Need ad-hoc notation to resolve meanings.
• In practice, we shall assume a tree of entity
  sets connected by isa, with all isas pointing
  from child to parent.

57
Keys

• A key is a set of attributes whose values can
  belong to at most one entity.
• In E/R model, every E.S. must have a key.
• It could have more than one key, but one set of
  attributes is the designated key.
• In E/R diagrams, you should underline all
  attributes of the designated key.

58
Example

• Suppose name is key for Beers.
• Beer name is also key for ales.
• In general, key at root is key for all.

Beers
name
manf
isa
Ales
color
59
Example A Multiattribute Key
number
hours
Courses
dept
room

• Possibly, the combination of hours room also
  forms a key, but we have not designated it as
  such.