MCAAssignments : CS-15 TMA Relational Database Management

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

This is Tutor Marked Assignment. There are four questions in this assignment. Answer all questions. Each question carries equal weightage. 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 current sizes:

Relation NameStudent
Attributes Roll number, Name, Address, Date of Birth, Phone, Programme, Year of start of Programme
Relation Size 5000 tuples

Relation Name Enrolment data (Subject wise)
Attributes Roll number, Subject Code, Date of Enrolment, Subject Grade
Relation Size 80,000 tuples
Relation Name Subjects
AttributesSubject Code, Credits, Subject Area, Fee,
Relation Size500 tuples

Please note that all UG and PG programmes are of 96 credits and a student gets a major, if s/he completes more than 48 credits in a specific subject area, otherwise s/he is given General UG, PG degree. A subject is to be completed within a year of enrolment and with a minimum C grade. Make suitable assumptions, if any and create the tables. Now, answer the following queries using SQL:

(a) Find the list of the students who have completed the UG programme with any major.
(b) Find the details of the subject that has the maximum enrolment.
(c) Find the fee deposited by each student who has started the programme in 2001.
(d) Find the subjects that has maximum failure rate.

Find the best strategy to evaluate above queries.

Question 2: What is the need of Log files? What does a Log file contain? Describe how can log file is used for transaction recovery. What is shadow paging? Describe with the help of example. Compare and contrast shadow paging to Checkpoint mechanism. Describe the states of a transaction.

Question 3: Consider the following concurrent transactions:

T1: Deposition of fee by the student (Information is being put in the
database system).
T2: Creation of study centre wise list of the students who have paid
the fee.
T3: Generation of Fee Receipt details of the University.

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 a deadlock in the context of database system? When can it occur in distributed database management system? Describe with the help of an example. What is a semi join operation? How does it increase the efficiency of distributed database query processing? Describe with the help of example.

Comment for MCAAssignments : CS-15 TMA Relational Database Management
Please provide the latest MCA I sem question paper

Home > Tutor Mark Assignments - MCA > MCAAssignments : CS-15 TMA Relational Database Management
Comments are closed.