Three-Schema Architecture of DBMS

The Three-Schema Architecture is a database architecture proposed by the ANSI/X3/SPARC committee that provides a clear separation between the user applications and the database system. It consists of three levels or schemas: the External Schema, the Conceptual Schema, and the Internal Schema.

1. External Schema:

The External Schema represents the user view of the data. It defines how individual users or user groups perceive the organization of the data. Each user or application can have its own external schema, customized to meet its specific requirements. This level shields users from changes in the database structure, providing a level of abstraction.

2. Conceptual Schema:

The Conceptual Schema represents the overall logical structure of the entire database as seen by the database administrator. It is an abstraction that defines the relationships between different entities and the constraints on the data. Changes to the database structure at this level impact all users but are transparent to them since their external schemas remain unchanged.

3. Internal Schema:

The Internal Schema defines how the data is stored in the physical storage devices, such as hard drives. It includes details about data structures, file organization, indexing mechanisms, and access paths. Changes at this level are transparent to both the conceptual and external schemas, ensuring that modifications to the physical storage do not affect the logical structure or user views.

Three-Schema Architecture Diagram

User Level (External Schema): Represents various user views or applications (e.g., user A and user B), each with its own customized view of the data.
Logical Level (Conceptual Schema): Represents the logical structure of the entire database, including relationships between entities and constraints.
Physical Level (Internal Schema): Represents the physical storage details, such as file organization, indexing, and access paths.

Physical Data Independence

Physical data independence is one of the key advantages provided by the Three-Schema Architecture. It refers to the ability to modify the physical storage structure of the database without affecting the conceptual or external schemas. This independence ensures that changes made to improve performance or storage efficiency do not require alterations to how users perceive or interact with the data.

Example of Physical Data Independence

Let’s consider a scenario where the internal schema needs optimization for storage space. Suppose the data is initially stored in a table with a fixed-length format:

CREATE TABLE employees (
    employee_id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    hire_date DATE
);

Later, to save storage space, the database administrator decides to compress the data and store it in a more space-efficient manner, perhaps using a different storage format or a different file organization.

-- Modified Internal Schema for Compression
CREATE TABLE employees_compressed (
    employee_id INT,
    full_name VARCHAR(100),
    hire_date DATE
);

Despite this internal change, the conceptual schema and external schemas remain unaffected. Users can still interact with the data using the same queries and applications as before, and the logical structure of the database hasn’t changed.

Logical Data Independence

Logical data independence is another critical aspect of the Three-Schema Architecture. It refers to the ability to modify the conceptual schema without affecting the external schemas or the applications built on top of them. This independence allows for changes in the logical organization of the data without disrupting user views.

Example of Logical Data Independence

Let’s consider an example where the company decides to add a new attribute, “email,” to the employees. Initially, the conceptual schema looks like this:

-- Initial Conceptual Schema
CREATE TABLE employees (
    employee_id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    hire_date DATE
);

Now, the company wants to add an “email” attribute to the conceptual schema:

-- Modified Conceptual Schema
CREATE TABLE employees (
    employee_id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    hire_date DATE,
    email VARCHAR(100)
);

This modification is transparent to the users operating at the external schema level. They can continue to use their applications without any changes because the external schema remains the same.

Conclusion

The Three-Schema Architecture provides a clear and organized approach to database design by separating user views (external schema), the logical structure of the database (conceptual schema), and the physical storage details (internal schema). Physical and logical data independence ensure flexibility and adaptability to changes in the storage structure or logical organization of the data without impacting users and applications. This architecture is foundational for building robust and scalable database systems.

Retrieving data from a database in Java typically involves using the JDBC (Java Database Connectivity) API. The ResultSet interface in JDBC is fundamental for fetching and processing data from a relational database. In this example, I’ll guide you through creating a simple Java program that connects to a database, executes a query, and retrieves the results using the ResultSet interface.

Setting Up the Database

