UNIT-2

Entanty instance :
An entity type is a single occurrence of an entity type. An entity type is
described just once in a database, while many instances of that entity type
may be represented by data stored in the database.
For example , there is one employee entity type in most organisations, but
there may be hundreds of instances of this entity type stored in the database.

E-R Model:
Any entity-relationship diagram(ERD) shows the relationship of
entity sets stored in a database. An entity in this context is a
component of data. In other words, ERdiagrams describe the logical
structure of databases. At first glance, an entity-relationship
diagram looks very much like a flow chart. It contains the
specialised symbols, and the meanings of those symbols, that
make it unique.
1. Components of E-R MODEL
The three main components of the ER MODEl are:
1. Entities
2. Attributes
3. Relationships
1.Entity
In ERM terms, an entity is a thing within the organisation that we
want to keep information about, such as a customer employee or
course. They are typically nouns. In other words, an entity in an
ERM actually refers to a table, and rows within the table are
referred to as entity occurrences.
Entities are represented by rectangles containing the name of the
entity. Entity names must be singular and in capital letters. There
are three types of entities discussed below;
● Strong entities: They exist independently from other entity
type. They always have one or more attributes that uniquely
distinguish each occurrence of the entity.
● Weak entities: They depend on same other entity. They do not
have unique attributes and have no meaning in the diagram
without depending on another entity . This other entity is
known as the owner.
● Associative entities: these are entities that associate the
instances of one or more entity types.
2. Attribute
An attribute is a property or descriptor of an entity.
Type of attributes
Depending on the nature of the attribute, they are classified
into five types.
● Required and optional attributes
A required attribute is an attribute that must have a value in it, while
an optional attribute may not have a value in it and can be left
blank.
Example: in the entity employee, E_ID, Name,Age and salary
would be required attributes as they uniquely define that table and
we assume all employees have E_ID, Name, Age and salary.
● Key and non-key attribute
In every entity an attribute or group of attributes uniquely identifies
that entity.
Example: above in the table employee, there is only one unique
identities, E_ID, which is the primary key of the table. The rest of
the attributes are descriptions.
● Single and composite attributes
The composite attribute is an attribute that can be subdivided into
other single attributes with meanings of their own.
Example: in entity employee, the attribute name can be divided into
first name, can be divided into first name, last name, and middle
name. The rest of attributes are called single attributes since they
connote be subdivided into parts.
● single -valued and multi-valued attributes
The single-value attribute can only have one value, while the
multi-valued attributes usually can store multiple data in them.
For example, in the entity employee, Phno could be considered a
muti-value attribute since an employee could have multiple phone
numbers.
● Derived attributes
The value of derived attribute is calculated from the value of other
attribute.
For example, in employee table, age would be considered as a
derived attribute since it could be calculated using the employee’
date of birth.
3. Relationship
Relationship describe how entities are associated with each other.
The two entities would be the employee and the department and
the act of working would be the relationship among the entities.

2.Degree of relationship
The following are the different types of relationships depending on
their degree:
1. Unary relationship
2. Binary relationship
3. Ternary relationship

1. Unary relationship
These are relationships which involve a single entity. These
relationships are also called recursive relationships

2. Binary relationship
Binary relationship is formed when two entities participate in a
relationship

3. Ternary relationship
A ternary relationship is formed when three entities participate in a
relationship

3.Cardinality
Four types of cardinalities are generally discussed.
1. One to one
2. One to many
3. Many to one
4. Many to many
 1. One to one :
In this type of relationship, one instance is one entity set is related
to only one instance in other entity set. The relation is denoted as
1:1 or 1-1.

2. One to many:
Here, one instance in one entity set is related to more than one
instance in other entity set. The notation is 1:* or 1-*. In the given
example, one customer can place many orders and many orders
can be placed by one customer.

3. Many to one:
Here, many instances in one entity set are related to one instance
in other entity set. The notation is *:1 or *-1. In the given example,
many employees may belong to one department and one particular
employee can belong to one department only.
 4. Many to many :
