What Is a Database Schema? GA S REGULAR Menu Lifewire Tech for Humans Newsletter! Search Close GO Software & Apps > Apps 32 32 people found this article helpful

What Is a Database Schema?

A schema is the blueprint of a database that ensures optimal organization

By Mike Chapple Mike Chapple Writer University of Idaho Auburn University Notre Dame Former Lifewire writer Mike Chapple is an IT professional with more than 10 years' experience cybersecurity and extensive knowledge of SQL and database management. lifewire's editorial guidelines Updated on July 17, 2021 Reviewed by Ryan Perian Reviewed by Ryan Perian Western Governors University Ryan Perian is a certified IT specialist who holds numerous IT certifications and has 12+ years' experience working in the IT industry support and management positions. lifewire's editorial guidelines Tweet Share Email Tweet Share Email

In This Article

Expand Jump to a Section Data Asset Containers Why Schemas Matter Creating Schemas Schemas vs Data Models Frequently Asked Questions A database schema is a collection of metadata that describes the relationships between objects and information in a database. An easy way to envision a schema is to think of it as a box that holds tables, stored procedures, views, and related data assets. A schema defines the infrastructure of this box.

Data Asset Containers

At its basic level, a schema serves as a container for data assets. However, different database vendors structure their schemas in different ways. Oracle, for example, treats every schema as a user account. To create a new schema, a database administrator creates a new database user with the intended schema name.

Why Schemas Matter

Because schemas constitute a basic structural feature of a database, most database environments apply access permissions to objects on a schema level. For example, a company database might contain a series of users. Each user incurs a schema, but access to different schemas is granted individually, and with the granularity of permissions, to users outside of the home schema. Most database management tools don't list schemas; instead, they list databases and users. For example, a company creates user accounts (schemas) for Bob and Jane. It also creates accounts for departments like HR and Marketing. Then, it gives an analyst in each department access to their department's schema account. The HR analyst creates tables and views within the HR schema and grants access to Bob to read (but not write to) an HR table that lists employee names and employee ID numbers. Also, the HR analyst may grant access to Jane to read and write to an HR table that lists employee phone numbers. By granting access this way, only the right roles and users can read, write, or modify the data in a self-contained data asset within the larger database. Every database engine looks to schemas as the foundational method of segregating data in a multi-user environment. Different database engines treat users and schemas differently. Refer to the documentation for your database engine to discover the syntax and logic models surrounding users, schemas, and permissions grants.

Creating Schemas

A schema is formally defined using ​Structured Query Language (SQL). For example, in Oracle, you create a schema by creating the user account that owns it: CREATE USER bob
IDENTIFIED BY temporary_password
DEFAULT TABLESPACE example
QUOTA 10M ON example
TEMPORARY TABLESPACE temp
QUOTA 5M ON system
PROFILE app_user
PASSWORD EXPIRE; Other users are granted access to new schemas by virtue of their username or by one or more roles that the user account has been added to.

Schemas vs Data Models

Like a data model, a schema isn't intrinsically structured to do anything. Instead, it's an infrastructure to support segmentation permissions in a database. A data model is a collection of tables and views joined on specific keys. These data assets, together, serve a business purpose. It's acceptable to apply a data model to a schema—for large and complex data models, associating them with schemas makes for smart database administration. But it's not logically necessary to use a schema for a data model or to treat a data model as a schema. For example, the HR department might include a data model for employee performance reviews in its schema. Instead of creating a schema for these reviews, the data model can sit in the HR schema (along with other data models) and remain logically distinct through prefixes of the table and view names for the objects in the data model. The data model might earn an informal name, such as performance reviews, and then all tables and views might be prefixed by pr_. The employee listing table might be referenced as hr.pr_employee without requiring a new schema for the performance reviews. FAQ What is the difference between a database schema and a database state? A database scheme describes the database. A database state refers to the content of a database at a moment in time and can be considered an extension of the database schema. What is a relational schema of a database? A relational schema outlines the relationships between tables and items that are associated with one another. A schema can be a graphic illustration or chart, or it can be written in SQL code. Was this page helpful? Thanks for letting us know! Get the Latest Tech News Delivered Every Day Subscribe Tell us why! Other Not enough details Hard to understand Submit More from Lifewire An Introduction to Databases for Beginners How to Find Out Who (or What) Is Accessing Your Gmail How to Create Users And Grant Permissions In MySQL Task Manager (What It Is & How to Use It) MDW File (What It Is & How to Open One) What Is a File System and What Are the Different Kinds? What Is a Cryptographic Hash Function? A Guide to Understanding Database Dependencies How to Create and Delete User Accounts in Windows 10 What Is a Database Instance? Full Functional Dependency in Database Normalization What Is mysqldump and How Do I Use It? What Is the Definition of a Database Query? Data Control Language (DCL) for Database Permissions What is MySQL? The Basics of Database Normalization Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up By clicking “Accept All Cookies”, you agree to the storing of cookies on your device to enhance site navigation, analyze site usage, and assist in our marketing efforts. Cookies Settings Accept All Cookies