What is a Database Schema

A database schema represents the logical configuration of all or part of a relational database. It can exist both as a visual representation and as a set of formulas known as integrity constraints that govern a database. These formulas are expressed in a data definition language, such as SQL. As part of a data dictionary, a database schema indicates how the entities that make up the database relate to one another, including tables, views, stored procedures, and more.

Typically, a database designer creates a database schema to help programmers whose software will interact with the database. The process of creating a database schema is called data modeling. When following the three-schema approach to database design, this step would follow the creation of a conceptual schema. Conceptual schemas focus on an organization’s informational needs rather than the structure of a database.

There are two main kinds of database schema:

1. A logical database schema conveys the logical constraints that apply to the stored data. It may define integrity constraints, views, and tables.
2. A physical database schema lays out how data is stored physically on a storage system in terms of files and indices.

At the most basic level, a database schema indicates which tables or relations make up the database, as well as the fields included on each table. Thus, the terms schema diagram and entity-relationship diagram are often interchangeable.

In the Oracle database system, the term database schema, which is also known as "SQL schema," has a different meaning. Here, a database can have multiple schemas (or “schemata,” if you’re feeling fancy). Each one contains all the objects created by a specific database user. Those objects may include tables, views, synonyms, and more. Some objects cannot be included in a schema, such as users, contexts, roles, and directory objects.

Users can be granted access to log into individual schemas on a case-by-case basis, and ownership is transferable. Since each object is associated with a particular schema, which serves as a kind of namespace, it’s helpful to give some synonyms, which allows other users to access that object without first referring to the schema it belongs to.

These schemas do not necessarily indicate the ways that the datafiles are stored physically. Instead, schema objects are stored logically within a tablespace. The database administrator can specify how much space to assign to a particular object within a datafile.

Finally, schemas and tablespaces don’t necessarily line up perfectly: objects from one schema can be found in multiple tablespaces, while a tablespace can include objects from several schemas.

These terms, though related, do not mean the same thing. A database schema is a sketch of a planned database. It doesn’t actually have any data in it.

A database instance, on the other hand, is a snapshot of a database as it existed at a particular time. Thus, database instances can change over time, whereas a database schema is usually static, since it’s difficult to change the structure of a database once it is operational.

Database schemas and database instances can affect one another through a database management system (DBMS). The DBMS makes sure that every database instance complies with the constraints imposed by the database designers in the database schema.

It can be useful to integrate multiple sources into a single schema. Make sure these requirements are met for a seamless transition:

Overlap preservation

Every overlapping element in the schemas you are integrating should be in a database schema table.

Extended overlap preservation

Elements that only appear in one source, but that are associated with overlapping elements, should be copied to the resulting database schema.

Normalization

Independent relationships and entities should not be lumped together in the same table in the database schema.

Minimality

It’s ideal if none of the elements in any of the sources are lost.

Certain patterns have developed in designing database schema.

The widely used star schema is also the simplest. In it, one or more fact tables are linked to any number of dimensional tables. It’s best for handling simple queries.

The related snowflake schema is also used to represent a multidimensional database. In this pattern, however, dimensions are normalized into lots of separate tables, creating the expansive effect of a snowflake-like structure.