Database System Concepts and Architecture

Data Models: Definition and Functional Components

  • Definition of a Data Model: A data model consists of a set of concepts used to describe the structure of a database, the operations available for manipulating those structures, and certain constraints that the database is required to obey.

  • Data Model Structure and Constraints:

    • Constructs: These are used to define the database structure and typically include elements and their specific data types.

    • Groups: Construct elements are organized into groups, such as entities, records, or tables.

    • Relationships: The model describes the interactions and connections among these groups.

    • Constraints: These specify restrictions on what constitutes valid data. It is mandatory for DBMS software to enforce these constraints at all times to maintain data integrity.

  • Data Model Operations:

    • Purpose: These are used to specify database retrievals and updates by directly referring to the constructs specified in the data model structure.

    • Basic Model Operations: These include generic tasks such as insert, delete, and update.

    • User-defined Operations: These are specific to business logic, such as compute_student_gpa or update_inventory.

Categorization of Data Models

  • Conceptual (High-Level/Semantic) Data Models: These provide concepts that are close to the way users perceive data. They are frequently referred to as entity-based or object-based data models.

  • Physical (Low-Level/Internal) Data Models: These describe the technical details of how data is stored in the computer. These concepts are often specified in an ad-hoc manner within DBMS design and administration manuals.

  • Implementation (Representational) Data Models: These fall between conceptual and physical models. They are used by many commercial DBMS implementations, with the relational data model being the primary example used in modern commercial systems.

Database Schemas, Instances, and States

  • Database Schema: The formal description of a database, including the structure, data types, and constraints. This is often referred to as the database intension.

  • Schema Diagram: A visual or illustrative display showing most aspects of a database schema.

  • Schema Construct: A specific component or object within the schema, such as STUDENT or COURSE.

  • Database State: This is the actual data stored in the database at a specific moment in time. It is also known as a database instance, occurrence, or snapshot. This is referred to as the database extension.

  • Instance as a Component: The term instance can also apply to individual parts of a database, such as a record instance, table instance, or entity instance.

  • Valid State: A database state that successfully satisfies all the structure and constraints defined in the schema.

  • Distinction Between Schema and State:

    • The database schema changes very infrequently.

    • The database state changes every time the database is updated.

    • The Initial Database State is the state when the database is first loaded into the system.

Illustrative Examples of Schema and State

  • Schema Diagram (Figure 2.1):

    • STUDENT: Attributes include Name, Student_number, Class, Major.

    • COURSE: Attributes include Course_name, Course_number, Credit_hours, Department.

    • PREREQUISITE: Attributes include Course_number, Prerequisite_number.

    • SECTION: Attributes include Section_identifier, Course_number, Semester, Year, Instructor.

    • GRADE_REPORT: Attributes include Student_number, Section_identifier, Grade.

  • Database State Example (Figure 1.2):

    • COURSE items: Intro to Computer Science (CS1310), Data Structures (CS3320), Discrete Mathematics (MATH2410), Database (CS3380).

    • SECTION entries: Section 85 (King), Section 92 (Anderson), Section 102 (Knuth), Section 112 (Chang), Section 119 (Anderson), Section 135 (Stone).

    • GRADE_REPORT sample: Student 17 in Section 112 (B), Student 8 in Section 85 (A).

    • PREREQUISITE pairs: CS3380 requires CS3320; CS3380 requires MATH2410; CS3320 requires CS1310.

The Three-Schema Architecture

  • Primary Goals: This architecture was proposed to support program-data independence and to allow for multiple views of the same data. While not explicitly used in all commercial products, it is the standard for explaining system organization.

  • The Three Levels:

    1. Internal Level: Uses an Internal Schema (physical data model) to describe physical storage structures and access paths such as indexes.

    2. Conceptual Level: Uses a Conceptual Schema to describe the structure and constraints of the entire database for a community of users. It uses conceptual or implementation data models.

    3. External Level: Uses External Schemas (user views) to describe specific parts of the database that individual users or groups are interested in.

  • Mappings: These are required to transform requests and data between levels. For example, a program refers to an external schema, and the DBMS maps this to the internal schema for execution. Results from the internal level (like an SQL query) must be reformatted for display in an external view (like a Web page).

Data Independence

  • Logical Data Independence: The ability to modify the conceptual schema without needing to change existing external schemas or their associated application programs.

  • Physical Data Independence: The capacity to modify the internal schema without affecting the conceptual schema. This typically happens when file structures are reorganized or new indexes are created to improve performance.

  • Mechanism: In a system that fully supports data independence, when a lower-level schema changes, only the mapping between that level and higher levels needs to be updated. The higher-level schemas and the application programs themselves remain unchanged.

DBMS Languages: DDL, DML, and Special Purpose Languages

  • Data Definition Language (DDL): Used by DBA staff and designers to specify the conceptual schema. In many systems, DDL also defines internal and external schemas.

  • Storage Definition Language (SDL): Used in some DBMSs specifically to define internal schemas (physical storage), typically via commands for the DBA.

  • View Definition Language (VDL): Used to define external schemas/views.

  • Data Manipulation Language (DML): Used for data retrieval and updates.

    • High-Level/Non-procedural (Declarative): Such as SQL. These are "set-oriented" and focus on what data to retrieve rather than how to retrieve it. They can be standalone or embedded.

    • Low-Level/Procedural: These retrieve data one record at a time. They require programming constructs like loops and positioning pointers and must be embedded in a host language.

DBMS Interfaces and User Interaction

  • Interface Types:

    • Stand-alone Query Language: For interactive use, such as entering queries into SQL*Plus in ORACLE.

    • Programmer Interfaces: For embedding DML in code. Approaches include:

      • Embedded Approach: e.g., embedded SQL (C/C++), SQLJ (Java).

      • Procedure Call Approach: e.g., JDBC (Java), ODBC.

      • Database Programming Language Interface: e.g., ORACLE PL/SQL.

    • User-Friendly Interfaces:

      • Menu-based (popular for web browsing).

      • Forms-based (designed for naive users).

      • Graphics-based (Point and Click, Drag and Drop).

      • Natural language (requests in written English).

    • Specialized Interfaces:

      • Speech as input/output.

      • Web browsers.

      • Parametric interfaces: Utilized by workers like bank tellers using dedicated function keys.

      • DBA Interfaces: Used for creating accounts, granting authorizations, setting parameters, and changing schemas.

Database System Utilities and Specialized Tools

  • Utilities: These perform administrative functions including:

    • Data Loading: Moving data from files into the database, including conversion tools.

    • Backup: Periodically saving the database to tape or other media.

    • File Reorganization: Optimizing storage structures.

    • Report Generation.

    • Performance Monitoring.

    • Other functions: Data compression, user monitoring, and sorting.

  • Data Dictionary / Repository: Used to store schema descriptions, design decisions, application descriptions, and usage standards.

    • Active: Accessed by both DBMS software and users.

    • Passive: Accessed by users/DBA only.

  • Application Development Environments (CASE Tools): Examples include PowerBuilder (Sybase), JBuilder (Borland), and JDeveloper 10G (Oracle).

DBMS Component Modules and System Interactions

  • User Types: DBA Staff, Casual Users, Application Programmers, and Parametric Users.

  • Compilers and Processors:

    • DDL Compiler: Handles DDL statements and updates the Catalog/Data Dictionary.

    • Query Compiler: Works with interactive queries.

    • Query Optimizer: Determines the most efficient way to execute queries.

    • Precompiler: Extracts DML commands from application programs.

    • DML Compiler: Compiles DML commands into code for the Runtime Database Processor.

  • Runtime Components: The Runtime Database Processor interacts with the Stored Data Manager to handle execution, concurrency control, and backup/recovery subsystems. It manages access to the Stored Database and the System Catalog.

Centralized and Client-Server Architectures

  • Centralized DBMS Architecture: Combines hardware, software, application programs, and UI processing into a single system. Users connect via remote terminals, but all processing occurs at the central site.

  • Logical Two-Tier Client-Server Architecture:

    • Servers: Specialized servers include Print, File, DBMS, Web, and Email servers.

    • Clients: Provide UI and client software modules; they can be PCs, workstations, or diskless machines connected via LAN or wireless networks.

    • DBMS Server: Provides query and transaction services. Relational servers are often called SQL servers. Clients use APIs such as ODBC (Open Database Connectivity) or JDBC (for Java) to access them.

  • Logical Three-Tier Client-Server Architecture:

    • Common for Web applications.

    • Presentation Layer: Client GUI or Web Interface.

    • Application Server (Business Logic Layer): Acts as an intermediate layer/conduit. It stores web connectivity software and business logic. This layer enhances security by preventing clients from accessing the database server directly.

    • Database Server (Database Services Layer): The final layer containing the DBMS software and data.

Classification and Cost Considerations for DBMSs

  • Data Model Classification: Traditional (Relational, Network, Hierarchical) or Emerging (Object-oriented, Object-relational).

  • User Classification: Single-user (PCs) vs. Multi-user (Enterprise).

  • Distribution Classification:

    • Centralized: Single computer.

    • Distributed (DDBMS): Multiple computers and databases. Types include Homogeneous, Heterogeneous, and Federated (Multidatabase) systems.

    • Note: Modern distributed systems are often called client-server based database systems.

  • Cost Considerations:

    • Range: From free open-source (MySQL, PostgreSQL) to multi-million dollar configurations.

    • Add-on Modules: Commercial systems offer specialized modules (cartridges in Oracle, blades in others) for time-series, spatial data, documents, or XML.

    • Licensing: Site licenses, seat licenses (maximum concurrent users), or single-user licenses.

Detailed History and Evolution of Data Models

  • Network Model:

    • Origins: First implemented by Honeywell in 1964-65 (IDS System).

    • Growth: Heavily supported by CODASYL (Conference on Data Systems Languages) and the DBTG report of 1971.

    • Systems: IDMS (Cullinet), DMS 1100 (Unisys), IMAGE (H.P.), VAX-DBMS (Digital/Compaq/HP).

    • Pros/Cons: Models complex relationships well and allows navigational optimization; however, it is highly procedural, relies on complex pointer arrays, and lacks automated query optimization.

  • Hierarchical Model:

    • Origins: Joint effort by IBM and North American Rockwell (~1965), leading to the IMS family.

    • Systems: IBM IMS (still has a large global base), System 2k (SAS Inc.).

    • Pros/Cons: Simple to operate and fits natural hierarchies like org charts; however, it is navigational, procedural, and visualizes data as a linear arrangement of records with limited optimization scope.

  • Relational Model:

    • Origins: Proposed in 1970 by E.F. Codd of IBM. First commercial systems appeared in 1981-82.

    • Systems: DB2, ORACLE, MS SQL Server, SYBASE, INFORMIX.

    • Standards: SQL-89 (SQL1), SQL-92 (SQL2), SQL-99 (SQL3).

  • Object-oriented/Relational Models:

    • OO Models: Persistent programming language models like C++ (ObjectStore, Versant) and Smalltalk (Gemstone). Standards include ODMG-93, 2.0, and 3.0.

    • Object-Relational: The most recent trend, starting with Informix Universal Server and continued in modern versions of Oracle-10i, DB2, and SQL Server. Standards began with SQL-99.