ER MODEL

Concept of ER model

 ER diagrams are visual tools that are used in the entity

relationship model.

 Initially proposed by Peter Chen in 1976.

 To create a uniform convention that considers both relational

database and network views.

 An entity relationship diagram is a visual representation of

different data using conventions that describe how these data
are related to each other.
SI-DBMS-UU 2
Contd…

 ER diagram can express the overall logical structure of a

database graphically.

 The entity relationship model is based on a perception of the

word as considering of a collection of basic objects (entities)
and relationships among these objects.

 It is an object based logical model.

 It is a high-level data model.

SI-DBMS-UU 3
 Contd…

The entity relationship model can be used as:

 A tool for data modeling and logical database design

(specification of an enterprise schema).

 A formal specification of overall system data structure.

 A tool for newcomers to learn database concepts and structure,

a communication tool between designers.

SI-DBMS-UU
A communication tool between designers and users. 4
 Contd…

There are three basic elements in ER model-

 Entities are the things about which we seek information.

 Attributes are the data we collect about the entities.

 Relationships provide the structure needed to draw

information from multiple entities.

SI-DBMS-UU 5
 Contd…
ER diagram consists of the following major components-

 RECTANGLES, which represent entity sets.

 ECLIPSE, which represent attributes.
 DIAMONDS, which represents relationship sets.
 LINES, which link attributes to entity sets and entity sets to
relationship sets.
 DOUBLE ELLIPSES, which represent multi valued attributes.
 DASHES ELLIPSES, which denote derived attributes.
 DOUBLE LINES, which indicate total participation of an entity
in our relationship set.
 DOUBLE RECTANGLES, which represent weak entity sets.
SI-DBMS-UU 6
Notation of ER Diagram

SI-DBMS-UU 7
Contd…

SI-DBMS-UU 8
Contd…

SI-DBMS-UU 9
Contd…

SI-DBMS-UU 10
Contd…

SI-DBMS-UU 11
 Example-

The elements - writer,

novel and consumer may
be described using ER
diagram as-

SI-DBMS-UU 12
Example-

SI-DBMS-UU 13
Example-

SI-DBMS-UU 14
Example-

SI-DBMS-UU 15
Example-

SI-DBMS-UU 16
Example-(Student-Course)

SI-DBMS-UU 17
Example-

SI-DBMS-UU 18
Entity, Entity Set and Entity Type

 An entity is an object that exists and is distinguishable from

other objects.
 For instance, Mac with phone number 93527381 is an entity.
 He can be uniquely identified as one particular person in the
universe.
 An Entity may be concrete (A person or a book, for example) or
abstract (like a holiday or a concept).

SI-DBMS-UU 19
Examples of entities

 Person: Employee, Student, Patient

 Place: Store, Building
 Object: Machine, product, and Car
 Event: Sale, Registration, Renewal
 Concept: Account, Course

SI-DBMS-UU 20
Entity Set

 An entity set is a set of entities .

 Example- all persons having an account at a bank.

 Entity sets need to be disjoint.

 Example, the entity set employee (all employees of the bank),
and the entity set customer. (All customers of the bank) may have
members in common.

SI-DBMS-UU 21
Entity Type

 An entity type defines a collection of entities that have the

same attributes and entities.
 An entity usually has an attribute whose values are distinct
from each individual entities in the collection.
 Attribute is called a key attribute, and its value can be used to
identify each entity uniquely.
 The entity type containing a key attribute are called a strong
entity type, or regular entity types.

SI-DBMS-UU 22
Differences between an Entity, an Entity
Type and an Entity Set
 A set of entities that have the same attributes is called an
entity type.

 Each type in the database is described by a name and a list of

attributes.

 Example an entity employee is an entity type that has name,

age and salary attributes.

 Employee- (Entity type) with attributes (Id,Name,Age)

SI-DBMS-UU 23
Contd…

 The individual entities of a particular entity type are grouped

into a collection or entity set, which is also called the
extension of the entity type.

 An entity is a thing in the real world it.

 It may be an object with a physical existence or an object with

a conceptual existence.

 A set of these entities having same attribute is the entity type

and collection of individual entity type is an entity set.
SI-DBMS-UU 24
Attributes

 Attributes are characteristics of entities.

 An attribute is a property used to describe the specific feature

