SELECT $,$ P$\_$CODE, P$\_$PRICESELECT $,$ P$\_$CODE, P$\_{?}_{-}QO{H}^{**}{P}_{-}$PRICEQuestion $1$

Name the type of transparency $($location$,$ replication, failure, and concurrency$)$ that is

indicated by each statement.

a$)$ End users in New York and Tulsa are updating the Engineering Parts database

in San Mateo at the same time. Neither user is aware that the other is accessing

the data, and the system protects the data from lost updates due to interference.

b$)$ An end user in Tulsa deletes an item from the Standard Price List at the site.

Unknown to the user, the distributed DBMS also deletes that item from the

Standard Price List in San Mateo and New York.

c$)$ A user in San Mateo initiates a transaction to delete a part from San Mateo parts

and simultaneously to add that part to New York parts. The transaction is

completed in San Mateo but, due to transmission failure, is not completed in New

York. The distributed DBMS automatically reverses the transaction at San Mateo

and notifies the user to retry the transaction.

d$)$ An end user in New York requests the balance on hand for part number $33445.$

The user does not know where the record for this part is located. The distributed

DBMS consults the directory and routes the request to San Mateo.

FROM PRODUCT

WHERE $,$ P$\_QO{H}^{**}{P}_{?}$PRICE $>($SELECT AVG PRICE$)$ FROM

PRODUCT$)$

What is the likely data sparsity of the $P_{-}$QOH and $P_{-}$PRICE columns?

Should you create an index, what would the index column$($s$)$ be$,$ and why should you

create that index?

SELECT P$\_$CODE, P$\_$DESCRIPT, P$\_$QOH, P$\_$PRICE, V$\_$CODE

FROM PRODUCT

WHERE $,$ V$\_$CODE $=\text{'}21344\text{'}$

ORDER BY P$\_$CODE;

$12.$What index would you recommend, and what command would you use?

SELECT CUS$\_$CODE, MAX $($LINE$\_$TOTAL$)$

FROM CUSTOMER NATURAL JOIN INVOICE NATURAL JOIN LINE

WHERE CUS$\_$AREACODE $=\text{'}615\text{'}$

GROUP BY CUS$\_$CODE;

What indexes would you recommend for the query you above?

FROM PRODUCT

WHERE P$\_$PRICE $>=($SELECT AVG $($P$\_$PRICE$)$ FROM PRODUCT$)$ ;

What is the likely data sparsity of the P$\_$PRICE column?

Should you create an index? Why or why not?

SELECT P$\_$CODE, SUM$($LINE$\_$UNITS$)$

FROM LINE

GROUP BY P$\_$CODE

HAVING SUM$($LINE$\_$UNITS$)>($SELECT MAX $($LINE$\_$UNITS$)$ FROM LINE$)$;

What is the likely data sparsity of the LINE$\_$UNITS column?

Should you create an index? If so$,$ what would the index column$($s$)$ be and why

would you create that index?

Should you create an index on $P_{-}$CODE? If so$,$ write the SQL command to create

that index. If not, explain your reasoning.