A data warehouse is a central repository of integrated data collected from multiple sources, designed for analysis, reporting, and decision-making. Explanation: Example Architecture:

Backup and recovery refer to the process of creating copies of data (backup) to prevent data loss and restoring data (recovery) in case of system failures, corruption, or accidental deletion. Explanation: Example SQL Backup Command (MySQL): sqlCopyEditBACKUP DATABASE mydb TO DISK = ‘C:\backup\mydb.bak’; Example SQL Restore Command: sqlCopyEditRESTORE DATABASE mydb FROM DISK = ‘C:\backup\mydb.bak’;

A stored procedure is a precompiled SQL program that is stored in the database and can be executed repeatedly by calling it. It helps in reusability, security, and efficiency by reducing the need for repetitive query execution. Explanation: A stored procedure consists of SQL statements that can be executed as a unit. It is beneficial…

A trigger is an automatic SQL procedure that executes in response to certain events on a table, such as INSERT, UPDATE, or DELETE. Example SQL Trigger: sqlCopyEditCREATE TRIGGER update_salary BEFORE UPDATE ON Employees FOR EACH ROW SET NEW.salary = GREATEST(NEW.salary, 30000); This ensures salaries never drop below 30,000.

A view is a virtual table based on the result of an SQL query that does not store data physically but provides a logical representation of it. Example SQL View: sqlCopyEditCREATE VIEW HighSalaryEmployees AS SELECT name, salary FROM Employees WHERE salary > 50000; Querying the view: sqlCopyEditSELECT * FROM HighSalaryEmployees; Returns only employees with a…

SQL Injection is a type of cyber attack where malicious SQL code is inserted into a query to manipulate or steal data from a database. Example of Vulnerable Code: phpCopyEdit$query = “SELECT * FROM users WHERE username = ‘$username’ AND password = ‘$password'”; Preventing SQL Injection (Using Prepared Statements in PHP): phpCopyEdit$stmt = $pdo->prepare(“SELECT *…

ACID properties (Atomicity, Consistency, Isolation, Durability) ensure reliable processing of transactions in a database. Example: A bank transfer must withdraw and deposit money together (Atomicity), ensure correct balances (Consistency), prevent interference from other transactions (Isolation), and remain stored permanently (Durability).

A transaction is a sequence of one or more SQL operations executed as a single unit of work, ensuring data consistency using ACID properties. Transactions are used to ensure that multiple database operations either all succeed or all fail. If an error occurs, the transaction can be rolled back to its previous state. Example SQL…

Data integrity refers to the accuracy, consistency, and reliability of data stored in a database by enforcing constraints and validation rules. Data integrity ensures that data remains accurate and valid throughout its lifecycle. It is enforced using: Example Constraint for Data Integrity: sqlCopyEditCREATE TABLE Employees ( emp_id INT PRIMARY KEY, salary DECIMAL(10,2) CHECK (salary >…

Query optimization is the process of improving the efficiency of SQL queries to minimize execution time and resource usage. Optimizing queries helps improve database performance by using techniques like: Example SQL Optimization: Instead of: sqlCopyEditSELECT * FROM Orders WHERE customer_name = ‘Alice’; Use an indexed column: sqlCopyEditSELECT * FROM Orders WHERE customer_id = 1; This…