Question

Question2

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.

a. Find the maximum age of professors in each department.

b. Find the name of professors who have a user-specified research specialty.

c. List names of the departments with the budget more than 100K.

d. List the department id and chairperson for departments with a user-specified department name.

Answer 1

Answer:

a. Select Dept.dname, max(Prof.age) from Dept, Prof where Dept.did = Prof.dept_did group by Prof.dept_did;

b. Select pname from Prof where specialty = "user-specified research";

c. Select dname from Dept where budget > 100000;

d. Select Dept.did, Dept.dname, Prof.pname from Dept, Prof where Dept.chair_sin = Prof.sin;