SBQL - Examples for the AS0 model

SBQL - Examples for the AS0 Store Model

In Fig.30 we present a relational and an equivalent object-oriented schema that will be used in our examples addressing the AS0 model. The schemata are basically self-explanatory. In the relational schema arrows with dashed lines denote primary-foreign keys dependencies; an arrow is leading from a foreign key to the corresponding primary key. The object-oriented schema is presented in the UML-like syntactic fashion, with two differences. First, cardinalities are put after each defined entity (default cardinality 1..1 is omitted). For instance, cardinalities [0..1] after Address denote that for a particular employee the address can occur or not. Note that such information cannot be expressed in the relational schema. A department can be located in one or more locations (this information also cannot be expressed in the relational schema). Second, we represent AS0 pointer objects associated with particular objects, thus for instance, worksIn pointer objects are close to Emp objects. In UML the worksIn role of the association would be close to the Dept class, just otherwise to our convention. Our examples will address the object-oriented schema. If we will address the relational schema we say that explicitly.

Fig.30. Relational schema and an equivalent object schema used in examples

E.AS0.1	Get full information on employees.
SBQL:	Emp

In SQL (see the relational schema) and in OQL this query can be formulated as:

SQL: OQL:	select * from Emp
OQL:	Emp

We consider the SQL syntactic sugar unnecessary and contradictory to the tradition of programming languages. “User-friendliness” of this sugar is a false stereotype, especially for complex nested queries. Note also the semantic difference: the SBQL query returns a bag of references to all Emp objects, while the SQL query returns the entire Emp table. The OQL query returns in this case the set of Emp objects; a reference to an object is not a feature of the ODMG standard. SBQL queries never return objects but references to objects, perhaps within some more complex structures.

E.AS0.2	Get full information on names of employees.
SBQL:	Emp.name

In SQL (see the relational schema) and in OQL this query can be formulated as:

SQL: OQL:

select name from Emp

The SBQL query returns a bag of references to all name subobjects within Emp objects, while the SQL query returns an one-column table with values of the names. The OQL query returns a bag of literals, i.e. strings. OQL does not allow for the query Emp.name; the explanation of this restriction is at least strange. Because the SBQL in this case returns references to name subobjects, it is much more universal, for instance, can be an argument of updating statements. SQL and OQL by definition reject such a possibility.

E.AS0.3	Get all the information on the employee named “Doe”.
SBQL:	Emp where name = “Doe”

In SQL (see the relational schema) and in OQL this query can be formulated as:

SQL: OQL:

select * from Emp where name = “Doe”

The SBQL query returns a bag of references to the Emp objects storing the information about employees named Doe. The references can be consumed by another SBQL operator or by a programming statement based on SBQL, for instance, by the dot operator or by update statement. If the bag consists of exactly one element, it can be automatically coerced (by another operator) to this element. The SQL query returns a table with rows containing the values of Doe tuples. The OQL query returns a bag with Doe objects.

E.AS0.4	Get the salary of “Doe”.
SBQL:	(Emp where name = “Doe”).sal

In SQL (see the relational schema) and in OQL this query can be formulated as:

SQL: OQL:

select sal from Emp where name = “Doe”

The SBQL query returns a bag of references to the sal objects that are within Emp object storing the information about employees named Doe. The SQL query returns one-column table with sal values of Doe tuples. The OQL query returns a bag of literals - values of sal of Doe objects. Due to the fact that SBQL returns references to sal objects rather than their values (as SQL and OQL do) the above query can be used in an updating statement (set Doe’s salary to 5000):

SBQL:

(Emp where name = “Doe”).sal := 5000;

This is equivalent to the SQL updating statement:

SQL:

update Emp set sal = 5000 where name = “Doe”

However, SQL makes no explicit relationships of this statement with the above SQL query, what is violation of the orthogonality principle, resulting in many further disadvantages, in particular implementation effort overhead, lower potential for query optimization, longer user documentation, etc. Note also small (but painful) syntactic fault: the operator = in one SQL statement is used in two meanings (assignment and comparison) what could be an unexpected difficulty for a lot of beginners.

In the following we return to the topic of nesting SBQL queries into imperative statements.

