WELCOME TO IP UNIVERSITY QUESTION PAPERS WEBSITE
Homepage : http://ippapers.tripod.com/    |     Back to V semester papers

# Second-Term Examination

Fifth Semester [B.Tech] - November  2005

 Paper Code : ETCS – 309 Subject : Database Management System
 Time : 11/2 Hours Maximum Marks : 30
 Note : Attempt any 3 questions in all. Q1 is compulsory. Each question carries 10 marks.

 Q1 ( a ) Define the following terms: Normalization Full functional dependency Trivial join dependency Boyce-Codd Normal form 4 ( b ) Describe briefly any two desirable properties of a transaction. 2 ( c ) What is a lossless decomposition? 2 ( d ) Name the different input/output data constraints in SQL. 2

 Q2 ( a ) Find the minimal set of functional dependencies from the following given set: PQ --> R PS --> Q QS --> P PR --> Q S  --> R 4 ( b ) Consider the following relation: Book (Book-title, Author-name, Book-type, List-price, Author-affiliation, Publisher) Suppose the following functional dependencies exist: Book-title --> Publisher, Book-type Book-type --> List-price Author-name --> Author-affiliation What normal form is the relation in? Explain your answer.  (2) Apply normalization until you cannot decompose the relations futher. State the reasons behind each decomposition. (4)

 Q3 ( a ) Consider the following 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 following SQL queries for this database: Find the total number of people who owned cars that were involved in accidents in 1990. (2) Find the number of accidents in which the cars belonging to "Ram" were involved. (2) Add a new accident to the database; assume any values for the required attributes. (1) 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 between the working of time stamping techniques and locking techniques of concurrency control. 4

 Q4 ( a ) Let the following relational schema be given:EMPLOYEE(SSN, NAME, AGE, DNO) SALARY(SSN, SALARY) WORKS_ON(PROJECT#, SSN) PROJECT(PROJECT#, PROJECT-NAME, PLOCATION) For each of the following queries, give an expression in QUEL and QBE. Display the names of projects at "Delhi". Find the salaries of employees by Name. Retrieve the name and SSN of employees working on Project# A100. 6 ( b ) Explain the following terms: Serializability of schedules Shadow paging 4