A Relational Algebra Query Language For Programming .
Information Systems Education Journal (ISEDJ)9 (5)October 2011A Relational Algebra Query LanguageFor Programming Relational DatabasesKirby McMasterkmcmaster@weber.eduCS Dept., Weber State UniversityOgden, Utah 84408 USASamuel Sambasivamssambasivam@apu.eduCS Dept., Azusa Pacific UniversityAzusa, California 91702 USANicole Andersonnanderson@winona.eduCS Dept., Winona State UniversityWinona, Minnesota 55987 USAAbstractIn this paper, we describe a Relational Algebra Query Language (RAQL) and Relational Algebra Query(RAQ) software product we have developed that allows database instructors to teach relational algebrathrough programming. Instead of defining query operations using mathematical notation (theapproach commonly taken in database textbooks), students write RAQL query programs as sequencesof relational algebra function calls. The RAQ software allows RAQL programs to be run interactively, sothat students can view the results of RA operations. Thus, students can learn relational algebra in amanner similar to learning SQL—by writing code and watching it run.Keywords: database, query, relational algebra, programming, SQL1. INTRODUCTIONMost commercial database systems are basedon the relational data model. Recent editions ofdatabase textbooks focus primarily on therelational model. In this dual context, therelational model for data should be consideredthe most important concept in an introductorydatabase course.The heart of the relational model is a set ofobjects called relations or tables, plus a set ofoperations on these objects (Codd, 1972).Coverage of the relational model in databasecourses includes the structure of tables,integrity constraints, links between tables, anddata manipulation operations (data entry andqueries).Classroom discussion of queries and querylanguages generally leads to a detailedpresentation of SQL. Relational algebra (RA) asa query language receives less attention. In asurvey of database educators, Robbert and 2011 EDSIG (Education Special Interest Group of the AITP)www.aitp-edsig.org /www.isedj.orgPage 18
Information Systems Education Journal (ISEDJ)Ricardo (2003) found that only 70% includedRA in their courses, compared to 92% for SQL.Database textbooks provide substantially morematerial on SQL than on RA. An extreme case isthe textbook by Hoffer, et al (2008), whichprovides two full chapters on SQL but does notmention RA.Why Teach Relational Algebra?There is almost universal agreement that SQL isan essential component of an introductorydatabase course. But should we also teachrelational algebra? There are several goodreasons for doing so.1.The main reason for teaching RA is tohelp students better understand the relationalmodel. At the conceptual level, the relationalmodel provides a flexible, adaptable way toquery a database. The organization of data intotables, together with RA operations, providesthe foundation for this flexibility.Relational algebra is a query language, not adatabasedesigntool.However,anunderstanding of how RA operations can beperformed on tables to extract informationshould help support database analysis anddesign decisions.2.Knowledge of RA facilitates teachingand learning SQL as a query language. Thebasic syntax of the SQL SELECT statementprovides an integrated framework for combiningRA operations to express a query.3.An understanding of RA can also beused to improve query performance. The queryprocessing component of a DBMS enginetranslates SQL code into a query plan thatincludes RA operations. The DBMS queryoptimizer,togetherwiththedatabaseadminister, can speed up query execution byreducing the processing time of the RAoperations.How to Teach Relational Algebra?If an instructor decides to include relationalalgebra as a topic in a database course, afollow-up question is how to present this topicto students? RA coverage in leading databasetextbooksoftentakesamathematicalapproach. For example, the texts by Connollyand Begg (2009), Elmasri and Navathe (2006),and Silberschatz, et al (2006) present RAconcepts using mathematical notation. Thereare several problems with this form ofrepresentation.9 (5)October 2011Many database students are not comfortablewith mathematical notation, such as the use ofGreek letters (e.g. V and S) in a new context.The mathematical approach often mixes infixnotation (operator name is placed between twooperands; e.g. table1 union table2) andfunctional notation (operator name is placedbefore the operands; e.g. project table3 cols)when performing multiple RA operations withina single expression. This makes the expressionsdifficult to interpret, and it disguises theprocedural nature of RA.More importantly, students cannot executequery programs written in the mathematicalnotation. There is no easy way to verify that themathematical description of a query is correct.The mathematical approach contrasts with howprogramming courses are taught. In aprogramming course, an important part oflearning occurs when students write instructionsfor the computer and watch their code run.Errors in program execution provide feedback,which reduces the gap between a student'sperception of the problem and how thecomputer interprets the proposed solution.To demonstrate how computer implementationsdiffer from mathematical models, students needsoftware to experiment with. Students learnmathematical and computational concepts moreeffectively when they can work with actualcomputer representations. As with otherprogramming languages, this principle applieswhen we teach students how to query usingrelational algebra.All major relational database products offer SQLas the primary query language. On the otherhand, very few computer environments areavailable for developing and running RAprograms. One database system to offer RA asa query language is LEAP (Leyton, 2010). TheRel DBMS (Voorhis, 2010) uses a form of RAcalled Tutorial D (Date and Darwen, 2007). Athird choice is WinRDBI (Dietrich, 2001), whichsupports queries using RA and other querylanguages.Each of the above systems enables RA querieswithin a specific database system. None allowyou to use RA to query desktop databases. Inthis paper, we introduce a Relational AlgebraQuery Language (RAQL) and a customRelational Algebra Query (RAQ) softwareproduct that can be used to query relationaldatabases. 2011 EDSIG (Education Special Interest Group of the AITP)www.aitp-edsig.org /www.isedj.orgPage 19
Information Systems Education Journal (ISEDJ)We first present a function-based syntax forwriting RAQL query programs as sequences ofRA operations. We outline the main features ofthe RAQ software. Next, we demonstrate howto use the software to execute RAQL programs.Finally, we give examples of RA concepts thatcan be taught using this approach.2. RELATIONAL ALGEBRA PROGRAMMINGA RAQL query program consists of a set ofstatements that specify operations to performon database tables. The statements areexecuted in a particular sequence to yield aresult table that satisfies a query. Eachstatement might consist of a single relationalalgebra operation, or several operations can becombined into one "algebraic" expression.Rather than use complex expressions in queryprograms, we prefer to have each line of codeperform a single RA operation. Our coding stylereflects 2GL (assembly language) thinking morethan 3GL thinking (e.g. Fortran, C).We provide a library function for each RAoperation. A RAQL program is written as asequence of RA function calls. Each functionhas one or two input parameters that aretables, plus other input parameters asnecessary.The output of each function isanother table. Using functions to implement RAoperations provides database students with acomfortable programming environment forcreating RAQL query programs.Functions are provided forrelational algebra operations:thefollowingTable 1: Relational Algebra ename(Table1,OldColumnName,NewColumnName)To illustrate programming using RA functions,we require a sample database. The structure of9 (5)October 2011a simple inventory database is described in thenext section.Relational Database ExampleSuppose an INVENTORY database for amanufacturing environment consists of twotables, STOCK and STKTYPE. The diagram inFigure 1 describes the relational model for thisdatabase.This data model assumes that inventory itemsare divided into categories, or types. Attributesthat apply to individual items are recorded inthe STOCK table. Attributes that apply to allitems of the same type are included in theSTKTYPE table. The two tables are linked by acommon type code (SType and TType).Figure 1: Inventory DatabasePrimary keys are shown in boldIn this basic system, when the quantity-onhand for an item drops below its reorder point,a production run of a predetermined lot-size isscheduled on a specific production line. It isassumed that reorder point, lot-size, andproduction line depend on the stock type ratherthan on the individual item. Whenever aproduction run is scheduled, the OnOrder fieldfor the item is set to 'Y'. This field is reset to 'N'after the order is filled.RA Query1 ProgramConsider the following query for the INVENTORYdatabase.Query1: List the stock number, name, andquantity-on-hand for all items that aremanufactured on production line 3.A RAQL program for this query takes the formof a sequence of Table 1 function calls. Eachfunction receives one or two tables asarguments and returns a temporary table. Thetemporary table can be used in later RA 2011 EDSIG (Education Special Interest Group of the AITP)www.aitp-edsig.org /www.isedj.orgPage 20
Information Systems Education Journal (ISEDJ)9 (5)October 2011operations. Sample code for this query is shownbelow:without exiting and then rerunning the RAQsoftware.-- RA Query1: Inventory Query – Line 3T1 TJoin('STOCK', 'STKTYPE', "SType TType")T2 TSelect(T1, "ProdLine 3")T3 TProject(T2, "StkNo,StkName,QtyOnHand")2.Query Program textbox: Choose a RAQLquery program. The program must be in a textfile having a TXT extension. A new queryprogram can be selected at any time during theexecution of the RAQ software, but the actionsthat follow must be repeated.An explanation of each line of code for thisprogram follows:Line 1: This is a comment (--)Line 2: The STOCK and STKTYPE tables arejoined. The join condition states that the STypefield in the STOCK table must match the TTypefield in the STKTYPE table. Actual table namesare placed in matching single (or double)quotes, since they are fixed string values. Thejoin condition is also placed in quotes. Theoutput of the TJoin function is a cursor (atemporary table in memory). The cursor nameis randomly generated and is assigned tovariable T1. The name of the cursor is unknownto the programmer, but the cursor can bereferred to in later program statements usingthe variable name.3.Display button: Display the RAQLprogram code in a window. This command canbe selected whenever the Display button isenabled. Press the Escape key to close thewindow. The display window is read-only. Anychanges or corrections to the RAQL programmust be made in a separate text editor.Line 3: Rows of cursor T1 are then selected ifthey satisfy the condition that the ProdLine(production line) field in T1 equals 3. Quotesare not needed for the number 3. If quotes areneeded inside a row condition, then single anddouble quotes should be nested in pairs (e.g."OnOrder 'N'"). The output cursor name isassigned to variable T2. The T1 argument is notplaced in quotes, since T1 is a variable.Line 4: The three columns of cursor T2 specifiedin the column list are projected as cursor T3,which is the final result table for the query.3. RAQ COMPUTER SOFTWAREThe RAQ software allows us to execute querieswritten in the format of Query1. Ourexplanation of how to use RAQ to performqueries is organized according to the controls(textboxes and command buttons) on the RAQmain screen (see Figure 2).1.Database File textbox: Choose adatabase file. The database must be in aMicrosoft Access MDB file. The file can beselected with the file-chooser dialog box, whichincludes the ability to search in subdirectories.No other actions can be performed in the RAQsoftware until a valid database file is opened.Once a database is open, it cannot be changedFigure 2: RAQ Program Main Screen4.Load button: Before a RAQL programcan be run, it must be loaded. This action canbe repeated when you want to restart theprogram from the beginning.5.Step button: Each click of this buttonexecutes one RAQL instruction. This willnormally be a single relational algebraoperation. Comments in the program code areskipped. For each successful RA operation, theresulting query output cursor is shown on thescreen. Hit the escape key to close this view.If an error occurs while trying to execute aninstruction, an error message is displayed in thetop right-hand corner of the screen. The errormessage shows the code for the line that was 2011 EDSIG (Education Special Interest Group of the AITP)www.aitp-edsig.org /www.isedj.orgPage 21
Information Systems Education Journal (ISEDJ)9 (5)October 2011just attempted. Clicking the Display buttonallows the user to see the error in the contextof the full RAQL program.6.Save button: When an operation hascompleted, the current output cursor can besaved to disk. The format of the saved file is anExcel XLS file. The name of the output file is thename of the RAQL program file, followed by thestep number. The contents of the XLS file canbe easily transferred to a word processingdocument or other data file.7.Exit button: Click this button to exit theprogram. You will be prompted to confirm thisrequest before the program ends.The availability of most RAQ menu choicesdepends on which actions have alreadyoccurred during program execution. Textboxesand command buttons are disabled when theirselection would be inappropriate. For example,a result table cannot be saved if the currentcommand fails to execute correctly.Running the RA Query1 ProgramThe previous discussion of RAQ controls andfeatures was fairly general. To provide a moreconcrete demonstration, we list below onepossible sequence of RAQ actions that could betaken to execute the Query1 program.1. Load the INVENTORY database file. Sampledata for this database is listed in Appendix A.2.Load the text file that contains Query1.Assume this file is named RAQuery1.txt.3.Click the Display button to view thequery program code (optional).4.Click the Load button to initialize theprogram.5.Click the Step button. The comment linewill be skipped, and the TJoin operation will beperformed. The joined cursor T1 will appear in awindow.6.Click the Step button again, and theTSelect operation will produce cursor T2.7.Click Step a third time, and TProject willproduce and display cursor T3. The final resultfor Query1 is shown in Figure 3.8.To save a result cursor, click the Savebutton after closing the window showing thecursor. If the final result in this example issaved, the output file name will be RAQuery13.xls.Figure 3: RA Query1 Final Result9.Click Exit when you are finished; thenconfirm when prompted. If you prefer, you canLoad and rerun the same RAQL program, orchoose a new query program.Appendix B contains a sample RelationalAlgebra Project to give students experiencewriting and running RAQL programs.4. USING RAQ TO TEACH RELATIONALALGEBRA CONCEPTSThe RAQ software can be used to teachrelational algebra concepts interactively thatare usually explained intuitively. The advantageof using RAQ is that students can visualize theconcepts when they are implemented as RAQLprograms. Some examples of RA concepts thatcan benefit from this approach are describednext.Select Before JoinWhen select is used before join, the size of thejoined table will usually be much smaller than ifthe join operation is performed first. This willreduce the memory resources required for aquery and should decrease processing time. TheQuery2 RAQL program shown below achievesthe Query1 result, but starts with a selectoperation.-- RA Query2: Select before JoinT1 TSelect('STKTYPE', "ProdLine 3")T2 TJoin('STOCK', T1, "SType TType")T3 TProject(T2, "StkNo,StkName,QtyOnHand")This program can be compared to the Query1program, where the join operation is performedfirst. The relative size of the two joined cursors(T1 in Query1 and T2 in Query2) highlights theadvantage of joining tables "later."Set Union and IntersectionThe union of sets A and B consists of all distinctmembers of A and B. In RA, the union of twotables does not include duplicate rows. This 2011 EDSIG (Education Special Interest Group of the AITP)www.aitp-edsig.org /www.isedj.orgPage 22
Information Systems Education Journal (ISEDJ)concept can be illustrated with the followingQuery3 program.-- RA Query3: Union and IntersectionTA TSelect('STOCK', "QtyOnHand 50")TB TSelect('STOCK', "SType 'C'")T1 TUnion(TA, TB)T2 TIntersect(TA, TB)In this program, cursors TA and TB haveidentical attribute domains (union-compatible).The union cursor T1 does not contain duplicatesof rows that satisfy both conditions. Theintersection cursor T2 identifies the rows thatare in both TA and TB.Set Intersection and DifferenceIn set theory, it is known that the relationshipbetween intersection and difference satisfies theequationA B A - (A - B)The Query4 program listed below verifies thisrelationship.-- RA Query4: Intersection and DifferenceTA TSelect('STOCK', "OnOrder 'N'")TB TSelect('STOCK', "SType 'T'")T1 TMinus(TA, TB)T2 TMinus(TA, T1)T3 TIntersect(TA, TB)Here, cursor T1 is A - B, T2 is A - (A - B), andT3 is A B. Students can observe that T2 andT3 are identical.Product vs. DivideThe RA divide operation is sometimes describedas the "inverse" of the product operation, in thesense that for tables A and B,(A x B) B AThe following Query5 program illustrates thenature of this relationship.-- RA Query5: Product and DivideTA TSelect('STOCK')TB TSelect('STKTYPE')T1 TProduct(TA, TB)T2 TDivide(T1, TB)In this code, T1 is A x B and T2 is (A x B) B.Students can note that cursor T2 is the same ascursor TA.5. SPECIAL CONSIDERATIONSThe RAQ software is not a feature-rich,industrial-strength software product. It wasdesigned for academic use to provide a simple9 (5)October 2011way to teach relational algebra conceptsthrough programming. Users of this softwareshould be aware of certain limitations andspecial considerations.1.There is a 100-line maximum for RAQLquery programs (not including comments).Each instruction must be on a single line.2. RAQ provides modest error checking. Errormessages are displayed in the upper-rightcorner of the screen. Messages state the type oferror or show the offending line of code.3.RAQ has limited input options for data.The database must be in an Access MDB (notACCDB) file. If necessary, convert the ACCDBfile to MDB format. More generally, if thedatabase is an ODBC data source (e.g. Oracle,SQL Server, MySQL), then the table structuresand data can be imported into an Access filebefore using RAQ.4.RAQL query programs must be in a textfile with a TXT extension. Programs have to becreated and modified with a separate texteditor, since RAQ does not provide editingcapabilities.5.RAQ output for query results are shownon the screen. The display of intermediatecursors cannot be skipped, but RAQL programsare usually short. Query output can be saved inXLS files, and the Windows operating systemprovides various print-screen options.6.For convenience in expressing RAqueries, duplicate field names should beavoided in databases. If you prefer to haveduplicate field names in separate tables (e.g.the same name for primary key and foreignkey), use the TRename function in RAQLprograms. This
Keywords: database, query, relational algebra, programming, SQL 1. INTRODUCTION Most commercial database systems are based on the relational data model. Recent editions of database textbooks focus primarily on the relational model. In this dual context, the relational model for data
The Relational Algebra A procedural query language Comprised of relational algebra operations Relational operations: Take one or two relations as input Produce a relation as output Relational operations can be composed together Each operation produces a relation A query is simply a relational algebra expression Six "fundamental" relational operations
A graph query language is a query language designed for a graph database. When a graph database is implemented on top of a relational database, queries in the graph query language are translated into relational SQL queries [1]. Some graph query operations can be efficiently implemented by translating the graph query into a single SQL statement.
The relational model supports powerful query languages Relational calculus: a formal language based on mathematical logic Relational algebra: a formal language based on a collection of operators (e.g., selection and projection) for manipulating relations Structured Query Language (SQL): Builds upon relational calculus and algebra
Why should you Query? Centers for Medicare and Medicaid Services supports the use of query forms as a supplement to the health care record. “Use of the physician query form is permissible to the extent it provides clarification and is consistent with other medical record documentation.” 3 File Size: 254KBPage Count: 26Explore furtherPhysician Query Examples Journal Of AHIMAjournal.ahima.org2019 update: Guidelines for achieving a compliant query .acdis.orgGuidelines for Achieving a Compliant Query Practice (2019 .bok.ahima.orgThe Physician Query Process Compliance Issuesassets.hcca-info.orgThe Physician Query: What Every Coder Wants You To Knowcapturebilling.comRecommended to you b
Robert Gerver, Ph.D. North Shore High School 450 Glen Cove Avenue Glen Head, NY 11545 gerverr@northshoreschools.org Rob has been teaching at . Algebra 1 Financial Algebra Geometry Algebra 2 Algebra 1 Geometry Financial Algebra Algebra 2 Algebra 1 Geometry Algebra 2 Financial Algebra ! Concurrently with Geometry, Algebra 2, or Precalculus
Tags:css media query, css media query examples, css media query for ipad, css media query for mobile, css media query value defined in the query. max-width Rules applied for any browser width below the value defined in the query. min-height Rules applied for any browser height over the value defined in the query. max-height Rules applied for any
the design of a cloud multidatastore query language (CloudMdsQL), and its query engine. CloudMdsQL is a functional SQL-like language, capable of querying multi-ple heterogeneous data stores (relational and NoSQL) within a single query that may contain embedded invocations to each data store's native query interface. The query
Dictator Adolf Hitler was born in Branau am Inn, Austria, on April 20, 1889, and was the fourth of six children born to Alois Hitler and Klara Polzl. When Hitler was 3 years old, the family moved from Austria to Germany. As a child, Hitler clashed frequently with his father. Following the death of his younger brother, Edmund, in 1900, he became detached and introverted. His father did not .