E.AS0.5	Get e#, name and job of employees earning more than 1000.
SBQL:	(Emp where sal = 1000).struct(e#, name, job)
SBQL:	(Emp where sal = 1000).(e#, name, job)

In SQL (see the relational schema) and in OQL this query can be formulated as:

SQL: OQL:

select e#, name, job from Emp where sal = 1000

The SBQL query returns a bag of structures struct{i_e#, i_name, i_job} with references to proper subobjects stored within a corresponding Emp object. Note that the elements of the structure are unnamed (although names can be derived from names of corresponding objects). In general (in contrast to C/C++ and many other languages), for conceptual closure SBQL takes the point of view that structures may contain unnamed elements. References returned by the query are more universal than values returned by SQL and OQL queries, because references can be automatically dereferenced by some operators (as will be shown later), but also can be used within updating statements. This is impossible in SQL and OQL.

E.AS0.6	Get worksIn references for employees named Doe.
SBQL:	(Emp where name = “Doe”).worksIn

For SQL this query is irrelevant. In OQL such a query cannot be expressed. Such a query however makes a sense because it can be a part of more complex queries (see the next one). Such a query makes also a sense because a worksIn pointer needs to be updated. This updating feature is commonly neglected by many proponents of query languages. For instance, to move Doe to the Sales department it is necessary to assign to this pointer the proper reference. In the imperative extension of SBQL such a request can take the form of the following statement:

SBQL:

(Emp where name = “Doe”).worksIn := ref(Dept where dname = “Sales”)

On the left side of this statement we receive a reference to a worksIn pointer. On the right side of the statement we obtain a reference to the Sales department, which has to be assigned to the pointer. Such an assignment can automatically trigger an action on the twin employs pointer, as in the C++ and Java bindings of the ODMG standards.

E.AS0.7	Get all information on departments for employees named Doe.
SBQL:	((Emp where (name = “Doe”)).worksIn).Dept
SBQL:	(Emp where name = “Doe”).worksIn.Dept

The query returns a bag of references to Dept objects for persons named Doe. In SQL (see the relational schema) this query can be formulated as follows (one of several possible ways):

SQL:

select * from Dept where d# in select d# from Emp where name = “Doe”

In OQL this query can be formulated as (one of several possible ways):

OQL:

select d from Emp as e, e.worksIn as d where e.name = “Doe”

Note that the OQL query avoids using the name Dept, which seems to be an advantage: queries are shorter. We consider such a feature as disadvantageous for two reasons:

(1) For updating there is necessity to distinguish a reference to worksIn pointer and a reference to a Dept objects. OQL makes such a distinction impossible, thus it would be difficult if one would like to smoothly extend OQL with updating statements.

(2) SBQL queries are more legible, because they explicitly show where the navigation is finished, i.e. on Dept objects in this case. In OQL the programmer sees only worksIn relationship thus must verify the schema (which could be very large) to be sure what will be the result of the navigation. Hence, the SBQL convention is better from the conceptual modeling point of view.

E.AS0.8	Get names of departments for employees named Doe.
SBQL:	(((Emp where (name = “Doe”)).worksIn).Dept).dname
SBQL:	(Emp where name = “Doe”).worksIn.Dept.dname

The query returns a bag of references to dname objects for persons named Doe. In SQL (see the relational schema) this query can be formulated as follows (one of several possible ways):

SQL:

select dname from Dept where d# in select d# from Emp where name = “Doe”

In OQL this query can be formulated as (one of several possible ways):

OQL:

select e.worksIn.dname from Emp as e where e.name = “Doe”

The example illustrates so-called path expressions, that is, smooth navigation along some path in the database schema graph. In SBA and SBQL path expressions are side-effects of the definition of the binary dot operator (see the first query) and the syntactic convention assuming the evaluation from left to right (see the second SBQL query). Path expressions can be as long as necessary, for instance (get the name of the Doe’s boss):

SBQL:

(Emp where name = “Doe”).worksIn.Dept.boss.Emp.name

Because we do not define path expressions as special syntactic and semantic constructs (as for instance in [Kim89, Kife92, Cri96]) we receive full freedom in combining path expressions with other SBQL operators. We claim therefore that our path expressions have the highest universality from all the proposals that can be found in the literature and in other proposals of query languages. For instance, the following SBQL query specifies names and cities for employees working in the department managed by Kim:

SBQL:

(Dept where (boss.Emp.name) = “Kim”).employs.Emp.

(name, if exists(Address) then Address.city else “No address”)

Other proposals concerning path expressions are usually informal concerning their semantics, or the semantic specification is limited. In particular, OQL allows for path expressions, but only in cases when a sub-query before a dot returns a single element rather than a collection. In other cases OQL requires to use the select...from...where syntax, as in the above OQL query. Such limitations and requirements are of course rejected during the development of SBQL.

E.AS0.9	Get all information on employees earning more than Doe.
SBQL:	Emp where sal > ((Emp where name = “Doe”).sal)

The query illustrates the possibility to nest queries. It contains name sal two times, but due to the stack-based semantics each of them is bound differently: first sal is bound on ENVS having two sections, and the second sal is bound on ENVS having three sections. In SQL (see the relational schema) and OQL this query can be formulated as follows (one of several possible ways):

SQL:

OQL:

select * from Emp where sal > select sal from Emp where name = “Doe”

In our opinion, such SQL/OQL queries will also require some environment stack concept, but it is not explicit in the description of the languages’ semantics.

E.AS0.10	For each employee return all information about the employee and his/her department.
SBQL:	Emp join (worksIn.Dept)

The query uses the dependent join operator. The result is a bag of structures struct{i_Emp, i_Dept}, where i_Emp is a reference to an Emp object and i_Dept is a reference to the Dept object associated with the Emp object by the worksIn pointer. The corresponding OQL query is the following:

OQL:

select struct(employee:e, department:d) from Emp as e, e.worksIn as d

This is not a strict equivalent because in OQL elements of structures must have tags (as employee and department in the above query). Besides that it is not quite clear what such a OQL query will return - semantics of OQL is rather obscure (OQL does not introduce the concept of a reference) and this operator is improperly described as “cartesian product” (no comment). In SBQL such a query can also be issued to the relational structure:

SBQL:	Emp join (Dept where worksIn = d#)
SBQL:	struct(Emp, Dept) where worksIn = d#
SBQL:	(Emp, Dept) where worksIn = d#

The last query has an equivalent in SQL:

SQL:

select * from Emp, Dept where worksIn = d#)

The equivalence is not strict, because the SQL query returns a table of values rather than references. In particular, such SBQL query can be used in updating statements, while the SQL query cannot.

E.AS0.11	For each department get all information together with the average salary of their employees.
SBQL:	Dept join avg(employs.Emp.sal)

The result is a bag of structures struct{i_Dept, average_salary}, where i_Dept is a reference to a Dept object and average_salary is the real number being the average salary within this department. Changes of ENVS states during evaluation of this query are presented in Fig.31. An equivalent OQL query requires the use of the group by operator, but for obscure semantics of this feature we do not risk to express it. A similar SQL query also requires the use of the group by operator:

SQL:

select d.*, avg(e.sal) from Dept as d, Emp as e where d.d# = e.worksIn group by d.d#

The disadvantage of this query is the necessity to use the group by operator, which is non-orthogonal to other operators, implies far context semantic dependencies (is not compositional), is difficult to optimize, causes some semantic reefs and sometimes requires an additional having clause. Note that the SBQL query has 10 lexical tokens, while the equivalent SQL query - 31 tokens. We comment this difference in the next section.

We also show that SBQL query can also address the relational schema, with no necessity to use the group by option:

SBQL:	Dept join avg((Emp where worksIn = d#).sal)
SBQL:	(Dept as d) join avg(((Emp as e) where e.worksIn = d.d#).e.sal)

Fig.31. A view on states of ENVS during the progress in the evaluation of the query Dept join avg(employs.Emp.sal)

This PowerPoint presentation shows all the steps that are necessary to evaluate this query for a tiny database.

E.AS0.12	Get the average number of employees in all the departments.
SBQL:	avg(Dept.count(employs))

For the relational schema:

SBQL:

avg(Dept.count(Emp where d# = worksIn))

In the SQL-89 standard this query can be expressed through an additional view. In SQL-92 this query can be expressed by the group by clause. However, it leads to a well-known semantic reef, which concerns e.g. the case when some department has no employees. Such a department is not taken into account, hence the average will be different from expected. In SBQL such a semantic reef does not occur.

E.AS0.13	For each employee that earns more than 2000 and works in a department located in Paris get name, job, department name and the name of his/her boss.
SBQL:	((Emp join (works_in.Dept)) where sal > 2000 and “Paris” in loc). (name, job, dname, (boss.Emp.name))

In the result we obtain a bag of structures struct{i_empname, i_empjob, i_dname, i_bossname}, where references i_empname, i_empjob, i_dname, i_bossname refer to particular attributes. A similar SQL query is about two times longer:

SQL:

select e.name, e.job, d.dname, b.name

from Emp as e, Dept as d, Emp as b, Location as l

where e.sal > 2000 and l.loc = “Paris”

and e.worksIn = d.d# and d.boss = b.e# and l.d# = d.d#

E.AS0.14	Get departments employing a professional for any job in the company.
SBQL:	Dept where " distinct(Emp.job) as j ($employs.Emp (j = job))

A similar SQL query is very hard to express.

E.AS0.15	Is it true that each department employs an employee earning more than his/her boss?.
SBQL:	"Dept ($employs.Emp (sal > boss.Emp.sal))
SBQL:	"Dept as d ($d.employs.Emp as e (e.sal > d.boss.Emp.sal))

Such a query cannot be expressed in SQL, because SQL does not introduce queries returning boolean values. Moreover, because SQL has no explicit quantifiers, a similar query (e.g. returning 1 for true and 0 for false) requires the use of exists or count functions, what leads to an extremely clumsy statement. In OQL the query can be expressed as follows:

OQL:

for all d in Dept : exists e in select x from d.worksIn as x :

e.sal > select y.sal from d.boss as y

The reader may now evaluate if the select...from... sugar and the obligatory use of auxiliary iteration variables are useful or annoying features. Note that the first SBQL query has 17 lexical elements, while the OQL query - 31. Which of the queries is easier to write and comprehend?

E.AS0.16

Get names and salaries of designers who earn more than their bosses.

We present several styles that can be applied to formulate of this SBQL query. First we present the SQL style (see the relational schema):

SBQL,

SQL style:

((Emp as e, Dept as d, Emp as b)

where e.job = “designer” and e.worksIn = d.d#

and d.boss = b.e# and e.sal > b.sal) . (e.name, e.sal)

A corresponding SQL query is very similar; basically there are only minor syntactic differences:

SQL:

select e.name, e.sal

from Emp as e, Dept as d, Emp as b

where e.job = “designer” and e.worksIn = d.d#

and d.boss = b.e# and e.sal > b.sal

We can also present this query in the OQL style:

SBQL,

OQL style:

(((Emp as e join e.worksIn.Dept as d ) join d.boss.Emp as b)

where e.job = “designer” and e.sal > b.sal) . (e.name, e.sal)

A corresponding OQL query has also only minor syntactic differences:

OQL:

select e.name, e.sal

from Emp as e, e.worksIn.Dept as d , d.boss.Emp as b

where e.job = “designer” and e.sal > b.sal

SBQL allows for more compact form of this query:

SBQL:

(Emp where job = “designer” and sal > (worksIn.Dept.boss.Emp.sal)).

(name, sal)

SBQL allows also for the “domain calculus” style of this query (see the relational schema). This style is sometimes claimed to be more friendly and better to optimize (we do not believe in that). Sometimes OQL is claimed to be based on this style (although this is contradictory to another claim that OQL is similar to SQL, which is based on the “tuple calculus” style) . The style is also the property of QBE and RDQL.

SBQL,

domain calculus style:

((((Emp.(name as en, sal as es, job as ej, workdIn as ed)),

(Dept.(d# as dd, boss as db)),

(Emp.(sal as bs, e# as be)))

where ej = “designer” and ed = dd and db = be and es > bs).

(en, es)

By these examples we would like to show that SBQL allows for many styles of querying on equal rights. The old debates on advantage of one style over another one are totally irrelevant on the ground of SBA and SBQL. Any inferences concerning ability of particular database models to support query languages are attempts to make false stereotypes. Contrary to the Michael Stonebraker’s thesis, all database models have equal rights concerning query languages. There is no advantage of a particular model, including the object-relational model.

In the next section we will present just otherwise thesis: why the object-relational model we consider disadvantageous w.r.t. query languages.

Last modified: December 31, 2007