🗄️ BCA Semester 2 RDBMS Lab¶

Prerequisites: Basic SQL knowledge, Database concepts, Oracle/MySQL installation

🎯 Lab Overview¶

Course: BCA Semester 2 - RDBMS
Database: Oracle 11g/MySQL 8.0
Duration: 3 hours per lab
Total Labs: 12

📋 Lab 1: Database Creation and Basic DDL¶

Objectives¶

Create database schema
Implement constraints
Understand data types

Exercise: Student Information System¶

-- Create database
CREATE DATABASE StudentInfo;
USE StudentInfo;

-- Create Student table
CREATE TABLE Student (
    RollNo NUMBER(5) PRIMARY KEY,
    Name VARCHAR2(50) NOT NULL,
    DOB DATE,
    Gender CHAR(1) CHECK (Gender IN ('M', 'F', 'O')),
    Email VARCHAR2(50) UNIQUE,
    Phone VARCHAR2(15),
    AdmissionDate DATE DEFAULT SYSDATE
);

-- Create Course table
CREATE TABLE Course (
    CourseCode VARCHAR2(10) PRIMARY KEY,
    CourseName VARCHAR2(50) NOT NULL,
    Credits NUMBER(2) CHECK (Credits BETWEEN 1 AND 6),
    Department VARCHAR2(30)
);

-- Create Enrollment table
CREATE TABLE Enrollment (
    RollNo NUMBER(5),
    CourseCode VARCHAR2(10),
    Semester NUMBER(2),
    EnrollmentDate DATE DEFAULT SYSDATE,
    Grade VARCHAR2(2),
    PRIMARY KEY (RollNo, CourseCode, Semester),
    FOREIGN KEY (RollNo) REFERENCES Student(RollNo),
    FOREIGN KEY (CourseCode) REFERENCES Course(CourseCode)
);

Tasks¶

Insert 10 sample students
Insert 5 courses
Enroll students in courses
Verify constraints work

📊 Lab 2: Advanced Queries and Joins¶

Objectives¶

Master complex SELECT queries
Implement different JOIN types
Use aggregate functions

Exercise: Complex Data Retrieval¶

-- Find students with multiple enrollments
SELECT s.RollNo, s.Name, COUNT(e.CourseCode) AS CourseCount
FROM Student s
JOIN Enrollment e ON s.RollNo = e.RollNo
GROUP BY s.RollNo, s.Name
HAVING COUNT(e.CourseCode) > 1;

-- Students with no enrollments
SELECT s.RollNo, s.Name
FROM Student s
LEFT JOIN Enrollment e ON s.RollNo = e.RollNo
WHERE e.CourseCode IS NULL;

-- Course-wise student count
SELECT c.CourseName, COUNT(e.RollNo) AS StudentCount
FROM Course c
LEFT JOIN Enrollment e ON c.CourseCode = e.CourseCode
GROUP BY c.CourseCode, c.CourseName
ORDER BY StudentCount DESC;

Tasks¶

Find students with specific grades
Calculate average credits per student
List courses with no enrollments
Generate semester-wise reports

🔧 Lab 3: PL/SQL Programming (Oracle)¶

Objectives¶

Create stored procedures
Implement triggers
Handle exceptions

Exercise: Automated Grade Calculation¶

-- Procedure to calculate GPA
CREATE OR REPLACE PROCEDURE CalculateGPA(
    p_RollNo IN Student.RollNo%TYPE,
    p_GPA OUT NUMBER
) AS
    v_TotalCredits NUMBER := 0;
    v_GradePoints NUMBER := 0;
BEGIN
    SELECT SUM(c.Credits), 
           SUM(CASE e.Grade
               WHEN 'A' THEN 10
               WHEN 'B' THEN 8
               WHEN 'C' THEN 6
               WHEN 'D' THEN 4
               ELSE 0 END)
    INTO v_TotalCredits, v_GradePoints
    FROM Enrollment e
    JOIN Course c ON e.CourseCode = c.CourseCode
    WHERE e.RollNo = p_RollNo;

    IF v_TotalCredits > 0 THEN
        p_GPA := v_GradePoints / v_TotalCredits;
    ELSE
        p_GPA := 0;
    END IF;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        p_GPA := 0;
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

-- Trigger for admission date validation
CREATE OR REPLACE TRIGGER ValidateAdmissionDate
BEFORE INSERT OR UPDATE ON Student
FOR EACH ROW
BEGIN
    IF :NEW.AdmissionDate > SYSDATE THEN
        RAISE_APPLICATION_ERROR(-20001, 'Admission date cannot be in future');
    END IF;
END;
/

Tasks¶

