Practical 13 -ADBMS

Problems on Normalization

1. Check and convert following relation into 1NF,2NF and 3NF.

2. Check and convert following relation into 1NF,2NF.

3. Explain full functional dependency and partial functional dependency with examples.

QUESTION BANK ADBMS(3340701)

QUESTION BANK ADBMS(3340701)

1. Write a short note on :Functional Dependencies.
2. List and explain armstrong's axiom rule.
3. What is decomposition?Explain Lossy and Lossless join decomposition with example.
4. Write algorithm to find redundant functional dependency.
5. What is normalization? Give advantages of it.
6. Give example and explain 1NF.
7. Give example and explain 2NF.
8. Give example and explain 3NF.
9. Explain ACID properties of transaction.
10. Define Transaction.Draw and explain state transition diagram.
11. What is concurrency control?State and explain various problems of concurrency control.
12. What is deadlock?Explain deadlock detection and prevention technique.
13. Explain time-stamp method for concurrency control.
14. Explain optimistic method for concurrency control.
15. Differentiate commit/rollback.
16. Explain grant and revoke commands with example.
17. What is lock?Explain levels of lock.
18. What is view?Give advantages and types of view.
19. Give syntax to create and drop view.
20. Explain unique index with example.
21. What is sequence?Explain how to create and destroy sequence with example.
22. Differentiate SQL v/s PL/SQL.
23. Draw and explain generic PL/SQL block.
24. Explain for statement with example.
25. Describe explicit cursor.Explain various steps to manage explicit cursor.
26. What is parameterized cursor?Explain with suitable example.
27. List out any four predefined exceptions.Explain DUP_VAL_ON_INDEX exception.
28. Explain user defined exception with example.
29. Differentiate procedure/function.
30. Define package.Write steps to create package in PL/SQL.
31. What is trigger.Give advantages and types of trigger.
32. Give advantages of using function in PL/SQL.

Levels of Consistency in DBMS

Degree 0

A degree 0 transaction does not overwrite the dirty data of other transactions. In terms of locking protocols, degree 0 requires obtaining a (possibly short) exclusive lock on any data the transaction writes. Degree 0 consistent transactions are unrecoverable since they commit outputs before the end of the transaction. 

A degree 0 consistent transaction  always obtains an exclusive lock before writing.

Degree 1

Degree 1 consistency does not  overwrite the dirty data of other transactions also do not  commit any writes before the end of the transaction. This requires setting a long (held until the end of the transaction) exclusive lock on any data it writes.Thus transaction T does not make any of its update visible before it commits.

Degree 2

 In addition to the requirements for degree 1 consistency, it does not read the dirty data of other transactions. To achieve this it sets a (possibly short) share lock on any data it reads.

Degree 3

A degree 2 consistent transaction may read two different (both committed) values if it reads the same entity twice. A degree 3 consistent transaction takes long exclusive locks on any data it writes, and long share locks on any data it reads.