1
Seat No.: Enrolment
GUJARAT TECHNOLOGICAL UNIVERSITY
ME SEMESTER-II • EXAMINATION SUMMER 2017
Subject Code: 2722318 Date: 31/05/2017
Subject Name: Database Management Systems
Time: 02:30 PM To 05:00 PM Total Marks: 70
Instructions:
1. Attempt all questions.
2. Make suitable assumptions wherever necessary.
3. Figures to the right indicate full marks.
Q.1

Explain three level architecture of database system
Explain major functions of DBA
03
04

Design a generalization-specialization hierarchy for a motor-vehicle sales company, which sells motorcycles, passenger cars, vans, and buses.
Explain the distinction between disjoint and overlapping constraints.
04
03
Q.2

Consider following schema and give expression in relational algebra and write SQL query for the given statements.
employee (person-name, street, city)
works (person-name, company-name, salary)
company (company-name, city)
manages (person-name, manager-name)
Find the name of all employees and their manager who work for "Yes Bank"
Find the names and cities of residence of all employees who work for "Yes Bank" and salary more than 15000.
Find the names, company and cities of residence of all employees whose city and company city are same.
02
02
03

Consider the following relation schema and set of FD's F.
R
A BC CD E A
Find out closure of F
ii) Find out canonical cover of F
iii) Find out attribute closure of A. is A a candidate key or not?
07
OR

What is Functional Dependency? Explain lossless decomposition with suitable example.
07
Q.3

Explain system recovery procedure with check point record concept.
07

Define and explain with suitable example:
entity, binary relationship, multi value attribute, derived attribute
07
OR
Q.3

List the ACID properties. Explain the usefulness of each.
07

Construct an ER diagram considering a database used to record the marks that students get in different exams of different course offerings.
07
Q.4

Explain 1NF, 2NF, 3NF and BCNF with example.
07

What is recoverable schedule? Why is recoverability of schedules desirable?
07
OR
2
Q.4

What is Joins? Explain types of Join with example.
07

What is constraint in database? Explain types of constraints with suitable example.
List the benefits of database approach.
05
02
Q.5

Explain system recovery procedure with check point record concept
07

Why concurrency control is needed?
Explain basic concept of referential integrity with use of update and delete operation.
03
04
OR
Q.5

Explain following terms with suitable example.
Super key, candidate key, primary key
With example explain various mapping cardinalities.
03
04

Explain deadlock detection mechanism.
What is deadlock? Explain Wait-For-Graph.
04
03