Database Systems - Entity Relationship Modeling (Chapter 4/2)

Entity-Relationship Modeling
In this section you are introduced to Entity-
Relationship modeling a popular way to
design databases.

Why Data Modeling?
•To understand “meaning” or
“semantics” of data
•To communicate about information
requirements (among the project
members and customers)
•To use it as conceptual data model of
database (our interest)

Understanding Data
The understanding of data involves
understanding:
Each user’s perspective of the data
The nature of the data independent of its
physical representation
The use of the data across user views.

Good Data Model Characteristics
SNO Characteristic
1 Structural Validity
2 Simplicity
3 Expressibility
4 Nonredundancy
5 Shareability
6 Extensibility
7 Integrity
8 Diagrammatic Representation

ER Model Concepts
Entity
Entity Type
Entity Set
Relationship
Relationship Type
Relationship Set
Week Entities

Entity
An entity is an “object” in the real world that is
distinguishable from all other objects.
An entity may be a concrete or an abstract object.
Each entity may have one or more of properties.

Entity Examples
Each person in an organization is an entity. It is a
concrete entity.
If the organization employs 150 persons, then it
means the organization has 150 person entities.
If there is a person by name john, then john is an
entity. We used the term john even though the
person has first name, last name, designation, etc.,
because the name uniquely distinguishes the entity
from the remaining 149 entities.

Entity Examples (contd...)
Similarly each department in an organization is an
entity. (abstract)
Each purchase order is an entity. (concrete)
Each course in a university is an entity. (abstract)
Each university itself is also an entity. (concrete)
Each book is an entity. (concrete)
Each flight reservation is an entity. (abstract)

Entity Attributes
An attribute of an entity describes a specific
characteristic or property of the entity.
For example, first name is an attribute of person
entity. color is an attribute of entity car. Another
attribute is price.
Depending the database requirements, you choose
the attributes for entities.

Entity Attribute Examples
Attributes of some entity types are as follows:
Entities Attributes
person person id, first name, last name,
dob, address
book book id, title, author, publisher,
price
course course id, title, teacher

Entity Types
An entity type is a named set of attributes of similar
entities.
Examples of entity types Person, Book and Course.
Person: {person id, first name, last name, dob,
address }
Book: {book id, title, author, publisher, price}
Course: {course id, title, teacher}
Note that we used id columns (person id, book id,
course id) in each entity type so that we can uniquely
identify each entity.

Entity Set
An entity set is a named collection of entities having
the same attributes.
Example:
An entity set for persons could be as follows:
Persons:
{<1001, ‘Hari’, ‘KVS’, 1962-01-20, ‘Hyderabad’, ‘301, Banjara Hills’>,
<1002, ‘Mathews’, ‘Philips’, 1965-08-10, ‘Bangalore’, ‘10, Nagavara’>
}
This entity set has two entities.

Simple Attribute
An attribute that can not be divided into two or more
components is a simple attribute.
Examples of simple attributes are:
person id, first name, last name and dob.

Composite Attribute
A composite attribute consists of multiple components where
each component has its own independent existence.
An example is attribute address. An example value for address
is “305, 2nd C Main Road, 8th Block, Koramangala, Bangalore”
where each component has it own existence. “305” is a house
number, “2nd C Main Road” is street , “8th Block” indicates
small area, and “Koramangala” is an area of “Bangalore”.
Based on the application requirements, the attribute is
retailed as composite attribute or split into its component
simple attributes.

Single-Valued Attributes
A single-valued attribute is an attribute that can
contain only single value in each entity of an entity
type.
For example, attributes person id, first name, last name and
dob in the following example are single valued attributes:
person- id fist name last name phone dob
1001 Akhil M 7498750138
8035621234
1997-01-18
1002 Bhargav Tamiri 9498012398 1997-03-15
1003 Krishna S 8012398654
9449010137
1990-11-25

Multi-Valued Attributes
A multi-valued attribute is an attribute that can
contain multiple values in each entity of an entity
type.
For example, attribute phone may have multiple
phone numbers in each entity of entity type Person.
person- id fist name last name phone dob
1001 Akhil M 7498750138
8035621234
1997-01-18
1002 Bhargav Tamiri 9498012398 1997-03-15
1003 Krishna S 8012398654
9449010137
1990-11-25

Derived Attributes
A derived attribute is an attribute that has its value
derived from one or more attributes not necessarily
from the same entity type.
For example, attribute age in Person is a derived
attribute as its value is derived from dob.
person-
id
fist
name
… phone dob age
1001 Akhil .. 7498750138
8035621234
1997-01-18 16
1002 Bhargav … 9498012398 1997-03-15 16
1003 Krishna … 8012398654
9449010137
1990-11-25 23

Domain
A domain is a set of values an attribute of an entity
set can have in an entity.
Sample domains:
Attribute Domain
first name set of 30 character strings
color red, green, silver, white
price 30000 to 70000
rating 0 to 10

Keys
A key is a minimal set of attributes whose values
uniquely identify an entity in an entity set.
Entity Set Key
Persons person id
Books book id
Courses course id
Student Marks roll number, subject id

Candidate Key and Primary Key
An Entity Set or Entity Type may have more than one
key. Each key is known as candidate key. One of the
candidate keys is designated as primary key used as
the main key for the entity set.
Example: Account(customer number, PAN number,
aadhaar number, first name, last name, balance)
Candidate keys: customer number, PAN number,
aadhaar number
Primary key: customer number

