Unit-III, Data Modelling using the Entity-Relationship model

Unit – 2
Data Modelling Using Entity- Relationship Model

High-Level Conceptual Data Models for Database Design

The figure shows a simplified overview of the database design process.

a. Requirements Collection and Analysis:

 This is a first step during this step, the database designer’s interview
prospective database users to understand and document their data
requirements.
 The result of this step is a collected written set of users’ requirements.
Unit-III, Data Modelling using the Entity-Relationship model

 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.

b. Conceptual Schema:

 Next step is to create conceptual schema for database using a high-level

conceptual data model.
 This step is called conceptual design.
 The conceptual schema is consist of 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.
 The high-level conceptual schema can also use to ensure that all users’ data
requirements are met and that the requirements do not conflict.
 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.

c. Logical Design

 The next step in database design is the actual implementation of the

database, using a commercial DBMS.
 Most current DBMSs use an implementation data model such as the
relational or the object-relational database 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.
Unit-III, Data Modelling using the Entity-Relationship model

d. Physical Design

 The last step in database design is the physical design phase.

 The actual physical design will be done in this step.
 In this step the internal storage structures, file organizations, indexes, access
paths, and physical design parameters for the database files are specified.

Entity

An entity is a real-world thing which can be distinctly identified like a person, place or a
concept. It is an object which is distinguishable from others.
An entity can be of two types:

Tangible Entity: Tangible Entities are those entities which exist in the real world
physically. Example: Person, car, etc.

Intangible Entity: Intangible Entities are those entities which exist only logically and
have no physical existence. Example: Bank Account, etc.

Entity Type
The entity type is a collection of the entity having similar attributes.

Example:
Unit-III, Data Modelling using the Entity-Relationship model

Types of Entity type

 Strong Entity Type

 Weak Entity Type

1. Strong Entity Type: Strong entity are those entity types which has a key attribute.
The primary key helps in identifying each entity uniquely. It is represented by a
rectangle. In the above example, Roll_no identifies each element of the table
uniquely and hence, we can say that STUDENT is a strong entity type.

2. Weak Entity Type: Weak entity type doesn't have a key attribute. Weak entity
type can't be identified on its own. It depends upon some other strong entity for its
distinct identity. This can be understood with a real-life example. There can be
children only if the parent exits. There can be no independent existence of
children.
Unit-III, Data Modelling using the Entity-Relationship model

Entity Set

Entity Set is a collection of entities of the same entity type.

We can say that entity type is a superset of the entity set as all the entities are
included in the entity type.

Attribute

An attribute is a property or characteristic of an entity. An entity may contain any

number of attributes. One of the attributes is considered as the primary key.

In DBMS, there are various types of attributes available:

 Simple Attributes
Simple attributes in an ER model diagram are independent attributes that can't be
classified further and also, can't be sub-divided into any other component. These attributes
are also known as atomic attributes.
Unit-III, Data Modelling using the Entity-Relationship model

 Composite Attributes

Composite attributes have opposite functionality to that of simple attributes as we

can further sub-divide composite attributes into different components or sub-parts
that form simple attributes. In simple terms, composite attributes are composed
of one or more simple attributes.

 Single Valued Attributes

Single valued attributes are those attributes that consist of a single value for each entity
instance and can't store more than one value. The value of these single-valued attributes
always remains the same just like the name of a person.
Unit-III, Data Modelling using the Entity-Relationship model

 Multi-Valued Attributes

Multi-valued attributes have opposite functionality to that of single-valued attributes and

as the name suggests, multi-valued attributes can take up and store more than one value at
a time for an entity instance from a set of possible values. These attributes are represented
by co-centric elliptical shape and we can also use curly braces { } to represent multi-
valued attributes inside it.

As we can see in the above example, the Student entity has four attributes: Roll_no and Age
are simple as well as single-valued attributes as discussed above
but Mob_no and Email_id represented by co-centric ellipse are multi-valued
attributes. Each student in the real world can provide more than one email-id as well as a
mobile contact number and therefore, we need these attributes to be multi-valued so that
they can store multiple values at a time for an entity instance.

 Derived Attributes

Derived attributes are those attributes whose values can be derived from the values of
other attributes. They are always dependent upon other attributes for their value.

For example, As we were discussing above, DOB is a single-valued attribute and remains
constant for an entity instance. From DOB, we can derive the Age attribute which changes
every year, and can easily calculate the age of a person from his/her date of birth value.
Hence, the Age attribute here is derived attribute from DOB single-valued attribute.
Unit-III, Data Modelling using the Entity-Relationship model

 Complex Attributes (Rarely used attributes)

Complex attributes are rarely used in DBMS. They are formed by the combination of multi-
valued and composite attributes. These attributes always have many sub-sections in their
values

