Institute of Chartered Financial Analysts of India %28ICFAI%29 University 2006 B.E Database Management System - exam paper

Database Management System
Paper Code : ETCS – 309

Note : Attempt any three ques. in all. Q1 is compulsory. every ques. carries 10 marks.

Time : 11/2 Hours
Maximum Marks : 30

Q1


( a )


describe the subsequent terms:

1. Normalization
2. Full functional dependency
3. Trivial join dependency
4. Boyce-Codd Normal form



4

( b )
Describe briefly any 2 desirable properties of a transaction. 2
( c ) What is a lossless decomposition? 2
( d ) Name the various input/output data constraints in SQL. 2

Q2


( a )


obtain the minimal set of functional dependencies from the subsequent provided set:
PQ --> R
PS --> Q
QS --> P
PR --> Q
S --> R


4

( b )


Consider the subsequent relation:
Book (Book-title, Author-name, Book-type, List-price, Author-affiliation, Publisher)

Suppose the subsequent functional dependencies exist:
Book-title --> Publisher, Book-type
Book-type --> List-price
Author-name --> Author-affiliation

1.

What normal form is the relation in? discuss your ans. (2)
2.

Apply normalization until you cannot decompose the relations futher. State the reasons behind every decomposition. (4)



Q3


( a )
Consider the subsequent database. The priamry keys are underlined.

Person(driver-id#, name, address)
Car(licence, model, year)
Accident(report-number, date, location)
Owns(driver-id#, license)
Participated(driver-id#, licence, report-number, damage-amount)

Construct the subsequent SQL queries for this database:

1. obtain the total number of people who owned cars that were involved in accidents in 1990. (2)
2. obtain the number of accidents in which the cars belonging to "Ram" were involved. (2)
3. Add a new accident to the database; presume any values for the needed attributes. (1)
4. Update the damage amount for the car with the license number "A123" in the accident with the report number "20" to Rs. 5000.





( b )
Differentiate ranging from the working of time stamping techniques and locking techniques of concurrency control. 4

Q4


( a )
Let the subsequent relational schema be given:

EMPLOYEE(SSN, NAME, AGE, DNO)
SALARY(SSN, SALARY)
WORKS_ON(PROJECT#, SSN)
PROJECT(PROJECT#, PROJECT-NAME, PLOCATION)

For every of the subsequent queries, provide an expression in QUEL and QBE.

1. Display the names of projects at "Delhi".
2. obtain the salaries of employees by Name.
3. Retrieve the name and SSN of employees working on Project# A100.



6

( b )
Explain the subsequent terms:

1. Serializability of schedules
2. Shadow paging

Earning:   Approval pending.