Database Management Systems - Dec 2014

Information Technology (Semester 3)

TOTAL MARKS: 80
TOTAL TIME: 3 HOURS (1) Question 1 is compulsory.
(2) Attempt any three from the remaining questions.
(3) Assume data if required.
(4) Figures to the right indicate full marks. 1 (a) Define the following terms:
i) Foreign key
ii) Derived attribute
iii) Deadlock
iv) Schedule
v) Data Independence(10 marks) 1 (b) Suppose that we decompose the schema
R={A, B, C, D, E} into
R1={A, B, C} and
R2={A, D, E}
Show that this decomposition is lossless join decomposition if the following set of functional dependencies hold
A?BC CD?E
B?D E?A(5 marks) 1 (c) Explain Generalization and Specialization.(5 marks) 2 (a) What are triggers? Explain with example.(10 marks) 2 (b) Explain advantages of DBMS over file system.(10 marks) 3 (a) Draw E-R diagram for university database consisting of four entities; Student, department, class, faculty, Student has a unique id, the student can enroll for multiple classes and has at most one major. Faculty must belong to department and faculty can teach multiple classes. Each class is taught by only one faculty. Every student will get grade for the class he/she has enrolled.(10 marks) 3 (b) Explain serializability with example.(10 marks) 4 (a) Consider Insurance Database given below and answer the following queries in SQL.
Person (driver_id, name, address)
Car(license, model, year)
Accident (report_no, adate, location)
Owns (driver_id, license)
Participated (driver_id, license, report_no, damage_amount)
i) Find total number of people who owned cars that are involved in accidents in 2004.
ii) Find the number of accidents in which car belonging to 'John Smith' were involved.
iii) Add new accident to Database.
iv) Delete 'Santro' belonging to 'John Smith'.(10 marks) 4 (b) List the ACID properties. Explain usefulness of each.(10 marks) 5 (a) Consider the following relation.
CAR-SALE ( Car#, Date-Sold, Salesman#, Commission%, Discount-amt) Assume that {Car#, Salesman#} is the primary key. Additional dependencies are Date-sold ? Discount-amt
Salesman#?commision%
Based on the given primary key, is this relation in 1NF, 2NF or 3NF? Why or why not? How would you successively normalize it completely?(10 marks) 5 (b) Explain concurrency control in database system with the help of only two protocols.(10 marks) 6 (a) Explain any four relational algebra operations with proper examples.(10 marks) 6 (b) Draw a query tree for the following SQL query
Select P. Pnumber, P.Dnum, E.Lname, E.Address, E.Bdate from project as P, Department as D, Employees as E where P.Dnum=D.Dnumber and D.Mgr-ssn=E.ssn and P.Plocation='Mumbai'.(10 marks)