Database, Network, and Application Integration

Introduction

Most useful software systems are not isolated programs. A banking application stores accounts in a database, obtains exchange rates from a network service, exchanges data in JSON, and presents results through a web, desktop, or mobile interface. Application integration is the discipline of connecting these components so that data moves reliably, securely, and meaningfully between them.

This chapter introduces practical integration using relational databases, REST APIs, structured data, and application frameworks. Examples use Python because its syntax is compact, but the principles apply to C++ and Java. Equivalent libraries exist in all three languages: JDBC and Spring for Java, ODBC and Qt for C++, and DB-API, Flask, FastAPI, and Django for Python.

Integration is not merely a matter of making connections. A professional application must also manage authentication, validation, errors, transactions, concurrency, resource cleanup, logging, and privacy. A system that works only on a developer’s computer is not yet a reliable application.

Learning Objectives

After completing this chapter, students should be able to:

1. Explain the roles of databases, APIs, serialization formats, and application frameworks.
2. Design a small relational schema and access it safely from a program.
3. Construct and consume REST requests using appropriate HTTP methods.
4. Serialize and validate structured data such as JSON.
5. Distinguish presentation, application, and data-access responsibilities.
6. Apply transactions, parameterized queries, authentication, and error handling.
7. Evaluate the limitations and risks of integrated applications.
8. Build a small application that combines a database with an external or local API.

1. The Integration Problem

An integrated application usually contains several layers:

• Presentation layer: user interface, web pages, desktop windows, or mobile screens.
• Application or service layer: business rules, workflows, authorization, and coordination.
• Data-access layer: SQL statements, repositories, database connections, and transaction management.
• External-service layer: clients for payment, mapping, messaging, identity, or other APIs.
• Persistence layer: relational or non-relational storage.

A common flow is:

User interface → application service → database or external API → application service → formatted response.

Layering reduces coupling. For example, a user interface should not contain raw SQL, and a database driver should not decide whether a customer is allowed to place an order. Each layer should have a clear responsibility.

Integration contracts

A contract specifies how two components communicate. It may define:

• operation names and URLs;
• request and response fields;
• data types and units;
• authentication requirements;
• error formats;
• timeout and rate-limit behavior;
• compatibility and versioning rules.

An API contract is both a technical and organizational agreement. Changing a field from customer_id to client_id without coordination can break multiple applications.

2. Relational Databases and Program Access

A relational database stores data in tables consisting of rows and columns. A row represents an entity or relationship instance, while a column represents an attribute. A primary key uniquely identifies a row. A foreign key refers to a key in another table and enforces a relationship.

For an order system, a simple design might contain:

The relationship between orders and products is many-to-many, resolved by order_items. This avoids repeating product columns in the orders table.

SQL operations

The four basic data operations are commonly summarized as CRUD:

• Create: INSERT
• Read: SELECT
• Update: UPDATE
• Delete: DELETE

A query joining customers and orders might be:

SELECT c.name, o.order_id, o.status
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id
WHERE c.email = ?;

The question mark is a parameter placeholder. It is not replaced by string concatenation.

Parameterized queries and SQL injection

This code is unsafe:

email = input("Email: ")
query = "SELECT * FROM customers WHERE email = '" + email + "'"

A malicious input could alter the meaning of the SQL statement. The safe form separates the SQL structure from the value:

cursor.execute(
    "SELECT customer_id, name FROM customers WHERE email = ?",
    (email,)
)

The database driver treats the parameter as data rather than executable SQL. Parameterization is essential for all values supplied by users or external systems.

Connections, cursors, and transactions

A program generally performs the following steps:

1. Open a connection.
2. Create a cursor or statement object.
3. Execute parameterized SQL.
4. Read results or inspect affected rows.
5. Commit successful changes or roll them back after failure.
6. Close resources.

A transaction groups operations into one logical unit. The common ACID properties are:

• Atomicity: all operations succeed or none take effect.
• Consistency: constraints remain valid before and after the transaction.
• Isolation: concurrent transactions do not produce unacceptable interference.
• Durability: committed data survives an appropriate system failure.

For example, transferring money requires both a debit and a credit. Committing only one operation would corrupt the account balances. A transaction should therefore contain both operations and roll back if either fails.

In Python with SQLite:

import sqlite3

connection = sqlite3.connect("shop.db")
try:
    with connection:
        connection.execute(
            "UPDATE accounts SET balance = balance - ? WHERE account_id = ?",
            (100.00, 1)
        )
        connection.execute(
            "UPDATE accounts SET balance = balance + ? WHERE account_id = ?",
            (100.00, 2)
        )
finally:
    connection.close()

The with connection block commits when successful and rolls back if an exception occurs. In a real financial system, additional checks are required, such as verifying sufficient funds, preventing negative balances, and using appropriate monetary data types.

Data modelling and integrity

Good schemas use constraints to protect data:

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
    stock INTEGER NOT NULL CHECK (stock >= 0)
);

NOT NULL, CHECK, UNIQUE, primary-key, and foreign-key constraints move some validation into the database. Application validation remains necessary because it can provide better messages and enforce business rules, but database constraints provide a final integrity boundary.

For money, decimal or integer minor units should normally be used rather than binary floating-point values. For example, storing 1099 cents as an integer avoids many representation problems associated with values such as 10.99.

3. REST APIs and HTTP

A REST-style API exposes resources through URLs and uses HTTP methods to communicate intent. Typical methods include:

An operation is idempotent when repeating the same request has the same intended effect as performing it once. This does not mean that every response is identical; it describes the intended state change.

HTTP status codes

Important status codes include:

• 200 OK: successful request with a response body;
• 201 Created: resource created successfully;
• 204 No Content: successful request without a response body;
• 400 Bad Request: malformed or invalid input;
• 401 Unauthorized: authentication is absent or invalid;
• 403 Forbidden: identity is known but not permitted;
• 404 Not Found: resource does not exist;
• 409 Conflict: request conflicts with current state;
• 429 Too Many Requests: rate limit exceeded;
• 500 Internal Server Error: unexpected server failure.

The distinction between 401 and 403 is important: the first concerns authentication, while the second concerns authorization.

Consuming an API

A Python client using the requests package might be written as follows:

import requests

response = requests.get(
    "https://api.example.org/weather",
    params={"city": "Nairobi"},
    timeout=5
)
response.raise_for_status()
data = response.json()
print(data["temperature"])

A timeout is essential. Without one, a program may wait indefinitely for a slow or unavailable service. Production clients should also consider bounded retries for temporary failures, exponential backoff, rate limits, circuit breakers, and structured logging. Retrying a GET is generally safer than automatically retrying a payment POST unless an idempotency key is supported.

Designing a small API

A Flask endpoint can expose customer data:

from flask import Flask, jsonify, request

app = Flask(__name__)

@app.get("/customers/<int:customer_id>")
def get_customer(customer_id):
    customer = find_customer(customer_id)
    if customer is None:
        return jsonify({"error": "customer_not_found"}), 404
    return jsonify(customer), 200

A production endpoint would additionally authenticate the request, authorize access to the customer, validate output fields, avoid leaking confidential information, and use a stable error schema.

4. Structured Data and Serialization

Structured data has an explicit organization that software can parse. JSON is widely used because it is readable and supported by almost every modern language.

Example JSON:

{
  "order_id": 1042,
  "customer": {
    "name": "Amina Otieno",
    "email": "[email protected]"
  },
  "items": [
    {"product_id": 7, "quantity": 2}
  ],
  "status": "confirmed"
}

JSON supports objects, arrays, strings, numbers, Boolean values, and null. It does not inherently define dates, currency, units, or database relationships. Those conventions must be documented by the API.

Serialization converts an in-memory object into a transferable representation. Deserialization performs the reverse operation. A common error is to trust the shape and type of received data without validation. A field that should be an integer may arrive as a string; a required object may be absent; or an attacker may supply unexpectedly large input.

Validation should check:

• required fields;
• data types and ranges;
• string length and format;
• allowed enumeration values;
• nested object structure;
• unknown or forbidden fields where appropriate.

Schema tools such as JSON Schema, Pydantic, Java Bean Validation, and C++ serialization libraries can make these checks explicit.

XML remains important in some enterprise and financial systems, particularly where established schemas, namespaces, and document-signing standards are used. CSV is useful for simple tabular exchange but is less expressive than JSON and requires careful handling of quoting, character encoding, and missing values.

