Transcript The Entity-Relationship Model

Chapter 3:
Modeling Data in the
Organization
Modern Database Management
7th Edition
Jeffrey A. Hoffer, Mary B. Prescott, Fred R.
McFadden
© Prentice Hall, 2002
1
SDLC Revisited – Data Modeling is an
Analysis Activity
(figures 2.4, 2.5)
Project Identification
and Selection
Purpose –thorough analysis
Deliverable – functional system specifications
Project Initiation
and Planning
Analysis
Logical Design
Physical Design
Database activity –
conceptual data modeling
Implementation
Maintenance
Chapter 3
© Prentice Hall, 2002
2
THE E-R MODEL

A logical representation of the data for an
organization or for a business area
 Expressed in terms of entities, relationships
among entities, and the attributes of entities
and relationships
Chapter 3
© Prentice Hall, 2002
3
Sample E-R Diagram
Entity
Relationship
Cardinality
Chapter 3
© Prentice Hall, 2002
4
E-R Model Constructs

Entity: A person, place, object, event, concept in the user environment
about which the organization wishes to maintain data






Given a noun name
Person: EMPLOYEE, STUDENT, FACULTY
Place: STORE, CLASS ROOM, HOSTEL
Object: COMPUTER, PROJECTOR, PEN
Event: REGISTRATION, RENEWAL, SALE, GRADUATION
Concept: MESS ACCOUNT, EXTRAS, PROJECT
– Entity Type – collection of entities that share common properties or
characteristics



Name is singular
Described only once in database
Ex: STUDENT(IDNO,NAME)
– Entity instance – A single occurrence of an entity type
 Each entity type will have many instances
 Ex: (200A1PS111,RAVI), (2003B1A1112,KIRAN)
Chapter 3
© Prentice Hall, 2002
5
E-R Model Constructs

Attribute - property or characteristic of an entity type that is of interest
to the organization
–

Ex: STUDENT (Idno, Name, Hostel, Roo_No, Cgpa)
Relationship: an association representing an interaction among the
instances of one or more entity types that is of interest to the
organization
– Given a verb phrase name

Relationship type: A meaningful association between entity types
– Ex: STUDENT registers COURSE
– FACULTY teaches COURSE
– STUDENT awarded SCHOLARSHIP

Relationship Instance – An association between entity instances
– Ex: 2005A1PS111 awarded MCN
– 2003A4PS123 awarded Pratibha
– 2004B1A2111 not awarded MCN
Chapter 3
© Prentice Hall, 2002
6
Figure 3-2 -- Basic E-R Notation
A special
entity that is
also a
relationship
Entity
symbol
s
Attribute
symbols
Relationshi
p symbols
Chapter 3
© Prentice Hall, 2002
7
What Should an Entity Be?

Entity: A person, place, object, event,
concept in the user environment about which
the organization wishes to maintain data
 SHOULD BE:
– An object that will have many instances
each with distinguishing characteristic
– An object that will be composed of multiple
attributes
– An object that we are trying to model
Chapter 3
© Prentice Hall, 2002
8
What Should an Entity Be?


Entity: A person, place, object, event, concept in the
user environment about which the organization
wishes to maintain data
SHOULD NOT BE:
– A user of the database system


ACCOUNTANT receives DEPOSITS
PROFESSOR uploaded GRADES
– An output of the database system (e.g. a report)


STUDENT receives CGCARD
PAYSLIP sent to FACULTY
– The names receives, sent to, uploaded refer to
activities that transfer or translate data but does
not indicate any association between data.
Chapter 3
© Prentice Hall, 2002
9
Figure 3-4
Inappropriate entities
System output
System user
Appropriate entities
Chapter 3
© Prentice Hall, 2002
10
Strong Vs Weak Entity Types

Strong Entity Type: An entity that exists
independently of other entity types.
– It has identifier, attribute or combination of
attributes that uniquely distinguish each
occurrence of that entity
– Ex: COURSE, STUDENT, FACULTY

Weak Entity Type: An entity type whose
existence depends on some other entity type
– It doesn’t have its own identifier
– Ex: COURSE and LECTURE SECTION
– EMPLOYEE and FAMILY MEMBER

Identifying owner: The entity type on which
the weak entity type depends
– Ex: COURSE and LECTURE SECTION
– EMPLOYEE and FAMILY MEMBER
Chapter 3
© Prentice Hall, 2002
11
Attributes

Attribute - property or characteristic of an entity type
that is of interest to the organization
– Has a noun name
– Ex: STUDENT (Idno, Name, Hostel, Room_No, CGPA)

Classifications of attributes:
– Required versus Optional Attribute
 Required: An attribute of an entity that must have a value for
each entity instance
 Optional : An attribute of an entity that may not have a value for
each entity instance
 Ex: Idno Vs Room No
Chapter 3
© Prentice Hall, 2002
12
Attributes

