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

Paper Details

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

Max Marks: 10

Create following tables with appropriate constraints with primary key and foreign key. Insert minimum 5 records in each table. - doctor (doc_no, doc_name, city) - patient (pat_no, doc_no, pat_name, disease)

1. Doctor Table Creation

Create the doctor table with a primary key.

Hint

Start with the parent table (doctor) before the child table (patient). Use NUMBER for IDs and VARCHAR2 for names.

View Solution & Output

-- Create DOCTOR table
CREATE TABLE doctor (
    doc_no NUMBER(5) PRIMARY KEY,
    doc_name VARCHAR2(50) NOT NULL,
    city VARCHAR2(30)
);

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

Create the patient table linked to doctor.

flowchart TD
D[Doctor Table]
P[Patient Table]
D --> P

View Solution & Output

-- Create PATIENT table with Foreign Key
CREATE TABLE patient (
    pat_no NUMBER(5) PRIMARY KEY,
    doc_no NUMBER(5),
    pat_name VARCHAR2(50) NOT NULL,
    disease VARCHAR2(50),
    CONSTRAINT fk_doctor_patient 
        FOREIGN KEY (doc_no) 
        REFERENCES doctor(doc_no)
);

print "Table 'patient' created with FK to 'doctor'.";

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 DOCTOR
INSERT INTO doctor VALUES (101, 'Dr. Rahul Sharma', 'Mumbai');
INSERT INTO doctor VALUES (102, 'Dr. Priya Patel', 'Delhi');
INSERT INTO doctor VALUES (103, 'Dr. Amit Kumar', 'Surat');
INSERT INTO doctor VALUES (104, 'Dr. Sneha Gupta', 'Chennai');
INSERT INTO doctor VALUES (105, 'Dr. Vikram Singh', 'Bangalore');

-- Insert records into PATIENT
INSERT INTO patient VALUES (501, 101, 'Mr. Arun', 'Fever');
INSERT INTO patient VALUES (502, 103, 'Ms. Bela', 'Injury');
INSERT INTO patient VALUES (503, 101, 'Mr. Chandu', 'COVID-19');
INSERT INTO patient VALUES (504, 102, 'Ms. Deepa', 'Fever');
INSERT INTO patient VALUES (505, 105, 'Mr. Eshan', 'Diabetes');

COMMIT;

-- Verify data
SELECT * FROM doctor;
SELECT * FROM patient;

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: Specialist Finder PL/SQL

Max Marks: (Misc)

Write a PL/SQL block that take input of disease from user and display name of doctor who is treating that disease for patient.

Hint

You need to join doctor and patient on doc_no. Use &disease for user input and handle exceptions like NO_DATA_FOUND.

flowchart TD
input[Input Disease]
look_up[Find Patient]
find_doc[Find Doctor]
output[Print Result]

input --> look_up
look_up --> find_doc
find_doc --> output

View Solution & Output

SET SERVEROUTPUT ON;

DECLARE
    v_disease VARCHAR2(50) := '&disease_name';
    v_doc_name doctor.doc_name%TYPE;
    v_pat_name patient.pat_name%TYPE;
BEGIN
    -- [1] Join doctor and patient to find the pair treating the disease
    SELECT d.doc_name, p.pat_name 
    INTO v_doc_name, v_pat_name
    FROM doctor d 
    JOIN patient p ON d.doc_no = p.doc_no
    WHERE p.disease = v_disease
    AND ROWNUM = 1; -- Using ROWNUM if multiple patients have same disease

    DBMS_OUTPUT.PUT_LINE('Disease: ' || v_disease);
    DBMS_OUTPUT.PUT_LINE('Treating Doctor: ' || v_doc_name);
    DBMS_OUTPUT.PUT_LINE('Patient: ' || v_pat_name);

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Error: No doctor found for this disease.');
    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: ROWNUM

When using SELECT INTO, Oracle expects exactly one row. If your query returns 10 patients with "Fever", it will error with TOO_MANY_ROWS. Using ROWNUM = 1 picks the first match found, which is a common strategy when you just need a sample match.

Q2: Viva Preparation

Max Marks: 5

Potential Viva Questions

1. Q: What is a PRIMARY KEY constraint?
2. A: It uniquely identifies each record in a table and automatically enforces both NOT NULL and UNIQUE.
3. Q: Why use SET SERVEROUTPUT ON?
4. A: It enables the display of output generated by DBMS_OUTPUT.PUT_LINE in the SQL client.
5. Q: Difference between VARCHAR2 and CHAR?
6. A: VARCHAR2 is variable-length (saves space); CHAR is fixed-length (pads with spaces).
7. Q: What is the ROWNUM pseudo-column?
8. A: It assigns a unique number to each row in the result set based on the order they are retrieved.
9. Q: What is an Exception in PL/SQL?
10. A: An error condition during program execution. It can be internally defined by the system or explicitly defined by the user.
11. Q: How do you handle multiple doctors for the same disease?
12. A: By using a CURSOR to iterate through all matching rows instead of a SELECT INTO which only handles one row.

Common Pitfalls

• Exact String Matches: Fever and fever are different in typical RDBMS searches. Use UPPER(disease) = UPPER('&disease_name') for better results.
• Foreign Key Integrity: You cannot assign a patient to a doctor who isn't already in the doctor table.

---

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

---

Last Updated: April 2025