Skip to main content

Architecture and Languages

In a DBMS, data must be structured, organized, and managed efficiently. To understand its internal workings, we look at the schemas, instances, overall architecture, and the users who interact with it.

Schema, Instance, and Metadata​

1. Schema​

The description of a database is called a database schema. It is specified during the database design process and is not expected to change frequently.

2. Instance​

The data stored in the database at a particular moment in time is called the database state or instance.

3. Metadata​

The DBMS stores the descriptions of schema constructs and constraints (data about data) in a catalog, referred to as metadata.

Three-Schema Architecture​

The goal of the three-schema architecture is to separate the user applications from the physical database structure, providing data independence.

  1. Physical Level: Describes how data is physically stored on SSDs/HDDs.
  2. Logical Level: Describes what data exists and the relationships among them.
  3. View Level: Describes only a specific part of the database. Useful for security and access simplification.

Database Users and Administrators​

Database Administrator (DBA)​

The DBA manages centralized control over data. Their duties include:

  • Generating the original database schema.
  • Specifying storage structures and access methods.
  • Assisting in backup and permissions configuration.

Types of Users​

  1. Application Programmers: Interact via program calls.
  2. Sophisticated Users: Run custom complex queries usually via SQL without predefined tools.
  3. Naive Users: Interact purely via pre-built user interfaces or mobile applications.

Database Languages​

1. Data Definition Language (DDL)​

  • Used to specify the database schema structure (creating tables, altering tables).
  • Generates the metadata stored in the data dictionary.

2. Data Manipulation Language (DML)​

  • Used to retrieve, insert, delete, or modify data in the database.
  • Procedural DML: Users specify what data is needed and how to get it.
  • Non-procedural (Declarative) DML: Users specify what data is needed, leaving the how to the DBMS query execution planner. SQL is the prime example.