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

Paper Details

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

Max Marks: 10

Create following tables with appropriate constraints with primary key and foreign key. Insert minimum 5 records in each table. - course (course_id, course_name) - student (roll_no, course_id, student_name, total_marks)

1. Course Table Creation

Create the course table with a primary key.

Hint

Start with the course table as it is the "Parent" table referenced by the student table.

View Solution & Output

-- Create COURSE table
CREATE TABLE course (
    course_id NUMBER(5) PRIMARY KEY,
    course_name VARCHAR2(50) NOT NULL
);

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

Create the student table linked to course.

flowchart TD
C[Course Table]
S[Student Table]
C --> S

View Solution & Output

-- Create STUDENT table with Foreign Key
CREATE TABLE student (
    roll_no NUMBER(5) PRIMARY KEY,
    course_id NUMBER(5),
    student_name VARCHAR2(50) NOT NULL,
    total_marks NUMBER(5) CHECK (total_marks BETWEEN 0 AND 500),
    CONSTRAINT fk_course_student 
        FOREIGN KEY (course_id) 
        REFERENCES course(course_id)
);

print "Table 'student' created with FK to 'course'.";

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. Inserting Data

Insert minimum 5 records in each table.

View Solution & Output

-- Insert records into COURSE
INSERT INTO course VALUES (10, 'BCA');
INSERT INTO course VALUES (20, 'BBA');
INSERT INTO course VALUES (30, 'B.Com');
INSERT INTO course VALUES (40, 'MCA');
INSERT INTO course VALUES (50, 'MBA');

-- Insert records into STUDENT
INSERT INTO student VALUES (1, 10, 'Rahul Sharma', 450);
INSERT INTO student VALUES (2, 20, 'Priya Patel', 410);
INSERT INTO student VALUES (3, 10, 'Amit Kumar', 380);
INSERT INTO student VALUES (4, 40, 'Sneha Gupta', 470);
INSERT INTO student VALUES (5, 10, 'Vikram Singh', 460);

COMMIT;

-- Verify data
SELECT * FROM course;
SELECT * FROM student;

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: Max Marks PL/SQL

Max Marks: (Misc)

Write a PL/SQL block that take input of course name from user and display name of student whose total marks is maximum in that course.

Hint

Connect the student and course tables using course_id. Use &course_name for user input and a subquery to find MAX(total_marks).

flowchart TD
input[Input Course]
find_id[Find ID]
find_max[Find Topper]
output[Print Result]

input --> find_id
find_id --> find_max
find_max --> output

View Solution & Output

SET SERVEROUTPUT ON;

DECLARE
    v_course_name VARCHAR2(50) := '&course_name';
    v_course_id NUMBER;
    v_student_name student.student_name%TYPE;
    v_max_marks student.total_marks%TYPE;
BEGIN
    -- [1] Find course ID
    SELECT course_id INTO v_course_id
    FROM course
    WHERE course_name = v_course_name;

    -- [2] Find student with maximum marks in that course
    SELECT student_name, total_marks 
    INTO v_student_name, v_max_marks
    FROM student
    WHERE course_id = v_course_id
    AND total_marks = (
        SELECT MAX(total_marks) 
        FROM student 
        WHERE course_id = v_course_id
    );

    DBMS_OUTPUT.PUT_LINE('Course: ' || v_course_name);
    DBMS_OUTPUT.PUT_LINE('Topper: ' || v_student_name);
    DBMS_OUTPUT.PUT_LINE('Marks: ' || v_max_marks);

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Error: No data found for the given course.');
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('Error: Multiple students have the same maximum marks.');
    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: Integrity Constraints

Integrity constraints ensure your database remains reliable. A FOREIGN KEY (like course_id) ensures that students can only be added to courses that actually exist, preventing "orphaned" records in the database.

Q2: Viva Preparation

Max Marks: 5

Potential Viva Questions

1. Q: What is a PRIMARY KEY?
2. A: A field that uniquely identifies each record and cannot contain NULL values.
3. Q: Difference between BETWEEN and IN?
4. A: BETWEEN checks for a range of values (incl. endpoints); IN checks for membership in a specific list of values.
5. Q: When is ROLLBACK used?
6. A: To undo any changes made in the current transaction that haven't been COMMITTED yet.
7. Q: What is a Unique Key? How is it different from Primary Key?
8. A: A Unique Key also ensures uniqueness but it can accept one NULL value, whereas a Primary Key cannot.
9. Q: What is the purpose of the DESC keyword in ORDER BY?
10. A: It sorts the result set in descending order (largest to smallest).
11. Q: What is an Autonomous Transaction?
12. A: An independent transaction that can be committed or rolled back without affecting the main transaction.

Common Pitfalls

• Incomplete Inserts: If you skip course_id when inserting a student, the foreign key constraint will block the save unless you explicitly allow NULLs.
• Input Casing: If you search for "bca" but stored as "BCA", the query will return nothing. Use UPPER() for robust searches.

---

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

---

Last Updated: April 2025