Database Management System 2071

Attempt all the questions.

1.a) Why do we need database management system? Discuss drawbacks of file system and advantages of database management system. [2+3]

    b) What is database system architecture? Describe three levels and benefits of this architecture. [1+4]

2. A database is being constructed to keep track of the teams and games of a sports league. A team has a number of players, not all of whom participate in each game. If is desired to keep track of the players participating in each game. Design an ER diagram for this application. [10]

3.(a) Define integrity constraint? Discuss referential integrity in detail. [1+4]

    (b) What is functional dependency? How can we use functional dependency to normalize a relation in 3NF?Discuss with suitable example. [1+2+2]

4.Consider the following employee database, where primary keys are underlined. [20]

employee (employee-name, street, city)

works (employee-name, company-name, salary)

company (company-name, manager-name)

manages (employee-name, manager-name)

Give an expression in SQL for each of the following queries.

a)Find the names of all employees who work for Second Bank Corporation.

b) Find the names, street and cities of residence of all employees who work for Second Bank

Corporation.

c)Find the names, street addresses, and cities of residence of all employees who work for Second

Bank Corporation and earn less than $10,000.

d) Find the names of all employees who work under the manager “Devi Prasad”.

e)Find the number of employees having salary greater than or equal to 20000

5.(a) Why do we need concurrency control mechanism? Discuss basic, conservative, and rigorous two phase locking algorithm. [2+3]

    (b) What is shadow paging? How can we use this technique to recover our database? [5]