CS240A Project 2

XQuery XSLT

XQuery

1. Retrieve the employment history of employee 'Joe Doe' (i.e., the departments where he worked and the periods during which he worked there).
   
   XQuery Result
   
   
2. Retrieve the name and the salary of employees whose salary, on 1994-05-06, was above 85,000.
   
   XQuery Result
   
   
3. For all departments, show their manager history in the period starting on 1994-05-06 and ending 1995-05-06.
   
   XQuery Result
   
   
4. Count the number of employees each manager managed in the last two years.
   
   XQuery Result
   
   
5. For each department, show the count of their employees on 1999-01-01.
   
   XQuery Result
   
   
6. Show the complete history of the average salary for all the employees in XYZ.
   
   XQuery Result
   
   Explanation:
   There are two functions in this xquery. The first one, calAverage, is to return salaries of all employees at a given time. The second one, allDate, is to return all the date where there is a possible change of average salary ordered in ascending order (In fact, these date are given by reporting all the tstart value of the salary element after removal of duplicate). The query logic is to find all these possible date where the average salary will change (using allDate), and calculate the average of salary by that time by finding the salary of all employees by that time (using calAverage). This query uses a lot of memory (~900MB) and takes about 1.5 hours to finish in a P4 2.8GHz machine.
   (Only an attribute tstart is shown (instead of both tstart and tend) in the average element because the period where the average salary is valid is automatically given by tstart and the tstart of next average element.)
   
   
7. find the longest period during which there is no change of title of employees.
   
   XQuery Result  supplementary data
   
   Explanation:
   My interpretation on this query is that, during the whole lifespan of the XYZ company, employees are changing their title, we are going to figure out the longest period between the title changes of any employees in this company. By change of title, we mean the employee must have at least 2 "title" sub-elements, and the change. Suppose an employee has 5 title sub-element, only the tend attribute of the 1st, 2nd, 3rd, and 4th element will be counted as valid time of title change (in other words, for employees with only 1 title during their service in the company, we consider there is no title change). Thus, we define a function allRange() to find out all the instance of title change of employees, together with the firstDate function, which return the earliest tstart attribute in all title sub-elements, we get a list of dates representing a change of title of employees. We define a recursive function difference, which takes in the list of date, calculate the difference in days between two consecutive elements using the subtract-dates function (sadly, the XQuery proposed standard subtract-dates function is not supported by QuiP). Then we sort the output from the difference function descending based on number of difference in days, and output the first element as the longest period.
   
   As subtract-dates function is not supported by QuiP, we have no way to complete the query, the supplementary data shows the result return by the difference function, which record all the period where there is no change of title of employees, by checking the supplementary data, we figure out the longest period is from 1985-02-15 to 1990-02-18, and the xquery q7.xquery will function correctly if the subtract-dates function is supported by QuiP.
   
   
8. Find the employees who never got a salary raise from the time they joined to the time their job title was changed.
   
   XQuery Result
   
   Explanation:
   My interpretation on this query is that, when an employee first join the company (denoted by the tstart value of the title element), until he change the title (denoted by the tstart value of the second title element, if any)), check if there is any raise in his salary during this period (as long as there is a raise within this period, we will count it as a raise even the overall trend in this period is dropping). Thus the program logic is like the following: I first select those employee with more than one "title" sub-elements under the employee element, then i extract the tstart attribute of the first and second title element. I make use of two functions. The first one is allsalary, which given an employee number, and the starting and ending date, it will return the list of salary element of this employee during the stated period. The second one is checkraised, which require a sequence of salary element (under ascending chronological order), then it check whether there is a raise of salary between two consecutive period, thus it is a recursive function by recursively calling the subsequence of the list. If there is such a raise, it will return the raise period, if not, nothing is returned. The main query thus use this function "checkraised" to test whether there exists such a period and return information of the employee when nothing is returned. The result of this query is a empty-set, which means that all employees receive at least one salary raise during the period they first join the company to the time their job title was changed.
   

Extra Credits

1. v-emps.xml  xslt  combined output
   
   
2. v-depts.xml  xslt  combined output