Database catalog and schema

reference ： https://technet.microsoft.com/en-us/library/dd283095(v=sql.100).aspx

The behavior of schemas changed in SQL Server 2005. Schemas are no longer equivalent to database users; each schema is now a distinct namespace that exists independently of the database user who created it. In other words, a schema is simply a container of objects. A schema can be owned by any user, and its ownership is transferable.

1.   Database Schema :

A database schema is a way to logically group objects such astables, views, stored procedures etc.

Think of a schema as a container of objects.

          You can assign auser login permissions to a single schema so that the user can only access theobjects they are authorized to access.

          Schemas can becreated and altered in a database, and users can be granted access to a schema.

          A schema can beowned by any user, and schema ownership is transferable.’

A schema is a collection ofdatabase objects (used by a user.). 
Schema objects are the logicalstructures that directly refer to the database’s data.
A user is a name definedin the database that can connect to and access objects.
Schemas and users help database administratorsmanage database security.

2.    Database catalog :

The databasecatalog of a database instance consists of metadata in which definitions of database objects such as base tables, views (virtual tables), synonyms, value ranges,indexes, users, and user groups are stored

3.   Comparison

1)   In Oracle:

·        server instance ==database == catalog == all data managed by same execution engine

·        schema == namespacewithin database, identical to user account

·        user == schema owner== named account, identical to schema, who can connect to database, who ownsthe schema and use objects possibly in other schemas

·        to identify any objectyou need (schema name + object name)

 

2)   In PostgreSQL:

·        server instance == dbcluster == all data managed by same execution engine

·        database == catalog ==single database within db cluster, isolated from other databases in same dbcluster

·        schema == namespacewithin database

·        user == named account,who can connect to database, own and use objects in database

·        to identify any objectyou need (database name + schema name + object name)

 

3)   In MySQL:

·        server instance == notidentified with catalog, just a set of databases

·        database == schema ==catalog == a namespace within the server.

·        user == named account,who is can connect to server and use (but can not own - no concept of ownership) objects in one ormore databases

·        to identify any objectyou need (database name + object name)

 

Summary:

1.   The meaning of"catalog", "schema" and "database" vary from oneimplementation to another.

2.   a catalog is anabstraction of data storage. it should be also defined as a self-containedisolated namespace, but not all SQL engines do it.

3.   "Catalog" isoften synonymous to "database" (at least in Oracle and Postgres whichboth make a good standard. MySQL and MS SQL take it different).

 

在SQL环境下Catalog和Schema都属于抽象概念，主要用来解决命名冲突问题

一个数据库系统包含多个Catalog，每个Catalog包含多个Schema，每个Schema包含多个数据库对象（表、视图、字段等）

如数据库对象表的全限定名可表示为：Catalog名.Schema名.表名

ＳＱＬ标准并不要求每个数据库对象的完全限定名称是唯一的，就象域名一样，如果喜欢的话，每个ＩＰ地址都可以拥有多个域名

从实现的角度来看，各种数据库系统对Catalog和Schema的支持和实现方式千差万别，比较简单而常用的实现方式是使用数据库名作为Catalog名，使用用户名作为Schema名，具体可参见下表：

供应商	Catalog支持	Schema支持
Oracle	不支持	Oracle User ID
MySQL	不支持	数据库名
MS SQL Server	数据库名	对象属主名，2005版开始有变
DB2	指定数据库对象时，Catalog部分省略	Catalog属主名
Sybase	数据库名	数据库属主名
Informix	不支持	不需要
PointBase	不支持	数据库名