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

Paper Details

• Subject: Core of Relational Database Management System (CRDMS)
• Subject Code: 205
• Set: F
• 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: Distributor, Item & Dist_item Schema

Max Marks: 10

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

• Distributor (Dno, D_Name, phno)
• Item (Item_no, Dno, item_name, price)
• Dist_item (Dno, item_no, qty)

1. Distributor Table Creation

Hint

Create parent tables first. Distributor is the main parent table.

View Solution & Output

-- Create DISTRIBUTOR table
CREATE TABLE Distributor (
    Dno NUMBER(5) PRIMARY KEY,
    D_Name VARCHAR2(50) NOT NULL,
    phno VARCHAR2(15)
);

-- Output: Table 'Distributor' created.

2. Item Table with Foreign Key

View Solution & Output

-- Create ITEM table with Foreign Key to Distributor
CREATE TABLE Item (
    Item_no NUMBER(5) PRIMARY KEY,
    Dno NUMBER(5),
    item_name VARCHAR2(50) NOT NULL,
    price NUMBER(10,2),
    CONSTRAINT fk_item_distributor 
        FOREIGN KEY (Dno) 
        REFERENCES Distributor(Dno)
);

-- Output: Table 'Item' created with FK to 'Distributor'.

3. Dist_item Table (Junction Table)

View Solution & Output

-- Create DIST_ITEM junction table with Foreign Keys
CREATE TABLE Dist_item (
    Dno NUMBER(5),
    item_no NUMBER(5),
    qty NUMBER(5),
    PRIMARY KEY (Dno, item_no),
    CONSTRAINT fk_dist_item_distributor 
        FOREIGN KEY (Dno) 
        REFERENCES Distributor(Dno),
    CONSTRAINT fk_dist_item_item 
        FOREIGN KEY (item_no) 
        REFERENCES Item(Item_no)
);

-- Output: Table 'Dist_item' created with FKs to both tables.

4. Inserting Data

View Solution & Output

-- Insert records into DISTRIBUTOR
INSERT INTO Distributor VALUES (101, 'ABC Distributors', '9876543210');
INSERT INTO Distributor VALUES (102, 'XYZ Traders', '9876543211');
INSERT INTO Distributor VALUES (103, 'PQR Suppliers', '9876543212');
INSERT INTO Distributor VALUES (104, 'LMN Vendors', '9876543213');
INSERT INTO Distributor VALUES (105, 'DEF Enterprises', '9876543214');

-- Insert records into ITEM
INSERT INTO Item VALUES (1, 101, 'Laptop', 45000.00);
INSERT INTO Item VALUES (2, 101, 'Mouse', 500.00);
INSERT INTO Item VALUES (3, 102, 'Keyboard', 800.00);
INSERT INTO Item VALUES (4, 103, 'Monitor', 12000.00);
INSERT INTO Item VALUES (5, 102, 'Headphones', 1500.00);

-- Insert records into DIST_ITEM (junction)
INSERT INTO Dist_item VALUES (101, 1, 50);
INSERT INTO Dist_item VALUES (101, 2, 200);
INSERT INTO Dist_item VALUES (102, 3, 150);
INSERT INTO Dist_item VALUES (103, 4, 75);
INSERT INTO Dist_item VALUES (102, 5, 100);

COMMIT;

-- Verify data
SELECT * FROM Distributor;
SELECT * FROM Item;
SELECT * FROM Dist_item;

Q1B: Display Item Distributors PL/SQL

Max Marks: 10

Write a PL/SQL block that accept Item no and display its distributors details.

Hint

Use JOIN between Distributor, Item, and Dist_item tables. Accept Item_no as input.

View Solution & Output

SET SERVEROUTPUT ON;

DECLARE
    v_item_no NUMBER := &item_no;
    v_item_name Item.item_name%TYPE;
    v_price Item.price%TYPE;
BEGIN
    -- Get item details
    SELECT item_name, price INTO v_item_name, v_price
    FROM Item
    WHERE Item_no = v_item_no;

    DBMS_OUTPUT.PUT_LINE('========================================');
    DBMS_OUTPUT.PUT_LINE('ITEM DETAILS');
    DBMS_OUTPUT.PUT_LINE('========================================');
    DBMS_OUTPUT.PUT_LINE('Item No: ' || v_item_no);
    DBMS_OUTPUT.PUT_LINE('Item Name: ' || v_item_name);
    DBMS_OUTPUT.PUT_LINE('Price: ' || v_price);
    DBMS_OUTPUT.PUT_LINE('----------------------------------------');
    DBMS_OUTPUT.PUT_LINE('DISTRIBUTORS:');
    DBMS_OUTPUT.PUT_LINE('----------------------------------------');

    -- Find all distributors for this item
    FOR rec IN (
        SELECT d.Dno, d.D_Name, d.phno, di.qty
        FROM Distributor d
        JOIN Dist_item di ON d.Dno = di.Dno
        WHERE di.item_no = v_item_no
    ) LOOP
        DBMS_OUTPUT.PUT_LINE('Distributor No: ' || rec.Dno || 
                            ' | Name: ' || rec.D_Name ||
                            ' | Phone: ' || rec.phno ||
                            ' | Qty: ' || rec.qty);
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('========================================');

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Error: Item not found.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