Attribute - property or characteristic of an entity type
that is of interest to the organization
– Has a noun name
– Ex: STUDENT (Idno, Name, Hostel, Room_No, CGPA)

Classifications of attributes:
– Simple versus Composite Attribute
 Simple: An attribute that cant be broken down into smaller
components
 Composite: An attribute that can be broken down into
component parts
 Ex: CGPA Vs Address
Chapter 3
© Prentice Hall, 2002
13
Figure 3-7 -- A composite attribute
An attribute
broken into
component parts
Chapter 3
© Prentice Hall, 2002
14
Attributes

Attribute - property or characteristic of an
entity type that is of interest to the
organization
– Has a noun name
– Ex: STUDENT (Idno, Name, Hostel, Room_No,
CGPA)

Classifications of attributes:
– Single-Valued versus Multivalued Attribute
– Multivalued: An attribute that can take more than
one value for a given entity instance

Chapter 3
Ex: STUDENT having multiple skills
© Prentice Hall, 2002
15
Attributes[2]

Attribute - property or characteristic of an entity type
that is of interest to the organization
– Has a noun name
– Ex: STUDENT (Idno, Name, Hostel, Room_No, CGPA)

Classifications of attributes:
– Identifier Attributes
 An attribute ( or combination of attributes) that uniquely
identifies individual instances of an entity type
– Ex: COURSE: comp code


Identifier attribute is underlined
Composite identifier:: An identifier that consists of a composite
attribute
– Ex: STUDENT Idno
Chapter 3
© Prentice Hall, 2002
16
Criteria for selecting Identifiers (keys)

Should not change its value over the life of each
instance of the entity type
– Ex: Idno Versus Record_No

Should not take null values
– NULL -> no value

Avoid intelligent identifiers
– e.g. containing locations or classifications that might change
– Intelligent identifiers: Idno, course code AAOC C311

Consider substituting single attribute surrogate
identifiers for long, composite keys
– Ex: comp code for Course_Code, Record_No for Idno
Chapter 3
© Prentice Hall, 2002
17
Figure 3-8 -- Entity with a multivalued attribute (Skill) and
derived attribute (Years_Employed)
What’s wrong with this?
Multivalued:
Derived
an employee can have
more than one skill
from date employed and current date
Chapter 3
© Prentice Hall, 2002
18
Figure 3-9a – Simple key attribute
The key is underlined
Chapter 3
© Prentice Hall, 2002
19
Figure 3-9b -- Composite key attribute
The key is composed
of two subparts
Chapter 3
© Prentice Hall, 2002
20
Figure 3-19 – an attribute that is both multivalued and composite
This is an
example of
time-stamping
Chapter 3
© Prentice Hall, 2002
21
More on Relationships


Recap:
Relationship: an association representing an interaction among
the instances of one or more entity types that is of interest to the
organization
– Given a verb phrase name
– Modeled as diamond with connecting lines

Relationship type: A meaningful association between entity
types
– Ex: STUDENT registers COURSE
– FACULTY teaches COURSE
– STUDENT awarded SCHOLARSHIP

Relationship Instance – An association between entity instances
– Ex: 2005A1PS111 awarded MCN
– 2003A4PS123 awarded Pratibha
– 2004B1A2111 not awarded MCN
Chapter 3
© Prentice Hall, 2002
22
More on Relationships

Relationships can have attributes
– These describe features pertaining to the association between the
entities in the relationship
– Associated with many-many or one-one relationship
– Ex: STUDENT registers COURSE. Which entity is the grade
associated with?


Two entities can have more than one type of relationship
between them (multiple relationships)
Associative Entity : An entity type that associates the instances
of one or more entity types and contains attributes that are
peculiar to the relationship between those entity instances
– combination of relationship and entity
– More on this later
Chapter 3
© Prentice Hall, 2002
23
Figure 3-11a A binary relationship with an attribute
Here, the date completed attribute pertains specifically to the
employee’s completion of a course…it is an attribute of the
relationship
Chapter 3
© Prentice Hall, 2002
24
Figure 3-12c -- A ternary relationship with attributes
Chapter 3
© Prentice Hall, 2002
25
Figure 3-10a Relationship type
3-10b Entity and Relationship instances
Chapter 3
© Prentice Hall, 2002
26
Degree of Relationships

Degree of a Relationship is the number of
entity types that participate in that relationship
 Most common relationship degrees are
– Unary Relationship



Relationship between the instances of a single entity
type.
They are also called “recursive relationships”
Ex: Employee manages employees
– Binary Relationship
– Ternary Relationship
Chapter 3
© Prentice Hall, 2002
27
Degree of Relationships [2]


Degree of a Relationship is the number of entity types that
participate in that relationship
Most common relationship degrees are
– Binary Relationship





Relationship between the instances of two entity types
Most commonly encountered
Ex: One-to-one :WARDEN takes care of one HOSTEL
One-to-Many: HOSTEL contains many ROOM s
Many-to-many: STUDENT graduating in many DEGREE s
– Ternary Relationship
 A simultaneous relationship among the instances of three entity
