UNIT-2

Entity-Relationship Model: Basic Concepts, Constraints, Keys: Primary Key, Super key,
Candidate key, Entity Types, Entity Sets, Design issues, Entity-Relationship Diagram, Relations,
Relationship types, Roles and Structural Constraints, Weak Entity sets, Extended ER Features,
Design of E-R Database Schema, Reduction of an E-R Schema to tables.

The entity-relationship (E-R) data model uses a collection of basic objects, called entities, and
relationships among these objects. The ER data Model allows us to describe the data involved in
the real world enterprise in terms of objects and relationships and is widely used in the database
design.

Entities: An entity is a “thing” or “object” in the real world that is distinguishable from other
objects. An entity may be an object with a physical existence (for example, a particular person,
car, house, or employee) or it may be an object with a conceptual existence (for instance, a
company, a job, or a university course). Entities can even be events such as an appointment to
see a doctor.

Entities are the principal data objects about which information is to be collected and is
distinguishable from other objects; they usually denote a person, place, thing, object, event or
concept of informational interest.

Attributes: An Entity is represented by a set of attributes. Attributes are particular properties

that describe the entity. For example, an EMPLOYEE entity may be described by the employee’s
name, age, address, salary, and job.

Values: Each Entity will have a value for each of its attributes. For example The EMPLOYEE
entity e1 has four attributes: Name, Address, Age, and Home_phone; their values are ‘John
Smith,’ ‘2311 Kirby, Houston, Texas 77001’, ‘55’, and ‘713-749-2630’, respectively.

Entity Set: An entity set is a set of entities of the same type that share the same properties, or
attributes. The set of all people who are employees at a given company, for example, can be
defined as the entity set Employee. Similarly, the entity set student might represent the set of all
students in the university.
Types of Attributes:
1) Composite versus Simple (Atomic) Attributes: Composite attributes are the attributes that
can be divided into subparts (that is, other attributes). For example, an attribute name could be
structured as a composite attribute consisting of first name, middle, initial, and last name.

The address can be defined as the composite attribute address with the Attributes Street, city,
state, and zip code. Note also that a composite attribute may appear as a hierarchy. In the
composite attribute address, its component attribute street can be further divided into street
number, street name, and apartment number. Figure depicts these examples of composite
attributes:

A simple attribute is an attribute that cannot be subdivided. For example marital status, rollno,
accountno etc.
2) Single-Valued versus Multivalued Attributes: A single valued attribute is an attribute that
can have only a single value. For example age is a single valued attribute. Loanno, Accountno
etc are single valued attributes.

Multivalued attributes are attributes that can have many values. For example colors attribute for
a Car or a Degree attribute for a person. An Employee may have zero or more phone numbers.

3) Derived Attributes: A Derived attribute is the attribute whose value is calculated or derived
from the values of other attributes. 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.

Relationships:
A relationship is an association among two or more entities. The entities that participate in a
relationship are also known as participants. Each relationship is identified by a name. For
example: PROFESSOR teaches CLASS, EMPLOYEE Works_For DEPARTMENT etc.

A Relationship set is a set of relationships of the same type. For example, consider a relationship
type WORKS_FOR between the two entity types EMPLOYEE and DEPARTMENT, which
associates each employee with the department for which the employee works. Each relationship
instance in the relationship set WORKS_FOR associates one EMPLOYEE entity and one
DEPARTMENT entity. In Figure, the employees e1, e3, and e6 work for department d1; the
employees e2 and e4 work for department d2; and the employees e5 and e7 work for department
d3.

Partcipant Relationship Set Partcipant

Degree of a Relationship Type or Relationship Degree:
The degree of a relationship type is the number of participating entity types. Hence, the
WORKS_FOR relationship is of degree two. A relationship type of degree two is called binary,
and one of degree three is called ternary. An example of a ternary relationship is SUPPLY,
shown in Figure, where each relationship instance associates three entities—a supplier s, a part p,
and a project j—whenever s supplies part p to project j.

Relationships can generally be of any degree, but the ones most common are binary
relationships. Higher-degree relationships are generally more complex than binary relationships.

Null Values :-

An attribute takes a null value when an entity does not have a value for it. The null value may
indicate “not applicable”. For example, the ApartmentNumber attribute of an address applies
only to addresses that are in apartment buildings and not to other types of residences, such as
single-family homes. Similarly, a CollegeDegrees attribute applies only to persons with college
degrees. For such situations, a special value called null is created. An address of a single-family
home would have null for its ApartmentNumber attribute, and a person with no college degree
would have null for CollegeDegrees.

