14. (TCO 1) The SQL Relational Set operators can greatly simplify certain query tasks. However, not every relational database management system, such as MySQL, implements all of the relational set operators. Given the following partial ERD showing the BOOKS and ORDER_ITEMS tables, discuss how queries equivalent to the MINUS operator can be crafted, and provide an example SQL query, using the tables provided. Your answer should both characterize the general approach for replacing this relational set operator, as well as the specific SQL query to replace it in order to generate an equivalent result set.

The ORDER_ITEMS table contains records of all books that have ever been ordered. The BOOKS table contains a list of all books available for sale through the store. The following SQL will show BOOKIDs for those books currently available that have never been ordered previously.
SELECT BOOKID
FROM BOOKS
MINUS
SELECT BOOKID
FROM ORDER_ITEMS; (Points : 20)

 

Question 15.15. (TCO 2 and 9) Discuss the purpose and use of the EXPLAIN PLAN command, as it pertains to the MySQL DBMS. In your explanation, be sure to discuss specific examples of information which EXPLAIN PLAN can produce, its interpretation, and the actions that should be taken in response to various examples of EXPLAIN PLAN output in order to optimize performance. Be sure to touch on items such as a value of "ALL" being returned for type; considerations of differing column lengths and types; and what may be learned by comparing the EXPLAIN PLAN-predicted rows
returned with the actual number of rows returned by the query. (Points : 20)

 

Question 16.16. (TCO 6 and 8) Provide an example table of your own devising, incorporating a spatial index (do not duplicate the table used in the Lab 4 exercise, though you may study it as an example). Show the SQL required to create a spatial index on the table definition you provide. Show the SQL that would be required to output the coordinates of the points (x element, y element) in a human-readable format. Explain why you cannot simply use a "Select *" statement to obtain this. (Points : 20)

 

Question 17.17. (TCO 5 and 7) A DBA for a rapidly growing company is assigned the task of proposing three options to the CIO for implementing role-based authentication in the company's fast-growing, MySQL-based database operations. The CIO has requested a brief overview to present to management in straightforward business language, including the following information: 1) Explain role-based authentication (RBA), and its benefits; and 2) Discuss the relative merits, drawbacks, and any notable costs (at an overview, not detailed level) associated with options of a) implementing a custom (homebrew) solution; b) implementing RBA using MySQL Proxy; or c) switching to a commercial license of MySQL and using the Pluggable Authentication Modules (PAM). (Points : 20)

 

Question 18.18. (TCO 3, 4 and 10) Define OLTP and OLAP, and explain how and why the characteristics and tuning of DBMS systems differ. Describe a scenario in which a business problem could be addressed with an OLAP solution, specifically making use of a GROUP BY ROLLUP query, detailing specifically how the GROUP BY ROLLUP query would address the business problem. (Points : 20)

 

14. (TCO 1) The SQL Relational Set operators can greatly simplify certain query tasks. However, not every relational database management system, such as MySQL, implements all of the relational set operators. Given the following partial ERD showing the BOOKS and ORDER_ITEMS tables, discuss how queries equivalent to the MINUS operator can be crafted, and provide an example SQL query, using the tables provided. Your answer should both characterize the general approach for replacing this relational set operator, as well as the specific SQL query to replace it in order to generate an equivalent result set.

The ORDER_ITEMS table contains records of all books that have ever been ordered. The BOOKS table contains a list of all books available for sale through the store. The following SQL will show BOOKIDs for those books currently available that have never been ordered previously.
SELECT BOOKID
FROM BOOKS
MINUS
SELECT BOOKID
FROM ORDER_ITEMS; (Points : 20)

 

Question 15.15. (TCO 2 and 9) Discuss the purpose and use of the EXPLAIN PLAN command, as it pertains to the MySQL DBMS. In your explanation, be sure to discuss specific examples of information which EXPLAIN PLAN can produce, its interpretation, and the actions that should be taken in response to various examples of EXPLAIN PLAN output in order to optimize performance. Be sure to touch on items such as a value of "ALL" being returned for type; considerations of differing column lengths and types; and what may be learned by comparing the EXPLAIN PLAN-predicted rows
returned with the actual number of rows returned by the query. (Points : 20)

 

Question 16.16. (TCO 6 and 8) Provide an example table of your own devising, incorporating a spatial index (do not duplicate the table used in the Lab 4 exercise, though you may study it as an example). Show the SQL required to create a spatial index on the table definition you provide. Show the SQL that would be required to output the coordinates of the points (x element, y element) in a human-readable format. Explain why you cannot simply use a "Select *" statement to obtain this. (Points : 20)

 

Question 17.17. (TCO 5 and 7) A DBA for a rapidly growing company is assigned the task of proposing three options to the CIO for implementing role-based authentication in the company's fast-growing, MySQL-based database operations. The CIO has requested a brief overview to present to management in straightforward business language, including the following information: 1) Explain role-based authentication (RBA), and its benefits; and 2) Discuss the relative merits, drawbacks, and any notable costs (at an overview, not detailed level) associated with options of a) implementing a custom (homebrew) solution; b) implementing RBA using MySQL Proxy; or c) switching to a commercial license of MySQL and using the Pluggable Authentication Modules (PAM). (Points : 20)

 

Question 18.18. (TCO 3, 4 and 10) Define OLTP and OLAP, and explain how and why the characteristics and tuning of DBMS systems differ. Describe a scenario in which a business problem could be addressed with an OLAP solution, specifically making use of a GROUP BY ROLLUP query, detailing specifically how the GROUP BY ROLLUP query would address the business problem. (Points : 20)