dbms | Gate Vidyalay

Tag: dbms

Serializability in DBMS | Conflict Serializability

Database Management System

Schedules in DBMS-

Before you go through this article, make sure that you have gone through the previous article on Schedules in DBMS.

We have discussed-

A schedule is the order in which the operations of multiple transactions appear for execution.
Serial schedules are always consistent.
Non-serial schedules are not always consistent.

In DBMS, schedules may be classified as-

In this article, we will discuss about Serializability in DBMS.

Serializability in DBMS-

Some non-serial schedules may lead to inconsistency of the database.
Serializability is a concept that helps to identify which non-serial schedules are correct and will maintain the consistency of the database.

Serializable Schedules-

If a given non-serial schedule of ‘n’ transactions is equivalent to some serial schedule of ‘n’ transactions, then it is called as a serializable schedule.

Characteristics-

Serializable schedules behave exactly same as serial schedules.

Thus, serializable schedules are always-

Consistent
Recoverable
Casacadeless
Strict

Serial Schedules Vs Serializable Schedules-

Serial Schedules	Serializable Schedules
No concurrency is allowed. Thus, all the transactions necessarily execute serially one after the other.	Concurrency is allowed. Thus, multiple transactions can execute concurrently.
Serial schedules lead to less resource utilization and CPU throughput.	Serializable schedules improve both resource utilization and CPU throughput.
Serial Schedules are less efficient as compared to serializable schedules. (due to above reason)	Serializable Schedules are always better than serial schedules. (due to above reason)

Types of Serializability-

Serializability is mainly of two types-

Conflict Serializability
View Serializability

In this article, we will discuss about Conflict Serializability.

Learn about View Serializability.

Conflict Serializability-

If a given non-serial schedule can be converted into a serial schedule by swapping its non-conflicting operations, then it is called as a conflict serializable schedule.

Conflicting Operations-

Two operations are called as conflicting operations if all the following conditions hold true for them-

Both the operations belong to different transactions
Both the operations are on the same data item
At least one of the two operations is a write operation

Example-

Consider the following schedule-

In this schedule,

W1 (A) and R2 (A) are called as conflicting operations.
This is because all the above conditions hold true for them.

Checking Whether a Schedule is Conflict Serializable Or Not-

Follow the following steps to check whether a given non-serial schedule is conflict serializable or not-

Step-01:

Find and list all the conflicting operations.

Step-02:

Start creating a precedence graph by drawing one node for each transaction.

Step-03:

Draw an edge for each conflict pair such that if X_i (V) and Y_j (V) forms a conflict pair then draw an edge from T_i to T_j.
This ensures that T_i gets executed before T_j.

Step-04:

Check if there is any cycle formed in the graph.
If there is no cycle found, then the schedule is conflict serializable otherwise not.

NOTE-

By performing the Topological Sort of the Directed Acyclic Graph so obtained, the corresponding serial schedule(s) can be found.
Such schedules can be more than 1.

Next Article- Practice Problems On Conflict Serializability

Get more notes and other study material of Database Management System (DBMS).

Watch video lectures by visiting our YouTube channel LearnVidFun.

Participation Constraints | DBMS

Database Management System

Participation Constraints-

Before you go through this article, make sure that you have gone through the previous article on Introduction to ER Diagrams.

Participation constraints define the least number of relationship instances in which an entity must compulsorily participate.

Types of Participation Constraints-

There are two types of participation constraints-

Total participation
Partial participation

1. Total Participation-

It specifies that each entity in the entity set must compulsorily participate in at least one relationship instance in that relationship set.
That is why, it is also called as mandatory participation.
Total participation is represented using a double line between the entity set and relationship set.

Example-

Here,

Double line between the entity set “Student” and relationship set “Enrolled in” signifies total participation.
It specifies that each student must be enrolled in at least one course.

Also read- Relationship Sets in DBMS and Entity Sets in DBMS

2. Partial Participation-

It specifies that each entity in the entity set may or may not participate in the relationship instance in that relationship set.
That is why, it is also called as optional participation.
Partial participation is represented using a single line between the entity set and relationship set.

Example-

Here,

Single line between the entity set “Course” and relationship set “Enrolled in” signifies partial participation.
It specifies that there might exist some courses for which no enrollments are made.

Relationship between Cardinality and Participation Constraints-

Minimum cardinality tells whether the participation is partial or total.

If minimum cardinality = 0, then it signifies partial participation.
If minimum cardinality = 1, then it signifies total participation.

Maximum cardinality tells the maximum number of entities that participates in a relationship set.

Next Article- Types of Attributes in DBMS

Get more notes and other study material of Database Management System (DBMS).

Watch video lectures by visiting our YouTube channel LearnVidFun.

Relationship Sets | DBMS

Database Management System

Relationship in DBMS-

Before you go through this article, make sure that you have gone through the previous article on Introduction to ER Diagrams.

A relationship is defined as an association among several entities.

Example-

‘Enrolled in’ is a relationship that exists between entities Student and Course.

Also read- Entity Sets in DBMS

Relationship Set-

A relationship set is a set of relationships of same type.

Example-

Set representation of above ER diagram is-

Degree of a Relationship Set-

The number of entity sets that participate in a relationship set is termed as the degree of that relationship set. Thus,

Degree of a relationship set = Number of entity sets participating in a relationship set

Types of Relationship Sets-

On the basis of degree of a relationship set, a relationship set can be classified into the following types-

