Download JNTU Kakinada B.Tech 2-2 2014 August DATA BASE MANAGEMENT SYSTEMS_PP fr 237 Question Paper

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

(Com. to CSE, IT)
Time: 3 hours Max. Marks: 75

Answer any FIVE Questions
All Questions carry Equal Marks

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

~~~~~~~~~~~~~~~~~~~~~~~~

1. a) What is data model? List and explain different data models.
b) Explain the difference between external, internal, and conceptual schemas. How are these
different schema layers related to the concepts of logical and physical data independence?

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

(8M+7M)
2. a) Discuss in detail about the main steps in the database design and clearly focus in detail about
the goal of each step? In which steps is the ER model mainly used?
b) Draw and explain E-R diagram of an Airline reservation system? (8M+7M)

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

3. a) Discuss in detail about integrity constraint over relations.
b) What is join operation in relational algebra? Discuss in detail about variants of joins.
(8M+7M)
4. a) Consider the following relational schema. An employee can work in more than one
department; the pct time ?eld of the Works relation shows the percentage of time that a given

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

employee works in a given department.
Emp(eid: integer, ename: string, age: integer, salary: real)
Works(eid: integer, did: integer, pct time: integer)
Dept(did: integer, budget: real, managerid: integer)
Write the following queries in SQL:

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

i) Print the names and ages of each employee who works in both the Hardware department
and the Software department.
ii) For each department with more than 20 full-time-equivalent employees (i.e., where the
part-time and full-time employees add up to at least that many full-time employees), print
the did together with the number of employees that work in that department.

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

iii) Print the name of each employee whose salary exceeds the budget of all of the
departments that he or she works in.
iv) Find the manager ids of managers who manage only departments with budgets greater than
$1,000,000.
v) Find the enames of managers who manage the departments with the largest budget.

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

b) What is a trigger? What are the three parts? Differentiate row-level and statement-level
triggers. (8M+7M)

1 of 2
R10

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

SET - 1


FirstRanker.com - FirstRanker's Choice
|''|''|||''|'''|||'|

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

Code No: R22053

II B. Tech II Semester Regular Examinations August - 2014
DATA BASE MANAGEMENT SYSTEMS
(Com. to CSE, IT)

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

Time: 3 hours Max. Marks: 75

Answer any FIVE Questions
All Questions carry Equal Marks
~~~~~~~~~~~~~~~~~~~~~~~~

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

1. a) What is data model? List and explain different data models.
b) Explain the difference between external, internal, and conceptual schemas. How are these
different schema layers related to the concepts of logical and physical data independence?
(8M+7M)

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

2. a) Discuss in detail about the main steps in the database design and clearly focus in detail about
the goal of each step? In which steps is the ER model mainly used?
b) Draw and explain E-R diagram of an Airline reservation system? (8M+7M)

3. a) Discuss in detail about integrity constraint over relations.

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

b) What is join operation in relational algebra? Discuss in detail about variants of joins.
(8M+7M)
4. a) Consider the following relational schema. An employee can work in more than one
department; the pct time ?eld of the Works relation shows the percentage of time that a given
employee works in a given department.

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

Emp(eid: integer, ename: string, age: integer, salary: real)
Works(eid: integer, did: integer, pct time: integer)
Dept(did: integer, budget: real, managerid: integer)
Write the following queries in SQL:
i) Print the names and ages of each employee who works in both the Hardware department

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

and the Software department.
ii) For each department with more than 20 full-time-equivalent employees (i.e., where the
part-time and full-time employees add up to at least that many full-time employees), print
the did together with the number of employees that work in that department.
iii) Print the name of each employee whose salary exceeds the budget of all of the

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

departments that he or she works in.
iv) Find the manager ids of managers who manage only departments with budgets greater than
$1,000,000.
v) Find the enames of managers who manage the departments with the largest budget.
b) What is a trigger? What are the three parts? Differentiate row-level and statement-level

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

triggers. (8M+7M)

1 of 2
R10
SET - 1

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

|''|''|||''|'''|||'|
Code No: R22053

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

5. a) What is join dependency? How is it different to that of multi-valued dependency and
functional dependency? Give an example for join dependencies and multi-valued
dependencies.
b) Contrast 3NF decomposition method with BCNF decomposition method illustratively.

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

(8M+7M)

6. a) What is a serializable schedule? What is a recoverable schedule? What is a schedule that
avoids cascading aborts? What is a strict schedule?
b) Discuss in detail about the phases the recovery manager proceeds when the system is restarted

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

after a crash. (8M+7M)

7. a) Describe in detail about algorithms for updating single level indices.
b) Give comparison of different file organizations. (8M+7M)

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

8. Describe 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.
i) Construct B+ tree for the case where the number of pointer that will fit in one node is four.
ii) Show the step involved to find records with a search-key value of 11. (8M+7M)

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

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

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

2 of 2

R10
SET - 1

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

FirstRanker.com - FirstRanker's Choice
|''|''|||''|'''|||'|
Code No: R22053

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

II B. Tech II Semester Regular Examinations August - 2014
DATA BASE MANAGEMENT SYSTEMS
(Com. to CSE, IT)
Time: 3 hours Max. Marks: 75

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

Answer any FIVE Questions
All Questions carry Equal Marks
~~~~~~~~~~~~~~~~~~~~~~~~

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

1. a) What is data model? List and explain different data models.
b) Explain the difference between external, internal, and conceptual schemas. How are these
different schema layers related to the concepts of logical and physical data independence?
(8M+7M)
2. a) Discuss in detail about the main steps in the database design and clearly focus in detail about

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

the goal of each step? In which steps is the ER model mainly used?
b) Draw and explain E-R diagram of an Airline reservation system? (8M+7M)

3. a) Discuss in detail about integrity constraint over relations.
b) What is join operation in relational algebra? Discuss in detail about variants of joins.

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

(8M+7M)
4. a) Consider the following relational schema. An employee can work in more than one
department; the pct time ?eld of the Works relation shows the percentage of time that a given
employee works in a given department.
Emp(eid: integer, ename: string, age: integer, salary: real)

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

Works(eid: integer, did: integer, pct time: integer)
Dept(did: integer, budget: real, managerid: integer)
Write the following queries in SQL:
i) Print the names and ages of each employee who works in both the Hardware department
and the Software department.

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

ii) For each department with more than 20 full-time-equivalent employees (i.e., where the
part-time and full-time employees add up to at least that many full-time employees), print
the did together with the number of employees that work in that department.
iii) Print the name of each employee whose salary exceeds the budget of all of the
departments that he or she works in.

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

iv) Find the manager ids of managers who manage only departments with budgets greater than
$1,000,000.
v) Find the enames of managers who manage the departments with the largest budget.
b) What is a trigger? What are the three parts? Differentiate row-level and statement-level
triggers. (8M+7M)

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

1 of 2
R10
SET - 1

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

|''|''|||''|'''|||'|
Code No: R22053

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

5. a) What is join dependency? How is it different to that of multi-valued dependency and
functional dependency? Give an example for join dependencies and multi-valued
dependencies.
b) Contrast 3NF decomposition method with BCNF decomposition method illustratively.
(8M+7M)

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

6. a) What is a serializable schedule? What is a recoverable schedule? What is a schedule that
avoids cascading aborts? What is a strict schedule?
b) Discuss in detail about the phases the recovery manager proceeds when the system is restarted
after a crash. (8M+7M)

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

7. a) Describe in detail about algorithms for updating single level indices.
b) Give comparison of different file organizations. (8M+7M)

8. Describe a B+ tree for the following set of key values:(2,3,5,7,11,17,19,23,29,31)

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

Assume that the tree is initially empty and values are added in ascending order.
i) Construct B+ tree for the case where the number of pointer that will fit in one node is four.
ii) Show the step involved to find records with a search-key value of 11. (8M+7M)

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

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

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

2 of 2

R10
SET - 1

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

|''|''|||''|'''|||'|
Code No: R22053

II B. Tech II Semester Regular Examinations August - 2014

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

DATA BASE MANAGEMENT SYSTEMS
(Com. to CSE, IT)
Time: 3 hours Max. Marks: 75

Answer any FIVE Questions

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

All Questions carry Equal Marks
~~~~~~~~~~~~~~~~~~~~~~~~

1. a) What are the responsibilities of a DBA? If we assume that the DBA is never interested in
running his or her own queries, does the DBA still need to understand query optimization?

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

Why?
b) Which of the following plays an important role in representing information about the real
world in a database? Explain briefly about:
i) The data definition language.
ii) The data manipulation language.

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

iii) The buffer manager.
iv) The data model. (7M+8M)

2. a) Describe in detail about conceptual design with ER model.
b) Construct E-R diagram for a banking enterprise. (8M+7M)

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

3. a) Using the following schema represent the following queries using Tuple relational calculus :
PROJECT (Projectnum, Project Name, Project Type, Project Manager)
EMPLOYEE ( Empnum, Empname)
ASSIGNED_TO (Projectnum, Empnum)

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

Find Employee details working on a project name starts with ?L?
List all the employee details who are working under project manager ?Clevee?
List the employees who are still not assigned with any project.
List the employees who are working in more than one project.
b) What is view, updatable and non-updatable views? Explain the advantages of view in

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

maintaining the security. (8M+7M)



1 of 2

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

R10
SET - 2


FirstRanker.com - FirstRanker's Choice

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

|''|''|||''|'''|||'|
Code No: R22053

II B. Tech II Semester Regular Examinations August - 2014
DATA BASE MANAGEMENT SYSTEMS

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

(Com. to CSE, IT)
Time: 3 hours Max. Marks: 75

Answer any FIVE Questions
All Questions carry Equal Marks

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

~~~~~~~~~~~~~~~~~~~~~~~~

1. a) What is data model? List and explain different data models.
b) Explain the difference between external, internal, and conceptual schemas. How are these
different schema layers related to the concepts of logical and physical data independence?

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

(8M+7M)
2. a) Discuss in detail about the main steps in the database design and clearly focus in detail about
the goal of each step? In which steps is the ER model mainly used?
b) Draw and explain E-R diagram of an Airline reservation system? (8M+7M)

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

3. a) Discuss in detail about integrity constraint over relations.
b) What is join operation in relational algebra? Discuss in detail about variants of joins.
(8M+7M)
4. a) Consider the following relational schema. An employee can work in more than one
department; the pct time ?eld of the Works relation shows the percentage of time that a given

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

employee works in a given department.
Emp(eid: integer, ename: string, age: integer, salary: real)
Works(eid: integer, did: integer, pct time: integer)
Dept(did: integer, budget: real, managerid: integer)
Write the following queries in SQL:

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

i) Print the names and ages of each employee who works in both the Hardware department
and the Software department.
ii) For each department with more than 20 full-time-equivalent employees (i.e., where the
part-time and full-time employees add up to at least that many full-time employees), print
the did together with the number of employees that work in that department.

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

iii) Print the name of each employee whose salary exceeds the budget of all of the
departments that he or she works in.
iv) Find the manager ids of managers who manage only departments with budgets greater than
$1,000,000.
v) Find the enames of managers who manage the departments with the largest budget.

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

b) What is a trigger? What are the three parts? Differentiate row-level and statement-level
triggers. (8M+7M)

1 of 2
R10

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

SET - 1


|''|''|||''|'''|||'|
Code No: R22053

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

5. a) What is join dependency? How is it different to that of multi-valued dependency and
functional dependency? Give an example for join dependencies and multi-valued
dependencies.

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

b) Contrast 3NF decomposition method with BCNF decomposition method illustratively.
(8M+7M)

6. a) What is a serializable schedule? What is a recoverable schedule? What is a schedule that
avoids cascading aborts? What is a strict schedule?

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

b) Discuss in detail about the phases the recovery manager proceeds when the system is restarted
after a crash. (8M+7M)

7. a) Describe in detail about algorithms for updating single level indices.
b) Give comparison of different file organizations. (8M+7M)

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

8. Describe 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.
i) Construct B+ tree for the case where the number of pointer that will fit in one node is four.
ii) Show the step involved to find records with a search-key value of 11. (8M+7M)

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

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

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

2 of 2

R10

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

SET - 1


|''|''|||''|'''|||'|
Code No: R22053

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

II B. Tech II Semester Regular Examinations August - 2014
DATA BASE MANAGEMENT SYSTEMS
(Com. to CSE, IT)
Time: 3 hours Max. Marks: 75

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

Answer any FIVE Questions
All Questions carry Equal Marks
~~~~~~~~~~~~~~~~~~~~~~~~

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

1. a) What are the responsibilities of a DBA? If we assume that the DBA is never interested in
running his or her own queries, does the DBA still need to understand query optimization?
Why?
b) Which of the following plays an important role in representing information about the real
world in a database? Explain briefly about:

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

i) The data definition language.
ii) The data manipulation language.
iii) The buffer manager.
iv) The data model. (7M+8M)

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

2. a) Describe in detail about conceptual design with ER model.
b) Construct E-R diagram for a banking enterprise. (8M+7M)

3. a) Using the following schema represent the following queries using Tuple relational calculus :
PROJECT (Projectnum, Project Name, Project Type, Project Manager)

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

