Unit-3

Entity-Relationship model

Prepared By:
Dr. Aayushi Chaudhari,Ms. Rikita Chokshi
Assistant Professor, CE, CSPIT,
CHARUSAT

9 January 2025| U & P U. Patel Department of Computer Engineering 1

Agenda
● Basic concepts
● Design process
● Constraint
● Keys
● Design issues
● E-R diagrams
● Weak Entity Sets
● Extended E-R
● features- Generalization
● Specialization
● Aggregation
● Reduction to E-R database schema

9 January 2025| U & P U. Patel Department of Computer Engineering 2

Introduction
We typically follow the below steps for designing a database for an application.

● Gather the requirements (functional and data) by asking questions to the database users.
● Do a logical or conceptual design of the database. This is where ER model plays a role. It is the
most used graphical representation of the conceptual design of a database.
● Physical Database Design (Like indexing) and external design (like views)

The Entity Relationship Model is a model for identifying entities (like student, car or company) to be
represented in the database and representation of how those entities are related. The ER data model
specifies enterprise schema that represents the overall logical structure of a database graphically.

9 January 2025| U & P U. Patel Department of Computer Engineering 3

Need of ER Diagrams
• ER diagrams represent the E-R model in a database, making them easy to convert into relations (tables).
• ER diagrams provide the purpose of real-world modeling of objects which makes them intently useful.
• ER diagrams require no technical knowledge of the underlying DBMS used.
• Provides solution for visualizing the data logically.

Components of ER Diagrams

9 January 2025| U & P U. Patel Department of Computer Engineering 4

Entity
● An Entity may be an object with a physical existence – a particular
person, car, house, or employee – or it may be an object with a
conceptual existence – a company, a job, or a university course. It
just defines the types of data stored.
● What is Entity Set?
● An Entity is an object of Entity Type and a set of all entities is called an
entity set.
● e.g.: E1 is an entity having Entity Type Student and the set of all students
is called Entity Set. In ER diagram, Entity Type is represented as:

● We can represent the entity set in ER Diagram but can’t represent entity in
ER Diagram because entity is row and column in the relation and ER
Diagram is graphical representation of data.

9 January 2025| U & P U. Patel Department of Computer Engineering 5

Strong vs weak entities
Strong entity Weak entity

Strong entity always has a primary key. It will not have a primary key but it has partial
discriminator key.

It is not dependent on any other entity. Weak entity is dependent on the strong entity.

Represented by a single rectangle. Represented by double rectangle.

Relationship between two strong entities Relationship between a strong entity and the
is represented by a single diamond. weak entity is represented by double Diamond.

A strong entity may or may not have It has always total participation.
total participation.

9 January 2025| U & P U. Patel Department of Computer Engineering 6

Attributes
Attributes define what type of data is stored in database table.
E.g. Student table will have roll_no, name, attendance, age, etc , which will act as an attribute
to the table.
● There are 6 types of attributes
○ Simple attributes
○ Composite attributes
○ Single valued attributes
○ Multi valued attributes
○ Derived attributes
○ Key attributes

9 January 2025| U & P U. Patel Department of Computer Engineering 7

Simple and Composite Attributes
● Simple attributes are those attributes which
cannot be divided further.
● e.g. Gender, Contact_no, etc.
● Composite attributes are those attributes
which are composed of many other simple
attributes.
● e.g. Name, Address, etc. where name may
contain First name + Middle name + Last
name, similarly for address can be
combination of house number, area, city,
state, pincode.

9 January 2025| U & P U. Patel Department of Computer Engineering 8

Single valued and multivalued attributes
Single valued attributes are those attributes which can take only one value for a given entity
from an entity set.
e.g. Rollno, Name, age, etc.
Multi valued attributes are those attributes which can take more than one value for a given
entity from an entity set.
e.g. “Mob_no” and “Email_id” are multi valued attributes as they can take more than one
values for a given entity.

Multivalued attributes are represented with two ellipse.

9 January 2025| U & P U. Patel Department of Computer Engineering 9

Derived and Key attributes
Derived attributes are those attributes which can be derived from other attribute(s).

Derived are represented by dotted ellipse.

Example :
The attribute “Age” is a derived attribute as it can be derived from the attribute “DOB”.
Key attributes are those attributes which can identify an entity uniquely in an entity set.
E.g. “Roll_no” is a key attribute as it can identify any student uniquely.

Key attribute are represented with underline.

9 January 2025| U & P U. Patel Department of Computer Engineering 10

Representation of attributes
The Complete Entity Type Student with its Attributes can be represented as:

9 January 2025| U & P U. Patel Department of Computer Engineering 11

Relationship types and sets
A Relationship Type represents the association between entity types. For example, ‘Enrolled in’ is a relationship type that exists
between entity type Student and Course. In ER diagram, the relationship type is represented by a diamond and connecting the
entities with lines.

A set of relationships of the same type is known as a relationship set. The following relationship set depicts S1 as enrolled in C2, S2
as enrolled in C1, and S3 as registered in C3.

9 January 2025| U & P U. Patel Department of Computer Engineering 12

Degree of relationship set
The number of different entity sets participating in a relationship set is called the degree of a relationship set.
Unary Relationship: When there is only ONE entity set participating in a relation, the relationship is called a unary relationship. For example,
one person is married to only one person.

Binary Relationship: When there are TWO entities set participating in a relationship, the relationship is called a binary relationship. For
example, a Student is enrolled in a Course.

Ternary Relationship: When there are three entity sets participating in a relationship, the relationship is called a ternary relationship.
N-ary Relationship: When there are n entities set participating in a relationship, the relationship is called an n-ary relationship.

9 January 2025| U & P U. Patel Department of Computer Engineering 13

Cardinality
• The number of times an entity of an entity set
participates in a relationship set is known as
cardinality . Cardinality can be of different types:
• 1. One-to-One: When each entity in each entity set
can take part only once in the relationship, the
cardinality is one-to-one. Let us assume that a male
can marry one female and a female can marry one
male. So the relationship will be one-to-one.
• the total number of tables that can be used in this is
2.

9 January 2025| U & P U. Patel Department of Computer Engineering 14

Cont..
• One-to-Many: In one-to-many mapping as
well where each entity can be related to more
than one entity and the total number of tables
that can be used in this is 2. Let us assume that
one surgeon department can accommodate
many doctors. So the Cardinality will be 1 to M.
It means one department has many Doctors.
• total number of tables that can used is 3.

9 January 2025| U & P U. Patel Department of Computer Engineering 15

Cont..
Many-to-One: When entities in one entity set can take part only once in the
relationship set and entities in other entity sets can take part more than once in the
relationship set, cardinality is many to one. Let us assume that a student can take only
one course but one course can be taken by many students. So the cardinality will be n
to 1. It means that for one course there can be n students but for one student, there
will be only one course.
The total number of tables that can be used in this is 3.
set representation:
each student is taking only 1 course but 1 course has been taken by many students.

9 January 2025| U & P U. Patel Department of Computer Engineering 16

Cont..
Many-to-Many: When entities in all entity sets can take part more than once in the
relationship cardinality is many to many. Let us assume that a student can take more than one
course and one course can be taken by many students. So the relationship will be many to
many.
the total number of tables that can be used in this is 3.

In this example, student S1 is enrolled in C1 and C3 and Course C3 is enrolled by S1, S3, and
S4. So it is many-to-many relationships.

9 January 2025| U & P U. Patel Department of Computer Engineering 17

Symbols Used in ER Model
ER Model is used to model the logical view of the system from a
data perspective which consists of these symbols:

● Rectangles: Rectangles represent Entities in the ER

Model.
● Ellipses: Ellipses represent Attributes in the ER Model.
● Diamond: Diamonds represent Relationships among
Entities.
● Lines: Lines represent attributes to entities and entity
sets with other relationship types.
● Double Ellipse: Double Ellipses represent Multi-Valued
Attributes.
● Double Rectangle: Double Rectangle represents a Weak
Entity.

9 January 2025| U & P U. Patel Department of Computer Engineering 18

Design Process for drawing ER diagram
Step 1: Identifying Entities

Determine the main objects you want to represent in the database. Eg, "students", "courses", or "products".

Step 2: Defining Attributes

Identify the properties(attributes) of properties of each entity. These attributes provide more details about an entity.

Step 3: Specifing Relationships

Create relationships between entities to specify how entities interact with each other. Relationships are verbs like "teaches",
"studies", or "sells".

Step 4: Drawing Entities

Draw entities as rectangle and write the name.

9 January 2025| U & P U. Patel Department of Computer Engineering 19

Design Process for drawing ER diagram
Step 5: Adding Attributes

To add attributes of a entity write attributes inside the rectangle or connect them with lines.

Step 6: Connecting Entities

Draw lines between the related entities to represent their connection.

Step 7: Specifying Cardinality

Indicate the minimum and maximum number of relationship instances associated with an entity using notations like crow's
foot.

Step 8: Organizing ER Diagram

Organize all entities and relationships in a clean way for better readibility and understanding.
9 January 2025| U & P U. Patel Department of Computer Engineering 20
Entities and Attributes
Relationships and Cardinalities
Final ER diagram
Benefits of ER diagrams
• ER diagrams provides a clear and visual representation of the database structure. This make
it easier for developers and designers to understand the relationships between the entities.
• It is an effective communication tool for database designers and the stakeholders which
helps to understand the data model.
• ER diagrams serve as the foundation for designing the database schema. They guide the
creation of tables, relationships and constraints in the database management system.
• Creating an ER diagram helps in understanding and analyzing the data requirements of the
system.
• Database managers can more quickly and efficiently undertake maintenance activities,
including adding new entities or changing existing relationships, when they have a well-
designed Entity Relationship diagram that makes the database structure easy to understand.
9 January 2025| U & P U. Patel Department of Computer Engineering 24
Constraints in DBMS
• Constraints in DBMS (Database Management Systems) are rules or conditions that are
applied to the data within a database to ensure data integrity, consistency, and adherence to
business rules. They define limitations and requirements that the data must meet, thereby
preventing the entry of invalid or inconsistent data.
• The purpose of constraints is to enforce data quality and prevent data inconsistencies,
thereby enhancing the overall data integrity and reliability of the database. Constraints
define boundaries for data values, relationships between entities, uniqueness requirements,
and more. By enforcing constraints, DBMS can ensure that the data conforms to predefined
standards and business rules, making the database more robust and reliable.

9 January 2025| U & P U. Patel Department of Computer Engineering 25

Types of Constraints
In relational databases, there are following constraints in DBMS called relational constraints.
 Domain Constraints
 Key Constraints
 Entity Integrity Constraints
 Referential Integrity Constraints
 Tuple Uniqueness Constraints

9 January 2025| U & P U. Patel Department of Computer Engineering 26

Domain Constraints
The domain means a range of values.
This is a constraint applied to attributes, not tuples. This means that it defines what values are
allowed to be kept inside a particular column (attribute) for a table.
The domain constraint specifies that the value of an attribute must be an atomic value in its
own domain.
Types:
NOT NULL - Ensures that a column cannot have a NULL value
CHECK - Ensures that the values in a column satisfies a specific condition

9 January 2025| U & P U. Patel Department of Computer Engineering 27

Example for domain constraint
Create table Students
(Student_id NUMBER not null,
Student_name varchar(30),
marks NUMBER check(marks > 35));
Correct View:

Student ID Student Name Marks (in %)

1 Geet 90
2 Aryan 92
3 Yash 87
4 Lav 90
5 Ashu 79
9 January 2025| U & P U. Patel Department of Computer Engineering 28
Tuple Uniqueness Constraint
Tuple means row or record.
As the name suggests, the tuple uniqueness constraint in DBMS specifies that each tuple in the
table must be unique.
A tuple is said to be duplicate if all the corresponding attribute values of that tuple are present
in some other tuple simultaneously in the table.

Type:
UNIQUE - Ensures that all values in a column are different
PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each
row in a table
9 January 2025| U & P U. Patel Department of Computer Engineering 29
Example for Tuple Uniqueness/key constraint
Create table Students
(Student_id NUMBER NOT NULL UNIQUE,
Student_name varchar(30),
marks NUMBER check(marks > 35)
PRIMARY KEY (Student_id));
Correct view:
Student ID Student Name Marks (in %)
1 Geet 90
2 Aryan 92
3 Yash 87
4 Lav 90
5 Ashu 79

