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.

Texql

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:

Movies [
    movieno     integer,
    title       text,
    director    text,
    Casttab     [
                    cast text,
                ],
    description text,
    released    integer,
    duration    integer,
    ];

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',

     'Hitchcock, Alfred',

     [   

          'Stewart, James',

          'Kelly, Grace',

          'Burr, Raymond'

     ],

     'One of Hitchcock's most stylish

      thrillers with Stewart confined to a

      wheelchair in his apartment and

      discovering a possible murder'

     1954, 112

)

(    2,'Life of Brian',

     'Jones, Terry',

     [

          'Cleese, John',

          'Palin, Michael',

          'Idle, Eric',

          'Chapman, Graham',

          'Jones, Terry',

          'Gilliam, Terry'

     ],

     'Life of Brian is a Monty Python

      religious parable about the story of a

      man whose life parallels Christ',

     1979, 93

)

(    3,'The Man From Snowy River',

     'Miller, George',

     [

          'Douglas, Kirk',

          'Burlinson, Tom',

          'Thornton, Sigrid',

          'Thompson, Jack'

     ],

     'Grand old fashioned western saga based

      on an epic Australian poem'

     1982, 112

)

(    4, 'The Seven Samurai',

     'Kurosawa, Akira',

     [

          'Mifune, Toshiro',

          'Shimura, Takashi',

          'Inaba, Yoshio'

     ],

     'Classic film about life in 16th

      Century Japan where villagers hire

      professional warriors to fend

      off bandits',

     1954, 141

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
from Movies
where duration < 100;

('Life of Brian', 'Jones, Terry')

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. 
Using functional notation select the movie number, title, and duration of movies released in 1954.

Movies[title, duration]
where released = 1954;

(1, 'Rear Window', 112)
(4, 'Seven Samurai', 141)

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.

Text Support

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
from Movies
where description contains 'life';

(1979, 'Life of Brian')
(1954, 'Seven Samurai')

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'.

select title
from Movies
where description contains 'century life';

('Seven Samurai')

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.

select title
from Movies
where description contains '=Life';

('Life of Brian')

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".

select movieno,title
from Movies
where description contains '"old fashioned"';

(3, 'The Man From Snowy River') 

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'.

select movieno,title
from Movies
where description contains '~village';

(4, 'Seven Samurai')

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'.

select title,director
from Movies
where director contains '@milar';

('The Man From Snowy River', Miller, George')

The contains operator incorporates a not facility using the '!' character.

Find the title of movies whose description does not contain the word 'life'.

select title
from Movies
where description contains '!life';

('Rear Window')
('The Man From Snowy River')

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'.

select title
from Movies
where not description contains 'life';

('Rear Window')
('The Man From Snowy River')

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'.

select title
from Movies
where description contains 'wheelchair' or description contains 'python';

('Rear Window')
('Life of Brian')

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.

select title
from Movies
where title contains 's?m*';

('The Seven Samurai')

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.

select title
from Movies
where description contains '"possible murder" stylish
                @weelchare !hospital =One';

('Rear Window')

Aggregate Functions

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.

avg(
        select duration
        from Movies
);

114.50

Texql provides a count function whose argument is any table.

Find the number of movies whose duration is greater than 100 minutes.

count(
            select all
            from Movies
            where duration > 100
);

3

Sorting

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.

order (
            select released, title
            from Movies
        ) on released desc;

(1982, 'The Man From Snowy River')
(1979, 'Life of Brian')
(1954, 'Rear Window')
(1954, 'The Seven Samurai')

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.

distinct (
                select released
                from Movies
               );

(1954)
(1979)
(1982)

Relational Operators

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.

Table Operations

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.

select title
from Movies
where exists (
                            Casttab
                            where cast contains 'james stewart'
                    );

('Rear Window')

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

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.

Rentals [
                rentalno integer,
                movieno integer ref movies,
                customer text,
                hirelength integer
];

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
from Rentals
where hirelength = 3;

('Citizen, John', 3, 'Rear Window')
('Doe, Jane', 3, 'The Seven Samurai')

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.

update Movies
set released = 1982
where title = 'Life of Brian';

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.

insert into
Movies
values
[
        5,'Star Wars',
        'Lucas, George',
        [
            'Hamill, Mark',
            'Ford, Harrison',
            'Fisher, Carrie',
            'Cushing, Peter',
            'Guiness, Alec'
        ],

'Imaginative, elaborate
science-fiction block buster'
1977, 121
];

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.

delete
from Movies
where title = 'Star Wars';