EMPLOYEE ( Empnum, Empname)
ASSIGNED_TO (Projectnum, Empnum)
Find Employee details working on a project name starts with ?L?
List all the employee details who are working under project manager ?Clevee?
List the employees who are still not assigned with any project.

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

List the employees who are working in more than one project.
b) What is view, updatable and non-updatable views? Explain the advantages of view in
maintaining the security. (8M+7M)

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

1 of 2
R10
SET - 2

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

|''|''|||''|'''|||'|
Code No: R22053

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

4. a) What are nested queries? What is correlation in nested queries? How would you use the
operators IN, EXISTS, UNIQUE, ANY and ALL in writing nested queries? Why are they
useful? Illustrate your answer by showing how to write the division operator in SQL.
b) Compare constraints and triggers and also give examples for each. (8M+7M)

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

5. a) Consider a relation R with five attributes ABCDE. You are given the following dependencies:
A ? B, BC ? E, and ED ? A.
List all keys for R.
Is R in 3NF?
Is R in BCNF?

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

b) What is decomposition? Describe problems related to decomposition. (9M+6M)

6. a) Is every conflict serializable schedule is serializable? Explain.
b) Explain different types of failures that arise due to loss of non-volatile storage. (8M+7M)

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

7. a) What is an index? Discuss important properties of an index that affect the efficiency of
searches using the index.
b) Describe in detail about different RAID levels. (8M+7M)

8. a) What are the main differences between ISAM and B+ tree indexes?

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

b) Describe 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. Construct B+ tree for the
case where the number of pointer that will fit in one node is six. (6M+9M)

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

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

2 of 2
R10
SET - 2

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

FirstRanker.com - FirstRanker's Choice
|''|''|||''|'''|||'|
Code No: R22053

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

II B. Tech II Semester Regular Examinations August - 2014
DATA BASE MANAGEMENT SYSTEMS
(Com. to CSE, IT)
Time: 3 hours Max. Marks: 75

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

Answer any FIVE Questions
All Questions carry Equal Marks
~~~~~~~~~~~~~~~~~~~~~~~~

1. a) What is data model? List and explain different data models.

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

b) Explain the difference between external, internal, and conceptual schemas. How are these
different schema layers related to the concepts of logical and physical data independence?
(8M+7M)
2. a) Discuss in detail about the main steps in the database design and clearly focus in detail about
the goal of each step? In which steps is the ER model mainly used?

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

b) Draw and explain E-R diagram of an Airline reservation system? (8M+7M)

3. a) Discuss in detail about integrity constraint over relations.
b) What is join operation in relational algebra? Discuss in detail about variants of joins.
(8M+7M)

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

4. a) Consider the following relational schema. An employee can work in more than one
department; the pct time ?eld of the Works relation shows the percentage of time that a given
employee works in a given department.
Emp(eid: integer, ename: string, age: integer, salary: real)
Works(eid: integer, did: integer, pct time: integer)

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

Dept(did: integer, budget: real, managerid: integer)
Write the following queries in SQL:
i) Print the names and ages of each employee who works in both the Hardware department
and the Software department.
ii) For each department with more than 20 full-time-equivalent employees (i.e., where the

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

part-time and full-time employees add up to at least that many full-time employees), print
the did together with the number of employees that work in that department.
iii) Print the name of each employee whose salary exceeds the budget of all of the
departments that he or she works in.
iv) Find the manager ids of managers who manage only departments with budgets greater than

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

$1,000,000.
v) Find the enames of managers who manage the departments with the largest budget.
b) What is a trigger? What are the three parts? Differentiate row-level and statement-level
triggers. (8M+7M)

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

1 of 2
R10
SET - 1

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

|''|''|||''|'''|||'|
Code No: R22053


5. a) What is join dependency? How is it different to that of multi-valued dependency and

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

functional dependency? Give an example for join dependencies and multi-valued
dependencies.
b) Contrast 3NF decomposition method with BCNF decomposition method illustratively.
(8M+7M)

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

6. a) What is a serializable schedule? What is a recoverable schedule? What is a schedule that
avoids cascading aborts? What is a strict schedule?
b) Discuss in detail about the phases the recovery manager proceeds when the system is restarted
after a crash. (8M+7M)

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

7. a) Describe in detail about algorithms for updating single level indices.
b) Give comparison of different file organizations. (8M+7M)

8. Describe 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.

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

i) Construct B+ tree for the case where the number of pointer that will fit in one node is four.
ii) Show the step involved to find records with a search-key value of 11. (8M+7M)

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

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

2 of 2

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

R10
SET - 1

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

|''|''|||''|'''|||'|
Code No: R22053

II B. Tech II Semester Regular Examinations August - 2014
DATA BASE MANAGEMENT SYSTEMS

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

(Com. to CSE, IT)
Time: 3 hours Max. Marks: 75

Answer any FIVE Questions
All Questions carry Equal Marks

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

~~~~~~~~~~~~~~~~~~~~~~~~

1. a) What are the responsibilities of a DBA? If we assume that the DBA is never interested in
running his or her own queries, does the DBA still need to understand query optimization?
Why?

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

b) Which of the following plays an important role in representing information about the real
world in a database? Explain briefly about:
i) The data definition language.
ii) The data manipulation language.
iii) The buffer manager.

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

iv) The data model. (7M+8M)

2. a) Describe in detail about conceptual design with ER model.
b) Construct E-R diagram for a banking enterprise. (8M+7M)

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

3. a) Using the following schema represent the following queries using Tuple relational calculus :
PROJECT (Projectnum, Project Name, Project Type, Project Manager)
EMPLOYEE ( Empnum, Empname)
ASSIGNED_TO (Projectnum, Empnum)
Find Employee details working on a project name starts with ?L?

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

List all the employee details who are working under project manager ?Clevee?
List the employees who are still not assigned with any project.
List the employees who are working in more than one project.
b) What is view, updatable and non-updatable views? Explain the advantages of view in
maintaining the security. (8M+7M)

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

1 of 2
R10

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

SET - 2


|''|''|||''|'''|||'|
Code No: R22053

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

4. a) What are nested queries? What is correlation in nested queries? How would you use the
operators IN, EXISTS, UNIQUE, ANY and ALL in writing nested queries? Why are they
useful? Illustrate your answer by showing how to write the division operator in SQL.

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

b) Compare constraints and triggers and also give examples for each. (8M+7M)

5. a) Consider a relation R with five attributes ABCDE. You are given the following dependencies:
A ? B, BC ? E, and ED ? A.
List all keys for R.

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

Is R in 3NF?
Is R in BCNF?
b) What is decomposition? Describe problems related to decomposition. (9M+6M)

6. a) Is every conflict serializable schedule is serializable? Explain.

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

b) Explain different types of failures that arise due to loss of non-volatile storage. (8M+7M)

7. a) What is an index? Discuss important properties of an index that affect the efficiency of
searches using the index.
b) Describe in detail about different RAID levels. (8M+7M)

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

8. a) What are the main differences between ISAM and B+ tree indexes?
b) Describe 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. Construct B+ tree for the
case where the number of pointer that will fit in one node is six. (6M+9M)

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

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

2 of 2
R10

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

SET - 2


|''|''|||''|'''|||'|
Code No: R22053

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

