Please visit, subscribe and share 10 Minutes Lectures in Computer Science

## Database Management Systems University Questions 5

### Database Management Systems

Time : Three Hours                                                              Max.Marks:100
PART – A (8 X 5 = 40 Marks)

1. Differentiate between physical and logical data independence.
2. Given tables R and S as follows: [2.5 + 2.5]
Find: a) R∪S                                       b) R∩S
R
 Sno Dept S1 Physics S2 Chemistry S3 Mathematics S4 Computer

S
 Sno Department S10 Psychology S2 Chemistry S15 Information Technology S16 Biology

3. Distinguish between:
a) Stored and derived attributes.                                [2.5]
b) Simple and composite attributes.                            [2.5]
4. Prove:
a) If X->Y, X->Z then X->YZ                                   [2.5]
b) If X->Y and Y⊇Z then X->Z                                [2.5]
5. Distinguish between various ways of organizing records in files.
6. State the reasons for bucket overflow.
7. Mention the various methods to recover from a deadlock.
8. Differentiate between homogeneous and heterogeneous distributed database systems.

PART – B (6 X 10 = 60 Marks)

9. Discuss the major advantages of database system.

10. Consider the following relational schema:
Emp (SSN, Name, MGR_SSN, Salary, Dno)
Write the following of queries in SQL.
a) Display the names of the employees in the descending order of their salaries.                  [2]
b) Retrieve the names of the employees working in ‘IT’ department.                                   [3]
c) Retrieve the department number (DNo), number of employees in each department and average salary of each department.                                                                                                     [3]
d) Retrieve the names of employees who have no supervisors.                                              [2]

11. a) Suppose that we decompose the schema.                                                                     [5]
R = (A, B, C, D, E) into R1(A, B, C) and R2(A, D, E).
Show that this decomposition is lossless-join decomposition if the following set F of functional dependencies holds:
F = {A → BC,CD → E,B → D,E → A}
b) Given a relation R={A,B,C,D,E,H}, having a set of functional dependencies                 [5]
F={A->BC, CD->E, E->C, D->AEH, ABH->BD, DH->BC}.
Find the key for relation R with respect to F.

12. Elaborate on the following and differentiate them:
a) Primary index
b) Secondary index and
c) Clustering index.

13. Consider the following relational schema:
Branch(branch_name,branch_city,assets)
Account(account_number,branch_name,balance)
Depositor(customer_name,account_number)
a) Write a SQL query to “Find the names of all the customers who have an account at any branch located in Chennai and have a balance greater than Rs.10,000”.
b) Transform it into relational algebra expression.
c) Draw the initial query tree and show how it is optimized.

14. Mention the different types of log based recovery schemes and compare them.

15. List all possible sequences of sates through which a transaction may pass. Explain why each transition may occur?

a) Horizontal
b) Vertical and
c) Mixed fragmentation.

************

## Featured Content

### Multiple choice questions in Natural Language Processing Home

MCQ in Natural Language Processing, Quiz questions with answers in NLP, Top interview questions in NLP with answers Multiple Choice Que...

data recovery