Term-End Examination JUNE, 2007 ( CS-15 )

Term-End Examination JUNE, 2007

CS-15 : RELATIONAL DATABASE MANAGEMENT SYSTEM

Time : 3 hours Maximum Marks : 75

Note : Question number 1 is compulsory. Answer any three questions from the rest.

1.( a ) In an organization several projects are undertaken. Each project can employ one or more employees. Each employee can work on one or more projects. Each project is undertaken on the request of a client. A client can request for several projects. Each project has only one client. A project can use a number of items and an item may be used by several projects. Draw an ER-diagram and convert it into a Relational Schema. [ 8 ]

( b ) Describe the multi-version technique for Concurrency Control. [ 7 ]

( c ) How is the check-pointing information used in the recovery operation following a system crash ? [ 7 ]

( d ) Describe two phase commit protocol in distributed databases. [ 8 ]


2.( a ) ( i ) The following relations are used to store data about students, courses and enrolment of students in courses and teachers of courses. (Primary key in relation is marked by *)
Students (rollno*, sname, saddr)
Enrol (rollno, cno*, grade)
Teach (tno*, tname, cno*)
Write Create Table statements for defining the above tables.
Further write SQL statements to get rollno and names of students who have got an ‘A’ grade in a course taught by “Rohan”. [ 9 ]

( ii ) What is QBE ? [ 3 ]

( b ) Why are certain functional dependencies called “trivial functional dependencies” ? Explain. [ 3 ]


3.( a ) Consider the following relations :
ITEM (I#, INAME)
ORDER (O#, ONAME, CNAME)
ORDERITEM (I#,O#)
Write the relational algebra queries for the following :
(i ) Get the details of items ordered by the customer “Sohan”.
( ii ) Get the Item Nos. of items which are included in all orders. [ 6 ]

( b ) Discuss “Wait-die” and “Wound-wait” approaches of deadlock avoidance. Compare these approaches of deadlock avoidance with a deadlock avoidance approach in which data items are locked in a particular order (according to their rank). [ 9 ]


4.( a ) What are the relative advantages and disadvantages of Record-level logging. Page level logging and Query language logging ? [ 9 ]

( b ) describe normalization using join dependency with the help of an example. [ 6 ]


5. Explain the following terms : [ 15 ]
( i ) Physical data independence
( ii ) Sequential data retrieval in hierarchical model
( iii ) Content dependent access control
(iv ) Distributed locking
( v ) Domain integrity