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.

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.

Consider the following employee record in an organization

Employee (ID, name, date of joining, age, address, department, manager, ID’s of projects working on, role in the project, project name, project team leader, duration of project, dependent names)

An employee works in one department. Each department is managed by one manager. An employee can work on many projects. A projects has team leader. An employee can have many departments, however, one department can be related to only one employee.

Identify the functional dependencies in the relation given above. Normalize the relational up to BCNF. Make suitable assumptions, if any

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.

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.

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 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.