CUST ID CUST NAME TEL NO

1 A 2223
2 A 555555

UNIT 3-14 MKS

DATABSE DESIGN USING ER MODEL

1.Entity Relationship Model

The entity-relationship data model perceives the real world as consisting of basic objects,
called entities and relationships among these objects. It was developed to facilitate data
base design by allowing specification of an enterprise schema which represents the
overall logical structure of a data base.
Main features of ER-MODEL:
• Entity relationship model is a high level conceptual model
• It allows us to describe the data involved in a real world enterprise in terms of
objects and their relationships.
• It is widely used to develop an initial design of a database
• It provides a set of useful concepts that make it convenient for a developer to
move from a baseid set of information to a detailed and description of information
that can be easily implemented in a database system
• It describes data as a collection of entities, relationships and attributes.

2. ENTITIES
1.It is a collection of objects.
2.An entity is an object that is distinguishable from other objects by a set of
attributes.
3.This is the basic object of E-R Model, which is a 'thing' in the real world
with an independent existence.
4.An entity may be an 'object' with a physical existence.
5.Entities can be represented by 'Ellipses'.Example:i.Customer, account
etc.
3. ATTRIBUTES
Characteristics of an entity are called as an attribute.The properties of a
particular entity are called as attributes of that specified entity.
Example:Name, street_address, city ---customer database.Acc-no, balance
---account database.
Types:These can be classified into following types.
1.Simple Attributes.
2.Composite Attributes.
3.Single Valued Attributes.
4.Mutivalued Attributes.
5.Stored Attributes.
6.Derived Attributes.
Explanation is as below

1.Simple Attributes:
The attributes that are not divisible are called as 'simple or atomic
attributes'.Example:cust_name, acc_no etc..

2.Composite Attributes:The attributes that can be divided into smaller

subparts, which represent more basic attributes with independent
meaning.These are useful to modelsituations in which a user sometimes
refers to the composite attribute as unit but at other times refers specifically
to its components.
Example:Street_address can be divided into 3 simple attributes as Number,
Street and Apartment_no. Street_addressCityState Zip

3.Single Valued Attribute:

The attributes having a single value for a particular entity are called as
'Single Valued Attributes'.Example:'Age' is a single valued attribute of
'Person'.

4.Multi Valued Attribute:

The attributes, which are having a set of values for the same entity, are
called as 'Multi Valued Attributes'.

Example:
A 'College Degree' attribute for a person.i.e, one person may not have a
college degree, another person may have one and a third person may have
2or more degrees.
A multi-valued attribute may have lower and upper bounds on the number
of values allowed for each individual entity.

5.Derived Attributes:
An attribute which is derived from another attribute is called as a ‘derived
attribute.
Example:‘Age’ attribute is derived from another attribute ‘Date’.

6.Stored Attribute:
An attribute which is not derived from another attribute is called as a ‘stored
attribute.
Example:In the above example,’ Date’ is a stored attribute.

Types of Attributes-

In ER diagram, attributes associated with an entity set may be of the following types-

1. Simple attributes
2. Composite attributes
3. Single valued attributes
4. Multi valued attributes
5. Derived attributes
6. Key attributes

1. Simple Attributes-

Simple attributes are those attributes which can not be divided further.
Example-

Here, all the attributes are simple attributes as they can not be divided further.

2. Composite Attributes-

Composite attributes are those attributes which are composed of many other simple attributes.

Example-
Here, the attributes “Name” and “Address” are composite attributes as they are composed of
many other simple attributes.

3. Single Valued Attributes-

Single valued attributes are those attributes which can take only one value for a given entity from
an entity set.

Example-

Here, all the attributes are single valued attributes as they can take only one specific value for
each entity.

4. Multi Valued Attributes-

Multi valued attributes are those attributes which can take more than one value for a given entity
from an entity set.

Example-
Here, the attributes “Mob_no” and “Email_id” are multi valued attributes as they can take more
than one values for a given entity.

5. Derived Attributes-

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

