Database Management Systems - Dec 2015

Computer Science Engg. (Semester 5)

TOTAL MARKS: 100
TOTAL TIME: 3 HOURS (1) Question 1 is compulsory.
(2) Attempt any four from the remaining questions.
(3) Assume data wherever required.
(4) Figures to the right indicate full marks. 1 (a) Write a note on various types of end users who use DBMS.(8 marks) 1 (b) Explain the three level DBMS architecture, with a neat diagram. Why do we need mappings between schema levels? Explain mapping in DBMS architecture.(12 marks) 2 (a) Explain the ER notations used for various constructs in database schema.(10 marks) 2 (b) With respect to ER model, explain with example.
i) Composite attributes
ii) Cardinality ratio
iii) Participation constraints
iv) Binary relationship(10 marks) 3 (a) Discuss the various type of JOIN operations. Why is theta join required?(6 marks) 3 (b) Consider the following relational schema;
user (uid, name, cost)
groups (gid, title, category, n, gsize, owner)
posts (pid, uid, gid, tid ptext, pdate)
Write the following queries in relational algebra.
i) Show the text and number of all the post made by user member 4 before March 1, 2007.
ii) Show the names of the all the users who responded to post number 2.
iii) Show the aid and cost of all the users who are group owners and posted a thread on 1.1.2003.(9 marks) 3 (c) Explain the SELECT and PROJECT operation in relational algebra with example.(5 marks) 4 (a) Explain the following:
i) Primary key
ii) Foreign key
iii) Candidate key(6 marks) 4 (b) Consider the following relations:
i) Hotel (hotelno, name, address)
Room (roomno, hotelno, type, price)
Booking (hotelno, guestno, datefrom, dateto, roomno)
Guest (guestno, name, address)
Write the SQL statements for the following:
i) List the name and address of all guest booked the hotel, which is located in Chandigarh, alphabetically ordered by name.
ii) List all family rooms with a price below Rs. 400 per night, in ascending order of price in hotel ?RVH?
iii) How many hotels are there?(9 marks) 4 (c) Explain with example in SQL.
i) Drop command.
ii) Delete command(5 marks) 5 (a) What is a view? Explain how to create the view and how view can be dropped?(8 marks)

Explain the following:

5 (b) (i) Embedded SQL(6 marks) 5 (b) (ii) Database stored procedure(6 marks) 6 (a) Explain informal design guidelines for relation schemas.(6 marks) 6 (b) What is the need for normalization? Explain the first, second and third normal forms with examples.(14 marks) 7 (a) Consider the schema
R=(A, B, C, D, E). Support the following functional dependencies hold
E → A
CD → E
A →BC
B → D
State whether the following decomposition of R are lossless join decomposition or not, Justify.
{(A, B, C), (A, D, E)}
{(A, B, C), (C, D, E)}(10 marks)

Explain the following:

7 (b) (i) Inclusion dependencies(10 marks) 7 (b) (ii) Domain key Normal Form(10 marks) 8 (a) Explain why a transaction execution should be atomic? Explain ACID properties by considering the following transaction
Ti: read (A):
A: A-50;
write (A);
read (B);
B: = B+50;
write (B);(10 marks) 8 (b) Briefly discuss on the two phase locking protocol used in concurrency control.(10 marks)