What Is a Database Schema? A Quick Guide With Examples

A database schema is an integral part of application or software development, especially those that revolve around data and databases. In short, the database schema is a term used for a structure or layout defining a set of data.

In other words, it describes how the data will be organized and connected. Hence, a database schema may include schema objects like tables, views, fields, relationships, packages, indexes, types, and many other elements.

Database designers create database schemas to establish important elements, attributes, and connections from a specific data group.

It comes in the form of a schema diagram that explains to a database administrator how the data will be stored in a relational or non-relational database. Also, the size and complexity of the diagram depend on the project.

A database schema is a framework or blueprint used by programmers to construct and code the database. It’ll also help a database user understand and read the information stored in the database.

Furthermore, database schemas are necessary for designing database management systems (DBMS) and relational database management systems (RDBMS) like MySQL, PostgreSQL, and Oracle.

This article will explain the two main types of schemas in a database, their integration requirements, and provide a couple of examples.

Logical and Physical Database Schemas

When it comes to database design, there are two types of schemas – logical and physical.

Logical Database Schema

The logical database schema describes the logical constraints or rules that will be applied to the data. The main concern is to understand the data entities, including their relations and attributes.

Creating this schema should not be limited to a particular DBMS. The logical database design will stay the same regardless of which platform you use.

One way to show a logical design is by making a visual representation called an entity-relationship diagram (ER diagram). Microsoft Visio and LucidChart are some software you can use to create the diagram. An ER diagram generally displays:

  • All important entities.
  • Attributes of every entity.
  • A primary key that uniquely identifies a specific instance of an entity.
  • Plus, a foreign key that describes the relationships between entities.

For example, let’s say the entity “Book” has the following attributes – title, subject, publisher, and year. Its primary key is the “ISBN”. Furthermore, “Book” has a relationship with the entity “Author”, represented by the foreign key “Author ID”.

A custom graph illustrating the concept of database schema.

Keep in mind that logical schemas for different projects are created in several ways. They will also have different syntaxes to describe the logical constraints and architecture of the data.

Physical Database Schema

The physical database schema represents how the data is physically organized in a storage system, usually in the form of files and indices. It transforms the logical database schema to the physical data structure that works on a specific DBMS.

Table structures are all displayed in the physical database schema design. This includes the column name, data type, and constraints. Plus, the primary keys, foreign keys, and the relationship among them.

A physical database model also has the following characteristics:

  • Explains the data requirements of a project.
  • All tables and columns are specified.
  • Foreign keys are used to determine the connection between tables.
  • It may be different from the logical model because of physical constraints.

At this stage, you are mapping:

  • Entities to tables.
  • Attributes to columns.
  • Primary keys to unique indexes.
  • Foreign keys to non-unique indexes.
A custom graph illustrating how logical schema works.

Database Schema Integration Requirements

A database can have more than one schema. Thus, to have them work seamlessly, the following requirements need to be met:

  • Overlap preservation – make sure the elements overlapping in the schemas are in a database schema table.
  • Extended overlap preservation – copy entities connected with overlapping elements – but only appear in one schema – to the resulting database schema.
  • Normalization – avoid grouping independent elements and relationships together in one table.
  • Minimality – check that no entities from any of the database schemas are lost.

Database Schema Examples

Now that you know what a schema is and its requirements, let’s look at schema examples from the SQL and PostgreSQL databases.

1. SQL

A schema in an SQL server is a collection of unlimited database objects linking to a database system. It includes tables, functions, views, and indexes. It’s a logical type of schema that brings a number of advantages. For example:

  • A single schema can be used in multiple databases.
  • Security permissions can protect database objects like tables and indexes.
  • Transferring the ownership of schemas is possible.
  • Objects created in the database can be moved between schemas.
  • It helps in the process of manipulating and accessing the database objects.

The general syntax to create a schema in SQL is:

CREATE SCHEMA [schema_title] [AUTHORIZATION owner]
[DEFAULT CHARACTER SET set_name]
[PATH schema_title[, ...]]
[ ANSI CREATE statements [...] ]
[ ANSI GRANT statements [...] ];

The parameters used are:

  • schema_title – the name of the schema created.
  • AUTHORIZATION owner – the name of the user who owns the schema.
  • DEFAULT CHARACTER SET set_name – defines the default character set for the schema objects.
  • PATH schema_title[, …] – optional file path and name.
  • ANSI CREATE statements […] – consists of a single CREATE statement or more.
  • ANSI GRANT statements […] – consists of a single GRANT statement or more.

2. PostgreSQL

The schema in PostgreSQL is a namespace containing named database objects. This includes tables, data types, indexes, functions, views, and operators. It ensures all objects have unique names.

A database can have more than one schema, but each is part of only one database. Different objects in two schemas can also share the same name.

The syntax to create a database schema in PostgreSQL 9.3.13 is:

CREATE SCHEMA schema_title [ AUTHORIZATION user] [ schema_element [ ... ] ]
CREATE SCHEMA AUTHORIZATION user [ schema_element [ ... ] ]
CREATE SCHEMA IF NOT EXISTS schema_title [ AUTHORIZATION user ]
CREATE SCHEMA IF NOT EXISTS AUTHORIZATION user

PostgreSQL also automatically creates a public schema for every new database. This is where you find objects without a schema name.

Conclusion

A database schema is a plan or diagram that refers to the organization of the data stored in the database. There are two types of schema – logical and physical. The first represents how data is organized, and the second defines the structure of its physical storage.

A database schema is vital for creating databases and database management systems. It helps users understand and read the information stored in the database.

We hope you learned what the role of a database schema is and how to use it. If you have any questions about this topic, feel free to leave us a comment below.

Author
The author

Ratna Siti N.

Ratna is a web hosting expert and content writer at Hostinger. With her experience with all things tech and SEO, she is ever-ready to share. In her spare time, Ratna likes to read a good book or resume her cross-stitch project.