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

FirstRanker.com

GUJARAT TECHNOLOGICAL UNIVERSITY
BE - SEMESTER- III (New) EXAMINATION — WINTER 2019

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

Subject Code: 3130703 Date: 30/11/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) Define Primary key, Candidate key and Super key. 03
(b) List the relational algebra operators. Discuss any two such algebra 04

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

operator with suitable example.
(c) Enlist and explain the advantages of DBMS over traditional file 07
system.

Q.2 (a) Explain Instance and Schema in detail. 03
(b) The relational database schema is given below. 04

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

employee (person-name, street, city)
works (person-name, company-name, salary)
company (company-name, city)
manages (person-name, manager-name)
Write the relational algebra expressions for the given queries.

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

1. Find the names of all employees who work for First Bank Corporation.
2. Find the names and cities of residence of all employees who work for First Bank Corporation:
3. Find the names, street address, and cities of residence of all employees who work for First Bank Corporation and earn more than $10,000 per annum:
4. Find the names of all employees in this database who do not work for First Bank Corporation.

(c) Construct an E-R diagram for a car insurance company whose 07

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

customers own one or more cars each. Each car has associated
with it zero to any number of recorded accidents. Each insurance
policy covers one or more cars, and has one or more premium
payments associated with it. Each payment is for a particular
period of time and has an associated due date and the date when

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

the payment was received.

OR

(c) Explain specialization and generalization concepts in ER diagram 07
with suitable example.

Q.3 (a) What do you mean by integrity constraints? Discuss various 03

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

integrity constraints.
(b) Consider schema R = (A, B, C, G, H, I) and the set F of functional 04
dependencies
{A—B,A—C,CG— H,CG—[,B— H}. Prove that AG —
I Holds.

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

(c) A software contract and consultancy firm maintains details of all 07
the various projects in which its employees are currently involved.
These details comprise:
Lecturer Number, Lecturer Name, Lecturer Grade, Department
Code, Department Name, Subject Code, Subject Name, Subject

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

Level
Assume that each lecturer may teach many subjects but may not
belong to more than one department. Subject Code, Subject Name
and Subject Level are repeating fields.
Normalize this data to Third Normal Form.

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

OR

Q.3 (a) Explain various Normal forms up to 3NF. 03
(b) Explain Armstrong’s Axioms in detail. 04
(c) A software contract and consultancy firm maintain details of all 07
the various projects in which its employees are currently involved.

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

These details comprise: Employee Number, Employee Name,
Date of Birth, Department Code, Department Name, Project Code,
Project Description, Project Supervisor
Assume the following:
e Each employee number is unique.

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

Each department has a single department code.
Each project has a single code and supervisor.
Each employee may work on one or more projects.
Employee names need not necessarily be unique.
Project Code, Project Description and Project Supervisor

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

are repeating fields.
Normalize this data to Third Normal Form.

Q.4 (a) Explain Authorization and access control in brief. 03
(b) Discuss various steps of query processing with diagram. 04
(c) Construct a B tree for the following set of key values: 07

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

(2,3,5,7,11,17,19,23,29,31)
Assume that the tree is initially empty and values are added in
ascending order. Consider-the number of pointers in each node as
four.

OR

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

Q.4 (a) Explain various mapping cardinalities. 03
(b) Describe log-based recovery in brief. 04
(c) Explain Dense and Sparse indices in detail. 07

Q.5 (a) What is PL/SQL. Explain the difference between SQL and 03
PL/SQL.

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

(b) Write a note on two phase locking protocol. 04
(c) Consider following schema and write SQL for given 07
statements.
Student (RollNo, Name, DeptCode, City)
Department (DeptCode, DeptName)

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

Result (RollNo, Semester, SPI)

1. Display the name of students with RollNo whose name ends with ‘sh’.
2. Display department wise total students whose total students are greater than 500.
3. List out the RollNo, Name along with CPI of Student.
4. Create RO, ErétReaRRBIREOKSY for exighing, firstranker.com table.

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

5. Display student name who got highest SPI in semester 1.
6. Display the list of students whose DeptCode is 5, 6,7,10.
7. Create table Student_New from student table without data.

OR

Q.5 (a) Explain conflict serializability with the help of suitable example. 03

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

(b) Enlist and explain ACID properties for transaction. 04
(c) Consider the tables given below. Write the SQL queries for 07
the questions given below:
T1 ( Empno, Ename , Salary, Designation,)
T2 (Empno, Deptno.)

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

1. Display all the details of the employee whose salary is lesser than 10000.
2. Display the Deptno in which Employees with name starting with letter ‘S’ is working.
3. Add a new column Deptname in table T2.
4. Change the designation of Geeta from ‘Manager’ to ‘Senior Manager’.
5. Find the total salary of all the employees department wise.

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

6. Add Empno as primary key in existing table T1.
7. Display the Deptno having highest number of employees.

FirstRanker.com