Create the relations from the E-R diagram that you have drawn for part (ii). The relations must be at least in 2NF. You must do the following with the relations

1. Enter about 5 sets of meaningful data in each of the relations
2. Identify the domain of various attributes
3. Identify the primary keys of all the relations
4. Identify the foreign keys and referential integrity constraints integrity constraints in the relations.

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.

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?

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.

Consider a relation student(ID: 9 characters) having about 1,000,000 students records. The database is stored on a disk having a disk block size of 1 MB. Assume that the primary index of the relation is ID and its relation is required mostly for the application that generates programme wise list of student names in alphabetical order. Create a secondary index that will improve the performance of the system for the given application. Show how many block transfers will saved on average due to creation of index. Make suitable assumptions if any.

Consider the database schema of Question3, suggest at least two different types of users for the system. You may use my_account view for one of the users. Create an authorization matrix for the relations. Make suitable assumptions, if any.