1 / 26

# WinRDBI Windows-based Relational DataBase Interpreter

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

## WinRDBI Windows-based Relational DataBase Interpreter

E N D

### Presentation Transcript

1. WinRDBI Windows-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 Understanding Relational Database Query Languages

2. User Interface • Multiple Query Panes: one query language is associated with each pane; result of queries displayed in the bottom subwindow of the query pane • One Schema Pane: displays the schema and instance of the currently opened relational database Understanding Relational Database Query Languages

3. ICONS Understanding Relational Database Query Languages

4. 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 Understanding Relational Database Query Languages

5. 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{ tqtr | tq  q and tr  r } Additional Operators r  sr - ( r - s ) p   q  (p × q) p  qP  Q(   (p × q) )where  = (p.ai=q.ai and … and p.aj=q.aj) P  Q = {ai, …, aj} p  qP - Q (p) - P - Q ((P - Q (p) × q) - p) Relational Algebra Syntax Summary Understanding Relational Database Query Languages

6. WinRDBI select condition (r) project ai, …, aj (r) r union s r difference s 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 Algebra WinRDBI Syntax Summary Understanding Relational Database Query Languages

7. Relational AlgebraWinRDBI Demo Illustrate the features of the relational algebra language by example using the employee training enterprise that can be found at http://www.prenhall.com/dietrich Understanding Relational Database Query Languages

8. Domain Relational CalculusSyntax Summary { D1, …, Dn | F(D1, …, Dn) } • F describes the properties of the data to be retrieved. • The output schema of F is given by the domain variables D1, …, Dn that act as global variables in F. • 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), F is true. Understanding Relational Database Query Languages

9. LetDi be a domain variablec be a domain constant be a comparison operator Atoms r(D1, D2, …, Dn) Di  Dj Di  c Let F, F1 and F2 be formulas Formulas ( F ) not F F1 and F2 F1 or F2Let D be free* in F(D) (exists D) F(D) (forall D) F(D)* a variable is free in a formula if it is not quantified by exists or forall Domain Relational CalculusAtoms & Formulas Understanding Relational Database Query Languages

10. Domain Relational CalculusValid Expression { D1, …, Dn | F(D1, …, Dn) } is a valid DRC expression if it has only the variables appearing to the left of the vertical bar | free in F.Any other variable appearing in F 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 Understanding Relational Database Query Languages

11. 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) } Understanding Relational Database Query Languages

12. Domain Relational CalculusWinRDBI Demo Illustrate the features of the DRC language by example using the employee training enterprise that can be found at http://www.prenhall.com/dietrich Understanding Relational Database Query Languages

13. Tuple Relational CalculusSyntax Summary { T1, …, Tn | F(T1, …, Tn) } • F describes the properties of the data to be retrieved. • The output schema of F is given by the tuple variables T1, …, Tn that act as global variables in F. Understanding Relational Database Query Languages

14. 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 F, F1 and F2 be formulas Formulas ( F ) not F F1 and F2 F1 or F2Let T be free* in F(T) (exists T) F(T) (forall T) F(T)* a variable is free in a formula if it is not quantified by exists or forall Tuple Relational CalculusAtoms & Formulas Understanding Relational Database Query Languages

15. Tuple Relational CalculusValid Expression { T1, …, Tn | F(T1, …, Tn) } is a valid TRC expression if it has only the variables appearing to the left of the vertical bar | free in F.Any other variable appearing in F 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 Understanding Relational Database Query Languages

16. 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) } Understanding Relational Database Query Languages

17. Tuple Relational CalculusWinRDBI Demo Illustrate the features of the TRC language by example using the employee training enterprise that can be found at http://www.prenhall.com/dietrich Understanding Relational Database Query Languages

18. SQLSimple Query Syntax select distinct a1,…,am from r1, r2, …, rn where condition is equivalent to a1,…,am ( condition (r1 × r2 × … × rn) ) Understanding Relational Database Query Languages

19. 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 SQLRelational Completeness Understanding Relational Database Query Languages

20. 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], … ] Understanding Relational Database Query Languages

21. 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) Understanding Relational Database Query Languages

22. SQLInsert Syntax Summary insert into TABLE-NAME [ (ATTRIBUTE-LIST)] SOURCE where SOURCE is one of: • values ( EXPLICIT-VALUES) • SELECT-STATEMENT Understanding Relational Database Query Languages

23. SQLUpdate & Delete Syntax Summary update TABLE-NAME set COLUMN-NAME = VALUE-EXPR, … [where UPDATE-CONDITION] delete from TABLE-NAME [where DELETE-CONDITION] Understanding Relational Database Query Languages

24. 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 ... Understanding Relational Database Query Languages

25. 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); SQLAggregation in Nested Queries Understanding Relational Database Query Languages

26. SQLWinRDBI Demo Illustrate the features of the SQL language by example using the employee training enterprise that can be found at http://www.prenhall.com/dietrich Understanding Relational Database Query Languages

More Related