Enhancing the SQL Interfacade

1. Enhancing the SQL Interfacade Evaluation conventions Implications for software design A possible solution

2. Evaluation Conventions 1 • Three evaluation conventions in EDDI • no multiple rows • strict type checking on domains and attributes • use of natural join • Can change these via the Uneddifying Interface • See Worksheet 6 Questions 3-6 for illustration

3. Evaluation Conventions 2 • Standard SQL violates all three evaluation conventions: • allows duplicate rows - implements two types of selection: SELECT DISTINCT and SELECT • dispenses with type checking on attributes • uses “unnatural” join • Issue: How to implement standard SQL using EDDI?

4. The Uneddifying Interface • Worksheet 6 questions 3-6 expose many relevant issues by exercising The Uneddifying Interface: • issues for multiple rows and for the implementation of SELECT DISTINCT / SELECT • implications of more liberal type checking • problematic aspects of unnatural join • Summarise these in turn ...

5. Multiple rows • There is no syntactic support in EDDI for distinguishing SELECT from SELECT DISTINCT: ‘distinct’ refers to the evaluation context not the query • There is no support in EDDI for relations that are anything other than sets of tuples: is the standard SQL intention to have multisets of tuples? • “EDDI allows multiple rows” is not an invariant assertion about the contents of relation tables: multiplicity once introduced is not eliminated

6. Loose type checking • Loose type checking raises the key issue: • What is meant by the value of a relation? • EDDI : [name, price, qnt] are part of the value of ‘apple’ • The set of tuple values alone doesn’t define the relation • Are X+Y and Y+X the same relation if they are only compatible wrt domains not wrt attributes? • If YES, there are unpleasant implications ...

7. Unnatural join • Consider the unnatural join: ‘apple * allfruits’ • Joining [name, price, qnt] and [name, begin, end] leads to [name_1, price, qnt, name_2, begin, end] • Issue: is name_1 of the same type as name? etc • To implement unnatural join as an algebraic operator would like to ensure e.g. (X*Y)*Z  X*(Y*Z), or at very least that the two expressions are union-compatible • Issue: how to process the attributes names?

8. It’s no longer pure algebra • Implications of loose type checking and unnatural join: • values of algebraic expressions aren’t definable in a context-dependent way e.g. can’t determine how to name attributes without considering other issues, such as the context and the order of evaluation • laws of relational algebra and substitution properties that we expect of pure algebra (e.g. if X=3, then X*2 is the same thing as 3*2 = 6) no longer operate • Practical implications for implementing standard SQL?

9. Implications for standard SQL • Consider query such as • SELECT DISTINCT apple.name, A.name FROM allfruits A, apple, allfruits B WHERE B.name<>'granny' AND A.name<>B.name; • Table tagging (‘allfruits A’) and attribute annotation (‘apple.name’) are necessary syntactic features to support unnatural join

10. A possible implementation 1 • Unnatural join is implemented by referring to the FROM: • “FROM allfruits A, apple, allfruits B …” • Associated attributes are • [name,begin,end], [name,price,qnt], [name,begin,end] • Must disambiguate wherever natural join might operate: • name  A.name, apple.name, B.name • begin  A.begin, B.begin • Will assume that the SQL query is formulated so that this disambiguation is precisely what is essential: i.e. • tag a table only if it shares an attribute with other table

11. A possible implementation 2 • Now construct the natural join of the relations to be joined with attributes appropriately renamed: • For instance: • “FROM allfruits A, apple, allfruits B …” • translates to the natural join of the three relations • derived from allfruits, apple, allfruits resp. by renaming: • name >> A_name, begin >> A_begin, end >> A_end • name >> apple_name, price, qnt • name >> B_name, begin >> B_begin, end >> B_end • Translate attribute references as A_begin, apple_name

12. A possible implementation 3 • To deal with the distinction between SELECT and SELECT DISTINCT, introduce a pseudo relational operator “Makedistinct” to convert a multiset of tuples into a set of tuples. • This makes it possible to use queries such as that framed above in conjunction with view creation, as in • CREATE VIEW XXDIST AS (SELECT DISTINCT apple.name, A.name FROM allfruits A, apple, allfruits B WHERE B.name<>'granny' AND A.name<>B.name);

13. THE EDDI TRANSLATION OF THE SQL QUERY: • SELECT DISTINCT apple.name, A.name FROM allfruits A, apple, allfruits B WHERE B.name<>'granny' AND A.name<>B.name • %eddi • A_1 is allfruits % name >> A_name, begin >> A_begin, end >> A_end; • %eddi • apple_1 is apple % name >> apple_name, price, qnt; • %eddi • B_1 is allfruits % name >> B_name, begin >> B_begin, end >> B_end; • %eddi • expr_1 is ((A_1 * apple_1 * B_1) : B_name != "granny" . (A_1 * apple_1 * B_1) : • A_name != B_name) % apple_name, A_name • %eddi • distexpr_1 is Makedistinct(expr_1); • %eddi • ?(distexpr_1);

14. The SQLEDDI translator • A variant of the SQLEDDI environment that supports the translation of a richer subset of standard SQL according to the conventions outlined above can be derived from the basic SQL0 interface by including one additional file. • This file is the SQLextra.e file in the ~wmb/public/cs233/ directory. It is an eden file, and should be included with the %eden prompt or radio button in operation.