WO2002027551A2 - Nested conditional relations (ncr) model and algebra - Google Patents

Abstract

A model of nested conditional relations ('NCR') is provided for facilitating the integration of multiple data stores with incompatible formats. A data integration engine accepts queries using a standard query language such as XML, executes the queries against the multiple data stores and returns the results. The data integration engine uses XML as the data model in which the data from the various data stores is represented. The data integration converts data in the native format into data in a format referred to as nested conditional relations ('NCR'), applies various operators (e.g. joins, unions) to the data in NCR format to generate the query results in an NCR format and converts the results from NCR format into an XML format.

Description

NESTED CONDITIONAL RELATIONS (NCR) MODEL AND ALGEBRA

CROSS-REFERENCE TO RELATED APPLICATIONS

This application claims the benefit of U.S. Provisional Application No. 60/222,070 filed August 1, 2000 and is related to U.S. Patent Application No. 09/718,228 filed November 21, 2000, U.S. Patent Application No. 09/517,131 filed March 2, 2000 and U.S. Patent Application No. 09/517,468 filed March 2, 2000, which are hereby incorporated by reference.

BACKGROUND

The described technology relates generally to accessing data and particularly to accessing data from data sources with diverse formats.

Large organizations may have their digital data stored in various data stores, such as databases and file systems, in diverse and incompatible formats. Different groups within the large organizations may have created their own data stores to meet the needs of the group. Each group would typically select its own type of data storage system and format to meet its particular needs. Traditionally, these data stores were created independently of any other data stores within the organization. As a result, the various data stores of an organization often contained duplicate and inconsistent data.

Recently, these large organizations have adopted standards such as the extensible markup language ("XML") for representing data in a uniform format. The use of XML by each group within an organization increases the compatibility of the data stores. It is, however, difficult for organizations to provide an XML interface to each of its existing data stores. The organizations would need to expend considerable resources to provide a mapping between their existing data stores or other sources of data and the XML formats.

It would be desirable to have a system that would facilitate the integrating of data stores with incompatible formats. BRIEF DESCRIPTION OF THE DRAWINGS

Figure 1 illustrates the schema of this XML document.

Figure 2 represents a Joinln graph (JIG) for the match expression of Table 8.

Figure 3 is a block diagram illustrating the overall organization of an execution program generated by the data integration engine.

Figure 4 is a block diagram illuslrating the function to generate an execution program.

Figure 5 is a flow diagram illustrating processing of the generate extract program function in one embodiment.

Figure 6 is a flow diagram illustrating the processing of the generate extract plan function in one embodiment.

Figure 7 is a flow diagram illustrating processing of the match expression function in one embodiment.

Figure 8 is a flow diagram illustrating the processing of the create Joinln graph function in one embodiment.

Figure 9 is a flow diagram illustrating processing of the generate Joinln graph into one embodiment.

Figure 10 illustrates the tables of the data store.

Figure 11 illustrates the results of the sorted outer union for the tables of Figure 10.

Figure 12 illustrates the SQL query for each of the tables of Figure 10 paid to generate the sorted outer union.

Figure 13 is a flow diagram illustrating the processing of generating a sorted outer union.

Figure 14 is a flow diagram illustrating processing of a generate SQL query function in one embodiment.

Figure 15 is a block diagram illustrating an extract program.

Figure 16 is a flow diagram that illustrates code of a join node of an extract program in one embodiment.

Figure 17 illustrates the output of the nodes of the extraction plan to Figure 15.

Figure 18 illustrates a final NCR structure. Figure 19 illustrates the Correspondence Tree.

DETAILED DESCRIPTION

A method and system for providing data integration of multiple data stores with diverse formats is provided. In one embodiment, the data integration engine accepts queries using a standard query language such as XML-QL, executes those queries against the multiple data stores, and returns the results. The data stores may include relational databases, hierarchical databases, file systems, application data available via APIs, and so on. A query may reference data that resides in different data stores. The data integration engine allows operations such as joins across multiple data stores. In one embodiment, the data integration engine uses XML as the data model in which the data from the various data stores is represented. The data integration engine processes a query by parsing the query into an internal representation, compiling and optimizing the internal representation into a physical execution representation, and then executing the execution representation. By providing a uniform and data model, the data integration engine allows access to data stores in diverse formats.

In one embodiment, the data integration engine executes a query on a data store by first providing a mapping of the data store format into an XML format. The query for the data store is based on XML format. The data integration engine upon receiving a query, generates a native query for the data store from the received query using the provided mapping. The data integration engine then executes the native query to generate data in a native format needed to generate the results of the received query. The data integration engine then converts the data in the native format into data in a format referred to as nested conditional relations ("NCR"). The data integration engine then applies various operators (e.g., joins and unions) to the data in NCR format to generate the query results in an NCR format. The data integration engine then converts the results in the NCR format into an XML format. In this way, the integration engine can provide access to various data sources in different formats.

A nested conditional relation is a table in which each row may have a different schema and each column is either a primitive type or a nested NCR. The schema of each row in an NCR is indicated by a tag, which can be considered to be the zero column of the row. For example, certain rows of the table may represent employees of a company and have columns named "first name," "last name," "phone number," and so on. Other rows in the table may represent departments within the company and have columns named "department name," "department head," and so on. The tag for a row indicates whether the row is an employee or a department row. A column for a certain type of row may itself contain a nested conditional relation. For example, an employee row may include a column named "skills" that contains a table with sub-rows containing information relating to computer skills and accounting skills of the employee. The table may itself be a nested conditional relation in that each sub-row may include a tag indicating whether the row represents a computer skill or an accounting skill. The nesting of nested conditional relations may occur to an arbitrary level. The NCR format is described below in detail.

The following example illustrates a data store, a mapping for the data store, a query, an LMatch representation for the query, a Joinln graph for the query, and an SQL query used to retrieve the data from the data source. Tables 1-3 illustrate an example of data that is stored in a data store such as a relational database. The relational database contains three tables: DEPARTMENTS table, EMPLOYEES table, and BUILDINGSDOCS table.

TABLE 1 DEPARTMENTS

TABLE 2 EMPLOYEES

TABLE 3 BUILDINGSDOCS

The DEPARTMENTS table contains one row for each department of an organization. As illustrated by Table 1, the organization has a finance and an engineering department. The DEPARTMENTS table contains two columns: name and contact. The name column contains the name of the department, and the contact column contains the employee identifier of the contact person for the department. For example, the first row of the table indicates that the department is "finance" and that the contact employee is "E1247." The EMPLOYEES table contains a row for each employee in the organization. Each row includes seven columns: ID, Fname, Lname, Dept, Bldg, Office, and Manager. The ID column uniquely identifies the employee, the Fname column contains the first name of the employee, the Lname column contains the last name of the employee, the Dept column identifies the employee's department, the Bldg column identifies the building in which the employee is located, the Office column identifies the employee's office within the building, and the Manager column identifies the employee's manager. The Dept column contains one of the values from the Name column of the DEPARTMENTS table. The BUILDINGSDOCS table contains a row for each office within each building of the organization. The BUILDINGSDOCS table contains four columns: Building, Office, Phone, and MaintContact. The Building column identifies a bwlding, the Office column identifies an office within the building, the Phone column contains the phone number associated with that office, and the MaintContact column identifies the employee who is the maintenance contact for the office. The combination of the Building and Office columns uniquely identifies each row. The Bldg and Office columns of the EMPLOYEES table identifies a row within the BUILDINGSDOCS table.

Table 4 is an example of data stored as an XML document.

TABLE 4

<deptlist>

<deptname="Finance"> <employee> <name><lϊrst>Bobby</lϊrst><last>Darrows</last></name> <office phone="xll02"/> </employee> <employee>

<name><first>Alice</first><last>LeGlass< last></name>

<office phone="xll03"/> </employee>

</dept>

<dept name="Engineering"> <employee>

<name><fϊrst>David</first><last>McKinzie</last></name> </employee> <employee>

<name><&st>Misha</first><last>Niev</last></name> <office phone="x0150'7> </employee>

</dept> </deptlist>

The XML document includes the root element <deptlist> that has a name attribute and that contains a <dept> element corresponding to each department within an organization. Each <dept> element contains an <employee> element for each employee within the department. Each <employee> element contains a <name> element and optionally an <offιce> element. The <name> element includes a <first> element and <last> element. The <office> element includes a phone attribute. The schema of an XML document may be represented by an XML data type definition ("DTD") of the document. Figure 1 illustrates the schema of this XML document. As this figure illustrates, the schema is specified as a tree-like hierarchy with the nodes of the tree having parent-child relationships. For example, node 104 is the parent of nodes 105 and 108, which are children of node 104. Node 101 corresponds to the <deptlist> element and has one child node 102, which corresponds to the <dept> element. Node 102 has two child nodes, 103 and 104. Node 104 corresponds to the name attribute of the <dept> element and node 104 corresponds to the <employee> element. Node 104 has two child nodes 105 and 108. Node 105 corresponds to the <name> element and has two child nodes 106 and 107. Node 106 corresponds to the <first> element, and node 107 corresponds to the <last> element. Node 108 corresponds to the <office> element and has one child node 109, which corresponds to the phone attribute.

The mapping technique is particularly useful in situations where a legacy database, such as the example database of Tables 1-3, is to be accessed using queries designed for XML data, such as the example of Table 4. The XML schema may be previously defined and many different applications for accessing data based on that XML schema may have also been defined. For example, one such application may be a query of the data. An example query for semi-structured data may be an XML transform that is designed to input data in XML format and output a subset of the data in XML format. For example, a query for the database of Tables 1-3 may be a request to list the ID of each employee in the "Finance" department. The subset of that data that is output corresponds to the results of the query represented by the XSL transform. One skilled in the art would appreciate that queries can be represented in other formats such as XML-QL. When a legacy database is to be accessed, the data is not stored using XML format. Thus, in one embodiment, a query system inputs a semi-structured query and uses a mapping table to generate a structured query, such as an SQL query, that is appropriate for accessing the legacy database. The mapping technique for generating that mapping table is described in the following.

Table 5 is a portion of the mapping table generated in accordance with the mapping technique that maps the XML schema of Table 4 to the legacy database of Tables 1-3.

TABLE 5

The mapping table contains one row for each parent-child relationship of the XML schema. The mapping is further described in U.S. Patent Application entitled "Method and Apparatus for Storing Semi-Structured Data in a Structured Manner." As shown in Figure 1, the XML schema defines eight parent-child relationships such as the relationship between node 102 and node 104. Thus, the mapping table contains eight rows. Each row uniquely identifies a parent-child relationship using the ParentName and ChildName columns. For example, the parent-child relationship of node 102 and node 104 is represented by row 3 as indicated by the ParentName of "dept" and the ChildName of "employee." Each row maps the parent-child relationship to the table in the legacy database that corresponds to that relationship. In the example of row 3, the Table column indicates that the "dept-employee" relationship maps to the EMPLOYEES table. The query system could use only the ParentName, ChildName, and Table columns of the mapping table to generate a structured query from a semi- structured query. For example, if the legacy database had used the same column names as defined by the elements of the XML schema (e.g., "employee" rather than "ID"), then only these three columns would be needed to generate the structured query. For example, if the semi-structured query requested an identifier of all employees within the finance department and the DEPARTMENTS table contained an "employee" column rather than an "ID" column, then the query system could input a semi- structured query with only these three columns and generate a structured query. In the more general case where the columns of the legacy database are arbitrarily named, the mapping table includes a parent key column ("PKey") and a child key column ("CKey"). The parent key column contains the name of the column that identifies the parent of the parent-child relationship. The child key column contains the name of the column that identifies the child of the parent-child relationship. For example, in row 3, the parent is identified by the "dept" column and the child is identified by the "ID" column in the EMPLOYEES table. Thus, to generate the structured query to retrieve the ID of an employee within the finance department, the query that uses a select clause of EMPLOYEES. dept="Finance" would be used. Table 5 also includes a column named "A/E" to indicate whether the row corresponds to an element within the semi- structured data or an attribute of an element with semi-structured data. As illustrated by rows 7 and 8, some of the parent and child keys actually consist of multiple columns that uniquely identify a row in the corresponding table. For example, the rows of the BUILDINGSDOCS table are uniquely identified by a combination of the Building and Office columns.

The query system maps the selections within the semi-structured query to selections within a structured query. The following illustrates the basic format of that mapping when the structured query is an SQL format.

SELECT {TABLE}JCKEY} FROM {TABLE}

WHERE {TABLE}.{PKEY} = pkey

The TABLE, CKEY, and PKEY parameters are replaced by the corresponding values from the row in the mapping table for the parent-child relationships specified by the selection. In other words, this query will find all the children given the key for the parent. The following illustrates the format of the mapping when the query represents the identification of the idea of all employees within the finance department.

SELECT EMPLOYEES.ΓD

FROM EMPLOYEES

WHERE EMPLOYEES.Dept = "Finance"

The query system also allows chaining of keys to effectively navigate through the hierarchy defined by the semi-structured data. The query system uses the joint concept of relationship databases to effect this chaining of keys. The following illustrates chaining:

SELECT {TABLE2}.{CKEY2}

FROM {TABLE1}, {TABLE2}

WHERE {TABLE1}.{PKEY1} = pkey && {TABLE1}JCKEY1}=

{TABLE2}.{PKEY2}

The TABLE1, PKEYl, and CKEYl parameters are derived from the first parent-child relationship in the chain, and the TABLE2 , PKEY2, and CKEY2 parameters are derived from the second parent-child relationship in the chain. The child key associated with the first parent-child relationship matches the parent key associated with the second parent-child relationship. The following is an example of the chaining to identify the building for the employees of the finance department.

SELECT BUΓLDINGSDOCS.BUΓLDING

