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