Using the catalogue
amado...@netcabo.pt
Relations (Name) with key (Name)
Fields (RelationName,FieldName,FieldType) with key
(RelationName,FieldName)
The catalogue is reflexive i.e.
relation Relations contains { (Relations), (Fields) }
relation Fields contains
{ (Relations, Name, Symbol),
(Fields, RelationName, Symbol),
(Fields, FieldName, Symbol),
(Fields, FieldType, Symbol) }
Suppose a database instance, and a query like
all triplets (field name, value, relation), from any relation, such
that field name is in {Name, Designation}, field type is String, and
field value = "King"
Please confirm that this query is impossible to express in SQL, and
suggest other relational languages where it is possible. Tutorial D,
Alaphor, the original relational calculus or algebra?
Note that what is at stake is the capability of the language to
transfer from field values to relation and field names. RDF has the
corresponding capability in its data model. XQuery?
Thanks a lot,
Marius A. Alves
PhD student, University of Porto
Alexandr Savinov
As far as I understand, this cannot be solved (easily) in traditional
data models where relations and fields are dedicated elements with
special role. This has significant advantages but the price we pay for
them is also very high especially in the case of large systems.
However, I seriously doubt that the solution proposed by RDF can be
qualified as satisfactory. The problem is deeper than it seems to be.
(Although it is better than nothing.)
David Portas
It seems that two relational operators would be sufficient. One to
return the catalogue and one to "evaluate" a union based on some
restriction of a catalogue relvar. A D language assumes an open ended
set of user-defined operators and types so I don't see why this should
be forbidden in Tutorial D for example.
SQL has the catalogue but the "evaluation" part isn't in standard SQL
AFAIK. However, all SQL products that I'm familiar with implement some
form of dynamic SQL as an extension - that should be up to the task.
--
David Portas
SQL Server MVP
--
amado...@netcabo.pt
> > that field name is in {Name, Designation}, field type is String, and
> > field value = "King"
> It seems that two relational operators would be sufficient. One to
> return the catalogue and one to "evaluate" a union based on some
> restriction of a catalogue relvar. A D language assumes an open ended
> set of user-defined operators and types so I don't see why this should
> be forbidden in Tutorial D for example.
>
> SQL has the catalogue but the "evaluation" part isn't in standard SQL
> AFAIK. However, all SQL products that I'm familiar with implement some
> form of dynamic SQL as an extension - that should be up to the task.
I see.
And the easiest way I envisage to endow a traditional language with the
capability is to extend the language with two things
(1) means to create strings by concatenating any part of the result of
any query
(2) an interpreter function
I think many SQLs already have (1), and as for (2) clearly the DBMS has
an SQL interpreter inside so it is just a matter of making it available
in the language (as some programming languages do e.g. Clipper,
Snobol).
Ok, so I guess my question is not one of possible vs. impossible
anymore but of "how well"---which is a very much harder question to
answer altogether, so let me start again:
(a) is this an interesting problem at all?
(b) does it occur in the real world?
Thanks a lot.
--Marius
David Portas
news:1132158320....@z14g2000cwz.googlegroups.com...
*Dynamic* queries certainly occur in the real world. Decision support
systems and searches that have optional parameters are two applications that
often make extensive use of dynamic queries. It is also certain that there
are important problems around the implementation of dynamically coded
operations, their security, reliability and optimization. Security because
user-supplied data that drives dynamic code may allow a user to execute
arbitrary code in the database; Reliability because of the formidable
difficulty of testing dynamic code for correctness; Optimization because
that has to occur to some extent at runtime rather than design-time.
Mikito Harakiri
How about looking into the problem from the other end? Wouldn't having
a single EAV table as RDBMS physical implementation make it easier? The
catalog queries:
select distinct tableName from EAV -- get all the tables
select distinct tableName, columnName from EAV
where tableName = 'Employees' -- get the Employees table schema
select distinct tableName, columnName from EAV
where value = 'King' -- accomodates blind search easily
-- Pivot the data to get the Employee relation:
select
min(case when columnName='NAME' then value else null end) as
name,
min(case when columnName='SAL' then TO_INTEGER(value) else null
end) as sal,
min(case when columnName='HIRED' then TO_DATE(value) else null
end) as hired
from EAV
where tableName = 'Employees'
group by rowId
The tricky part is allowing (composite) indexes and materialized views
on (pivoted) relations.
Wallace Vinicius
> a single EAV table as RDBMS physical implementation make it easier?
I suppose that the EAV table is ugly. I believe generalization would be
better, by exporting the supertype primary key to the subtype or vice-versa.
--
wallace vinicius
Carl Federl
Actually, using SQL to write SQL is fairly common for one time
activities such as DBA maintenance activities or as part of application
development. It is not typically to perform this type of activity
dynamically on a regular basis.
One example, is that instead of typing the names of the columns, the
catalog is accessed and then the output is re-formated with a text
processor.
Another example is for the common maintenance activity to update the
information about the distribution of key values for use by the query
optimizer. One may generate the statements based on the view
"information_schema.tables", which is part of the ISO SQL standard. The
SQL statement to generate the commands is shown below where the plus
sign is operator overload for concatenating and the brackets are used
in case the table name is a resererved word.
select 'update statistics [' + table_schema + '].[' + table_name + ']'
from information_schema.tables
Many vendors provide methods to treat the resulting relation of a SQL
statement as another SQL statement that should be run. An example is
the stored procedure "sp_execresultset: from MS SQL Server, where one
just passes the SQL statement:
exec dbo.sp_execresultset @cmd = 'select ''update statistics ['' +
table_schema + ''].['' + table_name + '']'' from
information_schema.tables where table_type = ''BASE TABLE'''
--------------
Regarding your example for (field name, value, relation), such that
field name is in {Name, Designation}, field type is String, and field
value = "King" .
Actually the field type is not string - that is the physical
implimentation. The logical implimentation (somethimes called a domain
and sometimes a data type) would be more on the order of PersonName.
If the same attribute exists in multiple relations, then I would say
that the schema is not normalized and the physical implimenatation is
being presented instead. Very common is to impliment physical
partitioning of rows by creating seperate tables e.g. Active Employees
as one table and InActive Employees as a different table. Remapping
back to a logical schema can be done by creating a view that unions the
two physical tables, at which point, only the view needs to be operated
upon. At this point, you question regarding searching all relations
becomes moot as there is only one relation to operate upon.
amado...@netcabo.pt
No, I was not thinking about a sloppy shema but something like
relations People (Name, ...) and Titles (Designation, ...) with "King"
being both a person and a title.
Everybody, thanks a lot.
--Marius
Carl Federl
If someone asked for all Persons whose Name was the same as their
Weight, what would be your response ?
On comparing Person.Name to Person.Designation: If "King" = "King" then
is "King" = 'König" or 'König" = "King" ? 'König" is German for
King.
Is comparing Persons.Name to Persons.Designation any different ? It
might be amusing but not logical.
Gene Wirchenko
wrote:
It is logical. It might not be useful, but it might be. Let the
user make that decision.
"I remember thinking that the person had the same surname as his
job title, but I can not remember what that was. You have the data in
the database. Can you find it for me?"
Do you want to have to say no?
Sincerely,
Gene Wirchenko
Jon Heggland
cfe...@yahoo.com says...
> Given: relation Person (Name, Weight, Designation, ... )
>
> If someone asked for all Persons whose Name was the same as their
> Weight, what would be your response ?
That depends on the domains/types of Name and Weight. If they are the
same (not likely), or one can be coerced to the other, no problem. It
depends on the domain definitions. It might be prudent to arrange for
the system to issue a warning for such a comparison, though.
In any case, it should be no problem at all to ask for all Persons whose
string representation of their Name matches the/a string representation
of their Weight.
> On comparing Person.Name to Person.Designation: If "King" = "King" then
> is "King" = 'König" or 'König" = "King" ? 'König" is German for
> King.
Again, it depends on the domains/types and their implementation (though
I think 'König" would be a syntax error---is it correct to mix single
and double quotes like that in German?).
As for "King" being equal to both "King" and "König": your domain(s)
might support values with more than one representations, just like
integer values in most programming languages have two (or more)
different representations, e.g. 13 and 0xD.
> Is comparing Persons.Name to Persons.Designation any different ? It
> might be amusing but not logical.
No difference. And it's logical; it might not be /useful/, though. But
that is for the user to decide.
--
Jon
Gene Wirchenko
<hegg...@idi.ntnu.no> wrote:
[snip]
>In any case, it should be no problem at all to ask for all Persons whose
>string representation of their Name matches the/a string representation
>of their Weight.
"James, you have lost a lot of weight."
[snip]
Sincerely,
Gene Wirchenko