Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Relationship between catalog, schema, user, and database instance

To compare databases of different vendors (Oracle, SQL Server, DB2, MySQL, and PostgreSQL) how can I identify any object uniquely and do I need a catalog? For instance, In Java's DatabaseMetadata I should specify catalog and schema fooPattern at least.

Is it true that catalog is just an abstraction of data storage?

like image 415
ruruskyi Avatar asked Oct 30 '11 00:10

ruruskyi


People also ask

What is difference between catalog and schema?

Catalog name is the database name. Schema name corresponds to the database owner. Catalog name is the database name. Schema name corresponds to the catalog owner, such as dbo.

What is the difference between database schema and user?

A schema is collection of database objects, including logical structures such as tables, views, sequences, stored procedures, synonyms, indexes, clusters, and database links. A user owns a schema. A user and a schema have the same name. The CREATE USER command creates a user.

Is catalog same as database?

A library catalog allows you to search for items owned by a specific library, including books, movies, journals, magazines, music scores, government documents, and more. Library databases allow you to efficiently search for published information such as magazine, journal, and newspaper articles.

What is a catalog in a database?

A database catalog 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.


2 Answers

In Oracle:

  • server instance == database == catalog == all data managed by same execution engine
  • schema == namespace within database, identical to user account
  • user == schema owner == named account, identical to schema, who can connect to database, who owns the schema and use objects possibly in other schemas
  • to identify any object in running server, you need (schema name + object name)

In PostgreSQL:

  • server instance == db cluster == all data managed by same execution engine
  • database == catalog == single database within db cluster, isolated from other databases in same db cluster
  • schema == namespace within database, by default public is used
  • user == named account, who can connect to database, own and use objects in each allowed database separately
  • to identify any object in running server, you need (database name + schema name + object name)

In MySQL:

  • server instance == not identified with catalog, just a set of databases
  • database == schema == catalog == a namespace within the server.
  • user == named account, who can connect to server and use (but can not own - no concept of ownership) objects in one or more databases
  • to identify any object in running server, you need (database name + object name)

In Microsoft SQL Server:

  • server instance == set of managed databases
  • database == namespace qualifier within the server, rarely referred to as catalog
  • schema == owner == namespace within the database, tied to database roles, by default dbo is used
  • user == named account, who can connect to server and use (but can not own - schema works as owner) objects in one or more databases
  • to identify any object in running server, you need (database name + owner + object name)

So I think answer to your questions is:

  1. It depends on implementation, whether catalog name is needed to identify objects. The meaning of catalog, schema and database vary from one implementation to another.

  2. Yes, a catalog is an abstraction of data storage. I think it should be also defined as a self-contained isolated namespace, but not all SQL engines do it.

  3. Database and schema are pretty well defined by all vendors. Catalog is sometimes synonymous to "database" (at least in Oracle and Postgres), sometimes synonymous to "schema", and sometimes synonymous to both. The term catalog also often means metadata collection (aka system tables).

  4. Schema is what programmers should use to organize artifacts in SQL database as it represents a logical namespace with access control layer.

like image 112
filiprem Avatar answered Oct 05 '22 06:10

filiprem


For DB2, schema is used as namespaces. So if you want to uniquely identify an object in a database you would say *schema.object_name*. This is a very handy way to achieve multitenancy. You can have a separate schema for each tenant in your database. This provides for good separation of concerns from both security as well as management aspects. You can have 32K schemas in a single DB2 database.

A catalog in DB2 is simply a collection of system tables that contain metadata about the database. In general, it is considered a bad practice to access catalog objects directly. It is best to use the facilities provided by your API (e.g. JDBC) to explore the catalog and the metadata it contains.

DB2 also has other abstraction layers. You can have multiple instances of DB2 running on the same machine. Each instance can manage 256 separate databases (each with 32K schemas). The number of DB2 instances on a server is limited only by the amount of memory you have available. At one point in time we had 120 instances of DB2 (each with one database and 10 connections) running on Amazon EC2 m1.large. You can also have multiple installs of DB2 on a single server. it is useful when testing a new version you plan to migrate to. I do find it confusing though often forgetting to switch to the right install.

like image 39
Leon Katsnelson Avatar answered Oct 05 '22 06:10

Leon Katsnelson