Catalog and Schema

According to the SQL standard, both the catalog and schema are abstract concepts in a SQL environment and can be understood as a hierarchy in a container or database object namespace, primarily to address naming conflicts. Conceptually, a database system contains multiple catalog, each containing multiple schemas, and each schema contains multiple database objects (tables, views, fields, etc.), in turn a database object must belong to a schema, And the schema must belong to a catalog, so that we can get the fully qualified name of the database object to resolve the problem of naming conflicts; For example, the fully qualified name of a database object table can be represented as: Catalog name. Schema name. Table name. It is also important to note that the SQL standard does not require that the fully qualified name of each database object be unique, like a domain name, and that each IP address can have multiple domain names if you like.

From the point of view of implementation, the various database systems to the catalog and schema support and implementation of a variety of different, specific problems need to refer to specific product specifications, relatively simple and common implementation is to use the database name as the catalog name, using the user name as the schema name, Refer to the following table for details:

Table 1 Common databases

Suppliers	Catalog Support	Schema support
Oracle	Not supported	Oracle User ID
Mysql	Not supported	Database name
MS SQL Server	Database name	The object belongs to the main name, version 2005 has been changed
DB2	When specifying a database object, the catalog section omits	Catalog owner Name
Sybase	Database name	The database belongs to the main name
Informix	Not supported	Don't need
Pointbase	Not supported	Database name

The last thing to note is the word schema, which has a different meaning in the SQL context than it does in the field of data modeling. In SQL environment, schema is a set of related database objects, the name of the schema defines a namespace for that group of objects, and in the field of data modeling, schema (schema) represents the structure of a database described in formal language; The schema< of data modeling is metadata > stored in a table in the corresponding catalog under the SQL environment named Definition_schema>, schema<, It can also be obtained by querying the view under another schema< named Information_schema> in the catalog, which is not described in detail.

In addition, I combine MySQL's official MySQL administrater database management tool to understand the so-called Schema,catalog:

Click on the catalogs, and the following will come out all the database. Thought for a moment, I summed it up like this:

Database: Refers to MySQL (or Oracle, etc.)

Schema: refers to that when I create database CAICECLB, CAICECLB is a schema

Catalog: Refers to all of the database directories, as shown, will be MySQL original (Mysql,infomation_schema) and later a new set of database.

Catalog and Schema