Create procedure for course registration
Implement trigger for grade validation
Add exception handling for edge cases
Test with sample data

📈 Lab 4: Views and Indexes¶

Objectives¶

Create database views
Implement indexes for performance
Optimize query performance

Exercise: Performance Optimization¶

-- Create view for student performance
CREATE OR REPLACE VIEW StudentPerformance AS
SELECT 
    s.RollNo,
    s.Name,
    COUNT(e.CourseCode) AS CoursesTaken,
    AVG(CASE e.Grade
        WHEN 'A' THEN 10
        WHEN 'B' THEN 8
        WHEN 'C' THEN 6
        WHEN 'D' THEN 4
        ELSE 0 END) AS GPA,
    MAX(e.Semester) AS CurrentSemester
FROM Student s
JOIN Enrollment e ON s.RollNo = e.RollNo
GROUP BY s.RollNo, s.Name;

-- Create indexes for performance
CREATE INDEX idx_student_name ON Student(Name);
CREATE INDEX idx_enrollment_rollno ON Enrollment(RollNo);
CREATE INDEX idx_enrollment_course ON Enrollment(CourseCode);

-- Analyze execution plan
EXPLAIN PLAN FOR
SELECT * FROM StudentPerformance WHERE Name LIKE 'A%';

Tasks¶

Create view for course statistics
Implement composite indexes
Compare query performance with/without indexes
Analyze execution plans

🔍 Lab 5: Database Administration¶

Objectives¶

User management
Backup and recovery
Security implementation

Exercise: Database Security¶

-- Create users with different privileges
CREATE USER student_user IDENTIFIED BY "stud123";
CREATE USER faculty_user IDENTIFIED BY "fac123";

-- Grant specific privileges
GRANT SELECT, INSERT ON Student TO student_user;
GRANT SELECT, INSERT, UPDATE ON Enrollment TO student_user;
GRANT ALL PRIVILEGES ON Student TO faculty_user;

-- Create role for common privileges
CREATE ROLE college_role;
GRANT SELECT ON Course TO college_role;
GRANT SELECT ON StudentPerformance TO college_role;
GRANT college_role TO student_user, faculty_user;

-- Backup procedure
CREATE OR REPLACE PROCEDURE BackupDatabase AS
BEGIN
    DBMS_OUTPUT.PUT_LINE('Starting backup at: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));

    -- Export command (simplified for lab)
    DBMS_DATAPUMP.EXPORT_DATABASE(
        job_name => 'STUDENT_BACKUP',
        directory => 'DATA_PUMP_DIR',
        dumpfile => 'student_backup.dmp'
    );

    DBMS_OUTPUT.PUT_LINE('Backup completed at: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
END;
/

Tasks¶

Create users with specific roles
Implement row-level security
Schedule regular backups
Test recovery procedures

📝 Lab Report Format¶

Report Structure¶

Lab No: [Number]
Date: [Date]
Duration: [Hours]

1. Objectives
   - Clear learning goals
   - Expected outcomes

2. Database Schema
   - ER diagram
   - Table structures
   - Relationships

3. Implementation
   - SQL commands used
   - Sample data
   - Screenshots of results

4. Challenges Faced
   - Technical issues
   - Solutions implemented
   - Lessons learned

5. Conclusion
   - Objectives achieved
   - Additional insights
   - Future improvements

🎓 Viva Questions¶

General Database Concepts¶

What is the difference between DELETE and TRUNCATE?
Explain ACID properties with examples.
What are database normal forms?
Differentiate between clustered and non-clustered indexes.

PL/SQL Specific¶

What is the difference between procedure and function?
Explain exception handling in PL/SQL.
What are triggers and their types?
How do you optimize PL/SQL performance?

Practical Scenarios¶

How would you handle concurrent database access?
What backup strategy would you implement?
How do you ensure data integrity?
Explain database security best practices.

⚡ Performance Tips¶

Query Optimization¶

✅ Use appropriate indexes
✅ Avoid SELECT * in production
✅ Use EXISTS instead of IN for subqueries
✅ Implement proper join conditions

PL/SQL Best Practices¶

✅ Use bulk operations for large datasets
✅ Implement proper exception handling
✅ Use bind variables instead of literals
✅ Avoid implicit data type conversions

🔍 Common Errors and Solutions¶

Error	Cause	Solution
ORA-01400	Cannot insert NULL	Check NOT NULL constraints
ORA-02291	Integrity constraint violated	Verify foreign key values exist
ORA-00904	Invalid identifier	Check column names and spelling
ORA-01031	Insufficient privileges	Grant necessary permissions

This lab manual covers BCA Semester 2 RDBMS practical requirements with comprehensive examples and viva preparation.