Database System Concepts Chapter 2

Chapter 2: Database System Concepts and Architecture

• Fundamentals of Database Systems, Sixth Edition, by Elmasri and Navathe
• Copyright © 2011 Pearson Education, Inc.

Data Models

• A set of concepts to describe database structure, operations, and constraints.
• Data Model Structure and Constraints
  • Constructs define database structure.
  • Includes elements (and their data types), groups of elements (e.g., entity, record, table), and relationships among them.
  • Constraints specify restrictions on valid data and must be enforced.
• Data Model Operations
  • Used for database retrievals and updates.
  • Include basic model operations (insert, delete, update) and user-defined operations (e.g., compute_student_gpa, update_inventory).

Categories of Data Models

• Conceptual (high-level, semantic)
  • Close to how users perceive data.
  • Also called entity-based or object-based.
• Physical (low-level, internal)
  • Describes how data is stored in the computer.
  • Usually specified in ad-hoc manner through DBMS design and administration manuals.
• Implementation (representational)
  • Falls between conceptual and physical models.
  • Used by many commercial DBMS implementations (e.g., relational data models).

Data Models, Schemas, and Instances

• Data abstraction
  • Suppresses details of data organization and storage.
  • Highlights essential features for better understanding.
• Database schema
  • Description of a database.
  • Includes descriptions of the database structure, data types, and constraints.
• Schema diagram
  • Displays selected aspects of the schema.
• Schema construct
  • Each object in the schema.
• Database state (or snapshot)
  • Data in the database at a particular moment in time.

Categories of Data Models (continued)

• Entity: Represents a real-world object or concept.
• Attribute: Represents a property of interest that further describes an entity.
• Relationship: Represents an association among two or more entities.
• Entity-Relationship model
  • A type of data model.
• Relational data model
  • Commonly used in commercial DBMSs.
• Object data model
  • A new family of higher-level implementation models, closer to conceptual data models.
• Physical data models
  • Describes how data is stored as files in the computer.
  • Access path: Structure to efficiently search for specific database records (active).
  • Index: Allows direct access to data using an index term or keyword.

Schemas, Instances, and Database State (continued)

• Defining a new database involves specifying the database schema to the DBMS.
• Initial state: Populated or loaded with initial data, satisfies the structure and constraints.
• Valid state

Database Schema vs. Database State

• Database schema changes infrequently, while the database state changes every time the database is updated.
• Schema is also called intension (building).
• State is also called extension.

Three-Schema Architecture and Data Independence

• Internal level: Describes the physical storage structure of the database.
• Conceptual level: Describes the overall database structure for a community of users.
• External or view level: Describes the part of the database that a particular user group is interested in.
  • Mappings among schema levels are needed for transforming requests.

Data Independence

• Logical Data Independence: Changing the conceptual schema without affecting the external schemas and their associated application programs.
• Physical Data Independence: Changing the internal schema without affecting the conceptual schema.

DBMS Languages

• Data Definition Language (DDL): Defines schemas (used by DBA and DB designers).
• Storage Definition Language (SDL): Specifies the internal schema.
• View Definition Language (VDL): Specifies user views / mappings to conceptual schema (external level).
• Data Manipulation Language (DML): Allows retrieval, insertion, deletion, and modification.
• High-Level (Non-procedural) Language
• Low-Level (Procedural) Language

DBMS Interfaces

• Menu-based interfaces (Web clients or browsing), forms-based interfaces, graphical user interfaces, natural language interfaces, speech input and output, interfaces for parametric users, interfaces for the DBA.

The Database System Environment

• DBMS component modules
  • Buffer management: Schedules disk read/write to improve performance.
  • Stored data manager: Controls access to information stored on disk (database and catalog).
• Interactive query interface: Interface for casual database users.
• Query compiler: Compiles queries to internal form and validates syntax.
  • Query optimizer: Reorders operations, eliminates redundancies, and uses correct algorithms and indexes.
• Precompiler: Extracts DML commands from host programming languages, compiles those commands into object code for database access.
• Runtime database processor: Executes privileged commands, executable query plans, and transaction plans with runtime parameters
• System catalog: Information about the database itself (metadata)
• Concurrency control system: Manages concurrent access to data.
• Backup and recovery system: Creates backups and restores the database

Database System Utilities

• Loading: Loads existing data files.
• Backup: Creates backup copies of the database
• Database storage reorganization: Reorganizes database files.
• performance monitoring : Provides stats for DBA

Tools, Application Environments, and Communications Facilities

• Data dictionary / repository: Stores schema descriptions and other information about design decisions, application programs, users, standards, etc.
  • Active vs Passive

Centralized and Client/Server Architectures for DBMSs

• Centralized DBMS architecture: Combines everything (DBMS software, hardware, application programs, user interface) into a single system. Users connect through remote terminals.
• Basic Client/Server Architectures
  • Servers with specific functionalities (file servers, print servers, Web servers, email servers)
  • Client Machines (diskless, PCs with client software, network connection)
• DBMS Server (SQL servers, query servers, Transaction Servers), ODBC (Open Database Connectivity), JDBC (Java Database Connectivity)

Two-Tier Client/Server Architectures for DBMSs

• Client program connects to multiple DBMSs (or data sources).
• Data sources can be files or other non-DBMS software.

Two-Tier Client/Server Architectures (continued)

• Open Database Connectivity
• Provides application programming interface (API) to allow client-side programs to call the DBMS.
• JDBC
  • Allows Java client programs to access one or more DBMSs through a standard interface.

Three-Tier and n-Tier Architectures for Web Applications

• Application server (or Web Server): Intermediate layer between the client and the database server.
• Stores web connectivity software and business logic.
• Conduit for sending partially processed data.
• Three-tier architecture enhances security; clients cannot directly access the database server.

Classification of Database Management Systems

• Based on the data model being used (relational, network, hierarchical, object-oriented, object-relational).
• Other classifications
  • Single-user (personal computers) vs. multi-user (most DBMSs)
  • Centralized (single computer, one database) vs. distributed (multiple computers, multiple databases)

Variations of Distributed DBMSs (DDBMSs)

• Homogeneous DDBMS
• Heterogeneous DDBMS
• Federated or Multidatabase Systems
• Distributed DBMSs are now frequently implemented as client-server systems consisting of database servers supporting a set of clients. They do not support a totally distributed environment.

Cost Considerations for DBMSs

• Cost range: from free open-source systems to high-cost commercial configurations.
• Examples of free relational DBMSs: MySQL, PostgreSQL, etc.
• Commercial DBMSs: offer specialized modules (e.g., time-series, spatial data, document, XML) that are purchased separately.
• Various licensing options and costs

This quiz covers Chapter 2 of 'Fundamentals of Database Systems' by Elmasri and Navathe. Explore the various data models, their structures, operations, and constraints. Test your understanding of conceptual and physical data models and how they relate to database architecture.