UNIT 3

1. List and explain the SQL functions for string conversions.

2. Explain the numeric and date SQL functions.
3. Explain Group by and Having clauses in SQL.
4. How would you use the operators IN, EXISTS, UNIQUE, ANY and ALL in writing nested
queries? Why are they useful? Explain with an example.
5. Discuss about updatable views. What is the need to restrict view updates?
6. What are the Union-Compatibility rules? Explain different types of joins with suitable examples
and their representation in SQL with suitable syntax and example.
7. Consider the following schema of airline flight information:
 Flights(flno: integer, from: string, to: string, distance: integer, departs: time, arrives:time,
price: real) ;
 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.
Answer the following queries in SQL using suitable joins, sub queries and different aggregate
functions over group by.
i) List the names of aircrafts for which there exist a certified pilot to operate whose salary more than
$80,000.
ii) List the names of pilots who is certified for more than three aircraft.
iii) List the eid, aid and the maximum cruisingrange of the aircrafts for which pilot is certified.
iv) Find the names of pilots whose salary is less than the salary of the all Employees.
v) List all aircrafts details with cruisingrange over 1000 miles.
vi) List the eid, aid and cruisingrange of pilots where cruising range is more than 1500.
vii) List the eid, salary of those employees who are getting more salary than average salary of pilots.
viii) List the aid and average salary of pilots who are certified to fly the aircraft which is having
highest cruisingrange.

8. Explain different types of joins with suitable examples. Apply suitable joins and aggregate function
using the following schema and write SQL statements to answer the following queries:EMP(empid,
ename, dob, designation, sal, comm, hiredate, deptno), DEPT(deptno, dname, loc)
Answer the following queries in SQL using suitable joins, sub queries and different aggregate
functions over group by.
 List deptno and number of employee in each department.
 List the employee and department details by joining both tables to ensure that department
details are listed even though no employee exists in a department.
 List the maximum, minimum and average salary of employee department wise.
 List the ename of employees who joined on ‘Wednesday” in first quarter.
 List the ename of employees whose name starts with ‘S’ and contains ‘r’ as third character.
 List the ename of employees whose name is ending with ‘d’ and having length >10.
 List the empno, ename and salary of employees who are getting more than average salary of
their department
 List the empno, ename and salary of employees whose department is located in ‘Hyderabad’
 List the empno, ename and salary of employees salary more than average salary paid to
employee working in ‘Hyderabad’
 UNIT 4
1. Define functional dependency and state 1NF, 2NF & 3NF. Explain the steps in converting the
relation in to these with suitable example(s).
2. Define BCNF. How does BCNF differ from 3NF? Explain with an example how to convert the
given relation into 3NF and BCNF.
3. Give a brief on Multivalued Dependency and Join dependency and explain Fourth and Fifth
Normal Form with suitable example.
4. Discuss the requirements for schema refinement and principles of dependency preserving and
property for decomposition with example. Explain why it is important and how to achieve it.
5. Explain insertion, deletion, and update anomalies with suitable examples.
6. Consider the relation R(A,B,C,D,E,F) and the FDs ABC→DE, ABC→D, DE→ABCE, E→C.
Decompose the relation by considering the given set of FDs into BCNF relation. Is the
decomposition lossless and dependency preserving?
7. What is dependency closure F+ of a set of FDs? What the attribute closure X+ of a set of attributes
X with respect to a set of FDs F? Explain how to compute closure of set of functional dependency
with a suitable example schema.
8. Explain the requirement of Lossless decomposition. Consider relation R(A,B,C,D,E,F) and FDs
A→BC, F→A,C → AD → E, E → D, AD is the decomposition of R into R1(A,C,D) R2 (B,C,D)
and R3 (E,F,D) loss less?
9. Describe an algorithm for computing the minimal cover of a set of Functional Dependencies, how
to compute it and illustrates its application in 3NF with an example? Construct minimal cover for
set of functional dependencies which are: E : { B → A, D → A, AB →D}
10. Given a Relation R=(A,B,C,D,E) and Functional Dependencies are: F={BC→D, AC→BE, B→E}
Determine all Candidate keys of R and the highest normal form of R with proper explanation.
11. Given a Relation R=(A,B,C,D,E) and Functional Dependencies are: F={BC→D, AC→BE,
B→E}. Determine all Candidate keys of R and the highest normal form of R with proper
explanation.
UNIT 5
1. Define locking protocol. Describe the Strict Two-Phase Locking protocol and its role in ensuring
the concurrency of transactions with an example.
2. Explain the concept of deadlock and dead lock prevention policies with an example.
3. Explain the need of commit, rollback and save point operations in transaction management.
4. Explain the Timestamp based concurrency control mechanism.
5. Write about concurrent executions. How do they enforce integrity and recoverability? Explain with
example.
6. Discuss various anomalies that arise due to interleaved execution of transactions with suitable
examples for each.
7. How does a checkpoint mechanism help in database recovery? Explain through an example
8. Define the terms transaction, schedule, complete schedule, serial schedule and serializable
schedule. Consider the following two transactions Write a concurrent execution of T1 and T2 that
produces a non-serializable schedule:
T1: read(A);
read(B);
if A = 0 then B := B + 1;
write(B).

T2: read(B);
read(A);
if B = 0 then A := A + 1;
write(A).
9. Explain Hash indexes and B+ tree indexes and explore the distinguishing characteristics of each
and explain the importance of each of these indexing approaches.
10.What is a B+ Tree? Describe the properties of B+ trees. Explain the structure of internal and
external nodes in B+ tree. How the records are organized in Heap and Sequential files? Explain.
11.Construct a B+ tree for the following set of key values (2,5,7,9,8,12,17,14,6,22,18,4,5,10). Show
the trace for each of the insertions in the given order and deletion of 12 and 18.
12.Construct a B+ Tree for the following list of elements: 1, 4, 7, 10, 17, 21, 31, 25, 19, 20, 28, 42.
Assume the initial tree is empty and a node can hold maximum 3 key values. Give trace of
construction and followed by deletion of any two elements.
13.Explain the following: i) Primary index ii) Secondary index iii) Clustered index.