In this type of relationship, many instances in one entity set are
related to many instances is other entity set. The notation is *:* or
*-*. In the given example, many students can enroll in many
courses.

ENHANCED E-R MODEL :-

Enhanced entity relationship model :- the model that has
resulted from extending the original E-R model with new modeling
constructs. The most important new modeling construct
incorporated in the E-R model is super type/subtype relationships.
This facility allows us to model a general entity type (called the
super type ) and then subdivide it into several specialized entity
types called SUB TYPES. Enhanced E-R diagram is used to
capture important business rules such as constraint in SUPER
TYPES,SUB TYPE relationships
Representing super types and subtypes :-
A sub type is a sub grouping of the entities in an entity type that is
meaningful to the organisation.
Example :-
Student is an entity type in a university two sub types of student are
graduate student and under graduate student. Here student as the
super type.
A super type is a generic entity type that has a relationship with one
or more subtypes
Basic notation for super type/sub type relationships:-
The basic notation that we use for super type / sub type
relationship is shown in fig.below. The super type is connected with
a line to a circle, which in turn is connected by a line to each
subtype that has been defined. The u shaped symbol on each line
connecting a subtype. It also indicates that direction of the
subtype/supertype relationship.
Attributes that are are shared by all entities are associated
with the supertype. Attributes that are unique to a particular are
associated with that subtype.
Attributes inheritance:-
A subtype is an entity type in its own right. An entity instance
of a subtype represents the same entity instance of the supe type
attributes inheritance is the property by which subtype entities
inherit values of all attributes of the super type. This important
property makes it unnecessary to include super type attributes
redundantly with the sub types.

Generalization, Specialization and

Aggregation in ER Model
Generalization, Specialization and Aggregation in ER model are
used for data abstraction in which abstraction mechanism is used to
hide details of a set of objects. 
Generalization – 
1.Generalization is the process of extracting common properties
from a set of entities and create a generalized entity from it.
2. It is a bottom-up approach in which two or more entities can be
generalized to a higher level entity if they have some attributes in
common.
Ex:

In CAR entity airbags are extra attributes.

In TRUCK entity cabin type, capacity are extra attributes remaining
all common attributes in both.
Generalization is a process of generalizing an entity which contains
generalized attributes or properties of generalized entities. The
entity that is created will contain the common features.
Generalization is a Bottom up process.
We can have three sub entities as Car, Truck, Motorcycle and these
three  entities can be generalized into one general super class as
Vehicle.
It is a form of abstraction that specifies two or more entities (sub
class) having common characters that can be generalized into one
single entity (super class) at higher level hiding all the differences.

Specialization – 
1.In specialization, an entity is divided into sub-entities based on
their characteristics.
2.It is a top-down approach where higher level entity is specialized
into two or more lower level entities.
 

Consider in employee entity daily wages attributes is for workers.

Monthly wages is for supervisors. So we can derive two sub entities from
employee with special attributes as follows
Aggregation in DBMS

Aggregation refers to the process by which entities are combined to

form a single meaningful entity. The specific entities are combined
because they do not make sense on their own. To establish a single
entity, aggregation creates a relationship that combines these
entities. The resulting entity makes sense because it enables the
system to function well.

Aggregation in DBMS can be explained using the entity-relationship

model (ER model). This is a conceptual diagram that represents the
structure of a database and its components. It contains the
relationships, attributes, and entities in a DBMS. This is similar to
the columns, rows, and tables in a database.

The following diagram shows a simple ER model that can be used

to explain the process flow for aggregation in DBMS.
Image Source: EDUCBA

In this ER model, A, B, and C represent entities. A and B should be

combined into a single complex entity. R1 is the relationship that is
formed after A and B are linked. R1 needs to form a relationship
with other entities for other DBMS operations to be successful.

This operation generates a new relationship (R2). R2 is linked to

another entity C to enhance its functionality. This entity is also
formed through aggregation.

