Database Architecture

Database architecture can be understood through several layers and components, but a foundational concept is the Three-Schema Architecture, which describes the different levels of data abstraction provided by a database system to its users.

Three-Schema Architecture

Three-Schema Architecture architecture aims to separate user applications from the physical database, enabling data independence and data abstraction, allowing for better modularity, flexibility, and system management.

This architecture defines three levels of database schema

          +-------------------+
          |    EXTERNAL LEVEL |
          |   (User Views)    |
          +-------------------+
                ▲      │
                │      │ External/Conceptual Mapping
                ▼      │
          +-------------------+
          |   CONCEPTUAL LEVEL|
          |  (Logical Schema) |
          +-------------------+
                ▲      │
                │      │ Conceptual/Internal Mapping
                ▼      │
          +-------------------+
          |    INTERNAL LEVEL |
          |  (Physical Schema)|
          +-------------------+
                ▲      │
                │      │
                ▼      │
          +-------------------+
          |   STORED DATABASE |
          |   (Actual Data)   |
          +-------------------+

1. External Level (View Level)

• This is the highest level of abstraction and consists of external schemas or user views of the database, each describing the portion of the database that a particular user group is interested in, hiding the rest of the database from that group.
• Many views can exist for the same database. Views simplify user interaction and can also act as a security mechanism, preventing users from accessing certain parts of the database.

• User Focus: End-users and application programmers interact at this level, often through application programs that provide a simplified, abstract view of the information.
• External schemas are typically defined using the data definition language (DDL), such as SQL's CREATE VIEW statement.

2. Conceptual Level (Logical Level)

Describes the logical structure of the entire database for a community of users, abstracting away the details of physical storage.

It describes what data is stored in the database and what relationships exist among that data. It represents the logical design of the database and is often based on a high-level data model, like the Entity-Relationship (ER) model.

The conceptual schema hides physical storage details and focuses on what data is stored and and how it is related by describing :

• Entities and relationships
• Data types and attributes
• Integrity constraints
• User operations and business rules

User Focus: Database administrators (DBAs) and database designers primarily work at this level, deciding what information needs to be stored in the database.

3. Internal Level (Physical Level)

This is the lowest level of abstraction and details how the data is physically stored on computer storage media. The internal schema uses a physical data model and describes complex low-level data structures, file organizations, and access paths (like indexes).

Defines the internal physical schema, which includes:

• File structures and formats
• Indexes
• Access paths (e.g., hashing, B-trees)
• Storage locations
• Compression and encryption techniques (if any)
• Focus is on building efficient algorithms for fast data access.

User Focus: This level is typically understood and managed by computer specialists and implementers; most users are not aware of its complexity.

---

Main Goal of Three-Schema Architecture

The primary goal of this three-level structure is to allow different users to view the same data in customized ways, while physically storing the data only once.

To Achieve Data Independence

• Logical Data Independence: Changes in the logical schema (e.g., table structure) don’t affect external views.

• Physical Data Independence: Changes in the physical schema (e.g., indexing, storage location) don’t affect the logical schema.

To Simplify Database Design and Maintenance

• Modular structure makes it easier to manage and update the system.
• Allows developers to work on different levels independently.

To Support Different User Views

• Different users can see different views of the same data (e.g., a clerk vs. a manager).
• Ensures data security by restricting access to sensitive data.

To Provide Data Abstraction

• It hides the details of data storage and internal structure from users.
• Users don’t need to know how data is stored or organized physically.

---

Schema Mappings

In the three-schema architecture, data is just described at three different levels of abstraction: external (user views), conceptual (organizational view), and internal (physical storage). However, the actual data only exists at the internal level.

Mappings are the crucial processes that act as a bridge, translating requests and result data between these different levels, allowing users to interact at their levels, providing abstraction and allowing the database to evolve without breaking existing applications.

A user makes a request in their own language (the external view), which the DBMS translates into the core business language (the conceptual schema), and finally into the machine's language to find the data (the internal schema).

---

When a user submits a query, it travels "downward" through the schema's, with the DBMS mapping it at each step.

1. External to Conceptual Mapping: The user's request, which is framed against their specific external schema (view), is first mapped to the conceptual schema. The DBMS checks the mapping to understand which core data entities and attributes the user's view corresponds to.

2. Conceptual to Internal Mapping: The DBMS then takes the conceptual-level request and maps it to the internal schema. This step translates the logical data request into a specific plan for accessing the physical data files, indexes, and records on the storage device.

Once the data is retrieved, it travels "upward" in the reverse direction, being reformatted at each level.

1. Internal to Conceptual Mapping: The raw data retrieved from the disk is formatted according to the rules in the conceptual-internal mapping to fit the structure of the conceptual schema.

2. Conceptual to External Mapping: The data, now in its conceptual form, is then filtered and reformatted again according to the external-conceptual mapping to match the structure of the specific user's external view before being presented.

Data Independence:

Data independence, is the capacity to change the schema at one level without requiring changes to the schema at the next higher level. It allows for greater flexibility and maintainability of database systems.

Mappings are the essential mechanism that provides data independence in Three schema architecture.

There are two types of data independence:

• Physical Data Independence: Changing the physical schema and structure (e.g., indexing, changing storage location, altering file organization) without affecting the logical schema or any user applications.

  This is achieved by updating the conceptual-internal mapping to reflect the new physical layout.

  Physical data independence is easier to achieve and more common in practice, as low-level storage details are naturally abstracted from users.

• Logical Data Independence: Changing in the logical schema (e.g., adding or removing a data item, changing constraints or data types) without affecting the external schemas or application programs.

  As long as the external view can still be derived from the new conceptual model, only the mapping between the external and conceptual levels needs to be adjusted.

  Logical data independence is difficult to achieve because application programs are often tightly coupled to the structure of the data.