Chapter-2
Entity– Relationship Data Model
Dr. Jyoti Wadmare
Contents
• The Entity-Relationship (ER) Model: Entity types:
Weak and strong entity sets,
• Entity sets
• Types of Attributes
• Keys
• Relationship constraints: Cardinality and
Participation
• Extended Entity-Relationship (EER) Model:
Generalization, Specialization and Aggregation
Entity Relationship model
Why ER model is useful?
Symbols used in ER diagram
Symbols used in ER diagram
Symbols used in ER diagram
ER Modeling Constructs
Entity
Entity Set
Attributes
Example
Relationship
Eg.
Relationship: Descriptive Attribute
Degree of Relationship
Degree of Relationship
Degree of Relationship
Eg. DBMS subject taught by a teacher at different department
Like computer dept, AIDS dept etc.
Mapping cardinalities
Types of attributes
1. Simple Attribute
2. Composite Attribute
3. Single valued attributes
4. Multivalued Attribute
5. Stored Attributes
6. Derived Attribute
7. Key Attribute (Primary key)
Example of Customer entity With Composite, Multivalued,
and Derived Attributes
Mapping cardinalities
One to one Relationship
One to many Relationship
Many to one Relationship
Many to many Relationship
How to choose relationship
Eg. One customer has applied for personal loan, educational loan, home loan etc
How to choose relationship
Eg. Joint loan – two or three customers jointly taken one loan ( start-up, business etc)
Participation constraints
Each loan is associated with
Not necessary each customer has taken loan
the customer
Example Participation Constraints
(min ,max cardinality)
(0,2)
Project (3,15) assign Employee
Total Participation Partial Participation
Min-3-> 3 Employee Works on one Min-0-> Not necessary each
project Employee Works on project
Max15-> 15 employee can work on Max2-> one employee can work on
one project maximum 2 projects
Entity Types: Strong Entity
Weak Entity
Example: Strong and weak entity
Partial key or discriminator
1 M
Weak Relation/
Identifying Relation
Another Example
Difference between Strong entity and
weak entity
Exercise of ER Diagram
Exercise of ER Diagram
KEYS
Types of Keys
Super
key
It is kind of super set of keys.
Super key example
Etc.
Candidate key
Candidate key example
Primary key
Alternate Key
Foreign Key
Foreign key example
Foreign key
Composite key
Extended ER Model Features
• It is important for newer applications of database technology, such as databases for
engineering design and manufacturing (CAD/CAM),
• telecommunications,
• complex software systems, and
• Geographic Information Systems (GIS), among many other applications
These types of databases have more complex requirements than do the more traditional
applications.
This led to the development of additional semantic data modeling concepts
Extended ER Model Features
Many of these concepts were also developed independently in related areas of computer
science, such as
knowledge representation area of artificial intelligence and
object modeling area in software engineering
ER model can be enhanced to include these concepts, leading to the Enhanced ER (EER)
model.
Generalization
High level entity
Low level entities
Specialization
Example
Example: Attribute inheritance
How schema and Tables can be formed
All leaf Nodes
Aggregation
Example ( Relationship of relation)
Example: Aggregation
Constraints on Generalization and
Specialization
• Membership constraints
– Attribute defined
– User defined
• Disjointness constraints
– Disjoint
– Overlapping
• Completeness constraints
– Total G/S
– Partial G/S
Attribute defined
User defined
Disjoint
Overlapping
Example
Example