Q6Data Base Management System

(a) JDBC and the connectivity process

JDBC (Java Database Connectivity) is a standard Java API, part of the java.sql package, that provides a uniform interface for Java applications to connect to and communicate with a wide variety of relational databases regardless of the specific database vendor. Application code written against the JDBC interfaces can work with different databases (MySQL, Oracle, PostgreSQL, SQL Server, and so on) simply by plugging in the appropriate vendor-supplied JDBC driver, without changing the Java application logic itself.

The process of connecting a Java application to a database using JDBC involves the following steps.

• Load and register the JDBC driver: the appropriate driver class for the target database is loaded (in modern JDBC 4.0+ drivers this happens automatically via service discovery, though it can also be done explicitly).
• Establish a connection: a Connection object is obtained by calling DriverManager.getConnection(url, username, password), where the URL identifies the database host, port, and database name.
• Create a Statement or PreparedStatement: a Statement object (or a PreparedStatement for parameterized/precompiled queries) is created from the Connection to hold and send SQL commands.
• Execute the SQL query: the statement's executeQuery() method is called for SELECT statements (returning a ResultSet), or executeUpdate() for INSERT/UPDATE/DELETE/DDL statements (returning the number of affected rows).
• Process the ResultSet: for queries, the returned ResultSet is iterated row by row using next(), and column values are extracted using typed getter methods such as getString() or getInt().
• Close the connection and resources: the ResultSet, Statement, and Connection are closed (or handled via try-with-resources) to release database and network resources.

import java.sql.*;

public class JdbcExample {
    public static void main(String[] args) throws Exception {
        String url = "jdbc:mysql://localhost:3306/college";
        try (Connection con = DriverManager.getConnection(url, "root", "password");
             Statement stmt = con.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT RollNo, Name FROM Student")) {

            while (rs.next()) {
                System.out.println(rs.getInt("RollNo") + " " + rs.getString("Name"));
            }
        }
    }
}

This example shows the complete lifecycle: a connection is opened against the college database, a Statement executes a SELECT query, the ResultSet is iterated to print each student's roll number and name, and the try-with-resources block ensures the Connection, Statement, and ResultSet are all closed automatically once the block finishes.

The JDBC architecture itself is organized as a set of standard interfaces (Connection, Statement, PreparedStatement, CallableStatement, ResultSet, and so on) defined in the java.sql package, plus a vendor-specific driver that implements these interfaces against the wire protocol of a particular database engine. This driver-based design is what allows the same application code, written purely against the standard JDBC interfaces, to run against different databases simply by changing the driver JAR and the connection URL, without touching a single line of the Java logic that issues queries and processes results - this is directly analogous to how ODBC serves the same driver-abstraction role for non-Java applications.

(b)(i) DDL - Data Definition Language

DDL commands are used to define, modify, and remove the structure of database objects such as tables, schemas, and indexes; they act on the schema itself rather than on the data stored inside it. The main DDL commands are CREATE (to define a new database object), ALTER (to modify the structure of an existing object), and DROP (to permanently remove an object and all its data).

CREATE TABLE Student (
    RollNo INT PRIMARY KEY,
    Name VARCHAR(50),
    Branch VARCHAR(20)
);

ALTER TABLE Student ADD Email VARCHAR(50);

DROP TABLE Student;

The CREATE TABLE statement defines a new Student table along with its columns, data types, and the primary key constraint; ALTER TABLE modifies the existing table by adding a new Email column; DROP TABLE removes the table definition entirely along with all the rows it contains. DDL statements typically cause an implicit commit in most database systems, since they change the schema rather than transactional data.

Besides CREATE, ALTER, and DROP, DDL also includes the TRUNCATE command, which removes all rows from a table very quickly (typically by deallocating the data pages directly rather than deleting rows one at a time) while keeping the table's structure intact for future use; unlike DELETE, which is a DML operation that can be run with a WHERE clause and rolled back within a transaction, TRUNCATE cannot selectively remove rows and, like other DDL commands, is generally not undoable through a transaction rollback in most database systems. This distinction is a common point of confusion for students, since both DELETE (with no WHERE clause) and TRUNCATE appear to empty a table, but they belong to different SQL sub-languages with different transactional and performance characteristics.

(b)(ii) DML - Data Manipulation Language

DML commands are used to manipulate and query the actual data stored inside the tables defined by DDL, without changing the structure of those tables. The main DML commands are SELECT (to retrieve data), INSERT (to add new rows), UPDATE (to modify existing rows), and DELETE (to remove rows).

INSERT INTO Student (RollNo, Name, Branch) VALUES (101, 'Aman', 'EE');

UPDATE Student SET Branch = 'EEE' WHERE RollNo = 101;

DELETE FROM Student WHERE RollNo = 101;

SELECT Name, Branch FROM Student WHERE Branch = 'EEE';

Here INSERT adds a new student row, UPDATE changes the Branch value of the row with RollNo 101, DELETE removes that row, and SELECT retrieves the Name and Branch of students belonging to the EEE branch. Unlike DDL, DML operations are executed within transactions and can typically be rolled back if not yet committed, which is central to maintaining database consistency during concurrent data manipulation.

A further useful distinction within JDBC itself is between a plain Statement and a PreparedStatement, both of which can be used to run DDL and DML commands from Java. A Statement sends a complete, literal SQL string to the database exactly as written, which is fine for one-off or fixed commands but is inefficient and unsafe when the same command is executed repeatedly with different values, since the database must parse and plan the query afresh every time, and string-concatenating user-supplied values directly into the SQL text opens the door to SQL injection attacks. A PreparedStatement instead uses a parameterized SQL string containing placeholder markers (question marks), which is compiled and its execution plan cached once by the database; actual values are then bound to the placeholders via typed setter methods (setInt, setString, and so on) before each execution, which is both faster for repeated execution and inherently safe against injection, since parameter values are never interpreted as part of the SQL syntax.

PreparedStatement ps = con.prepareStatement("INSERT INTO Student (RollNo, Name, Branch) VALUES (?, ?, ?)");
ps.setInt(1, 102);
ps.setString(2, "Riya");
ps.setString(3, "EC");
ps.executeUpdate();

This example shows the same INSERT operation performed safely and efficiently through a PreparedStatement, binding RollNo, Name, and Branch values to the three placeholders before executing the update - the same pattern is used for UPDATE and DELETE statements with variable parameters, and is considered standard practice in production JDBC code in preference to building SQL strings manually with a plain Statement.