Fundamentals of Database Systems 4e Solutions Chaper 9

Chapter 9: Introduction to SQL Programming Techniques

1

CHAPTER 9: INTRODUCTION TO SQL PROGRAMMING TECHNIQUES No exercises.

9.15 (optional) A materialized view is a view that is stored as a physical table. a. Rewrite the views created in Figure 9.1 so that they are materialized views. b. What are the advantages of materialized views over traditional views? What are the disadvantages? 9.16 Create a function in PSM that computes the median salary for the EMPLOYEE table shown in Figure 5.5. 9.16 a. CREATE CREATE MATER MATERIAL IALIZE IZED D VIEW VIEW WORKS_ WORKS_ON1 ON1  AS SELECT FNAME, LNAME, LNAME, PNAME, HOURS FROM EMPLOYEE, PROJECT, WORKS_ON WHERE SSN = ESSN AND PNO = PNUMBER; CREATE MATERIALIZED VIEW DEPT_INFO (DEPT_NAME, NO_OF_EMPS, TOTAL_SAL)  AS SELECT DNAME, COUNT(*), SUM(SALARY) FROM DEPARTMENT, EMPLOYEE WHERE DNUMBER = DNO GROUP BY DNAME b. If a materialized view that contains enough information, we are able to answer our query much faster. 9.17 This implementation of MEDIAN_SALARY avoids the use of procedural loops which are much slower than even such a complicated complicated query. This function can be broken down into into three major steps. First, we create an ordered list of salaries with ranking. Second, we select the element on or elements around the median point. Finally, we average those median values that were selected. CREATE FUNCTION MEDIAN_SALARY () RETURNS FLOAT RETURN (SELECT AVG(SALARY) AS MedianSalary FROM (SELECT COUNT(e1.SALARY) AS SalaryRank, e1.SALARY FROM EMPLOYEE AS e1 INNER JOIN EMPLOYEE AS e2 ON e1.SALARY > e2.SALARY OR (e1.SALARY = e2.SALARY  AND e1.SSN >= e2.SSN) GROUP BY e1.SSN HAVING COUNT(e1.SALARY) >= (SELECT COUNT(*)/2 FROM EMPLOYEE) - 0.5  AND COUNT(e1.SALARY) <= (SELECT COUNT(*)/2 FROM EMPLOYEE) + 0.5))  Alternatively, this function could have been been implemented using a CURSOR and looping. These two methods are functionally the same, however the method implemented here will be performed quicker due to the fact that the looping is internal to the database engine. Copyright © 2007 Pearson Education, Inc. Publishing as Pearson Addison-Wesley.