The purpose of this assignment is to practice using indexing appropriately. The

Question

The purpose of this assignment is to practice using indexing appropriately. The ability to evaluate and implement various storage structures, index selections, and views is an important aspect of database management. Please note that when SQL queries are run, results are generated in the form of data. This data should be exported and saved to Excel for a visual check of accuracy. Create a Word document that includes the SQL query code used to explore the database tables and answer the following questions. You notice when running queries that the database is returning results very slowly. Discuss likely causes for the slow returns. Propose solutions for speeding the process. Include specific explanation that details why implementing the proposed solutions will speed returns. Discuss two indexing issues that can negatively affect databases. Provide specific examples in your discussion. The sales manager has received a customer request that all products be searchable according to color specifications. In this particular case, the customer is seeking on red products. Build a view consisting of ProductID, Product, and Color, and query the new view. The sales manager presented the customer with the view you created in question 3. The customer is still unhappy with the product list because many of the items were not available for bulk purchase or were not yet in storage, which resulted in delays. To address the problem, rebuild the view with multiple joins and add the parameter for "Finished Goods Storage" in the Location table. Bring in the "Quantity" field from the Inventory table. Rerun the query in the view.

Explanation / Answer

Cause of slow Returns

--------------------------------------------------

Huge amount of data and absence of indexes: When there are large amount of data in a Database table, the sequential search for a specific records in the table takes lot of time. To avoid this, a database table can be indexed.

Indexes are very similar to the indexes we have in out text books, when we want to search for something in out text books, we look at the index page to point it out.

Similarly when a database table is indexed, and when we fire a SELECT query, the database engine refers to the indexes to locate the data what we need.

--------------------------------------------------

Disadvantages of using Index

-----------------------------------------------

Indexes occupy space and hence additional storage will be required on the disk to store the indexes, however the storage space depends upon the data type and volume.

Indexes decrease the performance of UPDATE, INSERTS and DELETE operations on a table. This is because each time a row is updated or inserted or deleted, the database engine has to update all the indexes, and hence too many indexes will slow down the performance.

---------------------------------------------------------------------------

View for the customer to see all the red color items.

--------------------------------------------------------------------------------

Create view as Select ProductID, Product,Color
From Inventory where coloe = 'red';

-----------------------------------------------------------------------------------

Redesigned view to show the quantity of item available at a particular location

I am assuming the the location table is having columns like locationID, locationName,ProductId, quantity_aval

----------------------------------------------------------------------------------------------------------

Create view as Select ProductID, Product,Color,locationName,quantity_aval
From Inventory inv,
location loc
where inv.product_id = loc.product_id
and inv.color ='red';

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

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