Download GTU (Gujarat Technological University) BE/BTech (Bachelor of Engineering / Bachelor of Technology) 2019 Winter 3rd Sem New 3130703 Database Management Systems Previous Question Paper
Seat No.: ________ Enrolment No.___________
GUJARAT TECHNOLOGICAL UNIVERSITY
BE - SEMESTER ? III (New) EXAMINATION ? WINTER 2019
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.
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
operator with suitable example.
04
(c) Enlist and explain the advantages of DBMS over traditional file
system.
Enlist and explain the advantages of DBMS over traditional file system.
07
Q.2 (a) Explain Instance and Schema in detail. 03
(b) The relational database schema is given below.
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.
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.
04
(c) Construct an E-R diagram for a car insurance company whose
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
the payment was received.
07
OR
(c) Explain specialization and generalization concepts in ER diagram
with suitable example.
07
Q.3 (a) What do you mean by integrity constraints? Discuss various
integrity constraints.
03
(b) Consider schema R = (A, B, C, G, H, I) and the set F of functional
dependencies
{A ? B, A ? C, CG ? H, CG ? I, B ? H}. Prove that AG ?
I Holds.
04
FirstRanker.com - FirstRanker's Choice
1
Seat No.: ________ Enrolment No.___________
GUJARAT TECHNOLOGICAL UNIVERSITY
BE - SEMESTER ? III (New) EXAMINATION ? WINTER 2019
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.
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
operator with suitable example.
04
(c) Enlist and explain the advantages of DBMS over traditional file
system.
Enlist and explain the advantages of DBMS over traditional file system.
07
Q.2 (a) Explain Instance and Schema in detail. 03
(b) The relational database schema is given below.
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.
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.
04
(c) Construct an E-R diagram for a car insurance company whose
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
the payment was received.
07
OR
(c) Explain specialization and generalization concepts in ER diagram
with suitable example.
07
Q.3 (a) What do you mean by integrity constraints? Discuss various
integrity constraints.
03
(b) Consider schema R = (A, B, C, G, H, I) and the set F of functional
dependencies
{A ? B, A ? C, CG ? H, CG ? I, B ? H}. Prove that AG ?
I Holds.
04
2
(c) A college maintains details of its lecturers' subject area skills. These
details comprise:
Lecturer Number, Lecturer Name, Lecturer Grade, Department
Code, Department Name, Subject Code, Subject Name, Subject
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.
07
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
the various projects in which its employees are currently involved.
These details comprise: Employee Number, Employee Name,
Date of Birth, Department Code, Department Name, Project Code,
Project Description, Project Supervisor
Assume the following:
? Each employee number is unique.
? 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
are repeating fields.
Normalize this data to Third Normal Form.
07
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:
(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.
07
OR
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
PL/SQL.
03
(b) Write a note on two phase locking protocol. 04
(c) Consider following schema and write SQL for given
statements.
Student (RollNo, Name, DeptCode, City)
Department (DeptCode, DeptName)
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.
07
FirstRanker.com - FirstRanker's Choice
1
Seat No.: ________ Enrolment No.___________
GUJARAT TECHNOLOGICAL UNIVERSITY
BE - SEMESTER ? III (New) EXAMINATION ? WINTER 2019
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.
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
operator with suitable example.
04
(c) Enlist and explain the advantages of DBMS over traditional file
system.
Enlist and explain the advantages of DBMS over traditional file system.
07
Q.2 (a) Explain Instance and Schema in detail. 03
(b) The relational database schema is given below.
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.
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.
04
(c) Construct an E-R diagram for a car insurance company whose
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
the payment was received.
07
OR
(c) Explain specialization and generalization concepts in ER diagram
with suitable example.
07
Q.3 (a) What do you mean by integrity constraints? Discuss various
integrity constraints.
03
(b) Consider schema R = (A, B, C, G, H, I) and the set F of functional
dependencies
{A ? B, A ? C, CG ? H, CG ? I, B ? H}. Prove that AG ?
I Holds.
04
2
(c) A college maintains details of its lecturers' subject area skills. These
details comprise:
Lecturer Number, Lecturer Name, Lecturer Grade, Department
Code, Department Name, Subject Code, Subject Name, Subject
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.
07
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
the various projects in which its employees are currently involved.
These details comprise: Employee Number, Employee Name,
Date of Birth, Department Code, Department Name, Project Code,
Project Description, Project Supervisor
Assume the following:
? Each employee number is unique.
? 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
are repeating fields.
Normalize this data to Third Normal Form.
07
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:
(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.
07
OR
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
PL/SQL.
03
(b) Write a note on two phase locking protocol. 04
(c) Consider following schema and write SQL for given
statements.
Student (RollNo, Name, DeptCode, City)
Department (DeptCode, DeptName)
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.
07
3
4. Create RollNo field as primary key for existing Student
table.
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
(b) Enlist and explain ACID properties for transaction. 04
(c) Consider the tables given below. Write the SQL queries for
the questions given below:
T1 ( Empno, Ename , Salary, Designation,)
T2 (Empno, Deptno.)
(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.
(6) Add Empno as primary key in existing table T1.
(7) Display the Deptno having highest number of employees.
07
*************
FirstRanker.com - FirstRanker's Choice
This post was last modified on 20 February 2020