DBMS GTU Paper Solution Winter 2020 | 3130703

Here, We provide Database Management System GTU Paper Solution Winter 2020. Read the Full DBMS gtu paper solution given below.

DBMS GTU Old Paper Winter 2020 [Marks : 56] : Click Here 

(a) Define following terms.
1) Schema 2) Database Management System 3) Physical Data

  1. Schema: A schema is a logical description of the entire database. It defines the structure of the database, including the tables, relationships between tables, and constraints. A schema provides a blueprint for the database, outlining the different types of data that can be stored and the rules for organizing and accessing that data.
  2. Database Management System (DBMS): A database management system (DBMS) is a software system that allows users to define, create, maintain, and control access to a database. DBMS provides an interface for users to interact with the database and perform operations such as adding, modifying, and deleting data. It also ensures the integrity of the data by enforcing rules and constraints.
  3. Physical Data Independence: Physical data independence is the ability to modify the physical storage structures of the database without affecting the conceptual schema or application programs. This means that the user is shielded from the details of how the data is physically stored on the disk. Any changes made to the physical storage structures should not affect the applications or the logical organization of the data. This type of independence is important because it allows for easier maintenance and modification of the database.

(b) Describe tasks performed by the Database Administrator.

(c) Differentiate strong entity set and weak entity set. Demonstrate the concept of both using real-time example using E-R diagram.

(a)Consider the relation scheme R = {E, F, G, H, I, J, K, L, M, M} and the set of functional dependencies {{E, F} -> {G}, {F} -> {I, J}, {E, H} -> {K, L}, K -> {M}, L -> {N} on R. What is the key for R ?

We know that K is a key for Relation schema R it K → R; i.e. K functionally determines each & every attribute of R. This is definition off key using functional dependency. 

Now, the Given Relation-Schema is R (E, F, G, H, I, J, K, L, M, N) and the Given functional dependencies :

{E, F} → {G}    (i)                             {K} → {M}    (iv) 
{F} → {I, J}    (ii)                              {L} → {N}    (v) 
{E, H} → {K, L}    (iii) 

Now we need to find function dependency whose right side is R. 
(a) {E, F} Cannot be a key as it determines only ‘G’ 
(b) {E, F, H} is a key:  
⚈ using Transitivity on (i) and (ii) we get 
{E, F} → {G, I, J} …….(vi) · 

⚈ using preudotransitivity on (iii) and (vi) we get 
{E, F, H} → {G, I, J, K, L} ……..(vii) 

⚈ using Decomposition on (vii) we get 
{E, F, H} → {K} and {E, F, H} → {L} 
Combining above with (iv) and (v) respectively 
{E, F, H} → {M} and {E, F, H} → {N} 
Now, finally performing union of these with (vii) we get: 
{E, F, H} → {G, I, J, K, L, M, N} ………..(viii) 
Also {E, F, H} → {E, F, H} is trivial, combine this with (viii) using union, we get: 
{E, F, H} → {E, F, G, H, I, J, K, L, M, N} 
⇒ {E, F, H} → R 
So, {E, F, H} is a key of R.

(b) Consider a relation scheme R = (A, B, C, D, E, H) on which the following functional dependencies hold: {A–>B, BC–> D, E–>C, D–>A}. What are the candidate keys of R?( Any 1 in case of more than one candidate key

(c) Draw an E-R diagram of following scenario. Make necessary assumptions and clearly note down the same.We would like to make our College’s manually operated Library to fully computerized.

(a) Define the terms: a) Primary Key b) Super Key

Primary Key:

A Primary Key is the minimal set of attributes of a table that has the task to uniquely identify the rows, or we can say the tuples of the given particular table.A primary key of a relation is one of the possible candidate keys which the database designer thinks it’s primary. It may be selected for convenience, performance and many other reasons.

Super Key :

We can define a super key as a set of those keys that identify a row or a tuple uniquely. The word super denotes the superiority of a key. Thus, a super key is the superset of a key known as a Candidate key. It means a candidate key is obtained from a super key only.

(b) List the type of joins in relational algebra. Explain with example.

(c) Which operator is used for “For All “type of queries? Explain same with example.

(a) Define the terms : a) foreign key b) candidate key

Foreign Key:

A foreign key is the one that is used to link two tables together via the primary key. It means the columns of one table points to the primary key attribute of the other table. It further means that if any attribute is set as a primary key attribute will work in another table as a foreign key attribute. But one should know that a foreign key has nothing to do with the primary key.

Candidate Key

A candidate key is a subset of a super key set where the key which contains no redundant attribute is none other than a Candidate Key. In order to select the candidate keys from the set of super key, we need to look at the super key set.

(b) List unary relational operators and explain with example.

(c) Consider the following relational database schema consisting of the four relation schemas:
passenger ( pid, pname, pgender, pcity)
agency ( aid, aname, acity)
flight (fid, fdate, time, src, dest)
booking (pid, aid, fid, fdate)

Answer the following questions using relational algebra queries.

a. Get the details about all flights from Chennai to New Delhi.
b. Get the complete details of all flights to New Delhi.
c. Find the passenger names for passengers who have bookings on at least one flight.

(a) List and explain ACID properties with respect to Database transaction.

ACID properties are a set of properties that are used to ensure the reliability and consistency of database transactions. The properties are as follows:

  1. Atomicity: Atomicity means that a transaction is treated as a single, indivisible unit of work. This means that either all the operations in a transaction are completed successfully, or none of them are completed. If any operation in a transaction fails, the entire transaction is rolled back to its original state.

