Unit
- Database definition: A database is an organized collection of structured data stored electronically with a database management system (DBMS) providing tools for defining, creating, querying, updating, and administering data. Modern DBMSs support multiple data models including relational, document, key-value, and graph, each optimized for specific use cases and access patterns.
- Relational model: Data is organized into tables (relations) with rows (tuples) and columns (attributes). Relationships between tables are established through foreign keys. This model enforces ACID properties (Atomicity, Consistency, Isolation, Durability) and uses Structured Query Language (SQL) for data manipulation. Most enterprise applications rely on relational databases like PostgreSQL, MySQL, and SQL Server.
- NoSQL data models: NoSQL databases shun the rigid relational schema for flexibility and scalability. Document stores like MongoDB store JSON-like documents with varying fields. Key-value stores like Redis offer simple get/put operations with low latency. Column-family stores like Apache Cassandra handle wide-column data for analytics. Graph databases like Neo4j model relationships as first-class entities for connected data.
- Data independence: The three-schema architecture separates physical storage from logical views. The internal schema describes how data is stored on disk. The conceptual schema defines the global logical structure. External schemas represent user views. This separation allows changing the physical design without affecting applications, and vice versa.
- Database languages: Data Definition Language (DDL) defines schema structures (CREATE, ALTER, DROP). Data Manipulation Language (DML) performs insert, update, delete, and select operations. Data Control Language (DCL) manages permissions (GRANT, REVOKE). Transaction Control Language (TCL) handles commits and rollbacks.
- DBMS architecture: A DBMS includes a query processor (parser, optimizer, executor), storage manager (buffer manager, file manager, transaction manager), and metadata catalog. The query processor translates SQL into execution plans. The storage manager handles disk I/O, caching, and log recovery. The transaction manager ensures concurrent access integrity.
- Client-server model: Database systems typically follow a client-server paradigm where multiple clients connect to a central database server. The server process manages connections, executes queries, and returns result sets. Connection pooling improves scalability by reusing established connections.
- Database administrators (DBAs): DBAs manage database availability, performance, security, and capacity. Operational tasks include monitoring, backup and recovery, schema changes, user administration, and query tuning. The role spans development, production, and disaster recovery environments.
System Architecture
Pattern: Client-Server Database Architecture
Components:
- Client layer: Applications and tools that send SQL queries and receive result sets over a network protocol (e.g., TCP/IP, Unix socket).
- Server process: Accepts connections, parses and optimizes queries, manages transactions, and accesses storage. Example: PostgreSQL's postmaster process.
- Storage engine: Manages data files, indexes, transaction logs, and buffer cache. Different engines (InnoDB, MyISAM, RocksDB) offer trade-offs in durability, compression, and performance.
- Shared buffer pool: A memory area that caches frequently accessed data pages to reduce disk reads. Size directly impacts query performance and needs careful tuning.
Code Example
SQL
-- Create a relational table for customers
CREATE TABLE customers (
customer_id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Insert sample data
INSERT INTO customers (name, email)
VALUES ('Alice Johnson', 'alice@example.com');
-- Query with a JOIN
SELECT c.name, o.total
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.status = 'completed';This DDL creates a simple customer table with auto-incrementing primary key and a unique constraint on email. The DML shows an INSERT and a SELECT with a JOIN, demonstrating core relational operations.
Quick Revision
- Databases organize data using relational or NoSQL models, each with distinct advantages.
- The three-schema architecture provides data independence between physical storage and user views.
- SQL is the standard language for relational databases, encompassing DDL, DML, DCL, and TCL.
- DBMS architecture includes query processing, storage management, and transaction management components.
- DBAs are responsible for availability, performance, security, and capacity planning.