O que é WinRDBI - IME-USPjef/winRDBI.pdf · Does not support SQL-standard view definition: assumes intermediate table syntax across all query languages Language simplification disallows

Understanding Relational Database Query Languages

O que WinRDBI

O WinRDBI (Windows Relational DataBase Interpreter) uma ferramenta educacional utilizada pela Universidade do Estado do Arizona, e que fornece uma abordagem ativa para entender as capacidades das seguintes linguagens de consulta para bancos de dados relacionais:

lgebra Relacional Clculo Relacional de Domnio Clculo Relacional de Tupla SQL

O WinRDBI foi fundamentado na tecnologia de banco de dados dedutivos que utiliza a linguagem lgica Amzi! Prolog and LogicServerpara consultar instncias do banco de dados armazenadas como fatos lgicos e utiliza a API Swing Java para estabelecer a GUI. O WinRDBI pode ser executado tanto em Windows quanto em Linux.


Onde obter WinRDBI?

O WinRDBI pode ser obtido no endereo: http://www.eas.asu.edu/~winrdbi/download.html. Ele totalmente livre e s se exige o seu registro.


Viso geral do WinRDBI

Vrios recursos esto disponveis para aprender o WinRDBI. Para comear, existem duas demonstraes em Flash que exibem o bsico do que o WinRDBI pode fazer. Para assitir, clique nos links abaixo:

Introduo ao WinRDBI(approx 6 mins)

Criando um banco de dados com WinRDBI


WinRDBIWindows-based Relational DataBase Interpreter

http://www.eas.asu.edu/~winrdbi

An educational tool that provides an interactive approach to learning relational database query languages.

Relational algebra

Domain Relational Calculus (DRC)

Tuple Relational Calculus (TRC)

SQL


User Interface

Multiple Query Panes: one query language is associated with each pane; result of queries displayed in the bottom subwindowof the query pane

One Schema Pane: displays the schema and instance of the currently opened relational database


ICONS

new database paste

new query new table

open delete table

save insert tuple

save all delete tuple

print find

cut replace

copy execute


Syntax Conventions

Since the heart of WinRDBI is written in Prolog (with Java used for the graphical user interface), the following Prolog conventions are assumed:

constants:numeric constants and single-quoted strings

relation and attribute names:identifiers starting with a lowercase letter

variable names:identifiers starting with an uppercase letter


Relational Algebra Syntax Summary

Fundamental Operators (r)

{ t | t r and } ai,,aj(r)

{ t.ai, , t.aj | t r } r s

{ t | t r or t s } r - s

{ t | t r and t s} q r

{ t qtr | tq q and tr r }

Additional Operators r s

r - ( r - s )

p q (p q)

p qP Q( (p q) )where = (p.ai=q.ai and and p.aj=q.aj) P Q = {ai, , aj}

p qP - Q (p) -P - Q ((P - Q (p) q) - p)


Relational Algebra WinRDBI Syntax SummaryWinRDBI

selectcondition (r) project ai, , aj (r) r union s r differences q product r r intersect s p njoin q: WinRDBI does not provide

division and -join operators to encourage the use of the fundamental relational algebra operators.

Formal Relational Algebra condition(r) ai,,aj(r) r s r - s

q r

r s p q


Relational AlgebraWinRDBI Demo

Illustrate the features of the relational algebralanguage by example using the employee training enterprise that can be found at

http://www.prenhall.com/dietrich


Domain Relational CalculusSyntax Summary

{ D 1, , Dn | FF (D1, , Dn) }

F F describes the properties of the data to be retrieved.

The output schema of FF is given by the domain variables

D1, , Dn that act as global variables in FF.

The result of the DRC expression gives the set of all tuples

(d1, d2, , dn) such that when di is substituted for Di

(1 =< i =< n), FF is true.


Domain Relational CalculusAtoms & Formulas

LetDi be a domain variablec be a domain constant be a comparison operator

Atoms r(D1, D2, , Dn)

Di Dj

Di c

Let FF, F1F1 and F2F2 be formulasFormulas

( FF ) not FF F1F1 and F2F2 F1F1 or F2F2

Let D be free* in FF(D) (exists D) FF(D) (forall D) FF(D)

* a variable is free in a formula if it is not quantified by exists or forall


Domain Relational CalculusValid Expression

{ D 1, , Dn | FF (D1, , Dn) }is a valid DRC expression if it has only the variables appearing to the left of the vertical bar | free in FF.Any other variable appearing in FF must be bound.

free vs. bound variables

free (global): variable is not explicitly quantified

bound (free): variable is declared explicitly through quantification and its scope is the quantified formula


Domain Relational CalculusRelational Completeness