II B. Tech II Semester Regular Examinations August - 2014
DATA BASE MANAGEMENT SYSTEMS
(Com. to CSE, IT)
Time: 3 hours Max. Marks: 75

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

Answer any FIVE Questions
All Questions carry Equal Marks
~~~~~~~~~~~~~~~~~~~~~~~~

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

1. a) What are application programs? Discuss in detail about database access for application
programs.
b) Explain the difference between logical and physical data independence. What is logical data
independence and why is it important? (8M+7M)

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

2. a) A university database contains information about professors (identified by social security
number, or SSN) and courses (identified by courseid). Professors teach courses; each of the
following situations concerns the Teaches relationship set. For each situation, draw an E-R
diagram that describes it (assuming that no further constraints hold).

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

Professors can teach the same course in several semesters, and each offering must be
recorded.
Professors can teach the same course in several semesters, and only the most recent such
offering needs to be recorded.
Every professor must teach some course.

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

Every professor teaches exactly one course (no more, no less).
Every professor teaches exactly one course (no more, no less), and every course must be
taught by some professor.
b) Explain the difference between weak entity and strong entity set? How to represent the strong
entity and weak entity set through E-R diagram. (10M+5M)

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

3. a) What is the difference between a candidate key and the primary key for a given relation? What
is a super key?
b) Discuss in detail about integrity constraints over relations. (8M+7M)

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

4. a) Discuss the strengths and weaknesses of the trigger mechanism. Contrast triggers with other
integrity constraints supported by SQL.
b) Explain with examples about Logical connectivity?s ? AND, OR and NOT in detail. (8M+7M)

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

1 of 2
R10
SET - 3

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

FirstRanker.com - FirstRanker's Choice
|''|''|||''|'''|||'|
Code No: R22053

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

II B. Tech II Semester Regular Examinations August - 2014
DATA BASE MANAGEMENT SYSTEMS
(Com. to CSE, IT)
Time: 3 hours Max. Marks: 75

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

Answer any FIVE Questions
All Questions carry Equal Marks
~~~~~~~~~~~~~~~~~~~~~~~~

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

1. a) What is data model? List and explain different data models.
b) Explain the difference between external, internal, and conceptual schemas. How are these
different schema layers related to the concepts of logical and physical data independence?
(8M+7M)
2. a) Discuss in detail about the main steps in the database design and clearly focus in detail about

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

the goal of each step? In which steps is the ER model mainly used?
b) Draw and explain E-R diagram of an Airline reservation system? (8M+7M)

3. a) Discuss in detail about integrity constraint over relations.
b) What is join operation in relational algebra? Discuss in detail about variants of joins.

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

(8M+7M)
4. a) Consider the following relational schema. An employee can work in more than one
department; the pct time ?eld of the Works relation shows the percentage of time that a given
employee works in a given department.
Emp(eid: integer, ename: string, age: integer, salary: real)

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

Works(eid: integer, did: integer, pct time: integer)
Dept(did: integer, budget: real, managerid: integer)
Write the following queries in SQL:
i) Print the names and ages of each employee who works in both the Hardware department
and the Software department.

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

ii) For each department with more than 20 full-time-equivalent employees (i.e., where the
part-time and full-time employees add up to at least that many full-time employees), print
the did together with the number of employees that work in that department.
iii) Print the name of each employee whose salary exceeds the budget of all of the
departments that he or she works in.

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

iv) Find the manager ids of managers who manage only departments with budgets greater than
$1,000,000.
v) Find the enames of managers who manage the departments with the largest budget.
b) What is a trigger? What are the three parts? Differentiate row-level and statement-level
triggers. (8M+7M)

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

1 of 2
R10
SET - 1

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

|''|''|||''|'''|||'|
Code No: R22053

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

5. a) What is join dependency? How is it different to that of multi-valued dependency and
functional dependency? Give an example for join dependencies and multi-valued
dependencies.
b) Contrast 3NF decomposition method with BCNF decomposition method illustratively.
(8M+7M)

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

6. a) What is a serializable schedule? What is a recoverable schedule? What is a schedule that
avoids cascading aborts? What is a strict schedule?
b) Discuss in detail about the phases the recovery manager proceeds when the system is restarted
after a crash. (8M+7M)

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

7. a) Describe in detail about algorithms for updating single level indices.
b) Give comparison of different file organizations. (8M+7M)

8. Describe a B+ tree for the following set of key values:(2,3,5,7,11,17,19,23,29,31)

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

Assume that the tree is initially empty and values are added in ascending order.
i) Construct B+ tree for the case where the number of pointer that will fit in one node is four.
ii) Show the step involved to find records with a search-key value of 11. (8M+7M)

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

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

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

2 of 2

R10
SET - 1

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

|''|''|||''|'''|||'|
Code No: R22053

II B. Tech II Semester Regular Examinations August - 2014

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

DATA BASE MANAGEMENT SYSTEMS
(Com. to CSE, IT)
Time: 3 hours Max. Marks: 75

Answer any FIVE Questions

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

All Questions carry Equal Marks
~~~~~~~~~~~~~~~~~~~~~~~~

1. a) What are the responsibilities of a DBA? If we assume that the DBA is never interested in
running his or her own queries, does the DBA still need to understand query optimization?

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

Why?
b) Which of the following plays an important role in representing information about the real
world in a database? Explain briefly about:
i) The data definition language.
ii) The data manipulation language.

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

iii) The buffer manager.
iv) The data model. (7M+8M)

2. a) Describe in detail about conceptual design with ER model.
b) Construct E-R diagram for a banking enterprise. (8M+7M)

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

3. a) Using the following schema represent the following queries using Tuple relational calculus :
PROJECT (Projectnum, Project Name, Project Type, Project Manager)
EMPLOYEE ( Empnum, Empname)
ASSIGNED_TO (Projectnum, Empnum)

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

Find Employee details working on a project name starts with ?L?
List all the employee details who are working under project manager ?Clevee?
List the employees who are still not assigned with any project.
List the employees who are working in more than one project.
b) What is view, updatable and non-updatable views? Explain the advantages of view in

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

maintaining the security. (8M+7M)



1 of 2

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

R10
SET - 2


|''|''|||''|'''|||'|

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