Example of aggregation in DBMS

Let’s assume that there is a patient who has visited a doctor in the
hospital to seek treatment for a certain type of illness. The following
diagram shows the process flow for aggregation in the hospital.
Image Source: EDUCBA

We will follow the simple ER model described above. In the diagram

above, there are three entities: patient history, the doctor, and the
patient. Filing and diagnosis represent relationships. The doctor
performs a diagnosis on the patient.

The database stores data regarding this diagnosis and any other
patient data. Filing is required to make it easier for the doctor to
retrieve the patient’s information in the future.

In this example, the patient cannot work on his own. He has to form
a relationship with the doctor to get a diagnosis. The doctor also
cannot perform a diagnosis without the patient. In the future, the
doctor will need data about the patient’s history, that will require him
to collect it from a filing system.
The last entity (patient’s history) ensures that the entire system is
functional. Getting the patient’s history cannot be done without a
diagnosis from the doctor and a filing system.

ER Design Issues
In the previous sections of the data modeling, we learned
to design an ER diagram. We also discussed different ways
of defining entity sets and relationships among them. We
also understood the various designing shapes that
represent a relationship, an entity, and its attributes.
However, users often mislead the concept of the elements
and the design process of the ER diagram. Thus, it leads to
a complex structure of the ER diagram and certain issues
that does not meet the characteristics of the real-world
enterprise model.

Here, we will discuss the basic design issues of an ER

database schema in the following points:

1) Use of Entity Set vs Attributes

The use of an entity set or attribute depends on the

structure of the real-world enterprise that is being
modelled and the semantics associated with its attributes.
It leads to a mistake when the user use the primary key of
an entity set as an attribute of another entity set. Instead,
he should use the relationship to do so. Also, the primary
key attributes are implicit in the relationship set, but we
designate it in the relationship sets.

2) Use of Entity Set vs. Relationship Sets

It is difficult to examine if an object can be best expressed

by an entity set or relationship set. To understand and
determine the right use, the user need to designate a
relationship set for describing an action that occurs
in-between the entities. If there is a requirement of
representing the object as a relationship set, then its
better not to mix it with the entity set.

3) Use of Binary vs n-ary Relationship Sets

Generally, the relationships described in the databases are

binary relationships. However, non-binary relationships can
be represented by several binary relationships. For
example, we can create and represent a ternary
relationship 'parent' that may relate to a child, his father,
as well as his mother. Such relationship can also be
represented by two binary relationships i.e, mother and
father, that may relate to their child. Thus, it is possible to
represent a non-binary relationship by a set of distinct
binary relationships.

4) Placing Relationship Attributes

The cardinality ratios can become an affective measure in

the placement of the relationship attributes. So, it is better
to associate the attributes of one-to-one or one-to-many
relationship sets with any participating entity sets, instead
of any relationship set. The decision of placing the specified
attribute as a relationship or entity attribute should
possess the charactestics of the real world enterprise that
is being modelled.

For example, if there is an entity which can be

determined by the combination of participating entity sets,
instead of determing it as a separate entity. Such type of
attribute must be associated with the many-to-many
relationship sets.

Thus, it requires the overall knowledge of each part that is

involved inb desgining and modelling an ER diagram. The
basic requirement is to analyse the real-world enterprise
and the connectivity of one entity or attribute with other.
The Relational Model
● All data are represented as tables (relations)
● Tables are comprised of rows and columns (tuples)
● Rows are (officially) unordered (i.e., the order in which rows are
referenced does not matter)
● A proper relational table contains no duplicate rows.
● Each table has a primary key, a unique identifier constructed from
one or more columns
● Most primary keys are a single column (e.g., OWNERNUM for
OWNERS)
● A table is linked to another by including the other table's primary
key. Such an included column is called a foreign key

Features of a Good Relational Database Design

