1

1
The E-R Model
CS 157A Lecture 3

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

2
(No Transcript)
3
(No Transcript)
4
(No Transcript)
5
E-R Model

• The E-R model is not intended to be associated
  with any particular database model.
• E-R diagrams are intended to allow humans the
  ability to capture more of the applications
  meaning.

6
(No Transcript)
7
History of E-R Model

• E-R Model was proposed by Dr. Peter Chen
  (currently professor at Louisiana State
  University)
• Chens original paper on E-R Model is the 35th
  most sited paper in computer science
• Chen has written papers interconnecting E-R model
  and linguistics

8
The Entity-Relationship Model (History)

• Developed by Peter Chen in the 1970s
• Several variations have evolved
• All are designed towards the concise capture of
  the application semantics in terms appropriate
  for subsequent mapping to a specific database
  model.
• It is currently the most widely used.

9
(No Transcript)
10
The Entity-Relationship Approach

• Entity an object that exists and is
  distinguishable from other objects. i.e. person,
  place, thing, event or concept about which
  information(attributes) is recorded. The basic
  unit of the E-R model.
• The structure of an entity is called its schema.

11
More Terminology

• Object things in the real world that can be
  observed and classified because they have related
  properties
• Entity the groupings we use when we categorize
  the objects. Sometimes called a class.

12
(No Transcript)
13
(No Transcript)
14
Three Basic Graphical Symbols in an ER Diagram

• Rectangles are used to model conceptual data
  units or data objects.
• Circles are used to model attributes. Attributes
  are the characteristics, components or properties
  of entities.
• Diamonds are used to model the structural
  associations that exist between entities.

Course
CID
Enroll
15
(No Transcript)
16
(No Transcript)
17
BASIC CONCEPTS

• There are 3 basic notions in the E-R Model
• Entity Sets
• Relationship Sets
• Attributes

18
AN ENTITY

• An entity is a thing or object in the real
  world that is distinguishable from all other
  objects. It has an unique set of properties that
  may uniquely identify an entity. For example, a
  student entity has three attributes name ,
  student-id, and social-security numbers.

John 1222 123-12-2244
STUDENT entity
19
ENTITY SETS

• An entity set is a set of entities that share the
  same properties or attributes. Entity sets do not
  need to be disjoint. For example, a customer can
  also be an employee.

John 1222 123-12-2244
Kathy 2223 223-22-2245
Steve 3222 723-12-2244
John Manager
Kathy Teller
Steve Teller
Employee (Entity Set)
Customer (Entity Set)
20
ATTRIBUTE

• An entity is represented by a set of attributes.
  Attributes are descriptive properties possessed
  by each member of an entity set.
• Example

STUDENT entity
STUDENT
ss
student-id
address
name
Attributes
21
ATTRIBUTE TYPES

• There are several different types of attributes.
• Simple and Composite
• Single-valued and Multivalued
• Derived
• Null

22
ATTRIBUTE TYPES

• Simple attributes are not divided into subparts.
• Composite attributes can be divided into
  subparts.
• Using composite attributes in a design schema
  is a
• good choice if a user will wish to refer to
  an entire
• attribute on some occasions.

23
ATTRIBUTE TYPES

• Example address (Composite attribute)
• street city state zip
• street-number street-name apartment-number

Composite attribute
24
ATTRIBUTE TYPES

• Single-valued attributes are attributes that
  only have a single value for a particular entity.
• Multi-valued attributes refers to entities 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.

25
ATTRIBUTE TYPES

• Null attribute is used when an entity does not
  have a value for an attribute.
• Derived attributes refer to an attribute that
  can be derived
• from other related attributes or entities. For
  instance, suppose that Age and Date-of-birth are
  attributes of the CUSTOMER entity set. We can
  calculate Age from Date-of-birth. In this case,
  Age is a derived attribute.

26
RELATIONSHIP SETS

• A relationship is an association among several
  entities.
• A relationship set is a set of relationships of
  the same type. Consider the two entity sets
  customer and loan. We define the relationship
  set borrower to denote the association between
  customers and bank loans that the customers have.

321-12-3123 Jones Main Harrison
019-28-3746 Smith North Rye
555-55-5555 Jackson Dupont Woodside
321-12-3123 Curry North Rye
321-12-3123 Adam Spring Pittsfield
L-17 1000
L-23 2000
L-14 1500
L-19 500
L-16 1300
loan
customer
27
RECURSIVE RELATIONSHIP

• Recursive relationship- the same entity set
  participates in a relationship set more than
  once, in different roles. The function that an
  entity plays in a relationship is called that
  entitys role. For example, consider an entity
  set employee that records information about all
  employees of the bank. We may have a
  relationship set works-for that is modeled by
  ordered pairs of employee entities. The first
  employee of a pair takes the role of worker,
  whereas the second takes the role of manager.

employee-name
telephone-number
employee-id
works-for
manager
employee
worker
Recursive Relationship
28
BINARY RELATIONSHIP SETS

• Binary relationship set - relationship that
  involves two identity sets.
• Most of the relationship sets in a database
  system are binary. Occasionally, however,
  relationship set involve more than two entity.

Owns
PROPERTY-FOR-RENT
OWNER
Binary relationship called Owns
29
TERNARY RELATIONSHIP SET

• Ternary relationship set - relationship that
  involves three identity sets

