DBMS FINAL

Explain the concept of virtual tables (views) in SQL. Discuss the advantages of views and how they improve security and data independence.

a view in SQL is essentially a saved query that behaves like a table but does not store its own data. Instead it shows data pulled from one or more real tables using a saved query. So when you create a view, you’re just saving a SELECT statement, and every time you use that view, SQL runs that query behind the scenes and shows the results like it’s a regular table.

Explain the role of procedural SQL in database programming. How is it different from

standard SQL?

Procedural SQL is an extension of SQL that adds programming features like variables,loops, and if/else statements. Languages like PL/SQL and T-SQL allow you to write stored procedures, functions, and triggers that automate tasks inside the database. The difference between the two is that standard SQL is declarative, which means you specify what result you want without describing the steps to achieve it. Procedural SQL is imperative, meaning you define both what you want and how to accomplish it step by step

Discuss different looping structures used in Procedural SQL. Explain WHILE loop, FOR Loop, with Syntax

A WHILE loop executes a set of statements as long as a specified condition remains true. The condition is evaluated before each iteration, which means the loop may not run at all if the condition is false from the start.

Syntax:

WHILE condition LOOP
   -- statements
END LOOP;

A FOR loop, on the other hand, is used when the number of iterations is known in advance. It runs a fixed number of times based on a defined range, and the loop variable is automatically managed by the system.

Syntax:

FOR counter IN start..end LOOP
   -- statements
END LOOP;

What is a cursor? Explain cursor processing steps in SQL with examples. Discuss advantages and disadvantages of cursors

A cursor in SQL is a database object used to process query results one row at a time, instead of handling all rows at once like a normal query.

In cursor processing, first, you declare the cursor, which defines the SELECT query that retrieves the data. Next, you open the cursor, which executes the query and prepares the result set. Then, you fetch rows one at a time from the cursor into variables so they can be processed. After that, you process the data. Finally, you close the cursor to release resources.

Example (PL/SQL style):

DECLARE
   CURSOR emp_cursor IS SELECT name, salary FROM employees;
   v_name employees.name%TYPE;
   v_salary employees.salary%TYPE;
BEGIN
   OPEN emp_cursor;
   LOOP
      FETCH emp_cursor INTO v_name, v_salary;
      EXIT WHEN emp_cursor%NOTFOUND;
      -- process each row
   END LOOP;
   CLOSE emp_cursor;
END;

The main advantage of cursors is that they allow row-by-row processing, which is useful for complex logic that cannot be easily done with a single SQL statement. They also give more control over how each row is handled. However, cursors are generally slower and less efficient than set-based SQL operations because they process one row at a time, they use more system resources, and they can make code more complex and harder to maintain.

What are triggers.? Discuss practical applications of triggers in enforcing business rules and maintaining audit logs.

Triggers are special database objects that automatically execute a code when a specific event occurs on a table or view, such as an INSERT, UPDATE, or DELETE.

for enforcing business rules, a trigger can prevent invalid data from being inserted, such as stopping a transaction if a user tries to withdraw more money than their account balance, or ensuring that certain fields meet specific conditions before allowing an update. This helps maintain data integrity without relying only on application-level checks

for audit logging, whenever a record is updated or deleted, a trigger can automatically store the old and new values, along with a timestamp and user information, into a separate audit table. This creates a history of changes, which is useful for tracking activity, debugging issues, or meeting compliance requirements.

Explain the transaction concept and the states of the transaction.

a transaction in a database ensures reliable databae operations by controlling how changes are made and handled, especially in errors or system interruptions

1. Active State

The transaction has started and is executing its operations (reads, writes, updates).

• Data is being manipulated but not yet saved to the database.

• If an error occurs here, the transaction may move to the Failed state.

2. Partially Committed State

All operations have executed, but the final commit has not yet occurred.

• Changes exist only in memory or buffers.

• If commit succeeds → moves to Committed.

• If a failure occurs → moves to Failed.

3. Committed State

The transaction has successfully completed.

• All changes are permanently saved to the database.

• The transaction is guaranteed to be durable.

4. Failed State

An error or system issue prevents the transaction from completing.

• Execution stops immediately.

• The recovery system ensures the database remains consistent.

5. Aborted State

The DBMS rolls back the transaction to undo any partial changes.

• The system may:

  • Kill the transaction permanently, or

  • Restart it from the beginning after corrections.

6. Terminated State

The final state.

• The transaction has either been successfully committed or fully aborted.

• No further actions are required.

┌──────────────────────────┐

│ ACTIVE │

│ (Executing operations) │

└─────────────┬────────────┘

│

│ All operations complete

▼

┌──────────────────────────┐

│ PARTIALLY COMMITTED │

│ (Commit not yet final) │

└─────────────┬────────────┘

│

│ Commit successful

▼

┌──────────────────────────┐

│ COMMITTED │

│ (Changes permanently │

│ saved) │

└─────────────┬────────────┘

│

▼

┌──────────────────────────┐

│ TERMINATED │

│ (Transaction finished) │

└──────────────────────────┘

How are atomicity and durability implemented in a DBMS?

What is serializability, and how is it tested in concurrent executions?

Compare lock-based protocols and timestamp–based protocols in transaction management.

Describe validation-based protocols and their role in maintaining isolation.

Explain log-based recovery mechanisms and recovery with concurrent transactions.

What is multiple granularity in transactions, and how does it improve concurrency control?

Discuss the significance of recoverability and atomicity in DBMS transactions.

Discuss timestamp-based concurrency control and compare it with locking methods

What is Deadlock? Explain methods used for deadlock prevention and recovery. Give

suitable examples.

What are the key types of data storage in a DBMS and how is data stored externally?

Explain the major causes of poor database performance. How can database administrators improve system performance?

Compare cluster indexes, primary indexes and secondary indexes with examples.

What is hash based indexing, and how does it differ from tree- based indexing?

Compare different file organization techniques and their sustainability for various use

cases.

Explain the structure and working of Indexed Sequential Access Methods (ISAM).

How does data replication improve the performance and reliability of a distributed

database system?

Describe the two-phase commit protocol and its role in ensuring transaction consistency in

a distributed database system

Describe the three fragmentation strategies. Give some examples of each.

What are the primary responsibilities of a database administrator (DBA)? Explain the key

tasks involved in database administration.

Discuss the stages of Query processing and how optimizer selects and efficient execution

plan.

Discuss distribution transparency and why it is important for users of distributed databases.

Explain the concept of Distributed DBMS. Discuss its advantages and disadvantages

compared with centralized systems.