VNSGU BCA Sem 2: Core RDBMS (205) Practical Solutions - Set A

Paper Details

• Subject: Core of Relational Database Management System (CRDMS)
• Subject Code: 205
• Set: A
• 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: Database Schema & Constraints

Max Marks: 10

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

• account (account_no, acct_name, balance)
• transaction (trans_Id, account_no, trans_type, amount, trans_date)

1. Account Table Creation

Create the account table with a primary key and balance check.

Hint

Use NUMBER for numeric fields and VARCHAR2 for names. A CHECK constraint can prevent the balance from becoming negative.

View Solution & Output

-- Create ACCOUNT table
CREATE TABLE account (
    account_no NUMBER(10) PRIMARY KEY,
    acct_name VARCHAR2(50) NOT NULL,
    balance NUMBER(10,2) CHECK (balance >= 0)
);

print "Table 'account' created.";

Step-by-Step Explanation: 1. Initialization: Define the table name and specify columns with appropriate data types and sizes. 2. Logic Flow: Apply constraints like PRIMARY KEY, NOT NULL, or CHECK to ensure data validity and uniqueness. 3. Completion: Finalize the table definition in the database schema for future data operations.

2. Transaction Table with Foreign Key

Create the transaction table linked to account.

flowchart TD
A["account"]
B["transaction"]
A --> B

View Solution & Output

-- Create TRANSACTION table with Foreign Key
CREATE TABLE transaction (
    trans_Id NUMBER(10) PRIMARY KEY,
    account_no NUMBER(10),
    trans_type VARCHAR2(20) CHECK (trans_type IN ('DEPOSIT', 'WITHDRAWAL')),
    amount NUMBER(10,2),
    trans_date DATE DEFAULT SYSDATE,
    CONSTRAINT fk_account_transaction 
        FOREIGN KEY (account_no) 
        REFERENCES account(account_no)
);

print "Table 'transaction' created with FK constraint.";

Step-by-Step Explanation: 1. Initialization: Define the table structure with various data types (NUMBER, VARCHAR2, DATE) and constraints. 2. Logic Flow: Assign a PRIMARY KEY for uniqueness and establish a relationship using a FOREIGN KEY linked to the master table. 3. Completion: Execute the statement to create the table with full relational integrity.

3. Data Population

Insert minimum 5 records in each table.

View Solution & Output

-- Insert records into ACCOUNT table
INSERT INTO account VALUES (1001, 'Rahul Sharma', 15000.00);
INSERT INTO account VALUES (1002, 'Priya Patel', 8000.00);
INSERT INTO account VALUES (1003, 'Amit Kumar', 25000.00);
INSERT INTO account VALUES (1004, 'Sneha Gupta', 5000.00);
INSERT INTO account VALUES (1005, 'Vikram Singh', 12000.00);

-- Insert records into TRANSACTION table
INSERT INTO transaction VALUES (1, 1001, 'DEPOSIT', 5000.00, '15-APR-2025');
INSERT INTO transaction VALUES (2, 1002, 'WITHDRAWAL', 2000.00, '15-APR-2025');
INSERT INTO transaction VALUES (3, 1003, 'DEPOSIT', 10000.00, '16-APR-2025');
INSERT INTO transaction VALUES (4, 1001, 'WITHDRAWAL', 3000.00, '16-APR-2025');
INSERT INTO transaction VALUES (5, 1004, 'DEPOSIT', 2000.00, '17-APR-2025');

COMMIT;

-- Verify data
SELECT * FROM account;
SELECT * FROM transaction;

Sample Output:

ACCOUNT_NO | ACCT_NAME      | BALANCE
-----------|----------------|----------
1001       | Rahul Sharma   | 15000
...

Step-by-Step Explanation: 1. Initialization: Prepare a series of INSERT statements containing sample data that satisfies all table constraints. 2. Logic Flow: Insert records into the master table first, followed by the detail table to maintain referential integrity. 3. Completion: Use the COMMIT command to save all changes permanently and verify the data using SELECT statements.

Q1B: PL/SQL Withdrawal Logic

Max Marks: (Misc)

Write a PL/SQL block to withdraw a specified amount from a bank account, updating the balance. If the new balance after the withdrawal goes below 5000, raise an error.

Hint

Use a custom EXCEPTION for the minimum balance check. Use SELECT INTO to fetch the current balance and UPDATE to modify it.

flowchart TD
start[Start]
get[Fetch Balance]
check[Balance LT 5000?]
ex[Error Rollback]
upd[Update and Log]
finish[End]

start --> get
get --> check
check --> ex
check --> upd
upd --> finish
ex --> finish

View Solution & Output

SET SERVEROUTPUT ON;

DECLARE
    v_account_no NUMBER := 1001;
    v_withdrawal_amount NUMBER := 8000;
    v_current_balance NUMBER;
    v_new_balance NUMBER;

    -- Custom exception
    e_minimum_balance EXCEPTION;
BEGIN
    -- Get current balance
    SELECT balance INTO v_current_balance
    FROM account
    WHERE account_no = v_account_no;

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

    -- Check if balance goes below 5000
    IF v_new_balance < 5000 THEN
        RAISE e_minimum_balance;
    END IF;

    -- Update the balance
    UPDATE account
    SET balance = v_new_balance
    WHERE account_no = v_account_no;

    -- Insert transaction record
    INSERT INTO transaction (trans_Id, account_no, trans_type, amount, trans_date)
    VALUES ((SELECT NVL(MAX(trans_Id), 0) + 1 FROM transaction), 
            v_account_no, 'WITHDRAWAL', v_withdrawal_amount, SYSDATE);

    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Withdrawal Successful! New Balance: ' || v_new_balance);

EXCEPTION
    WHEN e_minimum_balance THEN
        DBMS_OUTPUT.PUT_LINE('Error: Minimum balance of 5000 required!');
        ROLLBACK;
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Error: Account not found!');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
        ROLLBACK;
END;
/

Step-by-Step Explanation: 1. Initialization: Declare variables for data storage and define a custom EXCEPTION for specific business rule violations. 2. Logic Flow: Fetch data into variables, perform logic checks, and RAISE the exception if conditions (like minimum balance) are met. 3. Completion: Handle success with a COMMIT or errors in the EXCEPTION block with a ROLLBACK for data consistency.

Concept Deep Dive: Exception Handling

Exception handling in PL/SQL prevents a program from crashing when an error occurs. By using User-Defined Exceptions (like e_minimum_balance), you can enforce business rules (like minimum balance) just as easily as system errors.

Q2: High Balance Cursor

Max Marks: 5

Create a cursor to display all accounts with a balance greater than 10,000.

flowchart TD
id1[Start]
id2[Open Cursor]
id3[Fetch Next]
id4[Record Found?]
id5[Display Record]
id6[Close Cursor]
id7[End]

id1 --> id2
id2 --> id3
id3 --> id4
id4 --> id5
id5 --> id3
id4 --> id6
id6 --> id7

View Solution & Output

DECLARE
    CURSOR c_high_balance IS
        SELECT account_no, acct_name, balance
        FROM account
        WHERE balance > 10000;
    v_account_no account.account_no%TYPE;
    v_acct_name account.acct_name%TYPE;
    v_balance account.balance%TYPE;
BEGIN
    OPEN c_high_balance;
    LOOP
        FETCH c_high_balance INTO v_account_no, v_acct_name, v_balance;
        EXIT WHEN c_high_balance%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Account: ' || v_account_no || ' | Balance: ' || v_balance);
    END LOOP;
    CLOSE c_high_balance;
END;
/

Step-by-Step Explanation: 1. Initialization: Declare a CURSOR with a SELECT query and define local variables to store the fetched row data. 2. Logic Flow: OPEN the cursor, FETCH rows in a LOOP, and process/display each record until %NOTFOUND is true. 3. Completion: CLOSE the cursor to release memory resources and terminate the PL/SQL block.

Q3: Viva Preparation

Max Marks: 5

Potential Viva Questions

1. Q: What is the difference between PRIMARY KEY and FOREIGN KEY?
2. A: PRIMARY KEY uniquely identifies a record in its own table; FOREIGN KEY links to a PRIMARY KEY in another table.
3. Q: What are the ACID properties in RDBMS?
4. A: Atomicity, Consistency, Isolation, and Durability.
5. Q: When should you use a user-defined exception?
6. A: When you want to enforce a business rule that isn't a standard database error (e.g., minimum balance).
7. Q: What is the purpose of NVL function?
8. A: It replaces a NULL value with a default value of your choice.
9. Q: What is a Cursor in PL/SQL?
10. A: A cursor is a pointer to a private SQL area that stores information about the processing of a SELECT or DML statement.
11. Q: Difference between %TYPE and %ROWTYPE?
12. A: %TYPE provides the data type of a single column, while %ROWTYPE provides a record type that represents an entire row in a table.
13. Q: What is the significance of the COMMIT statement?
14. A: It makes all changes made during the current transaction permanent in the database.

Common Pitfalls

• Case Sensitivity: SQL keywords are not case-sensitive, but data inside single quotes 'DEPOSIT' is.
• Missing Commits: Changes like INSERT or UPDATE won't be permanent until you run COMMIT.
• Foreign Key Violation: You cannot add a transaction for an account that doesn't exist in the account table.

---

Quick Navigation

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

Related Solutions

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

---

Last Updated: April 2025