B.E. 4th SEMESTER (CSE) Examination - December, 2009 DATABASE MANAGEMENT SYSTEMS

B.E. 4^th SEMESTER (CSE)

Examination  - December, 2009

DATABASE MANAGEMENT SYSTEMS

Paper : CSE-202-E

Note  : Attempt any five questions. All questions carry equal marks.

1.     (a) Discuss the complete architecture of a database system.         10

(b) What are the duties and responsibilities of a DBA?

Discuss.                                                                                             10                                                                                   

2.     (a) Construct an ER-diagram for a hospital with a set of patients and a set of medical doctors. Associate with each patient a log of various tests. And examinations conducted.                                                 10

(b) Construct appropriate tables for the ER-diagram constructed in above question (Q.2(a) ).                                                                 10

3.     (a) What are the causes of bucket overflow in a hash file organization? What can be done to reduce occurrence of bucket overflow?                                                                                          10                                                                              

(b) Differentiate between sequential and index sequential

file.                                                                                                    10                                                                                                  

4.     Consider the following database scheme where the primary keys are underlined:

Employee (name, SSN, bdate, address, salary, Dno)

Department (Dname, Dnumber, Mgr SSN)

Dept-locations (Dnumber, Dlocation, Dnum)

Project (pname, Pnumber, Plocation, Dnum)

Works-on(ESSN, Pno, hours)

Department (ESSN, dependentname, bdate, relationship)

Express the following queries in relational algebra and tuple   relational calculus:

(a)  Retrieve the name and address of all employees who work for ‘Research’ department.

(b) For every project located in London, list the project number, controlling department no. and the department manager’s name, birth date and address.

(c)  Retrieve the names of employees who have no dependants.

(d) Find names of employees who work on all the projects controlled by department No. 5.                                                                                 20

5.     (a) Define the term functional dependency. Why are some functional dependencies called trivial?                                                                  10

(b) Suppose you are given a relation R with four attributes ABCD. For each of the following sets of  FDs, assuming those are the only dependencies that hold for R, do the following:

(a) Identify the candidate key(s) for R.

(b) Identify the best normal form that satisfies normal form      (1NF, 2NF, 3NF, or BCNF).

(c) If R is not in BCNF, decompose it into a set of BCNF relations (preferable one that preserves the dependencies).                             10

6.     (a) What are the distributed database? What are their advantage and disadvantage?                                                                                        10

(b) Write a detailed note on architecture of parallel

 databases.                                                                                              10

7.     (a) Elaborate ACID properties of a transaction.                                10

(b) Describe the architecture of a data-warehouse.                           10

8.     Write short note on following :

(a)  MVD.

(b) SQL.

(c)  Mapping Constraints.

(d) Integrity Constraints                                                                       20