Download GTU BE/B.Tech 2019 Summer 3rd Sem New 2130703 Database Management Systems Question Paper

FirstRanker.com

GUJARAT TECHNOLOGICAL UNIVERSITY
BE - SEMESTER-III (NEW) EXAMINATION — SUMMER 2019

--- Content provided by‍ FirstRanker.com ---

Subject Code: 2130703 Date: 07/06/2019
Subject Name: Database Management Systems
Time: 02:30 PM TO 05:00 PM Total Marks: 70

Instructions:

1. Attempt all questions.

--- Content provided by​ FirstRanker.com ---

2. Make suitable assumptions wherever necessary.
3. Figures to the right indicate full marks.

MARKS

Q.1 (a) Explain query evaluation process. 03

(b) Explain disadvantages of file processing systems compare to Database 04 management system.

--- Content provided by‍ FirstRanker.com ---

(c) Consider the following relations: 07
EMP(empno, ename, jobtitle, managerno, hiredate, sal, commission,
deptno)
DEPT(deptno, dname, location)
Answer the following queries in SQL and Give an expression in the

--- Content provided by‌ FirstRanker.com ---

relational algebra to express each of the following queries.
a. Find the Employees working in the department number10, 20, 30
only.
b. Find Employees whose names start with letter A or letter a.
c. Find Employees along with their department name.

--- Content provided by FirstRanker.com ---

d. Find the Employees who are working in Smith's department
e. Find the Employees who get salary more than Allen’s salary.
f. Display employees who are getting maximum salary in each
department.
g. Find list of employees whose hire date is on or before 1-April-18.

--- Content provided by​ FirstRanker.com ---

Q.2 (a) What is the difference between data security and data integrity? 03

(b) What is transaction? List and explain ACID property of transaction. 04

(c) Design a database for an airline. The database must keep track of 07
customers and their reservations, flights and their status, seat assignments
on individual flights, and the schedule and routing of future flights. Your

--- Content provided by⁠ FirstRanker.com ---

design should include an E-R diagram, a set of relational schemas, and a
list of constraints, including primary-key and foreign-key constraints.

OR

(c) Design a database for a hospital with a set of patients and a set of medical 07
doctors. Associate with each patient a log of the various tests and

--- Content provided by FirstRanker.com ---

examinations conducted Your design should include an E-R diagram, a
set of relational schemas, and a list of constraints, including primary-key
and foreign-key constraints.

Q.3 (a) What is constraint? Explain types of constraints. 03

(b) Consider following set F of functional dependencies on schema R(A,B,C) 04

--- Content provided by FirstRanker.com ---

and compute canonical cover for F.
A— BC
A—B
AB— C

(c) Why should normalization be performed on a table and what are its 07

--- Content provided by⁠ FirstRanker.com ---

benefits. Explain 3NF and BCNF

OR

Q.3 (a) Describe the following SQL functions: (i) Add _months(x,y) (ii) Rtrim() 03
(iii) To_date

(b) Normalize the following schema, with given constraints, to 4NF. 04

--- Content provided by​ FirstRanker.com ---

books(accessionno, isbn, title, author, publisher)
users(userid, name, deptid, deptname)
List of constraints:
accessionno — isbn
isbn — title

--- Content provided by‌ FirstRanker.com ---

isbn — publisher
isbn —— author
userid — name
userid — deptid
deptid — deptname

--- Content provided by‌ FirstRanker.com ---

(c) What is redundant functional dependency? Explain trivial and non trivial 07
functional dependency with example.

Q.4 (a) Explain on delete cascade with example. 03

(b) Explain system recovery procedure with Checkpoint record concept. 04

(c) Explain following relational algebraic operation (i) Division (ii) inner join 07

--- Content provided by FirstRanker.com ---

(ii1) intersection

OR

Q.4 (a) Explain commit, rollback and savepoint command. 03

(b) Explain Log based recovery method. 04

(c) Explain following relation algebra'operations: (i) full outer join (i1) 07

--- Content provided by⁠ FirstRanker.com ---

projection (ii1) Cartesian product.

Q.5 (a) Write a PL/SQL block to print the sum of Numbers from 1 to 100. 03

(b) Explain the purpose of sorting with example with reference to query 04
optimization.

(c) Explain conflict serializability and view serializability with example. 07

--- Content provided by⁠ FirstRanker.com ---

OR

Q.5 (a) Write a PL/SQL block to print the given number is prime or not. 03

(b) Explain the measures of finding out the cost of a query in query 04
processing.

(c) What is a recoverable schedule? Why is recoverability of schedules 07

--- Content provided by‌ FirstRanker.com ---

desirable? Are there any circumstances under which it would be desirable
to allow non-recoverable schedules? Explain your answer

FirstRanker.com