Perform the following tasks using relational algebraic operations for the relations created at part

1. List all the courses of MCA programme.
2. Find the student name, programme code and the programme duration of the programme in which s/he is registered.
3. Find the list of students in BCA programme.

What is a deadlock? How can you detect deadlock? Explain with the help of a wait for graph. What are the possibilities that the deposit and withdrawal transactions as created in part (iii) will result into deadlock? Explain your answer.

Compare and contrast the two tier model to that of 3 tier model client server architecture. Can you implement the database given in question 3 as a 3 tier model? Explain your answer.

Consider the following relations

Account(ac_number,ac_holdername,ac_holderaddress,ac_balance)

Withdrawal(ac_number,wi_type,wi_date,wi_amount)

Deposit(as_number, de_by,de_date, de_type,de_amount)

Type of deposit or withdrawal may be –cash or cheque

Perform the following operations on these tables using SQL

1. Create the three tables giving suitable domains and constraints including referential actions.
2. Add one additional filed ac_type(it can be saving account, current account or student account) in the accounts table. Create a secondary index on ac_number for the withdrawal and deposit tables.
3. Create a view named my_account that contains the information about my account.
4. Give only READ only permission to see my_account view to a user.

Perform the following queries using SQL for the relations of part(i):

1. Find the list of the account holders whose account balance is less than Rs.1000/-
2. Find the details of those account holders who have withdrawal nore than Rs.1,00,000/- in the last month.
3. Find the sum of deposits that has been credited in each account in the last year.
4. Find an account holder who has not withdrawn any money in the last year.

Consider the database schema of Question 3. Create sample transaction log for the transactions that you have defined in Q3 part (iii). Explain how this log may be used to recover from failure.

Draw an ER diagram for a system having the following requirements:

A University maintains data of its students, the progarmmes they are registered in and the address information of the students. A programme consists of many sources. The database needs to store the programme duration and fees. A course has a number of credits associated with it and may be the part of more than one programmes. Some of the constraints that may be assumed for the University database system are:

• A student can taken only one programme at a time.
• A course may be part of more than one programme.
• The duration of the programme is in semester. A course is taught in atypical semester of the programme.

List all the entity sets, attributes of each entity sets and relationship sets. Draw the E-R diagram for the requirements as listed above for the database system. You may use the concept of keys, cardinality etc. in a proper way. Make and state suitable assumptions, if any.