of the entity.

 To describe an entity entirely, a set of attributes is used.

 Example, a student entity may be described by the student’s

name, Address, course etc.

SI-DBMS-UU 25
Contd…

 An entity will have a value for each of its attributes.

 Example- for a particular student, the following value can be
assigned:

 Roll number : 0401614010.

 Name : Sunil Kumar.
 Age : 27.
 Address : B4, Narayanpur Delhi.
 Course. : MCA.

 For each attribute there is a set of primitive values, called the

domain or value set of the attribute.
SI-DBMS-UU 26
Required and Optional Attributes

 A required attribute is an attribute that must have a value.

 It cannot be left in empty.

 An optional attribute is an attribute that does not require a

value.
 It can be left empty.

SI-DBMS-UU 27
 Types of Attributes

Attributes attached to an entity can be various type-

 Simple-

 The attribute that cannot be further divided into a smaller part

and represents the basic meaning is called the simple
attributes.

 For example, the first name, last name and age attributes of a
person entity represent a simple attribute.
SI-DBMS-UU 28
Contd…

 Compose -

 Attributes that can be further divided into a smaller unit, and

each individual unit contains a specific meaning.

 For example, the name attribute of an employee entity can be

subdivided into first name, last name and middle name.

SI-DBMS-UU 29
Contd…

 Single valued-

 Attributes having a single value for a particular entity.

 Age is a single valued attribute for a student entity.
 For instance, a part’s serial number, such as UP-08-01-80937 is
single valued but it is a composite attribute.
 Because it can be subdivided into the region in which the part
was produced (UP),
 The plant within the region (08).
 The shift within the plant (01).
 And the part number (80937).
SI-DBMS-UU 30
Contd…

 Multivalued-

 The attributes that have more than one values for a particular
entity is called a multi valued attributes.
 Different entities may have different number of values for
these kind of attributes.
 For multivalued attributes, we must also specify the minimum
and maximum number of values that can be attached.
 For example, phone number for a person entity. Is a
multivalued attribute.

SI-DBMS-UU 31
Contd…

 Derived attributes-

 An attribute may be classified as a derived attribute.

 A derived attribute is an attribute whose value is calculated
(derived) from other attributes.
 The derived attribute need not be physically stored in the
database; instead, it can be derived by using an algorithm.
 For example, An employee age may be found by computing the
integer value of the difference between the current date and
the date of birth.

SI-DBMS-UU 32
 Relationships, Relationship Sets,
Relationship Type
Constraints
Mapping Cardinalities
 Mapping Cardinalities or cardinalities ratios.
 It express the number of entities to which another entity can
be associated via relationship set.
 Mapping cardinality are most useful in describing binary
relationship sets.
 They can contribute to the description of relationship sets that
involve more than two entity sets R.
 Relationship set are between entity sets A and B. The mapping
cardinality must be one of the following-
SI-DBMS-UU 33
 Contd…
1. One-to-one

 Employee is assigned with a parking space.

 One employee is assigned with only one parking space and one
parking space is assigned to only one employee.
 Hence it is a 1:1 relationship and cardinality is One-To-One
(1:1)
SI-DBMS-UU 34
Contd…

 In ER modeling, this can be mentioned using notations as given

below

SI-DBMS-UU 35
Contd…

 An entity in A is associated with at most one entity in B.

 The entity in the B is associated with at most one entity in A.
 For example, relationship between college and principles.

SI-DBMS-UU 36
 Contd…

2. One-to-many

 Organization has employees.

 One organization can have many employees , but one
employee works in only one organization.
 Hence it is a 1:N relationship and cardinality is One-To-Many
(1:N)
SI-DBMS-UU 37
Contd…

 In ER modeling, this can be mentioned using notations as given

below

SI-DBMS-UU 38
Contd…

 An entity in A is associated with any number of entities in B.

 An entity in B is associated with at the most one entity in A.
 For example, Relationship between department and faculty.

SI-DBMS-UU 39
 Contd…

3. Many-to one

 It is the reverse of the One to Many relationship. employee

works in organization.
 One employee works in only one organization But one
organization can have many employees. Hence it is a M:1
SI-DBMS-UU 40
relationship and cardinality is Many-to-One (M :1)
Contd…

 In ER modeling, this can be mentioned using notations as given

below.

