9. Data Modeling and Design

Entity-Relationship (ER) Model

• Representation of entities, attributes, and relationships in a database.
• Entities: Objects or concepts about which data is stored.
• Attributes: Properties or characteristics of entities.
• Relationships: Associations between entities.

Normalization

• Process of organizing data in a database to reduce redundancy and improve data integrity.
• Normal Forms: 1NF, 2NF, 3NF, BCNF.

Indexes

• Data structures that improve the speed of data retrieval operations on a database table.
• Types: Clustered, Non-clustered.

10. Querying and Manipulating Data

Structured Query Language (SQL)

• Standard language for relational database management and manipulation.
• Operations: SELECT, INSERT, UPDATE, DELETE.

Data Definition Language (DDL)

• SQL commands for defining and managing database structures.
• Commands: CREATE, ALTER, DROP.

Data Manipulation Language (DML)

• SQL commands for manipulating data within database tables.
• Commands: INSERT, UPDATE, DELETE.

11. Transaction Management

ACID Properties

• Atomicity, Consistency, Isolation, Durability.
• Guarantees reliability and consistency of database transactions.

Concurrency Control

• Mechanisms to manage simultaneous access to data by multiple transactions.
• Techniques: Locking, Timestamp-based protocols, Multiversion concurrency control.

12. Data Security and Integrity

Access Control

• Regulates who can access the database and what actions they can perform.
• Techniques: Role-based access control (RBAC), Discretionary access control (DAC).

Data Encryption

• Protects sensitive data by converting it into a form that cannot be easily understood without authorization.
• Techniques: Symmetric encryption, Asymmetric encryption.

13. Backup and Recovery

Backup Strategies

• Full Backup, Incremental Backup, Differential Backup.
• Ensure data availability and disaster recovery.

Recovery Techniques

• Rollback: Undo changes made by incomplete transactions.
• Rollforward: Apply changes from backup to restore the database to a consistent state.

14. Distributed Databases

Definition

• Database system in which data is stored and processed across multiple locations or nodes.
• Types: Homogeneous, Heterogeneous.

Replication

• Copying and storing data in multiple locations to improve availability and fault tolerance.
• Types: Snapshot replication, Transactional replication.

Partitioning

• Dividing a database into smaller subsets (partitions) to distribute data across multiple nodes.
• Types: Horizontal partitioning, Vertical partitioning.

15. Database Performance Optimization

Query Optimization

• Techniques for improving the efficiency of database queries.
• Strategies: Indexing, Query restructuring, Table partitioning.

Database Tuning

• Process of adjusting database parameters to improve performance.
• Areas: Memory allocation, Disk I/O optimization, CPU utilization.

Caching

• Storing frequently accessed data in memory to reduce database access time.
• Types: Query result caching, Database caching, Application-level caching.

16. Data Warehousing and Business Intelligence

Data Warehouse

• Central repository for storing and managing data from multiple sources.
• Purpose: Support decision-making processes and business intelligence activities.

ETL (Extract, Transform, Load) Process

• Process of extracting data from various sources, transforming it into a consistent format, and loading it into a data warehouse.

OLAP (Online Analytical Processing)

• Technology for analyzing multidimensional data from data warehouses.
• Features: Drill-down, Slice-and-dice, Roll-up.

17. Big Data and NoSQL Databases

Big Data

• Term for datasets that are too large or complex for traditional data processing applications.
• Characteristics: Volume, Velocity, Variety, Veracity.

NoSQL Databases

• Non-relational databases designed to handle large volumes of unstructured or semi-structured data.
• Types: Document-oriented, Key-value stores, Column stores, Graph databases.

CAP Theorem

• Principle stating that it is impossible for a distributed system to simultaneously provide all three of the following guarantees: Consistency, Availability, Partition tolerance.

18. Database Administration

Database Backup and Recovery

• Strategies for protecting data against loss or corruption and recovering it in case of failure.
• Techniques: Full backup, Incremental backup, Point-in-time recovery.

Database Security

• Measures to protect data from unauthorized access, modification, or destruction.
• Techniques: Encryption, Access control, Auditing.

Database Monitoring and Performance Tuning

• Activities to monitor database performance and optimize system resources.
• Tools: Performance monitoring tools, Query analyzers, Profilers.

19. Data Governance and Compliance

Data Governance

• Framework for managing data assets and ensuring their quality, security, and availability.
• Components: Data policies, Standards, Processes.

Compliance Regulations

• Legal and industry standards that organizations must adhere to regarding data privacy and security.
• Examples: GDPR, HIPAA, PCI DSS.

Data Quality Management

• Processes and technologies for ensuring the accuracy, completeness, and consistency of data.
• Activities: Data profiling, Cleansing, Enrichment.

20. Emerging Trends in DBMS

Blockchain Databases

• Databases that use blockchain technology for secure and transparent data storage and verification.
• Applications: Cryptocurrencies, Supply chain management, Voting systems.

Serverless Databases

• Databases that do not require provisioning, scaling, or managing servers.
• Benefits: Reduced operational overhead, Pay-as-you-go pricing.

Machine Learning and AI in DBMS

• Integration of machine learning algorithms and artificial intelligence techniques into database systems for predictive analytics, anomaly detection, and automated query optimization.