END TERM EXAMINATION
SECOND SEMESTER [BCA]
MAY- JUNE 2015
Paper Code: BCA-1 10
Subject: Database Management Systems
(Batch 2011 onwards)
Time: 3 Hours Maximum Marks: 75
Note: Attempt any ﬁve questions including Q. no.1 which is compulsory. Select one question from each unit.
Question 1: Answer the following (2.5x10=25)
(a) What is timestamp based protocol?
(b) Distinguish between non-serializable and serializable schedules.
(c) Deﬁne the concept of minimal cover
(d) Discuss the ACID properties of a transaction.
(e) What is the loss-less join decomposition?
(f) What do you understand by DCL commands?
(g) How is a weak entity set different from a strong entity set?
(h) Write me syntax of selection and projection operations in relational algebra.
(i) Differentiate between equi-join and natural-join.
(j) When is a query called relationally complete?
Draw an ER diagram for The Book Club. It has members to whom the books are sold. The books are made available at different places in the city—called Book Club Chapter—to make it easy to members. The books are identiﬁed by a book_id, the author and the publisher. An author can write more than one book and a book can have more than one author. Members have information such as Membership_id, Name, Phone# and status. A member can place more than one order.
Choose additional attributes for the schema that seems appropriate. Mention any assumptions you make. Show minimum and maximum cardinality ratios based on the assumptions. (12.5)
Consider the following MAILORDER relational schema describing the data for a mail order company.
PARTS[Pno Pname, Qoh, Price, Olevel)
CUSTOMERS(Cno, Cname, Street, Zip, Phone)
EMPLOYEES(Eno, Ename, Zip, HireDate)
ORDERS(Ono, CNo, ENo, received, shipped)
ODETAILS(QNo, Pno, Qty)
The attribute names are self-explanatory: e.g. Qoh stands for quantity on hand. Specify the following queries using Relational Algebra:
(a) List the name of parts that costs less than Rs. 200
(b) List the name and cities of employees who have taken orders for parts costing more than Rs.500
(c) List the name of customers who have ordered parts from employees living in Mumbai
(d) List the name of customers who have placed exactly two orders (12.5)
(a) What is the purpose of integrity constrain in SQL?
(b) What is the difference between WHERE and HAVING clause?
(c) differentiate between Outer and Inner Join operations?
(a) Deﬁne BCNF. How is different from SNF? Present an example of a relation in 3NF that is not in BCNF. (8)
(b) A table R has attributes A,B,C,D,E and satisﬁes the following functional dependencies: (4.5)
(i) What are the candidate keys?
(ii) Is this table in 2NF/3NF/BCNF?
(a) What is a concurrent transition? What are the problems associated with concurrency? (5)
(b) What is a two—phase locking protocol? How does it guarantee serializability? (7.5)
(a) What is a time stamp? How does the system generate time stamps? (6)
(b) What are the rules followed to ensure serializibility in multiversion techniques based on timestamp ordering? (6.5)
What is a deadlock? How are deadlocks handled? What is deadlock prevention and how is it different from deadlock detection and deadlock recovery? (12.5)
Question 9: Writes notes on the following:
(a) Checkpoints (4)
(b) Triggers and Assertions (4)
(c) EER Model (4.5)