dbms notes TILL relational algebra and calculus

Introduction to DBMS (Database Management System)

  • A DBMS is a software system designed to manage and organize data in a structured manner.

  • It allows users to:

    • Create, modify, and query a database.

    • Manage security and access controls for the database.

Key Features of DBMS

  • Data Modeling: Tools for creating and modifying data models that define the structure and relationships of data.

  • Data Storage and Retrieval: Responsible for storing and retrieving data, providing various methods for searching.

  • Concurrency Control: Mechanisms for controlling simultaneous access to the database to prevent conflicts among users.

  • Data Integrity and Security: Tools to enforce integrity constraints and access controls.

  • Backup and Recovery: Mechanisms for data backup and recovery in case of system failures.

Types of DBMS

  • Relational DBMS (RDBMS): Data organized in tables with a set of rows and columns, related through primary and foreign keys.

  • Non-Relational DBMS (NoSQL): Data organized in key-value pairs, documents, graphs, or columns, optimized for large-scale applications.

Database Languages

  • Data Definition Language (DDL): Deals with database schema. Commands:

    • CREATE: Create a database and its objects (tables, indexes, etc.).

    • ALTER: Alter the structure of an existing database.

    • DROP: Delete objects from the database.

    • TRUNCATE: Remove all records from a table.

    • COMMENT: Add comments to data dictionary.

    • RENAME: Rename an object.

  • Data Manipulation Language (DML): Used for data manipulation, commands include:

    • SELECT: Retrieve data.

    • INSERT: Insert data.

    • UPDATE: Modify existing data.

    • DELETE: Remove records.

    • MERGE: Combination of insert and update.

    • CALL: Execute subprogram.

    • EXPLAIN PLAN: Interpret data access paths.

    • LOCK TABLE: Control concurrency.

  • Data Control Language (DCL): Manages user access permissions.

    • GRANT: Assign permissions.

    • REVOKE: Remove permissions.

  • Transactional Control Language (TCL): Manages transaction control.

    • ROLLBACK: Cancel changes.

    • COMMIT: Save changes.

    • SAVEPOINT: Set a temporary save point.

Paradigm Shift from File System to DBMS

  • A file system manages data using files on a hard disk with limitations such as:

    • Redundancy: Same data stored in multiple places leading to update complexities.

    • Inconsistency: Different copies of the same data may not match.

    • Difficult Data Access: Users must know the exact file location to access data.

    • Unauthorized Access: Risk of unprivileged users modifying sensitive data.

    • No Concurrent Access: Only one user can access data at a time.

    • No Backup and Recovery: Lack of automatic data recovery options in case of loss.

Types of DBMS

  1. Relational DBMS (RDBMS): Data in rows and columns, manipulated with SQL.

  2. Object-Oriented DBMS (OODBMS): Data stored as objects, manipulatable via OOP languages.

  3. NoSQL DBMS: Data in non-relational formats, suitable for big data scenarios.

DBMS Architecture

  • A DBMS architecture can be:

    • 1-Tier Architecture: Direct access to the database without an interface.

    • 2-Tier Architecture: Client-server model, with direct communication between applications and database.

    • 3-Tier Architecture: Client interacts only with an application server, which then communicates with the database. This adds a layer of abstraction for scalability and security.

Three Schema Architecture

  • Also known as ANSI/SPARC architecture, it defines:

    • Internal Level: Physical data storage structures.

    • Conceptual Level: Overall logical structure and relationships without implementation specifics.

    • External Level: User-specific views of the database, each tailored to different user needs.

DBMS Instances

  • An instance is data stored at a specific time within the database schema.

  • The instance changes based on modifications (addition, deletion, update).

Data Independence

  • Refers to the ability to modify the schema at one level without affecting other levels:

    • Logical Data Independence: Change in conceptual schema without impacting external schema.

    • Physical Data Independence: Change in internal schema without affecting the conceptual schema.

Architecture of DBMS

  • Components:

    1. Query Processor: Interprets user queries into database instructions.

      • Includes DML Compiler, DDL Interpreter, Embedded DML Pre-compiler, Query Optimizer.

    2. Storage Manager: Manages data consistency, integrity, and relationship constraints.

      • Contains Authorization, Integrity, Transaction, File, and Buffer Managers.

    3. Disk Storage: Houses data files, data dictionaries, and indices for fast retrieval.

    • The DBA manages database systems, ensuring performance, security, and integrity.