9 January 2025| U & P U. Patel Department of Computer Engineering 30

Key constraint
1. As the name suggests, this is a constraint applied on an attribute that we consider to be a
primary key. So, the conditions for a primary key in a table is in fact this constraint.
2. So, we know that a primary key cannot be null.
3. Also, a primary key must be unique.

Example for primary key constraint is in slide no. 30.

9 January 2025| U & P U. Patel Department of Computer Engineering 31

Entity Integrity Constraints
This is the same as the Key constraint. In fact, it is just a subset of the Key constraint.
The key constraint states that the Primary Key attributes should be unique and must not
contain null values.
However, Entity Integrity Constraint states that any attribute of a Primary key must not be
null.

9 January 2025| U & P U. Patel Department of Computer Engineering 32

Referential Integrity Constraint
Referential integrity is a database concept that ensures the consistency and accuracy of data
between related tables. It is maintained through the use of primary and foreign key.
Referential integrity constraint is applied when a foreign key references the primary key of our
table.
This constraint can be summarized in one line i.e. the referencing attribute must be the subset
of referred attribute.
This means that a record or a tuple cannot be inserted in the referencing relation if it is not
present in the referenced relation.
Also, any record that is present in the referencing relation cannot be updated or deleted from
the referenced relation.

9 January 2025| U & P U. Patel Department of Computer Engineering 33

Example
Student Table:
Student ID Student Name Marks (in %) Dept_id
Create table Students
1 Geet 90 D01 (Student_id NUMBER NOT NULL UNIQUE,
2 Aryan 92 D02 Student_name varchar(30),
marks NUMBER check(marks > 35)
3 Yash 87 D01 PRIMARY KEY (Student_id)
4 Lav 90 D03 FOREIGN KEY (Dept_id) References Department(Dept_No));
5 Ashu 79 D02

Department Table:
Dept_No Dept_Name
D01 CSE
D02 IT
D03 ECE
9 January 2025| U & P U. Patel Department of Computer Engineering 34
Keys
• Keys are one of the basic requirements of a relational database model. It is widely used to
identify the tuples(rows) uniquely in the table.
• We require keys in a DBMS to ensure that data is organized, accurate, and easily
accessible. Keys help to uniquely identify records in a table, which prevents duplication
and ensures data integrity.
• Keys also establish relationships between different tables, allowing for efficient querying
and management of data. Without keys, it would be difficult to retrieve or update specific
records, and the database could become inconsistent or unreliable.

9 January 2025| U & P U. Patel Department of Computer Engineering 35

Types of Keys
Following are the types of keys in DBMS:
primary key.
candidate key.
super key.
foreign key.
alternate key.
composite key.

9 January 2025| U & P U. Patel Department of Computer Engineering 36

The set of one or more attributes (columns) that can uniquely identify a tuple (record) is
known as Super Key.
For Example, STUD_NO, (STUD_NO, STUD_NAME), etc.
A super key is a group of single or multiple keys that uniquely identifies rows in a table. It
supports NULL values in rows.
A super key can contain extra attributes that aren’t necessary for uniqueness. For example, if
the “STUD_NO” column can uniquely identify a student, adding “SNAME” to it will still
form a valid super key, though it’s unnecessary.

9 January 2025| U & P U. Patel Department of Computer Engineering 37

Example
Table STUDENT

STUD_NO SNAME ADDRESS PHONE

1 Shyam Delhi 123456789
2 Rakesh Kolkata 223365796
3 Suraj Delhi 175468965

STUD_NO + PHONE can act as a super key.

9 January 2025| U & P U. Patel Department of Computer Engineering 38

Candidate Key
• The minimal set of attributes that can uniquely identify a tuple is known as a candidate key.
For Example, STUD_NO in STUDENT relation.
• A candidate key is a minimal super key, meaning it can uniquely identify a record but
contains no extra attributes.
• It is a super key with no repeated data is called a candidate key.
• The minimal set of attributes that can uniquely identify a record.
• A candidate key must contain unique values, ensuring that no two rows have the same
value in the candidate key’s columns.
• Every table must have at least a single candidate key.
• A table can have multiple candidate keys but only one primary key.
• STUD_NO is the candidate key for relation STUDENT.
9 January 2025| U & P U. Patel Department of Computer Engineering 39
Primary Key
There can be more than one candidate key in relation out of which one can be chosen as the primary
key. For Example, STUD_NO, as well as STUD_PHONE, are candidate keys for relation
STUDENT but STUD_NO can be chosen as the primary key (only one out of many candidate
keys).
A primary key is a unique key, meaning it can uniquely identify each record (tuple) in a table.
It must have unique values and cannot contain any duplicate values.
A primary key cannot be NULL, as it needs to provide a valid, unique identifier for every record.
A primary key does not have to consist of a single column. In some cases, a composite primary
key (made of multiple columns) can be used to uniquely identify records in a table.
Databases typically store rows ordered in memory according to primary key for fast access of records
using primary key.
STUD_NO is a primary key.

9 January 2025| U & P U. Patel Department of Computer Engineering 40

Alternate Key
An alternate key is any candidate key in a table that is not chosen as the primary key. In other words, all the keys
that are not selected as the primary key are considered alternate keys.
An alternate key is also referred to as a secondary key because it can uniquely identify records in a table, just like
the primary key.
An alternate key can consist of one or more columns (fields) that can uniquely identify a record, but it is not the
primary key.
STUD_NO, as well as PHONE both,
are candidate keys for relation STUDENT but
PHONE will be an alternate key

9 January 2025| U & P U. Patel Department of Computer Engineering 41

Foreign Key
A foreign key is an attribute in one table that refers to the primary key in another table. The
table that contains the foreign key is called the referencing table, and the table that is
referenced is called the referenced table.
A foreign key in one table points to the primary key in another table, establishing a
relationship between them.
It helps connect two or more tables, enabling you to create relationships between them. This
is essential for maintaining data integrity and preventing data redundancy.
They act as a cross-reference between the tables.
Refer to example of slide No. 34

9 January 2025| U & P U. Patel Department of Computer Engineering 42

Composite Key
• Sometimes, a table might not have a single
column/attribute that uniquely identifies all
the records of a table. To uniquely identify
rows of a table, a combination of two or more
columns/attributes can be used. It still can
give duplicate values in rare cases. So, we
need to find the optimal set of attributes that
can uniquely identify rows in a table.
• It acts as a primary key if there is no primary
key in a table
• Two or more attributes are used together to
make a composite key .
• Different combinations of attributes may give
different accuracy in terms of identifying the
rows uniquely.

9 January 2025| U & P U. Patel Department of Computer Engineering 43

 Key Type Definition Example

Uniquely identifies a Employee ID, Student Roll

Primary Key
record, no duplicates. Number

A combination of attributes
Super Key that can uniquely identify Employee ID + Name
records.

A minimal super key that Employee ID, Email

Candidate Key
can act as a primary key. Address

A candidate key is not Email Address (if

Alternate Key
chosen as the primary key. Employee ID is primary)

Links tables by referencing

Department ID in
Foreign Key a primary key from another
Employee Table
table.

Multiple columns are used

Composite Key together to identify records Course ID + Student ID
uniquely.

Ensures uniqueness in a
Email Address, Phone
Unique Key column but allows one null
Number
value.

9 January 2025| U & P U. Patel Department of Computer Engineering 44

Example for weak entity sets

9 January 2025| U & P U. Patel Department of Computer Engineering 45

Extended ER Features
Using the ER model for bigger data creates a lot of complexity while designing a database
model, So in order to minimize the complexity Generalization, Specialization, and
Aggregation were introduced in the ER model.
These were used for data abstraction. In which an abstraction mechanism is used to hide
details of a set of objects.
Thus extended ER features includes the following:
 Generalization
 Specialization
 Aggregation

9 January 2025| U & P U. Patel Department of Computer Engineering 46

Generalization
• Generalization is the process of extracting
common properties from a set of entities
and creating a generalized entity from it.
• It is a bottom-up approach in which two
or more entities can be generalized to a
higher-level entity if they have some
attributes in common.
• For Example, STUDENT and FACULTY
can be generalized to a higher-level entity
called PERSON.
• In this case, common attributes like
P_NAME, and P_ADD become part of a
higher entity (PERSON).

