DBI 001 Dotazovac jazyky Formlna smantika SQL Alexander

DBI 001 - Dotazovací jazyky Formálna sémantika SQL Alexander Kuzmin, Tomáš Kovařík

Obsah • Úvod, výhody formalizácie jayzka SQL • Trojhodnotový n-ticový predikátový kalkul • Popis gramatiky jazyka SQL • Prevod SQL na E 3 VPC • Ekvivalencia SQL dotazov

Prečo formalizovať SQL? • Optimalizácia dotazov • Určenie výpočetnej sily jazyka • Ekvivalencia SQL dotazov • Vývoj a vylepšovanie samotného jazyka SQL

Ako budeme postupovať • Zavedenie E 3 VPC (Extended 3 -valued tuple predicate calculus) • Výrazy, termy, operátory, formule, kvantifikácia • Rozšírenia • Formálna definícia gramatiky jazyka SQL • Prevod SQL dotazov na E 3 VPC • Ekvivalencia SQL dotazov

E 3 VPC • Založený na klasickom 2 hodnotovom n-ticovom predikátovom kalkule • Základným rozdielom je využitie 3 hodnotovej logiky • Obohatený o niektoré rozšírenia potrebné k definícii sémantiky jazyka SQL

E 3 VPC - Výrazy • E 3 VPC výraz má štruktúru: {t(v 1, . . . , vn): ||P(v 1, . . . , vn)||α} kde v 1, . . . , vn n-ticové premenné t(v 1, . . . , vn) cieľový zoznam výrazov v tvare v 1 R 1, . . . , vn Rn, R 1, . . . , Rn sú relácie (tabuľky) P(v 1, . . . , vn) predikátová formula || · ||α, α {T, F} interpretačný operátor

E 3 VPC - Operátory =, , <, klasické operátory pre porovnávanie • ak je jeden z operandov null, výsledok je unknown porovnanie s hodnotou null: • Správa sa rovnako operátor = okrem prípadu, keď majú oba operandy hodnotu null. V tom prípade je výsledok true. · unárny operátor vonkajšej väzby • Rozoberieme podrobne neskôr || · ||α unárny operátor interpretácie, α {T, F} • Opäť podrobne neskôr

E 3 VPC - Termy • konštanta je term • v premenná, a atribút => v. a, v. a sú termy • S výraz, v premenná, a atribút, F agregačná funkcia => F(v. a)S je term F {COUNT, AVG, SUM, MIN, MAX, COUNTD, AVGD, SUMD} Funkcie COUNTD, AVG a SUMD odpovedajú SQL funkciám COUNT/AVG/SUM DISTINCT

EVPC – Formule • Atomické formule • t 1, t 2 termy, operátor porovnania => t 1 t 2 je atomická formula • True, False, Unknown (T, F, U) sú atomické formule • P atomická formula => ||P|| je atomická formula • Vytváranie zložitejších formulí • atomická formula je formula • P, Q formule => P, P Q sú formule • S výraz, Q formula => S Q, S Q sú formule • Žiadne ďalšie výrazy nie sú E 3 VPC formule

Interpretácia E 3 VPC formulí • Výsledkom interpretácie formule je jedna z troch hodnôt: true, false, unknown • Vzniká problém s významom výrazov {x R : P(x)} pre x také, že P(x) = U • Je potreba transformovať 3 -hodnotovú formulu na 2 -hodnotovú

Operátor interpretácie • • • P(x) E 3 VPC formula, Q(x) 2 VPC formula. Q(x) je pozitívne interpretovaný 2 -hodnotový ekvivalent P(x), značíme ||P||T, ak pre každé x platí: a) P(x) T => Q(x) T b) P(x) F => Q(x) F c) P(x) U => Q(x) T Q(x) je negatívne interpretovaný 2 -hodnotový ekvivalent P(x), značíme ||P||F, ak pro každé x platí: a) P(x) T => Q(x) T b) P(x) F => Q(x) F c) P(x) U => Q(x) F

Operátor vonkajšej väzby • Operátor vonkajšej väzby mení rozsah platnosti premennej • v sa odkazuje na premennú na najbližšej vyššej úrovni • Napríklad vo výraze {v S: {v T: || v. a = v. b ||T}. . . } Vo výraze v. a = v. b ide raz o premennú v T a druhýkrát je to premenná v S vďaka použitiu operátora . • Operátor výrazne uľahčuje prevod SQL dotazov