types
 Ternary relationships is not same as three binary relationships
 Ex: COURSE or SECTION, INSTRUCTOR, CLASSROOM ->
TIMETABLE
 All ternary relationships are represented as associative entities
Chapter 3
© Prentice Hall, 2002
28
Degree of relationships – from figure 3-2
One entity
related to
another of
the same
entity type
Chapter 3
Entities of
two different
types related
to each other
© Prentice Hall, 2002
Entities of three
different types
related to each
other
29
Unary relationships -- figure 3-12a
Chapter 3
© Prentice Hall, 2002
30
Binary relationships – figure 3-12b
Chapter 3
© Prentice Hall, 2002
31
Ternary relationships –figure 3-12c
Chapter 3
© Prentice Hall, 2002
32
Attribute or Relationship?

Should skills be an attribute of STUDENT or
Should there be relationship between
STUDENT and SKILL?
 So when should be an attribute be linked to
an entity type via a relationship?
– When the attribute has to be shared by multiple
instances of that entity type we can represent that
attribute as entity type
Chapter 3
© Prentice Hall, 2002
33
Cardinality of Relationships


Cardinality specifies the number of instances of entity B that can
(or must) be associated with each instance of entity A.
One – to – One
– Each entity instance in the relationship will have exactly one related
entity instance

One – to – Many
– An entity instance on one side of the relationship can have many
related entity instances, but an entity instance on the other side will
have a maximum of one related entity instance

Many – to – Many
– Entity instances on both sides of the relationship can have many
related entity instances on the other side
Chapter 3
© Prentice Hall, 2002
34
Cardinality Constraints

Minimum Cardinality
– If zero, then optional
– If one or more, then mandatory

Maximum Cardinality
– The maximum number
Chapter 3
© Prentice Hall, 2002
35
Sample E-R Diagram
Entity
Relationship
Cardinality
Chapter 3
© Prentice Hall, 2002
36
Basic relationship with only maximum cardinalities showing –
figure 3-16a
Mandatory minimum cardinalities – figure 3-17a
Chapter 3
© Prentice Hall, 2002
37
Figure 3-17c
Optional cardinalities with unary degree, one-to-one relationship
Chapter 3
© Prentice Hall, 2002
38
Figure 3-21b -- Professors and courses (fixed upon constraint)
Here,max
cardinality
constraint is 4
Chapter 3
© Prentice Hall, 2002
39
Figure 3-13a A unary relationship with an attribute. This
has a many-to-many relationship
Representing a bill-of -materials structure
Chapter 3
© Prentice Hall, 2002
40
Examples of multiple relationships – entities can be
related to one another in more than one way
Figure 3-21a Employees and departments
Chapter 3
© Prentice Hall, 2002
41
Figure 3-15:
Multivalued attribute
vs. relationship.
Alternative approaches
Chapter 3
© Prentice Hall, 2002
42
Figure 3-5: Strong and weak entities
Strong entity
Chapter 3
Identifying relationship
© Prentice Hall, 2002
Weak entity
43
Associative Entities

It’s an entity – it has attributes

AND it’s a

When should a relationship with attributes instead be an
associative entity?
– All relationships for the associative entity should be many-tomany
– The associative entity could have meaning independent of
the other entities
– The associative entity preferably has a unique identifier, and
should also have other attributes
– The associative may be participating in other relationships
other than the entities of the associated relationship
– Ternary relationships should be converted to associative
entities (p102)
Chapter 3
relationship – it links entities together
© Prentice Hall, 2002
44
Figure 3-11b: An associative entity (CERTIFICATE)
Associative entity involves a rectangle with a diamond
inside.
Note that the many-to-many cardinality symbols face
toward the associative entity and not toward the other
entities
Chapter 3
© Prentice Hall, 2002
45
Figure 3-13c -- an associative entity – bill of materials structure
This could just be a relationship with
attributes…it’s a judgment call
Chapter 3
© Prentice Hall, 2002
46
Figure 3.18 -- Ternary relationship as an associative entity
Chapter 3
© Prentice Hall, 2002
47
Figure 3-22
E-R diagram for Pine
Valley Furniture
Chapter 3
© Prentice Hall, 2002
48
CASE 1

A company has a number of employees. The
attributes of EMPLOYEE include Employee_ID,
Name, Address, and Birthdate. The company also
has several projects. Attributes of PROJECT include
Project_Id, Project_Name, and Start_Date. Each
employee may be assigned to one or more projects,
or may not be assigned to a project. A project must
have at least one employee assigned, and may have
any number of employees assigned. An employee’s
billing rate may vary by project and the company
wishes to record the applicable billing rate for each
employee when assigned to a particular project.
Chapter 3
© Prentice Hall, 2002
49
CASE 1
Chapter 3
© Prentice Hall, 2002
50