Entity Relationship
Diagram
Slide 3-1
Using high level conceptual
data models for database design
Slide 3-2
A simplified overview of the database
design process
• Database design process is divided into 4 Steps:
1.requirements collection and analysis
2.Conceptual Design
3.Logical Design
4.Physical Design
Slide 3-3
A simplified
diagram to
illustrate the
main phases of
database
design.
Slide 3-4
1. requirements collection and analysis
•During this step, the database designers interview prospective
database users to understand and document their data requirements.
•The result of this step is a concisely written set of users’
requirements.
• These requirements should be specified in as detailed and complete
a form as possible.
•In parallel with specifying the data requirements, it is useful to
specify the known functional requirements of the application.
Slide 3-5
2. Conceptual design
•In parallel with specifying the data requirements, it is useful to specify
step is called conceptual design.
•The conceptual schema is a concise description of the data
requirements of the users and includes detailed descriptions of the
entity types, relationships, and constraints.
•These are expressed using the concepts provided by the high-level
data model. Ex. E-R model
•Because these concepts do not include implementation details, they
are usually easier to understand and can be used to communicate
with nontechnical users.
Slide 3-6
• The high-level conceptual schema can also be used as a
reference to ensure that all users’ data requirements are
met and that the requirements do not conflict.
• This approach enables database designers to concentrate on
specifying the properties of the data, without being
concerned with storage and implementation details, which
makes it is easier to create a good conceptual database design.
• During or after the conceptual schema design, the basic data
model operations can be used to specify the high-level user
queries and operations identified during functional analysis.
• This also serves to confirm that the conceptual schema meets
all the identified functional requirements.
• Modifications to the conceptual schema can be introduced if
some functional requirements cannot be specified using the
initial schema.
• Example of conceptual schema is Entity Relationship Diagram
Slide 3-7
3. Logical Design
The next step in database design is the actual implementation of
the database, using a commercial DBMS.
• Most current commercial DBMSs use an implementation data
model—such as the relational (SQL) model—so the conceptual
schema is transformed from the high-level data model into the
implementation data model.
• This step is called logical design or data model mapping;
• its result is a database schema in the implementation data
model of the DBMS.
Slide 3-8
4. Physical design
•The last step is the physical design phase,
• during which the internal storage structures, file
organizations, indexes, access paths, and physical design
parameters for the database files are specified.
•In parallel with these activities, application programs are
designed and implemented as database transactions
corresponding to the high-level transaction specifications
Slide 3-9
Entities and Attributes
• The basic concept that the ER model represents is an entity,
which is a thing or object in the real world with an independent
existence.
• An entity may be an object with a physical existence (for
example, a particular per son, car, house, or employee) or it
may be an object with a conceptual existence (for instance, a
company, a job, or a university course).
• Each entity has attributes—the particular properties that
describe it. For example, an EMPLOYEE entity may be
described by the employee’s name, age, address, salary, and
job.
Slide 3-10
Example
Slide 3-11
Types of Attributes
• Composite attributes can be divided into smaller subparts, which
represent more basic attributes with indepen dent meanings. For
example, the Address attribute of the EMPLOYEE entity shown in
can be subdivided into Street_address, City, State, and Zip.
• Attributes that are not divisible are called simple or atomic
attributes.
• Most attributes have a single value for a particular entity; such
attributes are called single-valued. For example, Age is a single-
valued attribute of a person.
• In some cases an attribute can have a set of values for the same
entity is called multivalued. For instance, a Colors attribute for a
car, or a College_degrees attribute for a person.
Slide 3-12
• In some cases, two (or more) attribute values are related—for
example, the Age and Birth_date attributes of a person. For a
particular person entity, the value of Age can be determined from
the current (today’s) date and the value of that person’s Birth_date.
The Age attribute is hence called a derived attribute and is said to
be derivable from the Birth_date attribute, which is called a stored
attribute.
• In some cases, a particular entity may not have an applicable value
for an attribute. For example, the Apartment_number attribute of an
address applies only to addresses that are in apartment buildings
and not to other types of resi dences, such as single-family homes.
Similarly, a College_degrees attribute applies only to people with
college degrees. For such situations, a special value called NULL is
created.
Slide 3-13
• We can represent arbitrary nesting by grouping can be specified as
components of a composite attribute between parentheses ( ) and
separating the components with commas, and by displaying
multivalued attributes between braces { }. Such attributes are
called complex attributes. For example, if a person can have
more than one residence and each residence can have a single
address and multiple phones, an attribute Address_phone for a
person
Slide 3-14
Entity Type
An entity type defines a collection (or set) of entities
that have the same attributes. A database usually
contains groups of entities that are similar. For
example, a company employing hundreds of employees
may want to store similar information concerning each
of the employees. These employee entities share the
same attributes, but each entity has its own value(s) for
each attribute. Each entity type in the database is
described by its name and attributes.
Slide 3-15
Slide 3-16
Entity Set
The collection of all entities of a particular entity type in
the data-base at any point in time is called an entity set.
It is called extension of entity type.
An entity type describes the schema or intension for
a set of entities that share the same structure. The
collection of entities of a particular entity type is grouped
into an entity set, which is also called the extension of
the entity type.
Slide 3-17
Key
• An important constraint on the entities of an entity
type is the key or uniqueness constraint on
attributes.
• An entity type usually has one or more attributes
whose values are distinct for each individual entity
in the entity set.
• Such an attribute is called a key attribute, and its
values can be used to identify each entity uniquely.
Slide 3-18
Value Set
Each simple attribute of an entity type is associated with
a value set (or domain of values), which specifies the
set of values that may be assigned to that attribute for
each individual entity.
Slide 3-19
Initial Conceptual Design of the COMPANY Database
An entity type is represented in ER diagrams as a
rectangular box enclosing the entity type name. Attribute
names are enclosed in ovals and are attached to their entity
type by straight lines. Composite attributes are attached to
their component attributes by straight lines. Multivalued
attributes are displayed in double ovals.
Following figure shows the Conceptual design of Company
DB
Slide 3-20
Slide 3-21
Relationship Types, Relationship Sets,
Roles, and Structural Constraints
Slide 3-22
Relationship Types, Sets, and Instances
1. Relationship Type
A relationship type in a DBMS defines the association or link
between two or more entity types (tables) at a conceptual level. It
indicates how entities interact but doesn't show specific data yet.
• Example: Consider the relationship type "Teaches" between two
entity types:
•Professor and Course.
•This relationship indicates that professors teach courses.
•Entity 1: Professor(ProfessorID, Name, Dept)
•Entity 2: Course(CourseID, CourseName, Credits)
•Relationship Type: Teaches
Slide 3-23
Professor Teaches Courses
Slide 3-24
2. Relationship Set
A relationship set is the actual collection of all the relationships of a
given type that currently exist in the database. These relationships
are formed by linking specific tuples (rows) from the related entity
sets (tables).
3. Relationship Instance
A relationship instance refers to a specific association between
individual entities at a particular moment. It represents one row in
the relationship set (table).
Slide 3-25
Professor Teaches Courses
Professor Teaches Courses
P1 r1 c1
P2 r2 c2
P3 r3 c3
p4 r4 c4
Entity Types : Professor, Courses
Relationship type : R (Teaches)
Relationship set : R(Teaches) = { r1,r2,r3,r4}
Relationship instances : { r1,r2,r3,r4}
Slide 3-26
4. Degree of a Relationship Type: The degree of a relationship
type is the number of participating entity types. Hence, the
“Teaches” relationship is of degree two.
Professor Teaches Courses
A relationship type of degree two is called binary, and one of
degree three is called ternary.
Slide 3-27
Supplier Supply Customer
Vendor
Slide 3-28
5. Role Names in Relationships
Role names are used when entities in a relationship participate in
different capacities. This is especially useful when an entity type plays
more than one role in a relationship. Role names help distinguish these
roles.
Example :
HOD-Faculty relationship
HOD & Faculty both are instances of entity type “Staff”
Relationship type reporting have two roles : HOD & Faculty
Example in Database :
Staff_id Staff_name Reporting_id Roles
1 Dr. Jinal Tailor Null HOD
2 Dr. Rajnish Rakholiya 1 Faculty
3 Dr. Hemina Bhavsar 1 Faculty
Slide 3-29
6. Recursive Relationships
A recursive relationship occurs when an entity type is related to
itself. These relationships are also known as self-referencing
relationships.
For example, in an organizational hierarchy:
•Staff (Faculty) can report to other Staff (HOD), forming a recursive
relationship within the “Staff" entity.
•In relational databases, recursive relationships are typically
implemented by having a foreign key in a table that references the
primary key of the same table.
Slide 3-30
Staff Report Staff
S2 r1
S3 r2
S1
S4 r3
S5 r4
Slide 3-31
Constraints on Binary Relationship Types
In a binary relationship (a relationship involving two entities),
constraints define the rules governing how the entities are related to
each other. These constraints ensure data integrity and provide clear
guidance on how relationships should be managed in a database. The
main types of constraints on binary relationship types are:
1. Cardinality Constraints
Cardinality constraints define the number of entities that can
participate in a relationship. The common types of cardinality
constraints are:
One-to-One (1:1): Each entity in the relationship is associated with
exactly one entity from the other entity set.
Slide 3-32
One-to-Many (1: ): An entity from one entity set can be related to
multiple entities from another entity set, but the reverse is not true.
Many-to-One (N:1): The reverse of one-to-many, where multiple
entities from one entity set are related to only one entity from
another entity set.
Many-to-Many (M:M): Each entity from one entity set can be
related to multiple entities in another entity set, and vice versa.
Slide 3-33
Slide 3-34
2. Participation Constraints
Participation constraints specify whether all instances of an entity
must participate in the relationship (total participation) or if some
instances can exist without being related (partial participation).
Event
Students
S1
S2 E1
S3 E2
S4
s5
Slide 3-35
3. Key Constraints (Identifying Dependencies)
Key constraints determine how one entity can uniquely identify
another in a relationship.
Student Exam
S1 E1
S2 E2
S3 E3
S4 E4
S5 E5
One-to-one
Slide 3-36
Customer Loan
Cust_id Loan_id
Cust_name Kyc
Mob_no Cust_id
Pan_no
Many-to-one
Slide 3-37
Customer Account
Cust_id Acc_id
Cust_name No.of Account
Mob_no Acc_Details
Pan_no Tax details
One-to-Many
Slide 3-38
4. Referential Integrity Constraints
Referential integrity constraints ensure that
relationships between entities remain valid. This is
particularly important when foreign keys are involved.
Example: If an employee has a department ID, the
department ID must exist in the Department table.
Cascade Delete/Update: When an entity is deleted or
updated, the corresponding related entities may need
to be deleted or updated as well to maintain
referential integrity.
Slide 3-39
5. Existence Dependency Constraints
Existence dependency constraints specify whether one entity's
existence depends on the existence of another.
•Existence-Dependent: An entity cannot exist unless it is
associated with another entity.
Example: A "Team" entity may depend on the existence of a
"Project." If a project is deleted, its teams must also be deleted.
•Existence-Independent: An entity can exist without being
associated with another entity.
Example: An employee can exist without managing a project,
meaning their existence is independent of the project entity.
Slide 3-40
Weak Entity Types vs. Strong Entity
Types
Weak Entity
•Entity types that do not have key attributes of their own
are called weak entity types.
•It refers to an entity that cannot be uniquely identified by
its own attributes alone.
•Instead, a weak entity depends on a "strong" (or
"regular") entity to provide a unique identifier, typically
through a relationship.
Slide 3-41
Key features of a weak entity:
•Dependent on a strong entity: A weak entity does not
have a primary key or enough attributes to uniquely
identify its instances. It needs a foreign key, typically a
reference to a strong entity, to form a composite key.
•Partial key: The weak entity usually has a "partial key"
(also called a "discriminator") that uniquely identifies
weak entities. But is not a primary key.
•Existence dependency: A weak entity typically has an
existence dependency on the strong entity, meaning it
cannot exist without being related to a particular instance
of the strong entity.
Slide 3-42
Strong Entity
•A "strong entity" (also known as a regular or
independent entity) in database design is an entity that
has a unique identifier (primary key) that allows it to be
uniquely identified without relying on another entity.
•Strong entities have their own set of attributes that can
independently distinguish one instance of the entity from
another.
Slide 3-43
Key characteristics of a strong entity:
•Primary key: A strong entity has a primary key,
which is an attribute or a set of attributes that can
uniquely identify each record or instance of that
entity.
•Independent existence: Strong entities do not
depend on other entities for their existence. They can
exist independently in the database.
•Distinct identity: Each instance of a strong entity is
distinguishable based solely on its own attributes.
Slide 3-44
Example
Consider a database for a library system:
•Strong entity: "Book" with an ISBN as its primary
key.
•Weak entity: "Chapter," which cannot be uniquely
identified without knowing the book it belongs to. The
chapter number (partial key) alone is not enough; the
combination of the book's ISBN and the chapter
number is required to uniquely identify a chapter.
Slide 3-45
Table (Entity) : BOOK
ISBN No Book Name Auther
2668 Proramming C Bala Guru Swami
2119 RDBMS Ramez,Shamkant
Table (Entity) : Chapter
Chapter No ISBN No Book Name Auther
1 2668 Proramming C Bala Guru Swami
5 2119 RDBMS Ramez,Shamkant
Slide 3-46
Refining the ER Design for the COMPANY
Database
Entity types :
1.Employee
2.Department
3.Project
Relationships on the base of cardinality constraint :
1.MANAGES, which is a 1:1(one-to-one) relationship type between
EMPLOYEE and DEPARTMENT. Participation is partial.
2.WORKS_FOR, a 1:N (one-to-many) relationship type between
DEPARTMENT and EMPLOYEE. Both participations are total.
3.CONTROLS, a 1:N relationship type between DEPARTMENT
and PROJECT. The participation of PROJECT is total, whereas that
Slide 3-47
of DEPARTMENT is deter mined to be partial.
ER Diagrams naming Conventions Design
Issues
Slide 3-48
Notation for ER Diagrams
Slide 3-49
Slide 3-50
Example of Other Notation: UML Class Diagrams
(OMT-Object Modeling Technique)
Slide 3-51
Relationship types of Degree
(a) Ternary Relationship (b) Binary Relationship Slide 3-52
EER (Enhanced Entity Relationship
Model)
Enhanced ERDs are high-level models that represent the
requirements and complexities of complex databases. The
EER model includes all modeling concepts of the ER
model. In addition, EER includes the following concepts.
•Subclasses and Superclasses
•Specialization and Generalization
Slide 3-53
Superclass and Subclass
A superclass is a high-level entity that can be further
segmented into subclasses or subsets. It is also referred
to as a Parent class. A subclass can be referred to as a
child or derived class.
Example: Science is a Super class which has subclasses
like Physics, Chemistry, Biology.
Slide 3-54
Generalization and Specialization
These are very common relationships found in real
entities. However, this kind of relationship was added
later as an enhanced extension to the classical ER
model. Specialized classes are often
called subclass while a generalized class is called a
superclass, probably inspired by object-oriented
programming.
Slide 3-55
Slide 3-56