1.Reflects real-world structure with semantics of attributes:
It refers to the real-world entities and meaning of each individual
attribute that is design a relation so that it is to explain it’s meaning.
Ex: eid --- for employee id
Ename---- for employee name
2.Provides efficient access to data
data can be retrieved easily with simple queries.
3.Avoids redundant storage of data items:
Storage of same data in more than one location. that is same data
stored in multiple tables.
It leads to wastage of storage.ad insert, update and deletion
anomalies may occurs.(anomalies means abnormalities).
Ex: consider we are storing employee phno in two tables.
If we update the phno one table and if we don’t update in other table
then update anomalies may occurs.
4.Supports the maintenance of data integrity over time:
Once a relational database is designed it should maintain itegrated
data for longer time with same consistency.
 5.lesser null values in tuples:
some of the attributes of a table are not known we may enter null
values to that. Sometime this may lead to incorrect result .
ex: consider in employee table there is address column, which will
accept null values also.
EMPLOYEE

eid ename Address

1 X Hyderabad

2 Y Chennai

3 Z Bangalore

4 P null
If we try to get number of employees using address column..

Count(address)

It gives number of rows as 3,because it will not count the address

with

null value.

Physical Database Design Methodology for

Relational Database
We have established that there are three levels of database
design:
 · Conceptual design: producing a data model which accounts
for the relevant entities and relationships within the target
application domain;

· Logical design: ensuring, via normalization procedures and

the definition of integrity rules, that the stored database will be
non-redundant and properly connected;

· Physical design: specifying how database records are stored,

accessed and related to ensure adequate performance.

We can consider the topic of physical database design from

three aspects:

· What techniques for storing and finding data exist

· Which are implemented within a particular DBMS

· Which might be selected by the designer for a given

application knowing the properties of the data Thus the
purpose of physical database design is:

1. How to map the logical database design to a physical

database design.

2. How to design base relations for target DBMS.

3. How to design enterprise constraints for target DBMS.

4. How to select appropriate file organizations based on

analysis of transactions.

5. When to use secondary indexes to improve performance

6. How to estimate the size of the database

7. How to design user views.

8. How to design security mechanisms to satisfy user

requirements.

9. How to design procedures and triggers.

Physical database design is the process of producing a

description of the implementation of the database on
secondary storage

1. Translate logical data model for target DBMS

This phase is the translation of the global logical data model to

produce a relational database schema in the target DBMS.

This includes creating the data dictionary based on the logical

model and information gathered. After the creation of the data
dictionary, the next activity is to understand the functionality of
the target DBMS so that all necessary requirements are fulfilled
for the database intended to be developed.

Knowledge of the DBMS includes:

*how to create base relations

*whether the system supports:

1.definition of Primary key

2 .definition of Foreign key

3. Referential integrity constraints

4. definition of enterprise level constraints

2. Design physical representation

This phase is the level for determining the optimal file

organizations to store the base relations and the indexes that
are required to achieve acceptable performance; that is, the
way in which relations and tuples will be held on secondary
storage.

Number of factors that may be used to measure efficiency:

· Transaction throughput: number of transactions processed in

given time interval.

· Response time: elapsed time for completion of a single

transaction.

· Disk storage: amount of disk space required to store

database files.

3. Design user view

To design the user views that was identified during the

Requirements Collection and Analysis stage of the relational
database application development lifecycle.

Define views in DDL to provide user views identified in data

model Map onto objects in physical data model

4. Design security mechanisms

To design the security measures for the database as specified

by the users. System security – Authentication Data
security-authorizations
 5. Consider the Introduction of Controlled Redundancy

The objective here is to determine whether introducing

redundancy in a controlled manner by relaxing the
normalization rules will improve the performance of the
system.

This is sometimes known as denormalization Informally

speaking, denormalization is merging of relations Result of
normalization is a logical database design that is structurally
consistent and has minimal redundancy.

However, sometimes a normalized database design does not

provide maximum processing efficiency. It may be necessary
to accept the loss of some of the benefits of a fully normalized
design in favour of performance.