condition (r):{ R1, , Rn | r(R1, , Rn) and condition}

ai,,aj(r):

{ Ri, , Rj | r(R1, , Ri, , Rj, , Rn)}

r s:

{ D1, , Dn | r(D1, , Dn) or s(D1, , Dn) }

r - s:

{ D1, , Dn | r(D1, , Dn) and not s(D1, , Dn) }

q r :

{ Q1, , Qm, R1, , Rn | q(Q1, , Qm) and r(R1, , Rn) }


Domain Relational CalculusWinRDBI Demo

Illustrate the features of the DRC language by example using the employee training enterprise that can be found at



Tuple Relational CalculusSyntax Summary

{ T 1, , Tn | FF (T1, , Tn) }

F F describes the properties of the data to be retrieved.

The output schema of FF is given by the tuple variables

T1, , Tn that act as global variables in FF.


Tuple Relational CalculusAtoms & Formulas

LetT and Ti be tuple variablesaj be an attributec be a domain constant be a comparison operator

Atoms r(T)

Ti.am Tj.an

T.ai c

Let FF, F1F1 and F2F2 be formulasFormulas

( FF ) not FF F1F1 and F2F2 F1F1 or F2F2

Let T be free* in FF(T) (exists T) FF(T) (forall T) FF(T)

* a variable is free in a formula if it is not quantified by exists or forall


Tuple Relational CalculusValid Expression

{ T 1, , Tn | FF (T1, , Tn) }is a valid TRC expression if it has only the variables appearing to the left of the vertical bar | free in FF.Any other variable appearing in FF must be bound.

free vs. bound variables

free (global): variable is not explicitly quantified

bound (free): variable is declared explicitly through quantification and its scope is the quantified formula


Tuple Relational CalculusRelational Completeness

condition (r):{ R| r(R) and condition}

ai,aj(r):

{ R.ai, , R.aj | r(R)}

r s:

{ T | r(T) or s(T) }

r - s:

{ T | r(T) and not s(T) }

q r :

{ Q, R | q(Q) and r(R) }


Tuple Relational CalculusWinRDBI Demo

Illustrate the features of the TRC language by example using the employee training enterprise that can be found at



SQLSimple Query Syntax

select distinct a1,,am

from r1, r2, , rn

where condition

is equivalent to

a1,,am ( condition (r1 r2 rn) )


SQLRelational Completeness

condition(r)

A (r)

r s

r - s

q r

select * from r where condition

select distinct A from r

select * from r union select * from s

select * from r except select * from s

select * from q, r


SQLQuery Syntax Summary

select [distinct] ATTRIBUTE-LIST

from TABLE-LIST

[where WHERE-CONDITION]

[group by GROUPING-ATTRIBUTES

[having HAVING-CONDITION]]

[order by COLUMN-NAME [asc | desc], ]


SQLData Definition Syntax Summary

create table TABLE-NAME(COL-NAME COL-TYPE [ATTR-CONSTRAINT],[TABLE-CONSTRAINT-LIST])where

ATTR-CONSTRAINT: not null or default value

TABLE-CONSTRAINT-LIST: primary key, uniqueness and referential integrity(foreign key)


SQLInsert Syntax Summary

insert into TABLE-NAME [ (ATTRIBUTE-LIST)]

SOURCE

where SOURCE is one of:

values ( EXPLICIT-VALUES)

SELECT-STATEMENT


SQLUpdate & Delete Syntax Summary

update TABLE-NAME

set COLUMN-NAME = VALUE-EXPR,

[where UPDATE-CONDITION]

delete from TABLE-NAME

[where DELETE-CONDITION]


SQLWinRDBI Syntax Summary

Since WinRDBI has an integrated GUI for defining and

manipulating the database, WinRDBI SQL supports only

the query language.

SQL-89 compatibility: no joined tables in the from clause

Does not support SQL-standard view definition: assumes

intermediate table syntax across all query languages

Language simplification disallows aggregation in a nested

subquery: use two queries instead ...


SQLAggregation in Nested Queries

SQL

select E.eID, E.eLast, E.eFirst, E.eTitle

from employee E

where E.eSalary =

(select min(S.eSalary)

from employee S );

WinRDBI

minimumSalary(minSalary) :=

select min(E.eSalary)

from employee E;

select E.eID, E.eLast, E.eFirst, E.eTitle

from employee E

where E.eSalary =

(select minSalary

from minimumSalary);


SQLWinRDBI Demo

Illustrate the features of the SQL language by example using the employee training enterprise that can be found at


Documents

O que é WinRDBI - IME-USPjef/winRDBI.pdf · Does not support SQL-standard view definition: assumes intermediate table syntax across all query languages Language simplification disallows