Database Management Systems - Visvesvaraya Technological University May/June 2010 Questions / May-June 2010 DBMS questions of Visvesvaraya Technological University / DBMS University questions with answers
USN 06CS54
Fifth
Semester B.E. Degree Examination, May/June 2010
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. Briefly discuss the advantages of using
the DBMS. (10 Marks)
b. Explain the component modules of DBMS and
their interaction with the help of a diagram. (10 Marks)
2. a. Define an entity and an attribute. Explain
the different types of attributes that occur in an ER model, with an example. (10 Marks)
b. Define the following with an example:
(i)
Weak entity type
(ii)
Participation constraints
(iii)
Cardinality ratio
(iv)
Ternary relationship
(v)
Recursive relationship (10 Marks)
3. a. Discuss the characteristics
of a relation with examples. (08 Marks)
b. Briefly discuss different
types of update operations on relational database. Show an example of a
violation of the referential integrity in each of the update operation. (09 Marks)
c. What is valid state and an
invalid state with respect to a database? (03
Marks)
4. a. Consider the following two
tables T1 and T2. Show the results of the following operations:
(i)
T1 ⋈T1.P = T2.A T2
(ii)
T1 ⋈T1.Q = T2.B T2
(iii)
T1 ⟕T1.P = T2.A T2
(iv)
T1 ⋈T1.P = T2.A AND T1.R = T2.C T2
(v)
T1 U T2
(Assume T1 and T2 are union
compatible). (10 Marks)
Table T1
|
Table T2
|
||||||
P
|
Q
|
R
|
A
|
B
|
C
|
||
10
|
a
|
5
|
10
|
b
|
6
|
||
15
|
b
|
8
|
25
|
c
|
3
|
||
25
|
a
|
6
|
10
|
b
|
5
|
||
b) Explain with an example, the
basic constraints that can be specified, when you create a table in SQL. (10 Marks)
PART
– B
5. a. Explain the syntax of a
SELECT statement in SQL. Write the SQL query for the following relational algebra
expression. (06 Marks)
Πbdate,
address(σfname=’john’ ᴧ minit=’B’ ᴧ lname=’smith’(Employee))
b. Explain DROP command with an
example. (04 Marks)
c. Consider the following tables:
WORKS (Pname, cname, salary)
LIVES (Pname, street, city)
LOCATED_IN (Cname, city)
MANAGER (Pname, mgrname)
Write the SQL query for the
following:
(i)
Find the names of all persons who live in the city ‘Mumbai’.
(ii)
Retrieve the names of all person of ‘Infosys’ whose salary is between Rs.
30,000 and Rs. 50,000.
(iii)
Find the names of all persons who live and work in the same city.
(iv)
List the names of the people who work for ‘Wipro’ along with the cities they
live in.
(v)
Find the average salary of all persons of ‘Infosys’. (10 Marks)
6. a. What is a functional
dependency? Write an algorithm to find a minimal cover for a set of functional
dependencies. (10 Marks)
b. What is the need for
normalization? Explain second normal form. Consider the relation EMP-PROJ =
{ssn, pnumber, hours, ename, pname, plocation}. Assume {ssn, pnumber} as
primary key. The dependencies are;
ssn pnumber → hours
ssn → ename
pnumber → pname, plocation.
Normalize the above relation into
2NF. (10 Marks)
7. a. Explain multi-valued dependency
and fourth normal form with an example. (10
Marks)
b. Let R = {ssn, pnumber, hours,
ename, pname, plocation} and D = {R1, R2, R3}, where;
R1 = EMP = {ssn, ename}
R2 = PROJ = {pnumber, pname,
plocation}
R3 = WORK_ON = {ssn, pnumber,
hours}
The following functional
dependencies hold on relation R.
F = {ssn → ename; pnumber → {pname,
plocation}; {ssn, pnumber} → hours}
Prove that the above decomposition
of relation R has the lossless join property. (10 Marks)
8. a. Explain the problems that
can occur when concurrent transactions are executed. Give examples. (10 Marks)
b. Briefly discuss the Two phase
locking protocol used in concurrency control. (10 Marks)
***********
No comments:
Post a Comment