Code No: R22053


4. a) What are nested queries? What is correlation in nested queries? How would you use the
operators IN, EXISTS, UNIQUE, ANY and ALL in writing nested queries? Why are they

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

useful? Illustrate your answer by showing how to write the division operator in SQL.
b) Compare constraints and triggers and also give examples for each. (8M+7M)

5. a) Consider a relation R with five attributes ABCDE. You are given the following dependencies:
A ? B, BC ? E, and ED ? A.

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

List all keys for R.
Is R in 3NF?
Is R in BCNF?
b) What is decomposition? Describe problems related to decomposition. (9M+6M)

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

6. a) Is every conflict serializable schedule is serializable? Explain.
b) Explain different types of failures that arise due to loss of non-volatile storage. (8M+7M)

7. a) What is an index? Discuss important properties of an index that affect the efficiency of
searches using the index.

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

b) Describe in detail about different RAID levels. (8M+7M)

8. a) What are the main differences between ISAM and B+ tree indexes?
b) Describe 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. Construct B+ tree for the

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

case where the number of pointer that will fit in one node is six. (6M+9M)

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

2 of 2

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

R10
SET - 2


|''|''|||''|'''|||'|

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

Code No: R22053

II B. Tech II Semester Regular Examinations August - 2014
DATA BASE MANAGEMENT SYSTEMS
(Com. to CSE, IT)

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

Time: 3 hours Max. Marks: 75

Answer any FIVE Questions
All Questions carry Equal Marks
~~~~~~~~~~~~~~~~~~~~~~~~

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

1. a) What are application programs? Discuss in detail about database access for application
programs.
b) Explain the difference between logical and physical data independence. What is logical data

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

independence and why is it important? (8M+7M)

2. a) A university database contains information about professors (identified by social security
number, or SSN) and courses (identified by courseid). Professors teach courses; each of the
following situations concerns the Teaches relationship set. For each situation, draw an E-R

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

diagram that describes it (assuming that no further constraints hold).
Professors can teach the same course in several semesters, and each offering must be
recorded.
Professors can teach the same course in several semesters, and only the most recent such
offering needs to be recorded.

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

Every professor must teach some course.
Every professor teaches exactly one course (no more, no less).
Every professor teaches exactly one course (no more, no less), and every course must be
taught by some professor.
b) Explain the difference between weak entity and strong entity set? How to represent the strong

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

entity and weak entity set through E-R diagram. (10M+5M)

3. a) What is the difference between a candidate key and the primary key for a given relation? What
is a super key?
b) Discuss in detail about integrity constraints over relations. (8M+7M)

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

4. a) Discuss the strengths and weaknesses of the trigger mechanism. Contrast triggers with other
integrity constraints supported by SQL.
b) Explain with examples about Logical connectivity?s ? AND, OR and NOT in detail. (8M+7M)

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

1 of 2
R10

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

SET - 3


|''|''|||''|'''|||'|
Code No: R22053

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

5. a) Consider the following relation R(A,B,C,D,E)
And FD?s
ABC CA DE FA ED

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

Is the decomposition of R into R
1
(A,C,D), R
2
(B,C,D) and R

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

3
(E,F,D) lossless? Explain the
requirements of lossless decomposition.
b) Explain in detail about Dependency-Preserving Decomposition. Explain why it is important.
(8M+7M)

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

6. a) What overheads are associated with lock-based concurrency control? Discuss blocking and
aborting overheads specifically?
b) Define these terms: atomicity, consistency, isolation, durability, schedule, blind write, dirty
read, unrepeatable read, serializable schedule, recoverable schedule, avoids-cascading- aborts

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

schedule. (8M+7M)

7. a) What are the causes of bucket overflow in a hash file organization? What can be done to
reduce the occurrence of bucket overflows?
b) Discuss about multilevel indices in detail. (8M+7M)

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

8. a) Explain main characteristics of a B+ tree in detail. Discuss operations on B+ trees.
b) Describe 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. Construct B+
tree for the case where the number of pointer that will fit in one node is six. (6M+9M)

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

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

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

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

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

2 of 2
R10
SET - 3

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

FirstRanker.com - FirstRanker's Choice
|''|''|||''|'''|||'|
Code No: R22053

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

II B. Tech II Semester Regular Examinations August - 2014
DATA BASE MANAGEMENT SYSTEMS
(Com. to CSE, IT)
Time: 3 hours Max. Marks: 75

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

Answer any FIVE Questions
All Questions carry Equal Marks
~~~~~~~~~~~~~~~~~~~~~~~~

1. a) What is data model? List and explain different data models.

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

b) Explain the difference between external, internal, and conceptual schemas. How are these
different schema layers related to the concepts of logical and physical data independence?
(8M+7M)
2. a) Discuss in detail about the main steps in the database design and clearly focus in detail about
the goal of each step? In which steps is the ER model mainly used?

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

b) Draw and explain E-R diagram of an Airline reservation system? (8M+7M)

3. a) Discuss in detail about integrity constraint over relations.
b) What is join operation in relational algebra? Discuss in detail about variants of joins.
(8M+7M)

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

4. a) Consider the following relational schema. An employee can work in more than one
department; the pct time ?eld of the Works relation shows the percentage of time that a given
employee works in a given department.
Emp(eid: integer, ename: string, age: integer, salary: real)
Works(eid: integer, did: integer, pct time: integer)

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

Dept(did: integer, budget: real, managerid: integer)
Write the following queries in SQL:
i) Print the names and ages of each employee who works in both the Hardware department
and the Software department.
ii) For each department with more than 20 full-time-equivalent employees (i.e., where the

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

part-time and full-time employees add up to at least that many full-time employees), print
the did together with the number of employees that work in that department.
iii) Print the name of each employee whose salary exceeds the budget of all of the
departments that he or she works in.
iv) Find the manager ids of managers who manage only departments with budgets greater than

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

$1,000,000.
v) Find the enames of managers who manage the departments with the largest budget.
b) What is a trigger? What are the three parts? Differentiate row-level and statement-level
triggers. (8M+7M)

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

1 of 2
R10
SET - 1

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

|''|''|||''|'''|||'|
Code No: R22053


5. a) What is join dependency? How is it different to that of multi-valued dependency and

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

functional dependency? Give an example for join dependencies and multi-valued
dependencies.
b) Contrast 3NF decomposition method with BCNF decomposition method illustratively.
(8M+7M)

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

