Nested Relational Model
Relational database systems are in widespread use in many business applications. Their support for atomic valued data only has meant that they are often best suited to highly structured or transaction processing applications.
The development of the nested relational model emerged from the need to extend the limiting atomic valued approach of the relational model to support more complex objects. These complex objects provide a much more natural object oriented mapping of real world data.
Texpress supports Texql, a nested relational query language. Texql is similar in style to SQL but provides extensions for free text, multi-valued fields and reference attributes. In Texql free text is treated as an atomic type and many operations on that type are supported.
Texql provides full support for English and European languages as well as the multi-byte Chinese and Japanese language encodings supported in Texpress.
To illustrate some of the facilities of Texql it is easiest to consider an example database and sample operations which could be performed.
Assume we have a Movies table as follows:
where we have fields for movie number, title, director's name, a nested table for the names of all cast members, then the release year and the movie duration in minutes. Texql keywords are shown in bold. The following movies have been catalogued:
( 1, 'Rear Window',
'One of Hitchcock's most stylish
thrillers with Stewart confined to a
wheelchair in his apartment and
discovering a possible murder'
( 2,'Life of Brian',
'Life of Brian is a Monty Python
religious parable about the story of a
man whose life parallels Christ',
( 3,'The Man From Snowy River',
'Grand old fashioned western saga based
on an epic Australian poem'
( 4, 'The Seven Samurai',
'Classic film about life in 16th
Century Japan where villagers hire
professional warriors to fend
Texql is a free-format language where white space (spaces, tabs, newlines) separates key words, symbols and identifiers.
Select - From - Where
As in SQL, the basic construct of a query in Texql is a Select - From - Where (SFW) expression. A SFW expression specifies a projection list of attributes (or expressions based on attributes) and a possible selection constraint.
Select the title and director of movies of less than 100 minutes duration.
select title, director
Texql also supports a functional notation for the specification of SFW queries where the from line table is first specified followed by an optional selection in square brackets.
Texql has a recursively defined syntax in order to support nested tables. SFW expressions can contain nested SFW expressions. This allows an expression that returns a table to be used at any place that a table name can be used. This property of Texql, known as orthogonality, supports an incremental approach to the formulation of complex queries and simplifies query formulation in many cases. In Texql it is possible to use a query expression in any of the select, from or where clauses. A SFW expression can also be used to define joins between tables.
Texql supports text as an atomic type and provides a number of text operators. These operators support the searching of text on a word and phrase basis and the transformation of text to case converted forms. Word stemming and phonetic transformations are also supported for the English language. Pattern matching (or wild card) text operators are also provided.
Texql supports the searching of free text using the contains operator to specify the word(s) to be matched.
Select the title and release year of movies whose description contains the word 'life'.
select released, title
If the contains operator specifies more than one word then an and relationship is implied. A match will occur where the field contains at least one occurrence of each word. The words do not need to be specified in any particular order.
Select the title of movies whose description contains the words 'century' and 'life'.
By default character case is ignored. Hence in the previous query the word 'century' matched with 'Century' in the actual description field.
Exact character matching can be indicated by preceding the word with the '=' transformation.
Select the title of movies whose description contains the word 'Life' in that exact character case.
Phrase searches locate a sequence of adjacent words in a specific order. Phrases are specified by enclosing words within double quotes.
Find the movieno and title of movies whose description contains the phrase "old fashioned".
Word stemming is specified using the '~' transformation. For example '~elect' would match 'elect', 'elected', 'election' etc.
Find the movieno and title of movies whose description matches the stem of the word ' village'.
Phonetic searches provide a method of matching words which sound similar and is typically useful for matching variations of person or place names.
Find the title and director of the movie whose director has a name sounding like 'milar'.
The contains operator incorporates a not facility using the '!' character.
Find the title of movies whose description does not contain the word 'life'.
The previous query could also be specified using the not key word (similar to SQL).
Find the title of movies whose description does not contain the word 'life'.
The where clause can use the or key word to test for a match of at least one of a series of conditions.
Find the title of movies whose description contains the word 'wheelchair' or the word 'python'.
Pattern matching facilities provide the ability to match any single character (? or _) or zero or more characters (* or %). Other more sophisticated pattern matching operators are also supported.
Find the title of movies whose title contains a word starting with 's', followed by any other single character, followed by 'm', followed by any characters.
All of the contains operator transformations may be used in any desired combination.
Find the title of movies by querying on description combining some of the many text operators.
Texql incorporates an orthogonal approach to its implementation of functions. In Texql the argument to an aggregate function such as sum, avg, max, min is a SFW expression that returns a vector (i.e. a table with one attribute) of numeric values.
Find the average movie duration.
Texql provides a count function whose argument is any table.
Find the number of movies whose duration is greater than 100 minutes.
Texql provides the order function to sort a table on specified criteria. Multiple sorting attributes may be specified with ascending or descending order.
Display movie titles and release years in descending order on release year.
Like SQL, duplicates are not removed in a Texql query. The function distinct takes a table as an argument and removes duplicates. Two rows are considered to be equivalent if all atomic values are equal and any nested tables contains the same set of rows in the same order.
Find the years in which movies were released removing any duplicates.
The equality (=) and inequality (<>) operators can be used on tables as well as rows and atomic values. The relational operators (<, <=, >, >=) may only be applied to atomic values.
The exists quantifier returns true if the table sub-expression contains at least one row, false otherwise.
Find the titles of movies involving the actor James Stewart.
The subset and superset facilities take two tables of identical structure and return true if all the tuples in one table exist in the other. A number of relational algebra operators such as union, intersect, minus, times and join are provided as abbreviations for SFW expressions. Texql also supports other special operators, nest, unnest, unfold and group to permit restructure of tables. The Texql in operator returns true if the left hand atomic expression is in the right hand table expression. The has operator is identical to in except that the operands are reversed.
Reference attributes represent implicit joins between rows in the table containing the reference and rows in the referenced table. Each of the attributes in the referenced table is directly available, through this implicit join, when querying on the row containing the reference.
Consider a simple movie rentals table which contains a reference to the Movies table.
A query of the Rentals table can refer directly to any attribute of the Movies table. Texql, when required, automatically resolves (using the ref attribute) references to attributes in other tables.
Show the customer name and hire length of movies on hire for 3 days. Also display the movie title.
select customer, hirelength, title
Implicit joins using reference attributes greatly simplify join query specification and execution.
Data Manipulation Language
The update DML statement can be used to modify an existing table (or nested table). The where clause qualifies which rows are to be updated or if there is no where clause, all rows are updated.
Change the 'Life of Brian' movie release year to 1982.
Rows are inserted into a table (or nested table) using the insert data manipulation language (DML) command. Multiple rows can be added with the one insert statement.
Add a new movie to the Movies table.
Rows can be deleted from tables (and nested tables) using the delete DML statement. The where clause qualifies which rows are to be deleted or if there is no where clause, all rows are deleted.
Delete the 'Star Wars' movie.