Vyhodnotenie E 3 VPC výrazu • E 3 VPC výraz: {v 1 R 1, . . . , vk Rk: Q(v 1, . . . , vk)} • výsledkom vyhodnotenia výrazu je množina n-tíc vybraných z relácií R 1, . . . , Rk, ktoré po dosadení za príslušné premenné v 1, . . . , vk spĺňajú formulu Q • Q musí byť formula 2 VPC (použitie ||. ||a) • predpokladáme, že n-tice v reláciách sú navzájom rôzne

Kvantifikované formule U = {x S: P(x)} Q(x) ( x) x S P(x) Q(x) E = {x S: P(x)} Q(x) ( x) x S P(x) Q(x) Nech M = {x S: P(x)}, potom platí: U true U false U unknown ( x M) Q(x) true ( x M) Q(x) false inak E true E false E unknown ( x M) Q(x) true ( x M) Q(x) false inak

Príklad Schéma: oddelenia(id, pocet_zam, mesto, manazer) zamestnanci(id, oddelenie_id, mesto) SQL: SELECT odd. manazer FROM oddelenia odd WHERE odd. mesto = ALL SELECT zam. mesto FROM zamestnenci zam WHERE zam. oddelenie_id = odd. id GROUP BY odd. manazer HAVING AVG(odd. pocet_zam)>500 E 3 VPC: {odd oddelenia: ||( {zam zamestnenci: || zam. oddelenie_id = odd. id ||F} odd. mesto = zam. mesto) AVG(odd. pocet_zam){odd oddelenia: ||( {zam zamestnenci: || zam. oddelenie_id = odd. id ||F} odd. mesto = zam. mesto) odd. manager ||F}>500 ||F}

Pokračovanie… • Zavedenie E 3 VPC (Extended 3 -valued tuple predicate calculus) • Formálna definícia gramatiky jazyka SQL • Syntaxou riadený preklad • Popis syntaxe SQL pomocou gramatiky • Prevod SQL dotazov na E 3 VPC • Ekvivalencia SQL dotazov

Úpravy SQL • • Vychádzame z normy ANSI SQL 85 Pridávame nasledujúce úpravy: 1. Výrazy „boolean expression of“ a „list of“ sú skratkou pre množinu zrejmých pravidiel. 2. Aritmetické výrazy s atribútmi nie sú uvažované. 3. Používanie aliasov relácií je povinné. 4. Do niektorých pravidiel sú zavedené ďalšie neterminály, prípadne sú niektoré pravidlá rozdelené na viac menších.

Gramatika SQL dotazov • Dotazy, ktoré vracajú n-ticu: <QUERY> : : = SELECT [ALL | DISTINCT] <SELECT LIST><FROM CLAUSE> [<WHERE CLAUSE>][<GROUP BY CLAUSE>][<HAVING CLAUSE>] • Dotazy, ktoré vracajú hodnotu alebo stĺpec: <SUBQ> : : = SELECT [ALL | DISTINCT] <COL OR VAL><FROM CLAUSE> [<WHERE CLAUSE>][<GROUP BY CLAUSE>][<HAVING CLAUSE>] • Dotazy, ktoré vracajú výsledok agregačnej funkcie: <AF SUBQ> : : = SELECT [ALL | DISTINCT] <FUNCTION><FROM CLAUSE> [<WHERE CLAUSE>][<GROUP BY CLAUSE>][<HAVING CLAUSE>]

<SELECT LIST> : : = “list of <SELECT ELEMENT>” <SELECT ELEMENT> : : = <COL OR VAL> | <FUNCTION> <COL OR VAL> : : = <alias>. <column> | <literal> <FUNCTION> : : = <COUNT> | <AGGR> <COUNT> : : = <COUNT DISTINCT> | COUNT(*) <COUNT DISTINCT>: : = COUNT (DISTINCT <alias>. <column>) <AGGR> : : = <AGGR DISTINCT> | <AGGR ALL> <AGGR DISTINCT> : : = <AGGR NAME>(DISTINCT <alias>. <column>) <AGGR ALL> : : = <AGGR NAME>([ALL ]<alias>. <column>) <AGGR NAME> : : = AVG | MAX | MIN | SUM