5. Application Frameworks and Architecture

A framework supplies reusable structure and services for application development. Examples include:

• Desktop: Qt for C++, JavaFX for Java, and Tkinter or PySide for Python.
• Web: Spring Boot, Django, Flask, FastAPI, and ASP.NET-related ecosystems.
• Service integration: HTTP clients, message brokers, background workers, and scheduling libraries.

Frameworks provide routing, configuration, dependency management, validation, templating, testing support, and security facilities. They do not automatically produce secure or well-designed systems. Developers must understand the behavior of the framework and configure it correctly.

Separation of concerns

A useful small architecture might contain:

HTTP route / user interface
          ↓
Application service: business rules and orchestration
          ↓
Repository: database operations
          ↓
Database

Application service → external API client → REST service

The service layer should not depend directly on button widgets or HTTP request objects. A repository should focus on persistence rather than deciding whether a discount is allowed. This separation makes components easier to test and replace.

Configuration and environments

Connection strings, API keys, and deployment settings should be supplied through environment variables or a secret-management system, not committed to source code. Separate development, testing, and production configurations reduce accidental data loss. Database migrations should be version-controlled so that schema changes can be reproduced.

6. Security, Reliability, and Governance

Integration enlarges the attack surface because data crosses process and network boundaries.

Security practices

1. Use HTTPS for network communication.
2. Store passwords using slow, salted password-hashing algorithms; never store plaintext passwords.
3. Use short-lived tokens and protect refresh tokens.
4. Apply least privilege to database accounts and service identities.
5. Validate input and use parameterized queries.
6. Do not place secrets in source code, URLs, or ordinary logs.
7. Return generic external errors while recording useful internal diagnostics.
8. Apply authorization at the server, not only in the interface.
9. Limit request size and rate where abuse is possible.
10. Patch dependencies and scan them for known vulnerabilities.

Authentication answers “Who are you?” Authorization answers “What are you allowed to do?” Confusing these concepts can expose records across users or organizations.

Reliability practices

Networks fail, services become slow, and responses can be incomplete. Robust applications use:

• explicit timeouts;
• appropriate retry policies;
• correlation or request identifiers;
• idempotency keys for repeatable creation requests;
• queues for work that need not be immediate;
• graceful degradation when optional services fail;
• health checks and monitoring;
• backups and tested recovery procedures.

Retries must be bounded. A retry storm can overload an already failing service. Backoff increases the delay between attempts, while jitter adds variation so that many clients do not retry simultaneously.

Privacy and governance

Applications should collect only necessary personal data, define retention periods, restrict access, and support correction or deletion where applicable law requires it. Logs may contain email addresses, tokens, medical information, or financial details; logging more data is not automatically better observability. Organizations should document data ownership, processing purposes, third-party services, and breach-response procedures.

7. Worked Application: Community Clinic Appointment Service

The following is an illustrative case.

Background

A community clinic records patients and appointments in a relational database. Staff need a web interface, while a reminder service needs access to appointment information through an API.

Problem

The clinic’s original desktop program stores appointments locally. Staff cannot access current schedules from another workstation, duplicate appointments are possible, and the reminder service has no controlled interface.

Analysis

The system requires:

• patient and appointment tables;
• prevention of overlapping appointments for the same clinician;
• authenticated staff access;
• a REST endpoint for authorized reminders;
• audit records for creation, changes, and cancellation;
• protection of patient information.

Approach

A simplified schema is:

CREATE TABLE patients (
    patient_id INTEGER PRIMARY KEY,
    full_name TEXT NOT NULL,
    phone TEXT NOT NULL
);

CREATE TABLE appointments (
    appointment_id INTEGER PRIMARY KEY,
    patient_id INTEGER NOT NULL,
    clinician_id INTEGER NOT NULL,
    start_time TEXT NOT NULL,
    end_time TEXT NOT NULL,
    status TEXT NOT NULL CHECK (status IN ('booked', 'cancelled', 'completed')),
    FOREIGN KEY (patient_id) REFERENCES patients(patient_id)
);