Example-

Here, the attribute “Age” is a derived attribute as it can be derived from the attribute “DOB”.

6. Key Attributes-
Key attributes are those attributes which can identify an entity uniquely in an entity set.

Example-

Here, the attribute “Roll_no” is a key attribute as it can identify any student uniquely.

4.ENTITY SETS
Entity Type:
A collection entities that have the same attributes is called as an 'entity
type'.Each entity type is described by its name and attributes.

Entity Set:
Collection of all entities of a particular entity type in the database at any
point of time is called as an entity set.
The entity set is usually referred to using the same name as the entity type.
An entity type is represented in ER diagrams as a rectangular box
enclosing the entity type name.

Example:Collection of customers.

5.Relationships
It is an association among entities.

6.Relationship Set

It is a collection of relationships

Types of Relationship Mapping

Following are the types of Relationship Mapping,
1. One - to - One Relationship
2. One - to - Many Relationship
3. Many - to - One Relationship
4. Many - to - Many Relationship

1. One - to - One Relationship

 In One - to - One Relationship, one entity is related with only one other entity.
 One row in a table is linked with only one row in another table and vice versa.
For example: A Country can have only one Capital City.

2. One - to - Many Relationship

 In One - to - Many Relationship, one entity is related to many other entities.

 One row in a table A is linked to many rows in a table B, but one row in a table B is
linked to only one row in table A.
For example: One Department has many Employees.
3. Many - to - One Relationship

 In Many - to - One Relationship, many entities can be related with only one other entity.
For example: No. of Employee works for Department.
 Multiple rows in Employee table is related with only one row in Department table.

4. Many - to - Many Relationship

 In Many - to - Many Relationship, many entities are related with the multiple other
entities.
 This relationship is a type of cardinality which refers the relation between two entities.
For example: Various Books in a Library are issued by many Students.
Participation Constraints
Following are the two types of Participation Constraints,
1. Total Participation
2. Partial Participation

1. Total Participation

 In Total Participation, every entity in the set is involved in some association of the
relationship.
 It is indicated by a double line ( ) between entity and relationship.
For example: Every Department must have a Manager.

2. Partial Participation

 In Partial Participation, not all entities in the set are involved in association of the
relationship.
 It is indicated by a single line ( ) between entity and relationship.
Mapping Cardinalities:
Mapping cardinalities or cardinality ratios, express the number of entities to which
another entity can be associated via a relationship set.
Mapping cardinalities are most useful in describing binary relationship sets, although
they can contribute to the description of relationship sets that involve more than two
entity sets.
For a binary relationship set R between entity sets A and B, the mapping cardinalities
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.
Eg: relationship between college and principal
11 11
college has principal

One to many:

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.

Eg: Relationship between department and faculty

11 M1

Department Works Faculty

in
Many to one:
An entity in A is associated with at most one entity in B. An entity in B is associated with
any number in A.

1 M
Course Teach Faculty
es
Many –to-many:
Entities in A and B are associated with any number of entities from each other.

1 M
Customer Depos Account
it

More about entities and Relationship:

Recursive relationships:
When the same entity type participates more than once in a relationship type in different
roles, the relationship types are called recursive relationships.
Participation constraints:
The participation constraints specify whether the existence of any entity depends on its
being related to another entity via the relationship. There are two types of participation
constraints
Total :
.When all the entities from an entity set participate in a relationship type , is called total
participation. For example, the participation of the entity set student on the relationship
set must ‘opts’ is said to be total because every student enrolled must opt for a course.
Partial:
When it is not necessary for all the entities from an entity set to particapte ion a
relationship type, it is called participation. For example, the participation of the entity set
student in ‘represents’ is partial, since not every student in a class is a class
representative.
Weak Entity:
Entity types that do not contain any key attribute, and hence can not be identified
independently are called weak entity types. A weak entity can be identified by uniquely
only by considering some of its attributes in conjunction with the primary key attribute of
another entity, which is called the identifying owner entity.
Generally a partial key is attached to a weak entity type that is used for unique
identification of weak entities related to a particular owner type. The following
restrictions must hold:
• The owner entity set and the weak entity set must participate in one to may
relationship set. This relationship set is called the identifying relationship set of
the weak entity set.
 • The weak entity set must have total participation in the identifying relationship.
