2004-05 MODEL PAPER

MCA 2.2.2

DATABASE MANAGEMENT SYSTEMS

First Question is Compulsory

Answer any four from the remaining

Answer all parts of any Question at one place.

Time: 3 Hrs.
Max. Marks: 100

1. Write a brief note of the following
a. Database schema
b. Database State
c. Null Value
d. Generalization
e. Logical design
f. DBA
g. ACID
h. Referential Integrity
i. State Armstrong axioms
j. Purpose of normal forms

2. a) With an appropriate example differentiate between logical data independence and physical data independence.
b) When is the concept of a week entity used in data modeling?
c) Define the terms owner entity type, week entity type, identifying relationship type and partial key.

3. a) What is subclass? When is subclass needed in data modeling?
b) What is difference between a specialization hierarchy and a specialization lattice?
c) Discuss the similarities and differences between an ontology and a database schema.

4. a) Draw an E-R diagram for an airline reservation system consisting of flights, aircrafts, airports, fares, reservations, tickets, pilots, crew and passengers. Clearly highlight the entities, the relationship, the primary keys and the mapping constraints
b) Write down the database schema, indicating the primary keys, the foreign keys and functional dependencies. Is there any redundancy in the definition? If so, give a normalized decomposition.
c) Explain clearly why the lossless-join property is a necessary condition for decomposition while dependency preservation is only a desirable condition.

5. Consider the following relations:
SHOP(Shop_No, Shop_name, Address, owner)
ITEM(I-No, I-Name)
SUPPLIED(I-No, C_No, Shop_No, Date, Price)
REQUIRES(C_No, I-No)
SUPPLIED database gives data about items supplied by a shop to a customer and REQUIRES gives data about items required by a customer
Write down queries in relational algebra for getting
a) names of customers who have been supplied items of maximum total value.
b) Names of customers who require items which have not been supplied (along with item names)
c) Names of customers who are supplied all the items from only one shop.
d) List of shop owners who supplied some item to the address "Krishna Nivas, MG Road".

6. a) List all functional dependencies(FDs) satisfied by the following relation r.

A	B	C	D
a1	b1	c1	d1
a1	b2	c1	d2
a2	b2	c2	d2
a2	b3	c2	d3
a2	b3	c2	d4

b) Why are certain FDs called trivial FDs?
c) Let the relation scheme R(ABCDE) be decomposed into (AD), (AB), (BE), (CDE) and (AE). Determine whether the above join is lossless if the following set of FDs hold A? C, B? C, C? D, DE? C, CE? A

7. a) Explain how concur rency can lead to inconsistency.
b) What is a deadlock?
c) Can it occur in a serializable schedule? If so, give an example. How can it be detected and resolved?
d) Explain the concept of the two phase locking and show that it guarantees serializability?

8. a) For each of the situations listed below, decide which of the following recovery technique(s) is most appropriate. Justify your answer
1) Backward recovery (UNDO)
2) Forward recovery(REDO)
3) Forward recovery using backup copy (REDO using backup copy)
4) Compensating transactions (introduced to correct errors)
The situations are:
i) A disk containing the database is damaged so that it cannot be read
ii) A lightning storm causes a power failure
iii) An incorrect amount is entered and posted for a student tuition payment. The error is not discovered for several weeks.
iv) A phone disconnection occurs while a user is entering a transaction.
b) Describe the recovery techniques that employs the immediate update scheme