FROM EMPLOYEES, BUTLDINGSDOCS WHERE EMPLOYEES = "Finance" && EMPLOYEES.BLDG = BUILDINGDOCS.BUILDING && EMPLOYEES.OFFICE = BUrLDINGDOCS.OFFICE

In one embodiment, the mapping table also contains the value rows corresponding to each leaf node, that is a node that is not a parent node. The leaf nodes of Figure 1 are nodes 103, 106, 107, and 109. In one embodiment, each value row identifies an XML element or attribute, the table in the legacy database that contains an element, and the name of the column in the table that contains the value for that element or attribute. Table 6 illustrates the four value rows for the mapping associated with Tables 1-3 and Table 4.

TABLE 6

The "A/E" column identifies whether the row is an attribute or element; the "Name" column identifies the name of the element and attributes; the "Table" column identifies the legacy table; the "Key" column identifies the key for that table; and the "Value" column identifies the name of the column where the value is stored.

Table 7 illustrates a query that is to be applied to the data of Tables 1-3. The query indicates to return the first and last names and phone number of each employee in the engineering department.

Table 7

The data integration engine generates a "match expression" for a logical match operation ("LMatch") for the query when compiling the query. The logical match operation supports operations for performing XML navigation. The match expression defines a tree of navigations. Each node of the tree indicates a navigation type (e.g., child, parent, or sibling), a navigation condition (e.g., a condition on the name of the child), whether the navigation is required, whether there should be a binding to the target of the navigation (i.e., a value returned with the specified name), and whether the result should be nested.

Jo- Table 8 illustrates a match expression for the XML of Table 4 for the query of Table 7. Each row of Table 8 represents a different navigation path. For example, the first row represents a navigation path from the root of the deplist element to its child element of the dept element and then to the name attribute of the dept element. The remaining rows represent different branches on the tree. For example, the second row represents the branch of roøt(deplist), c (dept), c <i(employee), c <i(name), and child(_ιst). The symbols prefixed with "$" represent bindings.

Table 8

Figure 2 represents a Joinln graph (JIG) for the match expression of Table 8. The Joinln graph is a data structure that facilitates the optimization of the query to be executed against the data store. This JIG indicates that the Departments, Employees, and Buildhigdocs tables of the data store are to be joined together. This JIG also indicates the bmdings (e.g., $first) and the join columns (e.g., Name and Dept). The format of the JIG is described below in detail. The JIG is generated from the match expression using the mapping. The data integration engine then generates the query to be executed. The following query is generated.

SELECT EMPLOYEES.Fname, EMPLOYEES. Lname, BUILDlNGSDOCS.phone FROM DEPARTMENTS, EMPLOYEES, BUILDINGSDOCS WHERE DEPARTMENTS.Name = EMPLOYEES.Dept AND

EMPLOYEES.Bldg = BUILDINGDOCS.Building AND

EMPLOYEES.Office = BUTLDINGSDOCS.Office AND

DEPARTMENTS.NAME = "Engineering"

Figure 3 is a block diagram illustrating the overall organization of an execution program generated by the data integration engine. An execution program consist of an extract program 310 and a construct program 320. A compiler of the data integration engine generates the execution program during a compilation phase. The extract program is a series of operations on a data extracted from the data sources. The extract program represents a graph of the operations. The leaf nodes 311 of the extract program represents a sorted outer union operation applied to the data stores 312. The compiler generates a query for each data store in the native query language of the data store to retrieve the results of the sorted outer union. The compiler generates the sorted outer union using the LMatch operation, Joinln graph, and mapping. During execution of the extract program, the generated query is applied to each data store. The construct program accesses the root node 313 of the extract program which retrieves the results generated by the extract program. The construct program collects the data and formats it into an XML output. As discussed below in more detail, the output of each operation of the extract program is in a nested conditional relation format.

Figures 4-9 are flow diagrams illustrating processing of the compiler of the data integration engine in one embodiment. Figure 4 is a block diagram illustrating a function to generate an execution program. The function first generates the extract program and then generates the construct program. In block 401, the function invokes a generate extract program function to generate an extract program for the specified query against the specified data stores. In block 402, the function invokes the generate construct program function to generate a construct program to generate the results from the extracted data.

Figure 5 is a flow diagram illustrating processing of the generate extract program function in one embodiment. In block 501, the function generates an extract plan, hi block 502, the function identifies fragments of the extract plan. A fragment of an extract plan are the set of operations that are applied to data derived from a single data source. Operations that apply to data from multiple data sources are grouped into one fragment. In block 503, the function optimizes the operations of the fragments and then returns.

Figure 6 is a flow diagram illustrating the processing of the generate extract plan function in one embodiment. In block 601, the function receives the XML query. In block 602, the function generates a match expression for the logical match associated with the data store. In block 603, the function creates the Joinln graph from the match expression using the mapping for the data store. In block 604, the function generates the native query from the Joinln graph. The function indicates additional processing to generate the extract plan from the Joinln graph. Blocks 602-604 illustrate the generation of the native query for the sorted outer union of the leaf nodes of the extract plan. The ellipses indicate other processing performed by the function. The function then returns.

Figure 7 is a flow diagram illustrating processing of the match expression function in one embodiment. This function is passed an XML node representing the data store and returns the match expression. This function is recursively invoked for each child node of the passed XML node. In block 701, the function initializes the subtree to the XML node. In block 702-705, the function loops creating a match expression for each child node. In a block 702, the function selects the next child node of the XML node. In decision block 703, if all the child nodes have already been selected, then the function returns, else the function continues at block 704. In block 704, the function recursively invokes the create match expression function passing the child node and receiving a child sub-tree in return. In block 705, the function adds the child sub-tree to the sub-tree and then loops to block 702 to select the next child.

Figure 8 is a flow diagram illustrating the processing of the create Joinln graph function in one embodiment. In block 801, the function invokes the generate Joinln graph passing the match expression and receiving the Joinln graph in return. In block 802, the function merges nodes of the Joinln graph. In block 803, the function processes merging of adjoining nodes of the Joinln graph and then returns.

Figure 9 is a flow diagram illustrating processing of the generate Joinln graph function into one embodiment. This function is passed a match expression and returns a Joinln graph. The function is recursively invoked for each child node of the passed match expression. In block 901, the function sets the Joinln graph to a node corresponding to the root of the match expression. The function retrieves the mapping rows that can further the path from the root. In block 902, the function selects the next child node of the match expression. In decision block 903, if all the children have already been selected, the function returns, else the function continues at block 904. In block 904, the function recursively invokes the generate Joinln graph function passing the selected child node of the match expression and receiving a child Joinln graph in return. In block 905, the function adds the child Joinln graph to the Joinln graph and then loops to block 902 to select the next match expression. Figures 10-15 illustrate the generation of an SQL query for a sorted outer union node of an extract program. Figure 10 illustrates the tables of the data store. The arrows between the tables illustrate joins between tables. For example, arrow 1001 represents a join between the third column of table 1.1 and the first column of table 2.L Figure 11 illustrates the results of the sorted outer union for the tables of Figure 10. Figure 12 illustrates the SQL query for each of the tables of Figure 10 that are used to generate the sorted outer union.

Figure 13 is a flow diagram illustrating the processing of a function to generate a sorted outer union. In block 1301, the function selects the next table of the source data store. In decision block 1302, if all the tables have already been selected, the function continues at block 1304, else the function continues at block 1303. In block 1303, the function invokes the generate SQL query for the selected table and then loops to block 1301 to select the next table. In block 1304, the function executes the generate SQL queries against the tables. In block 1305, the function aggregates of the result of the queries into a table. In block 1306, the function sorts the results and then returns.

Figure 14 is a flow diagram illustrating processing of a generate SQL query function in one embodiment. In block 1401, the function outputs a select, from, and where clause for the query. In blocks 1402-1408, the function loops selecting each table in a join path of the data store. In block 1402, the function selects the next table in the path. In decision block 1403, if all the tables have already been selected, then the function returns, else the function continues at block 1404. In block 1404, the function adds the table to the from clause. In block 1405, the function adds the table to the where clause. In block 1406-1408, the function loops selecting each column of the selected table. In block 1406, the function selects the next column. In decision block 1407, if all the columns have already been selected, then the function loops to block 1402 to select the next table of the path, else the function updates the select clause with the column and then loops to block 1406 to select the next column. Columns of tables not in the selected path are set to null.

Figure 15 is a block diagram illustrating an extract program. Each of the leaf nodes 1501-1505 represent SQL queries that are applied to a data source. Node 1506 represents a nesting of the results of nodes 1501 and 1502. Node 1507 represents a nesting of the results of nodes 1506 and 1503. Node 1508 represents a selection on the results of node 1507. Node 1509 represents a nesting of the results of nodes 1504 and 1505. Node 1510 represents a join of the results of nodes 1508 and 1509. Node 1511 represents a projection of the results of node 1510. Node 1512 represents the construct program that accesses the extract program.

Figure 16 is a flow diagram that illustrates code of a join node of an extract program in one embodiment. In one embodiment, the processing of each node of extract program is performed a pipeline manner, that is each node returns only the data needed to satisfy the next request from the construct program. In decision block 1601, if the right node is a fully processed, then the function continues at block 1602, else the function continues at block 1605. In decision block 1602, if the left node of the join is fully processed, then the function returns, else the function continues at block 1603. In block 1603, the function retrieves at the next results from the left node. In block 1604, the function initializes the right node based on the results returned from the left node. In block 1605, the function retrieves the next results from the right node. In decision block 1606, if the results returned from the right node are contained in nested table, then the function returns an iterator for that table, else the function returns the results. The iterator for a table is an optimization that allows nodes higher in the extract program to retrieve subsequent rows of the nested table without having to invoke lower-level nodes in the extract program.

Figure 17 illustrates the output of the nodes of the extraction plan to Figure 15. When the construct program 1712 invokes root node 1711 of the extract program, that invocation is propagated down to the leaf nodes. The SQL query of node 1701 returns result 1713, and the SQL query of node 1702 returns result 1714. Node 1706 indicates to nest results of nodes 1701 and 1702. In this case, result 1714 is nested within result 1713 as indicated by result 1715. Node 1703 returns result 1716. Node 1707 nests result 1716 within result 1715. The subscript within node 1707 specifies a target for the nesting. In this case, the subscript 2 indicates to nest within the third column of result 1715. (Columns are identified starting with column 0.) Result 1717 represents the result of the nesting. Node 1708 represents selection on the result 1717. The target represented by subscript 2.1 indicates to select the third column and the first row within the third column. The result of the selection is result 1718. Results 1719-1723 illustrates the results of the other nodes of the extract program.

LMatch Operation

The LMatch operator performs navigation-based selection over XML input. The following example illustrates an XMLQL syntax fragment and the LMatch instance that is created to model it inside the compiler:

<aXb><c>$c</></></> ELEMENT_AS $a

LMatch ( $ results , "self (a, $a) -cήild (b, ~ ) -child ( c, $c) " )

The "se//(a,$a) — child(b,—) — child(c,$c)" is a match expression. In this example, the match expression is a tree with three nodes. The general structure of the XMLQL pattern is translated into an isomorphic pattern within the match expression. The XMLQL variables become "bindings" within the navigations. The LMatch operator is one of the logical operators of the internal language of the data integration engine. The LMatch operator is generally the "first" operator that is applied to input data and is responsible for converting XML input in to NCRs that are then further processed by the query engine. The LMatch operator is a logical operator only in that one of the actions of the Compiler is to convert LMatch operators into a data source- dependent form (e.g., SQL for relational databases, or QLL for QL-Lite data sources).

The LMatch operator defines a match against XML data. The pattern is defined by the "match expression," which is a tree of navigation steps. Each navigation step describes a "movement" from a source element or attribute to a target element or attribute. The parameters of the navigation step that govern navigation are as following:

• The type of movement or navigation (child, parent, descendant etc.) The navigation types are based on XPath axes.

• The name of the target element or attribute, which may be a wild card.

• Whether the target should be an element, an attribute, or either.

• Whether the navigation is optional or not.

LMatch matching is top-down on the tree of navigation steps. That is, the match begins at the root of both the XML document and the root of the match expression. Matches for the first navigation step are sought in the entire XML document. If the first navigation is a root navigation, then it matches the root of the XML document (where we interpret root to be the root element, not the document item, as defined in DOM). If the first navigation step is something other than root, it is as a navigation from the root.

Once a node or set of nodes have been identified for the first navigation, the algorithm proceeds recursively: given a matched node, attempt each of the child navigations from the navigation tree (where child here means "child in the navigation tree," rather than child type node). Each attempted navigation will itself yield a new set of zero or more matches, which are then continued in the next level of the recursion, and so forth. While the recursion proceeds down the navigation tree, the navigations do not necessarily proceed "down" the XML tree; navigation types can move in arbitrary directions within the XML document (e.g., ancestor or precedingjsibling).

If an attempted navigation yields zero matches from some source node, then that navigation is said to have failed. If the navigation was not marked as optional=true, then the failure of the naviagation causes the source node to be "unmatched." The following match expression illustrates the failure of a navigation; se /(a,$a)— child(b,$b)— child(c,$c)

The first navigation step may yield a single element <a>. The second step may yield a set of <b> elements, some of which contain <c> elements and some of which do not. When the final navigation is evaluated, it will, for some <b> elements, yield no results. If the navigation is optional (optional=true), then all the <b> elements are included in the result. If, however, the navigation is required (optional=false), then those <b> elements that contain no <c> elements are removed from the set of matches for child b) from the root <a> element. The result contains only <b> elements that actually contain <c> elements. If no <b> elements remain after this process, then the failure propagates upward, ''urrmatching'' the <a> elements (unless the child(b) navigation was optional).

The evaluation of an LMatch operator is a three stage process: first, match the pattern within the LMatch operator against some source of XML; second, connect columns in the LMatch pattern with their associated items in the information set of the XML source; and thirs, structure those connected columns (the extracted information) into an NCR as indicated by the nesting settings on individual navigations. That is, an LMatch operator specifies a structural pattern that is sought after in a document, specifies which parts of that pattern should be returned, and specifies how the returned parts should be organized. The output of an LMatch operator is an NCR that contains the returned parts, organized as specified.

