{"id":696,"date":"2016-06-06T08:08:38","date_gmt":"2016-06-06T15:08:38","guid":{"rendered":"https:\/\/database.guide\/?p=696"},"modified":"2023-02-14T12:45:59","modified_gmt":"2023-02-14T02:45:59","slug":"what-is-a-database-schema","status":"publish","type":"post","link":"https:\/\/database.guide\/what-is-a-database-schema\/","title":{"rendered":"What is a Database Schema?"},"content":{"rendered":"<p>In database terms, a\u00a0<dfn>schema<\/dfn>\u00a0(pronounced &#8220;skee-muh&#8221; or &#8220;skee-mah&#8221;) is the organisation and\u00a0structure of a <a href=\"https:\/\/database.guide\/what-is-a-database\/\">database<\/a>. Both\u00a0<i>schemas<\/i>\u00a0and\u00a0<i>schemata<\/i> can be used as plural forms.<\/p>\n<p>A schema contains schema objects, which could be\u00a0<a href=\"https:\/\/database.guide\/what-is-a-table\/\">tables<\/a>,\u00a0<a href=\"https:\/\/database.guide\/what-is-a-column\/\">columns<\/a>, data types, <a href=\"https:\/\/database.guide\/what-is-a-view\/\">views<\/a>, <a href=\"https:\/\/database.guide\/what-is-a-stored-procedure\/\">stored procedures<\/a>, <a href=\"https:\/\/database.guide\/what-is-a-relationship\/\">relationships<\/a>, <a href=\"https:\/\/database.guide\/what-is-a-primary-key\/\">primary keys<\/a>, <a href=\"https:\/\/database.guide\/what-is-a-foreign-key\/\">foreign keys<\/a>, etc.<\/p>\n<p>A database schema can be represented in a visual diagram, which shows the\u00a0database objects and their relationship with each other.<\/p>\n<figure id=\"attachment_697\" aria-describedby=\"caption-attachment-697\" style=\"width: 468px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/database.guide\/wp-content\/uploads\/2016\/06\/MySQL_Schema_Music_Example.png\"><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-697\" src=\"https:\/\/database.guide\/wp-content\/uploads\/2016\/06\/MySQL_Schema_Music_Example.png\" alt=\"Screenshot of a database schema.\" width=\"478\" height=\"274\" srcset=\"https:\/\/database.guide\/wp-content\/uploads\/2016\/06\/MySQL_Schema_Music_Example.png 478w, https:\/\/database.guide\/wp-content\/uploads\/2016\/06\/MySQL_Schema_Music_Example-300x172.png 300w\" sizes=\"auto, (max-width: 478px) 100vw, 478px\" \/><\/a><figcaption id=\"caption-attachment-697\" class=\"wp-caption-text\">A basic schema diagram representing a small three-table database.<\/figcaption><\/figure>\n<p>Above is\u00a0a simple example of a schema diagram. It shows three tables, along with their data types, relationships between the tables, as well as their primary keys and foreign keys.<\/p>\n<p><!--more--><\/p>\n<p>Here is a more complex example of a database schema:<\/p>\n<figure id=\"attachment_706\" aria-describedby=\"caption-attachment-706\" style=\"width: 1410px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/database.guide\/wp-content\/uploads\/2016\/06\/sakila_full_database_schema_diagram.png\"><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-706\" src=\"https:\/\/database.guide\/wp-content\/uploads\/2016\/06\/sakila_full_database_schema_diagram.png\" alt=\"Screenshot of the Sakila Sample Database schema.\" width=\"1420\" height=\"1060\" srcset=\"https:\/\/database.guide\/wp-content\/uploads\/2016\/06\/sakila_full_database_schema_diagram.png 1420w, https:\/\/database.guide\/wp-content\/uploads\/2016\/06\/sakila_full_database_schema_diagram-300x224.png 300w, https:\/\/database.guide\/wp-content\/uploads\/2016\/06\/sakila_full_database_schema_diagram-768x573.png 768w, https:\/\/database.guide\/wp-content\/uploads\/2016\/06\/sakila_full_database_schema_diagram-1024x764.png 1024w, https:\/\/database.guide\/wp-content\/uploads\/2016\/06\/sakila_full_database_schema_diagram-676x505.png 676w\" sizes=\"auto, (max-width: 1420px) 100vw, 1420px\" \/><\/a><figcaption id=\"caption-attachment-706\" class=\"wp-caption-text\">A database schema diagram of the Sakila Sample Database.<\/figcaption><\/figure>\n<p>In this case, the\u00a0schema diagram has been separated into four sections:<\/p>\n<ul>\n<li><strong>Customer Data<\/strong>: Data\u00a0related to the customers, such as their name, address, etc<\/li>\n<li><strong>Business<\/strong>: Data required to run the business, such as staff, store locations, payment details, etc<\/li>\n<li><strong> Inventory<\/strong>: Details on all products. In this case the products are movies, so it contains data such as movie title, its category, the actors, etc.<\/li>\n<li><strong>Views<\/strong>: Special view on data used for appraisals.<\/li>\n<\/ul>\n<p>So by looking at these schema diagrams,\u00a0we could go ahead and create a database. In fact,\u00a0MySQL Workbench allows\u00a0you to <a href=\"https:\/\/database.guide\/how-to-generate-a-script-from-a-diagram-in-mysql-workbench\/\">generate a <code>CREATE TABLE<\/code> script straight\u00a0from the diagram<\/a>. You can then <a href=\"https:\/\/database.guide\/how-to-create-a-database-from-a-script-in-mysql\/\">use the\u00a0script to create a database<\/a>. You can even\u00a0<a href=\"https:\/\/database.guide\/how-to-reverse-engineer-a-database-in-mysql-workbench\/\">reverse engineer a database into a diagram<\/a>.<\/p>\n<h2>Is\u00a0a\u00a0Schema and a Database the Same Thing?<\/h2>\n<p>There&#8217;s a lot of confusion about schemas when it comes to databases. The question often arises whether there&#8217;s a difference between schemas and databases and if so, what is the difference.<\/p>\n<h3>Depends on the Vendor<\/h3>\n<p>Part of the reason for the confusion is that\u00a0database systems\u00a0tend\u00a0to approach schemas in their own way.<\/p>\n<ul>\n<li>The <a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/glossary.html#glos_schema\">MySQL documentation<\/a> states that physically, <q>a schema is synonymous with a database<\/q>. Therefore, a schema and a database are the <strong>same thing<\/strong>.<\/li>\n<li>However, the <a href=\"https:\/\/docs.oracle.com\/database\/121\/SQLRF\/sql_elements007.htm#SQLRF20003\">Oracle Database documentation<\/a> states that certain\u00a0objects can be stored inside a database but not inside a schema. Therefore, a schema and a database are <strong>two different things<\/strong>.<\/li>\n<li>And according to this <a href=\"https:\/\/technet.microsoft.com\/en-us\/library\/dd283095(v=sql.100).aspx\">SQL Server technical article<\/a>, a schema is a separate entity inside the database. So, they are <strong>two different things<\/strong>.<\/li>\n<\/ul>\n<p>So, depending on the RDBMS you use,\u00a0schemas and databases may\u00a0or may\u00a0not be the same thing.<\/p>\n<h3>What about the SQL Standard?<\/h3>\n<p>The <a href=\"http:\/\/www.iso.org\/iso\/iso_catalogue\/catalogue_tc\/catalogue_detail.htm?csnumber=53681\">ISO\/IEC 9075-1 SQL standard<\/a> defines a schema as\u00a0<q>a persistent, named collection of descriptors<\/q>.<\/p>\n<p>If you were confused before, hope I haven&#8217;t just made it worse&#8230;<\/p>\n<h3>Broad Meaning<\/h3>\n<p>Another reason for the confusion\u00a0is probably due to the fact that the term <i>schema<\/i> has such a broad meaning. It has different connotations\u00a0within\u00a0different contexts.<\/p>\n<p>The word schema originates from the Greek word\u00a0<i>skh\u0113ma<\/i>, which means <i>form<\/i>,\u00a0<i>figure<\/i>, <i>shape<\/i>, or <i>plan<\/i>.<\/p>\n<p>Schema is used in psychology to\u00a0describe an organised pattern of thought or behaviour that organises categories of information and the relationships among them.<\/p>\n<p>Before\u00a0designing a\u00a0database, we\u00a0also\u00a0need to look at the\u00a0categories of information and the relationships among them. We need to create a <em>conceptual<\/em> schema before we even start with the <em>physical<\/em> schema within the DBMS.<\/p>\n<p>In software development, when discussing schemas, one could be discussing <em>conceptual<\/em> schemas, <em>physical<\/em> schemas, <em>internal<\/em> schemas, <em>external<\/em> schemas, <em>logical<\/em> schemas, etc\u00a0. Each of these has its\u00a0own specific\u00a0meaning.<\/p>\n<h2>Schema Definitions by\u00a0DBMS<\/h2>\n<p>Here&#8217;s a quick definition of schema from the three leading database systems:<\/p>\n<h3>MySQL<\/h3>\n<blockquote><p>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.<\/p>\n<p>&#8230;.<\/p>\n<p>In MySQL, physically, a <span class=\"bold\"><strong>schema<\/strong><\/span> is synonymous with a <span class=\"bold\"><strong>database<\/strong><\/span>. You can substitute the keyword <code class=\"literal\">SCHEMA<\/code> instead of <code class=\"literal\">DATABASE<\/code> in MySQL SQL syntax, for example using <code class=\"literal\">CREATE SCHEMA<\/code> instead of <code class=\"literal\">CREATE DATABASE<\/code>.<\/p><\/blockquote>\n<p>Source:\u00a0<a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/glossary.html#glos_schema\">&#8220;MySQL Glossary&#8221;<\/a>. MySQL 5.7 Reference Manual. MySQL.\u00a0Retrieved 6 June 2016.<\/p>\n<h4><\/h4>\n<h3>SQL Server<\/h3>\n<blockquote><p><dfn><\/dfn>The names of tables, fields, data types, and primary and foreign keys of a database.<\/p><\/blockquote>\n<p><a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/ms165911.aspx\">&#8220;Glossary&#8221;<\/a>. SQL Server 2016 Technical Documentation. Microsoft Developer Network.\u00a0Retrieved 6 June 2016.<\/p>\n<h3>\u00a0Oracle Database<\/h3>\n<p><a href=\"https:\/\/database.guide\/what-is-oracle-database\/\">Oracle Database<\/a>&#8216;s schema system is quite different to the other systems. Oracle&#8217;s schema is very much tied to the database user.<\/p>\n<blockquote><p>A <span class=\"bold\">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<p>Source:\u00a0<a href=\"https:\/\/docs.oracle.com\/database\/121\/SQLRF\/sql_elements007.htm#SQLRF20003\">&#8220;Database Objects&#8221;<\/a>. \u00a0Oracle Database Online Documentation 12c Release 1 (12.1). Oracle Help Center.\u00a0Retrieved 6 June 2016.<\/p>\n<p>This article on <a href=\"https:\/\/database.guide\/schema-definitions-by-dbms\/\">schema definitions by DBMS<\/a> provides more detail.<\/p>\n<h2>Creating Schemas<\/h2>\n<p>Despite their differences in defining schemas, each of the three aforementioned DBMSs support the <code>CREATE SCHEMA<\/code> statement.<\/p>\n<p>And that&#8217;s where the\u00a0similarity ends.<\/p>\n<h3>MySQL<\/h3>\n<p>In MySQL,\u00a0<a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/create-database.html\"><code>CREATE SCHEMA<\/code><\/a> creates a database.<\/p>\n<p>This is because\u00a0<code>CREATE SCHEMA<\/code>\u00a0is a synonym for\u00a0<code>CREATE\u00a0DATABASE<\/code>. \u00a0In other words, you can use\u00a0<code>CREATE SCHEMA<\/code> or\u00a0<code>CREATE\u00a0DATABASE<\/code> to do the same thing.<\/p>\n<h3>Oracle Database<\/h3>\n<p>In Oracle Database, the <a href=\"https:\/\/docs.oracle.com\/database\/121\/SQLRF\/statements_6016.htm#SQLRF01313\"><code>CREATE SCHEMA<\/code><\/a> statement doesn&#8217;t actually create a schema. This is because a schema is already created with each database user.<\/p>\n<p>In Oracle, the\u00a0<a href=\"https:\/\/docs.oracle.com\/database\/121\/SQLRF\/statements_8003.htm#SQLRF01503\"><code>CREATE USER<\/code><\/a>\u00a0 statement creates the schema.<\/p>\n<p>In Oracle,\u00a0<code>CREATE SCHEMA<\/code> statement\u00a0lets you\u00a0populate your schema with tables and views and grant privileges on those objects without having to issue multiple SQL statements in multiple transactions.<\/p>\n<h3>SQL Server<\/h3>\n<p>In SQL Server, <a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/ms189462(v=sql.110).aspx\"><code>CREATE SCHEMA<\/code><\/a> will create a schema by the name you give it.<\/p>\n<p>Unlike MySQL, the\u00a0<code>CREATE SCHEMA<\/code>\u00a0statement creates a schema that is defined\u00a0separately to the\u00a0database.<\/p>\n<p>Unlike Oracle, the\u00a0<code>CREATE SCHEMA<\/code>\u00a0statement actually creates the schema.<\/p>\n<p>In SQL Server, once you create the schema, you can then add users and objects to it.<\/p>\n<h2>Conclusion<\/h2>\n<p>The term <i>schema<\/i> can be used within\u00a0many different contexts.\u00a0In the context\u00a0of creating schemas within\u00a0a specific database management system, you&#8217;ll need to work with\u00a0however that DBMS\u00a0defines schemas.<\/p>\n<p>And when you switch to a\u00a0new\u00a0DBMS, be sure\u00a0to look up\u00a0how that system defines schemas.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In database terms, a\u00a0schema\u00a0(pronounced &#8220;skee-muh&#8221; or &#8220;skee-mah&#8221;) is the organisation and\u00a0structure of a database. Both\u00a0schemas\u00a0and\u00a0schemata can be used as plural forms. A schema contains schema objects, which could be\u00a0tables,\u00a0columns, data types, views, stored procedures, relationships, primary keys, foreign keys, etc. A database schema can be represented in a visual diagram, which shows the\u00a0database objects and &#8230; <a title=\"What is a Database Schema?\" class=\"read-more\" href=\"https:\/\/database.guide\/what-is-a-database-schema\/\" aria-label=\"Read more about What is a Database Schema?\">Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[17,6,7,5],"tags":[30,31,18,166,167,32,20],"class_list":["post-696","post","type-post","status-publish","format-standard","hentry","category-database-concepts","category-mysql","category-sql","category-sql-server","tag-database-diagram","tag-database-schema","tag-database-terms","tag-define","tag-definition","tag-oracle","tag-what-is"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/696","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=696"}],"version-history":[{"count":15,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/696\/revisions"}],"predecessor-version":[{"id":1016,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/696\/revisions\/1016"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=696"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=696"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=696"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}