6. a) What is a serializable schedule? What is a recoverable schedule? What is a schedule that
avoids cascading aborts? What is a strict schedule?
b) Discuss in detail about the phases the recovery manager proceeds when the system is restarted
after a crash. (8M+7M)

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

7. a) Describe in detail about algorithms for updating single level indices.
b) Give comparison of different file organizations. (8M+7M)

8. Describe 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.

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

i) Construct B+ tree for the case where the number of pointer that will fit in one node is four.
ii) Show the step involved to find records with a search-key value of 11. (8M+7M)

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

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

2 of 2

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

R10
SET - 1

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

|''|''|||''|'''|||'|
Code No: R22053

II B. Tech II Semester Regular Examinations August - 2014
DATA BASE MANAGEMENT SYSTEMS

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

(Com. to CSE, IT)
Time: 3 hours Max. Marks: 75

Answer any FIVE Questions
All Questions carry Equal Marks

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

~~~~~~~~~~~~~~~~~~~~~~~~

1. a) What are the responsibilities of a DBA? If we assume that the DBA is never interested in
running his or her own queries, does the DBA still need to understand query optimization?
Why?

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

b) Which of the following plays an important role in representing information about the real
world in a database? Explain briefly about:
i) The data definition language.
ii) The data manipulation language.
iii) The buffer manager.

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

iv) The data model. (7M+8M)

2. a) Describe in detail about conceptual design with ER model.
b) Construct E-R diagram for a banking enterprise. (8M+7M)

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

3. a) Using the following schema represent the following queries using Tuple relational calculus :
PROJECT (Projectnum, Project Name, Project Type, Project Manager)
EMPLOYEE ( Empnum, Empname)
ASSIGNED_TO (Projectnum, Empnum)
Find Employee details working on a project name starts with ?L?

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

List all the employee details who are working under project manager ?Clevee?
List the employees who are still not assigned with any project.
List the employees who are working in more than one project.
b) What is view, updatable and non-updatable views? Explain the advantages of view in
maintaining the security. (8M+7M)

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

1 of 2
R10

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

SET - 2


|''|''|||''|'''|||'|
Code No: R22053

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

4. a) What are nested queries? What is correlation in nested queries? How would you use the
operators IN, EXISTS, UNIQUE, ANY and ALL in writing nested queries? Why are they
useful? Illustrate your answer by showing how to write the division operator in SQL.

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

b) Compare constraints and triggers and also give examples for each. (8M+7M)

5. a) Consider a relation R with five attributes ABCDE. You are given the following dependencies:
A ? B, BC ? E, and ED ? A.
List all keys for R.

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

Is R in 3NF?
Is R in BCNF?
b) What is decomposition? Describe problems related to decomposition. (9M+6M)

6. a) Is every conflict serializable schedule is serializable? Explain.

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

b) Explain different types of failures that arise due to loss of non-volatile storage. (8M+7M)

7. a) What is an index? Discuss important properties of an index that affect the efficiency of
searches using the index.
b) Describe in detail about different RAID levels. (8M+7M)

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

8. a) What are the main differences between ISAM and B+ tree indexes?
b) Describe 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. Construct B+ tree for the
case where the number of pointer that will fit in one node is six. (6M+9M)

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

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

2 of 2
R10

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

SET - 2


|''|''|||''|'''|||'|
Code No: R22053

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

II B. Tech II Semester Regular Examinations August - 2014
DATA BASE MANAGEMENT SYSTEMS
(Com. to CSE, IT)
Time: 3 hours Max. Marks: 75

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

Answer any FIVE Questions
All Questions carry Equal Marks
~~~~~~~~~~~~~~~~~~~~~~~~

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

1. a) What are application programs? Discuss in detail about database access for application
programs.
b) Explain the difference between logical and physical data independence. What is logical data
independence and why is it important? (8M+7M)

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

2. a) A university database contains information about professors (identified by social security
number, or SSN) and courses (identified by courseid). Professors teach courses; each of the
following situations concerns the Teaches relationship set. For each situation, draw an E-R
diagram that describes it (assuming that no further constraints hold).

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

Professors can teach the same course in several semesters, and each offering must be
recorded.
Professors can teach the same course in several semesters, and only the most recent such
offering needs to be recorded.
Every professor must teach some course.

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

Every professor teaches exactly one course (no more, no less).
Every professor teaches exactly one course (no more, no less), and every course must be
taught by some professor.
b) Explain the difference between weak entity and strong entity set? How to represent the strong
entity and weak entity set through E-R diagram. (10M+5M)

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

3. a) What is the difference between a candidate key and the primary key for a given relation? What
is a super key?
b) Discuss in detail about integrity constraints over relations. (8M+7M)

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

4. a) Discuss the strengths and weaknesses of the trigger mechanism. Contrast triggers with other
integrity constraints supported by SQL.
b) Explain with examples about Logical connectivity?s ? AND, OR and NOT in detail. (8M+7M)

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

1 of 2
R10
SET - 3

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

|''|''|||''|'''|||'|
Code No: R22053

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

5. a) Consider the following relation R(A,B,C,D,E)
And FD?s
ABC CA DE FA ED
Is the decomposition of R into R

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

1
(A,C,D), R
2
(B,C,D) and R
3

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

(E,F,D) lossless? Explain the
requirements of lossless decomposition.
b) Explain in detail about Dependency-Preserving Decomposition. Explain why it is important.
(8M+7M)

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

6. a) What overheads are associated with lock-based concurrency control? Discuss blocking and
aborting overheads specifically?
b) Define these terms: atomicity, consistency, isolation, durability, schedule, blind write, dirty
read, unrepeatable read, serializable schedule, recoverable schedule, avoids-cascading- aborts
schedule. (8M+7M)

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

7. a) What are the causes of bucket overflow in a hash file organization? What can be done to
reduce the occurrence of bucket overflows?
b) Discuss about multilevel indices in detail. (8M+7M)

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

8. a) Explain main characteristics of a B+ tree in detail. Discuss operations on B+ trees.
b) Describe 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. Construct B+
tree for the case where the number of pointer that will fit in one node is six. (6M+9M)

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

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

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

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

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

2 of 2
R10
SET - 3

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