<* CLAUSE> <FROM CLAUSE> : : = FROM “list of <TABLE REFERENCE>” <TABLE REFERENCE> : : = <table> <alias> <WHERE CLAUSE> : : = WHERE <WHERE CONDITION> <HAVING CLAUSE> : : = HAVING <HAVING CONDITION> <GROUP BY CLAUSE> : : = GROUP BY “list of <alias>. <column>” • • do klauzule FROM nie je možné vnoriť SELECT je možné vnoriť len do klauzulí WHERE, HAVING, za IN, NOT IN, EXISTS a operátory SOME a ALL

<* [AF] PRED> • Bez agregačnej funkcie v poddotaze: <SOME Q. PRED>: : =<COL OR VAL><comp op>SOME<SUBQ> <ALL Q. PRED>: : =<COL OR VAL><comp op>ALL<SUBQ> <COMPLEX IN PRED>: : =<COL OR VAL>IN<SUBQ> <COMPLEX NOT IN PRED>: : =<COL OR VAL>NOT IN<SUBQ> <COMPLEX COMP PRED>: : =<COL OR VAL><comp op><SUBQ> • S agragačnou funkciou v poddotaze: <SOME Q. AF PRED>: : =<COL OR VAL><comp op>SOME<AF SUBQ> <ALL Q. AF PRED>: : =<COL OR VAL><comp op>ALL<AF SUBQ> <COMPLEX IN AF PRED>: : =<COL OR VAL>IN<AF SUBQ> <COMPLEX NOT IN AF PRED>: : =<COL OR VAL>NOT IN<AF SUBQ> <COMPLEX COMP AF PRED>: : =<COL OR VAL><comp op><AF SUBQ> <EXISTS PRED>: : =EXISTS <SUBQ>

<HAVING CONDITION>: : =“boolean expression of <HAVING PRED>” <HAVING PRED>: : =<H SIMPLE PRED> | <H COMPLEX PRED> | <H AF COLUMN PRED> | <H AF FUNCTION PRED> | <H AF COMPLEX PRED> <HAVING SIMPLE PRED>: : =<SIMPLE PRED> <HAVING COMPLEX PRED>: : =<COMPLEX PRED> <HAVING AF COLUMN PRED>: : =<FUNCTION><comp op><COL OR VAL> <HAVING AF FUNCTION PRED>: : =<FUNCTION><comp op><FUNCTION>

<AF * [AF] PRED> • Bez agregačnej funkcie v poddotaze: <AF <AF <AF SOME Q. PRED>: : =<FUNCTION><comp op>SOME<SUBQ> ALL Q. PRED>: : =<FUNCTION><comp op>ALL<SUBQ> COMPLEX IN PRED>: : =<FUNCTION>IN<SUBQ> COMPLEX NOT IN PRED>: : =<FUNCTION>NOT IN<SUBQ> COMPLEX COMP PRED>: : =<FUNCTION><comp op><SUBQ> • S agragačnou funkciou v poddotaze: <AF <AF <AF SOME Q. AF PRED>: : =<FUNCTION><comp op>SOME<AF SUBQ> ALL Q. AF PRED>: : =<FUNCTION><comp op>ALL<AF SUBQ> COMPLEX IN AF PRED>: : =<FUNCTION>IN<AF SUBQ> COMPLEX NOT IN AF PRED>: : =<FUNCTION>NOT IN<AF SUBQ> COMPLEX COMP AF PRED>: : =<FUNCTION><comp op><AF SUBQ> <EXISTS PRED>: : =EXISTS <SUBQ>

Pokračovanie… • Zavedenie E 3 VPC (Extended 3 -valued tuple predicate calculus) • Formálna definícia gramatiky jazyka SQL • Prevod SQL dotazov na E 3 VPC • Ekvivalencia SQL dotazov

Překlad SQL => E 3 VPC

Pravidla překladu • • Výsledek překladu symbolu w je řetězec symbolů w. S některými pravidly gramatiky SQL jsou spojena překladová pravidla. Překladové pravidlo pro symbol w definuje w. Některá syntaktická pravidla mohou mít i více překladových pravidel. Rozlišíme je indexem: i • Použití závisí na kontextu.

