1

From Database System Concepts, by Silberschatz et al:

4.5.7 Schemas, Catalogs, and Environments

Like early file systems, early database systems also had a single name space for all relations. Users had to coordinate to make sure they did not try to use the same name for different relations. Contemporary database systems provide a three-level hierarchy for naming relations. The top level of the hierarchy consists of catalogs, each of which can contain schemas. SQL objects such as relations and views are contained within a schema. (Some database implementations use the term “database" in place of the term catalog.)

In order to perform any actions on a database, a user (or a program) must first connect to the database. The user must provide the user name and usually, a password for verifying the identity of the user. Each user has a default catalog and schema, and the combination is unique to the user. When a user connects to a database system, the default catalog and schema are set up for the connection; this corresponds to the current directory being set to the user’s home directory when the user logs into an operating system.

To identify a relation uniquely, a three-part name may be used, for example, catalog5.univ schema.course We may omit the catalog component, in which case the catalog part of the name is considered to be the default catalog for the connection. Thus if catalog5 is the default catalog, we can use univ schema.course to identify the same relation uniquely.

  1. A relation has a schema, which is the collection of all the attributes of the relation. The "schema" in the above quote seems to correspond to more than one relations. Does "schema" in the above quote mean the same as the schema of a relation?
  2. What is the relation between catalogs and databases? Is the relation between catalogs and databases one-to-one?
  3. What do the catalogs and schemas look like in mysql, postgresql, or SQL Server?

Thanks.

Community
  • 1
  • 1
Tim
  • 1
  • 141
  • 372
  • 590

1 Answers1

5

Your first sentence in # 1 makes no sense.

A table/relation like “person” has attributes/columns like “name”, “phone”, and “email”.

Tables are grouped together in a namespace known as a schema. So a schema such as “warehouse” can have a table named “person” while another schema such as “sales” can also have a table coincidentally named “person”. Each catalog has one or more schema, each schema carrying a name such as “warehouse” and “sales” seen here.

A schema commonly acts a security boundary, besides being a namespace. As far as I know, that is an implementation detail, not required by the SQL standard.

The word “schema” is also commonly used in a different, more casual and general way, to describe the tables & columns design choices made to fit the needs of an application. See first comment by IMSoP below. A schema in the casual sense might involve any number of catalogs, schemas, tables, and columns in the formal SQL Standard sense.

As for # 2, your quotation explains that. “Catalog” and “database” are synonyms. The word “catalog” is used formally by the SQL standard.

For # 3, advanced databases striving to implement the SQL standard typically support all levels defined by the standard: cluster > catalog > schema > table. This includes both Postgres and Microsoft SQL Server.

H2 Database Engine supports separate databases, each being a catalog with schemas, but no cluster grouping the catalogs/databases together.

MySQL is more limited and does not support the full hierarchy, from what I can tell in my limited searching of MySQL documentation.

For more info, see this related Question: What's the difference between a catalog and a schema in a relational database?

Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154
  • 1
    I think the confusion in question 1 is that we often talk about "the database schema" meaning generally "the structure of the database". Thus "the schema of a table" would be "the structure of that table". As you say, this is *not* the same meaning of "schema" as the SQL standard is using to mean "a grouping of tables". – IMSoP Jan 15 '18 at 17:09
  • As for MySQL, my understanding is that MySQL's "databases" act somewhat like "catalogs" and somewhat like "schemas" - you can both select the "current database" with a `USE` statement, and query across databases with `database.object` notation. The `INFORMATION_SCHEMA` views in MySQL place the database name in the `SCHEMA` column, not the `CATALOG` column. – IMSoP Jan 15 '18 at 17:12
  • 1
    @IMSoP Regarding your first comment about “schema”, thanks, I added a note about that in my Answer. As to your second comment about MySQL, that is one of many reasons why I am a [Postgres](https://www.postgresql.org/) fan. ;-) – Basil Bourque Jan 15 '18 at 17:22