Example:
Consider the entity type dependent related to employee entity, which is used to keep
track of the dependents of each employee. The attributes of dependents are : name
,birthrate, sex and relationship. Each employee entity set is said to its own the
dependent entities that are related to it. How ever, not that the ‘dependent’ entity does
not exist of its own., it is dependent on the employee entity. In other words we can
say that in case an employee leaves the organization all dependents related to without
the entity ‘employee’. Thus it is a weak entity.

Keys:
Super key:
A super key is a set of one or more attributes that taken collectively, allow us to
identify uniquely an entity in the entity set.
For example , customer-id,(cname,customer-id),(cname,telno)
Candidate key:
In a relation R, a candidate key for R is a subset of the set of attributes of R, which
have the following properties:
• Uniqueness:no two distinct tuples in R have the same values for the
candidate key
• Irreducible:No proper subset of the candidate key has the uniqueness
property that is the candidate key.
Eg: (cname,telno)
Primary key:
The primary key is the candidate key that is chosen by the database designer as the
principal means of identifying entities with in an entity set. The remaining candidate
keys if any, are called alternate key.

ER Diagrams
 ERD stands for Entity Relationship diagram.
 It is a graphical representation of an information system.
 ER diagram shows the relationship between objects, places, people, events etc. within
that system.
 It is a data modeling technique which helps in defining the business process.
 It used for solving the design problems.

Following are the components of ER Diagram,

Notations Representation Description

Rectangle It represents the Entity.

Ellipse It represents the Attribute.

 Diamond It represents the Relationship.

It represents the link between attribute and entity set to

Line
relationship set.

Double Rectangle It represents the weak entity.

It represents composite attribute which can be divided

Composite into subparts.
Attribute For eg. Name can be divided into First Name and Last
Name

Multi valued It represents multi valued attribute which can have many
Attribute values for a particular entity. For eg. Mobile Number.

It represents the derived attribute which can be derived

Derived Attribute
from the value of related attribute.

It represents key attribute of an entity which have a

Key Attribute unique value in a table.
For eg. Employee → EmpId (Employee Id is Unique).

 Relationship Mapping in ER Diagram of Databases

 .

EER Model
As the complexity of data increased in the late 1980s, it became more and more difficult to use
the traditional ER Model for database modelling. Hence some improvements or enhancements
were made to the existing ER Model to make it able to handle the complex applications better.
Hence, as part of the Enhanced ER Model, along with other improvements, three new concepts
were added to the existing ER Model, they were:

1. Generalization
2. Specialization
3. Aggregration

Let's understand what they are, and why were they added to the existing ER Model.

EER is a high-level data model that incorporates the extensions to the original ER model.

It is a diagrammatic technique for displaying the following concepts

 Sub Class and Super Class

 Specialization and Generalization
 Union or Category
 Aggregation
These concepts are used when the comes in EER schema and the resulting schema diagrams
called as EER Diagrams.

Features of EER Model

 EER creates a design more accurate to database schemas.
 It reflects the data properties and constraints more precisely.
 It includes all modeling concepts of the ER model.
 Diagrammatic technique helps for displaying the EER schema.
 It includes the concept of specialization and generalization.
 It is used to represent a collection of objects that is union of objects of different of
different entity types.

A. Sub Class and Super Class

 Sub class and Super class relationship leads the concept of Inheritance.

1. Sub Class
A subclass is a class derived from the superclass. It inherits the properties of the superclass and
also contains attributes of its own

 Sub class is a group of entities with unique attributes.

 Sub class inherits properties and attributes from its super class.
 Car, Truck and Motorcycle are all subclasses of the superclass Vehicle. They all inherit