|''|''|||''|'''|||'|
Code No: R22053

II B. Tech II Semester Regular Examinations August - 2014
DATA BASE MANAGEMENT SYSTEMS

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

(Com. to CSE, IT)
Time: 3 hours Max. Marks: 75

Answer any FIVE Questions
All Questions carry Equal Marks

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

~~~~~~~~~~~~~~~~~~~~~~~~

1. a) Why would choose a database system instead of simply storing data in operating system files?
When would it make sense not to use a database system?
b) Shows the structures of a typical DBMS based on the relational data model and explain in

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

detail. (8M+7M)

2. a) Construct an E-R diagram for a university registrar?s office. The office maintains data about
each class, including the instructor the enrollment, and the time and place of the class
meetings. For each student-class pair, a grade is recorded. Document all assumptions that you

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

make about the mapping constraints.
b) Explain the difference between weak entity and strong entity sets? How to represent the strong
and weak entity set through E-R diagrams. (8M+7M)

3. a) What restrictions are necessary to ensure that view is updatable? State any three advantages

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

and three disadvantages of views.
b) Discuss in detail about the set operations of relational algebra and explain with examples.
(8M+7M)
4. a) The following relations keep track of airline flight information:
Flights(flno: integer, from: string, to: string, distance: integer, departs: time, arrives: time,

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

price: integer) Aircraft(aid: integer, aname: string, cruisingrange: integer)
Certified(eid: integer, aid: integer)
Employees(eid: integer, ename: string, salary: integer)
Note that the Employees relation describes pilots and other kinds of employees as well; every
pilot is certified for some aircraft, and only pilots are certified to fly. Write each of the

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

following queries in SQL.
i) Find the names of aircraft such that all pilots certified to operate them earn more than
80,000.
ii) For each pilot who is certified for more than three aircraft, find the eid and the maximum
cruisingrange of the aircraft that he (or she) is certified for.

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

iii) Find the names of pilots whose salary is less than the price of the cheapest route from Los
Angeles to Honolulu.
iv) For all aircraft with cruising range over 1,000 miles, find the name of the aircraft and the
average salary of all pilots certified for this aircraft.
b) Explain in detail about different aggregative operators in SQL with example queries.

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

(8M+7M)

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

1 of 2
R10
SET - 4

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

FirstRanker.com - FirstRanker's Choice
|''|''|||''|'''|||'|
Code No: R22053

II B. Tech II Semester Regular Examinations August - 2014

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

DATA BASE MANAGEMENT SYSTEMS
(Com. to CSE, IT)
Time: 3 hours Max. Marks: 75

Answer any FIVE Questions

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

All Questions carry Equal Marks
~~~~~~~~~~~~~~~~~~~~~~~~

1. a) What is data model? List and explain different data models.
b) Explain the difference between external, internal, and conceptual schemas. How are these

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

different schema layers related to the concepts of logical and physical data independence?
(8M+7M)
2. a) Discuss in detail about the main steps in the database design and clearly focus in detail about
the goal of each step? In which steps is the ER model mainly used?
b) Draw and explain E-R diagram of an Airline reservation system? (8M+7M)

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

3. a) Discuss in detail about integrity constraint over relations.
b) What is join operation in relational algebra? Discuss in detail about variants of joins.
(8M+7M)
4. a) Consider the following relational schema. An employee can work in more than one

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

department; the pct time ?eld of the Works relation shows the percentage of time that a given
employee works in a given department.
Emp(eid: integer, ename: string, age: integer, salary: real)
Works(eid: integer, did: integer, pct time: integer)
Dept(did: integer, budget: real, managerid: integer)

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

Write the following queries in SQL:
i) Print the names and ages of each employee who works in both the Hardware department
and the Software department.
ii) For each department with more than 20 full-time-equivalent employees (i.e., where the
part-time and full-time employees add up to at least that many full-time employees), print

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

the did together with the number of employees that work in that department.
iii) Print the name of each employee whose salary exceeds the budget of all of the
departments that he or she works in.
iv) Find the manager ids of managers who manage only departments with budgets greater than
$1,000,000.

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

v) Find the enames of managers who manage the departments with the largest budget.
b) What is a trigger? What are the three parts? Differentiate row-level and statement-level
triggers. (8M+7M)

1 of 2

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

R10
SET - 1


|''|''|||''|'''|||'|

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

Code No: R22053


5. a) What is join dependency? How is it different to that of multi-valued dependency and
functional dependency? Give an example for join dependencies and multi-valued

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

dependencies.
b) Contrast 3NF decomposition method with BCNF decomposition method illustratively.
(8M+7M)

6. a) What is a serializable schedule? What is a recoverable schedule? What is a schedule that

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

avoids cascading aborts? What is a strict schedule?
b) Discuss in detail about the phases the recovery manager proceeds when the system is restarted
after a crash. (8M+7M)

7. a) Describe in detail about algorithms for updating single level indices.

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

b) Give comparison of different file organizations. (8M+7M)

8. Describe 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.
i) Construct B+ tree for the case where the number of pointer that will fit in one node is four.

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

ii) Show the step involved to find records with a search-key value of 11. (8M+7M)

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

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

2 of 2

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

R10
SET - 1


|''|''|||''|'''|||'|

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

Code No: R22053

II B. Tech II Semester Regular Examinations August - 2014
DATA BASE MANAGEMENT SYSTEMS
(Com. to CSE, IT)

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

Time: 3 hours Max. Marks: 75

Answer any FIVE Questions
All Questions carry Equal Marks
~~~~~~~~~~~~~~~~~~~~~~~~

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

1. a) What are the responsibilities of a DBA? If we assume that the DBA is never interested in
running his or her own queries, does the DBA still need to understand query optimization?
Why?
b) Which of the following plays an important role in representing information about the real

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

world in a database? Explain briefly about:
i) The data definition language.
ii) The data manipulation language.
iii) The buffer manager.
iv) The data model. (7M+8M)

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

2. a) Describe in detail about conceptual design with ER model.
b) Construct E-R diagram for a banking enterprise. (8M+7M)

3. a) Using the following schema represent the following queries using Tuple relational calculus :

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

PROJECT (Projectnum, Project Name, Project Type, Project Manager)
EMPLOYEE ( Empnum, Empname)
ASSIGNED_TO (Projectnum, Empnum)
Find Employee details working on a project name starts with ?L?
List all the employee details who are working under project manager ?Clevee?

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

List the employees who are still not assigned with any project.
List the employees who are working in more than one project.
b) What is view, updatable and non-updatable views? Explain the advantages of view in
maintaining the security. (8M+7M)

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

1 of 2
R10
SET - 2

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

