V1
Primary Key: A primary key is a unique identifier for each record in a database table. It ensures that each row in the table is uniquely identified and indexed for fast retrieval. (Source: “Database System Concepts” by Abraham Silberschatz, Henry F. Korth, and S. Sudarshan)
Foreign Key: A foreign key is a field or a group of fields in a database table that uniquely identifies a record in another table. It establishes a link or a relationship between two tables, enforcing referential integrity constraints. (Source: “Database System Concepts” by Abraham Silberschatz, Henry F. Korth, and S. Sudarshan)
Transaction: A transaction is a logical unit of work that contains one or more database operations, such as insert, update, or delete. It ensures that all operations within the transaction are completed successfully or rolled back if any operation fails, maintaining the ACID properties. (Source: “Database Management Systems” by Raghu Ramakrishnan and Johannes Gehrke)
Query Optimization: Query optimization is the process of selecting the most efficient execution plan for a given query to improve database performance. It involves analyzing various execution strategies, estimating costs, and choosing the optimal plan based on factors such as indexes, join methods, and access paths. (Source: “Database System Concepts” by Abraham Silberschatz, Henry F. Korth, and S. Sudarshan)
View: A view is a virtual table that is derived from one or more base tables or other views. It represents a subset of the data in the database and is defined by a query. Views provide a way to simplify complex queries, enforce security policies, and present data in a customized format. (Source: “Database Management Systems” by Raghu Ramakrishnan and Johannes Gehrke)
Index: An index is a data structure that improves the speed of data retrieval operations on a database table. It is created on one or more columns of a table to facilitate quick lookup of rows based on the indexed column values. Indexes can significantly enhance query performance but may incur overhead during data modification operations. (Source: “Database System Concepts” by Abraham Silberschatz, Henry F. Korth, and S. Sudarshan)
Trigger: A trigger is a database object that automatically executes a set of actions in response to certain events, such as insert, update, or delete operations on a table. Triggers are useful for enforcing business rules, maintaining data integrity, and auditing changes to the database. (Source: “Database Management Systems” by Raghu Ramakrishnan and Johannes Gehrke)
Normalization: Normalization is the process of organizing data in a database to minimize redundancy and dependency by dividing large tables into smaller, related tables and defining relationships between them. It helps to eliminate data anomalies and improve data integrity and consistency. (Source: “Database System Concepts” by Abraham Silberschatz, Henry F. Korth, and S. Sudarshan)
Redundancy: Redundancy refers to the duplication of data in a database, which can lead to wasted storage space, increased maintenance overhead, and data inconsistency. It is important to minimize redundancy to ensure data integrity and avoid anomalies such as update anomalies, insertion anomalies, and deletion anomalies. (Source: “Database Management Systems” by Raghu Ramakrishnan and Johannes Gehrke)
ACID Properties: ACID (Atomicity, Consistency, Isolation, Durability) properties are the four essential characteristics of a transaction in a database management system:
- Atomicity ensures that all operations within a transaction are completed successfully or rolled back as a single unit.
- Consistency ensures that the database remains in a valid state before and after the transaction.
- Isolation ensures that the execution of one transaction does not interfere with the execution of other concurrent transactions.
- Durability ensures that the changes made by a committed transaction are permanently saved and will not be lost, even in the event of a system failure. (Source: “Database System Concepts” by Abraham Silberschatz, Henry F. Korth, and S. Sudarshan)
V2
Primary Key: A unique key in a database table that identifies each record/row. It ensures that each row in the table has a unique value for the primary key column, and it cannot be NULL. (Source: “Database System Concepts,” Chapter 2.1)
Transaction: A logical unit of work performed within a database management system (DBMS) that either completely commits or rolls back, ensuring database consistency. It is a sequence of one or more SQL statements that access and possibly modify the contents of a database. (Source: “Database System Concepts,” Chapter 13.1)
Query Optimization: The process of selecting an efficient execution plan for a given SQL query by the DBMS. The optimizer transforms and rewrites the query, considering factors like table sizes, existence of indexes, and data statistics, to find the most effective way to retrieve the desired data. (Source: “Database System Concepts,” Chapter 12.2)
Index: A data structure that improves the speed of data retrieval operations in a database table. An index creates a separate entry for selected columns of a table (or the entire table) and stores them in a way that facilitates faster access. (Source: “Database System Concepts,” Chapter 8.1)
Foreign Key: A field in one table that refers to the primary key in another table. It establishes a relationship between two tables and ensures referential integrity, meaning that the values in the foreign key column must correspond to existing values in the referenced primary key column. (Source: “Database System Concepts,” Chapter 2.2)
Stored Procedure: A precompiled set of SQL statements that are stored in the database and can be executed by specifying its name. It can accept parameters, return data, and contain complex control-of-flow statements. Stored procedures are used to encapsulate frequently used or complex operations. (Source: “Database System Concepts,” Chapter 18.1)
Trigger: A special type of stored procedure that automatically executes when a specified event occurs in a database, such as an INSERT, UPDATE, or DELETE operation on a table. Triggers are used to maintain data integrity, perform logging, or execute other operations related to data changes. (Source: “Database System Concepts,” Chapter 19.1)
View: A virtual table based on the result of an SQL query. Views do not store data physically; they present a customized perspective of the data from one or more tables. Views can simplify complex queries, hide data complexity, and provide security by restricting access to certain columns or rows. (Source: “Database System Concepts,” Chapter 11.1)
Cursor: A database object that enables row-by-row navigation through a result set. Cursors are used when applications need to process query results one row at a time, rather than retrieving the entire result set at once. They provide a mechanism for controlling the traversal and manipulation of data within a result set. (Source: “Database System Concepts,” Chapter 14.1)
Isolation Level: A property that defines how a transaction controls concurrent read and write operations. Different isolation levels provide varying degrees of concurrency control and data consistency guarantees, potentially affecting performance. Common isolation levels include Read Uncommitted, Read Committed, Repeatable Read, and Serializable. (Source: “Database System Concepts,” Chapter 13.3)