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

Max Marks: 10

Create following tables with appropriate constraints with primary key and foreign key. Insert minimum 5 records in each table. - bus (bus_no, bus_type, capacity) - route (route_Id, bus_no, source, destination)

1. Bus Table Creation

Create the bus table with a primary key.

Hint

Always clear existing data or use CREATE TABLE and then INSERT. Use VARCHAR2 for types and NUMBER for capacity.

View Solution & Output

-- Create BUS table
CREATE TABLE bus (
    bus_no NUMBER(5) PRIMARY KEY,
    bus_type VARCHAR2(30) CHECK (bus_type IN ('AC', 'NON-AC', 'SLEEPER')),
    capacity NUMBER(3)
);

print "Table 'bus' 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. Route Table with Foreign Key

Create the route table linked to bus.

flowchart TD
B[Bus Table]
R[Route Table]
B --> R

View Solution & Output

-- Create ROUTE table with Foreign Key
CREATE TABLE route (
    route_Id NUMBER(5) PRIMARY KEY,
    bus_no NUMBER(5),
    source VARCHAR2(50) NOT NULL,
    destination VARCHAR2(50) NOT NULL,
    CONSTRAINT fk_bus_route 
        FOREIGN KEY (bus_no) 
        REFERENCES bus(bus_no)
);

print "Table 'route' created with FK to 'bus'.";

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 BUS
INSERT INTO bus VALUES (101, 'AC', 40);
INSERT INTO bus VALUES (102, 'NON-AC', 50);
INSERT INTO bus VALUES (103, 'SLEEPER', 30);
INSERT INTO bus VALUES (104, 'AC', 35);
INSERT INTO bus VALUES (105, 'NON-AC', 55);

-- Insert records into ROUTE
INSERT INTO route VALUES (501, 101, 'Surat', 'Mumbai');
INSERT INTO route VALUES (502, 103, 'Ahmedabad', 'Pune');
INSERT INTO route VALUES (503, 101, 'Surat', 'Ahmedabad');
INSERT INTO route VALUES (504, 102, 'Mumbai', 'Goa');
INSERT INTO route VALUES (505, 105, 'Pune', 'Surat');

COMMIT;

-- Verify data
SELECT * FROM bus;
SELECT * FROM route;

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: Route Lookup PL/SQL

Max Marks: (Misc)

Write a PL/SQL block that take input of route id from user and display bus type from bus table for that route.

Hint

Connect route and bus using bus_no. Use &route_id for user input and handle exceptions like NO_DATA_FOUND.

flowchart TD
input[Input ID]
look_up[Find Route]
find_bus[Find Bus Type]
output[Print Result]

input --> look_up
look_up --> find_bus
find_bus --> output

View Solution & Output

SET SERVEROUTPUT ON;

DECLARE
    v_route_id NUMBER := '&route_id_input';
    v_bus_type bus.bus_type%TYPE;
    v_source route.source%TYPE;
    v_dest route.destination%TYPE;
BEGIN
    -- [1] Find the bus type for the specified route
    SELECT b.bus_type, r.source, r.destination
    INTO v_bus_type, v_source, v_dest
    FROM bus b 
    JOIN route r ON b.bus_no = r.bus_no
    WHERE r.route_Id = v_route_id;

    DBMS_OUTPUT.PUT_LINE('Route ID: ' || v_route_id);
    DBMS_OUTPUT.PUT_LINE('From: ' || v_source || ' To: ' || v_dest);
    DBMS_OUTPUT.PUT_LINE('Bus Type: ' || v_bus_type);

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Error: Route record not found.');
    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: JOIN Logic in PL/SQL

Just like in SQL, PL/SQL blocks can use standard JOIN syntax. In this case, we use INNER JOIN to fetch the bus details paired with the requested route. This is much more efficient than using nested queries if you need multiple fields from both tables.

Q2: Viva Preparation

Max Marks: 5

Potential Viva Questions

1. Q: What is the purpose of COMMIT?
2. A: It permanently saves all data changes in the current transaction.
3. Q: What are DDL and DML?
4. A: DDL (Data Definition Language) defines structure like CREATE/DROP. DML (Data Manipulation Language) manages data like INSERT/UPDATE/DELETE.
5. Q: How can we rename a table in SQL?
6. A: Use RENAME old_table_name TO new_table_name;.
7. Q: What is an INNER JOIN?
8. A: It returns records that have matching values in both tables, effectively combining rows from two or more tables.
9. Q: What does the SQLERRM function do?
10. A: It returns the error message associated with the most recently raised exception.
11. Q: Why use VARCHAR2 instead of CHAR?
12. A: VARCHAR2 uses space only for the characters stored, while CHAR always uses the full defined length, padding with spaces.

Common Pitfalls

• Incorrect Foreign Key Values: You cannot insert a route for a bus_no that doesn't exist in the bus table.
• Indentation Error: Always ensure PL/SQL blocks correctly pair BEGIN and END;.

---

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 2025