Lecture1-ER Model-1

Download as pdf or txt
Download as pdf or txt
You are on page 1of 38

Database Systems

Entity-Relationship Model
CMPS 244
Your role as a Database
Developer
• Gather and analyze requirements given a database
description

• Design the database


• Define entities, attributes, relationships and constraints

• Create the database using a DBMS

• Manipulate the data in the database in an efficient way

• Build stand-alone applications on top of your database


Your role as a Database
Developer
• Gather and analyze requirements given a database
description

• Design the database


• Define entities, attributes, relationships and constraints

• Create the database using a DBMS

• Manipulate the data in the database in an efficient way

• Build stand-alone applications on top of your database


Entity-Relationship Model

• A conceptual model that is commonly used for designing a


database schema
• A database schema contains the entities, attributes,
relationships and constraints of the desired database
• A database schema is presented in the Entity-Relationship (ER)
model as an Entity-Relationship (ER) diagram
• There exist fairly mechanical ways to convert ER diagrams to
actual databases
• In this class, we will learn what is called Chen’s notation, but
various variations also exist (check additional resources at the
end of the module section on Moodle)
Designing a database using the
Entity-Relationship Model
• The questions to ask:
• What are the entities (objects, individuals) in the
database?
• Which relationships exist among the entities?
• What attributes do these entities and relationships
have?
• What are the constraints over the entities and the
relationships?

• The answers are represented in an ER diagram


Components of an Entity-
Relationship Diagram
• Entity Related Components
• Entity Sets
• Entity Attributes
• Entity Constraints

• Relationship Related Components


• Relationship Sets
• Relationship Attributes
• Relationship Constraints
Components of an Entity-
Relationship Diagram
• Entity Related Components
• Entity Sets
• Entity Attributes
• Entity Constraints

• Relationship Related Components


• Relationship Sets
• Relationship Attributes
• Relationship Constraints
Entity Sets
• Entity: thing or object
• E.g., student, course, department

• Entity Set: set of similar entities


• E.g., the STUDENT entity set is the set of all students

• In an ER diagram, each entity set is represented by a


rectangle

STUDENT COURSE

8
Entity Attributes
• Each entity has a set of attributes
• All entities in an entity set have the same set of attributes

• Types of entity attributes


• Simple
• Composite
• Multivalued
• Derived

9
Simple Attributes

• Can have a single value (e.g. Integer, string, etc.)

• Represented by an oval connected by a line to the


rectangle representing its entity set

id

STUDENT
Composite Attributes

• Combines two or more attributes


• Represented by a hierarchy of ovals

firstname lastname

id name

STUDENT
Multivalued Attributes

• Can have more than one value


• Represented by a double-lined oval

firstname lastname

hobby id name

STUDENT
Derived Attributes

• Derived from other entities (i.e., not an inherent


property of its entity)
• Represented by a dash-lined oval
firstname lastname

hobby id name
numofcourses

STUDENT
Entity Constraints

• Each entity set has a key (i.e., one or more attributes


whose values uniquely identify an entity)

• The attributes making up the key are underlined


firstname lastname

hobby id name
numofcourses

STUDENT
Entity Constraints (2)

• Some entities cannot be


uniquely identified by the
value of a single attribute name
year
• but may be identified by
the combination of two or Identifier credithours
more attributes

• Several attributes together


COURSE
make up a composite key
Components of an Entity-
Relationship Diagram
• Entity Related Components
• Entity Sets
• Entity Attributes
• Entity Constraints

• Relationship Related Components


• Relationship Sets
• Relationship Attributes
• Relationship Constraints
Relationship Sets

• Relationship: An association between two entities


• E.g., Alain Malek is enrolled in course CS123

• Relationship Set: set of all entity pairs related by the


relationship
• E.g., The ENROLLED relationship set is
{(Alain Malek, CS123), (George Halim, CS234), …}

• In an ER Diagram, each relationship set is


represented by a diamond connecting the related
entities
Relationship Sets (2)

firstname lastname

hobby id name
numofcourses

STUDENT name
year

Identifier credithours

ENROLLED
COURSE
Relationship Sets (3)

• Recursive Relationship: A relationship can associate


two entities from the same entity set
name
year

Identifier credithours

COURSE

PREREQUISITE
Relationship Attributes

• Relationships can also have attributes

• A relationship attribute is not a property of either


entity participating in the relationship but a property
of the relationship itself

• A relationship attribute is represented by an oval


attached to the diamond representing the
relationship
Relationship Attributes (2)

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

• In a many to many (m:n) relationship, an entity of either


set can be connected to many entities of the other set
• Represented in an ER diagram by labeling the edge
connected to one entity with an “n” and the edge
connected to the other with an “m”

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

One to One One to Many Many to Many

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

• An entity set totally participates in a relationship if


each entity in the entity set must take part in the
relationship

• An entity set partially participates in a relationship if


one or more of its entities do not have to take part in
the relationship

• In an ER diagram, a total participation of an entity


set is represented by a double line
Relationship Participation (2)

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

• Relationship Related Components


• Relationship Sets
• Relationship Attributes
• Relationship Constraints
University Database

• A student has a name, which is composed of a given


name and a family name, and a student ID. Each
student is uniquely identified by his/her student ID.

• A course has a subject and a course ID. For each


course, we want to record the number of students
taking that course and the type of equipment being
used for the course. A course is uniquely identified
by its course ID.
University Database (2)

• A student can be enrolled in an arbitrary number of


courses, and an arbitrary number of students can be
enrolled in a course. For each course the students are
enrolled in, they receive a lab mark and an exam
mark.

• A course cannot exist if there are no students


enrolled in it.
University Database (3)

• A school is distinguished by the honor's degree that


it awards. We also want to record to which faculty a
school belongs. A student is registered with at most
one school, while a school can have an arbitrary
number of students.

• A student belongs to a year of study. A year of study


is identified by a number between 1 and 4. A student
is registered for only one year of study, but each year
of study can have many students.
University Database (4)

• For each member of the staff, we want to record


their name and their room number. A member of the
staff is identified by the combination of these two
pieces of info. Staff are appraised by other staff. A
member of the staff has no more than one appraiser.

• Students can be allocated to a member of the staff as


their tutor. A student can have no more than one
tutor. The tutor and the student agree upon a time
slot for regular meetings.
University Database (5)

• For each year of study, there is one member of the


staff who acts as the year tutor. A member of the
staff can only be responsible for one year of study.

• Courses are taught by members of the staff. A


course can have several teachers, and a staff member
can teach several courses.

Draw the ER diagram representing the described university database


Further Readings

• Chapter 3 of the main textbook

You might also like