Here are the answers to the RDBMS questions for Unit-05, formatted and explained for 3-mark
answers:
---
### **1. Role of Indexing in Improving Database Performance**
Indexing improves database performance by allowing quicker data retrieval without scanning the
entire table. An index creates a data structure (like a B-Tree) that provides a fast lookup mechanism.
#### **Types of Indexes**
- **Primary Index**: Automatically created for primary keys.
- **Clustered Index**: Stores rows in the table based on key values; only one is allowed per table.
- **Non-Clustered Index**: A separate structure pointing to the table rows; multiple non-clustered
indexes are allowed.
- **Unique Index**: Ensures all values in the index column are unique.
---
### **2. Common Causes of Database Bottlenecks**
**Causes**:
1. **High CPU usage** due to complex queries.
2. **Insufficient Memory** for caching.
3. **Locking and Blocking** in concurrent transactions.
4. **Disk I/O bottlenecks** from frequent writes or reads.
#### **How to Identify**:
- Use performance monitoring tools like **SQL Profiler**, **Performance Monitor**, or **EXPLAIN
plans**.
- Look for metrics like CPU utilization, query execution time, or I/O operations.
---
### **3. Connection Pooling**
**Definition**:
Connection pooling involves reusing existing database connections instead of creating a new one for
every user request.
#### **How it Helps**:
- Reduces connection overhead.
- Improves response times for applications.
- Efficiently manages database resources.
---
### **4. Types of Database Backups**
1. **Full Backup**:
- **Advantages**: Captures entire data; simple to restore.
- **Disadvantages**: Time-consuming; storage-intensive.
2. **Incremental Backup**:
- **Advantages**: Only backs up changes since the last backup; faster.
- **Disadvantages**: Slower restoration due to dependency on previous backups.
3. **Differential Backup**:
- **Advantages**: Backs up changes since the last full backup; quicker restore than incremental.
- **Disadvantages**: Larger than incremental backups over time.
---
### **5. Tools and Techniques for Backup Monitoring and Recovery**
- **Tools**: SQL Server Management Studio (SSMS), Oracle RMAN, pgAdmin for PostgreSQL.
- **Techniques**:
- Schedule regular backups.
- Use alerts for backup failures.
- Perform periodic test restores to verify backup integrity.
---
### **6. Identifying and Resolving SQL Query Performance Bottlenecks**
**Identification**:
- Analyze execution plans.
- Look for slow queries using tools like SQL Profiler or `EXPLAIN`.
**Example**:
- **Bottleneck**: A full table scan in a query.
```sql
SELECT * FROM Orders WHERE CustomerID = 123;
```
- **Solution**: Add an index on the `CustomerID` column:
```sql
CREATE INDEX idx_customerid ON Orders(CustomerID);
```
---
### **7. Enhancing Performance Through Table Partitioning**
**How Partitioning Helps**:
- Divides a large table into smaller, manageable parts (partitions).
- Queries access only relevant partitions, reducing scan time.
**Considerations**:
1. Partition keys should align with query patterns.
2. Ensure partitions are balanced to prevent skew.
---
### **8. Views in T-SQL**
**Definition**:
A view is a virtual table based on a query. It does not store data but simplifies complex queries.
**Differences from Tables**:
- Tables store data; views do not.
- Views can combine data from multiple tables.
#### **Advantages**:
1. Simplifies query writing.
2. Enhances security by restricting direct table access.
**Example**:
Creating and using a view:
```sql
CREATE VIEW CustomerOrders AS
SELECT Customers.Name, Orders.OrderDate
FROM Customers
JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
SELECT * FROM CustomerOrders WHERE Name = 'John';
```
---
These answers cover key points concisely for 3-mark questions. Let me know if further elaboration is
needed!
Here's an explanation of the questions, structured for clarity and aligned with T-SQL concepts:
---
### **1. Different Types of Joins in T-SQL and Their Importance**
**Joins** are used to combine rows from two or more tables based on related columns.
#### **Types of Joins**:
1. **Inner Join**: Returns rows with matching values in both tables.
```sql
SELECT Employees.Name, Departments.DeptName
FROM Employees
INNER JOIN Departments ON Employees.DeptID = Departments.DeptID;
```
2. **Left Join** (Outer Join): Returns all rows from the left table and matching rows from the right
table, with `NULL` for non-matches.
```sql
SELECT Employees.Name, Departments.DeptName
FROM Employees
LEFT JOIN Departments ON Employees.DeptID = Departments.DeptID;
```
3. **Right Join**: Similar to Left Join but for the right table.
4. **Full Outer Join**: Returns all rows when there is a match in either table.
5. **Cross Join**: Returns the Cartesian product of both tables.
#### **Importance**:
Joins are essential for creating relationships between tables and retrieving combined data efficiently.
---
### **2. Cursors in T-SQL**
A **cursor** is a database object used to retrieve, manipulate, and iterate through query result sets
row by row.
#### **When to Use Cursors**:
- Use only when row-by-row processing is unavoidable (e.g., for batch updates where set-based
operations are impractical).
**Example**:
```sql
DECLARE cursor_example CURSOR FOR
SELECT Name FROM Employees;
OPEN cursor_example;
FETCH NEXT FROM cursor_example INTO @Name;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @Name;
FETCH NEXT FROM cursor_example INTO @Name;
END;
CLOSE cursor_example;
DEALLOCATE cursor_example;
```
---
### **3. Recursive T-SQL Function for Factorial**
A recursive function calls itself to solve problems like factorials.
**Example**:
```sql
CREATE FUNCTION Factorial (@Number INT)
RETURNS INT
AS
BEGIN
IF @Number <= 1
RETURN 1;
RETURN @Number * dbo.Factorial(@Number - 1);
END;
-- Usage:
SELECT dbo.Factorial(5); -- Output: 120
```
---
### **4. Categories of Built-In Functions in T-SQL**
1. **Aggregate Functions**: Perform calculations on a set of values.
- Example: `SUM()`, `AVG()`.
```sql
SELECT AVG(Salary) FROM Employees;
```
2. **String Functions**: Manipulate string data.
- Example: `LEN()`, `SUBSTRING()`.
```sql
SELECT SUBSTRING(Name, 1, 3) FROM Employees;
```
3. **Date Functions**: Work with date and time.
- Example: `GETDATE()`, `DATEADD()`.
```sql
SELECT DATEADD(DAY, 7, GETDATE());
```
4. **Mathematical Functions**: Perform mathematical operations.
- Example: `ROUND()`, `CEILING()`.
---
### **5. Optimizing Query Performance in T-SQL**
**Methods**:
1. Use appropriate indexes.
2. Avoid `SELECT *` and fetch only required columns.
3. Analyze query execution plans to identify bottlenecks.
#### **Role of Indexes in Query Performance**:
Indexes speed up data retrieval by reducing the amount of data scanned.
**Example**:
Without an index:
```sql
SELECT * FROM Orders WHERE OrderID = 102;
```
With an index on `OrderID`:
```sql
CREATE INDEX idx_OrderID ON Orders(OrderID);
```
---
### **6. Stored Procedures and Code Reuse in T-SQL**
**Definition**:
A stored procedure is a precompiled collection of SQL statements stored in the database.
**Advantages**:
- Reusability reduces code duplication.
- Improved performance due to pre-compilation.
**Example**:
```sql
CREATE PROCEDURE GetEmployeeDetails
@EmployeeID INT
AS
BEGIN
SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;
END;
-- Usage:
EXEC GetEmployeeDetails @EmployeeID = 1;
```
---
### **7. Triggers in T-SQL**
**Definition**:
Triggers are special types of stored procedures that automatically execute in response to events
(INSERT, UPDATE, DELETE) on a table.
**Example**:
```sql
CREATE TRIGGER trg_AfterInsert ON Employees
AFTER INSERT
AS
BEGIN
PRINT 'New record added to Employees table';
END;
```
---
### **8. Parsing and Semantic Analysis in Compiler Construction**
- **Parsing**: Converts source code into a structured format (parse tree) based on syntax rules.
- **Semantic Analysis**: Checks for meaning and correctness in the parse tree, like type checking.
**Annotated Parse Tree**:
Contains additional metadata, like variable types, symbol table references, and semantic rules.
---
This covers all the questions comprehensively. Let me know if you'd like further clarification on any
point!
