Hello Claudio,

I know of Schemas in Oracle and after that, MS SQL Server got
them in SQL Server 2005.

Schemas in Oracle work quite differently from MS SQL Server though,
but that's also because Oracle has "the database" differently from SQL
Server or Firebird.

That being said...

> Not sure this is the right time to post the subject, but since I was
> already
> writing a letter when someone mentioned schemas in fb-devel...
>
> Our parser treats "schema" as synonym for "database" for historical
> reasons.
> Not having read the standard regarding schemas but some examples in the
> net,
> I have a rough idea:
>
> - a db is divided into schemas
> - there's a default, implicit schema (that may or may not be mentioned
> explicitly)
> - the catalog (system tables) lives in its own schema (don't know how
> relevant it's, but I found it in other products)

Mostly "system" ;-)

> - a schema can have security (and new privileges are needed regarding the
> right to execute DDL, anyway)

A "user" in the database (not server wide) is automatically a "schema" in
SQL Server. Not sure if this is a requirement or even wanted as such.

> - a schema can change its owner
> - objects can be moved between schemas (what a mess)

Messy.

Different schemas can contain objects with the same name, so you
can have:

schemaname.mytable
schemaname2.mytable

> - relationships should work between objects in different schemas

Yes.

> - dropping a schema gets rid of all objects in it (but cannot be done if
> another schema references some object there, of course)
> - a schema is a logical partition and has nothing to do with object
> placement techniques like "tablespaces"
>
> Now, who sees schemas as an urgent need and why?

In large databases, having schemas as namespaces can be very useful,
especially given the maximum of characters that can be used for
Firebird object names.

With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!

Database questions? Check the forum:
http://www.databasedevelopmentforum.com