1Q

discuss the type of problem that encounter with transation that runs concurrently?
Ans: 1) The Lost Update Problem
2) The Temporary Update (or Dirty Read) Problem.
3) The Incorrect Summary Problem.
4) The Unrepeatable Read Problem.
1) The Lost Update Problem: This problem occurs when two transactions that access the
same database items have their operations interleaved in a way that makes the value of some
database items [Link] that transactions T1 and T2 are submitted at approximately
the same time, and suppose that their operations are interleaved as shown in Figure 21.3(a);
then the final value of item X is incorrect because T2 reads the value of X before T 1 changes
it in the database, and hence the updated value resulting from T1 is [Link] example,if X= 80
at the start (originally there were 80 reservations on the flight),N= 5 (T1 transfers 5 seat
reservations from the flight corresponding to X to the flight corresponding to Y), and M = 4
(T2 reserves 4 seats on X),the final result should be X = [Link],in the interleaving of
operations shown in Figure 21.3(a), it is X = 84 because the update in T1 that removed the
five seats from X was lost.

2) The Temporary Update (or Dirty Read) Problem. This problem occurs when one
transaction updates a database item and then the transaction fails for some
reason .Meanwhile, the updated item is accessed (read) by another transaction before it is
changed back to its original value. Figure 21.3(b) shows an example where T1 updates item
X and then fails before completion, so the system must change X back to its original value.
Before it can do so, however, transaction T2 reads the temporary value of X, which will not
be recorded permanently in the database because of the failure of [Link] value of item X that
is read by T2 is called dirty data because it has been created by a transaction that has not
completed and committed yet; hence, this problem is also known as the dirty read problem.
3) The Incorrect Summary Problem. If one transaction is calculating an aggregate summary
function on a number of database items while other transactions are updating some of these
items, the aggregate function may calculate some values before they are updated and others
after they are updated. For example, suppose that a transaction T3 is calculating the total
number of reservations on all the flights; meanwhile, transaction T1 is executing. If the
interleaving of operations shown in Figure 21.3(c) occurs, the result of T3 will be off by an
amount N because T3 reads the value of X after N seats have been subtracted from it but
reads the value of Y before those N seats have been added to it.

4) The Unrepeatable Read Problem. Another problem that may occur is called unrepeatable
read, where a transaction T reads the same item twice and the item is changed by another
transaction Tbetween the two reads. Hence, T receives different values for its two reads of the
same item. This may occur, for example, if during an airline reservation transaction, a
customer inquires about seat availability on several flights. When the customer decides on a
particular flight, the transaction then reads the number of seats on that flight a second time
before completing the reservation ,and it may end up reading a different value for the item.
2Q
List the problem that occur during concureency control and also explain with supporting
transaction diagram?
Ans: Several problems can occur when concurrent transactions execute in an uncontrolled
manner. We illustrate some of these problems by referring to a much simplified airline
reservations database in which a record is stored for each airline flight. Each record includes
the number of reserved seats on that flight as a named (uniquely identifiable) data item,
among other information. Figure 21.2(a) shows a transaction T1 that transfers N reservations
from one flight whose number of reserved seats is stored in the database item named X to
another flight whose number of reserved seats is stored in the database item named Y. Figure
21.2(b) shows a simpler transaction T2 that just reserves M seats on the first flight (X)
referenced in transaction T1.2

1) The Lost Update Problem. This problem occurs when two transactions that access the
same database items have their operations interleaved in a way that makes the value
of some database items [Link] that transactions T1 and T2 are submitted at
approximately the same time, and suppose that their operations are interleaved as
shown in Figure 21.3(a); then the final value of item X is incorrect because T2 reads
the value of X before T 1 changes it in the database, and hence the updated value
resulting from T1 is lost.

2) The Temporary Update (or Dirty Read) Problem. This problem occurs when one
transaction updates a database item and then the transaction fails for some
reason .Meanwhile, the updated item is accessed (read) by another transaction before
it is changed back to its original value. Figure 21.3(b) shows an example where T1
 updates item X and then fails before completion, so the system must change X back to
its original value

3) The Incorrect Summary Problem. If one transaction is calculating an aggregate

summary function on a number of database items while other transactions are
updating some of these items, the aggregate function may calculate some values
before they are updated and others after they are updated. For example, suppose that a
transaction T3 is calculating the total number of reservations on all the flights;
meanwhile, transaction T1 is executing.

3Q
Demonstrate the system log in database transaction?
Ans: The SystemLog Tobe abletorecoverfrom failures
thataffecttransactions,thesystemmaintainsa log tokeeptrackof all transaction operations that
affect the values of database items, as well as other transaction information that may be
needed to permit recovery from failures. The following are the types of entries—called log-
records—that are written to the log file andthe corresponding action for each log record. In
these entries, T refers to a unique transaction-id that is generated automatically by the system
for each transaction and that is used to identify each transaction
4Q
Demonstrate the transaction state and additional operation?
Ans: Transaction States and Additional Operations Atransactionis
anatomicunitofworkthatshouldeitherbecompletedinitsentiretyornotdoneat all. For recovery
purposes, the system needs to keep track of when each transaction starts, terminates, and
commits or aborts . Therefore, the recovery manager of the DBMS needs to keep track of the
following operations

