IGNOU BCA BCSL 34 DBMS Lab Solved Assignment ENGLISH

Please make sure your assignment questions match your current IGNOU session

BCSL 034 (July 2026 - January 2027) - ENGLISH

Course Code : BCSL-034
Title : DBMS Lab
Assignment Number : BCA(III)/L-034/Assignment/2026-27
Maximum Marks : 50
Weightage : 25%
Last Date of Submission : 31st October, 2026 (for July 2026 Session)
: 30thApril, 2027 (for January 2027 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: (15 Marks)
Design and implement a database system using any relational DBMS for an Online Hospital
Appointment Management System, demonstrating fundamental DBMS concepts such as database
design, entity relationships, CRUD operations and SQL queries for data retrieval and analysis.
Create Database Schema
Create the following tables.
Patients Table
 patient_id (Primary Key, INT, Auto Increment)
 patient_name (VARCHAR)
 gender (VARCHAR)
 date_of_birth (DATE)
 phone_number (VARCHAR)
 city (VARCHAR)
Doctors Table
 doctor_id (Primary Key, INT, Auto Increment)
 doctor_name (VARCHAR)
 specialization (VARCHAR)
 consultation_fee (DECIMAL)
 experience (INT)
Appointments Table
 appointment_id (Primary Key, INT, Auto Increment)
 patient_id (Foreign Key)
 doctor_id (Foreign Key)
 appointment_date (DATE

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.

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.