SI-DBMS-UU 41
Contd…

 An entity in A is associated with at most one entity in B.

 An entity in B is associated with any numbers in A.
 For example, relationship between course and instructor.

SI-DBMS-UU 42
 Contd…

4.Many-to-many

 Students enrolls for courses.

 One student can enroll for many courses and one course can be
enrolled by many students.
 Hence it is a M:N relationship and cardinality is Many-to-Many
SI-DBMS-UU 43

(M:N)
Contd…

 In ER modeling, this can be mentioned using notations as given

below

SI-DBMS-UU 44
 Contd…
 Entities in A and B are associated with any number of entities
from each other.
 Taught by relationship between course and faculty.
 One faculty member can be assigned to teach many courses.
 And one course may be taught by many members.

SI-DBMS-UU 45
Contd…

 Relationship between book and author.

 One author can write many books and one book can be written
by more than one authors.

SI-DBMS-UU 46
Keys

 In the relational model key are important because they are

used to ensure that each row in a table is uniquely identifiable.

 They are also used to establish relationship among tables and

to ensure the integrity of the data.

 A key is a minimal set of attributes whose values uniquely

identify an entity in the set.

SI-DBMS-UU 47
 Contd…
 In the given schema of Customer Table

 In the given relational table, “cust_id” is the Primary Key as it can

identify all the row uniquely from the table.
 In the given relational table, “cust_id”, “cust_aadhaar_number”,
“cust_pan_number” are the Candidate Key as it can identify all the
row uniquely from the table.
 In the given relational table, Primary Key and Candidate Key can be
Super Key. In Customer Table i.e ‘cust_id’ is sufficient to identify
the tuples uniquely from the table. Any set of the column which
contains ‘cust_aadhaar_number’, ‘cust_pan_number’ is a Super Key.
SI-DBMS-UU 48
Super key
 A super key is an attribute or set of attributes used to identify
the records/rows/tuples uniquely in a relation.
 It is a superset of candidate key.
 A table can have many super keys.
 It can have additional attribute that are not needed for unique
identity.
 Consider our relationship person.

SI-DBMS-UU 49
Contd…

SI-DBMS-UU 50
Candidate key
 Super key of a relation can contain extra attributes.
 Candidate keys are minimal super key i.e. such a key
contains no extraneous attribute.
 An attribute is called extraneous, if even after
removing it from the key makes the remaining
attribute still has the property of a key.
 It is a distinct set o attributes from which primary key
can be selected.

SI-DBMS-UU 51
Contd…
 The following properties must be satisfied by the candidate
keys:

 A candidate key must be unique.

 A candidate keys value must exist. It cannot be Null. (This is also
called entity integrity rule.)
 A candidate key is a minimal set of attributes.
 The value of a candidate key must be stable. Its value cannot
change outside the control of the system.

SI-DBMS-UU 52
Contd…

SI-DBMS-UU 53
Primary key
 A relation can have more than one candidate keys and
none of them can be chosen as a primary key.
 The primary key should be chosen in such a way that
its value must not be (or rarely) change.
 Primary key cannot be duplicate.
 It cannot be NULL.
 The value must be assigned while inserting records.
 A relation can have only one primary key.

SI-DBMS-UU 54
Contd…
 Note: We shall use the term primary key to denote a
candidate key that is chosen by the database designer
as the principal means of identifying entities within an
entity set.
 If we add additional attributes to a primary key, the
resulting combination would still uniquely identify an
instance of the entity set. Such keys are called Super
key.
 A primary key is therefore a minimum super key.

SI-DBMS-UU 55
Contd…

SI-DBMS-UU 56
Contd…

SI-DBMS-UU 57
 Foreign key
 Sometimes the information is stored in a relation is
linked to the information stored in other relation.
 If one of the relations is modified, the other must be
checked and perhaps modified.
 To keep the data consistent, a foreign key, FK is used.
 It is an attribute whose values match the primary key
values in another table.
 A foreign key is a copy of primary key of another table.

SI-DBMS-UU 58
 Contd…

Some of the important points of foreign keys are as follows:

 It has the same name as the primary key, from which is was
copied.
 It can take NULL values.
 There is no restriction on a foreign key to be unique.
 The field specifications of the primary key from which it was
copied have to be the same for the foreign key too.
 The value of foreign key is a substitute, or drawn from a
