Re: Joining data tables and system tables

You highlighted the end user perspective. DBA, however, doesn't really care about application schema; his perspective might be:

"what table contains maximum number of rows"?

If there is a statistics table, then:

"how big are discrepancies between estimated and actual number of rows?"

Somewhat more fancy query:

"Give me all pairs of columns such that column A contains more duplicates of each value than column B and there is no foreign key constraint between them".

"Steve Kass" <skass_at_drew.edu> wrote in message news:auiuiu$nmn$1_at_slb0.atl.mindspring.net...
> 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 - 03:28:41 CET