Null can also be used if we do not know the value of an attribute for a particular entity-for
example, if we do not know the home phone of an employee. The meaning of the former type of
null is not applicable, whereas the meaning of the latter is unknown. The "unknown" category of
null can be further classified into two cases. The first case arises when it is known that the
attribute value exists but is missing-for example, if the Height attribute of a person is listed as
null. The second case arises when it is not known whether the attribute value exists-for example,
if the HomePhone attribute of a person is null. For example In application forms, there is one
column called middlename so one may have no middle name then a null value is entered in that
column.

Role Names:-

Each entity type that participates in a relationship type plays a particular role in the relationship
or the function that an entity plays in a relationship is called that entity’s role. The role name
signifies the role that a participating entity from the entity type plays in each relationship
instance, and helps to explain what the relationship means. For example, in the WORKS_FOR
relationship type, EMPLOYEE plays the role of employee or worker and DEPARTMENT plays
the role of department or employer.

Role names are not technically necessary in relationship types where all the participating entity
types are distinct, since each participating entity type name can be used as the role name.
However, in some cases(Unary relationship) the same entity type participates more than once in
a relationship type in different roles. In such cases the role name becomes essential for
distinguishing the meaning of each participation. Such relationship types are called recursive
relationships. Figure 3.11 shows an example. The SUPERVISION relationship type relates an
employee to a supervisor, where both employee and supervisor entities are members of the same
EMPLOYEE entity type. Hence, the EMPLOYEE entity type participates twice in
SUPERVISION: once in the role of supervisor (or boss), and once in the role of supervisee (or
subordinate).

Constraints on Binary Relationship Types:

Relationship types usually have certain constraints that limit the possible combinations of entities
that may participate in the corresponding relationship set. For example, if the company has a
rule that each employee must work for exactly one department, then we would like to describe
this constraint in the schema.

1) Structural constraints: Cardinality ratio and participation constraints, taken together, will be
referred as the structural constraints of a relationship type.

a) Cardinality Ratios for Binary Relationships Or Mapping Cardinalities:

Mapping cardinalities, or cardinality ratios, express the number of entities to which another
entity can be associated via a relationship set. For a binary relationship set R between entity sets
A and B, the mapping cardinality must be one of the following:

• One-to-one. An entity in A is associated with at most one entity in B, and an entity in B is

associated with at most one entity in A.
The relationship between the President and the country is an example of one-to-one relationship.
For a particular country there will be only one President. In general, a country will not have more
than one President hence the relationship between the country and the President is an example of
one-to-one relationship.

Another Example: HOD manages DEPARTMENT

• One-to-many. An entity in A is associated with any number (zero or more) of entities in B. An

entity in B, however, can be associated with at most one entity in A.

Example of one-to-many relationship is Country having states. For one country there can be
more than one state hence it is an example of one-to-many relationship.

Another Example: HOD manages EMPLOYEES

• Many-to-one. An entity in A is associated with at most one entity in B. An entity in B,

however, can be associated with any number (zero or more) of entities in A.

For Example: EMPLOYEE ManagedBy HOD

• Many-to-many. An entity in A is associated with any number (zero or more) of entities in B,
and an entity in B is associated with any number (zero or more) of entities in A.

For Example: EMPLOYEE WorksOn PROJECT

b) Participation Constraints:

The participation of an entity set E in a relationship set R is said to be total if every entity in E
participates in at least one relationship in R. If only some entities in E participate in relationships
in R, the participation of entity set E in relationship R is said to be partial.

➢ Customer Participation is partial because an individual can be a bank customer whether

or not he has a loan with the bank.

➢ Loan participation is total because we expect that every loan entity to be related to at least
one Customer.
Existence Dependency: An Entity is said to be existence dependent if it can exist in the database
only when it is associated with another related entity or we can say that an entity is existence
dependent if it has a mandatory foreign key- that is a foreign key attribute that cannot be null.
For example DEPENDENT entity is clearly existence dependent on the EMPLOYEE entity.

2) Key Constraints:

An important constraint on the entities of an entity type is the key on attributes.

a) Super Key: A Superkey is a set of one or more attributes that, taken collectively, allows
us to identify uniquely a tuple in the relation. For Example CustomerID attribute of the
customer relation. Thus CustomerID is a superkey. Similarly the combination of
CustomerName and CustomerID is a superkey for the relation Customer. The
CustomerName alone is not a superkey as several people might have the same name.

b) Candidate Key: The concept of Superkey is not sufficient for our purposes since a
superkey may contain extraneous attributes. If K is a superkey, then so is any superset of
K. Suppose that a combination of CustomerName and Adrress is sufficient to distinguish
among members of the Customer relation. Then, both {CustomerID} and
{CustomerName, Address} are candidate keys. Although the attributes CustomerID and
CustomerName together can distinguish Customer tuples, their combination,
{CustomerID, CustomerName}, does not form a candidate key, since the attribute
CustomerID alone is a candidate key.

c) Primary Key: Primary key denote a candidate key that is chosen by the database
designer as the principal means of identifying entities within an Entity Set or we can say
that out of all available candidate keys, a database designer can identify a Primary Key.

d) Foreign Key: Sometimes the information stored in an Entity set is linked to the
information stored in another entity set or A Foreign Key is an attribute whose values
match the Primary Key values in the related Entity Set. For example DeptNo in the
Department table is a primary key while Deptno in the employee table is a foreign key.

e) Composite Key: Ideally a Primary key is composed of only a single attribute. However it
is possible to use a composite key that is a primary key composed of more than one
attribute.

Weak Entity Sets:

A Weak Entity is one that meets two conditions:

1) It is existence dependent.
 2) It does not have sufficient attributes to form a primary key is termed a weak entity set. An
entity set that has a primary key is termed a strong entity set.

For a weak entity set to be meaningful, it must be associated with another entity set, called the
identifying or owner entity set. Every weak entity must be associated with an identifying entity;
that is, the weak entity set is said to be existence dependent on the identifying entity set. The
identifying entity set is said to own the weak entity set that it identifies. The relationship
associating the weak entity set with the identifying entity set is called the identifying
relationship.

The identifying relationship is many-to-one from the weak entity set to the identifying entity set,
and the participation of the weak entity set in the relationship is total.

The primary key of a weak entity set is formed by the primary key of the identifying entity set,
plus the weak entity set’s discriminator.

Consider the entity type DEPENDENT, related to EMPLOYEE, which is used to keep track of
the dependents of each employee via a 1:N relationship. An Employee may or may not have
dependents but the DEPENDENT must be associated with an EMPLOYEE so The
DEPENDENT cannot exist without the EMPLOYEE so DEPENDENT is a weak Entity. In our
example, the attributes of DEPENDENT are Name (the first name of the dependent), Birth_date,
Sex, and Relationship (to the employee). Two dependents of two distinct employees may, by
chance, have the same values for Name, Birth_date, Sex, and Relationship, but they are still
distinct entities. They are identified as distinct entities only after determining the particular
employee entity to which each dependent is related. Each employee entity is said to own the
dependent entities that are related to it.

EMPLOYEE ---- EmpNo, Ename, DoB, Hiredate, Salary

DEPENDENT -----EmpNo, Name, Birth_date, Sex, Relationship

Entity Relationship Diagram(ER Diagrams):

ER Diagram can express the overall logical structure of a database graphically. Following
symbols are used in the ER Model:
An Employee Entity Set:

Relationship between Entity Sets:

Derived Attribute:

Multivalued Attribute:
Composite Attribute:

ER Diagram with Composite, Multivalued and Derived Attribute:

Weak Entity:
One-to-many:

Many-to-many:

Ternary Relationship:
Role Names:
Extended E-R Features

Although the basic E-R concepts can model most database features, some aspects of a database
may be more aptly expressed by certain extensions to the basic E-R model. In this section, we
discuss the extended E-R features of specialization, generalization, higher- and lower-level entity
sets, attribute inheritance, and

Specialization:

Specialization is the process of defining a set of subclasses of an entity type; this entity type is
called the superclass of the specialization or the process of designating subgroupings within an
entity set is called specialization.

The set of subclasses that forms a specialization is defined on the basis of some distinguishing
characteristic of the entities in the superclass. For example, the set of subclasses {SECRETARY,
ENGINEER, TECHNICIAN} is a specialization of the superclass EMPLOYEE that
distinguishes among employee entities based on the job type of each employee.

As an example, Consider an entity set person with attributes PersonId, name, street, city etc. A
Person may be further classified as one of the following:

• employee.
• student.

Each of these person types is described by a set of attributes that includes all the attributes of
entity set person plus possibly additional attributes. Specialization is a top-design process.
Generalization:

Generalization is the reverse process of abstraction in which we suppress the differences among
several entity types, identify their common features, and generalize them into a single superclass
of which the original entity types are special subclasses.

Generalization proceeds from the recognition that a number of entity sets share some common
features (namely, they are described by the same attributes and participate in the same
relationship sets).

For example, consider the entity types CAR and TRUCK shown in Figure 4.3(a). Because they
have several common attributes, they can be generalized into the entity type VEHICLE, as
shown in Figure 4.3(b). Both CAR and TRUCK are now subclasses of the generalized superclass
VEHICLE. We use the term generalization to refer to the process of defining a generalized entity
type from the given entity types. Notice that the generalization process can be viewed as being
functionally the inverse of the specialization process; we can view {CAR, TRUCK} as a
specialization of VEHICLE rather than viewing VEHICLE as a generalization of CAR and
TRUCK.
Attribute Inheritance:
A crucial property of the higher- and lower-level entities created by specialization and
generalization is attribute inheritance. The attributes of the higher-level entity sets are said to be
inherited by the lower-level entity sets. For example, student and employee inherit the attributes
of person.

A lower-level entity set (or subclass) also inherits participation in the relationship sets in which
its higher-level entity (or superclass) participates. Like attribute inheritance, participation
inheritance applies through all tiers of lower-level entity sets. For example, suppose the person
entity set participates in a relationship person dept with department. Then, the student, employee,
instructor and secretary entity sets, which are subclasses of the person entity set, also implicitly
participate in the person dept relationship with department. The above entity sets can participate
in any relationships in which the person entity set participates.

Aggregation:

One limitation of the E-R model is that it cannot express relationships among relationship.
Consider the ternary relationship WorksOn between an Employee, branch and Job. Now we
suppose we want to record managers for tasks performed by an Employee at a branch, that is, we
want to record managers for (Employee, Branch, Job) combination.

There is redundant information in the resultant figure, however, since every Employee, Branch,
Job combination in manages is also in WorksOn.

So Aggregation is an abstraction through which relationships are treated as higher-level entities.

Thus, for our example, we regard the relationship set WorksOn as a higher-level entity set called
WorksOn. Such an entity set is treated in the same manner as is any other entity set. We can then
create a binary relationship manages between WorksOn and Manager to represent who manages
what task.

Reduction of an ER Diagram to Tables:

1) Representation of Strong or regular Entity type:

Each regular entity type in an ER Diagram is transformed into a relation. The name given to
the relation is the same as entity type.

Each simple attribute of the entity type becomes an attribute of the Relation.

The Identifier of the Entity type becomes the Primary key of the corresponding relation.

2) Converting Composite Attribute in an ER Diagram to tables:

When a regular entity type has a composite attribute, only the simple component attributes of
the composite attribute are included in the relation.

3) Converting Multivalued Attribute in an ER Diagram to tables:

When the regular entity type contains a multivalued attribute, two new relations are created.
The first relation contains all of the attributes of the entity type except the multivalued
attribute. The second relation contains two attributes that form the Primary key of the second
relation. The first of these attributes is the Primary key from the first relation, which becomes
the foreign key in the second relation.

4) Converting “Weak Entities” in ER Diagram to tables:

For each weak entity type, create a new relation and include all of the simple attributes as
attributes of the relation. Then include the Primary key of the identifying relation as the
foreign key attribute of this new relation. The Primary key of the new relation is the
combination of the Primary key of the Primary key of the identifying and the partial
identifier of the weak entity type. In this example, DEPENDENT is weak entity.

5) Mapping of Binary one-to-one relationship type:

Choose one of relations-S, say-and include as a foreign key in S the primary key of T. It is
better to choose an entity type with total participation in R in the role of S. Include all the
simple attributes of the 1:1 relationship type R as attributes of S.
6) Mapping of Binary one-to-many relationship type:

For each regular binary 1:N relationship type R, Identify the relation S that represents the
entity type at the N-side of the relationship type. Include a foreign key in S, the primary key
of the relation T that represents the other entity type participating in R. This is done because
each entity instance on the N side is related to at most one entity instance on the 1-side of the
relationship type. Include any simple attribute of the 1:N relationship type as attributes of S.

7) Mapping of Binary many-to-many relationship type:

For each binary M:N relationship type R, create a new relation S to represent relationship R.
Include a foreign key attributes in S the primary keys of the relations that represent the
participating entity type, their combinations will form the primary key of S. Also include any
simple attributes of the M:N relationship type as attributes of S. In binary M:N it is necessary
to create separate relationship S.
8) Mapping of Generalization:
Create a schema for the higher level entity set. For each lower level entity set, create a
schema that includes an attribute for e ach of the attributes of that entity set plus one for each
attribute of the primary key of the higher level entity set. Thus:

The Primary key attribute of the higher level entity set becomes Primary key attribute of the
higher level entity set as well as lower level entity sets.

2) Alternatively we can create just two relations, corresponding to employee and customer.

The first approach is general and always applicable.

9) Mapping of Aggregation:
The Employee, Branch, Job and manager entity sets and WorksOn relationship set are
mapped as described in previous section.

For the manages relationship set, we create a relation with the following attributes: The key
attribute of manager, The key attribute of WorksOn and the descriptive attribute of manages.