dbms 1.1

Chapter 1: Database Concepts and Systems

1.1 Introduction

  • Purpose of Database Systems:

    • Provide efficient data management solutions.

    • Enhance data integrity, security, and accessibility.

  • DBMS Architecture:

    • Framework for organizing databases and their functionalities.

    • Includes components such as storage manager, query processor, and transaction manager.

  • Data Models:

    • Frameworks for structuring and managing data.

  • Data Independence:

    • Ability to modify the database schema without altering the application programs.

1.2 Database Languages, Users, and Administrators

  • Database Languages:

    • Enable developers to interact with the database.

    • Categories include Data Definition Language (DDL) and Data Manipulation Language (DML).

  • Database Users:

    • Divide into naive users, sophisticated users, and administrators.

  • Database Administrators:

    • Responsible for managing, maintaining and securing the database systems.

1.3 Different Types of Database Systems

  • Various formats designed for different purposes:

    • Relational Database Systems: Store data in tabular form.

    • Object-Oriented Databases: Support complex data types.

    • Hierarchical and Network Models: Older models used for specific applications.

1.4 University Database Example

  • Functionalities of University Database Applications:

    • Add new students and instructors.

    • Manage course registrations, generate rosters, compute GPAs, and issue transcripts.

  • Historical Context: Early database applications utilized direct access methods over file systems.

1.5 Drawbacks of Using File Systems

  • Data Redundancy & Inconsistency:

    • Data may exist in multiple file formats, leading to duplication.

  • Data Access Challenges:

    • New tasks require additional programming for each specific function.

  • Data Isolation & Integrity Problems:

    • Multiple file formats can lead to integrity issues; constraints hidden in program codes.

1.6 Further Drawbacks of File Systems

  • Atomicity of Updates:

    • Failures could result in partial updates leaving databases inconsistent.

  • Concurrency Issues:

    • Concurrent access by multiple users may lead to inconsistent states.

  • Security Problems:

    • Difficult to control user access levels.

1.7 Levels of Abstraction

  • Physical Level:

    • Details how records are stored physically.

  • Logical Level:

    • Represents the database structure and relationships logically.

  • View Level:

    • Abstracts details and manages user-specific data visibility.

1.8 View of Data

  • Architecture segregating physical, logical, and view levels to manage data effectively.

1.9 Instances and Schemas

  • Logical Schema:

    • Defines the overall structure of the database, akin to type definitions in programming.

  • Physical Schema:

    • Details physical storage of data.

  • Instance:

    • The actual data stored at a certain time.

  • Physical Data Independence:

    • Modifications at physical schema do not affect logical schema.

1.10 Data Models

  • Functions of Data Models:

    • Describe data, relationships, semantics, and constraints.

  • Types of Data Models:

    • Relational Model: Primary model using tables.

    • Entity-Relationship Model: Primarily used for database design.

    • Object-Based Models: Extend relational models with object-oriented features.

1.11 Relational Model

  • Structure:

    • Data organized into tables composed of rows (tuples) and columns (attributes).

1.12 Sample Relational Database

  • Instructor Table:

    • Contains fields such as ID, name, department name, and salary.

  • Department Table:

    • Includes department details like name, building, and budget.

1.13 Data Definition Language (DDL)

  • Function:

    • Definition and schema creation of database objects.

  • Example:

    • Command to create an instructor table with various attributes.

  • Metadata:

    • Stored in a data dictionary containing schema info and integrity constraints.

1.14 Data Manipulation Language (DML)

  • Usage:

    • To query and manipulate data within the database.

  • Categories:

    • Pure languages (e.g., relational algebra) and commercial languages (e.g., SQL).

1.15 SQL

  • Overview:

    • Widely used commercial language not equivalent to Turing machines.

  • Embedding SQL:

    • Typically, SQL is embedded in higher-level programming languages.

1.16 Database Design

  • Logical Design:

    • Schema determining the structure and attributes of the database.

  • Physical Design:

    • Physical layout decisions regarding data storage.

1.17 Database Design Challenges

  • Reflection on potential problems within the database design and necessary adjustments.

1.18 Design Approaches

  • Entity Relationship Model:

    • Map out entities and relationships.

  • Normalization Theory:

    • Methodology for quality relations and integrity in database designs.

1.19 Object-Relational Data Models

  • Expansion of the Relational Model:

    • Integrates object-oriented features with relational data structures.

1.20 XML: Extensible Markup Language

  • Purpose:

    • Originally a document markup language turned into a major data interchange format.

  • Structure:

    • Capable of defining new tags and nested structures for efficient data exchange.

1.21 Database Engine

  • Components:

    • Storage manager, query processing module, and transaction manager.

1.22 Storage Management

  • Responsibilities of Storage Manager:

    • Interfaces directly with the OS file manager, managing data efficiently.

  • Tasks Include:

    • Ensuring data storage, retrieval, and updating processes are optimized.

1.23-1.24 Query Processing

  • Steps Involved:

    1. Parsing and translation

    2. Optimization

    3. Evaluation

  • Performance Considerations:

    • Cost estimation for operations relies on statistical data.

1.25 Transaction Management

  • Transaction Definition: Collection of operations in a single logical function.

    • Ensures the database remains consistent despite potential failures.

  • Concurrency Control:

    • Manages interactions among concurrent transactions to maintain consistency.

1.26 Database Users and Administrators

  • User Roles:

    • Differentiates between naive users, sophisticated users, and database administrators.

1.27 Database System Internals

  • System Components Include:

    • Query processors, storage managers, transaction managers, and more.

1.28 Database Architecture

  • Influencing Factors:

    • Varied architectures such as centralized, client-server, parallel, and distributed systems.

1.29-1.30 History of Database Systems

  • Evolution:

    • From tape-based data processing to the development of various database models from the 1950s to the present, including SQL and XML.

1.31 End of Chapter 1

  • Summary of the concepts covered in database systems from history to modern practices.

1.32 Hierarchical Model Overview

  • Example: Use of hierarchical model within database architecture.

1.33 Network Model Overview

  • Depicts how data structures interrelate, specifically within a department or library context.

1.34 Relational Model Summary

  • Representation of data through rows and columns in relational format.

1.35 Important Terminologies - Relational Model

  • Key Terms:

    • Attribute: Columns in tables (e.g., Employee_ID).

    • Tuple: A single record or row.

    • Degree: Number of attributes.

    • Cardinality: Total number of rows.

    • Relation Key: Identifiers for rows.

    • NULL Values: Undefined or missing values.

1.36 Object-Oriented Data Model

  • Stores multimedia data forms such as audio, video, and images.

1.37 Object-Relational Data Model

  • Merging relational and object-oriented models permitting complex types and relationships.