Consider the following BCNF relation, which lists the ids, types (e.g., nuts or bolts), and costs of

Question:

Consider the following BCNF relation, which lists the ids, types (e.g., nuts or bolts), and costs of various parts, along with the number available or in stock:
Parts (pid, pname, cost, num avail)
You are told that the following two queries are extremely important:
• Find the total number available by part type, for all types. (That is, the sum of the num avail value of all nuts, the sum of the num avail value of all bolts, and so forth)
• List the pids of parts with the highest cost.
1. Describe the physical design that you would choose for this relation. That is, what kind of a file structure would you choose for the set of Parts records, and what indexes would you create?
2. Suppose your customers subsequently complain that performance is still not satisfactory (given the indexes and file organization you chose for the Parts relation in response to the previous question). Since you cannot afford to buy new hardware or software, you have to consider a schema redesign. Explain how you would try to obtain better performance by describing the schema for the relation(s) that you would use and your choice of file organizations and indexes on these relations.
3. How would your answers to the two questions change, if at all, if your system did not support indexes with multiple-attribute search keys?
Fantastic news! We've Found the answer you've been seeking!

Step by Step Answer:

Related Book For  book-img-for-question

Database management systems

ISBN: 978-0072465631

3rd edition

Authors: Raghu Ramakrishan, Johannes Gehrke, Scott Selikoff

Question Posted: