Data and Database Fundamentals
Data and structure
Data are raw facts; information is data given context. Structured data fits rows and columns; semi-structured data (JSON, XML) carries tags with a flexible shape; unstructured data (documents, images, video) has no fixed model. A DBMS stores, retrieves, and secures data. Relational databases use tables of rows (records) and columns (fields), enforced by schemas and linked by keys. NoSQL families - document, key-value, column, graph - trade strict schemas for scale and flexibility.
Keys, SQL, and integrity
A primary key uniquely identifies a row and cannot be null; a foreign key references another table's primary key and enforces referential integrity. Normalization reduces duplication; denormalization intentionally duplicates data for read speed. SQL splits into DDL (CREATE, ALTER, DROP), DML (SELECT, INSERT, UPDATE, DELETE), and DCL (GRANT, REVOKE). CRUD maps to create, read, update, delete. Indexes speed reads and slow writes.
Transactions and analytics
ACID means atomicity (all or nothing), consistency (valid state to valid state), isolation (concurrent transactions do not interfere), and durability (committed data survives failure). A data warehouse holds structured, modeled data for reporting; a data lake holds raw data of many types; ETL extracts, transforms, and loads. Backups enable recovery; archives retain records for compliance.
Worked example
In an Orders table, customer_id is a foreign key to Customers.id. Deleting a customer who still has orders should be blocked or cascaded - that behavior is referential integrity.
Traps
- Reversing primary and foreign key direction.
- Assuming NoSQL means no schema at all.
- Confusing backup with replication, which copies but also copies mistakes.