| 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.
| |