DEGREE
earned
PERSON earned DEGREE on DATE
DATE
PERSON
Ternary Relationship Set
30
DEGREE OF A RELATIONSHIP SET

• Degree of a relationship set is the number of
  entity sets that participate in a relationship
  set. For example, a binary relationship set is
  of degree 2 a ternary relationship set is of
  degree 3.

31
CONSTRAINTS

• An E-R enterprise scheme may define certain
  constraints to which the contents of a database
  must conform. The two of most important types of
  constraints are Mapping Cardinalities and
  Participation Constraints.
• Participation Constraints
• The participation of an entity set E in a
  relationship set R is said to be total, if every
  entity in E participates in at least one
  relationship in R. If only some entities in E
  participate in relationship R, the participation
  of entity set E in relationship R is said to be
  partial.

32
CONSTRAINTS

• Mapping Cardinalities or Cardinality ratios
• Express the number of entities to which another
  entity 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

33
MAPPING CARDINALITIES

• One to one
• An entity in A is associated with at most one
  entity in B, and an entity in B is associated
  with at most one entity in A.

A
B
B1
A1
B2
A2
A3
B3
34
MAPPING CARDINALITIES

• One to many
• An entity in A is associated with any number
  (zero or more) of entities in B. An entity in B,
  however, can be associated with at most one
  entity in A.

A
B
B1
A1
B2
A2
B3
A3
B4
35
MAPPING CARDINALITIES

• Many to one
• An entity in A is associated with at most one
  entity in B. An entity in B, however, can be
  associated with any number (zero or more) of
  entities in A.

A
B
A1
B1
A1
B2
A2
B3
A3
36
MAPPING CARDINALITIES

• Many to many
• An entity in A is associated with any number
  (zero or more) of entities in B, and an entity in
  B is associated with any number (zero or more) of
  entities in A.

A
B
A1
B1
A1
B2
A2
B3
A3
37
KEYS

• A key allows us to identify a set of attributes
  that
• suffice to distinguish entities from each
  other. Keys
• also help uniquely identify relationships,
  and thus
• distinguish relationships from each other.
  There are
• three types of keys
• Super key
• Candidate key
• Primary key

38
KEY

• Superkey is a set of one or more attributes
  that, taken collectively, us to identify
  uniquely an entity 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 entities 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.

39
RELATIONSHIP SETS

• Primary key of an entity set allows us to
  distinguish among entities of the set. Similar
  mechanism is needed in order to distinguish among
  the various relationships of a relationship set.
• The structure of the primary key for the
  relationship set depends on the mapping
  cardinality of the relationship set.

40
RELATIONSHIP SETS

• For example, suppose that there is a relationship
  set Depositor, with attribute access-date,
  between Customer relation and Account relation.
  Suppose that the relationship set is many to
  many. Then the primary key of Depositor consists
  of the union of the primary keys of Customer and
  Account. If a customer can have only one
  account- that is, if the Depositor relation is
  many to one from Customer to Account- then the
  primary key of the Depositor relationship is
  simply the primary key of customer.

41
RELATIONSHIP SETS

• Customer (ss , name)
• Account (account-num)
• Depositor (ss, account-num, access-date)

John 123-12-2244
Kathy 223-22-2245
Steve 723-12-2244
A-101
A-215
A-102
10 May 2002
24 May 2002
3 June 2002
20 June 2002
21 June 2002
Account
Depositor
Customer
42
DESIGN ISSUE

• Use of Entity Sets versus Attributes
• Consider the entity set employee with attributes
  employee-name and telephone-number.
• Treating a telephone as an attribute
  telephone-number implies that employees have
  precisely one telephone number each.
• Treating a telephone as an entity telephone
  permits employees to have several telephone
  number ( including zero) associated with them.
  However, we could instead easily define
  telephone-number as a multivalued attribute to
  allow multiple telephones per employee.

43
USE OF ENTITY SETS VERSUS ATTRIBUTES

• Treating a telephone as an attribute
• Treating a telephone as an entity

John 408-345-5366
Kathy 408-222-3455
Steve 714-555-6366
EMPLOYEE (employee-name, telephone-number)
408-888-3565 cell-phone
408-345-5366 video-phone
408-222-3455 cell-phone
714-555-6366 cell-phone
John
Kathy
Steve
Alex
TELEPHONE(telephone-number, type)
EMPLOYEE (employee-name)
44
ENTITY-RELATIONSHIP DIAGRAM

• Ellipses, which represent attributes
• Diamonds, which represent relationship sets
• Line, which link attributes to entity sets and
  entity sets to relationships sets
• Double ellipses, which represent multivalued
  attributes
• Double lines, which indicate total participation
  of an entity
• in a relationship set
• Double rectangles, which represent weak entity
  sets

45
Example of the 3 elements in E/R Diagram
46
Classification of Constraints

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

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

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

49
(No Transcript)
50
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

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

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

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

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

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

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

57

Doted-line double-line
58
Detailed Conceptual Design
Employee
Emp No unique identifier of an emp. Identifier Emp
Name name of an employee Composite First
Name first name of an employee Mid
Initials middle initials of an employee Last
Name last name of an employee NID national id of
an employee Unique Address address of an
employee Salary salary of an employee Gender sex
of an employee DOB birth date of an employee
59
Detailed Conceptual Design
60
(No Transcript)
61
Conceptual Design with Weak Entities