From: GW
To: Editor
Date: August 9, 2003

I have a question on Chris Date's response to the recently posted On An Old Celko Puzzle. I am trying to understand the proposed solution. Please let me know if I am missing something, or interpreting it incorrectly. Here is Mr. Date's solution:

WITH (salaries RENAME (sal_date AS prev_date,

                       sal_amt AS prev_amt)) AS t1,

     ((salaries JOIN t1) WHERE sal_date > prev_date) AS t2,

      ((salaries{emp} MINUS t2{emp}) JOIN salaries) AS t3,

      (EXTEND t3 ADD (DATE(1900-01-01) AS prev_date,

                      0.00 AS prev_amt)) AS t4:

     t2 UNION t4

 

I see how you derive the relvar T4, whose value consists only of those employees who do NOT have a previous salary (hence the EXTEND).

Obviously, the full answer requires a UNION with all those employees who do have a previous salary. But does T1 fit the bill? Doesn't T1 consist of all employees, with their columns renamed to "prev_date" and "prev_amt" respectively? Minimally, don't you have to apply the criteria shown in the specification of T2?

And once you have a set of all employees with at least one prior salary, don't you have to constrain the results to eliminate all but the current salary and the one immediately prior to it?

I ask all these questions because I am uneasy about Tutorial D (having been raised with SQL), and think I may be misinterpreting the syntax.

 

Ed. Note: T2 in red is a correction from T1 in the original, an error during editing and should take care of the question in GW’s second paragraph.]

 

 

Chris Date Responds: Thank you for your question.

 

Regarding the constraint question: Fair enough.

 

 

Probably. Possibly.  I don’t have the original problem statement. I probably didn’t regard this part of the problem as interesting, if it was part of the problem. Entire salary history seems more useful!

 

But here’s the fix (in red) if you’re interested

 

WITH (salaries RENAME (sal_date AS prev_date,

                       sal_amt AS prev_amt)) AS t1,

     ((salaries JOIN t1) WHERE sal_date > prev_date) AS t2,

      ((salaries{emp} MINUS t2{emp}) JOIN salaries) AS t3,

      (EXTEND t3 ADD (DATE(1900-01-01) AS prev_date,

                      0.00 AS prev_amt)) AS t4,

      (SUMMARIZE t2 BY {emp}

       ADD MAX(sal_date) AS sal_date) AS t5,

       (t5 JOIN t2) AS t6:

     t6 UNION t4

 

A note regarding SUMMARIZE: The BY form is a shorthand, described in AN INTRODUCTION TO DATABASE SYSTEMS, 8^th Ed., but not THE THIRD MANIFESTO, 2^nd Ed. book.

 

SUMMARIZE r BY {a}… <--> SUMMARIZE r PER r{a}…

 

Your understanding of Tutorial D is just fine! No misinterpretation that I can see.

 

 

Posted: 10/03/03

 

 

 

[ABOUT] [QUOTES] [LINKS]