VNSGU BCA Sem 2: Core RDBMS (205) Practical Solutions - April 2025 Set C

Paper Details

• Subject: Core of Relational Database Management System (CRDMS)
• Subject Code: 205
• Set: C
• Semester: 2
• Month/Year: April 2025
• Max Marks: 25
• Time Recommendation: 45 Minutes
• Paper: View Paper | Download PDF

---

Questions & Solutions

All questions are compulsory

Q1A: Bank & Customer Schema

Max Marks: 10

Create following tables with appropriate constraints with primary key and foreign key. Insert minimum 5 records in each table.

• bank (bank_id, Bank_Name, address)
• Customer (cust_id, account_no, bank_id, cust_name, balance, phone_no)

1. Bank Table Creation

Hint

Start by creating the master table (bank) before the detail table (Customer).

View Solution & Output

-- Create BANK table
CREATE TABLE bank (
    bank_id NUMBER(5) PRIMARY KEY,
    Bank_Name VARCHAR2(50) NOT NULL,
    address VARCHAR2(100)
);

-- Output: Table 'bank' created.

2. Customer Table with Foreign Key

View Solution & Output

-- Create CUSTOMER table with Foreign Key
CREATE TABLE Customer (
    cust_id NUMBER(5) PRIMARY KEY,
    account_no NUMBER(10) UNIQUE NOT NULL,
    bank_id NUMBER(5),
    cust_name VARCHAR2(50) NOT NULL,
    balance NUMBER(12,2) CHECK (balance >= 0),
    phone_no VARCHAR2(15),
    CONSTRAINT fk_bank_customer 
        FOREIGN KEY (bank_id) 
        REFERENCES bank(bank_id)
);

-- Output: Table 'Customer' created with FK to 'bank'.

3. Inserting Data

View Solution & Output

-- Insert records into BANK
INSERT INTO bank VALUES (101, 'State Bank of India', 'Mumbai Main Branch');
INSERT INTO bank VALUES (102, 'HDFC Bank', 'Delhi Connaught Place');
INSERT INTO bank VALUES (103, 'ICICI Bank', 'Bangalore MG Road');
INSERT INTO bank VALUES (104, 'Axis Bank', 'Chennai T Nagar');
INSERT INTO bank VALUES (105, 'Bank of Baroda', 'Ahmedabad Navrangpura');

-- Insert records into CUSTOMER
INSERT INTO Customer VALUES (1, '100001', 101, 'Rahul Sharma', 25000.00, '9876543210');
INSERT INTO Customer VALUES (2, '100002', 102, 'Priya Patel', 15000.00, '9876543211');
INSERT INTO Customer VALUES (3, '100003', 101, 'Amit Kumar', 35000.00, '9876543212');
INSERT INTO Customer VALUES (4, '100004', 103, 'Sita Verma', 12000.00, '9876543213');
INSERT INTO Customer VALUES (5, '100005', 104, 'Ram Gupta', 28000.00, '9876543214');

COMMIT;

-- Verify data
SELECT * FROM bank;
SELECT * FROM Customer;

Q1B: Withdrawal with Minimum Balance PL/SQL

Max Marks: 10

Write a PL/SQL block that accept customer ID and withdraw specific amount from his account, if the new updated balance after the withdrawal goes below Rs. 2000, raise an exception.

Hint

Accept cust_id and withdrawal amount as input. Check if new balance >= 2000 before updating. Raise custom exception if below.

View Solution & Output

SET SERVEROUTPUT ON;

DECLARE
    v_cust_id NUMBER := &cust_id;
    v_withdraw_amount NUMBER := &withdraw_amount;
    v_current_balance Customer.balance%TYPE;
    v_new_balance Customer.balance%TYPE;
    v_cust_name Customer.cust_name%TYPE;

    -- Custom exception for minimum balance
    e_min_balance EXCEPTION;
BEGIN
    -- Get current balance and customer name
    SELECT balance, cust_name INTO v_current_balance, v_cust_name
    FROM Customer
    WHERE cust_id = v_cust_id;

    -- Calculate new balance
    v_new_balance := v_current_balance - v_withdraw_amount;

    -- Check if balance goes below 2000
    IF v_new_balance < 2000 THEN
        RAISE e_min_balance;
    END IF;

    -- Update balance
    UPDATE Customer
    SET balance = v_new_balance
    WHERE cust_id = v_cust_id;

    COMMIT;

    DBMS_OUTPUT.PUT_LINE('========================================');
    DBMS_OUTPUT.PUT_LINE('WITHDRAWAL SUCCESSFUL');
    DBMS_OUTPUT.PUT_LINE('========================================');
    DBMS_OUTPUT.PUT_LINE('Customer: ' || v_cust_name);
    DBMS_OUTPUT.PUT_LINE('Amount Withdrawn: ' || v_withdraw_amount);
    DBMS_OUTPUT.PUT_LINE('New Balance: ' || v_new_balance);
    DBMS_OUTPUT.PUT_LINE('========================================');

