What are the problems associated with data Redundancy in a relation? How can you solve those problems? Can referential integrity constraints help in addressing those problem? Give reasons in support of your answer.

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.

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.

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.

For creating a student information management system of a University a database management system is better than that of file management system.” Justify the statement given above. Now, assume that you are assigned the role of database administrator for the University database. What are the key responsibilities you have to handle?

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.

Write the psedocode for the withdrawal and deposit transactions for the schema of the bank given in this question. Use two phase locking protocol and lock the data items suitably such that there is no concurrency related problem in the database.