Lecture1-ER Model-1
Lecture1-ER Model-1
Lecture1-ER Model-1
Entity-Relationship Model
CMPS 244
Your role as a Database
Developer
• Gather and analyze requirements given a database
description
STUDENT COURSE
8
Entity Attributes
• Each entity has a set of attributes
• All entities in an entity set have the same set of attributes
9
Simple Attributes
id
STUDENT
Composite Attributes
firstname lastname
id name
STUDENT
Multivalued Attributes
firstname lastname
hobby id name
STUDENT
Derived Attributes
hobby id name
numofcourses
STUDENT
Entity Constraints
hobby id name
numofcourses
STUDENT
Entity Constraints (2)
firstname lastname
hobby id name
numofcourses
STUDENT name
year
Identifier credithours
ENROLLED
COURSE
Relationship Sets (3)
Identifier credithours
COURSE
PREREQUISITE
Relationship Attributes
firstname lastname
hobby id name
numofcourses
STUDENT name
year
Identifier credithours
grade
ENROLLED
COURSE
Relationship Constraints
• Cardinality Constraints
• One to One
• One to Many
• Many to Many
• Participation Constraints
• Total Participation
• Partial Participation
Relationship Constraints
• Cardinality Constraints
• One to One
• One to Many
• Many to Many
• Participation Constraints
• Total Participation
• Partial Participation
One to One Relationships
• In a one to one (1:1) relationship, each entity of either
entity set is related to at most one entity of the other set
• Represented in an ER diagram by labeling each edge with a
“1”
1 1
STUDENT PARTNER
RELATIONSHIP
Each student can be in a relationship with one partner only and each partner
has only one student as a partner
24
One to Many Relationships
• In a one to many (1:m) relationship, an entity of the first set can
be connected to more than one entity of the second set
• But each entity of the second set can be connected to at most one
entity of the first set
• Represented in an ER diagram by labeling the edge connected to
the first entity with a “1” and the edge connected to the second
with “m”
1 m
PROFESSOR STUDENT
ADVISES
A professor can advise more than one student but a student can have at most
one advisor
25
Many to Many Relationships
m n
STUDENT COURSE
ENROLLED
A student can enroll in more than one course and a course can have many
students enrolled in it
26
Cardinality of Relationships
Summary
27
Cardinality of Recursive
Relationships
• A recursive relationship associates two entities from
the same entity set
PROFESSOR
manager 1 m managee
MANAGES
A professor can manage more than one professor but a professor can be
managed by at most one professor
Label the edges between the relationship and the entity sets with roles
Relationship Related
Constraints
• Cardinality Constraints
• One to One
• One to Many
• Many to Many
• Participation Constraints
• Total Participation
• Partial Participation
Relationship Participation
m n
STUDENT COURSE
ENROLLED
Each student must be enrolled in a course and each course must have students
enrolled in it
1 m
PROFESSOR STUDENT
ADVISES
Each student must be advised by a professor but some professors do not have
to advise students
Components of an Entity-
Relationship Diagram
• Entity Related Components
• Entity Sets
• Entity Attributes
• Entity Constraints