Překlad terminálů • • Je-li w terminál, pak w : : = w. Význam přeložených SQL terminálů v E 3 VPC: proměnná <column> atribut <table> relace <literal> konstanta <comp op> operátor porovnání <alias>

Implicitní překlad • • • Buď LHS: : =RHS syntaktické pravidlo, w 1, . . . , wk neterminály na jeho pravé straně v pořadí zleva doprava. Nechť neexistuje pravidlo překladu pro LHS: : =RHS. Pak LHS : : = w 1. . . wk syntaktické pravidlo: <WHERE CLAUSE>: : =WHERE <WHERE CONDITION> překlad: <WHERE CLAUSE> : : = <WHERE CONDITION>

Pravidla popsaná metajazykem 1<FUNCTION> : : = odpovídající jméno agregační funkce a v závorkách její parametr 2<FUNCTION> : : = hodnota odpovídající funkce pro prázdnou množinu “boolean expression of <X>” : : = odpovídající výraz v E 3 VPC, v němž jsou predikáty nahrazeny svými překlady “list of <X>” : : = seznam přeložených neterminálů oddělených čárkami <COL OR VAL> : : = konstanta nebo proměnná. atribut

<QUERY> syntaktické pravidlo: <QUERY> : : = SELECT [ALL | DISTINCT] <SELECT LIST><FROM CLAUSE> [<WHERE CLAUSE>][<GROUP BY CLAUSE>][<HAVING CLAUSE>] pravidlo překladu: <QUERY> : : = { <FROM CLAUSE>: || <WHERE CLAUSE> <HAVING CLAUSE>||F} • negativní interpretace • <GROUP BY CLAUSE> se projeví až při překladu <HAVING CLAUSE> a při překladu agregačních funkcí • <SELECT LIST> není použit – jedná se o vnější SELECT

<* CLAUSE> <FROM CLAUSE> : : = FROM “list of <TABLE REFERENCE>” <TABLE REFERENCE> : : = <table> <alias> <WHERE CLAUSE> : : = WHERE <WHERE CONDITION> <HAVING CLAUSE> : : = HAVING <HAVING CONDITION> <GROUP BY CLAUSE> : : = GROUP BY “list of <alias>. <column>” pravidla překladu spojená s těmito syntaktickými pravidly: <TABLE REFERENCE> : : = <alias> <table> <GROUP BY CLAUSE> : : = <alias>. <column> [ <alias>. <column>]. . . zbývá definovat: <WHERE CONDITION>, <HAVING CONDITION>

<* CONDITION> implicitní překlad a překlad popsaný metajazykem: <WHERE CONDITION>: : =“boolean expression of <WHERE PRED>” <WHERE PRED>: : =<SIMPLE PRED> | <COMPLEX PRED> <SIMPLE PRED>: : =<COL OR VAL><comp op><COL OR VAL> <COMPLEX PRED>: : =. . . | <ALL QUANTIFIED PRED> |. . . <HAVING CONDITION>: : =“boolean expression of <HAVING PRED>” <HAVING PRED>: : =. . . | <H AF COLUMN PRED> |. . . zbývá tedy přeložit: <ALL QUANTIFIED PRED>, <H AF COLUMN PRED>, . . .

<ALL QUANTIFIED PRED>, . . . <ALL QUANTIFIED PRED>: : =<COL OR VAL><comp op>ALL<SUBQ> 1<SUBQ><COL OR VAL> <comp op> 2<SUBQ> <SOME QUANTIFIED PRED>: : =<COL OR VAL><comp op>SOME<SUBQ> 1<SUBQ><COL OR VAL> <comp op> 2<SUBQ> <COMPLEX IN PRED>: : =<COL OR VAL>IN<SUBQ> 1<SUBQ><COL OR VAL> = 2<SUBQ> <COMPLEX NOT IN PRED>: : =<COL OR VAL>NOT IN<SUBQ> 1<SUBQ><COL OR VAL> 2<SUBQ> <EXISTS PRED>: : =EXISTS <SUBQ> 1<SUBQ>. . .

<SUBQ> : : = SELECT [ALL | DISTINCT] <COL OR VAL><FROM CLAUSE> [<WHERE CLAUSE>][<GROUP BY CLAUSE>][<HAVING CLAUSE>] výsledek poddotazu může být množina hodnot: 1<SUBQ> = { <FROM CLAUSE>: || <WHERE CLAUSE> <HAVING CLAUSE>||F} výsledek poddotazu musí být jenom jedna hodnota: 2<SUBQ> = <COL OR VAL>

