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]
***************