Question

Intro to database systems question:

Question2 (20 points, 5 points each) Consider the following relational schema as part of a university database Prof(sin, pname, o_ce, age, sex, specialty, dept did) Dept(did, dname, budget, num majors, chair sin) Assume the following queries are the most common queries in the workload for this university and they are almost equivalent in frequency and importance Assume that both B+ trees and hashed indexes are supported by the DBMS and that both single and multiple-attribute index search keys are permitted. Specify the attributes you recommend indexing on, indicating whether each index should be clustered or unclustered and whether it should be a B+ tree or a hashed index. Note: If you can search with an index-only plan you should create a composite search key 1. Find the maximum age of professors in each department. Find the name of professors who have a user-specified research specialty List names of the departments with the budget more than 100K. List the department id and chairperson for departments with a user-specified department 2. 3. 4. ame

Answer 1

1. Since we want to find maximum age of professor in each department, there should be multiple attribute indexing of professor based on department code and age with clustering index with increasing order of department code and within same department code, decreasing order of age is better choice , so that first element in index for each department code will have the professor with maximum age. Since we have range query of finding maximum age, so B+ tree is better choice.

2. For this query build non-clustering index based on speciality since it does not make sense to physically store the records in sorted order of research speciality. Also since we want to perform search based on user specified value of research interest, hence hash function is better indexing which will help to quickly finding element satisfying the query .

3. Build non-clustering index based on budget since making clustered index on budget does not make sense(why to physically store the data in sorted order of budget). Also since we want to find all department with budget more than 100K, so this is range query and hence using B+ tree for indexing Budget is better choice.

4. Build non-clustering index on department name(since clustering is better to do for department id), and since we want to search department whose name matches with department name specified by user, hash index is better choice.

Please comment for any clarification.