primary key.
SI-DBMS-UU 59
Contd…

SI-DBMS-UU 60
Alternate Keys

SI-DBMS-UU 61
Contd…

SI-DBMS-UU 62
Composite Key

SI-DBMS-UU 63
Contd…

SI-DBMS-UU 64
 Contd…
KEY TYPE DEFINITION
Super key An attribute or combination of attributes that uniquely
identifies each row in a table.
Candidate A minimal (Irreducible) super key.
key
Primary A candidate key selected to uniquely identify all other
Key attribute, value in any given row.
Secondary An attribute or combination of attributes used strictly for
key data retrieval purpose.
Foreign An attribute or combination of attributes in one table,
key table, whose values must either match the primary key in
SI-DBMS-UU
another table or be null. 65
 Important Points
 SQL keys are used to identify rows in a table uniquely.
 SQL keys can either be a single column or a group of columns.
 A Super key is a single key or a group of multiple keys that can uniquely identify tuples in a table.
 Super keys can contain redundant attributes that might not be important for identifying tuples.
 Super keys are a superset of Candidate keys.
 Candidate keys are a subset of Super keys. They contain only those attributes which are required to
identify tuples uniquely.
 All Candidate keys are Super keys. But the vice-versa is not true.
 A Primary key is a Candidate key chosen to uniquely identify tuples in the table.
 Primary key values should be unique and non-null.
 There can be multiple Super keys and Candidate keys in a table, but there can be only one Primary
key in a table.
 Alternate keys are those Candidate keys that were not chosen to be the Primary key of the table.
 A Composite key is a Candidate key with more than one attribute.
 A Foreign key is an attribute that is a Primary key in its parent table but is included as an attribute
in the host table.
 Foreign keys may accept non-unique and null values.

SI-DBMS-UU 66
Weak Entities

 Based on the concept of foreign key, there may arise a

situation when we have to relate an entity having a
primary key of its own and an entity not having a
primary key of its own.
 In such a case, the entity having its own primary key
is called a strong entity.
 The entity not having its own primary key is called a
weak entity.

SI-DBMS-UU 67
Contd…
 For a weak entity set to be meaningful it must be associated
with another entity set called the identifying or owner entity
set.
 A weak entity is represented by a double rectangle.
 The relation between one strong and one weak entity is
represented by a double diamond.
 This relationship is also known as identifying relationship.

SI-DBMS-UU 68
 Contd…

A weak entity is one that meets two conditions-

 The entity is existence dependent i.e. it cannot exist without

the entity with which it has a relationship.

 The entity has a primary key that is partially or totally derived

from the parent entity in the relationship.

SI-DBMS-UU 69
 Contd…
We have weak entities for several reasons:

 We want to avoid the data duplication and consequent possible

inconsistencies caused by duplicating the key of the strong
entity.
 Weak entities reflect the logical structure of an entity being
dependent on another entity.
 Weak entities can be deleted automatically when the strong
entity is deleted.
 Weak entities can be stored physically with their strong
entities.
SI-DBMS-UU 70
Contd…

A weak entity can be easily converted to a

strong entity by adding a primary key or
key attribute, which can define the
uniqueness constraint.

SI-DBMS-UU 71
ER Diagram Examples

 In a University database, we might have entities for Students,

Courses, and Lecturers.

 Students entity can have attributes like Rollno, Name, and

DeptID.

 They might have relationships with Courses and Lecturers.

SI-DBMS-UU 72
Contd…
 The ER diagram is given below:

SI-DBMS-UU 73
 Generalization
 Generalization is like a bottom-up approach in which two or
more entities of lower level combine to form a higher-level
entity if they have some attributes in common.

 In generalization, an entity of a higher level can also combine

with the entities of the lower level to form a further higher-
level entity.

 Generalization is more like subclass and superclass system, but

the only difference is the approach.

SI-DBMS-UU
Generalization uses the bottom-up approach. 74
Contd…
 In generalization, entities are combined to form a more
generalized entity, i.e., subclasses are combined to make a
superclass.
 For example, Faculty and Student entities can be generalized
and create a higher-level entity Person.

SI-DBMS-UU 75
Specialization
 Specialization is the abstracting process of introducing new
characteristics to an existing class of object to create one or
more new classes of object.
 This involves taking a higher-level entity and using additional
