Relational Databases, NoSQL, and Forensic Artifacts

Relational databases organize facts and relationships

A relational design separates different kinds of records into tables and connects them using keys.

Cases -> Evidence -> Acquisitions -> Hashes

One case can have many evidence items, one evidence item can have multiple acquisitions, and one acquisition can have multiple hash records.

Relationship types

One-to-one

One record connects to one record.

One-to-many

One case has many evidence items.

Many-to-many

Many tags can apply to many notes. A linking table, such as note_tags, connects them.

Normalization

Normalization reduces unnecessary duplication. Instead of writing the examiner’s full name in every acquisition row, store the examiner once and reference an ID.

Benefits include fewer inconsistent spellings, easier updates, smaller records, and clearer relationships.

Over-normalization can make simple reads harder, so design is a tradeoff.

Joins

A join combines related rows.

SELECT cases.case_number, evidence.description
FROM cases
JOIN evidence ON evidence.case_id = cases.case_id;

Constraints

Relational databases can enforce rules:

• Unique primary keys
• Required fields
• Valid foreign keys
• Expected values
• Prohibited duplicate combinations

Transactions

A transaction groups changes into one unit. It either completes or rolls back.

The familiar ACID concepts are atomicity, consistency, isolation, and durability. Exact guarantees vary by product and configuration.

SQLite architecture

SQLite is embedded in the application rather than accessed through a separate server.

The main database may be accompanied by:

• -wal write-ahead log
• -shm shared-memory file
• Rollback journal

These sidecar files may contain recent or uncheckpointed changes. Copying only the main file while an application is active can produce an incomplete view.

SQLite pages and records

SQLite stores data in pages that may contain table B-trees, indexes, schema information, overflow data, free space, or freelist content.

Forensic tools may handle allocated records, freelist material, WAL frames, and journals differently. Validation matters.

SQL does not automatically mean purely relational

Modern relational systems may support JSON, arrays, full-text search, graph features, and vectors. NoSQL systems may support transactions, schemas, joins, and SQL-like languages.

The labels describe primary data models, not absolute capability boundaries.

Document databases

MongoDB stores BSON documents in collections. Documents contain fields, nested documents, and arrays.

Advantages can include flexible record shapes and natural mapping to application objects.

Forensic considerations include dynamic schemas, nested records, object identifiers, replication, cloud architecture, and storage-engine behavior.

Key-value databases

A key identifies a value.

session:1058 -> user data

Redis supports keys mapped to strings and richer structures such as hashes, lists, sets, sorted sets, streams, and JSON.

Forensic considerations include in-memory data, persistence files, expiration, eviction, replication, binary values, and application-defined key names.

Wide-column databases

Systems such as Cassandra and HBase organize data around partition keys and column families for large distributed workloads.

Graph databases

Systems such as Neo4j emphasize nodes, relationships, and properties. They are useful when traversing connections is the primary task.

Choosing a model

Use relational databases when relationships, consistency, transactions, and structured querying are central.

Consider documents when records are naturally nested and flexible.

Consider key-value when fast lookup by a known key dominates.

Consider graph when relationship traversal is the central operation.

Real systems often combine models.

Why SQLite can fit ByteCase

SQLite is a reasonable future local-data option because it is embedded, portable, transactional, queryable, and suitable for connected records.

That does not mean every ByteCase output should disappear into one database. A future database can coordinate structured data while readable reports and exportable files remain available outside the application.

Examiner takeaway

Identify:

1. Database product and version
2. Storage model
3. Main and sidecar files
4. Application schema
5. Timestamps and encodings
6. Deleted-data behavior
7. Encryption or compression
8. Parsing-tool limitations