1 / 28

Why Bother?

Teaching Relational Algebra and Relational Calculus: A Programming Approach Kirby McMaster – Weber State University Nicole Anderson - Weber State University Ashley Blake – Seabrook, Texas. Why Bother? Should we teach relational algebra (RA) and relational calculus (RC) in database courses?

billywright
Download Presentation

Why Bother?

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Teaching Relational Algebraand Relational Calculus: A Programming ApproachKirby McMaster – Weber State UniversityNicole Anderson - Weber State UniversityAshley Blake – Seabrook, Texas Teaching Relational Algebra & Relational Calculus

  2. Why Bother? • Should we teach relational algebra (RA) and relational calculus (RC) in database courses? • A 2001 survey of 106 database teachers (Robbert and Ricardo, 2003) reported: • 92% covered SQL • 70% included RA • 42% mentioned RC Teaching Relational Algebra & Relational Calculus

  3. Why Relational Algebra? • RA is the "operations on tables" part of the relational model. • RA provides a procedural way to query a database. • Knowledge of RA facilitates learning, using, and understanding SQL. • The DBMS query processor translates SQL code into RA operations. Teaching Relational Algebra & Relational Calculus

  4. Why Relational Calculus? • Codd's 1970 database paper suggested predicate calculus (the basis for RC) as a query language. • RC provides a non-procedural way to query a database. • RC is the foundation for Query-By-Example. • RC can be used to develop an intelligent front-end for a database. Teaching Relational Algebra & Relational Calculus

  5. How to Teach RA and RC? Mathematical approach • Write queries using mathematical notation, but unable to execute them. Programming approach • Write query code and watch it run. • RA function library • RC with Prolog Teaching Relational Algebra & Relational Calculus

  6. Teaching Relational Algebra Suppose an Inventory database consists of two tables, STOCK and STKTYPE: STOCKSTKTYPE SNo (PK) TType (PK) SType (FK) TName SName ROP QOH OSize OnOrder Teaching Relational Algebra & Relational Calculus

  7. Inventory Query List the stock number (SNo), stock name (SName), quantity-on-hand (QOH), reorder point (ROP), and order size (OSize) for all inventory items in which the quantity-on-hand is at or below the reorder point, and no order has been placed (OnOrder = 'N'). Teaching Relational Algebra & Relational Calculus

  8. Mathematical approach • A common textbook approach for writing RA queries uses a mathematical notation that includes both infix operators and functional operators. • Using this notation, the Inventory Query could be written as: πSNo,SName,QOH,ROP,OSize(σOnOrder=‘N’ (σQOH<=ROP(STOCK wvSType=TTypeSTKTYPE))) Teaching Relational Algebra & Relational Calculus

  9. This query expression is easier to understand when it is divided into a sequence of RA operations: TEMP1← STOCKwvSType=TTypeSTKTYPE TEMP2← σQOH<=ROP (TEMP1) TEMP3← σOnOrder='N‘ (TEMP2) TEMP4← πSNo,SName,QOH,ROP,OSize(TEMP3) • This format is clearer (but looks less like "Algebra"). Teaching Relational Algebra & Relational Calculus

  10. Programming approach • A alternative approach for writing a RA query is to make a sequence of function calls, with each function performing one RA operation. • We have developed a function library called RAlgProc. RA queries that use this library can be executed in the Visual FoxPro environment. (Very little knowledge of FoxPro is required.) Teaching Relational Algebra & Relational Calculus

  11. The RAlgProc library includes functions to perform the following RA operations: OperationFunction select TSelect(Table1,RowCond) project TProject(Table1,ColList) join TJoin(Table1,Table2,JoinCond) union TUnion(Table1,Table2) intersect TIntersect(Table1,Table2) difference TMinus(Table1,Table2) product TProduct(Table1,Table2) divide TDivide(Table1,Table2) rename TRename(Table1,ColName,NewName) Teaching Relational Algebra & Relational Calculus

  12. A sample RA program for the Inventory Query using the RAlgProc library is shown below. * Relational Algebra – Inventory Query set procedure to RALGPROC T1 = TJoin('STOCK','STKTYPE',[SType=TType]) T2 = TSelect(T1,[QOH<=ROP]) T3 = TSelect(T2,[OnOrder='N']) T4 = TProject(T3,[SNo,SName,QOH,ROP,OSize]) browse Sample output is shown on the following slides. Teaching Relational Algebra & Relational Calculus

  13. Sno Stype Sname Qoh Onorder Ttype Tname Rop Osize 101 B Prune Basket 65 N B Basket 60 90 105 B Pear Basket 48 N B Basket 60 90 107 B Peach Basket 21 Y B Basket 60 90 202 W Deluxe Tower 54 N W Tower 40 60 204 W Special Tower 29 N W Tower 40 60 301 T Mint Truffles 116 N T Truffles 90 120 303 T Almond Truffles 44 Y T Truffles 90 120 304 T Mocha Truffles 72 N T Truffles 90 120 306 T Mixed Truffles 93 N T Truffles 90 120 401 F Chocolate Fudge 145 N F Fudge 120 180 404 F Marble Fudge 103 N F Fudge 120 180 502 C Berry CheeseCake 73 N C CheeseCake 50 75 505 C Apple CheeseCake 46 N C CheeseCake 50 75 506 C Lemon CheeseCake 18 Y C CheeseCake 50 75 508 C Plain CheeseCake 65 N C CheeseCake 50 75 T1: JOIN Tables Teaching Relational Algebra & Relational Calculus

  14. Sno Stype Sname Qoh Onorder Ttype Tname Rop Osize 105 B Pear Basket 48 N B Basket 60 90 107 B Peach Basket 21 Y B Basket 60 90 204 W Special Tower 29 N W Tower 40 60 303 T Almond Truffles 44 Y T Truffles 90 120 304 T Mocha Truffles 72 N T Truffles 90 120 404 F Marble Fudge 103 N F Fudge 120 180 505 C Apple CheeseCake 46 N C CheeseCake 50 75 506 C Lemon CheeseCake 18 Y C CheeseCake 50 75 T2: SELECT Rows: QOH <= ROP Teaching Relational Algebra & Relational Calculus

  15. Sno Sno Stype Sname Sname Qoh Rop Qoh Osize Onorder Ttype Tname Rop Osize 105 105 B Pear Basket Pear Basket 48 60 48 90 N B Basket 60 90 204 204 W Special Tower Special Tower 29 40 29 60 N W Tower 40 60 304 304 T Mocha Truffles Mocha Truffles 72 90 72 120 N T Truffles 90 120 404 404 Marble Fudge F Marble Fudge 103 120 103 180 N F Fudge 120 180 505 505 Apple CheeseCake C Apple CheeseCake 46 50 46 75 N C CheeseCake 50 75 T3: SELECT Rows: OnOrder = 'N' T4: PROJECT Columns Teaching Relational Algebra & Relational Calculus

  16. Inventory Query 2 List the stock number, stock name, and quantity-on-hand of all Truffles products. A sample RA program for this query is: * Relational Algebra – Inventory Query 2 set procedure to RALGPROC T1 = TSelect('STKTYPE',[TName='Truffles']) T2 = TJoin('STOCK',T1,[SType=TType]) T3 = TProject(T2,[SNo,SName,QOH]) browse Note that the Join is performed after the Select. Teaching Relational Algebra & Relational Calculus

  17. Sno Ttype Sno Sname Stype Tname Sname Rop Qoh Osize Qoh Onorder Ttype Tname Rop Osize 301 301 T Mint Truffles T Truffles Mint Truffles 90 116 120 116 N T Truffles 90 120 303 303 Almond Truffles T Almond Truffles 44 44 Y T Truffles 90 120 304 304 Mocha Truffles T Mocha Truffles 72 72 N T Truffles 90 120 306 306 Mixed Truffles T Mixed Truffles 93 93 N T Truffles 90 120 T1: Select T2: Join T3: Project Teaching Relational Algebra & Relational Calculus

  18. Teaching Relational Calculus • Relational Calculus is based on a branch of logic called Predicate Logic. • In programming, a predicate is a function in which the return value is either true or false. • RC uses predicates to specify the data in tables and the result sets for queries. • When RC is implemented as a Prolog program: • Table predicates are defined by facts. • Query predicates are defined by rules. Teaching Relational Algebra & Relational Calculus

  19. A table predicate is true for each set of facts that appear as a row in the database. • A query predicate is true whenever the logical expression for the rule is true. • The Prolog query processor searches through the table predicates to determine which row and column values cause a query predicate to be true. • Query output consists of all parameter values for which a query predicate is true. Teaching Relational Algebra & Relational Calculus

  20. Because the Prolog query engine performs the searching and pattern matching, a query program is non-procedural. • Students write RC queries by defining predicates--one for each table using facts, and one for each query using rules. Teaching Relational Algebra & Relational Calculus

  21. Inventory Database in Prolog /* Inventory Database Prolog implementation (Predicate Calculus) */ domains SNo, QOH, ROP, OSize = integer SType, SName, OnOrder, TType, TName = symbol predicates /* One predicate for each table */ STKTYPE(TType, TName, ROP, OSize) STOCK(SNo, SType, SName, QOH, OnOrder) clauses /* Facts - one for each table row */ STKTYPE("B", "Basket", 60, 90). STKTYPE("C", "CheeseCake", 50, 75). STKTYPE("F", "Fudge", 120, 180). STKTYPE("T", "Truffles", 90, 120). STKTYPE("W", "Tower", 40, 60). Teaching Relational Algebra & Relational Calculus

  22. STOCK(101, "B", "Prune Basket", 65, "N"). STOCK(105, "B", "Pear Basket", 48, "N"). STOCK(107, "B", "Peach Basket", 21, "Y"). STOCK(202, "W", "Deluxe Tower", 54, "N"). STOCK(204, "W", "Special Tower", 29, "N"). STOCK(301, "T", "Mint Truffles", 116, "N"). STOCK(303, "T", "Almond Truffles", 44, "Y"). STOCK(304, "T", "Mocha Truffles", 72, "N"). STOCK(306, "T", "Mixed Truffles", 93, "N"). STOCK(401, "F", "Chocolate Fudge", 145, "N"). STOCK(404, "F", "Marble Fudge", 103, "N"). STOCK(502, "C", "Berry CheeseCake", 73, "N"). STOCK(505, "C", "Apple CheeseCake", 46, "N"). STOCK(506, "C", "Lemon CheeseCake", 18, "Y"). STOCK(508, "C", "Plain CheeseCake", 65, "N"). Teaching Relational Algebra & Relational Calculus

  23. Inventory Query 1 List the stock number (SNo), stock name (SName), quantity-on-hand (QOH), reorder point (ROP), and order size (OSize) for all inventory items in which the quantity-on-hand is at or below the reorder point, and no order has been placed (OnOrder = 'N'). Inventory Query 2 List the stock number, stock name, and quantity-on-hand of all Truffles products. Teaching Relational Algebra & Relational Calculus

  24. Prolog (RC) Query Program /* Inventory Queries */ include "STKDATA.PRO" /* Contains Database */ predicates QUERY1(SNo, SName, QOH, ROP, OSize) QUERY2(SNo, SName, QOH) clauses /* Rules */ QUERY1(SNo, SName, QOH, ROP, OSize) if STOCK(SNo, SType, SName, QOH, OnOrder) and STKTYPE(TType, TName, ROP, OSize) and SType = TType and QOH <= ROP and OnOrder = "N". QUERY2(SNo, SName, QOH) if STOCK(SNo, SType, SName, QOH, OnOrder) and STKTYPE(TType, TName, ROP, OSize) and SType = TType and TName = "Truffles". Teaching Relational Algebra & Relational Calculus

  25. Goal: QUERY1(SNo,SName,QOH,ROP,OSize). SNo=105, SName=Pear Basket, QOH=48, ROP=60, OSize=90 SNo=204, SName=Special Tower, QOH=29, ROP=40, OSize=60 SNo=304, SName=Mocha Truffles, QOH=72, ROP=90, OSize=120 SNo=404, SName=Marble Fudge, QOH=103, ROP=120, OSize=180 SNo=505, SName=Apple CheeseCake, QOH=46, ROP=50, OSize=75 5 Solutions Query 1 Results Teaching Relational Algebra & Relational Calculus

  26. Query 2 Results Goal: QUERY2(SNo,SName,QOH). SNo=301, SName=Mint Truffles, QOH=116 SNo=303, SName=Almond Truffles, QOH=44 SNo=304, SName=Mocha Truffles, QOH=72 SNo=306, SName=Mixed Truffles, QOH=93 4 Solutions Teaching Relational Algebra & Relational Calculus

  27. Summary and Conclusions • This paper has described a programming approach for teaching Relational Algebra and Relational Calculus in database courses. • Instead of using mathematical notation for RA operations, we have created a FoxPro function library to perform RA queries. • We have shown how to use Prolog to express RC queries in a non-procedural form. • Sample query programs have been demonstrated in both the FoxPro and Prolog environments. Teaching Relational Algebra & Relational Calculus

  28. Programming can provide a valuable learning tool throughout the database course, not just for SQL. • Using this approach, students can learn RA and RC the same way they learn other computing concepts--by writing programs and watching them run. Teaching Relational Algebra & Relational Calculus

More Related