<H AF COLUMN PRED>, . . . <H AF COLUMN PRED>: : =<FUNCTION><comp op><COL OR VAL> 1<FUNCTION> { <FROM CLAUSE>: || <WHERE CLAUSE> <GROUP BY CLAUSE>||F} <comp op> <COL OR VAL> <H AF FUNCTION PRED>: : =<FUNCTION><comp op><FUNCTION> 1<FUNCTION> { <FROM CLAUSE>: || <WHERE CLAUSE> <GROUP BY CLAUSE>||F} <comp op> 1<FUNCTION> { <FROM CLAUSE>: || <WHERE CLAUSE> <GROUP BY CLAUSE>||F} <H SIMPLE PRED>: : =<SIMPLE PRED> { <FROM CLAUSE>: || <WHERE CLAUSE> <GROUP BY CLAUSE>||F} <SIMPLE PRED>. . .

Příklad SQL: SELECT d. manager FROM dept d WHERE d. location = ALL SELECT e. residence FROM emp e WHERE e. dept. Id = d. id GROUP BY d. manager HAVING AVG(d. nof. Emp)>500

<QUERY> = { FRCLAUSE || WHCLAUSE ||F} • FRCLAUSE <correlation name>in table name • • WHCLAUSE WHERE SEARCH COND • HCLAUSE HAVING SEARCH COND {d in DEPT: || WHERE SEARCH COND HAVING SEARCH COND ||F}

WHERE SEARCH COND • ALL QUANTIFIED PRED 1<SUBQ><COL or VAL><comp op> 2<SUBQ> 1<SUBQ> d. location = 2<SUBQ> • <SUBQ> = { FRCLAUSE: || WHCLAUSE> HCLAUSE>||F } 1<SUBQ> => {e in EMP: ||e. d# = d. d#||F } • <SUBQ> = <COL or VAL> 2<SUBQ> => e. residence

WHERE SEARCH COND {e in EMP: ||e. d# = d. d#||F } d. location = e. residence

HAVING SEARCH COND • • • <HAVING SEARCH COND> = „Boolean expression of“ <HPRED> = <HACOL PRED>|. . . <HACOL PRED> = <FUNC SPEC>{ <FRCLAUSE>: || <WHCLAUSE> <GBCLAUSE>||F} <comp op><COL or VAL> AVG(d. nofemp){ <FRCLAUSE>: || <WHCLAUSE> <GBCLAUSE>||F} > 500

HAVING SEARCH COND AVG(d. nofemp){ <FRCLAUSE>: || <WHCLAUSE> <GBCLAUSE>||F} > 500 AVG(d. nofemp){d in DEPT: : || {e in EMP: ||e. d# = d. d#||F } d. location = e. residence <GBCLAUSE>||F} > 500

<GBCLAUSE> • <GBCLAUSE> = <correlation name><column name> <correlation name> <column name> d. manager d manager

• HAVING SEARCH COND AVG(d. nofemp){d in DEPT: : || {e in EMP: ||e. d# = d. d#||F } d. location = e. residence <GBCLAUSE>||F} > 500 AVG(d. nofemp){d in DEPT: : || {e in EMP: ||e. d# = d. d#||F } d. location = e. residence d. manager d manager||F} > 500

Dokončení příkladu SQL: SELECT d. manager FROM dept d WHERE d. location = ALL SELECT e. residence FROM emp e WHERE e. dept. Id = d. id GROUP BY d. manager HAVING AVG(d. nof. Emp)>500 schéma: dept(id, nof. Emp, location, manager) emp(id, dept. Id, residence) E 3 VPC: {d dept: ||( {e emp: || e. dept. Id = d. id ||F} d. location = e. residence) AVG(d. nof. Emp){d dept: ||( {e emp: || e. dept. Id = d. id ||F} d. location = e. residence) d. manager||F}>500 ||F}

Ekvivalence SQL dotazů

Úkol • • Zjišťujeme, zda dané SQL výrazy jsou ekvivalentní. Uděláme to přes E 3 VPC SQL výrazy přeložíme di E 3 VPC a zjistíme ekvivalenci E 3 VPC výrazů. Jak to provedeme?

Kanonická forma E 3 VPC výrazu E 3 VPC výraz je kanonický, pokud ●interpretační operátor je aplikován na každou atomickou formuli ●interpretační operátor není aplikován na jinou než atomickou formuli ●neobsahuje zkrácené kvantifikované výrazy: {x S: P(x)} Q(x), {x S: P(x)} Q(x)

Pozorování • S kanonickým výrazem lze pracovat ve 2 VPC. • Ve 2 VPC umíme zjistit ekvivalenci dvou výrazů. • Má každý E 3 VPC výraz svůj kanonický tvar? • Ano – následuje lemma, jejichž násobnou aplikací dostaneme kanonickou formu výrazu

Lemma 2 1. 2. 3. || {x S: ||P(x)|| } Q(x)|| ( x S): ||P(x)|| ||Q(x)|| || {x S: ||P(x)|| } Q(x)|| ( x S): || P(x)|| ||Q(x)|| , kde P(x), Q(x) jsou formule, S relace, , {T, F}

Množina kritické ekvivalence <Q 1> = {x in R: ||P(x)||F } <Q 2> = {x in R: ||P(x)||T }

Jednoduché dotazy • Jednoduchý dotaz – bez negace a kvantifikace Pravidlo 3 <Q 1> = {x in R: ||P(x)||F } <Q 1> = {x in R: ¬||P(x)||F } {x in R: ||¬P(x)||T } Pravidlo 4 <Q 2> = {x in R: ||P(x)||F } <Q 2> = {x in R: || ||P(x)||T ||F } {x in R: ||P(x)||T }

Univerzální kvantifikátor 0)||¬ {y in S: ||Q(x, y) P(y)|| }|| 1)||¬ {y in S: ||Q(x, y)|| }P(y)|| 2)||¬ {y in S: ||P(y)|| }Q(x, y)|| 3)|| {y in S: ||Q(x, y)|| }¬P(y)|| 4)|| {y in S: ||P(y)|| }¬Q(x, y)||

Univerzální kvantifikátor II Kanonické formy: 0 y in S: ||¬ Q(x, y) || ||¬ P(y) || 1 y in S: ||¬ Q(x, y) || ||¬ P(y) || 2 y in S: ||¬ Q(x, y) || ||¬ P(y) || 3) 4) =2 =3

