Test Papers / Previous Question Papers of IGNOU CS15 Relational Database Management System June 2006

June, 2006

1. (a) An organisation has multiple retail stores. These stores have to keep track of products, suppliers, customers and employees. Make an E-R diagram. stating the assumptions used, if any. (8) (b) Explain multi-valued dependency with the help of an example. How is it related to normalization? Explain this with the help of an example. (7) (c) Explain the major transaction states with a suitable diagram. Describe a suitable method using Locks, for handling a large number of concurrent transactions per minute in an online airline reservation system. (7) (d) Explain the significance and usage of views in . a relational database system, with the help of suitable examples. Explain any four constraints of updating a fuple in a view. (8) 2. (a) Define indices and their advantages in RDBMS. Explain the concepts of clustering and hashing indices, using an example of each. (7) (b) Consider the relations : GRADE (stud_id, subject_idg, rade) SUBJECT (subject-id, s-namd, teacher) (i) Write the relational algebraic query for the following : * List the student number, subject names and grades of the student whose id is 100. * List the grades of all the students in the subjects taught by teacher "XYZ". (ii) Convert the queries above into an optimized query graph. (8) 3. (a) Consider the following relations in the context of a BPO: Customer (C-code, C-name, C-phone) Product (P-code, P-desc, P-price) Call (Ca-id, C-code, P-code, Ca-result, Ca-date) Write the appropriate SQL commands for the following : (i) Find the name of customers to whom a call is not made. (ii) Find the total number of calls made to the customer whose name is 'Smith'. (iii) List C-name and P-desc of the calls made today. (iv) Find the product details of those products whose price is above average. (b) A university maintains database tables about the faculty and the students. FACULTY (f-id, f-name, subject-id) STUDENT (S-id, S-name, S-course, S-dob) MARKS (S-id, subject-id, marks) Constraints: One faculty teaches only one subject. (i) Identify suitable access rights for the faculty, students and database administrator' (ii) Create the access matrix for (i) above' (iii) Write the query modification statement for the students. 4. (a) Explain the three problems of concurrent transactionsin RDBMS, with suitablee xamples' 5 (b) What is a timestamp ? How does the system generate timestamps ? Explain the timestamp ordering protocol for concurrencyc ontrol' 5 cs-15 P.T.O.