9 January 2025| U & P U. Patel Department of Computer Engineering 47

Specialization
In specialization, an entity is divided into sub-
entities based on its characteristics. It is a
top-down approach where the higher-level
entity is specialized into two or more lower-
level entities. For Example, an EMPLOYEE
entity in an Employee management system
can be specialized into DEVELOPER,
TESTER, etc.
In this case, common attributes like E_NAME,
E_SAL, etc. become part of a higher entity
(EMPLOYEE), and specialized attributes
like TES_TYPE become part of a specialized
entity (TESTER).

9 January 2025| U & P U. Patel Department of Computer Engineering 48

Aggregation
An ER diagram is not capable of representing the relationship between an entity and a
relationship which may be required in some scenarios.
In those cases, a relationship with its corresponding entities is aggregated into a higher-level
entity. Aggregation is an abstraction through which we can represent relationships as
higher-level entity sets.
For Example, an Employee working on a project may require some machinery. So, REQUIRE
relationship is needed between the relationship WORKS_FOR and entity MACHINERY.
Using aggregation, WORKS_FOR relationship with its entities EMPLOYEE and
PROJECT is aggregated into a single entity and relationship REQUIRE is created between
the aggregated entity and MACHINERY.

9 January 2025| U & P U. Patel Department of Computer Engineering 49

Example

9 January 2025| U & P U. Patel Department of Computer Engineering 50

Reduction to E-R database schema
1. Normalize Attributes
Avoid storing redundant data by ensuring the attributes in your entities are normalized.
Split large entities with many attributes into smaller entities if they represent different concepts.
2. Combine Similar Entities
Merge entities that share a one-to-one relationship or have similar attributes to reduce redundancy.
3. Use Weak Entities Appropriately
Replace unnecessary weak entities with attributes in the parent entity if they don’t add significant complexity or relationships.
4. Remove Unused Relationships
Eliminate relationships that don’t contribute to the functionality or understanding of the diagram.
Focus on meaningful relationships that are critical to your database goals.
5. Apply Generalization and Specialization
Replace multiple entities with a generalized parent entity when they share common attributes.
Use specialization only when distinct sub-entities have unique attributes or relationships.

9 January 2025| U & P U. Patel Department of Computer Engineering 51

Cont..
6. Use Multi-Valued Attributes
Represent simple relationships with multi-valued attributes when appropriate instead of separate
entities.
7. Simplify Many-to-Many Relationships
Replace many-to-many relationships with associative (junction) entities and ensure they are concise.
8. Focus on Key Attributes
Only include attributes that are necessary for the database operations.
Exclude derived attributes that can be calculated from existing data.
9. Group Similar Relationships
Combine multiple relationships into a single relationship if they share the same context and entities.

9 January 2025| U & P U. Patel Department of Computer Engineering 52

ER diagram for Hospital

9 January 2025| U & P U. Patel Department of Computer Engineering 53

ER diagram for University

9 January 2025| U & P U. Patel Department of Computer Engineering 54

Practice Example for ER diagram
Car Dealership
Create an ERD for a car dealership. The dealership sells both new and used cars, and it operates a service facility.
Base your design on the following business rules:
A salesperson may sell many cars, but each car is sold by only one salesperson.
A customer may buy many cars, but each car is bought by only one customer.
A salesperson writes a single invoice for each car he or she sells.
A customer gets an invoice for each car he or she buys.
A customer may come in just to have his or her car serviced; that is, a customer need not buy a car to be classified as a customer.
When a customer takes one or more cars in for repair or service, one service ticket is written for each car.
The car dealership maintains a service history for each of the cars serviced. The service records are referenced by the car’s serial
number.
A car brought in for service can be worked on by many mechanics, and each mechanic may work on many cars.
A car that is serviced may or may not need parts (e.g., adjusting a carburetor or cleaning a fuel injector nozzle does not require
providing new parts).

9 January 2025| U & P U. Patel Department of Computer Engineering 55

 Thank you

9 January 2025| U & P U. Patel Department of Computer Engineering 56