Before you begin, ensure you have a database installed (e.g., MySQL, PostgreSQL, SQLite) and the necessary JDBC driver for that database. For this example, let’s assume you are using MySQL.

Download MySQL Connector/J: Download the MySQL Connector/J JDBC driver from the official MySQL website: MySQL Connector/J.
Create a Database and Table: Create a database and a sample table. Here’s an example SQL script:

CREATE DATABASE sampledb;
USE sampledb;

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

INSERT INTO employees VALUES (1, 'John Doe', 30);
INSERT INTO employees VALUES (2, 'Jane Doe', 25);

Writing the Java Program

Now, let’s create a Java program that connects to the database, executes a query, and retrieves the data using the ResultSet interface.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class DatabaseRetrievalExample {

    // JDBC URL, username, and password of MySQL server
    private static final String JDBC_URL = "jdbc:mysql://localhost:3306/sampledb";
    private static final String USERNAME = "your_username";
    private static final String PASSWORD = "your_password";

    public static void main(String[] args) {
        // Step 1: Load and register the JDBC driver
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            System.err.println("Error loading JDBC driver: " + e.getMessage());
            e.printStackTrace();
            return;
        }

        // Step 2: Establish a connection to the database
        try (Connection connection = DriverManager.getConnection(JDBC_URL, USERNAME, PASSWORD)) {

            // Step 3: Create a Statement object to execute SQL queries
            try (Statement statement = connection.createStatement()) {

                // Step 4: Execute a SQL query
                String query = "SELECT * FROM employees";
                try (ResultSet resultSet = statement.executeQuery(query)) {

                    // Step 5: Process the result set
                    while (resultSet.next()) {
                        int id = resultSet.getInt("id");
                        String name = resultSet.getString("name");
                        int age = resultSet.getInt("age");

                        // Process the retrieved data (you can print or use it as needed)
                        System.out.println("ID: " + id + ", Name: " + name + ", Age: " + age);
                    }
                }
            }

        } catch (Exception e) {
            System.err.println("Database connection error: " + e.getMessage());
            e.printStackTrace();
        }
    }
}

Explanation of the Code

Load and Register JDBC Driver:

Class.forName("com.mysql.cj.jdbc.Driver");

This line loads and registers the MySQL JDBC driver. It’s necessary to load the driver before establishing a connection.
Establish a Connection:

Connection connection = DriverManager.getConnection(JDBC_URL, USERNAME, PASSWORD);

Use the DriverManager.getConnection method to establish a connection to the database. Replace "your_username" and "your_password" with your actual database username and password.
Create a Statement:

Statement statement = connection.createStatement();

The createStatement method creates a Statement object, which is used to execute SQL queries.
Execute a SQL Query:

String query = "SELECT * FROM employees";
ResultSet resultSet = statement.executeQuery(query);

The executeQuery method is used to execute a SQL query and obtain a ResultSet containing the results.
Process the Result Set:

while (resultSet.next()) {
    int id = resultSet.getInt("id");
    String name = resultSet.getString("name");
    int age = resultSet.getInt("age");

    // Process the retrieved data (you can print or use it as needed)
    System.out.println("ID: " + id + ", Name: " + name + ", Age: " + age);
}

The next method of the ResultSet moves the cursor to the next row, and you can retrieve data from each column using appropriate getter methods.

Running the Program:

Compile the Java Program:

javac DatabaseRetrievalExample.java

2.Run the Java Program:

java DatabaseRetrievalExample

If everything is set up correctly, you should see the program connect to the database, execute the query, and print the retrieved data.

Remember to replace the placeholder values for USERNAME and PASSWORD with your actual database credentials. Additionally, make sure the JDBC driver for your specific database is on the classpath when running the program.

Learn Loner

Posts tagged: SQL

What is three scheme architecture of DBMS. Explain this architecture with the help of diagram. Also explain physical data independence and logical data independence with the help of example and structure.