SCQL. SQL. Table

Use SCM

Table is a Relation º

Table Field is an Attribute º

[(Table–Relation–Attribute–Table Field)] <All attributes of tables are table fields and vice versa.>

Primary Key Field is a Primary Key Attribute º

<It’s a table field also.>

Dependent Field is a Dependent Attribute º

<It’s a table field also.>

Table Subfield is a Nested Attribute Field º

<It’s nested to a table field.>

Table Field Element is an Elementary Attribute Field º

<It’s table subfield. All such elementary attribute fields are table field elements.>

Table Field Element is nested to a Table Field ®

<It is nested to that table field which corresponds to an attribute containing the appropriate elementary attribute field.>

SCQL. SQL. Field

Nested Field is used for calculation of a Derived Field –

Nested Field is a Field º

Derived Field is a Field º

Calculated Field is a Field º

<All fields calculated be a SQL function are calculated ones.>

<All fields having several nested fields are to be calculated.> {Calculated field can be based on none of nested fields.}

Copied Field is a Field º

<All fields that are not calculated.>

<Copied field is to have one nested field.>

Base Field is a Field º

<Base field is not to be calculated or derived.> {But it can be nested.}

Base Field is a Table Field Element ®

{Several base fields may be built on one table field element.}

SCQL. SQL. Field. Name

Field has a Name ®

<If the field is base one, the name is a name of the appropriate table field element.>

<If the field is not base and is included to a selection operation, a) its name is the name of nested field plus <field position>, if there is one nested field and the selection operation is not union or minus, b) its name is the name of nested field, if there is one nested field and the selection operation is union or minus, c) its name is <f<field position>>, otherwise.>

Field has a Position ®

<Positions of fields are unique within a SQL selection operation and are between 1 and its fields quantity.>

Field is a Function. Variable º

Field has a Full Name ®

<Full name is ‘<alias of selection operation>.<name>’. >

Field has a Body ®

<If the field is base, it’s table field element name.>

<If the field is not base and is copied, it’s full name of a nested field or, if the nested field pertains to a union or a minus, it’s a body of the nested field.>

<If the field is calculated, it’s ‘(<nested field statement string>)’>

SCQL. SQL. Field. Net

Use Net

Field is a Net Node º

Nested Field is a Child Node º

Derived Field is a Parent Node º

SCQL. SQL. Selection Statement

Selection Statement is a SQL Statement º

Selection Statement has a root Selection Operation ®

Selection Statement has Field Orders ¬

<All fields of statement field orders are to be presented in the root selection operation.>

Field Order is defined for a Field ®

Field Order has an SCQL. Order Type ®

Field Order has Position ®

SCQL. SQL. Selection Operation

Selection Operation is based on Fields ¬

<Fields are to have different names within each selection operation.>

<If a field of a selection operation is derived then its base fields are to be in nested operations of the selection operation.>

Nested Selection Operation is used for calculation of Calculated Selection Operation ®

Nested Selection Operation is a Selection Operation º

Calculated Selection Operation is a Selection Operation º

Selection Operation has a Position ®

Selection Operation has an Alias ®

<Aliases of all selection operations nested to a calculated one are unique and are ‘t<select operation position>’. Alias of root selection operation is ‘t0’.>

SCQL. SQL. Table Selection

Table Selection is a Selection Operation º

<Table selection is not to have nested operations.>

Table Selection is based on a Table ®

<Table selection is to be based on all or part of those table fields which constitute the appropriate table.> {Selected table fields are specified explicitly.}

SCQL. SQL. Transformation

Transformation is a Selection Operation º

<Transformation is to have one nested operation.>

<All transformation result fields that are not calculated by a constant function are to be derived.> {See <If a field is derived then its base fields are in nested operations.> of selection operation.}

{The result is calculated as grouping by grouping fields if necessary, calculating calculated fields by SQL functions if exist. In the simplest case only projection is done.}

Transformation may be defined by SQL Functions ¬

<Each SQL function is to calculate its own field of this transformation. For each calculated field one SQL function calculated it is to be.>

<Each SQL function of a transformation is to be based on fields of a nested operation.>

<SQL non-aggregate functions are to be based on grouping fields (only if they exist).>

<SQL aggregate functions are to be based only on fields not being grouping.>

Grouping Field is a Field º

Transformation may have Grouping Fields ¬

<The transformation has grouping fields only if at least one SQL aggregate function is defined for it. In this case, all fields of the nested operation of the transformation not used in aggregate functions are grouping fields of the transformation.> {Functions can not be grouping since it can be modeled with two subsequent transformations.}

SCQL. SQL. Join Criterion

Selection Operation has Join Criterions ¬

<Only filtered join, union, and minus can have join criterions.>

<SQL union and minus must have join criterions.>

<First and second join fields of one join criterion are to pertain to different selection operations.>

<All first and second join fields are to participate as fields in nested selection operations of the selection operation.>

Join Criterion has a First Join Field ®

Join Criterion has a Second Join Field ®

First Join Field is a Field º

Second Join Field is a Field º

Join Field is a Field º

