Database Management Systems - Visvesvaraya Technological University December 2013 January 2014 Questions / December 2013 January 2014 DBMS questions of Visvesvaraya Technological University / DBMS University questions with answers
USN 10CS54
Fifth
Semester B.E. Degree Examination, Dec. 2013/Jan. 2014
Database Management Systems
Time:
3 hrs. Max.
Marks: 100
Note: Answer any FIVE
full questions, selecting at least TWO questions from each part.
PART – A
1. a. What are
the responsibilities of DBA and Database designers? (04 marks)
b. With a neat
diagram, explain the component modules of DBMS and their interactions. (08
marks)
c. List the
advantages and disadvantages of DBMS. Discuss any five advantages by comparing
with files systems. (08 marks)
2. a. Define
the following terms: i) Recursive relationship ii) Weak entity type iii)
Atomic attributes iv) Participation role. (04 marks)
b. Discuss the
conventions for displaying an ER schema as an ER diagram. (04 marks)
c. Draw an ER
diagram for Musicians who perform for album. Assume any four entities.
Indicate all key and cardinality constraints and any assumptions that are
made. (12 marks)
3. a. List and
explain characteristics of relations. (06 marks)
b. List set
theory operations used in relational data model. Explain any two with
example. (06 marks)
c. Consider
the following relations for a sailor database that keeps track of reservation
of boats by sailors.
SAILORS (SID,
SNAME, RATING, AGE)
BOATS (BID, BNAME,
COLOR)
RESERVES (SID,
BID, DAY)
Specify the
following queries in relational algebra.
i) Find the
SIDs of sailors with age over 20 who have not reserved a ‘Red’ boat.
ii) Find the
names of sailors who have reserved all boats.
iii) Find the
names of sailors who have reserved boat 103. (08
marks)
4. a. Describe
the six clauses in the syntax of an SQL retrieval query. Show what type of
constructs can be specified in each of the six clauses. Which of the six
clauses are required and which are optional. (06
marks)
b. Explain how
the GROUP BY clause works. What is the difference between WHERE and HAVING
clause. (04 marks)
c. Consider
the following relations of a database;
Supplier (Sno,
Sname, Status, City)
Product (Pno, Pname,
Color, Weight, City)
Shipments
(Sno, Pno, Qty)
Specify the
following queries in SQL.
i) Retrieve
names of supplier who supply part P2.
ii) Retrieve
the names of suppliers who do not supply any part supplied by S2.
iii) Retrieve
parts number for all parts supplied by more than one supplier.
iv) For each
part supplied, get the part number, maximum quantity, minimum quantity supplied
for that part.
v) Retrieve
supplier numbers for suppliers with status less than the current maximum in
the supplier table. (10 Marks)
PART – B
5. a. List the
differences between Independent nested and co-related nested query. (04
marks)
b. Discuss
main approaches to database programming. What you mean by Impedance mismatch.
(08 Marks)
c. With
program segment, explain retrieving of tuples with embedded SQL. (08 Marks)
6. a. Discuss
insertion, deletion and modification anomalies. Why are they considered bad?
Illustrate with examples. (08 Marks)
b. What you
mean by closure of attribute? Write an algorithm to find closure of attribute.
(06 Marks)
c. Given below
are two sets of FDs for a relation R(A, B, C, D, E). Are they equivalent?
i) A → B , AB
→ C, D → AC, D → E ii) A →
BC, D → AE (06 Marks)
7. a. Consider
the following Universal relation
R = {A, B, C,
D, E, F, G, H, I, J} and the set of functional dependencies.
F = {{A, B} →
C, A → {D, E}, B → F, F → {G, H}, D → {I, J}}. What is the key of R? Decompose R
into 2NF, then 3NF relations. (08 Marks)
b. What is the
dependency preservation property for a decomposition? Why is it important? (06
Marks)
c. Define
fourth normal form. When is it violated? Why is it useful? (06 Marks)
8. a. What are
the anomalies occur due to interleave execution? Explain them with example. (08 Marks)
b. Consider the three transaction T1, T2, and T3 and schedules S1 and
S2 given below. Determine whether each schedule is serializable or not. If a
schedule is serializable, write down the equivalent serial schedule (S). (08 marks)
T1: R1(X); R1(Z); W1(X);
T2: R2(Z); R2(Y); W2(Z); W2(Y);
T3: R3(X); R3(Y); W3(Y);
S1: R1(X); R2(Z); R1(Z); R3(X); R3(Y); W1(X); W3(Y); R2(Y); W2(Z); W2(Y);
S2: R1(X); R2(Z); R3(X); R1(Z); R2(Y); R3(Y); W1(X); W2(Z); W3(Y); W2(Y);
c. Describe
the three steps in crash recovery in ARIES. What is the goal of each phase?
(04 marks)
*****************
|