CS-06 DATABASE MANAGEMENT SYSTEM
DEC 2003

1(a) Construct an ER diagram (including important attributes) for a car insurance database that includes data about customer (car owners), cars, accidents, drivers involved in accidents and injured drivers and/or passengers. Note that any customer can insure many cars, each car may have different drivers at different times and accidents typically involve one or more cars.           [10]

(b) Explain the 3 levels of the database architecture. Also discuss the advantages of having the 3 levels.                           [8]

(c) What are functional dependencies? With a suitable example explain the importance of functional dependencies in database design and data modelling.                                             [7]

(d) Discuss the Boyce-Codd Normal Form (BCNF) with the help of an example.                                                           [5]

2(a) Consider the following Relational schema. An employee can work in more than one department:                              [10]

   Emp (Eid, Ename, Salary)

   Works (Eid, did)

   Dept (did, dname, managerid, floornum)

Write the following in SQL:

(i) Print the names of all employees who work on the 10^th floor and earn salary less than Rs. 50,000.

(ii) Print the names of the departments that employee Santa works in.

(iii) Print the names of all managers who manage three or more departments on the same floor.

(iv) Print the names of all employees who work on floor(s) where Jane Donald works.

(v) Give every employee who works in the toys dept. a 10% raise in the salary.

(b) Compare and contrast primary key, candidate key and foreign key by giving a suitable example.        [5]

3 Explain the following:                                                           [15]

(i) Hashing

(ii) Indexing

(iii) B⁺ tree indexes

(iv) Specialization

(v) Generalization

4(a) Discuss the features of OODBMS. Also mention the advantages.                                                                [8]

(b) Discuss the following and also list the area of application:  [7]

5 Explain the following:                                                           [15]

(i) Multithreaded Processes

(ii) Object Linking

(iii) Client/Server Computing

6(a) Consider the relation R with 5 attributes A, B, C, D, E. You are given the following dependencies:                             [9]

   A ® B

   BC ® E

   ED ® A

(i) List all keys of R.

(ii) Is R in 3NF or BCNF?

(iii) Design the normalised tables.

(b) Explain the trade offs in distributing the database in the context of Distributed database.                                                 [6]