Before inserting an appointment, the service validates that the end time is after the start time and checks for an overlapping booking. For half-open time intervals, an existing appointment overlaps a proposed interval when:

Here all four variables represent timestamps in the same time zone and format. The condition excludes an appointment ending exactly when another begins, which is usually desirable for consecutive slots. The check must be performed within a suitable transaction or protected by a database-level concurrency strategy; checking in application code alone can allow two simultaneous requests to pass before either inserts its row.

The API might expose:

• POST /appointments to create an appointment;
• GET /appointments?date=2026-04-12 to list appointments;
• PATCH /appointments/{id} to change status;
• GET /patients/{id}/appointments for authorized staff.

A request body could be:

{
  "patient_id": 31,
  "clinician_id": 8,
  "start_time": "2026-04-12T09:00:00+03:00",
  "end_time": "2026-04-12T09:30:00+03:00"
}

The service validates the input, checks authorization, verifies that the patient and clinician exist, detects conflicts, writes the appointment and audit event in a transaction, and returns 201 Created. If the slot is already occupied, it returns 409 Conflict rather than silently overwriting data.

The reminder worker calls the API or reads from a controlled service interface. It should receive only the data required for reminders. Full clinical information should not be included.

Outcome

Staff obtain a shared schedule, duplicate bookings are reduced, and the reminder service uses a documented contract. Monitoring can measure failed requests, response times, reminder delivery, and database errors without exposing unnecessary patient data.

Learning points

The important achievement is not simply connecting a web page to a database. It is the coordinated design of schema constraints, transactions, API contracts, validation, authorization, privacy, and operational monitoring.

8. Testing Integrated Applications

Unit tests should test business rules without requiring a live network or production database. Integration tests should verify real boundaries, such as SQL queries against a temporary database or API calls against a controlled test service. End-to-end tests should exercise a complete user workflow.

Useful test categories include:

• valid and invalid JSON;
• missing and extra fields;
• authentication and authorization failures;
• SQL constraint violations;
• transaction rollback;
• duplicate requests;
• network timeout and malformed responses;
• concurrent appointment creation;
• migration from one schema version to another.

External APIs should usually be mocked or replaced by a sandbox during automated tests. Contract tests can verify that a producer and consumer agree on field names, types, and status codes.

9. Contemporary Developments

Modern integration increasingly uses containerized deployment, automated continuous integration, API description formats such as OpenAPI, event-driven messaging, and observability platforms. Message brokers are useful when producers and consumers should operate independently, but they introduce concerns about ordering, duplication, delivery guarantees, and eventual consistency.

Cloud-managed databases and serverless functions can reduce infrastructure administration, but they do not remove responsibility for access control, data location, cost management, backups, and vendor dependence. Artificial intelligence services may be integrated through APIs, but outputs require validation, privacy assessment, human oversight in sensitive domains, and protection against prompt or data leakage. An AI response should not be treated as a trusted database fact merely because it is expressed fluently.

Best-Practice Checklist

Before releasing an integrated application, confirm that:

• database operations use parameterized queries;
• transactions cover related updates;
• inputs and outputs are validated;
• API methods and status codes are documented;
• timeouts and bounded retries are configured;
• authentication and authorization are distinct and tested;
• secrets are managed outside source code;
• personal data is minimized and protected;
• logs contain correlation identifiers but not sensitive secrets;
• dependencies, migrations, backups, and recovery have been tested;
• unit, integration, and failure-path tests exist.

Chapter Summary

Application integration connects user interfaces, application logic, databases, and external services through explicit contracts. Relational databases provide structured persistence, constraints, joins, and transactions. Safe database access requires parameterized queries, appropriate schema design, resource management, and transaction control.

REST APIs use HTTP methods, resource-oriented URLs, status codes, and structured representations such as JSON. Reliable clients need timeouts, carefully designed retries, validation, and clear error handling. Frameworks accelerate development but do not replace architectural judgment or security knowledge. Separation of presentation, service, repository, and integration responsibilities improves maintainability and testing.

Professional integration also requires authentication, authorization, privacy protection, least privilege, monitoring, versioning, and tested recovery. The clinic case demonstrates that a successful solution combines database integrity, API design, concurrency control, and governance rather than merely connecting screens to tables.

Key Terms

