Unit
- Database: A structured collection of data organized for efficient retrieval, update, and management. It provides a systematic way to store and access data, reducing redundancy and maintaining consistency across applications.
- DBMS: A Database Management System is software that interacts with users, applications, and the database itself to capture and analyze data. Examples include
MySQL,PostgreSQL, andOracle Database. - Key benefits: Data independence, reduced data redundancy, improved data integrity, enhanced security, and concurrent access management. These benefits make DBMS essential for modern data-driven applications.
- Data models: Conceptual (high-level user view), logical (implementation-oriented), and physical (storage details). The relational model dominates due to its mathematical foundation and simplicity.
- History: The field evolved from file systems in the 1960s to hierarchical and network models, then to the relational model proposed by E. F. Codd in 1970, and later to object-oriented and NoSQL systems.
- Types: Relational (SQL-based), NoSQL (document, key-value, column-family, graph), NewSQL (scalable relational), and in-memory databases. Each type suits different workload requirements.
- Database languages: DDL (Data Definition Language) for schema creation, DML (Data Manipulation Language) for data operations, DCL (Data Control Language) for permissions, and TCL (Transaction Control Language) for transaction management.
- Three-schema architecture: Internal schema (physical storage), conceptual schema (logical structure), and external schema (user views). This separation provides logical data independence, allowing schema changes without affecting user applications.
- ACID properties: Atomicity ensures transactions are all or nothing; Consistency maintains database rules; Isolation prevents concurrent transactions from interfering; Durability guarantees committed transactions persist despite failures. These properties are fundamental for reliable transaction processing in relational databases.
- Normalization: A process to minimize data redundancy and dependency by organizing fields and tables. Normal forms (1NF, 2NF, 3NF, BCNF) provide guidelines. Proper normalization reduces anomalies in insert, update, and delete operations while improving data integrity.
- Indexing: A data structure that improves the speed of data retrieval operations on a table at the cost of additional writes and storage. Common index types include B-tree, hash, and bitmap indexes. Choosing the right index is critical for query performance optimization.
- Data integrity constraints: Rules enforced by the DBMS to ensure data accuracy and consistency. Primary key constraints ensure uniqueness, foreign key constraints maintain referential integrity, check constraints validate data values, and unique constraints prevent duplicates.
Code Example
SQL
CREATE TABLE employees (id INT PRIMARY KEY, name VARCHAR(100), department_id INT);This DDL command creates a table for employees with a primary key and a foreign key reference to departments. The VARCHAR type stores variable-length strings.
System Architecture
Pattern: Three-Schema Architecture
Components:
- External Level: Views and user interfaces that present data tailored to specific user groups.
- Conceptual Level: Logical structure representing the entire database content, independent of storage.
- Internal Level: Physical storage details like file organization and indexing.
Quick Revision
- A database is a structured data collection; a DBMS manages it.
- Data models provide abstraction layers from conceptual to physical.
- The three-schema architecture ensures data independence.
- DBMS languages include DDL, DML, DCL, and TCL.