Database Fundamentals
SQLite and Relational Databases: The Simple Explanation
A straightforward explanation of SQLite, tables, rows, columns, keys, relationships, SQL, and how relational databases differ from spreadsheets and NoSQL databases.
The one-sentence explanation
SQLite is a relational database stored in a regular file.
Applications can use it to organize many connected records without running a separate database server.
Is it a 3D Excel spreadsheet?
That is directionally useful, but a more accurate mental model is:
A workbook with multiple structured sheets connected by shared IDs.
The extra “dimension” is the relationships between records, not literal depth.
A relational database is designed around tables, rows, columns, data types, unique identifiers, relationships, queries, and consistency rules.
Simple forensic example
Cases table
| case_id | case_number | examiner |
|---|---|---|
| 1 | 26-01482 | M. McBride |
Evidence table
| evidence_id | case_id | description |
|---|---|---|
| 101 | 1 | Samsung mobile device |
| 102 | 1 | USB storage device |
The case_id in Evidence connects each item to the correct case. You do not have to repeat the entire case record in every evidence row.
Basic parts
Table
A named group of similar records, such as Cases, Evidence, Examinations, Hashes, or Notes.
Row
One record.
Column
One defined field.
Primary key
A value that uniquely identifies a row.
Foreign key
A field that points to a related row in another table.
What SQL does
SQL creates, reads, updates, and deletes relational data.
SELECT description
FROM evidence
WHERE case_id = 1;
That means: show the evidence descriptions connected to case 1.
Why SQLite differs from PostgreSQL or MySQL
SQLite stores its database in a local file and runs inside the application.
Common server-based relational databases include:
- PostgreSQL
- MySQL
- MariaDB
- Microsoft SQL Server
- Oracle Database
SQLite is common in mobile apps, desktop apps, browsers, local caches, settings, message stores, and forensic artifacts.
Is every .db file SQLite?
No. Extensions such as .db, .sqlite, and .sqlite3 are clues, not proof. Confirm the file format before interpreting it.
Common relational databases
- SQLite
- PostgreSQL
- MySQL
- MariaDB
- Microsoft SQL Server
- Oracle Database
- IBM Db2
- Microsoft Access
- DuckDB
What is the opposite?
There is no single opposite. NoSQL is an umbrella term for models that do not rely primarily on traditional relational tables.
Common categories:
- Document databases
- Key-value databases
- Wide-column databases
- Graph databases
Examples include MongoDB, CouchDB, Redis, Cassandra, Neo4j, and DynamoDB.
Why examiners see SQLite so often
SQLite is embedded, portable, and does not require a server. Applications use it for messages, contacts, history, accounts, events, media metadata, locations, and settings.
Examiners may also need to understand write-ahead logs, journal files, deleted records, schemas, timestamps, binary values, and encrypted fields.
Main takeaway
A relational database is not one giant spreadsheet. It is a set of structured tables connected through identifiers, with a query language and rules that help keep data consistent.