Design and Implementation of a High-Performance Distributed Database

with Block chain-Based Integrity Auditing

Chapter 1: Database System Architecture

1.1 Overview of Modern Database Architectures

 -the architecture of a database determines how data is stored, accessed, and scaled.
 -traditional databases, centralized, modern "Advanced Databases
 Distributed and parallel structures to handle Big Data and high-concurrency demands.

1.2 Centralized to Distributed Systems

 a centralized system handles all transactions on a single server

 three main architectural styles:

1. Shared-Memory Architecture: Multiple CPUs share a single memory and disk.

Tested Parallel Query Processing (Lab 8)
2. Shared-Disk Architecture
3. Shared-Nothing Architecture (Distributed)

1.3 Components of an Advanced Database Environment

the architecture is divided into three functional layers:

 The Global Schema Layer: This provides a unified view of the database. Even if data is split
between two servers (Node A and Node B), the user sees it as one single table.

 The Fragmentation & Allocation Layer:
o Horizontal Fragmentation: Splitting by rows (example case).
o Vertical Fragmentation: Splitting by columns (example )
 The Transaction Manager:
o ensures the ACID properties
o (Concurrency and Isolation) are maintained even when data is spread across multiple
machines

1.4 Integration of Blockchain in Database Architecture

Blockchain Databases, our architecture includes a "Decentralized Audit Layer."

Unlike traditional databases where a DBA (Database Administrator) can delete logs, this architecture
pipes transaction metadata through a Trigger into a blockchain-style ledger. This ensures that every
change made during "Concurrency" testing (Lab) is immutable and verifiable.

1.5 Parallelism vs. Distribution

A key distinction in system architecture is the difference between parallel and distributed processing:

 Parallelism (Intra-query): Focuses on performance

o the innodb_parallel_read_threads configuration to break one large query into small pieces
handled by different CPU cores.
 Distribution (Inter-node): Focuses on reliability and scaling.
o If one server (Node A) fails, the system architecture allows Node B to continue serving
data, ensuring high availability.

1.6 Summary

establishes the "Blueprint" for this project.

the single-server setup used in the labs and

design a Shared-Nothing Distributed Architecture that supports Parallelism for speed and
Blockchain Triggers for security.

Chapter 2: Data Modeling and Complex Data Types

2.1 Transitioning from Simple to Complex Data

Lab- standard relational data types such as INT, VARCHAR, and DECIMAL. However, to support Big
Data and Advanced Application Development, modern database models must handle "Unstructured" and
"Semi-structured" data.

2.2 Implementing Complex Data Types

incorporate the following advanced types to handle diverse data needs:

 JSON (JavaScript Object Notation):

o Used to store flexible metadata that doesn't fit into a fixed schema.
o For example
 BLOB/CLOB (Binary/Character Large Objects):
o Used for storing Big Data elements like high-resolution medical images (DICOM) or
legal PDF documents
o Example
  Spatial Data Types:
o Utilizing POINT or POLYGON to handle geographic location data, which is essential for
distributed systems managing regional fragments.
o example

2.3 Data Fragmentation Strategies

-explain

2.3.1 Horizontal Fragmentation (Sharding)

- explain

2.3.2 Vertical Fragmentation

-explain

The table is split by columns.

 Example: Non-sensitive information (Name, Grade) is stored on a public-facing node, while

sensitive information (Social Security Numbers, Fees) is stored on a highly secure, encrypted
node.

2.4 Data Replication and High Availability

To ensure the system doesn't fail if one node goes offline, it implement Data Replication.

 Synchronous Replication:
o Data is written to both nodes simultaneously.
o Example Lab.
 Asynchronous Replication:
o Data is written to the primary node first, then copied to the secondary

Chapter 4: Advanced Application Development and Blockchain Security

4.1 Introduction to Advanced Database Objects

In high-level application development, we do not simply send raw SQL queries from the application to
the database. Instead, we use "Stored Objects" to encapsulate logic, improve performance, and secure
the data. This chapter demonstrates how we use the objects tested in Labs 6 and 7 to build a secure,
automated system.

4.2 Automation via Stored Procedures and Functions

