Answer
📁 Data Files - The Core Data
Data files contain the actual customer orders, products, and user accounts organized in tables. Each table row is a record (e.g., Order #12345), each column is an attribute (order_date, total_amount). Without data files, you have no database.
📊 Index Files - Fast Search
Index files store references to data locations based on specific columns. Example: An index on 'email' column lets you find user 'john@email.com' instantly instead of scanning all 1 million rows. They speed up queries but can be rebuilt from data files.
📝 Transaction Log Files - The Recovery Key
Transaction logs record every change: 'User #123 placed order at 1:30 AM', 'Payment processed at 1:31 AM'. To restore to 1 hour ago, you'd load the last full backup and replay transaction logs up to that time. This is how point-in-time recovery works.
🗂️ System Catalog/Metadata Files
These store database structure: table definitions, column types, constraints. Example: 'orders table has columns: order_id (INT), user_id (INT), total (DECIMAL)'. The database needs this to understand its own structure.
💾 Backup Files
Full database snapshots taken periodically (e.g., daily at midnight). For your 2 AM crash, you'd restore the midnight backup, then replay transaction logs from midnight to 1 AM.
🔧 Temporary Files
Used during query processing for sorting/joining large datasets. Example: When running 'SELECT * FROM orders ORDER BY date' on 10 million rows, temp files help. These are automatically cleaned up.
Practice Question
Which file type is MOST critical for point-in-time database recovery?