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!