1.1. Data Abstraction

It provides different views of the database for different user groups. This ensures that a user or application only needs to interact with the data relevant to them, without needing to understand the underlying complexities of the database structure.

1.2. Data Independence

A key goal is to achieve data independence, which is the capacity to change the database storage structures without affecting the database's logic and vice versa. This includes logical data independence (changes at the conceptual level do not affect the external level) and physical data independence (changes at the internal level do not affect the conceptual level).

1.3. Efficient Data Management

By logically separating different aspects of the database system, the architecture allows for more efficient data management. This includes optimizing physical storage and access paths at the internal level without compromising the logical relationships and integrity constraints defined at the conceptual level.

1.4. Security and Integrity

The architecture supports the definition of security and integrity rules at the conceptual level, which can be enforced across all external views. This ensures that data remains consistent and secure across the database, regardless of the specific user interactions at the external level.

1.5. Support for Multiple Views

The architecture allows for the creation of multiple views at the external level, catering to the specific needs and access rights of different users. This enables personalized data presentation and interaction, enhancing user experience and data privacy.

1.6. Simplified Database Design

By separating concerns among the three levels, the architecture simplifies the process of database design. Designers can focus on logical structure at the conceptual level without worrying about physical storage details until later in the design process.

2.1 User Views

User views define what data is visible to different users and how this data is organized. For instance, a salesperson might see data related to customers and sales, whereas a manager might view aggregated sales reports.

2.2 View Schema

The view schema describes the schema of each user view. It defines the specific structure of the data as seen by the users, including relationships between different data elements.

3.1 Conceptual Schema

The conceptual schema is a detailed, technology-agnostic framework that outlines the database's structure and constraints without delving into how data is stored physically. It includes entities, data types, relationships, and integrity rules.

3.2 Data Independence

Data independence is a key feature at the conceptual level, allowing changes to the physical level without affecting the external views. Logical data independence shields users from changes in the logical structure of the database.

4.1 Physical Storage

Physical storage details the specific methods and structures used to store and retrieve data efficiently on physical media. This includes data storage formats, indexing techniques, and optimization of disk space.

4.2 Internal Schema

The internal schema defines the physical storage structure of the database. It specifies the physical data units, storage organization, compression methods, and access paths for optimizing performance and storage utilization.

5.1 External/Internal Mapping

External/internal mapping connects the external views to the conceptual schema, ensuring that the user views are consistent with the logical database structure.

5.2 Conceptual/Internal Mapping

Conceptual/internal mapping links the conceptual schema with the internal schema, facilitating the transformation of logical data structures into physical formats.

6.1 Defining a View (External Level)

CREATE VIEW SalesReport AS
SELECT ProductID, SUM(QuantitySold) AS TotalSales
FROM Sales
GROUP BY ProductID;

This SQL statement creates a view named 'SalesReport', allowing users to see the total sales per product without knowing the underlying database structure.

6.2 Creating a Table (Conceptual Level)

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(100),
    Email VARCHAR(100)
);

This statement defines a 'Customers' table, representing how data is logically structured in the database without specifying physical storage details.

6.3 Index Creation (Internal Level)

CREATE INDEX idx_CustomerName
ON Customers (Name);

Here, an index on the 'Name' column of the 'Customers' table is created to improve query performance, demonstrating an aspect of the internal level concerned with physical data access.