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.

Now, assume that the bank schema as given in Question 3 is being implemented using distributed database. Suggest suitable data fragmentation needed for this distributed database.

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.

Compare and contrast the features of sequential, indexed and hashed file organizations. Consider the problem(ii) in this question; propose a suitable file organization for each of the relation created. Justify your selection of file organization.

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?

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.