Question: Indexing

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

  • attachment_1attachment_1

UNFORMATTED ATTACHMENT PREVIEW

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.

Do you similar assignment and would want someone to complete it for you? Click on the ORDER NOW option to get instant services at essayloop.com

Do you have a similar assignment and would want someone to complete it for you? Click on the ORDER NOW option to get instant services at essayloop.com. We assure you of a well written and plagiarism free papers delivered within your specified deadline.