Different database management systems define schema in their own way. This can make it difficult for database developers to work out exactly what a schema is – especially when switching between different DBMSs.

This article provides definitions used by the three leading database systems.

 MySQL

The MySQL documentation defines a schema from both a conceptual and a physical standpoint.

Conceptual

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. For example, the information_schema and performance_schema databases use “schema” in their names to emphasize the close relationships between the tables and columns they contain.

Physical

It is then pointed out that, from a physical standpoint, there’s no distinction between schemas and databases:

In MySQL, physically, a schema is synonymous with a database. You can substitute the keyword SCHEMA instead of DATABASE in MySQL SQL syntax, for example using CREATE SCHEMA instead of CREATE DATABASE.

Source: “MySQL Glossary”. MySQL 5.7 Reference Manual. MySQL. Retrieved 6 June 2016.

SQL Server

Glossary

Database Schema

The names of tables, fields, data types, and primary and foreign keys of a database.

Source: “Glossary”. SQL Server 2016 Technical Documentation. Microsoft Developer Network. Retrieved 6 June 2016.

SQL Server Technical Article

This article about database object schemas, explicitly draws the distinction between a database user and schema (this is in contrast to how Oracle defines schemas – below).

A schema is a distinct namespace to facilitate the separation, management, and ownership of database objects.

and

An object owned by a database user is no longer tied to that user. The object now belongs to a schema – a container that can hold many database objects.

and

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.

Source: “SQL Server Best Practices – Implementation of Database Object Schemas”. Microsoft TechNet article. Published: November 2008. Retrieved 6 June 2016.

Oracle Database

Oracle Database‘s schema system is quite different to the other systems. Oracle’s schema is very much tied to the database user.

A schema 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.

Oracle distinguishes between schema objects and nonschema objects. So in other words, some database objects cannot be included in a schema.

Schema Objects

In Oracle Database, schema objects include the following:

• Clusters
• Constraints
• Database links
• Database triggers
• Dimensions
• External procedure libraries
• Index-organized tables
• Indexes
• Indextypes
• Java classes, Java resources, Java sources
• Materialized views
• Materialized view logs
• Mining models
• Object tables
• Object types
• Object views
• Operators
• Packages
• Sequences
• Stored functions, stored procedures
• Synonyms
• Tables
• Views

Nonschema Objects

In Oracle Database, the following objects are nonschema objects:

• Contexts
• Directories
• Editions
• Restore points
• Roles
• Rollback segments
• Tablespaces
• Users

Source: “Database Objects”.  Oracle Database Online Documentation 12c Release 1 (12.1). Oracle Help Center. Retrieved 6 June 2016.