Re: AW: Big 7.1 open items

Hiroshi Inoue writes:

> According to your another posting,your *database* hierarchy is
>         instance -> database -> schema -> object
> like Oracle.
> 
> However SQL92 seems to have another hierarchy:
>         cluster -> catalog -> schema -> object
> and dot notation catalog.schema.object could be used.

FYI:

An "instance" is a "cluster". I don't know where the word instance came
from, the docs sometimes call it "installation" or "site", which is even
worse. I have been using "database cluster" for the latest documentation
work. My dictionary defines a cluster as "a group of things gathered or
occurring closely together", which is what this is. Call it a "data area"
or an "initdb'ed thing", etc.

A "catalog" can be equated with our "database". The method of creating
catalogs is implementation defined, so our CREATE DATABASE command is in
perfect compliance with the standard. We don't support the
catalog.schema.object notation but that notation only makes sense when you
can access more than one catalog at a time. We don't allow that and SQL
doesn't require it. We could allow that notation and throw an error when
the catalog name doesn't match the current database, but that's mere
cosmetic work.

In entry level SQL 92, a "schema" is essentially the same as table
ownership. You can execute the command CREATE SCHEMA AUTHORIZATION
"peter", which means that user "peter" (where he came from is
"implementation-defined") can now create tables under his name. There is
no such thing as a table owner, there's the "containing schema" and its
owner. The tables "peter" creates can then be referenced by the dotted
notation. But it is not correct to equate this with CREATE USER. Even if
there was no schema for "peter" he could still connect and query other
people's tables.

Moving beyond SQL 92 you can also create schemas with a different name
than your user name. This is merely a little more naming flexibility.


-- 
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden