Overview
A computer database is an organized collection of structured data stored electronically in a computer system, typically managed by a database management system (DBMS). Databases enable efficient storage, retrieval, modification, and deletion of data, supporting a wide range of applications from small-scale personal records to large-scale enterprise systems. They serve as the foundational layer for modern information technology, ensuring data consistency, integrity, and accessibility for multiple users and concurrent transactions.
1 History
1.1 Early file-based systems
Before the advent of databases, data was stored in flat files managed by individual application programs. Each program defined its own file formats and access methods, leading to data redundancy, inconsistency, and difficulty in sharing information across applications. File-based systems lacked centralized control and were prone to data integrity problems.
1.2 Hierarchical and network databases
In the 1960s, the first database models emerged. The hierarchical model organized data in a tree-like structure with parent-child relationships, used in systems such as IBM’s Information Management System (IMS). The network model, defined by the CODASYL conference, allowed more complex many-to-many relationships through a graph structure. Both models required explicit navigation paths, making queries cumbersome.
1.3 Relational model (1970s)
In 1970, Edgar F. Codd proposed the relational model, which represented data as relations (tables) with rows and columns. This model separated logical data organization from physical storage, enabling querying through a high-level language like SQL. The relational model became dominant due to its simplicity and mathematical foundation, leading to commercial systems such as IBM Db2 and Oracle.
1.4 Object-oriented and NoSQL databases (2000s)
As applications grew more complex, object-oriented databases emerged in the 1990s to directly store objects from programming languages. The rise of the internet and big data in the 2000s spurred the development of NoSQL databases, which prioritized scalability, flexibility, and performance for unstructured and semi-structured data, often sacrificing strict consistency.
2 Types of databases
2.1 Relational databases
Relational databases store data in tables with predefined schemas. They enforce data integrity through constraints and support complex queries via SQL.
2.1.1 Key characteristics (tables, SQL)
Each table consists of rows (records) and columns (attributes). SQL (Structured Query Language) is used for defining, querying, and manipulating data. Relationships between tables are established using foreign keys.
2.1.2 Examples (MySQL, PostgreSQL, Oracle)
Popular relational database systems include MySQL (open source, widely used in web applications), PostgreSQL (advanced open-source with strong compliance), and Oracle Database (commercial, enterprise-grade with extensive features).
2.2 NoSQL databases
NoSQL databases are designed for large-scale, distributed systems and handle various data models other than the relational tabular format.
2.2.1 Document stores
Document stores, such as MongoDB, store data as documents (e.g., JSON or BSON). Each document can have a different structure, allowing schema flexibility. Queries are often based on document fields or full-text search.
2.2.2 Key-value stores
Key-value stores, like Redis and DynamoDB, associate each key with a value. They are highly performant for simple lookups and caching, but lack complex querying capabilities.
2.2.3 Column-family stores
Column-family stores, such as Apache Cassandra, organize data by column families rather than rows. They are optimized for wide-column tables and high write throughput, commonly used in time-series data and analytics.
2.2.4 Graph databases
Graph databases, like Neo4j, represent data as nodes (entities) and edges (relationships). They excel at traversing relationships, making them suitable for social networks, recommendation engines, and fraud detection.
2.3 Object-oriented databases
Object-oriented databases integrate database capabilities with object-oriented programming languages. They store objects directly, preserving complex data types and behavior without mapping to relational tables. Examples include ObjectDB and db4o.
2.4 Distributed databases
Distributed databases spread data across multiple physical locations, often in different geographic regions, to improve availability, fault tolerance, and performance. They may use replication or sharding and employ consensus protocols like Paxos or Raft to maintain consistency.
3 Database architecture
3.1 Database models
3.1.1 Conceptual, logical, physical schemas
Database design involves three levels of abstraction: conceptual (high-level user view, often using an ER model), logical (detailed representation of data structures independent of physical details), and physical (how data is actually stored on storage media, including indexes and partitioning).
3.1.2 Entity-relationship model
The entity-relationship (ER) model is a conceptual tool that describes data in terms of entities (objects or concepts) and relationships between them. It is typically represented as an ER diagram (ERD) and used during requirements analysis and conceptual design.
3.2 Storage structures
3.2.1 Data files
Data files store the actual records of a database. They can be organized as heap files (unordered), sorted files, or hashed files, each with different trade-offs for insertion, search, and update performance.
3.2.2 Indexes
Indexes are auxiliary data structures that speed up data retrieval. Common types include B+‑trees (balanced tree for range queries) and hash indexes (for equality lookups). Indexes add overhead on write operations.
3.2.3 Buffer management
The buffer manager manages the transfer of data pages between disk and main memory (buffer pool). It uses replacement policies (e.g., LRU, clock) to minimize I/O and improve performance. It is a critical component for efficient query execution.
3.3 Query processing
3.3.1 Parsing and optimization
When a query is submitted, the parser checks syntax and converts it into an internal representation (e.g., a parse tree). The query optimizer then generates multiple execution plans and selects the one with the lowest estimated cost, considering indexes, join algorithms, and statistics.
3.3.2 Execution plans
An execution plan is a sequence of operations (scans, joins, sorts, etc.) that the database engine follows to retrieve or modify data. Plans can be viewed as tree structures and sometimes manually tuned using hints.
4 Database management systems (DBMS)
4.1 Functions of a DBMS
4.1.1 Data definition
The DBMS provides a data definition language (DDL) to define schemas, tables, indexes, and constraints. DDL statements (e.g., CREATE, ALTER) are processed and stored in the system catalog.
4.1.2 Data manipulation
A data manipulation language (DML) enables users to insert, update, delete, and query data. SQL is the most common DML, supporting both procedural and declarative queries.
4.1.3 Transaction management
Transactions group a set of operations into a single unit of work. The DBMS ensures ACID properties (Atomicity, Consistency, Isolation, Durability) to maintain data integrity despite failures or concurrent access.
4.1.4 Concurrency control
Concurrency control mechanisms (e.g., locking, multi-version concurrency control) prevent conflicts when multiple transactions access the same data simultaneously. They ensure that the database remains consistent and that transactions appear to execute in isolation.
4.1.5 Recovery
Recovery techniques (e.g., write-ahead logging, checkpointing) guarantee durability and allow the database to be restored to a consistent state after a crash. The DBMS uses logs to redo committed transactions and undo uncommitted ones.
4.2 DBMS components
4.2.1 Query processor
The query processor handles parsing, validation, optimization, and execution of SQL statements. It interacts with the storage engine and metadata manager to produce query results.
4.2.2 Storage engine
The storage engine manages physical data storage and retrieval. It implements data file formats, indexes, buffer management, and transaction support (including logging and locking). Examples include InnoDB (MySQL) and WiredTiger (MongoDB).
4.2.3 Metadata manager
The metadata manager maintains the system catalog (data dictionary) containing information about schemas, tables, columns, indexes, users, and privileges. This metadata is used by other components for parsing and optimization.
5 Database design
5.1 Requirements analysis
The first phase involves gathering and documenting the data needs of the organization or application. Stakeholders are interviewed, and business rules are identified to define what data must be stored and how it will be used.
5.2 Conceptual design (ER diagrams)
Conceptual design translates requirements into a high-level data model, typically an entity-relationship diagram. Entities, attributes, and relationships are defined without considering physical implementation. This model serves as a blueprint for logical design.
5.3 Logical design (normalization)
Logical design maps the conceptual model to a database schema, often relational. Normalization techniques (1NF, 2NF, 3NF, BCNF) are applied to eliminate redundancy and update anomalies. The result is a set of tables with primary and foreign keys.
5.4 Physical design
Physical design determines how the logical schema is implemented on storage hardware. This includes choosing indexes, partitioning strategies, file organizations, and storage parameters to meet performance requirements such as query response times and throughput.
6 Data security and integrity
6.1 Access control
Access control mechanisms restrict who can view or modify data. DBMSs implement user authentication, authorization (roles, privileges), and sometimes row-level security. Common models include discretionary (DAC) and mandatory (MAC) access control.
6.2 Encryption
Encryption protects data at rest (in storage) and in transit (over networks). DBMSs may offer transparent data encryption (TDE) for database files, as well as support for SSL/TLS connections. Encryption keys must be managed securely.
6.3 Integrity constraints
6.3.1 Primary keys
A primary key uniquely identifies each row in a table. It enforces entity integrity, meaning the key must be unique and not null. DBMS automatically creates an index on the primary key.
6.3.2 Foreign keys
A foreign key creates a link between tables, enforcing referential integrity. It ensures that values in one table’s column match the primary key values of another table. Operations like delete and update may have cascade rules.
6.3.3 Check constraints
Check constraints define conditions that each row must satisfy (e.g., age > 0). They are used to enforce domain integrity at the column level, preventing invalid data entry.
7 Applications
7.1 Business (ERP, CRM)
Enterprise resource planning (ERP) systems and customer relationship management (CRM) platforms rely on databases to manage inventory, finances, sales, and customer interactions. Relational databases are common, with transaction processing and reporting capabilities.
7.2 Web applications (e-commerce, social media)
Modern web applications use databases to handle users, products, orders, and content. E-commerce sites need high concurrency and consistency, while social media platforms often employ a mix of relational and NoSQL databases to manage profiles, posts, and friend networks.
7.3 Scientific databases (bioinformatics, astronomy)
Scientific databases store large volumes of experimental or observational data. Examples include genomic sequence databases (GenBank) and astronomical catalogs (Sloan Digital Sky Survey). They require scalable storage, complex querying, and often support for spatial or time-series data.
8 Future trends
8.1 Cloud databases
Cloud databases offer on-demand, scalable, and managed database services (e.g., Amazon RDS, Google Cloud Spanner). They provide elasticity, automated backups, and high availability. Multi-cloud and hybrid-cloud deployments are gaining traction.
8.2 NewSQL
NewSQL databases aim to combine the scalability of NoSQL systems with the ACID guarantees of relational databases. Examples include Google Spanner and CockroachDB. They use distributed architectures and strong consistency models.
8.3 AI and database automation
Artificial intelligence is being applied to database management for tasks such as automated tuning (index recommendation, query optimization), anomaly detection, and self-healing. AI may also enable natural-language querying and knowledge graph integration.