Příklad SELECT * FROM R x WHERE NOT EXISTS SELECT * FROM S y WHERE x. Ai = y. Bi y. Bj 'P' SELECT * FROM R x WHERE 'P' = ALL SELECT y. Bj FROM S y WHERE x. Ai = y. Bi R: 1 -(1) S: 1 -(1, NULL)

Existenční kvantifikátor 0)|| {y in S: ||Q(x, y) P(y)|| }|| 1)|| {y in S: ||Q(x, y)|| }P(y)|| 2)|| {y in S: ||P(y)|| }Q(x, y)|| 3)||¬ {y in S: ||Q(x, y)|| }¬P(y)|| 4)||¬ {y in S: ||P(y)|| }¬Q(x, y)||

Existenční kvantifikátor II Canonické formy: 0 y in S: || Q(x, y) || P(y) || 1 y in S: || Q(x, y) || P(y) || 2 y in S: || Q(x, y) || P(y) || 3) = 2 4) = 3 =F

Ekvivalence SQL dotazů SQL dotazy, které neobsahují univerzální kvantifikátor nikdy nevedou na množinu kritické ekvivalence • SQL dotazy obsahující univerzální kvantifikátor vždy venou na množinu kritické ekvivalence • Počet různých interpretací dotazů se stejnou kanonickou strukturou s alespoň jedným univerzálním kvantifikátorem se zvetšuje s každým dalším kvantifikátorem (univerzálním i existenčním) •

Závěr • Definovali jsme jazyk E 3 VPC. • Ukázali jsme si gramatiku SQL (zjednodušeně). • Víme, že lze sestrojit syntaxí řízený překladač SQL => E 3 VPC. • Viděli jsme překlad SQL dotazu. • Umíme rozhodnout, zda jsou dva SQL dotazy ekvivalentní.

Použitá literatura • Formal Semantics of SQL Queries - M. Negri et. al. in ACM Transactions on Database Dystems, Vol. 17, No. 3, Sept. 1991, p. 513– 534.