These queries occur much more frequently than updates, so you should build whate

Question

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 that both B+ trees and hashed indexes are supported by the DBMS, and that both single- and multiple-attribute index keys are permitted.

Consider the following relational schema for a portion of a university database: Prof (ssno, pname, office, age, sex, specialty, dept_did) Dept (did, dname, budget, num majors, chair ssno) Note that: - ssno is the primary key for Prof and did is the primary key for Dept. - Prof.dept_did is a foreign key referencing Dept.did -Each professor is involved with some department. Suppose you know that the following queries are the five most common queries in the workload for this university and alve ae roughly equivalent in frequency and importance: List the names, ages, and offices of professors of a user-specified sex (male or female) who have a user-specified research specialty (e.g., artificial intelligence). Assume that the university has a diverse set of faculty members, making it very uncommon for more than a few professors to have the same research specialty. 1. 2. List all the department information for departments with professors in a user specified 3. List the department id, department name, and chairperson name for departments with a 4. List the lowest budget for a department in the university. age range user-specified number of majors. 5. List all the information about professors who are department chairpersons.

Explanation / Answer

Index will be created to make select query faster in table So we have to use index over where condition column which are frequently used.

In Prof table we use sex,age. As in this table speciality is very uncommmon to have similar speciality in for very big number of professor.
We also used primary key in index.

CREATE INDEX idx_prof
ON Prof (ssno, sex, age,dept_did);

In Dept table we should use did , num_majors and chair ssno for index.

CREATE INDEX idx_dept
ON Dept (did, num_majors, chair ssno);

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.