Relationship between catalog, schema, user, and database instance

by

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.

More Related Contents:

  1. SQL: Repeat a result row multiple times, and number the rows
  2. What resources exist for Database performance-tuning? [closed]
  3. What are the max number of allowable parameters per database provider type?
  4. How to select the nth row in a SQL database table?
  5. How do I decide when to use right joins/left joins or inner joins Or how to determine which table is on which side?
  6. Why do results from a SQL query not come back in the order I expect?
  7. Best practices for SQL varchar column length [closed]
  8. how to cast the hexadecimal to varchar(datetime)?
  9. Why in SQL NULL can’t match with NULL?
  10. SQL Query needed for fetch value from Two identical table but not fully identical table
  11. sql query to modify the selected value from database [closed]
  12. In SQL Server how to get the count of child if both parent and child are available [closed]
  13. Calculate Average of the largest values in multiple columns
  14. Does SQL Server Offer Anything Like MySQL’s ON DUPLICATE KEY UPDATE
  15. Storing JSON in database vs. having a new column for each key
  16. Simulating MySQL’s ORDER BY FIELD() in Postgresql
  17. Oracle Database Link – MySQL Equivalent?
  18. Stored Procedure with optional “WHERE” parameters
  19. MySQL LIMIT clause equivalent for SQL SERVER
  20. Import MySQL database into a SQL Server
  21. Does mysql have the equivalent of Oracle’s “analytic functions”?
  22. update columns values with column of another table based on condition [duplicate]
  23. Use Composite Primary Key as Foreign Key
  24. How to write a (MySQL) “LIMIT” in SQL Server?
  25. Import MySQL dump to PostgreSQL database
  26. sql group by versus distinct
  27. How to import a SQL Server .bak file into MySQL?
  28. Equivalent of MSSQL IDENTITY Column in MySQL
  29. Equivalent of MySQL ON DUPLICATE KEY UPDATE in Sql Server
  30. What happens when I exhaust a bigint generated key? How to handle it?

Leave a Comment