Unit 923: Advanced Database Systems

1. Database Query Optimization

1.1 Introduction to Query Optimization

• Importance of query optimization
• Cost-based vs rule-based optimization

1.2 Query Execution Plans

• Understanding execution plans
• Analyzing and interpreting execution plans
• Tools for viewing query plans

1.3 Indexing Strategies

• Types of indexes: B-tree, bitmap, hash
• Index design best practices
• Covering indexes and included columns
• Index maintenance and fragmentation

1.4 Query Tuning Techniques

• Writing efficient SQL queries
• Use of hints and optimizer directives
• Avoiding common pitfalls: unnecessary joins, subqueries

1.5 Performance Optimization

• Caching and buffering
• Partitioning and parallel query execution
• Monitoring and profiling query performance

2. Advanced Transaction Management

2.1 Concurrency Control

• Lock-based protocols
• Timestamp ordering
• Multiversion concurrency control (MVCC)

2.2 Transaction Isolation Levels

• Read uncommitted, read committed, repeatable read, serializable
• Phenomena: dirty reads, non-repeatable reads, phantom reads

2.3 Deadlock Handling

• Deadlock detection and prevention
• Deadlock resolution strategies

2.4 Distributed Transactions

• Two-phase commit protocol (2PC)
• Three-phase commit protocol (3PC)
• Challenges in distributed transaction management

3. Data Warehousing and Business Intelligence

3.1 Data Warehousing Concepts

• OLTP vs OLAP systems
• Architecture of data warehouses

3.2 ETL Processes

• Extraction, Transformation, Loading steps
• Tools and best practices

3.3 Data Modeling for Analytics

• Star schema design
• Snowflake schema design
• Fact and dimension tables

3.4 OLAP Cubes

• Concepts of OLAP
• Types of OLAP: MOLAP, ROLAP, HOLAP
• Operations: roll-up, drill-down, slice, dice

3.5 Designing and Implementing a Data Warehouse

• Requirements gathering
• Data integration and cleaning
• Performance considerations

4. NoSQL Databases

4.1 Overview of NoSQL

• Definition and characteristics
• CAP theorem overview

4.2 Types of NoSQL Databases

• Document-oriented databases (e.g., MongoDB)
• Key-value stores (e.g., Redis)
• Column-family stores (e.g., Cassandra)
• Graph databases (e.g., Neo4j)

4.3 Use Cases and Comparisons

• When to use NoSQL vs relational databases
• Strengths and limitations of each type

4.4 Data Modeling in NoSQL

• Schema flexibility
• Querying patterns

5. Advanced Data Security and Privacy

5.1 Database Encryption

• Transparent Data Encryption (TDE)
• Column-level and field-level encryption

5.2 Access Control

• Role-based access control (RBAC)
• Attribute-based access control (ABAC)
• Fine-grained access control

5.3 Auditing and Monitoring

• Database activity monitoring
• Audit trails and compliance

5.4 Data Masking and Anonymization

• Static and dynamic data masking techniques
• Privacy preservation methods

5.5 Compliance with Privacy Regulations

• Overview of GDPR, HIPAA
• Implementing compliance controls

6. Big Data Technologies

6.1 Introduction to Big Data

• Characteristics: Volume, Velocity, Variety, Veracity

6.2 Hadoop Ecosystem

• HDFS architecture
• MapReduce programming model
• Related tools: Hive, Pig

6.3 Apache Spark

• In-memory distributed computing
• Spark components: Core, SQL, Streaming, MLlib

6.4 Distributed Databases

• Concepts and examples
• CAP theorem implications

6.5 Real-time Analytics and Scalability

• Stream processing frameworks
• Scaling out vs scaling up

7. Advanced Data Modeling

7.1 Normalization and Denormalization

• Normal forms (1NF to 5NF)
• Trade-offs between normalization and denormalization

7.2 Star and Snowflake Schemas

• Structure and use cases
• Query performance considerations

7.3 Advanced Modeling Patterns

• Slowly Changing Dimensions (SCD)
• Fact constellations
• Data vault modeling

7.4 Modeling for NoSQL and Big Data

• Schema design principles
• Handling semi-structured and unstructured data

8. Database Replication and High Availability

8.1 Replication Strategies

• Master-slave, master-master replication
• Synchronous vs asynchronous replication

8.2 Failover Mechanisms

• Automatic failover
• Manual failover

8.3 Clustering

• Database clusters architecture
• Load balancing

8.4 Sharding

• Horizontal partitioning techniques
• Key-based and range-based sharding

8.5 Disaster Recovery

• Backup strategies
• Recovery point objectives (RPO) and recovery time objectives (RTO)