CS 333 Harvard University Database Systems Relational Schema Questions

Question: Indexing

Consider the following relational schema for a portion of a university database:

Washington State University
CptS 451 – Introduction to Database Systems
Homework-6
Question: Indexing
Consider the following relational schema for a portion of a university database:
Prof(ssn, pname, office, age, rank, specialty, did)
Dept(did, dname, budget, num_majors, chair_ssn)
Note that:
− ssn is the primary key for Prof and did is the primary key for Dept.
− Prof.did is a foreign key referencing Dept.did
− Each professor is involved with some department.
Suppose you know that the following queries are the six most common queries in the workload for this
university and all seven are roughly equivalent in frequency and importance:
1. List the names, ages, offices, and specialties of professors of a user-specified rank (e.g. ‘Associate
Professor’) who have a user-specified research specialty (e.g., ‘specialty-6491’). Assume that
the university has a diverse set of faculty members, making it uncommon for more than 10
professors to have the same research specialty.
2. List all the information for professors in a user specified age range (age between 30 and 40).
3. List the department id, department name, and chairperson name for departments with a budget
greater than 8,000K (budget>8000K).
4. List all the information about department chairs whose own research specialty is ‘specialty-6491’.
5. List the “did” of each department and the number of professors with “Associate Professor”
rank in that department.
6. Find the department(s) with the lowest budget. Return all department information.
These queries occur much more frequently than updates, so you should build whatever indexes you
need to speed up these queries. However, you should not build any unnecessary indexes (or include
any unnecessary attributes in an index), as updates will occur and would be slowed down by
unnecessary indexes. Given this information, decide which attributes should be indexed and whether
each index should be a clustered index or an unclustered index. Assume you create all these indexes on
a PostgreSQL database which supports both B+ trees and hashed indexes Assume that both single- and
multiple-attribute index keys are permitted. (Note: In PostgreSQL composite indexes are supported for
B+tree indexes, but not for hash indexes.)
HW6 Tasks:
1. Create a database named `hw6` and create the following tables in `hw6`.
2. Import the provided database backup.
Download link: https://eecs.wsu.edu/~arslanay/CptS451/project/hw6_db.zip
psql -U postgres -d hw6 -f hw6_db.sql
3. For each query given above:
− Write a correct SQL statement.
− Build whatever indexes you need to speed up these queries.
− For each index:
o identify the attributes you recommend indexing on (you can propose to create one or
more new indexes for each query or you may suggest to re-use the indexes that are
already created (proposed) for other queries. );
o indicate whether each index should be clustered or unclustered;
o indicate whether it should be a B+ tree or a hashed index;
o briefly describe how that index will be used to answer the query. You will be deduct
points if fail to describe how the index will help to speed up the query.
Submission Instructions:
HW6 will be submitted on Canvas. Write the SQL queries and the suggested indexes to a file and save it
as pdf. The Canvas dropbox will accept pdf submissions only.