IGNOU BCSL 34 SOLVED ASSIGNMENT

Included:

BCSL 34 2025 2026 - English

Course Code : BCSL-034

Title : DBMS Lab

Assignment Number :  BCA(III)/L-034/Assignment/2025-26

Maximum Marks :  50

Weightage : 25%

Last Date of Submission : 31st October, 2025(for July Session) 30th April, 2026(for January Session)

This assignment has only one question. Answer the question. This question carries 40 marks. Rest 10 marks are for viva voce. You may use illustrations and diagrams to enhance the explanation. Assumptions can be made wherever required. Please go through the guidelines regarding the assignments given in the programme guide for the format of presentation.

Q1. Design and implement a database system using any relational DBMS for a Student Academic Records Management System, demonstrating fundamental DBMS concepts such as table creation, relationships, CRUD operations, and SQL queries for data manipulation and retrieval.

Create Database Schema:

Create the following tables:

Students Table

• student_id (Primary Key, INT, Auto Increment)
• first_name (VARCHAR)
• last_name (VARCHAR)
• date_of_birth (DATE)
• email (VARCHAR, Unique)
• phone (VARCHAR)

Courses Table

• course_id (Primary Key, INT, Auto Increment)
• course_name (VARCHAR)
• credits (INT)
• semester (VARCHAR)

Enrollment Table

• enrollment_id (Primary Key, INT, Auto Increment)
• student_id (Foreign Key, INT)
• course_id (Foreign Key, INT)
• enrollment_date (DATE)
• grade(VARCHAR)

Relationships:

• A student can enroll in multiple courses.
• A course can have multiple students.

Draw an ER-diagram for the system.

Perform the following CRUD operations on your database:

• Insert new records into all tables.
• Read/display records from cach table.
• Update existing records (e.g., grade, contact details).
• Delete a student or a course entry.

SQL Queries

Write and execute the following SQL queries:

1. List all students with their enrollment details.

2. Retrieve all courses in which a specific student is enrolled.

3. Calculate the number of students enrolled in each course.

4. Find students who have secured grade 'A' in any course.

5. Retrieve the average grade (as alphabet, assuming A-4, B=3...) per course.

6. List all students who have not enrolled in any course.

7. Find courses that have the highest number of enrollments.

8. Retrieve student details sorted by last name.

9. Find all courses offered in a specific semester.

10. Retrieve total number of courses each student has enrolled in.

11. Display enrollment count per semester.

12. List students along with the number of courses they failed (assume F as fail).

13. Find the student who has scored the most A grades.

14. Display all students born before the year 2000.

15. Retrieve course-wise average grade for all enrolled students.

Documentation Checklist:

• Screenshot of table designs and data entry
• SQL query outputs (screenshots or printed result)
• ER-diagram (drawn using any tool or by hand)
• Short explanation of each query
• Summary of observations and learning

Note: You must perform the above said activities and also take prints of screenshots of the layouts, sample input and output along with the necessary documentation for this practical question. Assumptions can be made wherever necessary.

Q: How will I receive the PDF?
A: Immediately after payment, the download link will appear and be sent to your email.

Q: Is this hand-written or typed?
A: This is a professional typed computer PDF. You can use it as a reference for your handwritten submission.