Re: Joining data tables and system tables

Paul,

  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

You want to be able to ask what entities and relationships are in the model (what are all the tables?). This is metadata, and SQL does provide information_schema views to give you this information, but those aren't really intended for someone who stumbles into a database and asks what planet he is on. They are intended to allow someone to verify that a query is well-formed, or that a proposed entity name is not already in use, and so on.

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.

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

Paul wrote:

>Can I express this using purely relational operators?:
>
>I want to list all relations (tables) in my database, together with
>the total number of tuples (rows) in each relation.
>
>I can get the list of relations from the system catalog.
>I can use an COUNT aggregate to count the numnber of rows in a given
>relation.
>
>But how do I link the two so the DBMS does the hard work of counting
>each table for me? I know how to use procedural code, "dynamic SQL" or
>other tricks for specific DBMSs; I'm more interested from a
>theoretical viewpoint.
>
>I don't think it's possible in SQL.
>Is it possible in general relational algebra?
>Is it possible in Dataphor or other "next generation" RDBMSs?
>
>It would seem quite a reasonable thing to want to do.
>
>Paul.
>
>
Received on Sat Dec 28 2002 - 02:27:42 CET