|''|''|||''|'''|||'|
Code No: R22053

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

4. a) What are nested queries? What is correlation in nested queries? How would you use the
operators IN, EXISTS, UNIQUE, ANY and ALL in writing nested queries? Why are they
useful? Illustrate your answer by showing how to write the division operator in SQL.
b) Compare constraints and triggers and also give examples for each. (8M+7M)

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

5. a) Consider a relation R with five attributes ABCDE. You are given the following dependencies:
A ? B, BC ? E, and ED ? A.
List all keys for R.
Is R in 3NF?

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

Is R in BCNF?
b) What is decomposition? Describe problems related to decomposition. (9M+6M)

6. a) Is every conflict serializable schedule is serializable? Explain.
b) Explain different types of failures that arise due to loss of non-volatile storage. (8M+7M)

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

7. a) What is an index? Discuss important properties of an index that affect the efficiency of
searches using the index.
b) Describe in detail about different RAID levels. (8M+7M)

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

8. a) What are the main differences between ISAM and B+ tree indexes?
b) Describe 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. Construct B+ tree for the
case where the number of pointer that will fit in one node is six. (6M+9M)

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

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

2 of 2
R10
SET - 2

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

|''|''|||''|'''|||'|
Code No: R22053

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

II B. Tech II Semester Regular Examinations August - 2014
DATA BASE MANAGEMENT SYSTEMS
(Com. to CSE, IT)
Time: 3 hours Max. Marks: 75

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

Answer any FIVE Questions
All Questions carry Equal Marks
~~~~~~~~~~~~~~~~~~~~~~~~

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

1. a) What are application programs? Discuss in detail about database access for application
programs.
b) Explain the difference between logical and physical data independence. What is logical data
independence and why is it important? (8M+7M)

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

2. a) A university database contains information about professors (identified by social security
number, or SSN) and courses (identified by courseid). Professors teach courses; each of the
following situations concerns the Teaches relationship set. For each situation, draw an E-R
diagram that describes it (assuming that no further constraints hold).
Professors can teach the same course in several semesters, and each offering must be

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

recorded.
Professors can teach the same course in several semesters, and only the most recent such
offering needs to be recorded.
Every professor must teach some course.
Every professor teaches exactly one course (no more, no less).

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

Every professor teaches exactly one course (no more, no less), and every course must be
taught by some professor.
b) Explain the difference between weak entity and strong entity set? How to represent the strong
entity and weak entity set through E-R diagram. (10M+5M)

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

3. a) What is the difference between a candidate key and the primary key for a given relation? What
is a super key?
b) Discuss in detail about integrity constraints over relations. (8M+7M)

4. a) Discuss the strengths and weaknesses of the trigger mechanism. Contrast triggers with other

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

integrity constraints supported by SQL.
b) Explain with examples about Logical connectivity?s ? AND, OR and NOT in detail. (8M+7M)

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

1 of 2
R10
SET - 3

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

|''|''|||''|'''|||'|
Code No: R22053

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

5. a) Consider the following relation R(A,B,C,D,E)
And FD?s
ABC CA DE FA ED
Is the decomposition of R into R
1

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

(A,C,D), R
2
(B,C,D) and R
3
(E,F,D) lossless? Explain the

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

requirements of lossless decomposition.
b) Explain in detail about Dependency-Preserving Decomposition. Explain why it is important.
(8M+7M)

6. a) What overheads are associated with lock-based concurrency control? Discuss blocking and

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

aborting overheads specifically?
b) Define these terms: atomicity, consistency, isolation, durability, schedule, blind write, dirty
read, unrepeatable read, serializable schedule, recoverable schedule, avoids-cascading- aborts
schedule. (8M+7M)

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

7. a) What are the causes of bucket overflow in a hash file organization? What can be done to
reduce the occurrence of bucket overflows?
b) Discuss about multilevel indices in detail. (8M+7M)

8. a) Explain main characteristics of a B+ tree in detail. Discuss operations on B+ trees.

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

b) Describe 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. Construct B+
tree for the case where the number of pointer that will fit in one node is six. (6M+9M)

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

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

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

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

2 of 2

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

R10
SET - 3


|''|''|||''|'''|||'|

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

Code No: R22053

II B. Tech II Semester Regular Examinations August - 2014
DATA BASE MANAGEMENT SYSTEMS
(Com. to CSE, IT)

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

Time: 3 hours Max. Marks: 75

Answer any FIVE Questions
All Questions carry Equal Marks
~~~~~~~~~~~~~~~~~~~~~~~~

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

1. a) Why would choose a database system instead of simply storing data in operating system files?
When would it make sense not to use a database system?
b) Shows the structures of a typical DBMS based on the relational data model and explain in
detail. (8M+7M)

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

2. a) Construct an E-R diagram for a university registrar?s office. The office maintains data about
each class, including the instructor the enrollment, and the time and place of the class
meetings. For each student-class pair, a grade is recorded. Document all assumptions that you
make about the mapping constraints.

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

b) Explain the difference between weak entity and strong entity sets? How to represent the strong
and weak entity set through E-R diagrams. (8M+7M)

3. a) What restrictions are necessary to ensure that view is updatable? State any three advantages
and three disadvantages of views.

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

b) Discuss in detail about the set operations of relational algebra and explain with examples.
(8M+7M)
4. a) The following relations keep track of airline flight information:
Flights(flno: integer, from: string, to: string, distance: integer, departs: time, arrives: time,
price: integer) Aircraft(aid: integer, aname: string, cruisingrange: integer)

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

Certified(eid: integer, aid: integer)
Employees(eid: integer, ename: string, salary: integer)
Note that the Employees relation describes pilots and other kinds of employees as well; every
pilot is certified for some aircraft, and only pilots are certified to fly. Write each of the
following queries in SQL.

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

i) Find the names of aircraft such that all pilots certified to operate them earn more than
80,000.
ii) For each pilot who is certified for more than three aircraft, find the eid and the maximum
cruisingrange of the aircraft that he (or she) is certified for.
iii) Find the names of pilots whose salary is less than the price of the cheapest route from Los

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

Angeles to Honolulu.
iv) For all aircraft with cruising range over 1,000 miles, find the name of the aircraft and the
average salary of all pilots certified for this aircraft.
b) Explain in detail about different aggregative operators in SQL with example queries.
(8M+7M)

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

1 of 2

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

R10
SET - 4


|''|''|||''|'''|||'|

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

Code No: R22053



5. a) What is schema refinement? Discuss the problems caused by redundancy.

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

b) Give a set of FDs for the relation schema R(A,B,C,D) with primary key AB under which R is
in 2NF but not in 3NF. (8M+7M)

6. a) Explain various types of lock based concurrency control with a neat sketch and examples.
b) Describe in detail about shadow paging recovery technique. Under what circumstances does it

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

not require a log? (8M+7M)

7. a) Explain the distinction between closed and open hashing. Discuss the relative merits of each
technique in database applications.
b) On what factors techniques for indexing and hashing must be evaluated? Explain. (8M+7M)

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

8. Explain all the operations on B+ tree by taking a sample example. (15M)

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

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

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

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

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

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

2 of 2
R10

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

SET - 4


FirstRanker.com - FirstRanker's Choice