characteristics generating lower-level entities.
 The lower-level entities also inherit the characteristics of the
higher-level entity.
 Specialization is a top-down approach, and it is opposite to
Generalization. In specialization, one higher level entity can be
broken down into two lower-level entities.
 Specialization is used to identify the subset of an entity set
that shares some distinguishing characteristics.
SI-DBMS-UU 76
Contd…
 Normally, the superclass is defined first, the subclass and its
related attributes are defined next, and relationship set are
then added.
 For example: In an Employee management system, EMPLOYEE
entity can be specialized as TESTER or DEVELOPER based on
what role they play in the company.

SI-DBMS-UU 77
Aggregation
 One limitation of the ER diagram is that they do not allow
representation of relationships among relationships.
 In such a case, the relationship among with its entities are
promoted (Aggregated to form an aggregate entity, which can
be used for expressing the required relationships).
 In aggregation, the relation between two entities is treated as
a single entity.
 In aggregation, relationship with its corresponding entities is
aggregated into a higher-level entity.

SI-DBMS-UU 78
 Contd…
 For example: Center entity offers the Course, entity act as a
single entity in the relationship which is in a relationship with
another entity visitor.
 In the real world, if a visitor visits a coaching center then he
will never enquiry about the Course only or just about the
Center instead he will ask the enquiry about both.

SI-DBMS-UU 79
Reduction of an ER diagrams to Tables
 The database can be represented using the notations,
and these notations can be reduced to a collection of
tables.
 A database which conforms to an entity relationship
diagram can be represented by a collection of tables.
 Converting the ER diagram to a table format is the
basis for deriving a relational database design from an
ER diagram.
 For each entity set and relationship set, there is a
unique table, which is assigned the name of the
corresponding entity set, or relationship set.
SI-DBMS-UU 80
Contd…

 Primary keys allow entity sets and relationship sets to be

expressed uniformly as tables, which represent the contents of
the database.
 Each table has a number of columns (generally corresponding
to attributes) which have unique names.
 In the database, every entity set or relationship set can be
represented in tabular form.

SI-DBMS-UU 81
Points for converting the ER diagram to the Table
 Entity type becomes a table: In the given ER diagram,
LECTURE, STUDENT, SUBJECT and COURSE forms individual
tables.
 All single-valued attribute becomes a column for the table: In
the STUDENT entity, STUDENT_NAME and STUDENT_ID form the
column of STUDENT table. Similarly, COURSE_NAME and
COURSE_ID form the column of COURSE table and so on.
 A key attribute of the entity type represented by the
primary key: In the given ER diagram, COURSE_ID,
STUDENT_ID, SUBJECT_ID, and LECTURE_ID are the key
attribute of the entity.

SI-DBMS-UU 82
Contd…
 The multivalued attribute is represented by a separate table:
In the student table, a hobby is a multivalued attribute. So it is
not possible to represent multiple values in a single column of
STUDENT table. Hence we create a table STUD_HOBBY with
column name STUDENT_ID and HOBBY. Using both the column,
we create a composite key.
 Composite attribute represented by components: In the given
ER diagram, student address is a composite attribute. It
contains CITY, PIN, DOOR#, STREET, and STATE. In the STUDENT
table, these attributes can merge as an individual column.
 Derived attributes are not considered in the table: In the
STUDENT table, Age is the derived attribute. It can be
calculated at any point of time by calculating the difference
between current date and Date of Birth.
SI-DBMS-UU 83
 Contd…
 Using these rules we can convert the ER diagram to tables and
columns and assign the mapping between the tables. Table structure
for the given ER diagram is as below:

SI-DBMS-UU 84
Advantages of ER Model

 ER Modeling is simple and easily understandable.

 It is represented in business users language and it can be
understood by non-technical specialist.
 Intuitive and helps in Physical Database creation.
 Can be generalized and specialized based on needs.
 Can help in database design.
 Gives a higher level description of the system

SI-DBMS-UU 85
Disadvantages of ER Model

 Physical design derived from E-R Model may have some amount
of ambiguities or inconsistency.

 Sometime diagrams may lead to misinterpretations.

SI-DBMS-UU 86
 That’s All

SI-DBMS-UU 87
 Queries???

SI-DBMS-UU 88