Database System Structure & architecture in DBMS

Database System Structure & Architecture

A framework that defines how database systems store, manage, and retrieve data efficiently.
Detailing how data is stored, processed, and accessed.
It ensures data independence, integrity, security, and multi-user support.

The three main types of database architectures are:
Single-Tier Architecture (Centralized DBMS)
Two-Tier Architecture (Client-Server)
Three-Tier Architecture (Multi-Tier)

Single-Tier Architecture (Centralized DBMS)

All DBMS components are on a single machine.
Direct access to the database for users and applications.
Advantages: Simple setup, low cost.
Disadvantages: Not scalable, less secure, and performance bottlenecks.
Suitable for small applications.

Two-Tier Architecture (Client-Server Architecture)

Divides the system into Client and Server.
Client Tier: Sends requests to the server and receives results.
Server Tier: Executes database operations and processes requests.
Advantages: Faster response, supports multi-user environments.
Disadvantages: Limited scalability and security.
Ideal for small to medium-sized applications.

Three-Tier Architecture (Multi-Tier Architecture)

Divides the system into three layers:
Presentation Layer (Client): User interface for interaction.
Application Layer (Business Logic): Processes data and applies business rules.
Database Layer (Data Storage): Manages data storage and retrieval.
Advantages: High scalability, security, and flexibility.
Disadvantages: More complex to manage.
Used for enterprise-level and web-based applications.

Advantages of Database System Architecture

Data Independence: 
Security and Privacy:
Scalability: 
Improved Performance: 
Ease of Maintenance: 

Disadvantages of Database System Architecture

Complexity: 
Costly: 
Performance Overhead: 
-------------------------------------------------------------------------------------------------------

Database Architecture (3-Schema Architecture)

The ANSI/SPARC model standardizes how data is represented in a DBMS using three abstraction layers:

 Internal Level (Physical Level)

Purpose: Describes how data is stored on physical storage devices.
Focuses on:
Data storage formats (files, indices)
Access methods (B-trees, hash indexes)
Compression and encryption
Space allocation and optimization

Conceptual Level (Logical Level)

Purpose: Describes the structure and relationships of the entire database.
Focuses on:
Entities, attributes, and relationships
Constraints, views, and security
Integrity rules and normalization

External Level (View Level)

Purpose: Defines user-specific views of the database, providing controlled access.
Focuses on:
Custom views tailored to different users or applications
Data masking and security
Abstracting away complexity
-------------------------------------------------------------------

Architecture and components of the Database System

DBMS ARCHITECTURE

Components of DBMS (Database Management System) architecture
1. USERS
2. INTERFACES
3. QUERY PROCESSOR
4. STORAGE MANAGERS
5. STORAGE

I SYSTEM USERS

"System users" typically refer to the different types of individuals or roles that interact with a database management system (DBMS).

Types of system users
Database Administrators (DBAs)
Application Programmers
End Users
System Analysts
Database Designers

1. Database Administrators (DBAs)

Role: DBAs are responsible for managing and maintaining the database system. They oversee its performance, security, and integrity.
Interaction: They define the database schema, manage user access, ensure data backup and recovery, and optimize system performance. DBAs work at all levels of the architecture (external, conceptual, and internal).

2. Application Programmers

Role: These users develop applications that interact with the database, writing code to retrieve, update, or manipulate data.
Interaction: They use programming languages (e.g., Java, Python, or SQL) and APIs (e.g., JDBC, ODBC) to connect applications to the DBMS, typically working with the external and conceptual levels.

3. End Users

Role: End users access the database through pre-built applications or interfaces to perform tasks like querying or updating data. They are subdivided into:
Naive (Parametric) Users: Non-technical users who use simple, predefined applications without understanding the database structure.
Sophisticated Users: Technical users (e.g., engineers, analysts) who write their own queries (e.g., SQL) to extract data directly.
Casual Users: Occasional users, such as managers, who access the database infrequently for specific information.
Interaction: Naive users interact via the external level through user-friendly interfaces, while sophisticated and casual users may use query tools or custom applications.

4. System Analysts

Role: These users analyze requirements and design the database structure, bridging the gap between end users and developers.
Interaction: They work primarily at the conceptual level, defining how data is organized and ensuring it meets user needs.

5. Database Designers

Role: Designers create the database’s logical and physical structure, including tables, indexes, and relationships.
Interaction: They operate at the conceptual and internal levels, designing the schema and optimizing storage.
--------------------------------------------

2. INTERFACES

1. DBA Interface / Administrative Interface / Command Line Tools / GUI Tools

