Integrating SQL, Understanding NoSQL, and MongoDB Fundamentals

Posted on Nov 1, 2025 in Mathematics and Computer Science

Database Programming: Integrating SQL with Host Languages

Introduction to SQL Integration

This section covers how SQL is used in host languages like C and Java, utilizing techniques such as Embedded SQL, Dynamic SQL, JDBC, and SQLJ. It demonstrates essential steps like connecting to a database, declaring host variables, retrieving data using cursors, and executing dynamic queries.

Embedded SQL (C Example)

Embedded SQL allows direct integration of SQL statements within a host language program (e.g., C).

EXEC SQL BEGIN DECLARE SECTION;
int sid;
char sname[20];
EXEC SQL END DECLARE SECTION;
EXEC SQL SELECT name INTO :sname FROM Sailors WHERE sid = :sid;

:sname is a host variable used inside the SQL statement.
SQLSTATE and SQLCODE are used to handle errors and exceptions.

Using Cursors for Multi-Row Queries

Cursors are essential for processing query results that return multiple rows sequentially:

DECLARE CURSOR
OPEN the cursor
FETCH INTO variables (typically inside a loop)
CLOSE the cursor

Dynamic SQL Execution

Dynamic SQL allows constructing and executing SQL statements at runtime.

char query[] = "DELETE FROM Sailors WHERE rating > 5";
EXEC SQL PREPARE stmt FROM :query;
EXEC SQL EXECUTE stmt;

JDBC (Java Database Connectivity)

JDBC is the standard Java API for dynamic SQL execution and database interaction.

Connection conn = DriverManager.getConnection(...);
PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM Books WHERE author=?");
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
  System.out.println(rs.getString(1));
}

SQLJ (Java Embedded SQL)

SQLJ provides a mechanism for embedding static SQL statements directly into Java code.

#sql { SELECT title INTO :title FROM Books WHERE id = :id };

Key Takeaways on SQL Integration

Use cursors for processing multiple rows returned by a query.
JDBC is highly dynamic and portable across different databases.
SQLJ is best suited for static, precompiled queries.
Embedded SQL allows direct use of host variables within SQL statements.

NoSQL Databases: Concepts and Architecture

Introduction to NoSQL

NoSQL databases serve as an alternative to traditional Relational Database Management Systems (RDBMS), optimized for handling large, distributed, and schema-flexible data. This section highlights the CAP Theorem, the BASE model, and the major NoSQL database types.

Why Choose NoSQL?

Effective handling of big data volumes.
Facilitates horizontal scaling (sharding).
Provides schema flexibility.
Helps avoid the object-relational impedance mismatch common in RDBMS.

The CAP Theorem

The CAP Theorem states that a distributed data store can only fully guarantee two of the following three properties:

Consistency (All nodes see the same data at the same time)
Availability (Every request receives a response, without guarantee that it is the latest version)
Partition Tolerance (The system continues to operate despite arbitrary message loss or failure of part of the system)

BASE Model (vs. ACID)

The BASE model is often adopted by NoSQL systems, prioritizing availability and partition tolerance over immediate consistency:

Basically Available
Always in a Soft state (State changes over time, even without input)
Eventually consistent

This model is used in systems where strong consistency is not critical for operation.

Major Types of NoSQL Databases

Key-Value Stores: Examples: Redis, DynamoDB
Document Databases: Examples: MongoDB, CouchDB
Column-Family Stores: Examples: Cassandra, HBase
Graph Databases: Examples: Neo4j

NoSQL Use Cases and Trade-offs

Common Use Cases

Session management
Real-time analytics
Recommendation engines
Social networks and relationship mapping

Advantages (Pros)

Easy horizontal scaling
High availability
Flexible schema design

Disadvantages (Cons)

Fewer strong consistency guarantees
Weaker transactional support (compared to ACID systems)

MongoDB: A Document Database Deep Dive

MongoDB Fundamentals

MongoDB is a leading document-oriented NoSQL database. It stores data in flexible, JSON-like documents using the BSON (Binary JSON) format. Documents are organized into collections and support flexible fields, nesting, and arrays.

Basic Document Structure

{
  "_id": 1,
  "name": "Alice",
  "cities": ["London", "New York"],
  "address": { "city": "London", "zip": "E1 6AN" }
}

CRUD Operations in MongoDB Shell

Common Create, Read, Update, and Delete (CRUD) operations:

db.users.insertOne({name: "Alice", age: 30}); // Create
db.users.find({name: "Alice"}); // Read
db.users.updateOne({name: "Alice"}, {$set: {age: 31}}); // Update
db.users.deleteOne({name: "Alice"}); // Delete

Key Query Operators

MongoDB supports rich querying capabilities using various operators:

Comparison: $gt (greater than), $lt (less than), $eq (equal), $in
Logical: $and, $or
Arrays: $elemMatch, $size

Projections (Field Selection)

Projections limit the fields returned in the query result:

db.users.find({}, {name: 1, age: 1, _id: 0});

MongoDB Schema Design Strategies

Embedding: Suitable for 1-to-many relationships and optimizing for fast read performance.
Linking (Referencing): Provides a more normalized structure, better for data reusability and handling very large subdocuments.