Selection Operation has Join Fields ¬

<All first and second join fields of join criterions of the selection operation are its join fields.>

SCQL. SQL. Filtered Join

Use Undirected Graph

Filtered Join is a Selection Operation º

<Filtered join is to have at least one nested operation.> {It’s a selection in the simplest case.}

<At least one of nested operations of filtered join is to be not outer one.>

<Two different nested outer operations of one filtered join cannot be used in the same join criterion of it.> {So, each nested outer operation is to be connected with a not outer operation to provide nested operation graph connectivity (see below).}

Selection Operation is a Graph Object º

Nested Operations Graph is an Undirected Graph º

Filtered Join has a Nested Operations Graph ®

<The nested operations graph consists only of links between nested operations for which there is a join criterion based on fields of these operations.>

<Nested operations of a filtered join are to form connected graph.> {But cycles are possible within non-outer operations only: see the next constraint.}

Filtered Join may be defined by SQL Predicate ®

<SQL Predicate is to be based only on fields of the nested operations.>

Outer Selection Operation is a Nested Selection Operation º

<All outer selection operations are to be nested to filtered joins only.>

SCQL. SQL. Union

Union is a Selection Operation º

<It has two nested operations.>

<The result fields are only the following: for each not join field of nested operations there is one result copied field; for each join field of the first operation there is one result copied field.> {The union has only fields each of which is copied from a base field.}

{The result contains all rows of nested operations, a part of which, having the same all join criterions, is chained. Two rows are chained if all their joined fields have the same values by pairs.}

SCQL. SQL. Minus

Minus is a Selection Operation º

<Minus is to have two nested operations.>

<For each field of the first nested operation there is one result copied field in the minus.>

{The result contains only those rows of the first nested operation that have no chained rows in the second nested operation.}

Fully Joined Operation is a Selection Operation º

<Fully joined operation is a minus or union.>

<All nested operations fields of a fully joined operation are joined.>

<Each pair of joined fields of fully joined operation is to be in the same position in nested operations.> {For simplification of statement string generation.}

<Each pair of a first nested operation field of a fully joined operation and a result field copied from the field is to be in the same position.>

SCQL. SQL. Selection Operation. Tree

Use Tree

Selection Operation is a Tree Node º

Calculated Selection Operation is a Parent Node º

Nested Selection Operation is a Child Node º

SCQL. SQL. SQL Function

Use Function

SQL Function is a Function º

<SQL function is not to have a parent function.>

SQL Function calculates a Calculated Field º

<Base fields of the SQL function cannot serve as the calculated one.>

<SQL function calculates a field derived from nested fields being variables of the function, and vice versa (if the function has base variables).>

Field is a Function Variable º

{This connection is necessary for SQL predicate definition as well.}

SQL Function is based on Fields –

<All function variables of any SQL function are fields of it, and vice versa.>

SQL Aggregate Function is a SQL Function º

<It’s a function of aggregate type.>

SQL Arithmetic Function is a SQL Function º

<It’s a function of arithmetic type.>

SQL Term Function is a SQL Function º

<It’s a term function.>

SQL Constant Function is a SQL Function º

<It’s a constant function.>

SCQL. SQL. SQL Predicate

Use Predicate

SQL Predicate is a Predicate º

SQL Predicate is based on Fields –

<All function variables of any SQL predicate are fields.>

SCQL. SQL. Writing

Selection Statement has a Statement String ®

<It’s a statement string of a root selection operation plus “order by” clause.>

Selection Operation has a Statement String ®

<Table selection is written as ‘SELECT DISTINCT <table attributes> FROM <table name>’ if the table fields have no all attributes of a unique key of the table, and ‘SELECT <table attributes> FROM <table name>’ otherwise. If several fields look to one table attribute, the attribute is represented by one instance.>

<Transformation is written as ‘SELECT <transformation fields, including calculated> FROM <nested operation with alias> GROUP BY <grouping fields>’. GROUP BY clause can be omitted if it is not necessary (there are no SQL aggregate functions).>

<Filtered Join is written as ‘SELECT <join fields> FROM <nested operations with aliases> WHERE <join criterions with (+) if necessary, via AND> AND <predicate>’. Predicate and/or join criterions can be omitted if it is not necessary (and WHERE clause may be omitted too).>

<If all nested operations fields are joined, union is written as ‘<first nested operation> UNION <second nested operation>’, otherwise it is written as ‘SELECT <union fields> FROM <first nested operation> FULL OUTER JOIN <second nested operation> WHERE <join criterions>’.>

<If all nested operations fields are joined, minus is written as ‘<first nested operation> MINUS <second nested operation>’, otherwise it is written as ‘<first nested operation> MINUS (SELECT <first nested operation fields> FROM <nested operations> WHERE <join criterions>)’.>

SCQL. SQL. SQL Result

SQL Result is of a Selection Statement ®

SQL Result is calculated in a SQL Session ®

SQL Result can have SQL Rows ¬

SQL Row consists of Values for Fields

<The fields are of root selection operation of the statement.>

<Each field can have only one value within a SQL row.>