Components of DBMS and Architecture

A database system is divided into modules that deal with different responsibilities. The overall system structure consists of various components that interact to provide the database services.

Three-Schema Architecture (ANSI-SPARC)

The three-schema architecture separates user applications from the physical database through three levels of abstraction:

1. EXTERNAL LEVEL (View Level):
   • Highest level of abstraction
   • Describes WHAT data is seen by individual users
   • Multiple external schemas (views) exist
   • Each view describes the portion of database relevant to a particular user group

2. CONCEPTUAL LEVEL (Logical Level):
   • Community view of the database
   • Describes WHAT data is stored and relationships among data
   • Single conceptual schema for entire database
   • Focus on entities, data types, relationships, constraints

3. INTERNAL LEVEL (Physical Level):
   • Lowest level of abstraction
   • Describes HOW data is physically stored
   • Deals with: Storage structures, indexes, access paths, file organization

Data Independence

The capacity to change schema at one level without affecting schema at the next higher level.

1. LOGICAL DATA INDEPENDENCE:
   • Ability to change conceptual schema without changing external schemas
   • Example: Adding a new table doesn't affect existing views

2. PHYSICAL DATA INDEPENDENCE:
   • Ability to change internal schema without changing conceptual schema
   • Example: Creating an index doesn't affect queries

EXAMPLE 1: THREE-SCHEMA ARCHITECTURE IN ACTION

Physical Level (Internal):

• Students table stored as heap file
• B-tree index on StudentID
• Data blocks of 8KB size

Logical Level (Conceptual):
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
Name VARCHAR(100),
Email VARCHAR(100),
DepartmentID INT
);

View Level (External):
-- View for admissions office
CREATE VIEW AdmissionView AS
SELECT StudentID, Name, Email
FROM Students;

-- View for academic department
CREATE VIEW DepartmentView AS
SELECT S.Name, S.Email, D.DepartmentName
FROM Students S
JOIN Departments D ON S.DepartmentID = D.DepartmentID;

EXAMPLE 2: DATA INDEPENDENCE

Physical Independence:
-- Original: No index
SELECT * FROM Students WHERE LastName = 'Smith';
-- Slow: Full table scan

-- DBA creates index (physical level change)
CREATE INDEX idx_lastname ON Students(LastName);

-- Same query, now fast!
SELECT * FROM Students WHERE LastName = 'Smith';
-- Application code unchanged, but performance improved!

Logical Independence:
-- DBA adds new column (conceptual level change)
ALTER TABLE Students ADD COLUMN MiddleName VARCHAR(50);

-- Existing view still works
SELECT * FROM AdmissionView;
-- Returns same columns as before, application unaffected

EXAMPLE 3: DBMS COMPONENTS IN ACTION

User Query:
SELECT Name FROM Students WHERE GPA > 3.5;

Processing Flow:

1. DML Compiler: Parses query, checks syntax
2. Query Optimizer: Chooses execution plan
   • Option A: Full table scan
   • Option B: Use index on GPA
   • Chooses B (lower cost)
3. Query Evaluation Engine: Executes plan
4. Buffer Manager: Fetches required pages into memory
5. File Manager: Reads data from disk
6. Result: Returns matching records

EXAMPLE 4: THREE-TIER WEB APPLICATION

Presentation Tier (Frontend):
• React/Angular application
• Displays student list
• User clicks "View Details"

Application Tier (Backend):
• Node.js server receives request
• Validates user permissions
• Queries database
• Formats response as JSON

Database Tier:
• MySQL database
• Executes: SELECT * FROM Students WHERE StudentID = ?
• Returns data to application tier

IMPLEMENTING DBMS ARCHITECTURE:

1. Setting Up Three-Tier Application:

// Application Tier (Node.js + Express)
const express = require('express');
const mysql = require('mysql2/promise');

const app = express();

// Create connection pool (efficient connection management)
const pool = mysql.createPool({
  host: 'localhost',
  user: 'app_user',
  password: 'password',
  database: 'UniversityDB',
  waitForConnections: true,
  connectionLimit: 10
});

// API endpoint
app.get('/api/students/:id', async (req, res) => {
  try {
    const [rows] = await pool.query(
      'SELECT * FROM Students WHERE StudentID = ?',
      [req.params.id]
    );
    res.json(rows[0]);
  } catch (error) {
    res.status(500).json({ error: 'Database error' });
  }
});

app.listen(3000);

2. Implementing Views (External Level):

-- Public view: Hide sensitive data
CREATE VIEW PublicStudentInfo AS
SELECT StudentID, Name, Major
FROM Students;
-- Email, SSN hidden

-- Department view: Show department-specific data
CREATE VIEW CSStudents AS
SELECT StudentID, Name, GPA
FROM Students
WHERE Major = 'Computer Science';

-- Grant permissions on views
GRANT SELECT ON PublicStudentInfo TO public_user;
GRANT SELECT ON CSStudents TO cs_department;

3. Physical Independence Example:

-- Application uses this query
SELECT * FROM Orders WHERE OrderDate BETWEEN '2024-01-01' AND '2024-12-31';

-- DBA optimizes physical storage (no application changes needed)
CREATE INDEX idx_order_date ON Orders(OrderDate);
-- Or partition table by year
CREATE TABLE Orders_2024 PARTITION OF Orders
FOR VALUES FROM ('2024-01-01') TO ('2024-12-31');

4. DBA Daily Tasks:

# Monitor database performance
mysqladmin -u root -p processlist

# Check slow queries
mysql -u root -p -e "SELECT * FROM mysql.slow_log;"

# Backup database
mysqldump -u root -p UniversityDB > backup_$(date +%Y%m%d).sql

# Monitor disk space
df -h /var/lib/mysql

# Analyze table for optimization
mysql -u root -p -e "ANALYZE TABLE Students;"

5. Best Practices:
   • Use connection pooling (don't create connection per request)
   • Implement views for security and simplification
   • Create indexes on frequently queried columns
   • Separate read-only users from write users
   • Monitor query performance regularly
   • Document schema changes
   • Test backup and recovery procedures