Database Management System-(SUMMER 2021 PAPER SOLUTION)

 
GUJARAT TECHNOLOGICAL UNIVERSITY BE - SEMESTER–III (NEW) EXAMINATION – SUMMER 2021

Subject Code : 3130703 
Date : 11/09/2021 
Subject Name : Database Management Systems  
Total Marks : 70

1.What is Data Definition Language? List DDL statements and explain anyone with an example.

• A data definition language (DDL) is a computer language used to create and modify the structure of database objects in a database. These database objects include views, schemas, tables, indexes, etc.

• Also Data definition language (DDL) refers to the set of SQL commands that can create and manipulate the structures of a database. DDL statements are used to create, change, and remove objects including indexes, triggers, tables, and views. 

Common DDL statements include:

• CREATE : This command is used to create the database or its objects (like table, index, function, views, store procedure, and triggers).
• DROP : This command is used to delete objects from the database.
• ALTER : This is used to alter the structure of the database.
• TRUNCATE : This is used to remove all records from a table, including all spaces allocated for the records are removed.
• COMMENT : This is used to add comments to the data dictionary.
• RENAME : This is used to rename an object existing in the database.

2. List and describe ACID property of transactions.

A transaction is a single logical unit of work which accesses and possibly modifies the contents of a database. Transactions access data using read and write operations. 

In order to maintain consistency in a database, before and after the transaction, certain properties are followed. These are called ACID properties. 

 

Atomicity 
By this, we mean that either the entire transaction takes place at once or doesn’t happen at all. There is no midway i.e. transactions do not occur partially. Each transaction is considered as one unit and either runs to completion or is not executed at all. It involves the following two operations. 
—Abort: If a transaction aborts, changes made to database are not visible. 
—Commit: If a transaction commits, changes made are visible. 
Atomicity is also known as the ‘All or nothing rule’. 
 

 

Consider the following transaction T consisting of T1 and T2: Transfer of 100 from account X to account Y. 

 

If the transaction fails after completion of T1 but before completion of T2.( say, after write(X) but before write(Y)), then amount has been deducted from X but not added to Y. This results in an inconsistent database state. Therefore, the transaction must be executed in entirety in order to ensure correctness of database state. 

  
Consistency 
This means that integrity constraints must be maintained so that the database is consistent before and after the transaction. It refers to the correctness of a database. Referring to the example above, 
The total amount before and after the transaction must be maintained. 
Total before T occurs = 500 + 200 = 700. 
Total after T occurs = 400 + 300 = 700. 
Therefore, database is consistent. Inconsistency occurs in case T1 completes but T2 fails. As a result T is incomplete. 

  
Isolation 
This property ensures that multiple transactions can occur concurrently without leading to the inconsistency of database state. Transactions occur independently without interference. Changes occurring in a particular transaction will not be visible to any other transaction until that particular change in that transaction is written to memory or has been committed. This property ensures that the execution of transactions concurrently will result in a state that is equivalent to a state achieved these were executed serially in some order. 
Let X= 500, Y = 500. 
Consider two transactions T and T”. 

 

Suppose T has been executed till Read (Y) and then T’’ starts. As a result , interleaving of operations takes place due to which T’’ reads correct value of X but incorrect value of Y and sum computed by 
T’’: (X+Y = 50, 000+500=50, 500) 
is thus not consistent with the sum at end of transaction: 
T: (X+Y = 50, 000 + 450 = 50, 450). 
This results in database inconsistency, due to a loss of 50 units. Hence, transactions must take place in isolation and changes should be visible only after they have been made to the main memory. 

  
Durability: 
This property ensures that once the transaction has completed execution, the updates and modifications to the database are stored in and written to disk and they persist even if a system failure occurs. These updates now become permanent and are stored in non-volatile memory. The effects of the transaction, thus, are never lost. 

The ACID properties, in totality, provide a mechanism to ensure correctness and consistency of a database in a way such that each transaction is a group of operations that acts a single unit, produces consistent results, acts in isolation from other operations and updates that it makes are durably stored. 

3.Consider the relation R = {A, B, C, D, E, F, G, H, I, J} and the set of functional dependencies F={{A, B} → C, A → {D, E}, B → F, F →{G, H}, D →{I, J}} What is the key for R? Decompose R into 2NF, then 3NF relations.

4.Differentiate shared lock and exclusive lock in lock-based protocol.

Difference between Shared Lock and Exclusive Lock :

S.No.	Shared Lock	Exclusive Lock
1.	Lock mode is read only operation.	Lock mode is read as well as write operation.
2.	Shared lock can be placed on objects that do not have an exclusive lock already placed on them.	Exclusive lock can only be placed on objects that do no have any other kind of lock.
3.	Prevents others from updating the data.	Prevents others from reading or updating the data.
4.	Issued when transaction wants to read item that do not have an exclusive lock.	Issued when transaction wants to update unlocked item.
5.	Any number of transaction can hold shared lock on an item.	Exclusive lock can be hold by only one transaction.
6.	S-lock is requested using lock-S instruction.	X-lock is requested using lock-X instruction.