1310 Specify the following views as queries in XQuery on the

13.10. Specify the following views as queries in XQuery on the company XML schema shown in Figure 13.5.

a. A view that has the department name, manager name, and manager salary for every department

b. A view that has the employee name, supervisor name, and employee salary for each employee who works in the Research department

c. A view that has the project name, controlling department name, number of employees, and total hours worked per week on the project for each project

d. A view that has the project name, controlling department name, number of employees, and total hours worked per week on the project for each project with more than one employee working on it

STUDENT STUDENT SECTION COURSE DEPARTMENT SECTION COURSE H DEPARTMENT H INSTRUCTOR INSTRUCTOR INSTRUCTOR 1 (a) (b) MT STUDENT N SECTION DEPARTMENT N INSTRUCTOR COURSE1 INSTRUCTOR1 COURSE (c) Figure 13.15 Converting a graph with cycles into a hierarchical (tree) structure.

Solution

a. A view that has the department name, manager name, and manager salary for every department.

LET $d := doc(www.company.com/info.xml)

FOR $x IN $d/company/department,

    $y IN $d/company/employee

WHERE $x/departmentManagerSSN = $y/employeeSSN

RETURN <res>    $x/departmentName

                $y/employeeName, $y/employeeSalary </res>

b.  A view that has the employee name, supervisor name, and employee salary for each employee who works in the Research department.

LET $d := doc(www.company.com/info.xml)

FOR $x IN $d/company/department[departmentName=\"Research\"],

    $y IN $d/company/employee,

    $z IN $d/company/employee

WHERE $y/departmentNumber = $x/departmentNumber AND $y/employeeSupervisorSSN=$z/employeeSSN

RETURN <res>    $y/employeeName, $y/employeeSalary,

                $z/employeeName </res>