Database Management System

Consider the following sequence of two transactions on a bank account(A) with initial balance 20,000 that transfers 5,000 to another account (B) and then applies 10% interest.

(i) T1 start
(ii) T1 A old=20000 new=15,000
(iii) T1 B old=12000 new=17000
(iv) T1 commit
(v) T2 start
(vi) T2 A old=15000 new=16500
(vii) T2 commit

Suppose the database system crashes just before log record (vii) is written. When the system is restarted, which one statement is true of the recovery process?

• We can apply redo and undo operations in arbitrary order because they are idempotent.

• We must redo log record (vi) to set A to 16,500.

• We must undo log record (vi) to set A to 16,500 and then redo log records (ii) and (iii).

• We need not redo records (ii) and (iii) because transaction T1 has committed.

Answer explanation

When the system crashes before transaction T2 commits (at log record vii), we have to ensure that any operation done by T2 that was not yet committed must be undone. Therefore, we need to undo the changes made by T2 (log record vi), which would restore account A to 15,000.

Afterward, we need to redo the operations of T1 (log records ii and iii) to ensure that the transfer of 5,000 from account A to account B is preserved and the changes are reflected in the database correctly.

Which of the following scenarios may lead to an irrecoverable error in a database system?

• A transaction writes a data item after it is read by an uncommitted transaction.

• A transaction reads a data item after it is read by an uncommitted transaction.

• A transaction reads a data item after it is written by a committed transaction.

• A transaction reads a data item after it is written by an uncommitted transaction.

A transaction may be in one of the following states during its execution life cycle in a concurrent execution environment: A. FAILED B. TERMINATED C. PARTIALLY COMMITTED D. COMMITTED E. ACTIVE Given a transaction in the active state during its execution, find its next transitioned state from the options given below:

• A only

• C only

• D only

• Either A or C only

Which of the DBMS components ensures that concurrent execution of multiple operations on the database results into a consistent database state?

• Buffer manager

• File manager

• Logs

• Transaction processing system

Answer explanation

The Transaction Processing System (TPS) is responsible for managing database transactions. It ensures that multiple transactions are executed in such a way that the database maintains its consistency, even in the presence of concurrent access.

TPS enforces the ACID properties (Atomicity, Consistency, Isolation, Durability) which guarantee that concurrent transactions do not lead to an inconsistent database state.

In the context of concurrency control, a given pair of operations in a schedule is called a conflict schedule if: (A) At least one of the operations is a write operation.

(B) Both the operations are performed on the same data item.

(C) Both the operations are performed by different transactions.

(D) Both the operations are performed on different data items.

• (A), (B) and (C) only

• (A), (B), (C) and (D)

• (A) and (C) only

• (B) and (D) only

Answer explanation

A conflict schedule involves operations that meet three conditions:

• At least one operation must be a write operation (A).

• Both operations must act on the same data item (B).

• Both operations must come from different transactions (C).

_________ SQL command changes one or more fields in a record.

• LOOK-UP

• INSERT

• MODIFY

• CHANGE

___________ command is used to remove a relation from an SQL database.

• Update table

• Remove table

• Delete table

• Drop table

An attribute A of data type varchar(20) has the value 'xyz' and attribute B of data type char(20) has the value "Imnop", then the attribute A has spaces and attribute B has spaces.

• 20, 20

• 3, 20

• 3, 5

• 20, 5

Answer explanation

Attribute A of data type varchar(20) has the value 'xyz', which consists of 3 characters. Since varchar only stores the actual number of characters, the length is 3 characters and no additional padding is added. Thus, the number of characters in A is 3.

Attribute B of data type char(20) has the value "Imnop", which consists of 5 characters. Since char is a fixed-length string, it pads the remaining positions with spaces to meet the defined length of 20 characters. Therefore, the length is 20 characters, with the remaining 15 positions filled with spaces.

Given the following STUDENT-COURSE schema: STUDENT (Rollno, Name, Courseno) COURSE (Courseno, Coursename, Capacity)

where:

Rollno is the primary key of STUDENT

Courseno is the primary key of COURSE

Coursename of COURSE contains unique values

The number of records: STUDENT table = 5

COURSE table = 3

The following relational algebra query is executed: R = STUDENT × COURSE

Match List I with List II:

List I

A. Degree of table R

B. Cardinality of table R

C. Foreign key of relation STUDENT

D. Foreign key of relation COURSE

List II

I. 15

II. NIL

III. 6

IV. Courseno

• A-I, B-III, C-IV, D-II

• A-I, B-III, C-IV, D-II

• A-I, B-III, C-II, D-IV

• A-III, B-I, C-IV, D-II

Answer explanation

• Degree of Table R (A): Degree refers to the number of attributes in R. The attributes in STUDENT are (Rollno, Name, Courseno) = 3, and in COURSE are (Courseno, Coursename, Capacity) = 3. Total Degree = 3 + 3 = 6.

• Cardinality of Table R (B): Cardinality refers to the number of tuples in R. The Cartesian product results in 5 × 3 = 15 tuples.

• Foreign Key of Relation STUDENT (C): Courseno in STUDENT references Courseno in COURSE.

• Foreign Key of Relation COURSE (D): COURSE does not reference any other table, so it has no foreign key.

In SQL, _______ is an Aggregate function.

• SELECT

• CREATE

• AVG

• MODIFY