logging in or signing up WinRDBI Donato Download Post to : URL : Related Presentations : Share Add to Flag Embed Email Send to Blogs and Networks Add to Channel Uploaded from authorPOINTLite Insert YouTube videos in PowerPont slides with aS Desktop Copy embed code: (To copy code, click on the text box) Embed: URL: Thumbnail: WordPress Embed Customize Embed The presentation is successfully added In Your Favorites. Views: 597 Category: Education License: All Rights Reserved Like it (0) Dislike it (0) Added: January 16, 2008 This Presentation is Public Favorites: 0 Presentation Description No description available. Comments Posting comment... Premium member Presentation Transcript 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) SQLWinRDBI Online Demonstrations: WinRDBI Online Demonstrations Look for online demonstrations of the software on the WinRDBI web: http://www.eas.asu.edu/~winrdbi Getting Started with WinRDBI Creating a Database in WinRDBI Additional demonstrations will be added over time. User Interface: 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 databaseICONS: ICONSSyntax Conventions: 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 letterRelational Algebra Syntax Summary: 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 { tqtr | tq q and tr r } Additional Operators r s r - ( 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 WinRDBI Syntax Summary: Relational Algebra WinRDBI Syntax Summary 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 Domain Relational CalculusSyntax Summary: Domain Relational Calculus Syntax 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. Domain Relational CalculusAtoms & Formulas: Domain Relational Calculus Atoms & Formulas Let Di be a domain variable c 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 F2 Let 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 forallDomain Relational CalculusValid Expression: Domain Relational Calculus Valid 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 Domain Relational CalculusRelational Completeness: Domain Relational Calculus Relational 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) } Tuple Relational CalculusSyntax Summary: Tuple Relational Calculus Syntax 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.Tuple Relational CalculusAtoms & Formulas: Tuple Relational Calculus Atoms & Formulas Let T and Ti be tuple variables aj be an attribute c 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 F2 Let 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 forallTuple Relational CalculusValid Expression: Tuple Relational Calculus Valid 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 Tuple Relational CalculusRelational Completeness: Tuple Relational Calculus Relational 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) }SQLSimple Query Syntax: SQL Simple Query Syntax select distinct a1,…,am from r1, r2, …, rn where condition is equivalent to a1,…,am ( condition (r1 × r2 × … × rn) ) SQLRelational Completeness: SQL Relational 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, rSQLQuery Syntax Summary: SQL Query 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: SQL Data 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: SQL Insert Syntax Summary insert into TABLE-NAME [ (ATTRIBUTE-LIST)] SOURCE where SOURCE is one of: values ( EXPLICIT-VALUES) SELECT-STATEMENT SQLUpdate & Delete Syntax Summary: SQL Update & Delete Syntax Summary update TABLE-NAME set COLUMN-NAME = VALUE-EXPR, … [where UPDATE-CONDITION] delete from TABLE-NAME [where DELETE-CONDITION] SQLWinRDBI Syntax Summary: SQL WinRDBI 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 Aggregation 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); You do not have the permission to view this presentation. In order to view it, please contact the author of the presentation.
WinRDBI Donato Download Post to : URL : Related Presentations : Share Add to Flag Embed Email Send to Blogs and Networks Add to Channel Uploaded from authorPOINTLite Insert YouTube videos in PowerPont slides with aS Desktop Copy embed code: (To copy code, click on the text box) Embed: URL: Thumbnail: WordPress Embed Customize Embed The presentation is successfully added In Your Favorites. Views: 597 Category: Education License: All Rights Reserved Like it (0) Dislike it (0) Added: January 16, 2008 This Presentation is Public Favorites: 0 Presentation Description No description available. Comments Posting comment... Premium member Presentation Transcript 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) SQLWinRDBI Online Demonstrations: WinRDBI Online Demonstrations Look for online demonstrations of the software on the WinRDBI web: http://www.eas.asu.edu/~winrdbi Getting Started with WinRDBI Creating a Database in WinRDBI Additional demonstrations will be added over time. User Interface: 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 databaseICONS: ICONSSyntax Conventions: 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 letterRelational Algebra Syntax Summary: 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 { tqtr | tq q and tr r } Additional Operators r s r - ( 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 WinRDBI Syntax Summary: Relational Algebra WinRDBI Syntax Summary 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 Domain Relational CalculusSyntax Summary: Domain Relational Calculus Syntax 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. Domain Relational CalculusAtoms & Formulas: Domain Relational Calculus Atoms & Formulas Let Di be a domain variable c 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 F2 Let 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 forallDomain Relational CalculusValid Expression: Domain Relational Calculus Valid 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 Domain Relational CalculusRelational Completeness: Domain Relational Calculus Relational 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) } Tuple Relational CalculusSyntax Summary: Tuple Relational Calculus Syntax 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.Tuple Relational CalculusAtoms & Formulas: Tuple Relational Calculus Atoms & Formulas Let T and Ti be tuple variables aj be an attribute c 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 F2 Let 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 forallTuple Relational CalculusValid Expression: Tuple Relational Calculus Valid 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 Tuple Relational CalculusRelational Completeness: Tuple Relational Calculus Relational 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) }SQLSimple Query Syntax: SQL Simple Query Syntax select distinct a1,…,am from r1, r2, …, rn where condition is equivalent to a1,…,am ( condition (r1 × r2 × … × rn) ) SQLRelational Completeness: SQL Relational 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, rSQLQuery Syntax Summary: SQL Query 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: SQL Data 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: SQL Insert Syntax Summary insert into TABLE-NAME [ (ATTRIBUTE-LIST)] SOURCE where SOURCE is one of: values ( EXPLICIT-VALUES) SELECT-STATEMENT SQLUpdate & Delete Syntax Summary: SQL Update & Delete Syntax Summary update TABLE-NAME set COLUMN-NAME = VALUE-EXPR, … [where UPDATE-CONDITION] delete from TABLE-NAME [where DELETE-CONDITION] SQLWinRDBI Syntax Summary: SQL WinRDBI 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 Aggregation 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);