As we can see in the above example, Address_EmPhone (which represents Address, Email,
and Phone number all together) is a complex attribute. Email and Phone number are
multi-valued attributes while Address is a composite attribute which is further sub-divided
as House number, Street, City & State. This combination of multi-valued and composite
attributes altogether forms a complex attribute.

 Key Attributes

Key attributes are special types of attributes that act as the primary key for an entity and
they can uniquely identify an entity from an entity set. The values that key attributes store
must be unique and non-repeating

As we can see in the above example, we can say that the Roll_no attribute of the Student
entity is not only simple and single-valued attribute but also, a key valued attribute as
Unit-III, Data Modelling using the Entity-Relationship model

well. Roll_no of a student will always be unique to identify the student. Also note that
the Gender and Age of two or more persons can be same and overlapping in nature and
obviously, we can't identify a student on the basis of them. Hence, gender and age are not
key-valued attributes.

. Stored Attribute

Values of stored attributes remain constant and fixed for an entity instance and also, they
help in deriving the derived attributes. For example, Age attribute can be derived
from Date of Birth attribute, and also, Date of birth attribute has fixed and constant value
throughout the life of an entity. Hence, Date of Birth attribute is a stored attribute.

Types of Keys in DBMS

 Super Key – A super key is a group of single or multiple keys which identifies rows
in a table.
 Primary Key – is a column or group of columns in a table that uniquely identify
every row in that table.
 Candidate Key – is a set of attributes that uniquely identify tuples in a table.
Candidate Key is a super key with no repeated attributes.
 Alternate Key – is a column or group of columns in a table that uniquely identify
every row in that table.
 Foreign Key – is a column that creates a relationship between two tables. The
purpose of Foreign keys is to maintain data integrity and allow navigation between
two different instances of an entity.
 Compound Key – has two or more attributes that allow you to uniquely recognize a
specific record. It is possible that each column may not be unique by itself within the
database.
Unit-III, Data Modelling using the Entity-Relationship model

 Composite Key – is a combination of two or more columns that uniquely identify

rows in a table. The combination of columns guarantees uniqueness, though
individual uniqueness is not guaranteed.

Cardinality Ratios For Binary Relationships

 The cardinality ratio for a binary relationship specifies the maximum

number of relationship instances that an entity can participate in.
 For example, in the WORKS_FOR binary relationship type,
DEPARTMENT:EMPLOYEE is of cardinality ratio 1:N,
 Meaning that each department can be related to any number of
employees, but an employee can be related to (work for) only one
department.
 The possible cardinality ratios for binary relationship types are 1:1, 1:N,
N:1, and M:N.

ONE-TO-ONE(1:1):

 When only one instance of an entity is associated with the

relationship, it is marked as '1:1'.
 The following image reflects that only one instance of each entity
should be associated with the relationship.
 It depicts one-to-one relationship.

1. ONE-TO-MANY:

 When more than one instance of an entity is associated with a

relationship, it is marked as '1:N'.
Unit-III, Data Modelling using the Entity-Relationship model

 The following image reflects that only one instance of the entity on
the right and more than one instance of an entity on the left can be
associated with the relationship.
 It depicts one-to-many relationship.

2. MANY-TO-ONE:

When more than one instance of an entity is associated with the

relationship, it is marked as N:1'.

Many-To-Many:
 An entity of entity set is associated with two or more entities of
entity.
 For example: a doctor has many patients to diagnose on disease.
 It depicts many-to-many relationship.
Unit-III, Data Modelling using the Entity-Relationship model

Participation Constraints

Total Participation
 Each entity is involved in the relationship. Total participation is
represented by double lines.

Partial participation
 Not all entities are involved in the relationship. Partial
participation is represented by single lines.

ER- Model

o ER model stands for an Entity-Relationship model. It is a high-level data model. This

model is used to define the data elements and relationship for a specified system.
o It develops a conceptual design for the database. It also develops a very simple and
easy to design view of data.
o In ER modelling, the database structure is portrayed as a diagram called an entity-
relationship diagram.
o

Notations Of ER-Diagram
Unit-III, Data Modelling using the Entity-Relationship model

Examples on ER Diagram
Unit-III, Data Modelling using the Entity-Relationship model

Example 2: ER Diagram on Student Data base

Unit-III, Data Modelling using the Entity-Relationship model

RELATIONSHIP

 A Relationship describes relations between entities.

 The relationship is represented using diamonds.
 A relationship type R among n entity types E1, E2, ..., En defines a set of associations
or a relationship set among entities from these entity types.
Unit-III, Data Modelling using the Entity-Relationship model
Unit-III, Data Modelling using the Entity-Relationship model

It Means