Database Management System

Definition of Database Language

A database language is a specialized computer language provided by a Database Management System (DBMS) that allows users, application programmers, and administrators to specify database schemas, express queries, manipulate stored data instances, and enforce security authorizations.

The primary database language in modern relational systems is SQL (Structured Query Language), which encompasses sub-languages including:

• DDL (Data Definition Language): For defining schema structures (CREATE, ALTER, DROP).
• DML (Data Manipulation Language): For data retrieval and manipulation (SELECT, INSERT, UPDATE, DELETE).
• DCL (Data Control Language): For privilege management (GRANT, REVOKE).
• TCL (Transaction Control Language): For managing transaction states (COMMIT, ROLLBACK).

Database Administrator (DBA) and Roles

Who is a DBA?

A Database Administrator (DBA) is a highly skilled technical professional or specialized team responsible for the centralized operational management, performance, configuration, security, integrity, and ongoing maintenance of an organization’s database systems.

Key Roles and Responsibilities of a DBA:

1. Schema Definition & Modification: Formulates the physical and logical database schemas, writes data definition statements, and implements schema alterations to support evolving business requirements.
2. Storage Structure & Access-Method Definition: Determines physical disk layouts, file organizations, block sizes, clustering, and index creation (B-trees, hash indexes) for optimal query throughput.
3. Granting User Authorizations & Security Control: Administers user accounts, assigns role-based permissions (GRANT / REVOKE), prevents unauthorized data access, and maintains data privacy.
4. Routine Backup and Disaster Recovery: Establishes automated daily/weekly backup procedures, monitors Write-Ahead Logs, and executes disaster recovery drills to ensure zero data loss.
5. Performance Tuning & Optimization: Analyzes query execution plans, identifies bottlenecks, monitors server hardware utilization, and tunes database cache buffers.

Four Types of Database Users

Database users are classified into distinct categories based on their technical proficiency, access requirements, and the interfaces they use to interact with the DBMS:

Database User Spectrum

---

1. Database Administrator (DBA)

• Description: The chief authority possessing supreme administrative privileges over the entire database system.
• Core Responsibilities:
  • Installing and configuring DBMS software across hardware servers.
  • Creating schemas, storage layouts, and system indexes.
  • Managing user accounts, assigning permissions (GRANT/REVOKE), and enforcing security policies.
  • Scheduling automated backups, monitoring system performance, and executing disaster recovery procedures.

---

2. Database Designers

• Description: Information architects responsible for identifying enterprise data requirements and engineering database models prior to database deployment.
• Core Responsibilities:
  • Interviewing business stakeholders to capture entities, attributes, and business rules.
  • Constructing conceptual schemas using Entity-Relationship (ER/EER) diagrams.
  • Converting conceptual schemas into normalized relational schemas (1NF through BCNF) to prevent update anomalies.

---

3. Application Programmers / Software Developers