For example, consider a bank transfer transaction that involves debiting the account of one customer and crediting the account of another customer. If the debit operation is successful but the credit operation fails, the entire transaction will be rolled back, and the account of the first customer will not be debited.

  1. Consistency: Consistency means that a transaction brings the database from one valid state to another. The database must satisfy certain integrity constraints before and after the transaction. If the constraints are violated, the transaction is rolled back.

For example, consider a transaction that transfers money between two accounts. The transaction should maintain the total balance of all the accounts involved in the transaction.

  1. Isolation: Isolation means that multiple transactions can occur concurrently without affecting each other. Each transaction must be executed as if it were the only transaction in the system. This means that the intermediate states of a transaction should be invisible to other transactions.

For example, consider two transactions that access the same bank account. The isolation property ensures that the two transactions are executed independently, and the results of one transaction are not visible to the other transaction until it is committed.

  1. Durability: Durability means that once a transaction is committed, its effects are permanent and will survive subsequent system failures. The changes made to the database by a committed transaction should be stored permanently in non-volatile memory.

For example, consider a database that is updated with a new record. The durability property ensures that the new record will not be lost due to a system failure.

In summary, the ACID properties ensure that database transactions are reliable, consistent, and durable. They are important for ensuring the integrity of data in a database and are widely used in modern database management systems.

 (b) Explain RAID Levels with respect to Data Storage.

(c) Explain the concept of Conflict Serializable with suitable schedules

(a) List and explain types of locks in transactions.

There are two types of Locks 

  1. Shared lock
  2. Exclusive lock

1. Shared Lock (S):

  • Another transaction that tries to read the same data is permitted to read, but a transaction that tries to update the data will be prevented from doing so until the shared lock is released.
  • Shared lock is also called read lock, used for reading data items only.
  • Shared locks support read integrity. They ensure that a record is not in process of being updated during a read-only request.
  • Shared locks can also be used to prevent any kind of updates of record.
  • It is denoted by Lock-S.
  • S-lock is requested using Lock-S instruction.

For example, consider a case where initially A=100 and there are two transactions which are reading A. If one of transaction wants to update A, in that case other transaction would be reading wrong value. However, Shared lock prevents it from updating until it has finished reading. 

2. Exclusive Lock (X) :

  • When a statement modifies data, its transaction holds an exclusive lock on data that prevents other transactions from accessing the data.
  • This lock remains in place until the transaction holding the lock issues a commit or rollback.
  • They can be owned by only one transaction at a time.
  • With the Exclusive Lock, a data item can be read as well as written. Also called write lock.
  •  Any transaction that requires an exclusive lock must wait if another transaction currently owns an exclusive lock or a shared lock against the requested resource.
  • They can be owned by only one transaction at a time.
  • It is denoted as Lock-X.
  • X-lock is requested using Lock-X instruction.

For example, consider a case where initially A=100 when a transaction needs to deduct 50 from A. We can allow this transaction by placing X lock on it. Therefore, when the any other transaction wants to read or write, exclusive lock prevent it.

(b) With neat diagram explain data storage hierarchy so far.

 (c) Explain deadlock with suitable scheduling examples.

(a) Explain following SQL commands with syntax and significance. Grant & Revoke

  • Grant : 

SQL Grant command is specifically used to provide privileges to database objects for a user. This command also allows users to grant permissions to other users too. 


grant privilege_name on object_name
to {user_name | public | role_name} 

Here privilege_name is which permission has to be granted, object_name is the name of the database object, user_name is the user to which access should be provided, the public is used to permit access to all the users. 

  • Revoke : 

Revoke command withdraw user privileges on database objects if any granted. It does operations opposite to the Grant command. When a privilege is revoked from a particular user U, then the privileges granted to all other users by user U will be revoked.


revoke privilege_name on object_name
from {user_name | public | role_name}




Consider above 3 tables ,assume appropriate data and solve following SQL queries
1.     Find out unique values of DEPARTMENT from Worker table
2.     Print details of the Workers whose SALARY lies between 100000 and 500000.
3.     Print details of the Workers who have joined in Feb’2014.
4.     Fetch worker names with salaries >= 50000 and <= 100000.

(c) Write short note on query processing.

(a) Explain following SQL commands with syntax and significance. Commit & Rollback.


COMMIT in SQL is a transaction control language that is used to permanently save the changes done in the transaction in tables/databases. The database cannot regain its previous state after its execution of commit. 

Example: Consider the following STAFF table with records: 

Staff table
sql> SELECT * FROM Staff WHERE Allowance = 400;
sql> COMMIT;


So, the SELECT statement produced the output consisting of three rows. 


ROLLBACK in SQL is a transactional control language that is used to undo the transactions that have not been saved in the database. The command is only been used to undo changes since the last COMMIT.

Example: Consider the following STAFF table with records: 




So, the SELECT statement produced the same output with the ROLLBACK command. 




Consider above 3 tables ,assume appropriate data and solve following SQL queries

1. Print details of the Workers who are also Managers.
2. SQL query to clone a new table from another table.
3. Fetch the list of employees with the same salary.
4. Fetch “FIRST_NAME” from Worker table in upper case.

(c) List the techniques to obtain the query cost. Explain any one.

Read More : DBMS GTU Paper Solution Winter 2021
Read More : DF GTU Paper Solution Winter 2021

“Do you have the answer to any of the questions provided on our website? If so, please let us know by providing the question number and your answer in the space provided below. We appreciate your contributions to helping other students succeed.