API: A defined interface through which software components communicate.

Authentication: Verification of a user or system identity.

Authorization: Determination of what an authenticated identity may do.

CRUD: Create, read, update, and delete operations.

Foreign key: A column that references a key in another table.

HTTP: The protocol commonly used for web communication.

Idempotent operation: An operation whose repeated execution has the same intended state effect as one execution.

JSON: A lightweight structured data format based on objects and arrays.

Primary key: A value or set of values that uniquely identifies a database row.

Repository: A component that encapsulates data-persistence operations.

REST: An architectural style commonly using resources and HTTP semantics.

Serialization: Conversion of in-memory data into a transferable representation.

SQL injection: An attack that manipulates unsafe dynamically constructed SQL.

Transaction: A group of database operations treated as one logical unit.

Validation: Checking that data has the required structure, type, and allowed values.

Timeout: A limit on how long an operation may wait.

Web framework: Reusable software structure for developing web applications.

Review Questions

Short Answer Questions

1. What is the difference between a primary key and a foreign key?
2. Why are parameterized SQL queries safer than string concatenation?
3. What is the purpose of an HTTP 201 status code?
4. Distinguish authentication from authorization.
5. Name two advantages of separating a repository from an application service.

Long Answer Questions

1. Explain the ACID properties of a database transaction and apply them to a money-transfer operation.
2. Describe how a REST API should validate, process, and respond to a request that creates an order.
3. Compare JSON, XML, and CSV for exchanging structured data.
4. Explain how timeouts, retries, backoff, and idempotency contribute to reliable network integration.
5. Discuss the security and privacy responsibilities involved in integrating a healthcare application.

Analytical / Application Questions

1. Design tables and API endpoints for a library borrowing system. Identify keys, constraints, and suitable HTTP methods.
2. An application creates duplicate orders when a client retries after a timeout. Analyze the cause and propose an idempotency-based solution.
3. Two users can book the same appointment because both availability checks succeed simultaneously. Explain why this occurs and design a transaction or concurrency-control strategy to prevent it.

Multiple Choice Questions with Answers

Question 1

Which practice most directly prevents SQL injection?

A. Using parameterized queries

B. Adding more user-interface fields

C. Increasing database storage

D. Disabling database indexes

Correct Answer: A. Using parameterized queries

Question 2

Which HTTP status code normally indicates that a resource was successfully created?

A. 200

B. 201

C. 404

D. 500

Correct Answer: B. 201

Question 3

What is the main purpose of a database transaction?

A. To format JSON responses

B. To group related operations into a controlled unit

C. To replace authentication

D. To increase screen resolution

Correct Answer: B. To group related operations into a controlled unit

Question 4

Which HTTP method is normally used to retrieve a resource?

A. GET

B. POST

C. PATCH

D. DELETE

Correct Answer: A. GET

Question 5

What does authentication establish?

A. Whether a request is encrypted

B. The identity of a user or system

C. Whether a database row is indexed

D. The format of a CSV file

Correct Answer: B. The identity of a user or system

Question 6

Which JSON value represents an ordered collection?

A. Object

B. Array

C. Boolean

D. Null

Correct Answer: B. Array

Question 7

Why should an API client set a timeout?

A. To prevent indefinite waiting for a response

B. To make all responses successful

C. To remove the need for validation

D. To convert SQL into JSON

Correct Answer: A. To prevent indefinite waiting for a response

Question 8

Which layer should normally contain business rules such as whether a discount is allowed?

A. Presentation layer

B. Application or service layer

C. Network cable

D. Database index only

Correct Answer: B. Application or service layer

Question 9

A response with status 403 most commonly means that the client is authenticated but

A. the resource was created

B. the request has no syntax

C. the action is not permitted

D. the server has returned JSON

Correct Answer: C. the action is not permitted

Question 10

For appointment intervals, which condition detects overlap between an existing interval and a proposed interval?

A. existing_start < proposed_end AND proposed_start < existing_end

B. existing_start > proposed_end AND proposed_start > existing_end

C. existing_start = proposed_start OR existing_end = proposed_end

D. existing_start < proposed_start AND existing_end < proposed_end

Correct Answer: A. existing_start < proposed_end AND proposed_start < existing_end