VTU Computer Science (Semester 5)
Database Management Systems
December 2015
Total marks: --
Total time: --
INSTRUCTIONS
(1) Assume appropriate data and state your reasons
(2) Marks are given to the right of every question
(3) Draw neat diagrams wherever necessary


1 (a) Write a note on various types of end users who use DBMS.
8 M
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 M

2 (a) Explain the ER notations used for various constructs in database schema.
10 M
2 (b) With respect to ER model, explain with example.
i) Composite attributes
ii) Cardinality ratio
iii) Participation constraints
iv) Binary relationship
10 M

3 (a) Discuss the various type of JOIN operations. Why is theta join required?
6 M
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 M
3 (c) Explain the SELECT and PROJECT operation in relational algebra with example.
5 M

4 (a) Explain the following:
i) Primary key
ii) Foreign key
iii) Candidate key
6 M
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 M
4 (c) Explain with example in SQL.
i) Drop command.
ii) Delete command
5 M

5 (a) What is a view? Explain how to create the view and how view can be dropped?
8 M
Explain the following:
5 (b) (i) Embedded SQL
6 M
5 (b) (ii) Database stored procedure
6 M

6 (a) Explain informal design guidelines for relation schemas.
6 M
6 (b) What is the need for normalization? Explain the first, second and third normal forms with examples.
14 M

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 M
Explain the following:
7 (b) (i) Inclusion dependencies
10 M
7 (b) (ii) Domain key Normal Form
10 M

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 M
8 (b) Briefly discuss on the two phase locking protocol used in concurrency control.
10 M



More question papers from Database Management Systems
SPONSORED ADVERTISEMENTS