VNSGU BCA Sem 2: Core RDBMS (205) Practical Solutions - 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: Supplier & Item Schema

Max Marks: 10

Create following tables with appropriate constraints with primary key and foreign key. Insert minimum 5 records in each table. - supplier (sup_Id, sup_name, city) - item (item_Id, sup_Id, item_name, price)

1. Supplier Table Creation

Create the supplier table with a primary key.

Hint

Start by creating the master table (supplier) before the detail table (item). Use VARCHAR2 for names and cities.

View Solution & Output

-- Create SUPPLIER table
CREATE TABLE supplier (
    sup_Id NUMBER(5) PRIMARY KEY,
    sup_name VARCHAR2(50) NOT NULL,
    city VARCHAR2(30)
);

print "Table 'supplier' created successfully.";

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. Item Table with Foreign Key

Create the item table linked to supplier.

flowchart TD
S[Supplier Table]
I[Item Table]
S --> I

View Solution & Output

-- Create ITEM table with Foreign Key
CREATE TABLE item (
    item_Id NUMBER(5) PRIMARY KEY,
    sup_Id NUMBER(5),
    item_name VARCHAR2(50) NOT NULL,
    price NUMBER(10,2) CHECK (price > 0),
    CONSTRAINT fk_supplier_item 
        FOREIGN KEY (sup_Id) 
        REFERENCES supplier(sup_Id)
);

print "Table 'item' created with FK to 'supplier'.";

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 SUPPLIER
INSERT INTO supplier VALUES (101, 'Reliance Digital', 'Mumbai');
INSERT INTO supplier VALUES (102, 'Croma Retail', 'Delhi');
INSERT INTO supplier VALUES (103, 'Vijay Sales', 'Surat');
INSERT INTO supplier VALUES (104, 'Poorvika Mobiles', 'Chennai');
INSERT INTO supplier VALUES (105, 'Sangeetha Mobiles', 'Bangalore');

-- Insert records into ITEM
INSERT INTO item VALUES (501, 101, 'Laptop', 55000.00);
INSERT INTO item VALUES (502, 103, 'Television', 45000.00);
INSERT INTO item VALUES (503, 101, 'Smartphone', 25000.00);
INSERT INTO item VALUES (504, 102, 'Laptop', 52000.00);
INSERT INTO item VALUES (505, 105, 'Headphones', 5000.00);

COMMIT;

-- Verify data
SELECT * FROM supplier;
SELECT * FROM item;

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: Min Price PL/SQL

Max Marks: (Misc)

Write a PL/SQL block that take input of item name from user and display name of supplier whose price is minimum for that item.

Hint

You need to join item and supplier to get the supplier name. Use a subquery to find the MIN(price) for the specific item_name.

flowchart TD
input[Input Name]
get_min[Find Min Price]
find_sup[Find Supplier]
output[Print Result]

input --> get_min
get_min --> find_sup
find_sup --> output

View Solution & Output

SET SERVEROUTPUT ON;

DECLARE
    v_item_name VARCHAR2(50) := '&item_name';
    v_sup_name supplier.sup_name%TYPE;
    v_min_price item.price%TYPE;
BEGIN
    -- [1] Find minimum price and supplier name for the given item
    SELECT s.sup_name, i.price 
    INTO v_sup_name, v_min_price
    FROM supplier s 
    JOIN item i ON s.sup_Id = i.sup_Id
    WHERE i.item_name = v_item_name
    AND i.price = (
        SELECT MIN(price) 
        FROM item 
        WHERE item_name = v_item_name
    );

    DBMS_OUTPUT.PUT_LINE('Item: ' || v_item_name);
    DBMS_OUTPUT.PUT_LINE('Cheapest Supplier: ' || v_sup_name);
    DBMS_OUTPUT.PUT_LINE('Lowest Price: ' || v_min_price);

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Error: Item not found in records.');
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('Error: Multiple suppliers have the same minimum price.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

Step-by-Step Explanation: 1. Initialization: Declare variables and/or constants to hold input values and query results within the PL/SQL block. 2. Logic Flow: Execute procedural logic or SQL queries (using JOINS or Subqueries) to retrieve and process the required information. 3. Completion: Output the results using DBMS_OUTPUT and handle potential errors like NO_DATA_FOUND in the EXCEPTION section.

Concept Deep Dive: JOINS vs Subqueries

While a JOIN connects two tables based on a common field (sup_Id), a subquery allows us to perform an aggregate operation (like MIN) before selecting the specific record that matches that aggregate. Combining them is the gold standard for "Top N" or "Bottom N" query types.

Q2: Viva Preparation

Max Marks: 5

Potential Viva Questions

1. Q: What is a SELF JOIN?
2. A: A join where a table is joined with itself.
3. Q: What is the difference between a Pre-defined and User-defined exception?
4. A: Pre-defined exceptions are standard system errors (like NO_DATA_FOUND); User-defined are established by the programmer to handle unique logic.
5. Q: Why use CONSTRAINT name in CREATE TABLE?
6. A: It allows you to easily refer to and manage (drop/disable) the constraint later by name.
7. Q: What is an INNER JOIN?
8. A: A join that returns records that have matching values in both tables.
9. Q: What is the purpose of the GROUP BY clause?
10. A: It groups rows that have the same values into summary rows, like "find the number of items for each supplier".
11. Q: Can we use aggregate functions in a WHERE clause?
12. A: No, aggregate functions must be used in the SELECT list or a HAVING clause.

Common Pitfalls

• Inconsistent Data: If two suppliers have the same price, SELECT INTO will error unless you use a Cursor or ROWNUM.
• Quote Errors: Always ensure user input strings are handled correctly if used directly in SQL.

---

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 2025