Re: Joining data tables and system tables

Steve Kass <skass_at_drew.edu> wrote in message news:<auiuiu$nmn$1_at_slb0.atl.mindspring.net>...
> The relational model is all about asking questions about data, and
> it's designed for queries against a known model. If the model is
> known, then all the table names are known, and you can ask how many
> rows there are in any table easily
>
> select count(*) as numEmployees
> from Employees

The actual attribute values in the relations are the data, so you could think of the relation and attribute names etc. as metadata, then the system catalogue is metametadata. Then you could shift your conceptual view upwards in some sense so you ignore the original data and consider the relation etc. names as the data and the system catalogue as the metadata.  

> I think it's generally a bad idea when a query returns information from
> tables that are not hard-coded into the query, because it means the
> answer will contain information about things the person asking the
> question doesn't even know exist. What kind of database user would
> that be? In a typical business, a human relations employee who did not
> know that the business had employees, insurance carriers, dependents, and
> so on would not be employeed for long.

I'm really thinking here from a DBA perspective I suppose.

> So if you want to list all the tables and the number of rows in each,
> put the
> table names in explicitly.
>
> select 'Employees' as TableName, (select count(*) from Employees) as
> 'numRows'
> union all
> select 'Dependents', (select count(*) from Dependents)
> union all
> ...
>
> If this doesn't seem right, tell me why it would be the case that someone
> did not know the names of the tables, yet needed to know how many rows
> were in each?
>
> Steve Kass
> Drew University

Following the same logic could you not also argue that the system catalogue relations are not required at all? For example if you wanted a list of all relations in the database you could just do:

select 'foo'
union all
select 'bar'
union all
...

etc.

I don't think there's anything in the system catalogue (at least pertaining to the logical design) that can't be obtained by manually inspecting the database.

I'm wondering if seeing as the system catalogue is kind of redundant anyway in this sense, it should just have an extra column giving the number of rows in each table. Is the number of rows in a relation an attribute of the relation *value* rather than the relation *variable* though? Or is the distinction not relevant here since we could just say it's the number of rows of the particular value held by the relvar at that particular time?

Although I suppose that really we would be jumping down 2 meta levels here if we stored the row count since the system catalogue should just be about the meta level immediately below it (i.e. relations, attributes etc.) and not about the base data.

Is the whole idea of a system catalogue on a sound logical footing? The idea of having sets (relations) that are somehow on different levels is a bit worrying - didn't Russell & Whitehead reject the concept of a "set hierarchy" (did they call this "types" or something?) when they were formalising conventional set theory?

Maybe we're OK in this case though as we're constructing our sets explicitly (as a restriction on some universe) rather than defining them abstractly. I think that the system tables shouldn't contain themselves though for this exact reason (which they seem to do at least in SQL Server). Maybe we need a "system catalogue catalogue" to detail these (etc., etc.)?  

If I had something to link metadata to data I could get a paradox if I define a relation as: "one column with the names of all relations in the database that don't contain their own names as strings of data in one of their tuples" (should that relation just defined list itself or not?).

Paul. Received on Thu Jan 02 2003 - 11:38:37 CET