Use high-level admin tools or the SQL command line.
Interfaces may include SQL*Plus, Oracle Enterprise Manager, or pgAdmin.

2. Application Programming Interfaces (APIs) / Embedded SQL / ORM frameworks

Use programming languages like Java, Python, and C++ with DB connectivity.

3. Application Interfaces / Forms / Web Interfaces / Reports / Dashboards

Non-technical users interact via front-end applications.
Interact with data using: Web portals.GUIs (Graphical User Interfaces)

4. Design Tools / Modelling Tools / SQL Interfaces

Use tools to analyse system requirements and propose database solutions.

5. Data Modelling Tools / Schema Design Tools / SQL DDL

Responsible for designing the logical and physical structure of the DB.
--------------------------------------------------------

II. Define Query Processor

Components

(i) DDL Interpreter
(ii) DML Queries
(iii) DML Compiler and Organizer
(iv) Application Program Object Code
(v) Compiler and Linker
(vi) Query evaluation Engine

(i) DDL Interpreter
The Data Definition Language (DDL) Interpreter is responsible for processing DDL commands, which define and modify the structure of database objects (e.g., tables, schemas, indexes).
- 
(ii) DML Queries
Data Manipulation Language (DML) Queries are commands that manipulate the data within the database, such as `SELECT`, `INSERT`, `UPDATE`, and `DELETE`.
- 
(iii) DML Compiler and Organizer
The DML Compiler and Organizer is the component that translates DML queries into an executable form and optimizes them for efficiency.
-
(iv) Application Program Object Code
This refers to the compiled form of application programs (written in languages like Java, C++, or Python) that embed DML queries to interact with the database.
-
(v) Compiler and Linker
The Compiler and Linker are tools used to transform high-level application code (containing embedded DML queries) into executable object code.

(vi) Query Evaluation Engine
The Query Evaluation Engine (or Query Execution Engine) is the component that executes the optimized query plan produced by the DML compiler and optimizer.

Working Process

1. The DDL Interpreter sets up the database structure (e.g., tables, indexes).
2. DML Queries are submitted (either directly by users or via Application Program Object Code, which is prepared by the Compiler and Linker).
3. The DML Compiler and Organizer processes and optimizes these queries.
4. The Query Evaluation Engine executes the optimized plan and delivers the results.
--------------------

III. Components of storage manager –

(i) File Manager
(ii) Buffer Manager
(iii) Transaction Manager
(iv) Authorization and Integrity Manager

Storage Manager is an important component of the Database Management System (DBMS) responsible for managing the storage, retrieval, and organization of data on disk or in memory.

(i) File Manager
The File Manager is responsible for managing the underlying files where the database data is physically stored. It handles the allocation of disk space and organizes data into files, typically in the form of tables, indexes, or other structures.

(ii) Buffer Manager
The Buffer Manager oversees the use of main memory (RAM) to temporarily hold data pages fetched from disk, reducing direct disk I/O operations and speeding up data access.

(iii) Transaction Manager
The Transaction Manager ensures that database transactions (sequences of operations) are executed reliably, maintaining the ACID properties (Atomicity, Consistency, Isolation, Durability).

(iv) Authorization and Integrity Manager
The Authorization and Integrity Manager enforces security and consistency rules to protect the database and ensure data integrity.

Working process

These components collaborate within the storage manager to provide a seamless experience:
- The File Manager handles the physical storage layout.
- The Buffer Manager optimizes data access by caching pages in memory.
- The Transaction Manager ensures reliable and consistent updates.
- The Authorization and Integrity Manager safeguards the data’s security and correctness.
==============================

IV. Disk storage

(i) Data
(ii) Data Dictionary
(iii) Indices
(iv) Statistical Data

(i) Data
This refers to the actual user data stored in the database, such as the rows and columns of tables (e.g., customer names, ages, or transaction records).

(ii) Data Dictionary
The data dictionary (sometimes called the system catalog or metadata repository) is a centralized collection of metadata that describes the structure, organization, and properties of the database.

(iii) Indices
Indices are data structures stored on disk that provide efficient access paths to the actual data, speeding up query execution (e.g., B-trees, hash tables).

(iv) Statistical Data
Statistical data refers to summary information about the database’s content, such as the number of rows in a table, distribution of values in a column (e.g., histograms), or the selectivity of indexes.

Working process

- Data is the core content users interact with.
- The Data Dictionary provides the schema and rules governing the data.
- Indices enhance performance by speeding up data retrieval.
- Statistical Data supports optimization by giving the system insights into the data’s characteristics.

=========================================================