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.

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?

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

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.