In-Depth DBMS Notes

What is a DBMS?

  • A database-management system (DBMS) is:
    • A collection of interrelated data.
    • A set of programs to access that data.
  • Primary Goal: To provide a convenient and efficient way to store and retrieve database information.

Database System Applications

  • Key sectors utilizing DBMS applications:
    • Banking: Manages customer and account information.
    • Airlines: Handles reservations and scheduling information.
    • Universities: Manages student information and course registrations.
    • Credit Card Transactions: Processes transactions securely.
    • Sales: Manages customer, product, and purchase information.
    • Manufacturing: Manages production, inventory, and orders.
    • Human Resources: Manages employee information, salaries, and tax deductions.

Database System vs. File System

  • Example: Savings-bank enterprise tracking customer accounts.
  • File Processing System Features:
    • Stores information in operating system files.
    • Requires separate application programs for tasks such as:
    • Debit or credit accounts.
    • Add new accounts.
    • Find account balances.
    • Generate monthly statements.
Disadvantages of File Processing Systems:
  1. Data Redundancy and Inconsistency:
    • Multiple file formats and duplications (e.g., Savings and Current accounts).
  2. Difficulty in Accessing Data:
    • New programs needed for each task, complicating access.
  3. Data Isolation:
    • Multiple files and formats lead to isolated data sets.
  4. Integrity Problems:
    • Consistency constraints are hard to manage.
  5. Atomicity of Updates:
    • Failures can leave data in inconsistent states; examples include fund transfers.
  6. Concurrency Issues:
    • Multiple uncoordinated users can lead to inconsistencies (e.g., simultaneous updates).
  7. Security Problems:
    • Difficulty in regulating access to sensitive data.

Data Abstraction

  • A core purpose of DBMS:
    • Simplifies user interaction by providing an abstract view of data.
    • Complex data structures are used for efficiency, hiding complexity from users familiar with computing.
Levels of Data Abstraction:
  1. Physical Level:
    • Describes how data are actually stored (storage structures).
  2. Logical Level:
    • What data is stored and the relationships between them.
  3. View Level:
    • Simplifies user interactions with data by showing only needed parts.

Instances and Schemas

  • Instance: The data in the database at a specific moment.
  • Schema: The overall design of the database, analogous to variable declarations in a programming language.
    • Changes in the database can modify instances without altering the schema.

Data Models

  • Fundamental structure used in databases:
    • Types of Data Models:
    • Relational Model
    • Entity-Relationship Model
    • Object-based Models (Object-oriented and Object-relational)
    • Semistructured Model
    • Network Model
    • Hierarchical Model
Relational Model:
  • Consists of tables (relations) to represent data and relationships among data. Each table has:
    • Defined records (rows) of a specific type with fixed fields (columns).
Entity-Relationship Model:
  • Represents data using:
    • Entities: Distinguishable objects (e.g., people, accounts).
    • Attributes: Properties or details about entities (e.g., account number).
    • Relationships: Associations between entities (e.g., a customer owning accounts).
    • E-R Diagram Components:
    • Rectangles (entities), ellipses (attributes), diamonds (relationships).
Specialized Data Models:
  • Object-based Data Models: Store data as objects.
  • Semistructured Models: Allow varying attribute sets for data items; XML is a common format.
  • Network and Hierarchical Models: Represent data similarly but use distinct structures (graphs and trees).

Database Languages

  • Types of Database Languages:
    • Data Definition Language (DDL): Used to define database structures.
    • Data Manipulation Language (DML): Used to query and manage data.
    • Data Control Language: Controls access to data and permissions.
Example SQL Statements:
  • DDL Example: create table account (account-number char(10), Balance number(5));
  • DML Example: select customer-name from customer where customer-id = '192-83-7465';

Application Programs and Users

  • Types of Database Users:
    1. Naive Users: Use applications without technical knowledge (e.g., tellers).
    2. Application Programmers: Intermediate users who develop applications.
    3. Sophisticated Users: Query without programming knowledge, using query languages.
    4. Specialized Users: Develop applications for specific requirements (e.g., CAD systems).
Database Administrator (DBA):
  • Role of DBA:
    • Schema definition, storage structure, and access-method definition.
    • Authorization for data access and routine maintenance.

Transaction Management

  • Transactions: Collections of operations executed as a single logical unit.
    • Must ensure Atomicity, Consistency, Isolation, Durability (ACID) properties.
  • Example: Fund transfer must be all or nothing.

Database System Structure

  • Components:
    • Storage Manager: Manages physical data storage and retrieval processes.
    • Query Processor: Facilitates user queries, converting them into efficient operations.
  • Storage components manage disk and memory efficiently to meet the demands of large-scale databases.