CS2255 Database Management Systems question paper - May/June 2013 / Anna University Previous Year 2013 Exam Questions / Anna University Previous Year 2013 Computer Science and Information Technology Question Papers
Question
Paper Code : 21305
B.E./B.Tech. DEGREE EXAMINATION, MAY/JUNE
2013
Fourth Semester
Computer Science and Engineering
CS 2255/CS 46/CS 1254/10144 CS
406/08025009 — DATABASE MANAGEMENT SYSTEMS
(Common to Information Technology)
(Regulation 2008/2010)
(Common to PTCS 2255 – Database Management
Systems for B.E. (Part-Time) Third Semester – Computer Science and Engineering
(Regulation 2009))
Time: Three hours Maximum:
100 Marks
Answer ALL Questions
PART
A — (10 × 2 = 20 Marks)
1.
Define Atomicity in Transaction management.
2.
Give example for one to one and one to many relationship.
3.
What are primary key constraints?
4.
Write the purpose of Trigger.
5.
Define Boyce Codd Normal Form.
6.
What is the need for Normalization?
8.
Define two phase locking.
9.
What is the need for RAID?
10.
What is the basic difference between static hashing and dynamic hashing?
PART
B — (5 × 16 = 80 Marks)
11.
(a) Explain the purpose of database system
Or
(b) Write about the structure of database
system architecture with block diagram.
12.
Consider the following relational database
Employee (Employee-Name, Street, City)
Works (Employee-Name, Company-Name,
Salary)
Company (Company-Name, City)
Manager (Employee-Name, Manager-Name)
Give
an SQL DDL definition of this database. Identify referential integrity constraints
that should hold, and include them in the DDL definition.
Or
(b) Consider the following relation
Employee
(Employee-Name, Company-Name, Salary)
Write
SQL for the following: (4
X 4 = 16)
(i) Find the total salary of each
company
(ii) Find the employee name who is
getting lowest salary
(iii) Find the company name which has
lowest average salary
(iv) Find the employee name whose
salary is higher than average salary of TCS.
13.
(a) Consider the following relation
CAR-SALE
(Car#, Date-Sold, Salesman#, Commission%, Discount-amount)
Assume
that a car may be sold by multiple salesmen, and hence (Car#, Salesman#) is the
primary key.
Additional
dependencies are,
Date-Sold -->
Discount-amount
and
Salesman# --> Commission%
Based
on the given primary key, is this relation in 1NF, 2NF, or 3NF? Why or why not?
How would you successively normalize it completely?
Or
(b) Explain the principles of
(i) Loss less join decomposition (5)
(ii) Join dependencies (5)
(iii) Fifth normal form (6)
14.
(a) Illustrate deadlock and conflict serializability with suitable example.
Or
(b)
(i) Explain two phase commit protocol. (10)
(ii) Write different SQL facilities for
recovery (6)
15.
(a) Construct B+ tree to insert the following (order of the tree is 3)
26,
27, 28, 3, 4, 7, 9, 46, 48, 51, 2, 6.
Or
(b) Let relations r1(A, B, C) and r2(C, D, E)
have the following properties:
r1
has 20,000 tuples, r2 has 45,000 tuples, 25 tuples of r1 fit on one block and
30 tuples of r2 fit on one block. Estimate the number of block transfers and
seeks required, using each of the following Join strategies for r1⋈ r2:
(i) Nested-loop join. (4)
(ii) Block nested-loop join. (8)
(iii) Merge join. (4)
_____________________________