Course Code  CS-15
Course Title Relational Database Management System
Assignment Number  MCA(6)-15/TMA/2003
Maximum Marks  10
Last Date of Submission  15th October, 2003

This is a Tutor Marked Assignment. There are four questions in this assignment. Answer all the questions. Each question carry equal marks. You may use illustrations and diagrams to enhance explanations. Answer to each part of the question should be short not exceeding 300 words unless specified and to the point.  Please go through the guidelines regarding assignment given in the Starter Kit for the format of presentation.

Question 1: Consider the following Relations and their present size:
Relation Name Attributes Relation Size
SUPPLIER Supplier Number, Name, Address, City, phone 50 tuples
PARTS  part number, name, colour, we g t, Quantity in stock, reorder level 200 tuples
SUPP_PARTS Supplier Number, Part Number, date of supply quantity of supply 20,000 tuples

Make suitable assumptions, if any and create the tables. Now, answer the following queries using SQL:
(a)   Find the name of the suppliers who have supplied all the parts (Please note the parts may be supplied on different dates).
(b)   Find the name of the suppliers who had supplied the parts that are to be reordered.
(c)   Find the rnaximum of total quantity of supply of a day.
(d)   Find the details of the parts having maximum supply. Find the best strategy to evaluate above queries.

Question 2:  What is a Transaction? What are its states? Describe the Schedule of Parallel transactions with the help of an example  What is a LOG? Describe how log can be used to recover from various types of failure. (You must describe the above with the help of a meaningful transactions pseudo-code and properly relating it to LOG file entries). What is the need of Check  pointing?
Question 3: Consider the following concurrent transactions:

TI:    Issue of requested quantity of parts to the production line (Uses inventory records)

T2:    Issue of Purchase order for a part and putting this information in the pending order table

T3:    Receipt of parts (This will require comparing material receipt voucher to ordered quantity indicating
        discrepancies, if any. It also then updates the inventory records for the relevant parts)

What may be the problems that may be encountered on concurrent execution of the above transactions? Assuming that all the three transactions are valid, write pseudo codes for above transactions. Create a serializable schedule using hierarchy of locks with intention mode locking scheme.

Question 4: What is two phase commit Protocol?     How is a transaction committed in case of a distributed transaction processing system? Describe    with the help of an   example.  What is Deadlock? How is it detected in centralized and distributed management systems ? Describe with the help of an example.

     

 

!uestion 4: