Fundamentals of Database Systems

1. Database Systems: Essential for Modern Applications

This book introduces the fundamental concepts necessary for designing, using, and implementing database systems and database applications.

Ubiquitous presence. Database systems are integral to modern life, underpinning activities from banking and reservations to online shopping and social media. They manage vast amounts of information, both textual and multimedia, and are essential for organizing and accessing this data efficiently.

Beyond traditional applications. The scope of database systems has expanded significantly, now encompassing multimedia databases, geographic information systems (GIS), data warehouses, and real-time processing systems. These advancements cater to the evolving needs of diverse fields, including social media, e-commerce, and scientific research. For example:

• Social media platforms rely on databases to store user profiles, connections, and posts.
• E-commerce sites use databases to manage product catalogs, customer orders, and payment information.
• Scientific research utilizes databases to store and analyze experimental data, such as genomic sequences and climate models.

Fundamentals are key. Understanding the core principles of database technology, including data modeling, system architecture, and query languages, is crucial for anyone involved in designing, using, or implementing these systems. This knowledge provides a solid foundation for navigating the complexities of modern database applications.

2. DBMS: Defining, Constructing, Manipulating, and Sharing Data

A database management system (DBMS) is a computerized system that enables users to create and maintain a database.

Core functionalities. A DBMS is a software system designed to facilitate the creation, maintenance, and utilization of databases. It provides a general-purpose framework for managing data, offering functionalities that include defining data structures, constructing the database, manipulating data through queries and updates, and enabling data sharing among multiple users and applications.

Meta-data management. The DBMS stores descriptive information about the database, known as meta-data, in a database catalog or dictionary. This meta-data includes details about data types, structures, and constraints, enabling the DBMS to understand and manage the data effectively.

Key processes:

• Defining: Specifying the data types, structures, and constraints of the data.
• Constructing: Storing the data on a storage medium controlled by the DBMS.
• Manipulating: Querying, updating, and generating reports from the data.
• Sharing: Allowing multiple users and programs to access the database simultaneously.

3. Self-Describing Systems: Meta-Data is Key

A fundamental characteristic of the database approach is that the database system contains not only the database itself but also a complete definition or description of the database structure and constraints.

The catalog's role. A key feature of a DBMS is its ability to store a complete description of the database structure and constraints within the system itself. This description, known as meta-data, is stored in the DBMS catalog and is used by the DBMS software and database users to understand the database's organization.

Program-data independence. Unlike traditional file processing systems, where data definitions are embedded within application programs, a DBMS provides program-data independence. This means that changes to the database structure do not necessarily require modifications to the application programs that access the database.

NOSQL exception. It is important to note that some newer types of database systems, known as NOSQL systems, do not require meta-data. Rather the data is stored as self-describing data that includes the data item names and data values together in one structure.

4. Data Abstraction: Hiding Complexity for Users

A DBMS provides users with a conceptual representation of data that does not include many of the details of how the data is stored or how the operations are implemented.

Conceptual representation. Data abstraction allows users to interact with data at a conceptual level, without needing to know the underlying storage or implementation details. This is achieved through the use of data models, which provide a logical representation of the data that is easier for users to understand.

Data model benefits:

• Hides storage and implementation details.
• Provides a conceptual representation of data.
• Uses logical concepts like objects, properties, and relationships.

Program-operation independence. In object-oriented and object-relational databases, data abstraction extends to operations on the data. Users can invoke operations without needing to know how they are implemented, further simplifying the interaction with the database.

5. Multiuser DBMS: Sharing and Concurrency

A multiuser DBMS, as its name implies, must allow multiple users to access the database at the same time.

Concurrency control. A multiuser DBMS must allow multiple users to access the database concurrently. This requires concurrency control mechanisms to ensure that concurrent transactions do not interfere with each other and that the database remains consistent.

Transactions and ACID properties. The concept of a transaction is central to multiuser DBMSs. A transaction is a sequence of database operations that must be executed as a single, indivisible unit. The DBMS must enforce the ACID properties of transactions:

• Atomicity: All operations in a transaction are executed, or none are.
• Consistency: A transaction must maintain the consistency of the database.
• Isolation: Transactions must appear to execute in isolation from each other.
• Durability: Changes made by a committed transaction must be permanent.

6. Advantages of Using the DBMS Approach: Centralized Control

The database approach permits the DBA to define and enforce standards among database users in a large organization.

Centralized data management. The DBMS approach offers several advantages over traditional file processing systems, including:

• Controlling redundancy: Minimizing data duplication to save storage space and ensure consistency.
• Restricting unauthorized access: Implementing security mechanisms to protect sensitive data.
• Providing persistent storage for program objects: Enabling the storage and retrieval of complex data structures.
• Efficient query processing: Providing storage structures and search techniques for efficient query execution.
• Backup and recovery: Providing facilities for recovering from hardware or software failures.
• Multiple user interfaces: Supporting a variety of user interfaces to accommodate different user needs.
• Complex relationships: Representing complex relationships among data.
• Enforcing integrity constraints: Defining and enforcing rules to ensure data validity.
• Inferencing and actions: Permitting inferencing and actions using rules and triggers.

Standardization and reduced development time. The database approach facilitates the enforcement of standards, reduces application development time, and provides flexibility to adapt to changing requirements. It also ensures the availability of up-to-date information and economies of scale.

7. Evolution of Database Applications: A Historical View

Many early database applications maintained records in large organizations such as corporations, universities, hospitals, and banks.

Early systems. Early database applications, developed in the 1960s and 1970s, relied on hierarchical and network systems. These systems lacked data abstraction and program-data independence, making it difficult to adapt to changing requirements.

Relational revolution. Relational databases, introduced in the 1980s, provided data abstraction and application flexibility through high-level query languages and a separation of physical storage from conceptual representation. This led to the dominance of relational databases for traditional applications.

Object-oriented and beyond. The emergence of object-oriented programming languages led to the development of object-oriented databases (OODBs) for storing complex objects. XML emerged as a standard for data interchange on the Web, and database systems were extended to support specialized applications such as scientific data, multimedia, and spatial data.

Big data and NOSQL. The proliferation of social media and cloud computing has led to the emergence of big data storage systems and NOSQL databases, designed to handle massive volumes of data with high performance and scalability.

8. Three-Schema Architecture: Separating Concerns

The goal of the three-schema architecture is to separate the user applications from the physical database.

Levels of abstraction. The three-schema architecture provides a framework for organizing database systems into three levels:

• Internal level: Describes the physical storage structure of the database.
• Conceptual level: Describes the structure of the whole database for a community of users.
• External level: Describes the part of the database that a particular user group is interested in.

Data independence. This architecture promotes data independence, allowing changes to the schema at one level without affecting the schema at the next higher level. Logical data independence allows changes to the conceptual schema without affecting external schemas, while physical data independence allows changes to the internal schema without affecting the conceptual schema.

Mappings. The DBMS must transform requests and results between the different schema levels. These transformations are called mappings and are essential for achieving data independence.

9. Database Languages and Interfaces: Tailoring to Users

The DBMS must provide appropriate languages and interfaces for each category of users.

Variety of interfaces. A DBMS must provide a variety of languages and interfaces to accommodate different types of users with varying levels of technical expertise. These include:

• Data definition language (DDL): Used by DBAs and database designers to define the database schema.
• Storage definition language (SDL): Used to specify the internal schema.
• View definition language (VDL): Used to specify user views and their mappings to the conceptual schema.
• Data manipulation language (DML): Used for data retrieval, insertion, deletion, and modification.

User-friendly interfaces. In addition to formal languages, a DBMS should provide user-friendly interfaces such as menu-based interfaces, apps for mobile devices, forms-based interfaces, graphical user interfaces, and natural language interfaces.

10. Centralized and Client/Server Architectures: Distributing the Load

In a basic client/server DBMS architecture, the system functionality is distributed between two types of modules.

Centralized DBMSs. Older architectures used mainframe computers to provide the main processing for all system functions. All processing was performed remotely on the computer system housing the DBMS.

Client/server architecture. The client/server architecture distributes functionality between client modules, which handle user interaction and application programs, and server modules, which handle data storage, access, and search.

Two-tier architecture. In two-tier architectures, the user interface and application programs run on the client side, while the DBMS runs on the server side.

Three-tier architecture. The three-tier architecture adds an intermediate layer between the client and the database server, called the application server or Web server. This server runs application programs and stores business rules, improving security and scalability.

11. Classifying DBMSs: A Multifaceted Approach

Several criteria can be used to classify DBMSs.

Data model. DBMSs can be classified based on the data model they use, including relational, object, object-relational, NOSQL, hierarchical, and network models.

Number of users. DBMSs can be single-user or multiuser, depending on the number of concurrent users they support.

Number of sites. DBMSs can be centralized, with data stored at a single computer site, or distributed, with data distributed over multiple sites connected by a computer network.

Cost. DBMSs range from open source (free) to expensive commercial systems with modular components and various licensing options.

Access path options. DBMSs can be based on inverted file structures or other access path options.

Purpose. DBMSs can be general-purpose or special-purpose, depending on the specific application requirements.

Last updated: February 24, 2025