Alternative: Using Explicit Cursor

You can also solve this using an explicit cursor instead of a FOR loop:

SET SERVEROUTPUT ON;

DECLARE
    v_item_no NUMBER := &item_no;
    v_item_name Item.item_name%TYPE;
    v_price Item.price%TYPE;

    -- Explicit cursor for distributors
    CURSOR c_distributors IS
        SELECT d.Dno, d.D_Name, d.phno, di.qty
        FROM Distributor d
        JOIN Dist_item di ON d.Dno = di.Dno
        WHERE di.item_no = v_item_no;

    v_dno Distributor.Dno%TYPE;
    v_dname Distributor.D_Name%TYPE;
    v_phno Distributor.phno%TYPE;
    v_qty Dist_item.qty%TYPE;
BEGIN
    -- Get item details
    SELECT item_name, price INTO v_item_name, v_price
    FROM Item
    WHERE Item_no = v_item_no;

    DBMS_OUTPUT.PUT_LINE('========================================');
    DBMS_OUTPUT.PUT_LINE('ITEM DETAILS');
    DBMS_OUTPUT.PUT_LINE('========================================');
    DBMS_OUTPUT.PUT_LINE('Item No: ' || v_item_no);
    DBMS_OUTPUT.PUT_LINE('Item Name: ' || v_item_name);
    DBMS_OUTPUT.PUT_LINE('Price: ' || v_price);
    DBMS_OUTPUT.PUT_LINE('----------------------------------------');
    DBMS_OUTPUT.PUT_LINE('DISTRIBUTORS (Using Cursor):');
    DBMS_OUTPUT.PUT_LINE('----------------------------------------');

    -- Open and fetch from cursor
    OPEN c_distributors;
    LOOP
        FETCH c_distributors INTO v_dno, v_dname, v_phno, v_qty;
        EXIT WHEN c_distributors%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Distributor No: ' || v_dno || 
                            ' | Name: ' || v_dname ||
                            ' | Phone: ' || v_phno ||
                            ' | Qty: ' || v_qty);
    END LOOP;
    CLOSE c_distributors;

    DBMS_OUTPUT.PUT_LINE('========================================');

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Error: Item not found.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

One Step Further: The Cursor FOR Loop

If you use a Cursor FOR Loop, you don't even need to declare the %ROWTYPE variables or use OPEN/FETCH/CLOSE. PL/SQL handles the record declaration implicitly:

FOR rec IN (
    SELECT d.Dno, d.D_Name, d.phno, di.qty
    FROM Distributor d
    JOIN Dist_item di ON d.Dno = di.Dno
    WHERE di.item_no = v_item_no
) LOOP
    DBMS_OUTPUT.PUT_LINE('Distributor: ' || rec.D_Name);
END LOOP;

Q2: Viva Preparation

Max Marks: 5

Potential Viva Questions

1. Q: What is the purpose of COMMIT?
   • A: It permanently saves all data changes in the current transaction.
2. Q: What are DDL and DML?
   • A: DDL (Data Definition Language) defines structure like CREATE/DROP. DML (Data Manipulation Language) manages data like INSERT/UPDATE/DELETE.
3. Q: How can we rename a table in SQL?
   • A: Use RENAME old_table_name TO new_table_name;.
4. Q: What is an INNER JOIN?
   • A: It returns records that have matching values in both tables, effectively combining rows from two or more tables.
5. Q: What does the SQLERRM function do?
   • A: It returns the error message associated with the most recently raised exception.
6. Q: Why use VARCHAR2 instead of CHAR?
   • A: VARCHAR2 uses space only for the characters stored, while CHAR always uses the full defined length, padding with spaces.
7. Q: What is a Junction/Link Table?
   • A: A table that connects two tables in a many-to-many relationship (like Dist_item).
8. Q: Why do we need Dist_item table?
   • A: To track which distributor supplies which item and in what quantity.
9. Q: What is a Composite Primary Key?
   • A: A primary key made of multiple columns (Dno + item_no in Dist_item).
10. Q: Difference between One-to-Many and Many-to-Many?

• A: One-to-Many: one record relates to many. Many-to-Many: many records relate to many (needs junction table).

Common Pitfalls

• Junction Table PK: Use composite key (Dno, item_no) for Dist_item.
• Insert Order: Distributor → Item → Dist_item (maintain FK integrity).
• Many-to-Many: Don't duplicate data; use junction table properly.

---

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	Solutions
Set D	Solutions
Set E	Solutions
Set F	Current Page

---

Last Updated: April 2026