Question

Can someone please give a detailed solution for the problem? Just an explicit answer. Thank you very much.

a) How, in general, does a query optimizer use histograms? Make up an example in which a histogram might be useful.

b) Explain why, how, or whether the word SELECT in relational algebra (represented by the Greek letter sigma) is analogous to the WHERE clause in SQL. Also, explain how the word proJECT in relational algebra (represented by Greek letter pi) is somewhat analogous to SELECT in SQL.

Answer 1

a) Adding a histogram in query optimization makes the query run faster than the actual query.It helps to solve complex queries. It can used to do the calculations for complex statistical measures using the query.

Example for Histogram SQL statement which displays the purchase made by the customer based on their ages.Data of customer ages which is used for table Customer_Purchases.

SELECT
Sum(IIf([Age]<18,1,0)) AS Group1,
Sum(IIf([Age]>=18 and [Age]<30,1,0)) AS Group2,
Sum(IIf([Age]>=30 and [Age]<40,1,0)) AS Group3,
Sum(IIf([Age]>=40 and [Age]<50,1,0)) AS Group4,
Sum(IIf([Age]>=50 and [Age]<60,1,0)) AS Group5,
Sum(IIf([Age]>=60,1,0)) AS Group6
FROM Customer_Purchases;

OUTPUT

Group1	Group2	Group3	Group4	Group5	Group6
1	5	11	30	8	13

b) SELECT clause is used to choose the subset of the rows from the table, whereas the WHERE clause chooses the subset of the rows from the table based on condition.

Example: SELECT * FROM CUSTOMER

shows all rows from the table customers.

SELECT * FROM CUSTOMER WHERE id = 'C005'

shows the only the data for id = 'C005' based on the specified condition  

PROJECT is used to reorder and select,get rid of attributes from the table.PROJECT operator itself do selection of data.