Unary relationship set
Binary relationship set
Ternary relationship set
N-ary relationship set

1. Unary Relationship Set-

Unary relationship set is a relationship set where only one entity set participates in a relationship set.

Example-

One person is married to only one person

2. Binary Relationship Set-

Binary relationship set is a relationship set where two entity sets participate in a relationship set.

Example-

Student is enrolled in a Course

3. Ternary Relationship Set-

Ternary relationship set is a relationship set where three entity sets participate in a relationship set.

Example-

4. N-ary Relationship Set-

N-ary relationship set is a relationship set where ‘n’ entity sets participate in a relationship set.

Next Article- Cardinality in ER Diagram

Get more notes and other study material of Database Management System (DBMS).

Watch video lectures by visiting our YouTube channel LearnVidFun.

Cardinality in ER Diagram | DBMS

Database Management System

Cardinality Constraint-

Before you go through this article, make sure that you have gone through the previous article on Introduction to ER Diagrams.

Cardinality constraint defines the maximum number of relationship instances in which an entity can participate.

Types of Cardinality Ratios-

There are 4 types of cardinality ratios-

Many-to-Many cardinality (m:n)
Many-to-One cardinality (m:1)
One-to-Many cardinality (1:n)
One-to-One cardinality (1:1 )

Also read- Relationship Sets in DBMS and Entity Sets in DBMS

1. Many-to-Many Cardinality-

By this cardinality constraint,

An entity in set A can be associated with any number (zero or more) of entities in set B.
An entity in set B can be associated with any number (zero or more) of entities in set A.

Symbol Used-

Example-

Consider the following ER diagram-

Here,

One student can enroll in any number (zero or more) of courses.
One course can be enrolled by any number (zero or more) of students.

2. Many-to-One Cardinality-

By this cardinality constraint,

An entity in set A can be associated with at most one entity in set B.
An entity in set B can be associated with any number (zero or more) of entities in set A.

Symbol Used-

Example-

Consider the following ER diagram-

Here,

One student can enroll in at most one course.
One course can be enrolled by any number (zero or more) of students.

3. One-to-Many Cardinality-

By this cardinality constraint,

An entity in set A can be associated with any number (zero or more) of entities in set B.
An entity in set B can be associated with at most one entity in set A.

Symbol Used-

Example-

Consider the following ER diagram-

Here,

One student can enroll in any number (zero or more) of courses.
One course can be enrolled by at most one student.

4. One-to-One Cardinality-

By this cardinality constraint,

An entity in set A can be associated with at most one entity in set B.
An entity in set B can be associated with at most one entity in set A.

Symbol Used-

Example-

Consider the following ER diagram-

Here,

One student can enroll in at most one course.
One course can be enrolled by at most one student.

Next Article- Participation Constraints

Get more notes and other study material of Database Management System (DBMS).

Watch video lectures by visiting our YouTube channel LearnVidFun.

ER Diagrams | ER Diagram Symbols

Database Management System

ER diagram-

ER diagram or Entity Relationship diagram is a conceptual model that gives the graphical representation of the logical structure of the database.
It shows all the constraints and relationships that exist among the different components.

Components of ER diagram-

An ER diagram is mainly composed of following three components-

Entity Sets
Attributes
Relationship Set

Example-

Consider the following Student table-

Roll_no	Name	Age
1	Akshay	20
2	Rahul	19
3	Pooja	20
4	Aarti	19

This complete table is referred to as “Student Entity Set” and each row represents an “entity”.

Representation as ER Diagram-

The above table may be represented as ER diagram as-

Here,

Roll_no is a primary key that can identify each entity uniquely.
Thus, by using student’s roll number, a student can be identified uniquely.

ER Diagram Symbols-

An ER diagram is composed of several components and each component in ER diagram is represented using a specific symbol.

ER diagram symbols are discussed below-

1. For Entity Sets-

An entity set is a set of same type of entities.

An entity refers to any object having-

Either a physical existence such as a particular person, office, house or car.
Or a conceptual existence such as a school or a company.

An entity set may be of the following two types-

Strong entity set
Weak entity set

1. Strong Entity Set-

A strong entity set possess its own primary key.
It is represented using a single rectangle.

2. Weak Entity Set-

A weak entity set do not possess its own primary key.
It is represented using a double rectangle.

Read more- Entity Sets in DBMS

2. For Relationship Sets-

Relationship defines an association among several entities.
A relationship set is a set of same type of relationships.

A relationship set may be of the following two types-

Strong relationship set
Weak relationship set

1. Strong Relationship Set-

A strong relationship exists between two strong entity sets.
It is represented using a diamond symbol.

2. Weak Relationship Set-

A weak or identifying relationship exists between the strong and weak entity set.
It is represented using a double diamond symbol.

Read more- Relationship Sets in DBMS

3. For Attributes-

Attributes are the properties which describes the entities of an entity set.
There are several types of attributes.

Read more- Attributes in ER Diagram

4. For Participation Constraints-

Participation constraint defines the least number of relationship instances in which an entity has to necessarily participate.

There are two types of participation constraints-

Partial participation
Total participation

1. Partial Participation-

Partial participation is represented using a single line between the entity set and relationship set.

2. Total Participation-

Total participation is represented using a double line between the entity set and relationship set.

5. For Specialization and Generalization-

Generalization is a process of forming a generalized super class by extracting the common characteristics from two or more classes.
Specialization is a reverse process of generalization where a super class is divided into sub classes by assigning the specific characteristics of sub classes to them.