Entity-Relationship Data Model

1
Entity-Relationship Data Model

• CS 157A
• Professor Sin-Min Lee
• Student Yen-Chu Pan

2
Elements of E-R Model

• In the E/R model, the structure of data is
  represented graphically, as an entity-relationshi
  p diagram, using three Principal element types
• Entity Sets.
• An entity is an abstract object of some sort, and
  a collection of similar entities forms an entity
  set.
• Attributes
• The properties of the entities in the set.
• Relationship
• The connections among two or more entity Sets

3
Example of Elements of E-R Model

• Entity Sets
• Departments
• Professors
• Students
• Administrators
• Attributes
• Name of Departments, Phone No., Address...
• Name, SSN, Address of Professors...
• Relationship
• Students and Professors are under a certain
  department
• Admin manage the campus/ departments

4
Example of the 3 elements in E/R Diagram
5
Classification of Constraints

1. Keys
2. Single-value constraints
3. Multi-valued constraints
4. Mapping Cardinalities and Participation
   Constraints

6
Key in the E/R Model

• Superkey is a set of one or more attributes that,
  taken collectively, for us to identify uniquely
  an item in the entity set. For example,
  customer-id is a superkey.
• Candidate key is a minimal superkey. For
  example, customer-name and customer-street is
  sufficient to distinguish among members of the
  customer entity set. Then customer-name,
  customer-street is a candidate key.
• Primary key denotes a candidate key that is
  chosen by the database designer as the principal
  means of identifying items within an entity set.
  the primary key should be chosen such that its
  attributes are never, or very rarely, changed.
  For example, Social-security numbers are
  guaranteed to never changed.

7
Single/Multi-valued attributes

• Single-valued attributes are attributes that only
  have a single value for a particular entity.
• Multi-valued attributes refers to items that are
  not singled-value and Null valued. For example,
  consider an employee entity set with the
  attribute phone-number. An employee may have
  zero, one, or several phone numbers different
  employee may have different numbers of phones.

8
(No Transcript)
9
Mapping Cardinalities or Cardinality ratios

• Express the number of items to which another item
  can be associated via a relationship set
• Are most useful in describing binary relationship
  sets. For a binary relationship set R between
  entity sets A and B, the mapping cardinality must
  be one of the following
• One to One
• One to Many
• Many to One
• Many to Many

10
Participation Constraints

• The participation of an entity set E in a
  relationship set R is said to be total, if every
  item in E participates in at least one
  relationship in R. If only some items in E
  participate in relationship R, the participation
  of entity set E in relationship R is said to be
  partial.

11
Weak Entity Sets

• There is an occasional condition in which an
  entity sets key is composed of attributes some
  or all of which belong to another entity set.
  Such an entity set is called a weak entity set.

12
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.

13
Requirements for Weak Entity Sets

• We cannot obtain key attributes for a weak entity
  set indiscriminately. Rather, if E is a weak
  entity set then its key consists of
• Zero or more of its own attributes, and
• Key attributes from entity sets that are reached
  by certain many-one relationship from E to other
  entity sets. These many-one relationship are
  called supporting relationships for E.

14
Discriminator (cont.)

• 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.

15
Identifying a Weak Entity Type

• A Weak entity type doesnt have a primary key.
• If X is a weak entity type and Y is the entity
  type on which X is dependent.
• We form a primary key for X by combining the
  primary key of Y which one or more attributes,
  called discriminator or partial key, from X.
• In an E/R Diagram, a partial key is usually
  dash-underlined.
• e.g., primary key for DEPENDENT Employee No.,
  DName.

16

Doted-line double-line
17
References

• Peter Chens website http//bit.csc.lsu.edu/chen
  /chen.html
• Database Systems A First Course, J.D. Ullman
  J. Widom
• http//www-db.stanford.edu/ullman/fcdb.html
• http//www.cs.sjsu.edu/faculty/lee/cs157/25Sp157AL
  4.ppt
• http//www.cs.sjsu.edu/faculty/lee/cs157/25Sp157AL
  5Enhanced20ER-diagram.ppt