Embedded Files
DATABASE /
1. TRANSACTIONS
A series of operations which are executed as a single unit.
If one part fails, the entire transaction can be rolled back, ensuring data consistency.
Commands:
BEGIN TRANSACTION: starts a transaction
COMMIT: saves the changes and closes the transaction
ROLLBACK: reverts the changes and closes the transaction
The modifications are kept temporary in a transaction workspace, isolated from the live data. In case of COMMIT, the modifications are applied to the live data and the temporal workspace is deleted. In case of ROLLBACK, the temporary workspace is discarded. While the transaction is being built up, any other queries of the database will access the original data, and after the COMMIT command will the changed data be available.
Transactions are very useful if all changes must happen at once, together, mere simultaneously.
EXAMPLE FOR TRANSACTION:
2. EXECUTION PLANS
Execution plans show how the database processes your query. Most databases support a command like EXPLAIN or EXPLAIN PLAN.
Analyzes the execution plan for a query and suggest possible optimizations.
For large datasets, use it for identifying bottlenecks.
EXAMPLE FOR EXECUTION PLANS:
3. DATABASE ADMINISTRATION
Type of permissions:
Data Permissions: SELECT, INSERT, DELETE, UPDATE
Schema Permissions: CREATE, DROP, ALTER, INDEX
Administrative Permissions: GRANT, REVOKE, BACKUP, SHUTDOWN
EXAMPLE FOR USER PERMISSION MANAGEMENT:
Backup Strategies:
Full Backup: Backs up the entire database
Incremental Backup: Only backs up data that has changed since the last backup
Differential Backup: Backs up data that has changed since the last full backup
Steps to Restore a Backup:
Full Restoration: Use the backup file to recreate the database
Point-in-Time Restoration: Restore to a specific state using incremental backups and logs
Test Restorations: Always test the restoration process to ensure the backup is functional
EXAMPLE FOR FULL BACKUP AND RESTORATION (MYSQL):
Performance Tuning:
optimize queries
monitor indexes and execution plans
Monitoring:
use tools to track resource usage and database health (e.g., disk space, memory usage)
Security:
configure firewalls
use encryption for sensitive data
Database Maintenance:
run regular checks to repair tables
update schemas as necessary
Google Sites
Report abuse