Database System Concepts and Architecture

Data Models

A data model is a conceptual representation of how data is organized, stored, and accessed within a database. It defines the relationships between different data elements and the rules governing those relationships. Data models provide a blueprint for designing databases and ensure consistency and integrity of the data.

There are several types of data models, each with its own way of structuring data:

• Entity-Relationship (ER) Model: This is a high-level conceptual model that represents data as entities (objects of interest) and relationships between them. It uses graphical notations like rectangles for entities and diamonds for relationships. It's widely used for database design.
• Relational Model: This is the most widely used data model today. It organizes data into tables (relations) consisting of rows (tuples) and columns (attributes). Each table has a unique key to identify rows. SQL (Structured Query Language) is the standard language for interacting with relational databases.
• Hierarchical Model: This model organizes data in a tree-like structure, where each record has a single parent and multiple children. It was one of the earliest database models but is less flexible than the relational model.
• Network Model: Similar to the hierarchical model, but it allows a record to have multiple parent and child records, forming a graph-like structure. This provides more flexibility than the hierarchical model but is also more complex.
• Object-Oriented Model: This model views data as objects, similar to object-oriented programming. Objects have attributes (data) and methods (operations). It supports complex data types and inheritance.
• NoSQL Models: These are a diverse group of models designed for specific use cases, often prioritizing flexibility, scalability, and performance over strict adherence to ACID properties. Examples include:
  • Key-Value Stores: Simple databases where data is stored as a collection of key-value pairs.
  • Document Databases: Store data in document formats like JSON or BSON, allowing for flexible schemas.
  • Column-Family Stores: Organize data into column families, optimized for queries over large datasets.
  • Graph Databases: Represent data as nodes and edges, ideal for complex relationships and network analysis.

Schemas and Instances

In the context of databases, we distinguish between two key concepts: schema and instance.

Schema: The schema of a database is its structural definition. It describes the organization of the data, including the tables, columns, data types, constraints, and relationships defined in the database. Think of it as the blueprint or the skeleton of the database. The schema is generally static and changes infrequently.

There are different levels of schemas:

• Conceptual Schema: The overall logical structure of the database, describing entities, attributes, and relationships.
• Physical Schema: Describes the physical storage structure of the database, including files, indexes, and data storage details.
• External Schema (or View): Describes the part of the database that a particular user group is interested in. Multiple external schemas can exist for a single database.

Instance: An instance of a database is the actual data stored in the database at a particular moment in time. It's a snapshot of the data. The instance changes frequently as data is inserted, updated, or deleted. If the schema is the blueprint, the instance is the actual building constructed from that blueprint, filled with people and furniture.

For example, if a database schema defines a table called `Employees` with columns `EmployeeID`, `Name`, and `Salary`, then a specific set of rows in that table, like:

| EmployeeID | Name | Salary |

|------------|--------|--------|

| 101 | Alice | 50000 |

| 102 | Bob | 60000 |

is an instance of that table.

Three-Schema Architecture (ANSI-SPARC Architecture)

The three-schema architecture is a model for database management systems that separates the external, conceptual, and internal levels of the database. This separation aims to provide data independence and allow different users to interact with the database in ways that are relevant to them without affecting others.

The three levels are:

1. External Level (or View Level): This is the level closest to the user. It consists of descriptions of the database that are understandable to individual users or application programs. It describes only a part of the entire database, tailored to specific user needs. Multiple external schemas can exist for the same database. Each external schema represents a "view" of the database.
2. Conceptual Level (or Logical Level): This level represents the overall logical structure of the entire database. It describes all the entities, attributes, relationships, and constraints. It hides the details of physical storage but represents the entire database content. The conceptual schema is the bridge between the external views and the internal storage.
3. Internal Level (or Physical Level): This is the lowest level, closest to the physical storage. It describes how the data is actually stored on the storage devices. It includes information about file organization, indexing, data compression, and other physical storage details. The internal schema is complex and deals with the physical implementation.

Data Independence

Data independence is a key benefit of the three-schema architecture. It refers to the ability to modify the schema at one level without affecting the schema at the next higher level.

There are two types of data independence:

• Physical Data Independence: The ability to modify the physical schema (internal level) without causing application programs to be rewritten. For example, if we change the file organization or indexing strategy, applications that access the data should not need to be changed. This is achieved by the conceptual to internal mapping.
• Logical Data Independence: The ability to modify the conceptual schema (logical level) without causing application programs to be rewritten. For example, we can add a new attribute to an entity or split an entity into two, and applications that do not use the new or modified parts should continue to work. This is achieved by the external to conceptual mapping.

