DBMS Languages and Physical Data Independence

Physical Data Independence

• Allows changes to the internal schema without impacting the conceptual schema.
• Examples include reorganizing file structures or adding indexes for improved database performance.

DBMS Languages

• Data Definition Language (DDL):
  • Used by database administrators (DBA) and designers to define the conceptual schema.
  • Also handles internal and external schemas (views) in many DBMS.
• Storage Definition Language (SDL):
  • Implemented through DBMS commands for DBAs and designers.
• View Definition Language (VDL):
  • Used to define internal and external schemas.
• Data Manipulation Language (DML):
  • Used to perform operations on data including retrieval, insertion, deletion, and modification.
  • Two main types:
    • Low-level (Procedural) DML: Allows embedding data sublanguage commands in general-purpose programming languages like C or assembly.
    • High-level (Nonprocedural) DML: Enables standalone commands for direct data access (known as query languages).

High-Level and Low-Level DBMS Languages

• High-Level (Nonprocedural) Languages:
  • Examples: SQL
  • Set-oriented: focusing on what data to retrieve rather than how.
  • Also known as declarative languages.
• Low-Level (Procedural) Languages:
  • Record-at-a-time: specifying how to retrieve data.
  • Include constructs like looping.

DBMS Interfaces

• Stand-alone Query Language Interfaces: For direct interaction with the database.
• Programmer Interfaces: For embedding DML in programming languages:
  • Pre-compiler Approach: Translates embedded DML into standard code.
  • Procedure (Subroutine) Call Approach: Accesses database functionality via procedure calls.
• User-Friendly Interfaces:
  • Menu-based: Popular for browsing on the web.
  • Forms-based: Designed for casual users.
  • Graphics-based: Offers point-and-click, drag-and-drop functionalities.
  • Natural language: Allows communication with the database using plain English.
  • Combinations of the above: Integrating various interface types.

Other DBMS Interfaces

• Speech as Input and Output: Using voice commands.
• Web Browser as Interface: Accessing the database through a web browser.
• Parametric Interfaces: Standardized for users with specific roles (e.g., bank tellers).
• Interfaces for DBA:
  • Creating accounts.
  • Granting access permissions.
  • Setting system parameters.
  • Modifying schemas or access paths.

Database System Utilities

• Focus on:
  • Data Loading: Importing data from files. Includes data conversion tools.
  • Database Backup: Regular backups on tape.
  • Database Reorganization: Optimizing file structures.
  • Report Generation: Producing reports from data.
  • Performance Monitoring: Tracking database system efficiency.
  • Other functions like sorting, user monitoring, and data compression.

Other Tools

• Data Dictionary/Repository:
  • Stores schema descriptions and metadata like design decisions, application program details, user information, usage standards, etc.
  • Active Data Dictionary: Accessed by both DBMS and users/DBAs.
  • Passive Data Dictionary: Accessible only by users and DBAs.
• Application Development Environments and CASE (Computer-Aided Software Engineering) Tools:
  • Examples include PowerBuilder (Sybase) and Builder (Borland).

Centralized and Client/Server Architecture

• Centralized DBMS:
  • Combines all components in single system:
    • DBMS software.
    • Hardware.
    • Application programs.
    • User interface processing software.

Client/Server Architectures

• Basic Client/Server Architecture:
  • Designed to handle environments with multiple PCs, workstations, and servers.
  • Client: Typically a user machine offering user interface and local processing capabilities.
  • Server: System hosting both hardware and software, providing services to client machines.
• Three-Tier Client/Server Architecture:
  • Evolved with the rise of the web.
  • Intermediate Layer (Middle Layer):
    • Also known as Application Server or Web Server.
    • Stores web connectivity software and business logic (constraints) for data access.
    • Acts as a conduit for transmitting partially processed data between database server and client.
  • Additional Features:
    • Security: Encrypts data at the server before transmission and decrypts it at the client.

Two Tier vs. Three Tier Architecture

• Two-Tier: Direct communication between client and database server.
• Three-Tier: Introduces an intermediate layer for processing and managing communication.

Classification of DBMSs

• Based on Data Model:
  • Traditional: Relational, Network, Hierarchical.
  • Emerging: Object-oriented, Object-relational.
• Other Classifications:
  • Single-user: For individual use (typically with microcomputers).
  • Multi-user: For concurrent access by multiple users (most DBMS).
  • Centralized: Uses a single computer and database.
  • Distributed: Utilizes multiple computers and databases.

Distributed Database Systems

• Also known as client-server-based database systems.
• Don't support a fully distributed environment, but rather a set of database servers serving clients.
• Communication channels are used to connect different locations, each with its own memory and database.

Explore the critical concepts of physical data independence and various Database Management System (DBMS) languages, including DDL, SDL, VDL, and DML. Understand how these languages facilitate schema definitions and data manipulation while maintaining flexibility within database systems.