common attributes from vehicle such as speed, colour etc. while they have different
attributes also i.e Number of wheels in Car is 4 while in Motorcycle is 2.
 
For example: Square, Circle, Triangle are the sub class of Shape super class.

 The relationship between sub class and super class is denoted with symbol.

1. Super Class

 A superclass is the class from which many subclasses can be created. The subclasses
inherit the characteristics of a superclass. The superclass is also known as the parent class
or base class.

 In the above example, Vehicle is the Superclass and its subclasses are Car, Truck and
Motorcycle.Super class is an entity type that has a relationship with one or more
subtypes.
 An entity cannot exist in database merely by being member of any super class.
For example: Shape super class is having sub groups as Square, Circle, Triangle.

B. Specialization and Generalization

1. Generalization

Generalization is a bottom-up approach in which two lower level entities combine to form a
higher level entity. In generalization, the higher level entity can also combine with other lower
level entities to make further higher level entity.
It's more like Superclass and Subclass system, but the only difference is the approach, which is
bottom-up. Hence, entities are combined to form a more generalised entity, in other words, sub-
classes are combined to form a super-class.

 Generalization is the process of generalizing the entities..

 It is a bottom UP approach, in which two lower level entities combine to form a higher
level entity.
 Generalization is the reverse process of Specialization.
  It defines a general entity type from a set of specialized entity type.
 It minimizes the difference between the entities by identifying the common features.
For example:

 For example, Saving and Current account types entities can be generalised and an entity
with name Account can be created, which covers both.

________________________________________

In the above example, Tiger, Lion, Elephant can all be generalized as Animals.

2. Specialization
Specialization is opposite to Generalization. It is a top-down approach in which one higher level
entity can be broken down into two lower level entity. In specialization, a higher level entity may
not have any lower-level entity sets, it's possible.

 Specialization is a process that defines a group entities which is divided into sub groups
based on their characteristic.
 It is a top down approach, in which one higher entity can be broken down into two lower
level entity.
 It maximizes the difference between the members of an entity by identifying the unique
characteristic or attributes of each member.
 It defines one or more sub class for the super class and also forms the superclass/subclass
relationship.
For example


In the above example, Employee can be specialized as Developer or Tester, based on what role
they play in an Organization.
Aggregration
Aggregration is a process when relation between two entities is treated as a single entity.

In the diagram above, the relationship between Center and Course together, is acting as an
Entity, which is in relationship with another entity Visitor. Now in real world, if a Visitor or a
Student visits a Coaching Center, he/she will never enquire about the center only or just about
the course, rather he/she will ask enquire about both.
Aggregation is a process in which a single entity alone is not able to make sense in a relationship
so the relationship of two entities acts as one entity. I know it sounds confusing but don’t worry
the example we will take, will clear all the doubts.

Aggregration Example
DBMS Aggregration Example
In real world, we know that a manager not only manages the employee working under them but
he has to manage the project as well. In such scenario if entity “Manager” makes a “manages”
relationship with either “Employee” or “Project” entity alone then it will not make any sense
because he has to manage both. In these cases the relationship of two entities acts as one entity.
In our example, the relationship “Works-On” between “Employee” & “Project” acts as one entity
that has a relationship “Manages” with the entity “Manager”.
 empno name
dob
employee
Generalization Specialization

Is Is
degree degree

Full time Part-time

employee employee

Is Is Is Is

faculty staff teaching casual

degree Intrest Intrest Classificatio hourrat

Faculty(empno,degree,intrest)
EMPLOYEE(empno,name,dob) Staff(empno,hour-rate)
FULL_TIME_EMPLOYEE(empno,sala Teaching (empno,stipend)
ry)
PART_TIME_EMPLOYEE(empno,type)
ER- Diagram For College Database

rollno name addres

coursei cname duratio

Student opts Course

N 1
1
M

has enroll Taug

ed ht by
1 N
N 1 Work N fid
s in
gaurdian Department dno Faculty addre

Head
name dnam of 1 name sal
1
addres relationship

Date