{"id":712,"date":"2016-06-06T21:07:36","date_gmt":"2016-06-07T04:07:36","guid":{"rendered":"https:\/\/database.guide\/?p=712"},"modified":"2016-06-20T00:20:32","modified_gmt":"2016-06-20T07:20:32","slug":"schema-definitions-by-dbms","status":"publish","type":"post","link":"https:\/\/database.guide\/schema-definitions-by-dbms\/","title":{"rendered":"Schema Definitions by DBMS"},"content":{"rendered":"

Different database management systems define schema<\/i> in their own way. This can\u00a0make it difficult for database developers to work out exactly what a schema is<\/a> – especially when switching between different DBMSs.<\/p>\n

This article provides\u00a0definitions used\u00a0by the three leading database systems.<\/p>\n

<\/p>\n

\u00a0MySQL<\/h2>\n

The MySQL<\/a> documentation defines a schema from both a conceptual and a physical standpoint.<\/p>\n

Conceptual<\/h3>\n

Conceptually, a schema is a set of interrelated database objects, such as tables, table columns, data types of the columns, indexes, foreign keys, and so on. These objects are connected through SQL syntax, because the columns make up the tables, the foreign keys refer to tables and columns, and so on. Ideally, they are also connected logically, working together as part of a unified application or flexible framework.\u00a0For example, the information_schema<\/strong><\/span> and performance_schema<\/strong><\/span> databases use “schema” in their names to emphasize the close relationships between the tables and columns they contain.<\/p><\/blockquote>\n

Physical<\/h3>\n

It is then pointed out that, from a physical standpoint, there’s\u00a0no distinction between schemas and databases:<\/p>\n

In MySQL, physically, a schema<\/strong><\/span> is synonymous with a database<\/strong><\/span>. You can substitute the keyword SCHEMA<\/code> instead of DATABASE<\/code> in MySQL SQL syntax, for example using CREATE SCHEMA<\/code> instead of CREATE DATABASE<\/code>.<\/p><\/blockquote>\n

Source:\u00a0“MySQL Glossary”<\/a>. MySQL 5.7 Reference Manual. MySQL. Retrieved 6 June 2016.<\/p>\n

SQL Server<\/h2>\n

Glossary<\/h3>\n

Database Schema<\/dfn><\/p>\n

The names of tables, fields, data types, and primary and foreign keys of a database.<\/p><\/blockquote>\n

Source:\u00a0“Glossary”<\/a>. SQL Server 2016 Technical Documentation. Microsoft Developer Network.\u00a0Retrieved 6 June 2016.<\/p>\n

SQL Server Technical Article<\/h3>\n

This article about database object schemas<\/a>, explicitly\u00a0draws the distinction between a database user and schema (this is in contrast to how Oracle defines schemas – below).<\/p>\n

A schema is a distinct namespace to facilitate the separation, management, and ownership of database objects.<\/p><\/blockquote>\n

and<\/p>\n

An object owned by a database user is no longer tied to that user. The object now belongs to a schema \u2013 a container that can hold many database objects.<\/p><\/blockquote>\n

and<\/p>\n

This separation means objects and schemas can be created before users are added to the database. It also means a user can be dropped without specifically dropping the objects owned by that user.<\/p><\/blockquote>\n

Source:\u00a0“SQL Server Best Practices \u2013 Implementation of Database Object Schemas”<\/a>. Microsoft TechNet article. Published: November 2008. Retrieved 6 June 2016.<\/p>\n

Oracle Database<\/h2>\n

Oracle Database<\/a>‘s schema system is quite different to the other systems. Oracle’s schema is very much tied to the database user.<\/p>\n

A schema<\/span> is a collection of logical structures of data, or schema objects. A schema is owned by a database user and has the same name as that user. Each user owns a single schema.<\/p><\/blockquote>\n

Oracle distinguishes between schema objects<\/i> and nonschema objects<\/i>. So in other words, some database objects cannot be included in a schema.<\/p>\n

Schema Objects<\/h3>\n

In Oracle Database, schema objects<\/i> include the following:<\/p>\n