EXCEPTION
    WHEN e_min_balance THEN
        DBMS_OUTPUT.PUT_LINE('Error: Minimum balance of Rs. 2000 must be maintained!');
        DBMS_OUTPUT.PUT_LINE('Current Balance: ' || v_current_balance);
        DBMS_OUTPUT.PUT_LINE('Requested Withdrawal: ' || v_withdraw_amount);
        DBMS_OUTPUT.PUT_LINE('Would leave: ' || v_new_balance);
        ROLLBACK;
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Error: Customer ID not found.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
        ROLLBACK;
END;
/

Alternative Solution: Using Bind Variables & %ROWTYPE

This approach uses bind variables (:VARNAME) instead of substitution variables (&varname), and %ROWTYPE to fetch the entire customer record.

SET SERVEROUTPUT ON;

DECLARE
  -- Customer record for displaying full details
  r_customer Customer%ROWTYPE;

  -- Input parameters (using bind variables :CUST_ID, :WITHDRAW_AMOUNT)
  p_cust_id Customer.cust_id%TYPE := :CUST_ID;
  p_withdrawal_amount NUMBER(10,2) := :WITHDRAW_AMOUNT;

  -- Working variables
  v_new_balance Customer.balance%TYPE;

  -- Custom exception
  e_insufficient_balance EXCEPTION;

BEGIN
  -- Fetch customer record
  SELECT *
  INTO r_customer
  FROM Customer
  WHERE cust_id = p_cust_id;

  -- Display customer info
  DBMS_OUTPUT.PUT_LINE('Customer ID: ' || r_customer.cust_id);
  DBMS_OUTPUT.PUT_LINE('Account No: ' || r_customer.account_no);
  DBMS_OUTPUT.PUT_LINE('Name: ' || r_customer.cust_name);
  DBMS_OUTPUT.PUT_LINE('Current Balance: ' || r_customer.balance);
  DBMS_OUTPUT.PUT_LINE('Withdrawal Amount: ' || p_withdrawal_amount);

  -- Calculate new balance
  v_new_balance := r_customer.balance - p_withdrawal_amount;

  -- Check minimum balance constraint (Rs. 2000)
  IF v_new_balance < 2000 THEN
    RAISE e_insufficient_balance;
  END IF;

  -- Update customer balance
  UPDATE Customer 
  SET balance = v_new_balance 
  WHERE cust_id = p_cust_id;

  COMMIT;

  DBMS_OUTPUT.PUT_LINE('New Balance: ' || v_new_balance);
  DBMS_OUTPUT.PUT_LINE('Transaction completed successfully.');

EXCEPTION
  WHEN e_insufficient_balance THEN
    DBMS_OUTPUT.PUT_LINE('Transaction failed: Minimum balance of Rs. 2000 required.');
    ROLLBACK;
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Error: Customer ID ' || p_cust_id || ' not found.');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
    ROLLBACK;
END;
/

Key Differences:

1. Uses bind variables (:CUST_ID, :WITHDRAW_AMOUNT) instead of substitution variables
2. Uses %ROWTYPE to fetch entire customer record into r_customer
3. Consistent naming: p_ for parameters, r_ for records, v_ for variables, e_ for exceptions

Q2: Viva Preparation

Max Marks: 5

Potential Viva Questions

1. Q: What is a SELF JOIN?
   • A: A join where a table is joined with itself.
2. Q: What is the difference between a Pre-defined and User-defined exception?
   • A: Pre-defined exceptions are standard system errors (like NO_DATA_FOUND); User-defined are established by the programmer to handle unique logic.
3. Q: Why use CONSTRAINT name in CREATE TABLE?
   • A: It allows you to easily refer to and manage (drop/disable) the constraint later by name.
4. Q: What is an INNER JOIN?
   • A: A join that returns records that have matching values in both tables.
5. Q: What is the purpose of the GROUP BY clause?
   • A: It groups rows that have the same values into summary rows, like "find the number of items for each supplier".
6. Q: Can we use aggregate functions in a WHERE clause?
   • A: No, aggregate functions must be used in the SELECT list or a HAVING clause.
7. Q: What is the difference between bank_id and account_no?
   • A: bank_id identifies the bank branch. account_no identifies the customer's account.
8. Q: Why use UNIQUE constraint on account_no?
   • A: To ensure each customer has a unique account number.
9. Q: What happens if withdrawal leaves balance below 2000?
   • A: Custom exception e_min_balance is raised and withdrawal is rejected.
10. Q: Why use ROLLBACK in exception handler?

• A: To undo any partial changes and maintain data integrity.

Common Pitfalls

• Minimum Balance: Check is < 2000, not <= 2000.
• Insert Order: Insert banks first, then customers (FK constraint).
• Withdrawal Logic: Always verify sufficient funds before updating.

---

Quick Navigation

• ← Back to CRDMS Papers
• ← Back to Semester 2
• ← Back to All Past Papers

Related Solutions

Set	Link
Set A	Solutions
Set B	Solutions
Set C	Current Page
Set D	Solutions
Set E	Solutions
Set F	Solutions

---

Last Updated: April 2026