Assignment 3
Distributed: Nov 23/25 - Student Solutions: Nov 27/30 - Resolved: Nov 30/Dec 2

Information Systems for Business Applications, Harold Boley, Winter 1998

Conduct the dialog "A comparison of SQL and RelFun" (sql.script). In the following we assume some of the definitions made there; thus you may wish to re-perform it after issuing nopausebye via exec sql before testing your own definitions.

a) Give corresponding SQL and RelFun calls retrieving the Salary and Benefits of each employee in employee_statistics. Then modify the RelFun call such that it adds the retrieved Salary and Benefits. Now embed the addition results A into a unary unit constructor usd[A] and explain if you need an auxiliary ".="-assignment.

b) Give corresponding SQL and RelFun calls retrieving the Position of those having a salary of over $30,000. Then modify the RelFun call such that it lexicographically (via string<) sorts the result without duplicates. In both versions now include the condition that the benefits be less than or equal to $12,000.

c) Find the last names of those who ordered a chair, using SQL or RelFun. Complement to those who ordered something that is not a chair.

d) The `business rule' implemented by fintrans_antiques at the end of sql.bat permits "second-time owners", just preventing that they be sellers again. Change this into a shortrans_antiques definition such that even "second-time ownership" is prevented: trade histories should now end before owners buy back their antiques (hint: only the first clause needs an essential extra check). Test this with mored calls like

antique_owners(SellerID,smith,bob),
shortrans_antiques([SellerID],SellerID,BuyerID,pottery),
antique_owners(BuyerID,BuyerLastName,BuyerFirstName)
and with corresponding ones that exhibit the returned shortened trade histories.