Figure 21.4 shows a state transition diagram that illustrates how a transaction moves through
its
[Link],whereit
can execute its READ and WRITE operations. When the transaction ends, it moves to the
partially committed state. At this point, some recovery protocols need to ensure that a system
failure will not result in an inability to record the changes of the transaction permanently
(usually by recording changes in the system log). Once this check is successful, the
transaction is said to have reached its commit point and enters the committed state. When a
transaction is committed, it has concluded its execution successfully and all its changes must
be recorded permanently in the database, even if a system failure occurs.
However,atransactioncangotothefailedstateifoneofthechecksfailsorifthetransactionisaborted
during its active [Link] then have to be rolled back to undo theeffectof
itsWRITE operations on the database. The terminated state corresponds to the transaction
leaving the system.
Thetransactioninformationthatismaintainedinsystemtableswhilethetransactionhasbeenrunning
is removed when the transaction terminates. Failed or aborted transactions may be restarted
later— either automatically or after being resubmitted by the user—as brand new
transactions.
5Q
Explain the types of update anomalies wih example?
Ans: 3. Modification (Update) Anomaly

Definition: Inconsistent data due to redundant updates.

Example:

Student_ ID Student_ Name Course_ ID Course_ Name

101 Raj C101 DBMS

101 Raj C102 OS

102 Priya C101 DBMS

Problem:
● If the course name "DBMS" is changed to "Database Systems", we must
update multiple records.
● If we miss updating even one row (e.g., Priya’s record), the database
becomes inconsistent.
Why is it bad?
● Requires multiple updates for a single change.
● High risk of inconsistent data if updates are incomplete.
6Q
Write an algorithm to find the closure of functional dependency ‘F’?
Ans:the set of all dependencies that include F as well as all dependencies that can be inferred
from F is called the closure of F; it is denoted by F+.
Algorithm to Compute F⁺:
Input: A set of functional dependencies F
Output: The closure of F (F⁺)
1. Initialize F+=F
2. Repeat
o Apply Reflexivity Rule:

 For every attribute set X, add trivial

dependencies X→AX→A where A∈XA∈X.
o Apply Augmentation Rule:

 For each FD X→YX→Y in F+F+, and any attribute set Z,

add XZ→YZXZ→YZ.
o Apply Transitivity Rule:

 For any two FDs X→YX→Y and Y→ZY→Z, add X→ZX→Z.

o Apply Union Rule:

 If X→YX→Y and X→ZX→Z exist, add X→YZX→YZ.

o Apply Decomposition Rule:

 If X→YZX→YZ exists, split into X→YX→Y and X→ZX→Z.

o Apply Pseudotransitivity Rule:

 If X→YX→Y and WY→ZWY→Z exist, add WX→ZWX→Z.

Until no new FDs can be added to F+F+.
3. Return F+F+.

7Q
Explain insertion,deletion and modification anomalies .why are they consider bad? illustrate
with example ?
Ans:Anomalies occur in a database due to poor design, especially in unnormalized relations.
The three main types are:
1. Insertion Anomaly:
Occurs when certain data cannot be inserted into the database without the presence of other
data.
2. Deletion Anomaly:
Occurs when deletion of data unintentionally removes additional useful data.
3. Modification Anomaly:
Occurs when the same data is stored in multiple places, and updating it in one place but not
others leads to inconsistency.
Why are anomalies bad?
 Lead to data inconsistency
 Cause data redundancy
 Result in loss of information
 Make database maintenance difficult
Example Table: Student_Course

Student_ID Student_Name Course_Name Instructor

101 Ravi DBMS Dr. Kumar

102 Anita OS Dr. Sharma

103 Ravi OS Dr. Sharma

11Q
Define non additive join property of a decomposition and write an algorithm for testing of
non additive join property?
Ans:A decomposition of a relation is said to have Non-Additive Join Property (or Lossless
Join) if the natural join of the decomposed relations gives exactly the original relation
without any extra or missing tuples.
Algorithm for Testing Non-Additive Join Property
Input:
Relation R, decomposition R1,R2,…,Rn1 and set of functional dependencies (FDs).
Steps:
1. Create a table with one row for each relation Ri and one column for each attribute in .
2. Initialize:
o If attribute AA is in Ri, assign a unique symbol (like ai).
 o Otherwise, leave it blank.

3. Apply FDs:
o If X→Y holds and all attributes of X have the same symbol in a row, set Y to
the same symbol.
4. Check:
o If any row has all attributes with the same symbol, the decomposition is
lossless (non-additive).
o Else, it is lossy (additive).

8Q
With suitable example , explain properties of relational decompositions
Ans:Properties of Relational Decomposition
1. Lossless-Join Decomposition
 After decomposition, if we join the smaller relations, we must get back exactly the
original relation — no extra or missing tuples.
 Ensures no information is lost.
➔ Condition:

For decomposition of relation R into R1 and R2, the decomposition is lossless if:
(R1 ∩ R2) → (R1) or (R1 ∩ R2) → (R2) holds (using functional dependencies).
➔ Example:

Relation:
Employee(Emp_ID, Emp_Name, Dept_ID, Dept_Name)
Decompose into:
 Emp_Info(Emp_ID, Emp_Name, Dept_ID)
 Dept_Info(Dept_ID, Dept_Name)
Since Dept_ID is common and functionally determines Dept_Name, it is lossless.
2. Dependency Preservation
 After decomposition, all functional dependencies must be preserved in the individual
relations without requiring joins.
 Reduces the need to join tables to enforce constraints.
➔ Example:

Functional Dependencies:
  Emp_ID → Emp_Name, Dept_ID
 Dept_ID → Dept_Name
Decomposing Employee into Emp_Info and Dept_Info preserves these dependencies
separately.
Thus, checking constraints becomes easy without joining the tables.

3. Redundancy Control (No Redundant Data)

 Decomposition should minimize duplication of data across relations.
 Helps avoid update, insertion, and deletion anomalies.
➔ Example:

If in the above Employee relation, Department details were repeated with every employee,
redundancy would occur.
After decomposition, department data is stored once in Dept_Info.
4. Reconstruction of Information
 It must be possible to reconstruct the original relation from the decomposed relations
using natural join.
If decomposition is lossless, reconstruction is always possible without loss of data or wrong
data.
9Q
Define Transaction. Explain Read, Write operations of a transaction.
Ans:Transaction: The execution of a program that access or changes the contents of the
database is called a Transaction. The data access operations that a transaction can include are,
Read_item(X): Reads a database item named X into a program variable. Executing the
Read_item(X) command includes the following steps . 1. Find the address of the disk block
that contains item X. 2. Copy the disk block into a buffer in main memory ( if that disk block
is not already in some main memory buffer ) 3. Copy item X from the buffer to the program
variable named X. Write_item(X): Writes the value of the program variable X into the
database item named X . Executing aWrite_item(X) command includes the following steps 1.
Find the address of the disk block that contains item X 2. Copy that disk block into a buffer in
main memory ( if that disk block is not already in some main memory buffer ) Page 1 3.
Copy the item X from program variable named X into its correct locat

ion in the buffer 4. Store the updated block from the buffer block to disk. Usually the decision
about when to store back a modified disk block that is in a main memory buffer is handled by
the recovery manager or the operating system. Need of concurrency control
10Q
Explain why recovery is needed. OR Explain the different types of transaction failures.
Ans:Transaction failures: Whenever the transaction is submitted to DBMS for execution the
system is responsible for making sure that either (a) all operations in transaction are
completed successfully and their effect is recorded permanently in the database or (b) the
transaction has no effect on the database or on any other transactions. Page 3 The DBMS
must not permit some operations of transaction T to be applied to the database while other
operations of T are not. This may happen if transaction fails after executing some of its
operations but before executing all of them. Types of Failures: Transaction fail in the middle
of the execution for many reasons as explained below. 1. Computer failure (System crash): A
hardware or software error occurs in the computer system during transaction execution. If the
hardware crashes the contents of the computer internal memory may be lost. 2. A transaction
or system error: Some operation in the transaction may cause it to fail, such as integer
overflow or division by zero. Transaction failure may also occur because of logical
programming error. In addition the user may interrupt the transaction during its execution. 3.
Local error or exception conditions detected by the transaction: During transaction execution
certain condition may occur that necessitate the cancellation of the transaction. For example,
data for the transaction may not be found. 4. Concurrency control enforcement : The
concurrency control method may decide to abort the transaction, to restarted later( due to
deadlock ) 5. Disk failure: Some disk blocks may lose their data because of a read or write
malfunction or because of disk read/write head crash. 6. Physical problem and catastrophes:
This refers to an endless list of problems that includes power failure, fire, theft, overwriting
disk or tape by mistake etc. Failures of types 1, 2, 3, and 4 are more common than those of
types 5 or 6. Whenever a failure of type 1 through 4 occurs, the system must keep sufficient
information to quickly recover from the failure. Disk failure or other catastrophic failures of
type 5 or 6 do not happen frequently; if they do occur, recovery is a major task. 4) Explain the
operations recovery manager keeps track of for recovery purpose. For recovery purpose, the
system needs to keep track of when the transaction starts, terminates, and commits or aborts.
Hence the recovery manager keeps track of the following operations. •
BEGIN_TRANSACTION: This marks the beginning of the transaction execution. • READ or
WRITE: These specify read or write operation on the database items that are executed as a
part of transaction. Page 4 • END_TRANSACTION: This specifies READ and WRITE
transaction operations have ended and marks the end limit of transaction execution. At this
point it may be necessary to check whether the changes introduced by the transaction can be
permanently applied to the database (committed) or whether the transaction has to be aborted
because it violates concurrency control or for some other reason. •
COMMIT_TRANSACTION: This signals the successful end of the transaction so that any
changes executed by the transaction can be safely committed to the database and will not be
undone. • ROLLBACK (or ABORT):This indicates that the transaction hasended
unsuccessfully, so that any changes or effects that the transaction may have applied to the
database must be undone.