Enhancing the sql interfacade Evaluation conventions

Yüklə 467 b.
ölçüsü467 b.

Enhancing the SQL Interfacade

  • Evaluation conventions

  • Implications for software design

  • A possible solution

Sources for SQLEDDI

  • The SQLEDDI interpreter was developed for CS233

  • Consult worksheets 6-7 on the CS233 website for:

  • details of how to run / download eden + SQL0/EDDI

  • pointers to documentation for EDDI and SQL0

  • These slides are essentially the file BeyondSQL0.ppt

  • You are advised to study worksheets 6 and 7 - they

  • supply the background for the CS319 lectures on SQL.

Interface or Interfacade?

  • Have an SQL0 interface serving as an SQL ‘interfacade’

  • SQL0 interface : decent interface to EDDI + not SQL

  • SQL interfacade : real SQL + obscure interface to EDDI

  • Improving the link involves

  • understanding how SQL is related to relational theory

  • exposing the ugly and complex semantics of SQL

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

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?

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 ...

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

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 ...

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 attribute names?

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?

Implementing standard SQL?

  • The ‘obvious’ implementation strategy is

  • - build an EDDI core intepreter with unnatural join

  • - translate SELECT * FROM R, S into ?R*S;

  • This FAILS because of the difficulty of matching

  • - the attribute names generated by an unnatural join

  • with

  • - the attribute names associated with an SQL query

Implementing 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

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

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

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);



  • 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);

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 modifying the SQL_to_EDDI translator and setting the evaluation conventions for the EDDI interpreter appropriately. The resulting ‘toysql’ interpreter can be studied by invoking tkeden with the startup configurations Run1.eden / Run4.eden in the ~empublic/projects/sqleddiBeynon2002 directory

Dostları ilə paylaş:

Verilənlər bazası müəlliflik hüququ ilə müdafiə olunur ©genderi.org 2019
rəhbərliyinə müraciət

    Ana səhifə