RGPV DBMS November 2019 Solved Paper

CS-502 (CBGS)

B.Tech. V Semester 

Examination, November 2019

Choice Based Grading System (CBGS)

Database Management System

1. a) Differentiate between Database approach v/s Traditional file accessing approach.

Ans. Click here

b) Explain the concepts of Generalization and Aggregation with appropriate examples.

Ans. Generalization:

Entities with common attributes can be merged into a generic or super type entity by generalisation. 

For example, the entity EMPLOYEE is a super type of Professor, Conductor, and Engineer.

The “IS-A” relationship between the subtype and the super type can be used to define generalisation. 

For example, a Professor is an employee of the college.

Here, Professor, Principal and Engineer is subtypes. And, Employee is a supertype. 

A super type can be a subtype in another relationsship.

Aggregation:

Aggregation describes the formation of higher-level objects from lower level components. 

For example, a car is made up of an engine, chasis, tiers and so on.

Aggregation can be defined in terms of an HAS -A-relationship between the subtype & supertype.

2. a) What do you mean by data modeling? Compare different data models.

Ans. Click here

b) Explain the concepts of Primary key, Foreign key and Integrity constraints.

Ans. Click here

3. a) Explain triggers and assertions and explain it with through appropriate query.

Ans. Click here

b) Explain select, project and division operations with examples.

Ans. Select operations : Select operation displays the records for the condition. Select operation is denoted by sigma (σ).

Syntax: 

σcondition(TableName)

Example:

Input:

σName=Jayesh(Student)

Output:

Project operation : Project operations displays the specific column of a table. Projection operation is denoted by pie (∏).

Syntax: 

∏_ColumnName (TableName)

Example:

Input:

∏_RollNo (Student)

Output:

Division operations : 

Division operations involves dividing one relation by another.  The division operator is used for division operations.

In above example, when the division operation is performed, it will retrieve only those RollNo from relation “Student” who has branch “CSE” from relation Branch.

4. a) Consider the following employee database. (RGPV 2019)

Employee (empname,street, city) 

work (empname,companyname,salary) 

company (companyname,city) 

manages (empname,management). 

Give an expression in the SQL for each request.

i) Find the names of all employees who work for first Bank corporation.

select employee.empname from employee, works 

where employee.empname=works.empname 

and companyname = ‘First bank corporation’.

ii) Find the names, street address and cities of residence of all employees who work for first Bank corporation and earn more than 200000 per annum.

select employee.empname, employee.street, employee.city from

employee, works where employee.empname=works.empname 

and companyname = ‘First bank corporation’ and salary > 200000).

iii) Find the names of all employee in this database who live in the same city as the company for which they work.

select employee.empname from employee e, works w, company c

where e.empname = w.empname 

and e.city = c.city 

and w.companyname = c.companyname.

5. a) Explain Closure of set of functional dependency and closure of attribute.

b) Explain canonical cover and extraneous attributes with examples.

6. a) Explain the deferred and immediate modification versions of the log based recovery scheme.

b) How concurrency is performed? Explain the protocol that is used to maintain the concurrency concept.

Ans. Click here

7. a) Discuss on strict two – phase locking protocol and time stamp-based protocol.

b) Explain privilege and role management process.

8.a) Write short note on following:

a) Distributed database

b) Nested and parameterized cursors

c) Branching and looping constructs in ANSIAQL.