The parameters of the LMatch that govern how results are constructed are these:

• The set of columns returned. An NCR column "names" some piece of information returned from an element or attribute node that has been matched. There are several kinds of columns: o value: the contents of a simple element or attribute o subtree: the entire element (not applicable to attributes) o name: the name of the element or attribute o text: the text value of an element (used to extract text from mixed-content elements; in a simple element it is equivalent to 'value') o table: the table column gives a name to the entire set of results when nested— true

• Whether or not the results of the navigation should be nested.

Each navigation step may have one or more of the column types present. The type of the column is derived from the type of the corresponding contents of the XML document (except for the table column).

These columns are structured into an NCR based on the nested flag and the table column: linested=true, then the table column was specified, and the navigation creates a nested NCR. This NCR contains all the other columns for this navigation step, as well as all the columns generated by the subtree of navigations beneath it. For example: selfip,-)— _NcM<i(b,$b,$btable)— child(c,$c) —child(ά,$ά)

The root (top-level) navigation may also be nested or unnested. hi addition, the

LMatch operator, like other operators, provides an additional column that names the its entire schema. The child(b) navigation is a nested navigation that results in a nested NCR, named $btable, in the result. This NCR will contain columns $b (because $b is a column on the child(b) navigation) and $c (because $c is a column on a navigation in child(b)'s subtree). Figure 18 illustrates a final NCR structure.

A depth-first traversal of the match expression of an LMatch operator is used to construct the columns of the output NCR. As a result, the LMatch operator also defines an ordering of the columns as well as their structure and names.

When a navigation matches multiple times, then the results differ based on whether the navigation is nested. If the navigation is a nested navigation, then a nested NCR is created, which will contain the matches. But if the navigation is not nested, then the results are combined via a cross-product with all the other columns in the same table. So, if one <b> element contained multiple <c> elements, the $btable would contain the corresponding <b>-<c> pairs. Navigations that are not nested can be treated as a special case of nested navigations. Thus, an LMatch operator can be evaluated as if all navigations are nested. Then, for each navigation that is actually nested, a an LFlatten operation can be used to remove the table corresponding to the nesting.

A subtree column results in the entire XML subtree, tags and all, being returned as an atomic value. (This corresponds to the ELEMENT_AS notation in XML-QL.) The compiler transforms this column into a more complex LMatch expression that "pulls apart" the entire subtree contents and modifies the rest of the execution unit to reconstruct the result back into a subtree when needed. As a result, subtree columns exist initially, but they are replaced with more complex patterns. Before they are rewritten, the subtree columns are modeled in the NCR schema as a single, static column. After the rewrite, they begin with a table-valued column containing the nested results.

Advantages of the LMatch operator being a single, complex operation include:

1. When queries are generated for query languages which themselves contain some form of matching operations, then mapping onto those operations is enabled.

2. Certain optimizations that may be done on navigational matching are better enabled by capturing succinctly the navigation that is being done. In particular, reasoning about substitution of a descendant relation with a union of paths, and vice versa. Also, also reasoning about document order relations. 3. The LMatch operator combines two kinds of capabilities into a single operator: navigational operations and composition of the results into a complex structure (the NCR). This allows a concise representation of a very common idiom. The LMatch operator can be matched against a tree that represents an XML generator, rather than the actual XML document. For example,

• The XML RDB Map can be interpreted as a generator of an XML document from a relational database. Matching the LMatch operator against an XML RDB Map is a fundamental step in converting the XML query into SQL.

• The Construct Program of a query can be interpreted as a generator of an XML document from an NCR. Matching the LMatch operator against a Construct Program is a fundamental step in composing views.

The algorithm for matching against tree-structured XML generators is very similar to the algorithm for matching against XML input directly. One difference is that where matching against an XML document generates tuples of output, matching against a generator generally produces a Correspondence Tree, which encodes all the potential correspondence points between the nodes of the generator and the navigation steps of the LMatch.

An XML generator is a tree (actually, a forest suffices) where the nodes in the tree represent the generation of XML elements or attributes or their values, and arcs between nodes represent inclusion. For example: element("person") — attribute("ssn") — value() — element("name") — value() — element("address") — value()

The XML generator also indicates the arity of each arc. The values for arity are optional (0 or 1), singular (exactly one) and multiple (0 or more). If an arc is marked multiple, then the generator can generate more than one instance of the child node for each parent instance. In the above example, if the arc between "person" and "name" were marked multiple, then a person could have zero or more names. The arity of an arc is indicated by a subscript on the arc as shown in the following: element("person") — _sattribute("ssn") — value()

— _Melement("name") — value()

— ₀element("address") — value() When no arity is indicated, singular is assumed. If it is not possible to derive arity information from the generator, then multiple is assumed, since it is the most general case.

The Correspondence Tree tracks which navigation steps in the LMatch operator correspond with which nodes in the XML generator. The Correspondence Tree would be isomorphic to the LMatch navigation graph except for one thing: any given navigation step might match against multiple nodes in the generator. The following is an example of an XML generator, an LMatch operator, and the corresponding

Correspondence Tree:

The XML generator: elementC'person' i — attribute("ssn")₂ — value()₃

— element("name")₄ — value()₅

— element("name")₆ — value()₇

The LMatch: selft($ςχsoή)\ — child(ssn)₂

Figure 19 illustrates the Correspondence Tree.

The subscripts on nodes in the generator and LMatch distinguish otherwise identical nodes when they appear in the Correspondence Tree. The Correspondence Tree is "read" as: "The root navigation has a single match, namely the elementC'person"^ node of the XML generator. From this generator node, the next LMatch navigation, c <i(name)₃, is matched against two different generator nodes, and so on.

The Correspondence Tree is a bipartite graph. A bipartite graph is one in which nodes come in two different alternating types. In this case, the node types are called navigation nodes (which reference navigation steps, and are pictorially indicated with brachets [ ]) and choice nodes (which reference generator nodes, and are pictorially indicated with braces { } ). A bipartite graph is interpreted as having two different kinds of arcs, which are indicated by lines of different weights: light lines are choice arcs (arcs from navigation to choice nodes, choosing amongst multiple correspondences) and heavy lines are navigation arcs (arcs from choice to navigation nodes, following the navigation relationships in the LMatch operator).

A correspondence is a (navigation step, generator node) pair of a correpondence tree. A correspondence is derived from a choice node by including the navigation step from the parent. For example, the following subgraph of a correspondence tree yields the following correspondence: subtree: [ c (name)₃ ] — { S: element("name")₄ } correspondence: { c d(name)₃, element("name")₄ }

The following matching algorithm generates the Correspondence Tree, given an LMatch operator and an XML generator as input. The algorithm is a top-down recursion over the LMatch navigation graph.

The XML generator has the following operations:

XMLGenerator.root () -5> ordered list of GeneratorNode

Generate.rNode. type ( ) -5> { "element" I "attribute" | "value" }

GeneratorNode.name () -^ GName

GeneratorNode . genChildren ( ) --> list of GeneratorNode

GeneratorNode. arity (childNode) ^■-► { II_S II 1 lljyjll 1 IIQ1I J

In this example, the LMatch operator is limited to the following navigation types: root, child, self. The nested flag on LMatch navigation steps is irrelevant to matching. The LMatch operator provides the following pseudo code for accessing the match expression:

LMatch . root ( ) - NavStep

NavStep type() -5 { "root" | 'child" | "self" }

NavStep ea() -> { "element" | "attribute" 1 "either" }

NavStep name ( ) -> NName

NavStep navChildren ( ) -_> list of NavStep

NavStep optional ( ) -S boolean

There is also a fimction, nameMatch (GNa e, NName) -» boolean, that returns true or false as the name from a generator node matches the name of an LMatch navigation. The Correspondence Tree provides the following operations:

CorrespondenceTree.root () -j NavigationNode CorrespondenceTree.createRoot ( NavStep ) NavigationNode.ne ( NavStep ) NavigationNode.navStep () -_> NavStep

// Model . navStep () . type () NavigationNode.typef) -5 { "root" | "child" | "self" }

// And . navStep () . name () NavigationNode .name ( ) -_ NName NavigationNode.choiceChildren( ) -> list of ChoiceNode

NavigationNode. addChoiceChild( ChoiceNode )

ChoiceNode. ne ( GeneratorNode, arity )

ChoiceNode. generatorNode ( ) -_► GeneratorNode

ChoiceNode. type () -> { "element" | "attribute" | "value" } // ditto

ChoiceNode. name () -> GName // ditto

ChoiceNode. arity () - { "S" | "M" | "0" } // ditto

ChoiceNode .navChildren ( ) - list of NavigationNode

ChoiceNode . addNavChild( NavigationNode )

The following illustrates the BuildCorrespondence function that is invoked to build a Correspondence Tree for an XML generator and an LMatch operator:

// Assume a rooted LMatch match expression;

// normalize the LMa tch to make this true if necessary.

BuildCorrespondence ( XMLGenerator g, LMatch lm )

{

// create the correspondence tree ct <- new CorrespondenceTree nn <- new NavigationNode ( lm.root() )

// bootstrap the first level of expansion,

// matching root against roots ct. addRoot ( nn ) foreach( gn in g.getRoot() ) { en <- new ChoiceNode ( gn, "M" ) if ( addNavs ( nn, en ) ) nn->addChoiceChild( en )

} return ct

}

The form of the algorithm is mutual recursion between two functions, each of which extends the graph by one level, or fails to do so (because there is no match). The subroutines return boolean values indicating whether or not they were successful; this value is then used to determine whether or not to continue and whether or not to actually add nodes to the graph. The following is the pseudo code for the addNavs function:

// From a given corresponding navigation and choice node pair, // extend the choice node for each child navigation of the

// navstep. boolean addNavs ( NavNode nn, ChoiceNode en )

{ foreach ( step in nn. navStep () .navChildren () ) { stepnavnode <- new NavigationNode ( step ) success <- addChoices ( en, stepnavnode ) // if a navigation is optional, we include the navNode, // even if it failed (the navNode will have no choice children) if ( success I | step. optional ( ) ) cn->addNavChild ( stepnavnode ) else // failure of a required navigation/ abort return false

}

// if no required navigation failed, return true return true

The following is the pseudo code for the addChoices function:

// Given a location in the generator and a requested

// navigation, "follow" the navigation in the generator tree,

// finding a new layer of correspondences . boolean addChoices ( ChoiceNode en, NavNode nn )

{ success <- false foreach ( gn in follo ( cn.generatorNode () , nn. navStep () ) { choicenode <- new ChoiceNode ( gn, cn.generatorNode () .arity (gn) ) thissuccess <- addNavs ( nn, choicenode ) if ( thissuccess ) { nn->addChoiceChild ( choicenode ) success <- true } }

// return true if at least one choice worked out return success;

The following is the pseudo code for the follow function:

// Implement the actual navigation; this would be extended // with more types of navigation as the LMatch is extended // (and would probably require the generator to support more // powerful navigations as well, at least parent () ) . List<GeneratorNode> follow ( GeneratorNode gn, NavStep nav ) {

List<GeneratorNode> result <- (); switch ( nav. type () ) { case "self" : if ( nameMatch ( gn .name ( ) , nav.name ( ) ) result. add ( gn ) case "child" : foreach ( gnkid in gn. genChildren ( ) ) if ( (nav.ea() == "element" || nav.ea() == "either") && gnkid. type () == "element" && nameMatch ( gn. name ( ) , na . ame ( ) ) ) result. add( gn ) else if ( (nav.ea() == "attribute" || nav.ea() == "either")

&& gnkid. type () == "attribute"

&& nameMatch ( gn.name(), nav. name () ) ) result. add ( gn ) case "root" : foreach ( r in xmlGenerator. root ( ) ) // [1] if ( namematch ( r. name ( ) , nav. name ( ) ) result. add ( r ) } return result }

The BuildCorrespondence algorithm presented above does not match against actual XML data. However, an XML document may be considered a degenerate XML generators with singular-arity arcs and constant value nodes and and NCR is built rather than a Correspondence Tree . The relationship between a Correspondence Tree and an NCR is as follows:

• The values for any particular navigation are the concatenation of the values for each choice below that navigation; the result is a list of data.

• For nested navigations, the rows of the nested table are that list of data.

• For an unnested navigation that is at most singular, then the list of data can contain only 0 or 1 rows. In this case, the NCR column is essentially a field that is filled in the by value.

• In general for unnested navigations, the list of data is "joined against" the existing rows of the containing table. If the navigation is optional, the join is an outer join, if required, an inner join. If the list has multiple entries, the effect is a cross product against the other contents of the table.

Because navigations can result in failure that propagates recursively upwards, matches to the leaves are evaluated before committing to any results. Alternatively, the LMatch operation could contain only optional navigations or only required navigations in cases where the data will be present. Similarly, it is possible to eliminate the need to handle joins or cross products by limiting the LMatch operator to only allow unnested navigations when the data is at most singular.

Two type of normalization that can be performed on LMatch operators are removal of (non-root) self navigations and removal of implicit cross-products. The normahzed LMatch operator would consist only of a single root self navigation and following child navigations, where for each child navigation, nested=true. Alternatively, the normalization could cover either (nested=true) or (nested=false and optional=false and the child is known to exist in a strict 1:1 relationship with the parent). Additional normalizations, such as requiring optional=true on all nested child steps, may also possible.

To normalize the LMatch operator, additional operators are inserted to the Logical Extract Program to compensate for the changes to the LMatch operator. These logical operators include the LSelect, LFlatten, and LBox operators. The LSelect operator removes tuples from a table based on some condition. The LFlatten operator flattens a nested table within an NCR. The operator is applied to a single nested table, and the process of flattening removes that table. The LFlatten operator has a boolean parameter "outer" indicating whether the flattening operation should behave like an inner or left outer join — that is, if the nested table is empty, does flattening remove the containing row or not. The LBox operator serves to introduce an artificial level of nesting within a table.

A singular relationship between a child navigation and its parent navigation is identified by examining the XML schema of the data that the LMatch operates against. Initially, the matching algorithm has been run. After that, it can be deteimined, for each navigation step, which place(s) in the schema the LMatch operator could match. From that information, and from the cardinality information available in the schema, it can be identified whether the singular condition holds.

The first version of the algorithm generates an LMatch that contains a single, top-level set/ navigation and otherwise contains only child navigations. All navigations (including the self at the top) have nested=true. The resulting navigations may have optional-true or optional=false. The implementation can be styled in a bottom-up or top-down traversal, but note that in either case compensating operators are to be inserted at both the bottom and top of the chain.

The table below illustrates the various cases that can arise. The right-hand column has examples of the transformations. Here is a sample XML document this can be tested against:

<a>al<b>bl</b> <c>cl<d>dl</dx/c></a> <a>a2<b>bl</bxb>b2</b>

<c>cl<d>dl</dx/c>

<c>c2<d>d2</dx/c></a> <a>a3

<c>cl<d>dl</dX/c>

<c>c2<d>d2</dxd>d3</dx/cx/a> <a>a4<b>bl</bx/a>

Figures 20-25 illustrate normalization.

In one embodiment, the following optimization may be applied. If an LBox is followed by the flattening of all its columns, the nested tables can be joined with a sequence of LJoin operators (as cross products) instead. This optimization could be performed either during this algorithm, or as a post-processing step. To illustrate, the last example above could be rewritten as shown in Figure 25: Alternatively, the normalization can be modified to state that only nested=true are added to child steps that can have multiple (or, possibly, optional) values. This normalization is may be easier for inputs to create NCR' in which 1: 1 elements are listed as flat columns of a row; any nesting on these columns may need to be added by an explicit LBox operator. In the case of a child step that has nested=false, and the step has been marked as singular without changing the value of the nested flag and without adding an LFlatten operation. The other steps do not change; in particular elision of a self step in the general case may result in adding an LBox, possibly followed by a LSelect or LFlatten operation. However, if all child navigations of a se/ navigation are singular, then the LBox and corresponding LFlatten can be omitted. The corresponding LSelect needs to be changed to a test on the NULL-ness of the columns, rather than a test on the emptiness of a nested table. This condition can be detected in a postprocessing step, but it would require information from both the LMatch (the singularity of steps) and correlated information from the logical extraction program (the presence of LBox and LFlatten/LSelect); thus, this optimization may be implemented as an integral part of the recursive algorithm.

Nested Conditional Relations (NCR) Model and Algebra

NCR extends relational algebra in two ways. First, it makes relations heterogeneous (i.e., allows them to contain records of different types). Each record is accompanied by a tag, describing its type, hence the term conditional relation. Second, relations can be nested. The value of an attribute can be either atomic (e.g., int, float, string) or another NCR.

1.1 Relational Algebra

Traditional relational data models have tables that are homogeneous and flat and selection and projection operators select a subset of rows and fields in a table. A homogenous table is one that has rows of the same type. A flat table has atomic fields, that is fields in the first normal form. The following table is a traditional relational data model.

Each row in the table is a record of the same type:

[Name : string, ID : string, Phone : int, Floor : int] Each field in the row is an atomic type. The table is a set of such rows. Its type is:

Depts: {[Name : string, ID : string, Phone : int, Floor : int]}

The following selection operation (σ): σ Fioor_>3 (Depts) results in a subset of the original table, consisting of the highlighted rows below:

The following projection operation (TI):

TI Name, Phone (C Floor>3 (Depts)) results in a subset of the original table, consisting of the highlighted rows below:

T e Nested Conditional Relation model (NCRs) has tables that are heterogeneous and nested and have generalized version of selection and projection the select a subset of the fields.

1.2 Conditional Relations

A heterogeneous collection, or conditional relation is a relation which may have rows of different types. The Dept and Persons persons tables below are of the traditional relational model.

Persons

A heterogeneous table consisting of departments and persons is obtained by interleaving the rows of the Depts and Persons tables as shown below: DepartmentsPersons

Dept: Dept: Pers: Pers: Dept: Dept: Pers: Dept: Dept:

Dept:

The Departments rows have four fields and the Persons rows have only three fields. To represent such a table, a tag is added to each row. The value of the tag can be either Dept or Pers. Each row has a structure that depends on this tag. The type of such a row is called a tagged union type, and is denoted as:

<Dept: [Name : string, DD : string, Phone : int, Floor : int] | Pers: [SSN : int, Name: string, Salary: int]> A value of this type is either a record of type [Name : string, ID : string, Phone : int, Floor : int] preceded by the tag Dept, or a record of type [SSN : int, Name: string, Salary: int] preceded by a tag Pers.

The type of the entire table DepartmentsPersons is a set of a tagged union type:

DepartmentsPersons: {<Dept: [Name : string, ID : string, Phone : int, Floor : int] |

Pers: [SSN : int, Name: string, Salary: int]>}

The following selection operator selects rows with all departments above the 3^rd floor: σ <Dept(Fioor>3)> (DepartmentsPersons)

The "Dept" tag in the condition (i.e., "Dept/(Floor>3)") indicates to select rows with a Dept tag. The "FIoor>3 " indicates to select rows that have Floor>3. All rows that do not have the Dept tag are selected intact. Thus, the type of the result is:

{<Dept: [Name : string, ID : string, Phone : int, Floor : int] | Pers: [SSN : int, Name: string, Salary: int]>} The result is the highlighted rows as shown below:

Dept: Dept:

Pers: Pers: Dept: Dept: Pers: Dept: Dept:

Dept:

The following selection operation selects departments above the 3^r floor AND people earning more than 50000: σ <De_P (Fioor>3) i Pers/(Saiary>5oooo)> (DepartmentsPersons)

The condition applies to both Dept rows and Pers rows. For Dept rows, the condition specifies Floor>3; for Pers rows, the condition specifies Salary>50000. The result consists of the highlighted rows below:

DepartmentsPersons

Dept: Dept: Pers: Pers: Dept: Dept: Pers: Dept:

Dept:

Dept:

The same result can be achieved by applying the two selections in sequence, that is: σ <Dept (Fioor>3) | Pers/(Saiary>50Qoo)> (DepartmentsPersons) = ⁼ σ <Dept/(Fioor>3)> (o^"<p_ers/(Saiary>₅oooo)> (DepartmentsPersons)) = σ<pers/(Saiary>5oooo)> (o^"<De_Pt/(Fioor>3)> (DepartmentsPersons))

The following projection operation projects out the Name and Phone fields for the Dept rows and the Name field for the Pers rows:

TI <Dept:[Name,Phone] | Pers:[Name]> (σ <Dep</(Floor>3) | Pers/(Salary>₅0000)> (DepartmentsPerSOns))

The type of the result is:

{<Dept: [Name : string, Phone : int] | Pers: [Name: string]>}

The results consist of the highlighted fields below: DepartmentsPersons

Dept: Dept: Pers: Pers: Dept: Dept:

Pers:

Dept:

Dept:

Dept:

If only a subset of the tags are mentioned in the projection operator, then all rows tagged with the other tags are left unchanged in the result. Thus, the expression above is equivalent to:

TI <Dept:[Name,Phone] | Pers:[Name]> (θ^" <Dept/(Floor>3) | Pers/(Salary>5₀Q00)> (DepartmentsPersons))

= IT <Dept:[Name,Phone]>(π<p_erS:[ ame]>(σ<Dept/(Floor>3)> (σ<Pers/(Salaιy>50₀₀0)>(DepartmentsPerSθns))))

- TI <Dept:|Wame_IPhone]>(σ<Dept/(Floor>3)>(TI<Pers:[Naιne]> (o<p_ers (Salary>₅₀Q00)>(DepartmentsPerSOns))))

1.3 Nested Conditional Relations

The following table illustrates NCRs. The Pers rows have an Assignments field that is a non- atomic field. The field contains a nested condition relation in that its sub-rows can be of type Project or Committee.

DepartmentsPersons

Dept: Dept:

Pers:

Pers:

Dept: Dept:

Pers:

Dept: Dept: Dept:

The type of this table is:

{<Dept: [Name : string, ID : string, Phone : int, Floor : int] | Pers: [SSN : int, Name: string,

Assignments: {<Project: [Name: string, Lang:string] Committee: [Name: string]>}]>} Rows tagged with Dept are flat records. The rows tagged with Pers have an Assignments field with its value as another NCR, with its rows tagged either with Project or with Committee. In this example, there is only one level of nesting, in general, however, there may be arbitrary levels.

The following projection operation select the Name and Phone fields of the Dept rows and the Name field of the Pers rows.

II _<Deptpfame^hone^er_{S |} ame_]> (DepaitmentsPerSOns)

The result is a flat relation of type:

{<Dept: [Name : string, Phone : int] | Pers: [Name: string]>}

Projections and selections can be combined and applied to the inner relations. This is done with combined operator, called Combo, that does both the selection and the projection. A combo operator takes an argument p, called a.p-former, that describes what selections and projections are to be done. The p-former generalizes both the argument in a selection, σ_p, and that in a projection π_p. The following is an example of a p-former: p = <Dept/(Floor>3):[Name,Floor] | Pers/(Name like "S%"):

[Name, Assignments: {<Project/(Lang="C++"): [Name]|

Committee/(Name="Promotions")[Name]>}]>

Then the combo operator is written as:

Σp(DepartmentsPersons) This combo operator applies the selection condition (Floor > 3) and projects on the Name and Floor fields for the Dept rows. The combo operator also selects on the condition (Name like "S%") then projects on Name and Assignments fields on the Pers row. Furthermore, this combo operator processes Assignments recursively as follows. This combo operator applies the selection condition (Lang = "C++") and projects on the Name on Project rows and selects on (Name = "Promotions") and projects on the Name field on Committee rows. The type of the result of this combo operator is:

{<Dept: [Name : string, Floor : int] | Pers: [Name: string, Assignments: {<Project: [Name: string] |

Committee: [Name: string]>}]>}

The results of this combo operator is the highlighted fields below: DepartmentsPersons

Dept: Dept:

Pers:

Pers:

Dept: Dept:

Pers:

Dept: Dept: Dept:

The Combo operator selects a submatrix via a combination of selections (on tags and predicates) and projections (on fields). 2 Operands and Typing

An NCR is strongly typed which will allow type inference and type checking. The basic manipulable types are tables and single-valued attributes. The basic types are primitives or sets which are defined as:

Base or primitive types: b ::= integer | long integer | string | float | decimal [precision.accuracy] | ... Manipulable types: t ::= b Member of base type t ::= {u} Set (table) of tuples from "union type"

Record (i.e., row), variant, and union types are defined as:

Record type: r ::= [a₁: t_la a₂: t₂, ..., a_n: t„] where ai , a₂ ... a_n are distinct labels called attributes, or fields, or just labels.

Variant type: v ::= tag:r where tag is a label, called the tag of the variant type.

Union type: u ::= <vι I V2 I ... I v_n> where vi , v₂ , ... , v_n are variant types having distinct tags.

In the following example, a table is defined that includes both PeopIe(name: String, ssnύnt) rows and Employees(name: String, ssnant, salary: float) rows. The record types for People and Employees are defined as follows: r_Peopie : := [name: String, ssn:int] rempioyees ::= [name: String, ssn:int, salary:float]

The variants are defined as:

Vpeopie : "People: [name: String, ssn:int]

Vempioyees ::= Employees: [name: String, ssnint, salary:float] and the union type is:

Upeo ieEmpioyee ..-<People:[name:String,ssn:infJ |

Employees: [name: String,ssn:int,salary:float]>

The NCR type describing a table consisting of both people and employees is: tpeopi mpioyees :^~ {<People: [name:String, ssn:int] |

Employees: [name:String, ssn:int, salary:float]>}

Operators

A formal algebra using these types is defined using the following general principles. • Each operator should support tables with heterogeneous rows (i.e. , a table of union types).

• A different action can be specified for an operator for each unique row type.

• Operators focus on the common case and a generalized map operator handles complex cases.

2.1 Project

The Project operator returns a different subset of the attributes from each variant type. The project operator, denoted Up, is parameterized by a list, p, of elements of the form tag:[set of attributes]. "P" is called a. projection p-former. It determines both the type of the project operator and its semantics (which columns are being projected out).

Defn. projection p-former:

Given a union type with u = <tag_ : | ... |tag_ra: t_n>, where = [a._x , : t_{x 1} , a₁₂ : t₁₂ , ... , a_lnι : t_ln ] , ... , t_n= [a_ml ^•Λ_BΛ,a._αα:t_tti2t...,si_α. : t- l , a projection p-former with input u is an expression:

where the indexes satisfy:

l≤i„ <i₁₂ <...<i,_Pι ≤n_lχ

l≤i_ql <i_q2 <...<i_qPq <n_lq

The "type" of the projection p-former is: p : : u -» u' where u' is defined as follows. Let i/, j=l, ... ,m be:

^:tii_u'^ah₁₂ ^:tiι₁₂'-"'^aiι_lpι ^:th_lpι]

^■ _g ⁼ l-^aq>,ι ^{• t}qι,ι'^aqι_q2 ^{• t} q2'---'^aq'_Φq ^qiqp, -I

The output type is u' = <tagt: t'ι| ... |tag_m: t'_n>.

Defn. Project Operator:

Let p, u, u' be as above, and let t={u}, t'={u'}. The projection operator is: π_p(t) = f

The project operator only affects tuples with tags that are mentioned in p. Tuples with other tags are simply copied to the output, unaffected. The following table shows the results of applying a project operator. The input table is an EmpsDeptsSites NCR with Emp, Dept, and Site tags. Emp:

Dept:

Emp:

Site:

Emp:

Dept:

Emp:

Emp:

Dept:

Emp:

The following projection operator is applied to the EmpsDeptsSites table:

π<Emp:[name] | Dept: [name,mgr] | Site: [name,city]>(EnipsDeptsSites)

The result is the following table:

The project operator is equivalent to π<Emp:[name] | Dept [name,mgr] >(EmpsDeptsSites). The Site records are included in the result by default. 2.2 Select

The Select operator returns a different subset of records for each variant type. The select operator, denoted σ_p, is parameterized by a list, p, of elements of the form tag/condition. "P" is called a selection p-former. It determines both the type of the select operator and its semantics, hi the following, conditions and expressions are defined.

Defn. Expressions:

Given k record types r_ls ..., r_k, and a type t, an expression e of type t with arguments r_l3 ..., ric, in notation: e : rι x ... x rk - t is defined inductively below. Expressions occur in a context, which is defined to be a sequence of record types, (r^, r₂', ..., r_n'); unless specified, the context is empty, n=0.

1. Attribute: if n = [..., a : t, ...], then $i.a is an expression of type t.

2. Context: if rj- = [..., a : t, ...], then $[j].a is an expression of type t.

3_, Scalar operator: if e_l5 e₂ are two expressions of base types bi, b₂ respectively, then Qι op e₂ is an expression of type b, where op is an operator:

op is one of (+, -, *, /), or a string operator (concat, substr), or any user-defined function on scalar values. NCR expression: if ei, e₂, ... are expressions of types Xι, t₂, ... , then fi^e^, ... ) is an expression of type t, where f is an NCR expression f: ti x t₂ x ... — >• t. When k=l, then only $1 can occur (i.e., not $2, $3, ...) and $l.a is abbreviated with a.

Contexts are not used in selections, but are used later in the combo operator.

Defn. Conditions (predicates) on records:

Given k record types X\, ..., a condition with arguments r_ls ..., r , in notation: c : ϊι x ... x r — > bool is defined inductively as follows:

1. if ei, e₂ are expressions of base types b_l5 b₂ respectively, both with arguments x_\, ..., r , then a oprel e₂ is a condition with arguments r_ls ..., rk, where oprel is < <=, >, >= or string operations such as substr, prefix, suffix, like, etc. 2_ if e is an expression of type (<...| tag:[a₁:t₁,...,a_n:t_n] |...>} and e_l3 ..., e„ are expressions of types ti, ... , t_n respectively, then:

<tag:[aι:eι, ..., a_n:e„]> IN e is condition. 3_ if e is an expression of type {... }, then: exists(e) is a condition. 4. true, false are conditions. 5_^ if cl, c2 are conditions, then so are Ci and c₂, Ci or c₂, not ct.

Defn. Selection p-former:

A selection p-former is an expression:

p = < tag_lι/c₁ | ... | tag_Ik/c_k > and its "type" is:

p::<tagι:rι | ... | tag_ τ^> → < tag : t ... | tag_lk : t_lk >

where i_j,...,i_k e {l,...,n} , and C_j is a condition, c- : r — » bool , forj=l,...,k

Defn. Selection operator:

Let p be a selection p-former. The selection operator is: σ _P({t}) = {t}

The selection condition(s) only apply to tuples with tags mentioned in p. Tuples with other tags are simply copied to the output, unaffected.

When the following select operator is applied to the EmpsDeptsSites table described above

C> <Emp / (sal>₅00Q0) | Dept / (mgr="Betsy") | Site / (true)> (EmpsDeptsSites) the result is:

Emp:

Emp:

Site:

Emp:

Dept:

The same operator can be expressed as:

O^" <Emp /(sal>5₀000) | Dept /(mgr="Betsy") > (EmpsDeptsSites)

The select operator operates only on the top level, in that it decides for each top level record whether to keep it or toss it. However, the condition c can look deep inside the current record (e.g., by using existential/universal quantifiers).

2.3 Rename

The rename operator (p) renames tags and/or attributes. It is a generalization of the rename operator in the relational algebra:

Defn. Renaming p-former:

A renaming p-former is an expression: ρ = <tagι->tagι^,:[aιι→an^l, a₁₂→a₁₂', ... ] | tag2→tag₂':[a₂₁->a₂₁', a 2→a₂₂' > Defn. Renaming operator:

Given a renaming p-former p, the renaming operator is:

This operator renames tagi to tag'i, and renames the fields in the record of type tagi by changing an to an', • • • ; renames tag₂ to tag₂', and so on. All tags and/or labels that are not mentioned are left unchanged. The output type is "isomorphic" to the input type: only the variant and record labels at the top level have changed. The "identity" mapping aj — » a; can be abbreviated to a; in specifying a renaming.

2.4 Extend

The extend operator (ε) adds new fields to a record, each computed by some expression from other fields.

Defn. Extension p-former:

Given union types u = <tagι. ι,...,tag_n:r„> and u' = <tagi.τι',...,tag_n:r_π'>, an extension p-former of type u -> u' is an expression: p = <tagι:[cιι:en, c₁₂:e₁₂, ... ] | ... | tag_n:[Cni:e_nl, c_ώ:e„2, ... ]> where the expressions ey, ... have types ej_j : r; — » ty¹, and n' is a record type obtained by adding the fields [ci^tϋ¹, c_:l_ ...] to n.

Defn. Extension operator:

Given an extension p-former p, the extension operator is: ε_P({t}) = {f}.

The meaning is that new labels cπ, c₁₂, ... are added to the corresponding records, and their values are computed by the exrepssions en, e₁₂, ... The values of all the other labels are copied into the output unchanged. As for the other operators, not all tags need to be mentioned: the missing ones are copied to the output.

2.5 Combo

The Combo operator (Σ) combines Project, Select, Rename, and Extend, and does this to arbitrary nesting levels. The Combo operator is parameterized by an argument that is a deeply structured expression combining arguments of Project (IT), Select (σ), Rename (p), and Extend (ε). Such an expression is called a p-former. Unlike the other operators, combo does not implicitly copy "the other" tags and labels to the output, but deletes them. This allows both copying and deleting. (For convenience, another version of the combo operator may also be defined that copies by default.)

The rules below define a p-former inductively. A p-former, p, has an input type t and an output type , and the p-former is denoted by: p :: t → f The p-former takes an input value of type t and returns either an output value of type t' or "nothing." When used in a Combo operator, ∑_p, the type of the Combo operation is {t} — » {t¹}. Consider a selection in the relational algebra, σ _age<20 , (age<20) is a p-former: it takes a record and returns either the same record (if age<20) or nothing (otherwise).

For each p-former, there is a context (which, recall, is a sequence of record types, (r_ls ... , r_n)). Inner p-formers have a context consisting of all record types of the surrounding records. The top-level p- former has an empty context.

1. The "identity p-former":

_ :: t — > t for every type t returns its input, unchanged

2. The "record p-former":

if pi :: t_;ι → t'i , .... :: t_it → t'_k , are p-formers, {ύ, ..., i_k} c {1, 2, ..., n} and ei: .. ,a_n:t_n] - ti", ... , e_p: [ai :tι, ... ,a_n:t_n] -> t_p" are expressions, then

K^ai_. → ^bι) ^: Pι_>-JA_k → b .- p_t. c, : e₁,...,c_p : e_p] :: r -» r', where r = [ai:ti,...,a_a:t_n] and r' = [bι:tι^,,..., b_k:t_k ^,,c₁:t₁", ..., C_p:t_p"].

The (a — b) : p components rename an attribute a to b, and apply recursively the p- former p on the value of that attribute. The c.e components introduce a new attribute

(c) whose value is computed by the expression e over r. If the context of p is (r_1} ..., r„), then the context for each of p_1; ..., p is (r_1; ..., r_n, r).

The following condition applies to p-formers in Combo operators:

(*) a. ,...,a_ik are distinct and i, ..., e_p are scalar expressions

(The b's and c's are also distinct, which follows implicitly from their use in the type [bi :ti', ... , b :t ',Ci :ti", ... ,c_p:t_p"]). The restriction ensures that every complex value is copied to the output at most once, and new values being produced are scalar: this enables simple, pipeline computation.

Given an input value

. . ., a_n : v_n], the p-formers p_1; ..., p_k first apply to the values vn, ..., Vik respectively. If any of them returns "nothing," then the record p- former returns "nothing." Otherwise, let vi', ..., V_k' be the values returned by the - formers, and let wi, ... , w_p be the values record p-former returns the record [bi:vi'

3. The "variant p-former" is parameterized by a selection condition, c:

if c : r - bool and p : : r -» r' then (tag/c -> tag'):p :: tag:r - tag':r'

The condition c is checked first. If it returns false, then the p-former returns "nothing ¹ Otherwise, it returns whatever p returns, but changes the tag to tag'. The c may use existential/universal quantifiers on the set fields of r.

4. The "union" p-former is:

if ι::vii -> vι', ..., ρ_k :: v;k → v_k' and { , ..., i_k} c { 1, 2, .... n} then <pi | ... | ρ_k> :: <vι | ... |v_n> -» <vι'|... | v '> Given a value of type <vι |... | V > , union p-former checks that it is of one of the variant types v;ι,... ,v;k: if not, it returns "nothing," otherwise it returns whatever the corresponding p-former returns. Neither the variant types Vi , ... , Vk nor the variant types Vi¹,... , V_k' are disjoint: however identical tags in the latter correspond to identical types, i.e., <v_\ \... | V_k are a correctly formed type.

5. The "set" p-former is:

ifp :: u — » u' then {p} :: {u} -> {u'}

Given a set {x_ls ..., x_n}, the set p-former first applies the p-former p to each element in the set. Let yi, ..., yk be all values returned by p (t^'.e., excluding "nothing"), then the set p-former returns {yi, ... , yk}

Defn. Combo operator: ifp is a p-former, p :: t -> f, then:

∑ p ((t}) = ({t^*})

Given a set {x_l3 ..., x_n}, the operator first applies the p-former p to each element in the set. Let yi, ..., yk be all values returned by p (i.e., excluding "nothing"). Then Σ p returns {y_l3 ..., y_k}

The following p-former is defined for the EmpsDeptsSites table: p = <Emp/(sal>50000) →HighEarner:[name- name:J | Dept/(mgr= "Betsy") — >BetsyManages:[name→name:_] | Site/(true) — »Site: [name— »name:^city— >city : J>

The following combo operation is applied to the table:

∑_p (EmpsDeptsSites)

If(a→ a) is abbreviated with a, p:_ with p, and tag/(true) with tag, then combo operator can then be abbreviated as:

∑<Emp/(sal>₅QQ0O)- HιghEarner:[nanιe] | Dept (mgι= "Betsy")→Bets Manages:[name] | Sitc:[name,city]> (EmpsDeptsSites)

The results of this combo operator is:

HighEarner:

HighEarner:

Site:

HighEarner:

BetsyManages:

This example illustrates the use of contexts. The following relation (base types omitted) is used. Pers :{<a: [name, birthday, projects: {<b:[title, deadline, modules:{<c:[id,date]}]>}]>}

Each person has a set of projects, each project has a set of modules. a. The combo Σ_p2(Pers) returns all persons that work on a project whose deadline is on their birthday. Its definition needs two p-formers: pi = <b/($l.deadline=$[l].birthday):_> p2 = <a/(Exists(Σ_pl($l.projects)):_>

The context expression $[l].birthday in pi, which retrieves the birthday from one level higher up, is used in the "context" of p2, which defines the outer context to be a record of type [name, birthday, project]. b. The combo Σ_p3(Pers) deletes from every person all projects whose deadline is on the person's birthday: p3 = <a:[name:__J birthdays projects: {<b/($l. deadline != $[1] .birthday) :_>}]> c. This illustrates the use of a $[2] context. The combo Σ_p4(Pers) operates as before, but in addition it deletes all modules whose dates are on the person's birthday: p4 = <a:[name:__J birthdays projects: {<b/($l.deadline != $[1] .birthday): [titles deadline:_^, modules:{<c/($l.date != $[2].birthday):_ >}

]>

1. A Projection operator is a particular case of the Combo operator. For example π<Emp:[name] | Dept: [name,mgr] | Site: [name,cit ]>(EmpsDeptsSites) is the Same as ∑<Emp:[name] | Dept: [name,mgr] | Site: [name,city]>(EmpsDeptsSites)

2. A Selection operator is a particular case of the Combo operator. For example

C<Emp /(sal>50₀₀₀) | Dept/(mgr="Betsy") | Site/ (trae)> (EmpsDeptsSites) is the Same aS ∑<Emp / (sal>50000) | Dept / (mgr="Betsy") | Site / (true)> (EmpsDeptsSites)

3. A Renaming operator is a particular case of the Combo operator. For example, given the renaming p-former: p = <Emp— >Employee:[nanιe— >person,ssn,saI,phone— ^contact]] |

Dept- Depart: [name— teammember,loc— »place,mgr] |

Site— >Site:[name,city]> the renaming operator

Pp(EmpsDeptsSites) is the same as

Σ_p(EmpsDeptsSites).

4. The most general form of a Combo operator is like a submatrix selection. There is no copying and no unnesting involved.

5. Combo can be used to homogenize a collection. For example, in EmpsDeptsSites there are three different kinds of records, and all share a name attribute. The following Combo operator extracts all names and constructs a homogeneous collection:

∑<Emp→Res:[name] | Dept→Res: [name] 1 Site→Res: [name]>(EmpsDeptsSites).

The result is of type {<Res:[name:string]>} which is a homogeneous collection. 6. Combo can be used to dispatch records to different types (e.g., transforming a homogeneous collection into a heterogeneous one). The following Combo operator splits Emp's into Regular and HighPaid:

∑<Emp/(sal<100k)→ReguIar:[name, phone] | Emp/(sal>100k)-»HighPaid: [name,phone]>(Emps)

The input has type

{<EmpIoyee:[name, phone, salary]>},

(base types omitted) while the output has type

{<Regular:[name] | HighPaid: [name, phone]>}

In general conditions that are applied to a tag may overlap. For example, employees with saKlOOk may be dispatched to some type, and those with sal>50k to another type. In this case, records that satisfy both conditions will contribute to both outputs.

2.6 The Simple Combo

The semantics of combo is that tags not mentioned in the p-former are dropped from the output. The "simple" combo has a complementary semantics: only tags/labels that are to be modified need be mentioned. By default, all others are copied to the output. Moreover, the "simple" combo only does one single action, possible at some depth in the NCR.

Defn. Simple p-former:

A simple p-former is a p-former that includes only a single selection (i.e., for a single tag), a single projection, renaming of a single tag or label, or an extension with a single new label. Formally, it is defined like a p-former with additional syntactic restrictions that ensure that only one action is performed (only a projection, a selection, a renaming, or an extension). The other parts of the simple p-former, that do the copying, are omitted. The simple p-former is illustrated by the following examples:

<Emp/(saI>=50000)> this simple p-former is a selection and is equivalent to

<Emp/(sal>=50000):_ | Dept:_ | Other:_>, i.e., copy the other tags unchanged. Thus, to get the equivalent "real" p-former, the omitted tags Dept and Other need to be added.

<Dept:[name, projects]> this simple p-former is a projection and is equivalent to

<Emp:_ I Dept:[name:^, projects:_], Other:_>.

<Dept:[projects:{<urgent/(deadline>="10/10/2010")>}]> this is a selection on the inner relation projects, and is equivalent to

<Emp:_ I Dept:[name:_, floor:_, projects: {<normal:_ | urgent/(deadline>="10/10/2010"):_>}] | Other:_>.

Omitted tags (Emp, normal) are added and omitted labels (name, floor) are added too.

<Dept[projects:{<urgent:[name, team]>}]> this is a projection at a deeper level.

Tags and labels are added, except where projection is done:

<Emp:_ I Dept:[name:_, floor:_, projects: {<normal:[name:^ team:_|, urgent:_>}] |

Other: >

Defn. Simple combo:

Given a simple p-former p, the simple combo operator is:

The superscript 0 indicates that the combo is "simple" (i.e., all missing tags and labels in p have to be added). The semantics is defined as follows. Given a p-former, p::t -» t',(simple or not) define the completion of p to be c(p) : t — » t" obtained from p by "completing" the missing tags according to the type t (i.e., c(p) should actually be denoted c(p,t); it can be defined inductively; omitted). The semantics of the simple combo operator is:

Example

Given NCR EmpsDeptsSites from above, the following are simple combos:

∑⁰<Emp/(sai>5θooo):j> (EmpsDeptsSites) is the same as:

∑<Emp/(sal>₅0000)]:_ | Dept:_>(EmpsDeptsSites)

∑°<De_Pt [mgr]> (EmpsDeptsSites) is the same as: ∑oe_ptt_∞gr_{] ]} Em_p^> (EmpsDeptsSites)

The simple combos are strictly less powerful than combos, because they do not allow deletion of tags from the output type. For example, consider the combo Σ_<Dept:[m_gr]>(EmpsDeptsSites). Its output type is {<Dept: [mgr]>}. It may be expressed as a sequence of simple combos:

∑ <Emp/false:_> (∑ <Dept:[mgr]> (EmpsDeptsSites)) where the second simple combo is needed to eliminate all Emp records. But, the output type of this expression is {<Dept:[mgr] | Emp:[name, ssn, sal, phone]>}. A type checker could be modified to recognize that some conditions are (equivalent to) false and eliminate the corresponding tag from the output type. If so, combo operators could be expressed as a sequence of simple combo operators.

2.7 Match

The match operator, Ωm, generalizes the combo operator by relaxing some of its restrictions. The match operator is parameterized by an m-former, m, that is defined inductively like a p-former in Combo, with two generalizations:

1. In the record m-former:

[(a_ll → b₁) : p_lι ,...,(a_lk → b ^,^ : e„...,c_p : e_p] :: [ai:ti,...,a„:t„] - [bι:tι',..., b_k:t_k',ci:t ", ..., c_p:t_p"] the labels an, ... , a,k are not required to be distinct, and the expressions e_l3 ... , e_p are not restricted to be scalars. (That is the restriction (*) is dropped.) This allows data values to be copied.

Example: Ω<p_erS:[Name→Namei,Phone,Name→Name2]>, copies the Name value calling it Namel and Name2. Such copying can be expensive, when the value being copied is a large sub-relation. This is unlike Combo where no copying is done. 2. There exists an "unnest" m-former, with no corresponding p-former: if m : : u — > u' then unnest(m) :: {u} — u'

(This is different from the set p-former, {p} : {u} -» {u'}). Unnest flattens an inner relation. When two or more unnest m-formers are used in a record m-former, their result consists of a Cartesian product: again, this can be expensive.

Whenever an unnest m-former is used inside another m-former m : : t -» t', the result type t' is not a legal type any more, but an "extended" type, t' can be converted back into a legal type using a mapping norm.

Defn. Match operator: if m : : t — > t' is a m-former, where t is a type and t¹ an extended type. Then:

Ω_m({t}) = {norm(t')} Example:

Ω<τ:[A:unnesto, B:unnestQ]> (^T^ A:{<Ti:[a:tϋ,b:ti2] | T₂:[c:t ₁₃, d:t₁₄]>},

B:{<T₃:[e:t₂₁, f:t₂₂] | T₄:[g:t 23, h:t₂₄]>}]

>}) = {<T:[ A:<Tι:[a:tn,b:tι₂] | T₂:[c:t ₁₃, d:t₁₄]>,

B:<T₃:[e:t₂ι, f:t₂₂] | T₄:[g:t 23, h:t₂ ]>] >})

The only change in the output type is that some braces { ... } have been erased. The output type is technically illegal in the type system, because each record field needs to be either atomic or a set. It can be normalized by pulling out all variant types to the top level and flattening the records. The normalized type is defined to be the output type of Ω. We call norm the normalization operation. The following example illustrates the normalization operation: norm <T:[ A:<Ti:[a:tii,b:ti₂] | T₂:[c:t ι₃, d:t₁₄]>, B: T₃:[e:t₂₁, f:t₂₂] | T₄:[g:t ₁₃, h:t₁₄]>]>

= <TT₁T₃:[Aa:t₁ι,Ab:t₁₂Be:t2i,Bf:t₂2] | TT₁T₄:[Aa:t₁i,Ab:ti₂Bg:t2₃,Bh:t2₄] | TT₂T₃:[Ac:t₁₃,Ad:t₁₄Be:t₂₁,Bf:t₂₂] | TT₂T₄:[Ac:t₁₃,Ad:t₁₄Bg:t₂₃,Bh:t₂₄] >

The norm operation constructs new tag names and new field names by concatenating existing names.

Definition of norm. The symbol "®" is used to denote the following operation between record and/or union types:

[aπ:tn, a₁₂:t₁₂, ..., a_lm:tι_m] Θ [a₂₁:t₂₁, a₂₂:t₂₂, ..., a_2n:t2n] = [aπ:tπ, a₁₂:t₁₂, ..., a_lm:t_lm, a₂₁:t₂₁, a₂₂:t₂₂, ..., a_2n:t_2n] r ® <tagi : τ | tag₂ : r₂1 ... | tag„:r„> = <tag_t : r ® r_x | tag₂ : r ® r₂1 ... | tag_n:r ® r_n>

<tag₁ : τι I tag₂ : r₂1 ... | tag_n:r_n> ® r = <tagι : ri ® r | tag₂ : r₂ <E> r | ... | tag„:r„ ® r> <tagn : rn I tag₁₂ : r₁₂1 ... | tag_lm:r_lm> ® <tag₂₁ : r₂₁ 1 tag₂2 : r₂₂1 ... | tag₂„:r_2n> = < tagiftagz_j : m ® r_2j | i = 1, m, j = l,n >

In the last line tag concatenation is used. The norm operation concatenates two record types, or, if the two types are unions of m and n record types respectively, then constructs a new union type with mn record types, by considering all pairs of concatenations.

The symbol "Θ" is used to denote the union of two disjoint union types:

<tagn : ru I tag₁₂ : r₁₂1 ... | tag_lm:r_lm> θ <tag₂₁ : r₂₁ 1 tag₂₂ : r ₂1 ... | tag_2n:r_2n> = <tagn : rn I ... I tag_lm:r_lm | tag₂₁ : r₂₁ 1 ... | tag₂„:r₂„>

Two functions rlabel_a and ulabeltag. are defined that add or concatenate a record label a, or a tag to a type: rlabel_a(t) = [a:t] where t is an atomic type or a set type rlabela([aι:tι, a₂:t₂, ..., a„:t„]) = [aaι:tι, aa :t₂, ..., aa„:t„] rlabel_a(<tagι:rι | tag₂:r₂1 ... | tag_n:r„>) =

<tagι:rlabel_a(ri) | tag :rlabel_a(r₂) | ... | tag_n:rlabel_a(r„)> ulabel_tag(r) = <tag:r> where r is a record type

| tag₂:r₂1 ... | tag_n:r_n>) = <tagtagι:rι | tagtag₂:r₂1 ... | tagtag„:r„ >:

For the definition of the function norm, the definition of types ("normal types") is extended to "extended types. " These are precisely the types t' that can occur in m-formers, m :: t->t'.

= b

= {u}

= u /* this is an extended type and "illegal" under the normal definition */

= [a-ι-tι, a₂:t₂, ..., a„:t„]

: = <tagι : ri | tag₂ : r₂1 ... | tag„:r_n>

The norm operation is defined by: norm(b) = b norm({u}) = {norm(u)} norm([ai:ti, a₂:t₂, ..., a_n:t_n]) =

® ... ® rlabelan(norm(t_n)) norm(<tagι:rι j tag₂:r₂1... | tagn.τ_n>) = ulabelta_gl(norm(r i) ) ® ... θ ulabeltagn(norm(r_n) )

The operation norm(t) results in a normal type; and if t is a normal type then norm(t) = t.

2.8 Join

Join takes a tuple from each input table and tests this combination to see if it meets a predicate; if so, it returns a combined tuple as the result. Join allows specification of a different predicate and name and a different output tag for each pair wise combination of types from the two tables. Not all pairs must have a predicate and output tag: those that do not contribute to the join. An "inner" join is described in the following.

Defn. Conditional predicate on two tuples:

The Join and Nest operations need conditions on two records: c : n x η - bool

Defn. Combined tuple:

Join produces a combined tuple as its output. Recall the "® " operator, which returns a new combined tuple. rl ® r2 = [ah: th, al₂:tl₂, ..., al_n: tl_n, a2ι: t2_1; ..., a2_m: t2_J where rl = [ah: th, al₂:tl₂, ..., al„: tl„], r2 = [a2ι: t2ι, ..., a2_m: t2_m]

(There is an implicit condition here that the labels in the two records are disjoint.)

Defn. j -former:

A j -former of type : j :: <tagh:rh | ... | tagl_n:rl„>, < tag2_1:r2i| ... | tag2_m:r2_m> -> <tag', : rl ® r2, I ... I tag* : rl, ® r2, > is an expression of the form: j = <tagl_lι ,tag2_Jι /c, → tag\ | ... | tagl_lp ,tag2_Jp /c_p - tag'_p > where:

1. ii, ...,i_p e {1, ..., n}, ji, ...,j_p e {1, ..., m} such that all pairs (ii i), ..., (ip, j_P) are distinct (hence p <n²), and all tags tag'i, ..., tag'_pare distinct

2. c_k : rl, x r2_j — > bool, for k = 1, ..., p.

Defn. Join operator:

Let j : : tl x t2 — > t be a j-former. Then the join operator is: Xi ({tl}_> {t2}) = {t>

2.8.1.1. LI Example

The following NCRs are used to illustrate a join of two tables:

A join on the two tables can be performed with one join predicate for (Dept x Emp) and another for (Dept x Site):

IX] <Emp,Dept/($l.dept=$2.dname)→EmpLoc | Site,Dept /($l.loc=$₂.sname)→FullDept> (EmpSit^βS, Depts) returns the following NCR:

2.9 Outer Join

An outer join has the same syntax as an inner join, with minor additional restrictions. The semantics differs:

Defn. oj -former:

Let tl = <tagh:rh I • • • I tagl_n:rl_n> and t2 = < tag2ι_:r2ι| ... | tag2_m:r2_m> An oj-former for type: oj:: tl, t2 → tl θ t2 θ

® r2_Λ | ... |tag'_p : rl ® r2_Jp > is an expression of the form: oj = <tagl_IιJag2_Jι/c₁ - tag\ | ... | tagl_lp Jag2_Jp/c_p -> tag'_p > where:

1. ii, ...,i_p 6 {1, ..., n}, j_1; ...,j_p e {1, ..., m} such that all pairs (i } ), ..., (i_P, j_P) are distinct (hence p <mn), and all tags tag'i, ..., tag'_p are distinct

2. c_k : rl, x r2_Jk - bool, for k = 1, ..., p.

3. all tags tag'i, • • • , tag'_p are distinct, and they are also distinct both from tagi i, ... , tagl_n and from tag2_l3 ... , tag2_m

Defn. Outer Join operator:

Let oj :: tl x t2 — » t be an oj-former. The outer-join operator is:

Example

The outer join is illustrated by a data integration scenario. There are two sources of persons' phone numbers where each source has an attribute that tells us the confidence in that piece of information:

Sourcel: {<sourcel:[namel: string, phonel int, conf :real]>} Source2: {<source2:[name2: string, phone2:int, conf2:real]>}

The integration is done in two steps. First, the outer join is computed, then a selection/project that encapsulates the logic of the integration is applied. The first step results in the raw data: rawIntegratedData = Sourcel Cd [X] □ _0j Source2 where the oj-former is:

oj = <sourcel, source2/($l.namel=$2.name2) - integrated >

The type of rawIntegratedData is: rawIntegratedData :{<sourcel:[namel, phonel, confl]

I source2:[name2, phone2, conf2] I integrated: [namel, name2,phonel,phone2,coni ,conf2]>}

That is, it will include all "integrated" records, as well as "dangling tuples" from each source. The second step can now apply an arbitrarily sophisticated integration logic. For example, source 1 may be trusted more than source 2, have some complex rules on how to deal with conflicting information, and drop records where the confidence is too low:

integratedData = Σ_p(rawIntegratedData) where the p-former p encapsulates the integration logic:

p = <sourcel/(confl > 0.1) — certain: [namel -» name, phonel — » phone] I source2(conf2 > 0.5) — > certain: [name2 — > name, phone2 — phone]

I integrated/(confl. > 0.4) -» certain: [namel -» name, phonel — > phone] I integrated/(conf <0.4 and conf2>0.7) -» certain : [namel— name, phone2— »phone]

] integrated/(confl in 0.1..0.4 and conf2 in 0.5..0.7) → uncertain: [namel — name, phonel, phone2] >

The type of integratedData is: integratedData: {<certain: [name, phone], uncertain: [name, phonel, phone2]>} that is, in some cases both phone numbers are kept since the confidence does not favor one over the other.

2.10 Nest

The Nest operator works like a left outer join, but it nests all matching children within the tuple of each parent. Nesting is commonly done in XML-QL subqueries, and any time there is a l:n parent- child hierarchy in the output. Nest has left outer join semantics, rather than inner join semantics, and that it preserves the order of the parent relation. Nest can rename both the tag types for the nested tuples and for the parent tuples.

Defn. n-former.

Given n x m predicates:

C_y = rli x r2_j - bool and n new labels, b_l3 ... , b_n.. An n-former of type: n :: <tagh : rh | ... | tagl„ : rl_n> <tag2ι: r2ι| ... | tag2_m: r2_m> - <tagι:r_! ® [b_1:{u₂}] | ... | tag „ : r„ ® [b_n:{u₂}]> is given by an expression: n = <tagπ : [b; : <tag₂₁/c_ϋ | ... | tag_2m/ci_m>] | i = 1, ..., n>

Defn. Nest operator:

Let n :; u_1; u₂ -> u be an n-former. Then Nest_n: ({ui}, {u₂}) = {u}

Example This example uses same Depts and EmpsSites relations of types:

Depts :{<Dept: [dname, loc]>}

EmpsSites: {<Emp: [ename, ssn, dept] | Site: [sname, city]>} and performs the operation:

NeSt <Dept:[info: <Emp/($l.dname=$2.dept) | Site/($l.loc = $2.sname)>]>(DeptS, EmpsSites)

The result has type:

{<Dept: [dname, loc, info:{<Emp: [ename, ssn, dept] | Site: [sname, city]>}]>} and is depicted below:

2.11 Union

The union of two union types, Ui Θ u₂, is defined to consist of all tags in both types, provided that a tag occurring in both Ui and u₂ has identical types in Ui and u₂.

Defn. Union operator:

U ({<rϋ, r_i2, ..., _rin>}, {<r_jl5 r_j2, ..., r_jm>} = {<r.ι, r_i2, ..., r;n> θ ^• , r_j2, ..., r_jm>} Example

The union operator is illustrated using the Depts and EmpsSites tables from the join example. The operation: U (Depts, EmpsSites) results in the NCR:

Dep I dname | loc | Dept: Dept: Dept: Emp: Emp: Site:

Emp: Emp: Emp: Emp:

The expression A U B, for various types of A and B is illustrated in the following:

1. If A:{<Emp:[name, phone]>}, B:{<Dept:[name, floor]>}, then A U B has type {<Emp:[name, phone] | Dept: [name, floor]>} and denotes their disjoint union (i.e., no duplicates are introduced, and duplicate elimination is not needed).

2. If A:{<Emp:[name, phone] | Mngr:[name, beeper]>} and B:{<Dept:[name, floor] | Mngr:[name, beeper]>} then AUB has type {<Emp:[name, phone] | Mngr:[name, beeper] | Dept: [name, floor]>} and means: take the disjoint union of Emp's from A and Dept's from B, and take the regular union of Mngr's from both A and B. The output type is:

{<Emp:[name, phone] | Mngr:[name, beeper] | Emp [name, phone]>}. We need to do duplicate elimination on Mngr's. If the type of Mngr in B is changed, such that the types of Mngr in A and B do not coincide any more, then A U B is illegal.

3. If both A and B have the same type, say {<Emp:[name, phone] | Mngr: [name, beeper]>}, then A U B is the regular union and the output type is the same.

2.11 Distinct

The distinct operator removes duplicates: distinct: {t} -> {t}

2.12 Aggregates

Aggregate operators are included in the combo operator. The five aggregates in SQL and: count, sum, min, max, avg. Count is treated slightly differently. Let agg be one of sum, min, max, avg, and let b be the base type to which it applies (b can be int, real, or string when agg is min or max, int or real when agg is sum, and real only when agg is avg). Consider a union type: u = <tagi : n I ... I tagn : r„> and let ei : ri — b, ..., e„ : r_n — » b be expressions. Then the following is an expression:

agg<tagi/ei | ... tag_n/e„> : {u} → b

Expressions can be used in combo operators.

Example. The following example uses the NCR:

Products: {<Indigenous:[name, quantity, category], Imported: [n,q,c]>}

Where n,q,c stand also for name, quantity, category. The computation of the total quantities for all categories is performed in three steps: First, all categories are computed:

Cat = distinct(∑ indigenous → Cat [category - c] | Imported → Cat: [_c]>(PrθduCts))

The type is {<Cat:[c]>}. Second, the products are nested by categories:

Groups = Nest <_Cat:[Pr_ods:<In_di_gen_ou_s/($l._c=_$2._cat_egor ₎ | Imported/₍$l.c=S₂._c)>(Cat, Products)

The type is {<Cat:[c,Prods:{<Indigenous:[...], Imported: [...]>}]>}.

Third, the sum of all quantities is computed:

Answer = Σ <Cat:[c, total:sum<Indigenoιιs/quantity | Imported/q>(Prods)]>(GrOUps)

The type is {<Cat:[c, total]>}.

3 NCR-QL

The following illustrates how XML-QL could be mapped into the algebra. However, XML-QL works over XML data, not NCRs, so an NCR version is defined of XML-QL ("NCR-QL"), that works on NCRs.

The analogy between XML-QL and NCR-QL is the following:

XML-QL: where-construct

NCR-QL: from-case-where-construct

3.1 Query 1

EmpsDeptsSites is the relation defined earlier containing tuples about Employees, Departments, and Sites. HOwnersCities is a relation containing tuples about HomeOwners and Cities. The following NCR-QL query performs some join between the two:

From EmpsDeptsSites, HOwnersCities

Case (Emp:[name: $X, ssn: $Y, sal: $Z, phone: $U], HOwner:[lastname:$V, ziρ:$W]) : (Where $X=$N AND $Z > 100000

Construct EHO:[name:$X,ssn:Y,zip$W]) I (Dept: [name: $X, loc: $Y, mgr: $Z], HOwner:[lastname:$N, zip:$W]) :

(Where $Z=$N

Construct DHO:[name:$Z, dept:$X, zip:$W] I (Deρt:[name:$X, loc:$Y, mgr:$Z], City:[cityname:$N, place:$W]):

(Where $Y = $W

Construct DC: [name :$X, city: $N])

All combinations of tags from EmpsDeptsSites (defined above) and HOwnersCities (some other NCR) listed in the Case statement are inspected, and in each case a different output tag is produced. The corresponding algebra expression is:

Σ_p(EmpSites > j Depts) where: j = <Emp,HOwner/(name=lastname AND sal > 100000)→EHO

I Dept,HOwner /(mgr=lastname)— »DHO

I Dept, City / (loc=place) → DC>

p = <EHO:[name,ssn,zip], DHO:[mgr→name, name→dept,zip], DC:[name,cityname— »city]>

3.2 Query 2

This query illustrates patterns over sets and how they are translated into the algebra. It roughly corresponds to the XML-QL pattern:

<products> <product> <name> $n </>

<orders> <order> <date> $d </> </> </product> </products> IN Products where <orders> is a set. The NCR-QL query is more powerful since it handles heterogeneous collections of products and orders.

From Products

Case (SeattleProduct:[name:$n, price:$p, orders:$x]): Construct (From $x Case (order: [customer: $c, date:$d]): Where $p<100

Construct usProductDate: [name:$n,date:$d]) I (ParisProduct: [nome$n,prix:$p,orders:$x]): Construct (From $x Case (euOrder:[countιy:$c,date:$d]): Where $p>35

Construct euProductDate:[name:$n, date:$d] I (usOrder:[city:$c,date:$d]): Construct importProductDate:[name:$n,date:$d])

Here Product has type (base types omitted):

{<SeattleProduct: [name,price,orders: {<order: [customer,date]>}] | ParisProduct: [nome,prix,orders:{<euOrder:[country,date] | importOrder:[city,date]>}]>}

While XML-QL had a single pattern, nested patterns in NCR-QL are needed to match over nested sets. The NCR-QL query is translated into the algebra using a match operator:

Σ_p (Ω_m(Products)) where: m = <SeattleProduct:[name:_, price:^ orders:unnest{<order:[customer:_, date:_J>}] I ParisProduct: [nome:_, prix:_, orders: unnest {<euOrder: [country :_, date:_] I usOrder:[city:_, date:_]>}]>

Note that Ω_m(Products) has type:

{<SeattleProduct: [name,price,orders:<order: [customer,date]>] |

ParisProduct: [nome,prix,orders:<euOrder:[country,date] | importOrder: [city,date]>]>}

Which is the same type as for Products, with inner braces erased. It normalizes to:

{<SeattleProduct.order:[name, price, orders. customer, orders. date] I ParisProduct. euOrder:[nome, prix, orders. country, orders. date] I ParisProduct.importOrder:[orders.city, orders. date] >}

Hence, p is:

<SeattleProduct.order/price<100 - UsProductDate: [name ->• name, orders.date - date] I ParisProduct. euOrder/prix>35 -» EuProductDate: [name -» name, orders.date -» date] I ParisProductimportOrder -> ImportProductDate: [name -» name, orders.date -> date]>

3.3 Query 3

The nesting in NCR-QL is illustrated using subqueries. From Products

Case (product: [id:$x, name:$n, price:$p]: (Where $p<100 Construct ProductWit Orders: [name:$n, orders: From Orders

Case (order: [pid:$y, quantity:$q, date:$d]): Where $x=$y AND $q>5555 Construct order: [date: $d] ] )

The types are (with base types omitted):

Products: {<product:[id, name, price]>} Orders:{<order:[pid,quantity,date]>} The algebra expression equivalent to Query 3 is:

Σ_p(Nest„(Products, Orders)) where n is: n = <product: [orders: <order/id=pid>]> and p is:

~ ProductWithOrders): [name, orders:{<order/quantity>5555:[date]>}]>

Notice that Nest_n(Products, Orders) has type:

Nest_n(Products, Orders) : {<product:[id, name, price, orders: {<order: [pid, quantity, date]>}]>}

3.4 General Form of Queries

A general form of algebra expressions for NCR-QL (and, hence, XML-QL) queries is:

Σ_p(Nest_ni (Joini , Nest-_>(.roin₂, ... Nest-_k-_! (Join_k-ι , Join ) ...))) where:

Joinj = Ω_mll(R„) X_JU Ω_ml2(R₁₂) X_jπ ...

Join_k = ΩπjdCRk!) tXljki ΩπjaCRjώ) [X] |_.jki

4 Algebraic Laws

The following three kinds of algebraic laws are formed:

• Push selections and projections down. Selections and projections are captured by the Combo operator, ∑_p, hence laws are needed that commute Combo with other operators

• Join reordering: associativity, commutativity.

• Join-nest associativity.

4.1 Laws that Push Combo (=Selections and Projections) Down

4.1.1 The Combo-Combo Law

Let p : tl — _., and q : t2 — > t3 be two p-formers. Then:

Σ_q(Σ_p(R)) = Σ_r(R) (the combo-combo law) Here r is a new p-former defined as r=ppcompose(q,p), by induction on q first, and, where needed, by induction on p second.

/* q = identity */ ppcompose(_, p) = p / * q = record p-former */ ppcomposefl b^ -»cι):qι, ..., (b_lfc →c_k):q_k, c_k+1:e₁,c_k+2:e₂,...,c_r:e_r._k], _) = [(b -»cι):qι, ...,

(b_lk ->c ):q_k, c_k+ι:eι,c_k+2'.e2,...,Cr'.Qτ-k] ppcompose^b^ ->cι):qι, ..., (b_lk - c_k):q_k, c_k+1:e₁,Ck₊2:e₂,...,c_r:e_r-_k],[( a_h →bι):pι, ...,

(a_Jm - b_s):p_s, b_s+1:f₁,c_s+2:f2,...,b_m:f_m-_s] =

[(a_Jtι →c₁):ppcompose(q₁, p^ ), ..., (a_{J (} →Ct): ppcompose(q_k, p_lt ),

Ct+ι:f_Il+1__s , ct+2: f_ll+2__{i 5}..., c_k: f_Ik_. , c_k+ι: e_j p , c_k+2: e₂ ° p ,..., c_r: e_r.k ° p ]

/* here (jι, ..., j_m} c {1, 2, ..., n} and {i ..., i_k} c {1, 2, ..., m}, with i₁ < i₂ <...< i_t

p is the second p-former, p = [( a_}ι ->bι):p_l5 ..., (a_Jm -»b_s):p_s, b_s+1:f₁,c_s+2:f2,...,b_m:f_m. s], and e ° p means "apply the p-former p first, then e" and is defined below */ /* q = variant p-former */ ppcompose((tag'/c' — > tag"):q, _) = (tag/c - tag'):q ppcompose((tag'/c' — tag"):q, (tag/c — tag¹): p) = (tag/(c and c'_°p)→ tag'):ppcompose(q,p)

/* here c'-p is defined below and means: apply p first, then check c' */ /* q = union p-former */ ppcompose(<qi | ... | q_k> , _ ) = <qι | ... | q_k> ppcompose(<qi | ... j q_k> , <pι | ... | Pm>) = < ... | ppcompose(q_;, pj) | ... > /* here each qi is paired with all those pj that have an output tag that matches the input tag in qi : according to the definitions there could be one ore more */ /* q = set p-former */ ppcompose({q}, _) = {q} ppcompose({q}, {p}) = {ppcompose(q,p)}

Composition of a p former with an expression, e ° p , and a p-former with a condition, c=p, are defined. In both cases, the expression (e) and the condition (c) have a single record argument. (i.e., only use $1, which, by convention, may be omitted), while p is a record p-former, p = i( - b₁) : p_lι ,...,(a_lk → b_k) : p_k,c, : e„...,c_p : e_p ] .

epcompose($l.bj, p) = $1. a, epcompose($l.Cj, p) = e_j epcompose(e op e', p) = epcompose(e,p) op epcompose(e',p) where p is +, -, *, /, ... epcompose(f(e₁,e2,...), p) = f(epcompose(e_l5p), epcompose(e2,p), ...) cpcompose(e op e', p) = epcompose(e, p) op epcompose(e',p) where op is < >, <=, >=,

<tag:[a₁:epcompose(e₁,p), ..., a_n: epcompose(e_n,p)]> IN epcompose(e,p) cpcompose(exists(e), p) = exists(epcompose(e,p)) cpcompose(true, p) = true, cpcompose(false, p) = false cpcompose(cι and C2, p) = cpcompose(c_1; p) and cpcompose(c₂,p) same for or, not

Example. Let p = <Emp/(sal>50000)→HighEarner:[name-→-richName:_] | Dept/(mgr= "Betsy")^BetsyManages:[name^name:_J | Site/(true)→Site:[name-»name:^city-»city:_J> Define R= Σ_P(EmpsDeptsSites)

Let q = <HghEamer/(like(richName,"%Snιith%")- HighEarner:[richName - name: J |

BetsyManages/true - Betsy [name — » name:_] | Site/(city=" Seattle") — >■ HighEarner: [name — name:_J>

And define R' = Σ_q(R) = Σ_q(Σ_P(EmpsDeptsSites)).

Then R' = Σppcompose(q,p)(EmpsDeptsSites), where: ppcompose(q,p) =

<Emp/(sal>50000 and like(name,"%Smith%")) → HighEarner: [name → name:J |

Dept/(mgr="Betsy" and true) -» Betsy: [name - name: _J |

Site/(true and city=" Seattle") — > HighEarner: [name — » name: _ ]>

4.1.2 Applications of the combo-combo law

1.

h d h i h h b rule. The left hand side becomes:

∑ <Emp/(saI=20000)>(∑ <Dep /(mgr="Smith")>(R)) ^{= =} ∑<Emp/(sal=200₀₀) | Dept:_ | Other:_>(∑<Emp:_ | Dept/(mgr=" Smith") | Other: _>(R))

⁼ ∑<Emp/(sal=2₀₀₀Q) | Dept:/(mgr=" Smith") | Other:_>(R)

The right hand side is similarly:

∑ <Dept/(mgr="Smith")>( ∑ <Emp/(sal=20000)>(R)) ^{= =} ∑<Emp:_ I Dept (mgr="Smith") | Other:_>( ∑<Emp/(sal=2000Q) | Dept:_ | Other:_>(R)) ⁼ ∑<Emp/(saI=20000) | Dept:/(mgr="Smith") | Other:_>(R)

Hence the two are equal.

2. Commuting inner selections.

∑ <Dept:[project:{<urgent/(deadIine>="10/10/2010") >}]>( <Dept:[project:{<urgcnt/(budget<=10000) >}]>(R)) ^{= =} ∑ <Dept:[project:{<urgent/ budget<=10000)>}]>(∑ <Dept:[project:{<urgent/(deadline>="10/l₀/201₀") >}]>(R))

Indeed, take the left hand side and expand the simple combos into combos, then apply the combo-combo law

∑ <Dept:[project:{<urgent (deadline>="10/l₀/2010") >}]>(∑ <Dept:[project:{<urgent/(budget<=100Q0) >}]>(R)) ^{= =} ∑<Emp:_ I Dept:[name:_, mgr:_, project: {<urgent/(deadline>="10/10/2010") | normal:_>}]>(∑<Emp:_ | Dept:[name:_, mgr:_, project:{<urgent/(budget<=10000):_ | normal:_>}]>(.-^-J ) ~

⁼ ∑<Emp:_ I Dept:[name:_, mgr: , project:{<urgenf(deadliπe>="10/l 0/2010" and budget<= 10000) | normal._>}]>

The right hand side is treated similarly and results in the same expression, hence they are equal.

3. Pushing predicates to sources. Consider a selection on the predicate (name like "Smith%"). The source supports some predicates, but not this one. Suppose it supports the predicate (name like "%Smith%"). The predicate is: c = (name like "Smith%") the source predicate is: c_s = (name like "%Smith%") The optimizer knows the following implication: c => c_s which is equivalent to: c = c and c_s Hence it can perform the following optimization:

∑ <Emp/(name like "Smith%")>(R) ⁼ ∑ <Emp/(c)>(R) ~ ∑ <Emp/(c and cs)>(R)

⁼ ∑ <Emp/(o)>( ∑ <Emp/(os)>(R)) ⁼ ∑ <Emp/(name like "Smith%")>( ∑ <Emp/(name like "%Smith%")>(R))

4.1.3 The Combo-Nest Law

Let n :: uι, U2→ u be an n-former and p : : u — • u< be a p-former. Then the following holds:

Σ_p(Nest„(R, S)) = Σ_p3(Nest„< Σ_pl(R), ∑_p2(S))) (generic combo-nest law) where pi, p2, p3 are p-formers and n' is an n-former to be described next. The following can identity holds. But, pi and p2 are chosen to do

1 ... tag_2m:r2m>.

h f h n = <tagϋ : [b; : <tag ₁/c_il | ... | tag_2m/c_im>] | i = 1, ... , n> The p-former p has the form: p ⁼ <pι I ■ ■ • I Pk> where: p_; = tag_ljt / c/ → tagtfq., q' : r_ljt ® [b^ :{u₂}] → bool, i = 1, .., k, where (ji, ..., j_} c {l, 2, ..., n}

Combo-nest law 1 : Assume that, for some i=l, ... ,k, the predicate c;' ignores the nested part (formally: $1. b_j_ does not occur in q'), and that qi is the identity p-former. That is:

Pi = tag^ / ci' -→tag_3i:_

Define: p.- tag^ / c;' → tag_1Jt :_ /* i.e., tag^ replaces tag₃; in pi */ pi = <Pi'> ρ3 = <pι I ... I pi_ι I tagu_ - tag_3i:_ | ρ_i+1 1 ... ρ„>

The combo-nest law is:

Σ_p(Nest_n(R, S)) = ∑_p3(Nest„( Σ_pl(R), S)) (combo-nest law 1) Here n-n and p2 = _. Combo-nest law 2: Assume that, for every i=l, ... ,k, the predicate c;' only depends on its first argument (i.e., it ignores the nested part, b_j ), and that q; leaves all fields unchanged except for b_j where it applies a selection, and that, moreover, that selection is the same for all i=l, ...,k. More precisely: q_i = [....b_Jι →b_Jι :r]

where ... contains only identity p-formers, i.e., a- a:_ for attributes a, and where: r= <tag2i/cii"| ... | tag_2m/c_im"> is a selection that is the same for every i=l, ... ,k. Define:

Pi- tagi_j, ^ci' → tag_Xj_ :_ for i = 1, ..., k

/* i.e., _ replaces qi and tag^ replaces tag₃; in p, */ pi = <pι' I ... | p„'> p2 = r ρ3 = < tagiji → tagi₃ι :_ | ... | tag_ljk → tagi_3k :_ > The combo-nest law is:

Σ_p(Nest„(R, S)) = Σ_p3(Nest_n( Σ_pl(R), ∑_p2(S))) (combo-nest law 2)

4.1.4 The Combo-Join Law

Let j :: ul, u2 → u be a j-former and p::u - u' be a p-former. Then the following holds:

Σ_P(R IX] _j S) = Σ_p3(Σ_pl(R)X]j Σ_p2(S)) (the combo-join law) where pi, p2, p3 are defined below.

Notations:

j = <taglj, tag2j / Cij -» tagij' 1 i = 1, ..., n, j = 1, ..., m > p=

tagi_j" : q i_j I i = 1,...,n, j = 1, ...,m>

Here cy : rn x r_2j -> bool is the join condition, while C_y": rn x r2j — bool is a selection predicate. Not all combinations of tags tag;/ must occur in p, but to simplify notations they are included. Assume that Cy'(x,y) = di_j(x) AND eij(y) AND fij(x,y). That is dij(x) contains all conditions that only inspect only values from the left join operand, ej_j(y) those conditions that only inspect values from the right join operand, while f]j(x,y) contains conditions that inspect values from both operands and cannot be separated. The conditions on the left operand are independent of j, i-e.,: dii(x) = d_i2(x) = ... = di_m(x) = di(x) for i=l , ... , n and similarly: ey(x) = e_2j(x) = ... = e_nj(x) = βj(x) for j=l, ... , m

This can often be achieved, by manipulating boolean conditions, factoring out the common parts and pushing the specific parts into fij(x,y). Then define:

pi = <tagh / di → tagli, ...,tagl„/ d„→ tagl„> p2 = <tag2₁ / ei -> tag2_1; .... tag2_m/ e_m - tag2_m> p3= <tagij'/fij→ tagij" : q ij I i = 1, ... ,n, j = 1, ... ,m>

4.2 Laws that commute joins and nests

4.2.1 Join Associativity and Commutativity

Join commutativity holds:

RIXJ S =

(join commutativity law)

Join associativity: (RlXJji ^s)|X]j2T = R[X]j3 (S ] j4T) Goin associativity law) holds for various choices of the j-formers j3 and j4.

In R^XJ ji S only a subset of all pairs of tags need to have join conditions. To simplify notations, however, each join considers all pairs of tags. Hence: jl = <tagπ, tag_2j / ciij → tag_4ij | i = 1,..., n, j = 1, ..., m> j2 = <tag_4ij, tag_3k / c₂ij_k - tagijk | i = 1,..., n, j = 1, ..., m, k = 1,..., ρ>

The condition c₂ij_k looks at three records: x from R, y from S, and z from T. Decomposing it into two pieces:

C2ij_k(x,y,z) = c_3ijk(x,y,z) AND c _jk(y,z) such that the part inspecting only y and z is the same for all i=l, ... ,n. That is, in order to define c₄j_k(y,z) we inspect each condition C₂rj_k(x,y,z), ..., C_2nj (x,y,z) and factor out what all of them do in common with y and z. Then define:

j4 = <tag₂j, tag_3k / c_4jk → tag_5jk | j=l , ... ,m, k = 1 , ... ,p> j3 = <tagπ, tag₅j_k / c_3ij_k→ tagijk | i=l,...,n, j=l,...,m, k=l,...,p>

4.2.2 The Join-Nest Rule

The following holds:

^R X! Ji (^Nest"i(S, T)) = Nest_n2((RX] _j2 S), T) (join-nest law) provided that jl looks only at the S-component in Nest_nl(S, T). In that case, j2 is "the same" as j 1, except that it does not get to see the nested attribute, which jl did not use anyway. Similarly, n2 is "the same" as nl, except that now it gets to see an R component, which it ignores.

From the above description, it will be appreciated that although the specific embodiments of the technology have been described for purposes of illustration, various modifications may be made without deviating from the scope of the invention. Accordingly, the invention is not limited except by the appended claims.

Claims

CLAIMSI/We claim:

1. A computer-readable medium containing a data structure, the data structure having rows and columns, the data structure comprising: a tag column containing tag values that identify a type of the row for each of the rows of the table, each type identifies the columns of that row; and a column of row that includes a plurality of sub-rows, each sub-row representing data of the column for that row.

2. The computer-readable medium of claim 1 wherein each sub-row has a tag column that identifies a type of the sub-row.

3. The computer-readable medium of claim 2 wherein sub-rows are nested to multiple levels.

4. The computer-readable medium of claim 1 wherein the data structure represents a nested conditional relation.

5. A method in a computer system for generating a sorted outer union of relational tables, the method comprising: providing a plurality of relational tables; for each of the tables, generating a query for the table that results in a column for each of column of each table and that results in a value for each column of each table on a path of joins to the table and null for each column of each table not on a path of joins to the table; executing each of the generated queries to generate results; combing the results of the queries into a single results table; and sorting the results table.

6. The method of claim 5 including wherein in the query is an SQL query.

7. The method of claim 5 wherein the generating of a query includes: for each table in the path, adding the table to a from clause; adding a join to a where clause; and adding each column of the table to a select clause.

8. A method in a computer system for executing a query on data collection in diverse formats, the method comprising: providing a mapping of each data collection format to an XML format; receiving a query for a data collection based on the XML format; generating a native query for the data collection from the received query using the provided mapping; requesting execution of the native query to generate data in native format; converting the data in the native format to a nested conditional relation (NCR) format; and applying operators to the data in the NCR format to generate query results in NCR format; and converting the NCR results into an XML format.