Database Management Systems University Questions 6

Database Management Systems University Questions 6

Database Management Systems

Time : Three Hours                                                                          Max.Marks:100

NOTE:

i) Neatly draw the diagrams and explain it, wherever required.

ii) Wherever the example is require, explain with an example.

PART – A (8 X 5 = 40 Marks)

Answer ALL Questions

1. Discuss in detail about the functions of DBMS.

2. Explain the ACID properties of a transaction.

3. Explain in details, how TCL statements are important in banking applications.

4. Discuss the concept of query optimization in DBMS.

5. How database differs from data warehouse and data mining?

6. Explain Functional dependency and Multi valued dependency with example.

7. What is the need of the normalization? Explain the first three steps involved in the normalization.

8. Explain the three schema architecture in detail with diagram.

PART – B (6 X 10 = 60 Marks)

Answer any SIX Questions

9. Describe the overall architecture of a DBMS.

10. Use an Entity-Relationship diagram to depict the information needs of the following engineering enterprise:

- Each engineer works on a number of projects. For every engineer a record is kept of his/her number, name, title and salary. For every project a record is kept of its number, name, budget and location. Additionally, the responsibility each engineer has on a particular project and the amount of time spent on the project is noted.

- Clients contract these projects. The clients name, address and phone number must be recorded. The contract date, i.e. the date at which the client signs the contract for a specific project is also noted. A record is also kept of the machinery being used for a particular project. The name, amount and the date the machinery is needed for the project is recorded.

Note: Include any attributes you think should be represented (state why). State any assumptions you have made.

11. How files are organized using hashing and discuss about handling bucket overflow.

12. Why is concurrency control needed? Explain lost update, Inconsistent retrievals and uncommitted dependency anomalies.

13. Explain how the items in the database are cataloged using indices and define the evaluation techniques used for evaluating it.

14. Discuss in detail about the features of Distributed Databases.

15. Consider the below table and write the SQL queries:

RegNo	SName	Branch	Grade	Age	Gender
238	Kumar	BE (CSE)	6.5	23	M
300	Megan	BSc (CS)	9.2	22	F
354	Morris	MSc (CS)	7.4	22	M
302	Harry	BSc (CS)	8.7	23	M
230	Ramesh	BE (CSE)	8.9	23	M

a) Display the maximum and average Grade of BE – CSE students alone with student names.

b) Renme the Gender column to Sex.

c) Display the minimum Grade in each branch.

d) Display all the student names that starts with ‘R’ or ‘A’

e) Update the branch ‘BE (CSE)’ as B.E, ‘BSc (CS)’ as ‘B.Sc’ and ‘MSc (CS)’ as ‘M.Sc’

16. When is a transaction said to be deadlocked? Explain the deadlock prevention methods with an example.

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