Data independence is crucial for maintaining and evolving database systems over time. It allows for optimization of performance (physical independence) and adaptation to changing requirements (logical independence) without disrupting existing applications.

Database Languages and Interfaces

Database languages are used to define, manipulate, and control data within a database management system (DBMS). They provide a way for users and applications to interact with the database.

Common categories of database languages include:

• Data Definition Language (DDL): Used to define the database schema. DDL statements create, alter, and drop database objects like tables, indexes, and views.
  • Examples: `CREATE TABLE`, `ALTER TABLE`, `DROP TABLE`, `CREATE INDEX`.
• Data Manipulation Language (DML): Used to retrieve, insert, update, and delete data in the database.
  • Procedural DML: Requires the user to specify what data to retrieve and how to retrieve it (e.g., older versions of SQL).
  • Declarative DML: Requires the user to specify what data to retrieve, but not how to retrieve it. The DBMS figures out the optimal way to access the data. SQL's `SELECT` statement is a prime example.
  • Examples: `SELECT`, `INSERT`, `UPDATE`, `DELETE`.
• Data Control Language (DCL): Used to manage user access and permissions to the database. It ensures data security and integrity.
  • Examples: `GRANT`, `REVOKE`.
• Transaction Control Language (TCL): Used to manage transactions, ensuring data consistency and integrity during multiple operations.
  • Examples: `COMMIT`, `ROLLBACK`, `SAVEPOINT`.

Database Interfaces: These are the mechanisms through which users and applications interact with the database languages and the DBMS.

• Command-Line Interface (CLI): A text-based interface where users type commands directly to interact with the database (e.g., `psql` for PostgreSQL, `mysql` for MySQL).
• Graphical User Interface (GUI): Visual tools that provide a more user-friendly way to interact with the database, often including visual query builders and data browsing capabilities (e.g., pgAdmin, MySQL Workbench).
• Application Programming Interfaces (APIs)/Software Development Kits (SDKs): Libraries and functions that allow application programs written in various programming languages (like Java, Python, C#) to connect to and interact with the database. Examples include JDBC (Java Database Connectivity) and ODBC (Open Database Connectivity).
• Web Interfaces: Web-based applications that provide access to database functionalities, often through forms and interactive dashboards.

Centralized and Client-Server Architectures

Database systems can be structured in different ways to manage how users and applications access the data. Two fundamental architectural models are centralized and client-server.

Centralized Database Architecture

In a centralized database system, all the database components—including the data itself, the DBMS software, and the processing logic—reside on a single computer system. All users and applications connect to this single machine to access and manage the data.

Characteristics:

• Single Point of Access: All operations go through one central server.
• Simpler Management: Easier to manage, back up, and secure because everything is in one place.
• Potential Bottleneck: The single server can become overloaded if many users try to access it simultaneously, leading to performance issues.
• Single Point of Failure: If the central server fails, the entire database system becomes unavailable.

Use Cases: Small-scale applications, departmental databases, or systems where high concurrency is not a primary concern.

Client-Server Database Architecture

The client-server architecture is the most common model for modern database systems. It divides the system into two main components:

• Server: This is the machine that hosts the database management system and the actual database files. It handles data storage, retrieval, security, and concurrency control. The server is responsible for executing database operations requested by clients.
• Client: These are the machines or applications that request services from the server. Clients send requests to the server (e.g., SQL queries) and receive results back. Clients can be desktop applications, web browsers, mobile apps, or other programs.

This architecture allows for the distribution of processing power and resources.

Advantages of Client-Server Architecture:

• Scalability: The server can be powerful, and multiple clients can connect without overwhelming a single system. Load can be balanced.
• Performance: Processing can be distributed. The server focuses on database tasks, while clients handle user interface and application logic.
• Data Integrity and Security: Centralized control on the server ensures data consistency and security rules are applied uniformly.
• Accessibility: Users can access the database from various locations and devices.
• Reduced Network Traffic: Clients only send requests and receive results, not entire files, which can reduce network load compared to some older distributed models.

Variations of Client-Server:

Two-Tier Architecture: The client directly communicates with the database server. The application logic resides on the client. This is common in desktop applications connecting to a database.

Three-Tier Architecture: Introduces an intermediate layer, often called the application server or middleware, between the client and the database server.

• Client: Handles the user interface.
• Application Server: Executes business logic, processes client requests, and communicates with the database server.
• Database Server: Manages the database and data.

This architecture improves scalability, security, and manageability by decoupling the UI, business logic, and data layers.

N-Tier Architecture: Extends the three-tier model by adding more layers for specialized functions, further enhancing modularity and scalability.