Extra Notes

  • In the context of Database Management Systems (DBMS), overhead refers to the additional computational, storage, or operational resources required to perform tasks beyond the minimal cost of basic operations. Overhead can arise from various factors depending on the design, implementation, and use of the database system.
  • A partial dependency occurs when a non-prime attribute (an attribute not part of any candidate key) depends on part of a composite candidate key. Partial dependencies leading to repeated values of non-prime attributes. Transitive dependencies causing indirect relationships to be stored unnecessarily. Redundancy refers to repeated or unnecessary storage of data caused by dependencies. What Is Redundant: Attributes (and their values) that can be inferred from other attributes using functional dependencies.
  • We need to redo committed transactions during recovery because their changes may not have been fully written to the database before the system failure. This situation arises due to the use of buffering: updates are often stored temporarily in memory (buffers) and may not have reached the disk when the system crashes. The redo phase ensures that all updates from committed transactions are correctly applied to the database, preserving the durability property of ACID transactions.

  • A secondary index improves query performance by enabling quick access to rows based on non-primary key columns, avoiding full table scans. It supports flexible query patterns, range queries, and optimization for WHERE, ORDER BY, and JOIN clauses. While it speeds up reads, it can increase storage and maintenance costs, especially for write-heavy operations. However, a secondary index increases update overhead because:

    • When a row is updated, any indexed column involved must also update its corresponding entry in the secondary index.
    • This requires additional disk I/O and processing time, especially if there are multiple secondary indexes on the table.

    Thus, while secondary indexes speed up queries, they can slow down UPDATE operations.

  • Logical replication