DBMS GTU Paper Solution Winter 2021 | 3130703

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

DBMS GTU Old Paper Winter 2021 [Marks : 70] : Click Here 

Question: 1

(a) Explain three levels of data abstraction.

There are mainly levels of data abstraction: 

Data abstraction is the process of hiding complex implementation details of a system and presenting a simplified view of the data to the user. This helps in managing the complexity of the system and makes it easier to use and understand. There are three levels of data abstraction in a database management system:

  1. Physical Level: The physical level of data abstraction is the lowest level of abstraction. At this level, the database management system deals with the physical storage of data. It is concerned with how the data is stored on the storage media such as hard disks, tapes, or any other storage device. The physical level describes the data structures used for storing data, the file organization techniques, and the access methods used to retrieve data.
  2. Logical Level: The logical level of data abstraction is the second level of abstraction. At this level, the database management system deals with the logical representation of data. It is concerned with the meaning of the data stored in the database. The logical level describes the relationships between data elements, the constraints applied on data, and the operations that can be performed on the data.
  3. View Level: The view level of data abstraction is the highest level of abstraction. At this level, the database management system deals with the user interface or the user view of the data. It is concerned with the way the data is presented to the user. The view level describes the user’s view of the data, the user’s access privileges, and the way the data is presented to the user. Different users may have different views of the same data, depending on their needs and requirements.

Example: In case of storing customer data,

Physical level – it will contains block of storages (bytes,GB,TB,etc)

Logical level –  it will contain the fields and the attributes of data.

View level – it works with CLI or GUI access of database

(b) Describe Data Definition Language and Data Manipulation Language.

(c) What is integrity constraint? Explain primary key, reference key and check constraint with SQL syntax.

Question: 2

(a) Differentiate single-valued and multi-valued attributes with example.

  • Single-Valued Attributes

Single valued attributes are those attributes that consist of a single value for each entity instance and can’t store more than one value. The value of these single-valued attributes always remains the same, just like the name of a person.

Example Diagram:


As we can see in the above example, These attributes can store only one value from a set of possible values. Each entity instance can have only one Roll_no, which is a unique, single DOB by which we can calculate age and also fixed gender. Also, we can’t further subdivide these attributes, and hence, they are simple as well as single-valued attributes.

  • Multi-Valued Attributes

Multi-valued attributes have opposite functionality to that of single-valued attributes, and as the name suggests, multi-valued attributes can take up and store more than one value at a time for an entity instance from a set of possible values. These attributes are represented by co-centric elliptical shape, and we can also use curly braces { } to represent multi-valued attributes inside it.

Example Diagram:


As we can see in the above example, the Student entity has four attributes: Roll_no and Age are simple as well as single-valued attributes as discussed above but Mob_no and Email_id are represented by co-centric ellipse are multi-valued attributes.

(b) What is weak entity? How the weak entity can be converted to the strong entity? Show the symbol for representing weak entity.

(c)Why do we require E-R model? Explain the term ‘Generalization’, ‘Specialization’ and ‘Aggregation’.

(c) What is the similarity between relational model and E-R model? How the entity, attributes, primary key and relationship are shown in the relational model.

Question: 3

(a) Write Relational Algebra syntax for the following queries.          Employee(eno,ename,salary,designation)
1) Find out name of employees who are ‘Manager’.

2) Display name of customers.
3) Retrieve Employee records whose salary is less than 20,000.

  1. Π ename (σ designation=’Manager’ (Employee))
  2. Π cname (Customer)
  3. Π eno, ename, salary, designation (σ salary < 20000 (Employee))

(b)  Differentiate lossy decomposition and lossless decomposition.

(c)  What is redundancy? Explain insert, update and delete anomalies in database with example.

Question: 3

(a) Write Relational Algebra syntax for the given queries using the following database.
Employee(eno,ename,salary,designation) Customer(cno,cname,address,city)
1) Find out name of employees who are also customers.
2) Find out name of person who are employees but not customers. 3)Display all names who are either employees or customers.

  1. Π ename (Employee) ∩ Π cname (Customer)
  2. Π ename (Employee) – Π cname (Customer)
  3. Π ename (Employee) U Π cname (Customer)

(b) What is the limitation of index-sequential file? Explain with example how B+ tree overcomes it.

(c) What is the difference between Join and Sub query? Explain any two built-in function for following category. 1) Numeric 2) String 3) Date

Question: 4

(a) What is the view? How does it different from the table?

The view is a virtual/logical table formed as a result of a query and used to view or manipulate parts of the table. We can create the columns of the view from one or more tables. Its content is based on base tables.

