Database Management Systems University Question 7

Database Management Systems University Question 7

Database Management Systems

Time : Three Hours                                                                          Max.Marks:100

Answer ALL Questions

(10 X 10 = 100 Marks)

1. a) Define the following terms: database catalog ,program-data independence, canned transaction, deductive database system, meta-data. [5]

b) Discuss the main characteristics of the database approach and how it differs from traditional file systems. [5]

2. a) Describe the three-schema architecture. Why do we need mappings between schema levels? How do different schema definition languages support this architecture? [4]

b) Write a short note on component modules of an DBMS with neat diagram. [6]

3. a) Define the following terms: relation schema, relation state, degree of a relation. [3]

b) List the characteristics of relations that make them different from ordinary tables and files. [3]

c) Discuss the entity integrity and referential integrity constraints. Why each is considered important? [4]

4. a) List any two operations of relational algebra and the purpose of each. [4]

b) Student (snum: integer, sname: string, major: string, level: string, age: integer)

Class (name: string, meets at: string, room: string, fid: integer)

Enrolled (snum: integer, cname: string)

Faculty (fid: integer, fname: string, deptid: integer)

The meaning of these relations is straightforward; for example, Enrolled has one record per student-class pair such that the student is enrolled in the class. [6]

Write the following queries in SQL. No duplicates should be printed in any of the Answers.

i) Find the names of all Juniors (level = JR) who are enrolled in a class taught by particular Teacher.

ii) Find the age of the oldest student who is either a History major or enrolled in a course taught by I.Teach.

iii) Find the names of all classes that either meet in room R128 or have five or more students enrolled.

5.

Figure 1 - ER Diagram for question 5

a) Explain the various steps of ER-to-Relational mapping algorithm. [7]

b) Write the relational schema for the above ER conceptual schema diagram for the COMPANY database. [3]

6. Suppose you are given a relation R = (A, B, C, D, E) with the following functional dependencies: {CE → D, D → B,C → A}.

a) Find all candidate keys. [4]

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

c) If the relation is not in BCNF, decompose it until it becomes BCNF. At each step, identify a new relation, decompose and re-compute the keys and the normal forms they satisfy. [4]

7. a) Define the following terms [4]

i) Dense index ii) Sparse index iii) Index sequential file iv) Multilevel indices.

b) Explain the structure B+ Tree and illustrate update operations with examples. [6]

8. Explain the Heuristics query optimization techniques with example. [10]

9. a) List the various modes of lock and their uses. [3]

b) write a short note on [7]

i) Two phase locking protocol

ii) Graph-Based protocol

10. a) State the types of failures that may occur in a system. [3]

b) Explain log based recovery with respect to Deferred Database Modification and Immediate Database modifications. [7]

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