Diagrammatic Representation of
Entity Type
Entity type is represented using a rectangular box
with entity type name written inside it.
Examples of Entity Types:
Person Book

Entity Set
Diagram used for entity sets is the same that is used
for entity types:
Examples of Entity Sets:
Persons Books

Entity Type Versus Entity Set
1. In an ER model, use either entity types or entity
sets.
2. Do not use a mix of entity types and entity sets in
the ER diagrams.
3. Use naming conventions consistently.
4. Though the slides use singular names for entity
types and plural names for entity sets, you can
use any name you want.

Entity Type Attributes
Entity type attributes are
represented in the bottom
portion of rectangular box
separated by a line from
entity type name.
An examples of Entity Type
with attributes:
Employee
Employee Number
First name
Last name
Date of birth
Designation
Department Name

Relationship
A relationship is an association between two or more entities.
For example, there is a borrow relationship between entity
sets Persons and Books to indicate that a person (entity of
entity set Persons) borrows one or more books (entities of
entity set Books). We can also say the relationship between
Books and Persons is borrowed by to indicate a book is
borrowed by zero or one person.
Primary key values of participating entities will identify the
relation.

Relationship Type
A relationship type represents collection of
associations of similar type.
Similar types of associations means associations
having the same attributes from participating entity
types or sets. Attribute set (person id, book_id)
represents a relationship type. A name that could be
given to the relationship type is Borrower. The
relationship type can be specified as Borrower(person
id, book id).

Relationship Set
A relationship set is an instance of a relationship
type. It means one or more associations of a
relationship type is a relationship set.
For example relationship set Borrowers of relationship
type Borrower(person id, book id) could be as follows:
{ <1001, 12956>, <1001, 23567>, <1002, 12345>}

Relationships Explained using
Semantic Net
Person BookBorrows
p1
p2
p3
b1
b2
b3
b4
r1
r2
r3

Relationship
Relationship between two entity types is
represented with a line as shown in the following
diagram:
Person Book
Borrows

Degree of Relationship Type
Number of entity types involved in a relationship
type is called degree of the relationship type.
Person Book
Borrows
Borrows is a binary relationship type

Relationship Types of Various
Degrees
Degree Kind of Relationship Type
2 Binary relationship type
3 Ternary relationship type
4 Quaternary relationship type

An Example of Ternary
Relationship Type
Rental Agency
TenantLandlord Arranges

An Example of Quaternary
Relationship Type
Real Estate Agency
BuilderBank Arranges
Buyer

Strong and Week Entity Types
Strong Entity Type: An entity type that does not
depend on other entity types for its existence.
Example: Person
Week Entity Type: An entity type that depends
on other entity types for its existence.
Example: Hobby

Structural Constraints
Structural constraints are constraints on Entity
Types on how many entities of an Entity Type
participate in a relationship.
Identifying and representing constraints on
Entity Types is an important step in E-R modeling
of an enterprise data.
Multiplicity constraint is the main type of
constraint on relationships.

Multiplicity Constraint
Multiplicity is the number of possible entities of
an entity type that may relate to a single entity
of an associated entity type participating in a
relationship.

Binary Relationships
Binary relationships are relationships between
two entity types.
Types of binary relationships:
• One-to-one (1:1) relationships
• One-to-many (1:*) relationships
• Many-to-many (*:*) relationships

One-to-one (1:1) Relationship
A relationship is a 1:1 relationship, if an entity in
one entity type of the relationship is associated
with at most one entity in another entity type
of the relationship and vice versa.
Employee DepartmentManages

1:1 Relationship
DepartmentEmployee
Manages
0..11..1
Multiplicity 0..1 indicates
that an employee can
manage none or one
department
Multiplicity 1..1 indicates a
department is managed by
exactly one employee

One-to-many (1:*) Relationship
A relationship between two entity types is a one-to-
many (1:*) relationship, if an entity in one entity type
is associated with any number (zero or more) of
entities in another entity type whereas an entity of the
latter entity type can be associated with at most one
entity of the former entity type.
Staff SubjectTeaches

1:* Relationship
SubjectStaff
Teaches
0..*0..1
Multiplicity 0..* indicates
that a staff member teaches
none or one or more subjects
Multiplicity 0..1
indicates a subject is
taught by none or at
most one staff member

Many-to-one (*:1) Relationship
This is a mirror relationship of the one-to-many
relationship.
The one-to-many relationship of the earlier slide can be
called as many-to-one relationship if it is represented
as Taught-by relationship and swap its entity types.

Many-to-many (*:*) Relationship
A relationship between two entity types is a many-to-
many (*:*) relationship, if an entity in one entity type is
associated with any number (zero or more) of entities
in another entity type and an entity of the latter entity
type is also associated with any number entities of the
former entity type.
Author BookWrote

*:* Relationship
BookAuthor
Wrote
1..*1..*
that an author wrote one or
more books
a book was written by
one or more authors

Cardinality and Participation
Constraints
Cardinality is the maximum number of relationship
occurrences for an entity participating in a given
relationship type.
Participation determines whether all or only some
entities participate in a relationship.

Cardinality and Participation
Constraints
Entity -2Entity -1
Relationship-1
p..qm..n
Cardinality
Participation

Database Systems - Entity Relationship Modeling (Chapter 4/2)

Recommended

More Related Content

What's hot (20)

Similar to Database Systems - Entity Relationship Modeling (Chapter 4/2) (20)

More from Vidyasagar Mundroy (8)

Recently uploaded (20)

Database Systems - Entity Relationship Modeling (Chapter 4/2)