The view is a database object with no values and contains rows and columns the same as real tables. It does not occupy space on our systems.

  • The following points explain the differences between tables and views:

A table is a database object that holds information used in applications and reports. On the other hand, a view is also a database object utilized as a table and can also link to other tables.

A table consists of rows and columns to store and organized data in a structured format, while the view is a result set of SQL statements.

A table is structured with columns and rows, while a view is a virtual table extracted from a database.

The table is an independent data object while views are usually depending on the table.

The table is an actual or real table that exists in physical locations. On the other hand, views are the virtual or logical tablethat does not exist in any physical location.

A table allows to performs add, update or delete operations on the stored data. On the other hand, we cannot perform add, update, or delete operations on any data from a view. If we want to make any changes in a view, we need to update the data in the source tables.

We cannot replace the table object directly because it is stored as a physical entry. In contrast, we can easily use the replace option to recreate the view because it is a pseudo name to the SQL statement running behind on the database server.

(b) Explain below mentioned features of concurrency. 1) Improved throughput 2) Reduced waiting time

(c) What is index in the database? Explain sparse indices and Dense indices with proper example.

Question: 4

(a) Differentiate dynamic hashing and static hashing.

Dynamic HashingStatic Hashing
Hash table size can change during executionHash table size is fixed at compile time or initialization
Used when the number of keys is unknown or may changeUsed when the number of keys is known and will not change
Can use open addressing or chaining to resolve collisionsTypically uses chaining to resolve collisions
Insertions and deletions may require rehashing and resizing the tableInsertions and deletions do not require rehashing or resizing the table
Can use more memory than static hashingUses less memory than dynamic hashing

(b) What is the atomicity and consistency property of transaction?

(c) What is Query processing? Explain why ‘Parsing and translation’ and ‘Optimization’ steps are required for query processing.

Question: 5

(a) How does ‘partial commit’ state differ from ‘commit’ state of the transaction?

In a database management system, a transaction is a sequence of operations that are treated as a single unit of work. The two main states of a transaction are the “partial commit” state and the “commit” state.

  1. Partial Commit State: In the partial commit state, some of the changes made by the transaction are saved to the database, while the remaining changes are still in the transaction buffer and have not yet been written to the database. In this state, the transaction can still be rolled back if there is a failure, and the database can be restored to its original state before the transaction started.
  2. Commit State: In the commit state, all the changes made by the transaction are saved to the database, and the transaction is completed. Once the transaction has been committed, it cannot be rolled back, and the changes made by the transaction become permanent in the database.

The main difference between the partial commit state and the commit state is that in the partial commit state, only some of the changes made by the transaction are saved to the database, while in the commit state, all the changes made by the transaction are saved to the database. The partial commit state is useful in situations where a transaction involves multiple steps, and it is not necessary to commit all the changes at once. For example, in a banking application, a transaction that involves transferring money from one account to another may involve multiple steps, such as checking the account balance, deducting the amount from the source account, and adding the amount to the destination account. In this case, the partial commit state can be used to save the changes made in each step before committing the entire transaction. This can help in reducing the risk of errors and ensuring data consistency in the database.

(b) Enlist and explain user authorization to modify the database schema.

(c) How does two phase locking protocol differ from timestamp based protocol? Explain timestamp-ordering protocol.

Question: 5

(a) Why does the trigger require in database? Write SQL syntax for creating database trigger.

Triggers are used for several purposes:

Produce additional checking during insert, update or delete operations on the affected table.

They allow us to encode complex default values that cannot be handled by default constraints.

Implement referential integrity across databases.

They allow us to control what actually happens when one performs an insert, update, or delete on a view that accesses multiple tables.

You can calculate aggregated columns in a table using triggers.

A trigger is a stored procedure in database which automatically invokes whenever a special event in the database occurs. For example, a trigger can be invoked when a row is inserted into a specified table or when certain table columns are being updated. 


create trigger [trigger_name]
[before | after]
{insert | update | delete}
on [table_name]
[for each row]

Example :

create trigger stud_marks 
before INSERT 
for each row 
set Student.total = Student.subj1 + Student.subj2 + Student.subj3, Student.per = Student.total * 60 / 100;

(b) When do we require to use group by clause? How aggregate functions are used with group by clause?

(c) When Join is used in SQL? Explain Left outer, Right outer and Full outer join with SQL syntax.

Read More : DS GTU Paper Solution Winter 2021
Read More : DBMS GTU Paper Solution Winter 2020

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.

Read, DBMS Winter 2020 Paper Solution