Database Management Systems University Questions 5

Database Management Systems University Questions 5

Database Management Systems

Time : Three Hours                                                              Max.Marks:100

PART – A (8 X 5 = 40 Marks)

Answer ALL Questions

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)

Answer any SIX Questions

9. Discuss the major advantages of database system.

10. Consider the following relational schema:

Emp (SSN, Name, MGR_SSN, Salary, Dno)

Dept (Dno, Dname, Mgrssn)

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?

16. Discuss about:

a) Horizontal

b) Vertical and

c) Mixed fragmentation.

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