DBMS Notes

mobiprep (6).png

Database Management System

mobiprep (6).png

Data Modeling

mobiprep (6).png

Database Architecture

mobiprep (6).png

Relational Model

mobiprep (6).png

Relational Algebra

mobiprep (6).png

Entity Relationship Model

mobiprep (6).png

Functional Dependencies

mobiprep (6).png

Normalization

mobiprep (6).png

Transaction And Concurrency Control

mobiprep (6).png

Deadlock

mobiprep (6).png

Files and Storage

mobiprep (6).png

Indexing

Heading

Q

1

What do you understand by concurrent execution?

LRM_EXPORT_207556595493866_20190724_1939

Ans

Concurrent execution of database transactions in a multi-user system is where any number of users can use the same database at the same time.It may lead to inconsistent data.To prevent it Concurrency control is needed in order to avoid inconsistencies in the database.

LRM_EXPORT_207556595493866_20190724_1939

Q

2

Define Serializability.

LRM_EXPORT_207556595493866_20190724_1939

Ans

Serializability is the classical concurrency scheme. It ensures that a schedule for executing concurrent transactions is equivalent to one that executes the transactions serially in some order. It assumes that all accesses to the database are done using read and write operations

LRM_EXPORT_207556595493866_20190724_1939

Q

3

What is the basic assumption in serializability?

LRM_EXPORT_207556595493866_20190724_1939

Ans

Basic assumption in seializability is that each transaction preserves database consistency. Thus serial execution of a set of transactions preserves database consistency.

LRM_EXPORT_207556595493866_20190724_1939

Q

4

Explain conflict and view serializability?

LRM_EXPORT_207556595493866_20190724_1939

Ans

Conflict serializability: We say that a schedule S is conflict serializable if it is conflict equivalent to a serial schedule. If a schedule S can be transformed into a schedule S´ by a series of swaps of non conflicting instructions, we say that S and S´ are conflict equivalent. That is the order of each pair of conflicting operations in S and S` is the same.

view serializability: A schedule S is view serializable if it is view equivalent to a serial schedule. Let S and S´ be two schedules with the same set of transactions. S and S´ are view equivalent if the following three conditions are met, for each data item Q,
1. If in schedule S, transaction Ti reads the initial value of Q, then in schedule S’ also transaction Ti must read the initial value of Q.
2. If in schedule S transaction Ti executes read(Q), and that value was produced by transaction Tj (if any), then in schedule S’ also transaction Ti must read the value of Q that was produced by the same write(Q) operation of transaction Tj .
3. The transaction (if any) that performs the final write(Q) operation in schedule S must also perform the final write(Q) operation in schedule S’. As can be seen, view Every conflict serializable schedule is also view serializable

LRM_EXPORT_207556595493866_20190724_1939

Q

5

Explain recoverable schedule?

LRM_EXPORT_207556595493866_20190724_1939

Ans

if a transaction Tj reads a data item previously written by a transaction Ti , then the commit operation of Ti appears before the commit operation of Tj . The following schedule is not recoverable if T9 commits immediately after the read . If T8 should abort, T9 would have read (and possibly shown to the user) an inconsistent database state. Hence, database must ensure that schedules are recoverable.

LRM_EXPORT_207556595493866_20190724_1939

Q

6

Define Transaction.

LRM_EXPORT_207556595493866_20190724_1939

Ans

A transaction is a unit of program execution that accesses and updates various data items.

LRM_EXPORT_207556595493866_20190724_1939

Q

7

What are the ACID Properties?

LRM_EXPORT_207556595493866_20190724_1939

Ans

In ACID A stands for Atomicity, C stands for Consistency, I stands for Isolation and D stands for Durability .To preserve the integrity of data the database system must ensure:
1. Atomicity: Either all operations of the transaction are properly reflected in the database or none are.
2. Consistency: Execution of a transaction in isolation preserves the consistency of the database.
3.Isolation: Although multiple transactions may execute concurrently, each transaction must be unaware of other concurrently executing transactions. Intermediate transaction results must be hidden from other concurrently executed transactions. That is, for every pair of transactions Ti and Tj , it appears to Ti that either Tj , finished execution before Ti started, or Tj started execution after Ti finished.
4. Durability:After a transaction completes successfully, the changes it has made to the database persist, even if there are system failures.

LRM_EXPORT_207556595493866_20190724_1939

Q

8

Define Schedule.

LRM_EXPORT_207556595493866_20190724_1939

Ans

Schedule is nothing but a sequences of instructions that specify the chronological order in which instructions of concurrent transactions are executed.

LRM_EXPORT_207556595493866_20190724_1939

Q

9

What are the types of Schedules based on Recoverability?

LRM_EXPORT_207556595493866_20190724_1939

Ans

A)Recoverable Schedule –
A schedule is said to be recoverable if it is recoverable as name suggest. Only reads are allowed before write operation on same data. Only reads (Ti->Tj) is permissible.
Example – S1: R1(x), W1(x), R2(x), R1(y), R2(y),
W2(x), W1(y), C1, C2;
Given schedule follows order of Ti->Tj => C1->C2. Transaction T1 is executed before T2 hence there is no chances of conflict occur. R1(x) appears before W1(x) and transaction T1 is committed before T2 i.e. completion of first transaction performed first update on data item x, hence given schedule is recoverable.
B) Cascadeless Schedule –
When no read or write-write occurs before execution of transaction then corresponding schedule is called cascadeless schedule.
Example – S3: R1(x), R2(z), R3(x), R1(z), R2(y), R3(y),
W1(x), C1, W2(z), W3(y), W2(y), C3, C2;
In this schedule W3(y) and W2(y) overwrite conflicts and there is no read, therefore given schedule is cascadeless schedule.
C) Strict Schedule – if schedule contains no read or write before commit then it is known as strict schedule. Strict schedule is strict in nature.
Example – S4: R1(x), R2(x), R1(z), R3(x), R3(y),
W1(x), C1, W3(y), C3, R2(y), W2(z), W2(y), C2;
In this schedule no read-write or write write conflict arises before commit hence its strict schedule.

LRM_EXPORT_207556595493866_20190724_1939

Q

10

Define Concurrency Control.

LRM_EXPORT_207556595493866_20190724_1939

Ans

Cocurrency control means the caution which serializes transaction and prevents updating same record at same time by more than one user.

LRM_EXPORT_207556595493866_20190724_1939