1. Design of Schemas
Essential Ideas

• To cut down on redundancy, normalize
• Employ restrictions and foreign keys.
• For auditing, keep track of timestamps.

Schema Diagram

Customers ───< Orders ───< OrderItems >─── Products

Example Table: Customers

Checklist

• Primary & foreign keys defined
• Appropriate data types
• Default timestamps added

2. Indexing & Query Tuning
Scenario: Calculate total spending per customer.

Query
SELECT c.FirstName, c.LastName, SUM(oi.Quantity * oi.Price) AS TotalSpent
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
JOIN OrderItems oi ON o.OrderID = oi.OrderID
WHERE o.OrderDate >= '2025-01-01'GROUP BY c.FirstName, c.LastName
ORDER BY TotalSpent DESC;

Optimization: Add covering indexes to speed up joins.

3. ETL: Efficient Data Loading

Scenario: Update daily product prices from CSV.

Process Diagram
CSV File → Staging Table → Merge → Products Table

Example SQL
BULK INSERT ProductStaging FROM 'C:\Data\ProductPrices.csv'
WITH (FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n');

MERGE Products AS target
USING ProductStaging AS source
ON target.ProductID = source.ProductID
WHEN MATCHED THEN UPDATE SET Price = source.Price
WHEN NOT MATCHED BY TARGET THEN INSERT (Name, Price, Stock) VALUES (source.Name, source.Price, source.Stock);

Checklist

• Validate incoming data
• Only update changed records
• Log ETL results

4. Backup & Recovery
Backup Strategy Diagram
Full Backup → Differential Backup → Transaction Log Backups

Example SQL
BACKUP DATABASE ECommerceDB TO DISK = 'C:\Backups\ECommerceDB_FULL.bak' WITH INIT, COMPRESSION;
BACKUP LOG ECommerceDB TO DISK = 'C:\Backups\ECommerceDB_Log.trn';

Checklist

• Full backups scheduled
• Transaction logs backed up for minimal data loss
• Test restores regularly

5. Security: Controlling Access

Role-Based Access Table

SQL Example
CREATE ROLE ReadOnlyRole;
ALTER ROLE ReadOnlyRole ADD MEMBER [ReportingUser];
DENY INSERT, UPDATE, DELETE TO ReadOnlyRole;

6. Monitoring & Maintenance
Maintenance Flow Diagram
Index Rebuild → Statistics Update → Query Performance Monitoring → Alerts

Example SQL for Long-Running Queries
SELECT TOP 10
    qs.total_elapsed_time/1000 AS TotalMs,
    qs.execution_count,
    SUBSTRING(qt.text, qs.statement_start_offset/2,
        (CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS QueryText
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY TotalMs DESC;

Checklist

• Monitor slow queries & deadlocks
• Rebuild fragmented indexes
• Update statistics regularly
• Set alerts for critical issues