Download GTU (Gujarat Technological University) BE/BTech (Bachelor of Engineering / Bachelor of Technology) 2019 Summer 3rd Sem New 2130703 Database Management Systems Previous Question Paper
Seat No.: ________ Enrolment No.___________
GUJARAT TECHNOLOGICAL UNIVERSITY
BE - SEMESTER ?III (NEW) EXAMINATION ? SUMMER 2019
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.
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
management system.
04
(c) Consider the following relations:
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
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.
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.
07
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
customers and their reservations, flights and their status, seat assignments
on individual flights, and the schedule and routing of future flights. 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.
07
OR
(c) Design a database for a hospital with a set of patients and a set of medical
doctors. Associate with each patient a log of the various tests and
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.
07
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)
and compute canonical cover for F.
A? BC
04
FirstRanker.com - FirstRanker's Choice
1
Seat No.: ________ Enrolment No.___________
GUJARAT TECHNOLOGICAL UNIVERSITY
BE - SEMESTER ?III (NEW) EXAMINATION ? SUMMER 2019
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.
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
management system.
04
(c) Consider the following relations:
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
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.
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.
07
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
customers and their reservations, flights and their status, seat assignments
on individual flights, and the schedule and routing of future flights. 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.
07
OR
(c) Design a database for a hospital with a set of patients and a set of medical
doctors. Associate with each patient a log of the various tests and
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.
07
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)
and compute canonical cover for F.
A? BC
04
2
B ? C
A ? B
AB ? C
(c) Why should normalization be performed on a table and what are its
benefits. Explain 3NF and BCNF
07
OR
Q.3 (a) Describe the following SQL functions: (i) Add_months(x,y) (ii) Rtrim()
(iii) To_date
03
(b) Normalize the following schema, with given constraints, to 4NF.
books(accessionno, isbn, title, author, publisher)
users(userid, name, deptid, deptname)
List of constraints:
accessionno ? isbn
isbn ? title
isbn ? publisher
isbn ?? author
userid ? name
userid ? deptid
deptid ? deptname
04
(c) What is redundant functional dependency? Explain trivial and non trivial
functional dependency with example.
07
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
(iii) intersection
07
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 (ii)
projection (iii) Cartesian product.
07
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
optimization.
04
(c) Explain conflict serializability and view serializability with example. 07
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
processing.
04
(c) What is a recoverable schedule? Why is recoverability of schedules
desirable? Are there any circumstances under which it would be desirable
to allow non-recoverable schedules? Explain your answer
07
*************
FirstRanker.com - FirstRanker's Choice
This post was last modified on 20 February 2020