Data Glossary

Table of Contents:


    What is Database Schema

    Piotr Kononow - Dataedo Team Piotr Kononow 2018-10-04

    A few meanings

    Database schema is one of those fuzzy terms with a couple of meansings and interpretations. Depending on context it can mean:

    1. Data model in database
    2. Whole of data structures and programs within a database
    3. Namespace (element of SQL)

    1. Data model

    One of the meanings of database schema is the arrangement of tables in the database - specific tables, data types, primary and unique keys and foreign key constraints. It tied with specific DBMS and defined in formal language.

    2. Database implementation

    In broader sense database schema is the entirety of database elements - tables, constraints, views, stored procedures, data types etc.

    3. Namespace (element of SQL)

    Another meaning of schema is schema is a specific element in most relational database engines used for grouping objects. You can think of schema as a namespace or container that holds tables, views, functions etc. Their names can duplicate across different schemas.

    You create schema using following syntax in most databases:

    CREATE SCHEMA HumanResources;
    

    This creates new schema and it is prefix of each object within that schema:

    Why use schemas

    You may want to split database into schemas for follwing reasons:

    • Easy to assign permissions to entire schema,
    • Use same name for two or more different tables (or other object),
    • Group similar objects to reduce complexity.

    Support in RDBMS

    Virtually all relational databases support schemas, it's part of the SQL standard. But databases differ in function of schemas and whether they support "database" level of hierarchy.

    DBMS Database/Schema Implicit schema
    Just schemas
    Oracle Server => Schema = User n/a
    MySQL Server => Database = Schema n/a
    MariaDB Server => Database = Schema n/a
    Teradata Server => Database = Schema n/a
    Firebird Server => Database = Schema n/a
    Interbase Server => Database = Schema n/a
    SQLite File = Database = Schema main
    Databases and schemas
    SQL Server Database => Schema dbo
    PostgreSQL Database => Schema public
    Azure SQL Database Database => Schema dbo
    IBM Db2 Database => Schema n/a
    Amazon Redshift Database => Schema public
    Snowflake Warehouse => Database => Schema public
    SAP/Sybase ASE Database => Schema dbo
    SAP HANA Database => Schema n/a
    Vertica Database => Schema public
    IBM Informix Database => Schema n/a
    Comments (0)