Note: this article is published on 08/22/2024.
This series of articles will discuss Database Dictionary related issues
Introduction:
This article will discuss the database schema, a basic concept of a relational database. The content of this article will be
- A - What is a database schema?
- B - Database schema versus database instance
- C - Types of database schemas
- D - Benefits of database schemas
A - What is a database schema? [ref]
A database schema defines how data is organized within a relational database; this is inclusive of logical constraints such as, table names, fields, data types and the relationships between these entities.
Schemas commonly use visual representations to communicate the architecture of the database, becoming the foundation for an organization’s data management discipline. This process of database schema design is also known as data modeling.
The formal definition of a database schema is a set of formulas (sentences) called integrity constraints imposed on a database. These integrity constraints ensure compatibility between parts of the schema.
A schema comprises a collection of schema objects. Examples of schema objects include [ref]:
- tables
- views
- sequences
- synonyms
- indexes
- clusters
- database links
- snapshots
- procedures
- functions
- packages
On the other hand, non-schema objects may include:
- users
- roles
- contexts
- directory objects
B - Database schema versus database instance
--- The relationship between them is similar to Class vs. Object (Instance)
A database schema is considered the “blueprint” of a database which describes how the data may relate to other tables or other data models. However, the schema does not actually contain data.
A sample of data from a database at a single moment in time is known as a database instance. It contains all the properties that the schema describes as data values. Since database instances are just a snapshot at a given moment, they’re likely to change over time, unlike database schemas.
C - Types of database schemas
While the term schema is broadly used, it is commonly referring to three different schema types:
- Conceptual schemas --- as part of the process of gathering initial project requirements, offers a big-picture view of
- what the system will contain,
- how it will be organized, and
- which business rules are involved.
![]()
- Logical database schemas are less abstract than conceptual schemas. They clearly define schema objects with information, such as table names, field names, entity relationships, and integrity constraints—i.e. any rules that govern the database. However, they do not typically include any technical requirements.
![]()
- Physical database schemas provide the technical information that the logical database schema type lacks in addition to the contextual information, such as table names, field names, entity relationships, et cetera. That is, it also includes the syntax that will be used to create these data structures within disk storage.
![]()
D - Benefits of database schemas [ref]
As big data continues to grow, database objects and schemas are critical to ensure efficiency in day-to-day company operations. If relational models are poorly organized and poorly documented, they will be harder to maintain, posing problems for both its users and the company.
Some key benefits of database schemas include:
- Access and security: Database schema design helps organize data into separate entities, making it easier to share a single schema within another database. Administrators can also control access through database permissions, adding another layer of security for more proprietary data.
- Organization and communication: Documentation of database schemas allow for more organization and better communication among internal stakeholders. Since it provides a common source of truth, it enables users to understand the logical constraints and methods of aggregation across tables.
- Integrity: This organization and communication also helps to ensure data validity.
References:
- Database Schema,
- Data Dictionary