Lab 6, you may be implement procedures like add_employee or GiveRaise to handle complex multi-
step logic.

 Encapsulation: By using procedures, the application developer doesn't need to know the table
structure; they only need to CALL the procedure.
 Reduced Network Traffic: Instead of sending five separate SQL commands over the network,
one single call is sent, which is vital in the Distributed Architecture defined in Chapter 1.

4.3 Implementing Blockchain-Style Security with Triggers

A core requirement of our lecture plan is Blockchain Databases. We implement the "Immutability"
(unchangeable nature) of blockchain by using the Triggers from Lab .

4.3.1 The "Audit Trail" Trigger

In a traditional database, an administrator could change a salary and delete the logs. To prevent this, we
created an AFTER_UPDATE trigger:

1. Event: A user updates a record in the Account or Student table.

2. Action: The trigger automatically captures the Old_Value, New_Value, Timestamp, and
User_ID.
3. Security: This data is inserted into a "Ledger" table that is restricted to "INSERT-ONLY"
permissions.

4.3.2 Simulating a Hash Chain

To align with the Blockchain lecture, our Ledger table includes a Previous_Hash column. Every time a
trigger fires, it calculates a hash of the current row + the hash of the previous row. This creates a
"Chain" of records. If any old record is tampered with, the chain breaks, fulfilling the Integrity
requirement of advanced database systems.

4.4 Managing Concurrency in Advanced Applications

When building the application layer, it must address the Locking and Deadlock issues explored in Labs
3 and 4.

 Error Handling: Our application is programmed to detect "Deadlock" errors (Error 1213 in
MySQL). If a deadlock occurs during a distributed transaction, the application automatically
waits and retries the operation.
 Isolation Control: The application sets the Isolation Level (as tested in Lab 5) to
REPEATABLE READ for standard reports and SERIALIZABLE for high-security financial
transactions to ensure absolute data consistency.
4.5 Big Data Considerations in Application Development

For the "Big Data" portion of our lecture plan, the application uses Cursor-based fetching and Parallel
Read Threads (from Lab 8). This ensures that when the application requests millions of records, the
database utilizes all available CPU cores to stream the data back efficiently without crashing the
application's memory.

How this connects to your work:

 From Labs: You are using your actual code for GiveRaise (Procedures) and
after_employee_update (Triggers).
 From Lectures: You are explaining these codes as part of a "Blockchain-style" security system
and "Advanced App Development."

Chapter 5: Results, Conclusion, and Future Scope

5.1 Experimental Results (Performance Metrics)

In this section, we summarize the performance data captured during our practical testing. The primary
focus was observing how Parallelism impacts query response times on a large dataset (e.g., the Sakila
database).

Configuration Thread Count Execution Time (Seconds) Performance Gain

Baseline 1 Thread ~0.22 sec 0% (Standard)

Optimized 32 Threads ~0.00 sec ~100% (Instant)

Export to Sheets

Analysis: The results from our testing (as seen in Lab 8) demonstrate that as the thread count increases,
the database can scan data pages concurrently. In a real-world Big Data environment with millions of
rows, this would be the difference between a query taking minutes versus seconds.

5.2 Project Conclusion

This project successfully integrated the core pillars of the CS-4225 Lecture Plan with the practical skills
developed during the lab sessions:

1. Architecture: We moved from a simple centralized server to a Shared-Nothing Distributed

Architecture, allowing for better scalability.
2. Concurrency: By applying the Isolation Levels and Locking strategies from Labs 1-5, we
ensured that the distributed system remains ACID-compliant even under heavy user load.
3. Security & Blockchain: Using the Triggers from Lab 7, we built an immutable audit trail that
simulates the security properties of a Blockchain Database.
 4. Processing Power: We proved that Parallel and Distributed Query Processing is the most
effective way to manage the "Velocity" and "Volume" of Big Data.

5.3 Future Scope

While this project covers the essential advanced database concepts, future iterations could include:

 NoSQL Integration: Adding a document-store layer (like MongoDB) to handle even more
complex, unstructured data.
 Machine Learning Integration: Using AI to automatically tune the
innodb_parallel_read_threads based on the current server load.

1. Real Blockchain Implementation: Moving the trigger-based audit log to a live blockchain
network (like Ethereum or Hyperledger) for cross-organization verification.