• Description: Technical professionals who write application programs that interact with the database engine.
• Core Responsibilities:
  • Writing code in high-level programming languages (Java, C#, Python, JavaScript).
  • Embedding SQL Data Manipulation Language (DML) statements via APIs, ODBC/JDBC drivers, or ORM frameworks.
  • Building intuitive web and mobile user interfaces, error-handling routines, and transactional business logic.

---

4. End Users (Parametric and Casual Users)

• Description: Non-technical or business professionals who query and update the database during daily business operations.
• Subcategories:
  • Naive / Parametric Users: Represent the vast majority of users. They interact exclusively through pre-built graphical user interfaces, web forms, or POS terminals without knowing SQL (e.g., bank tellers, airline ticketing agents, retail cashiers).
  • Sophisticated Users: Business analysts, researchers, and data scientists who write ad-hoc SQL queries and use analytics tools to extract custom managerial reports.

Concept of Data Model

A data model is an abstract mathematical and conceptual framework that defines how data is structured, stored, organized, related, and manipulated within a Database Management System (DBMS). It provides the formal blueprint and grammar for database design.

Core Components of a Data Model:

1. Structural Component: Defines the organization of data objects, record types, data types, and inter-entity relationships (e.g., tables, rows, columns, foreign keys).
2. Integrity Component: Defines rules, constraints, and business logic that enforce data validity (e.g., Primary Key uniqueness, Entity Integrity, Referential Integrity, NOT NULL).
3. Manipulative Component: Defines the operational language and set of operations used to query, insert, update, and retrieve data (e.g., Relational Algebra, Structured Query Language - SQL).

Common Categories of Data Models:

• Relational Model (Codd, 1970): Represents data in two-dimensional tables (relations) of rows and columns (e.g., Oracle, MySQL, PostgreSQL).
• Entity-Relationship (E-R) Model: High-level conceptual model visualizing entities, attributes, and relationships.
• Hierarchical & Network Models: Legacy tree and graph-based pointer structures.
• NoSQL / Document Models: Schema-less JSON/BSON structures used in distributed big-data systems (e.g., MongoDB).

Why Level of Abstraction is Maintained in Database

A DBMS maintains multiple levels of data abstraction (the Three-Schema Architecture) to hide low-level hardware and storage complexities from end users and developers.

Key Reasons:

1. Data Independence:
   • Physical Data Independence: Changes to physical storage structures, indexing, or disks do not alter conceptual or external schemas.
   • Logical Data Independence: Changes to conceptual schemas (adding attributes or tables) do not require rewriting user application views.
2. Simplified User Interaction: End users interact with clean logical entities without concerning themselves with B-trees, page offsets, block pointers, or hashing.
3. Enhanced Security and Privacy: Different user groups access only their designated external view, preventing unauthorized exposure of sensitive enterprise data.

Types of Database Users

Database users are categorized based on their technical expertise, job roles, and how they interact with the DBMS:

Database Users

1. Database Administrator (DBA):
   • Responsible for overall administrative control, installation, configuration, security authorization, schema definition, backup/recovery, and performance tuning.
2. Database Designers:
   • Responsible for identifying data requirements, defining conceptual schemas (ER diagrams), logical schemas, constraints, and physical storage layouts prior to database deployment.
3. Application Programmers:
   • Software developers who write programs (in Java, C#, Python, PHP) that embed DML statements to interact with the database engine.
4. End Users:
   • Naive / Parametric Users: Unsophisticated users who interact through pre-built graphical user interfaces or forms without knowing underlying database structures (e.g., bank tellers, airline booking agents).
   • Sophisticated Users: Engineers, data analysts, and researchers who write complex custom queries in SQL or analytical tools to extract specialized information.
   • Casual / Standalone Users: Occasional users who access the database directly using interactive query interfaces.

Types of Database Languages

A Database Management System provides specialized interfaces and languages to define, manipulate, control, and secure data. In relational databases (SQL), these languages are categorized into four major subsystems:

Database Languages

---

1. Data Definition Language (DDL)

• Purpose: Used to define, alter, and destroy database structures, schemas, tables, views, indexes, and constraints in the data dictionary.
• Execution: Commands are auto-committed and alter the metadata.
• Key Commands: CREATE, ALTER, DROP, TRUNCATE, RENAME.
• Example:

CREATE TABLE Department (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50) NOT NULL,
    budget DECIMAL(12, 2) CHECK (budget > 0)
);

---

2. Data Manipulation Language (DML)

• Purpose: Used to retrieve, insert, update, and delete stored user data instances within existing relations.
• Categories:
  • Procedural DML: User specifies what data is needed and how to get it (Relational Algebra).
  • Declarative / Non-procedural DML: User specifies what data is needed without specifying algorithms (SQL).
• Key Commands: SELECT, INSERT, UPDATE, DELETE.
• Example:

-- Insert tuple
INSERT INTO Department VALUES (1, 'Computer Science', 750000.00);

-- Update salary
UPDATE Department SET budget = budget + 50000 WHERE dept_id = 1;

---

3. Data Control Language (DCL)

• Purpose: Used by administrators to control database permissions, privileges, and access rights, enforcing security policies.
• Key Commands: GRANT (confers privileges), REVOKE (cancels privileges).
• Example:

-- Grant select privilege to user 'teacher'
GRANT SELECT, INSERT ON Department TO teacher;

-- Revoke write privilege
REVOKE INSERT ON Department FROM teacher;

---

4. Transaction Control Language (TCL)

• Purpose: Manages changes made by DML operations, ensuring transaction boundaries and ACID compliance.
• Key Commands: COMMIT (permanent save), ROLLBACK (undo changes), SAVEPOINT (intermediate checkpoint).
• Example:

BEGIN TRANSACTION;
UPDATE Department SET budget = budget - 100000 WHERE dept_id = 1;
SAVEPOINT sp1;
-- If an error occurs:
ROLLBACK TO sp1;
-- When successful:
COMMIT;