Atomicity?????

Everything happens or nothing happens. No partially done transactions. Correct data must be in the
database. If everything happens commit or if not abort the action and rollback.

Consistency???

In database systems, consistency refers to the requirement that any given database transaction must
change affected data only in allowed ways.

Isolation????

Database isolation refers to the ability of a database to allow a transaction to execute as if there are no
other concurrently running transactions (even though in reality there can be a large number of
concurrently running transactions).

Durability????

Durability ensures that changes made to the database (transactions) that are successfully committed will
survive permanently, even in the case of system failures. This ensures that the data within the database
will not be corrupted by: Service outages. Crashes. Other cases of failure.

What is a Schedule????

A list of actions(reads, writes, commits, aborts) from a set of transactions and the order of actions in the
schedule is the same as the order of actions in transactions.

Schedule types

• A complete schedule is a schedule which contains either an abort or commit for each transaction
whose actions are listed. A complete schedule contains all actions of transactions that appears in
it.

• A serial schedule occurs when the actions of different transactions are not interleaved-
transactions are executed one after the other.

Serial schedule= a schedule that does not interleave the actions of different transactions

Serializable schedule(Non serializable schedule)=In serializable schedules things happened parallel and
it uses serial schedules as well. Advantage, can do things parallel.

Serializability

• Multiple transactions executed concurrently must be equivalent to some serial execution of the
transactions in order to preserve consistency.

• Order of transactions within schedule does not matter.

Two actions from multiple transactions conflict if they act on the same data and at least one of them is a
write action.Conflicting actions:

1. Write-read conflict

2. Read-write conflict

3. Write-write conflict

Write-Read Conflict(Dirty reads)

A dirty read occurs when a transaction reads data that has not yet been committed. For example,
suppose transaction 1 updates a row. Transaction 2 reads the updated row before transaction 1 commits
the update. Dirty reads means reading uncommitted data. It’s a conflict.

Here, the transaction T2 is reading the data

which is written by the T1 before T2 commits. It is
also called as Dirty Read.

What is Read-Write (RW) conflict? Transaction T2 is Writing data which

is previously read by transaction T1.
Here if you look at the diagram above,
data read by transaction T1 before
and after T2 commits is different.

What is Write-Write (WW) conflict?

 Here Transaction T2 is writing data which is
already written by other transaction T1. T2
overwrites the data written by T1. It is also
called as a blind write operation.
Data written by T1 has vanished. So it is data
update loss.

WRITE-WRITE CONFLICT(WW)

Lock based concurrency protocol

It is a mechanism in which a transaction cannot read or write data unless the appropriate lock is
acquired. This helps in eliminating the concurrency problem by locking a particular transaction to a
particular user. The lock is a variable that denotes those operations that can be executed on the
particular data item.

Strict 2PL (Two-Phase Locking)

is a concurrency control protocol used in database management systems (DBMS) to ensure the
serializability of transactions. It is a conservative protocol that guarantees conflict serializability, meaning
that the final state of the database is equivalent to some serial execution of the transactions.

In Strict 2PL, a transaction is divided into two phases: the growing phase and the shrinking phase.

1. Growing Phase:

1. During the growing phase, a transaction can acquire locks on data items but cannot
release any locks.

2. When a transaction needs to read or write a data item, it requests a shared (read) lock
or an exclusive (write) lock, respectively.

3. If a lock is not available, the transaction must wait until it can acquire the required lock.

4. Once a lock is acquired, the transaction can access the data item.

2. Shrinking Phase:

• During the shrinking phase, a transaction can release locks but cannot acquire any new locks.
 • After a transaction has finished accessing a data item, it releases the associated lock, allowing
other transactions to acquire it.

Strict two phase locking(Strict 2PL)Protocol

Strict 2PL is the most restrictive form of 2PL. In strict 2PL, a transaction is not allowed to release any
locks until it has reached the commit point. This means that a transaction will hold all of its locks until it
has completed its execution and is ready to be committed

Each XACT must obtain a S(shared)lock on object before reading and an X(exclusive)lock on object
before writing. All locks held by transaction are released after transaction completes.

Non Strict 2PL

The rules for 2PL are similar to those of Strict 2PL:

1. If a transaction T wants to read/write an object, it must request a

shared/exclusive lock on the object.

2. A transaction can not request additional locks on the object once it releases any lock, and it can
release locks at any time. (not only at commit times like as in Strict 2PL).

Query processing

is the process of translating a high-level query, such as SQL, into a low-level query that can be executed
by the database system.

• What happens to a query in a database?

1. Parsing and translation

2. Optimizing

3. Executing plan and output

Equivalence Rule

To transform a relational algebra expression into an equivalent efficient query expression, you can apply
various optimization techniques and rewrite the expression using SQL query constructs.

Now we will learn cascade of select examples using relational algebra notation with the sigma (σ)
operator:
 1. Basic Selection: Select all tuples from the "Employees" relation where the salary is
greater than 50000.
Answer=σ(Salary > 50000)(Employees)

2. Compound Selection: Select all tuples from the "Students" relation where the age is greater than or
equal to 18 and the grade is 'A’.

• Answer= σ(Age >= 18 ∧ Grade = 'A')(Students)

Note ∧ is AND operator.

3. Negation in Selection: Seelct all tuples from the "Products" relation where the category is not
'Electronics' and the price is not less than 100.

Answer= σ(¬(Category = 'Electronics' ∨ Price < 100))(Products)

Note : ¬ is NOT Operator

4. Selection with Projection: Select the "CustomerName" attribute from the "Customers" relation for
tuples where the country is 'USA’.

Answer= π(CustomerName)(σ(Country = 'USA')(Customers))

5. Selection with Join: Select the "OrderID" from the "Orders" relation and the "CustomerName" from
the "Customers" relation for tuples where the CustomerID matches and the ShippedDate is null.

Answer= π(Orders.OrderID, Customers.CustomerName)(σ(Orders.CustomerID = Customers.CustomerID ∧

Orders.ShippedDate IS NULL)(Orders ⨝ Customers))

6. Selection with Union: Select all tuples from the "Products" relation where the category is either
'Electronics' or 'Furniture' using union operation

Answer= σ(Category = 'Electronics')(Products) ∪ σ(Category = 'Furniture')(Products)

Equivalence rules states that two expressions are equal

It does not state, which one is better
Input output Cost- measurement

DISK I/O cost= read/write measure

There is a S table with 100 pages while another table sp table contains 10,000 pages

We will discuss 2 plans to choose the efficient one…

Plan 1

• Reading Cost: 10 * 10,000 = 100,000 (in both plans)

• Writing Cost: In Plan 1: 10 * 10,000 = 100,000 In Plan 2: 0 (no additional writing cost)

• Therefore, the disk I/O cost for each plan can be summarized as follows:

• Plan 1: Reading Cost: 100,000 Writing Cost: 100,000 Total Disk I/O Cost: 200,000

Plan 2

Reading Cost: 100,000 Writing Cost: 0 Total Disk I/O Cost: 100,000

• In Plan 1, both the reading and writing costs are incurred, resulting in a total disk I/O cost of
200,000. On the other hand, in Plan 2, only the reading cost is incurred, resulting in a lower total
disk I/O cost of 100,000.

So the efficient plan is Plan 2

Example 2: We have a reading cost of 10,000 and an additional I/O cost of 100.

The reading cost of 10,000 typically represents the number of input/output operations required to read
a certain amount of data from a disk or storage medium. It can be measured in terms of the number of
disk I/O operations performed.

In addition to the reading cost, there is an extra I/O cost of 100 mentioned. This could refer to any
additional disk I/O operations required for tasks such as writing metadata, updating indexes, or other
overhead operations related to the reading process.

Answer: Therefore, the total I/O cost in this scenario is calculated by summing up the reading cost and
the additional I/O cost: 10,000 (reading) + 100 (I/O) = 10,100 I/O operations.

Enumeration of Alternative Plans

• There are two main cases:

• Single-relation plans

• Multiple-relation plans

• For queries over a single relation, queries consist of a combination of selects, projects, and
aggregate ops:
• Each available access path (file scan / index) is considered, and the one with the least
estimated cost is chosen.