DBMS001

DBMS Practice Experiment

 

Design 7 tables as per the given requirements, with at least 10 rows per table, including primary keys and foreign keys wherever appropriate.

Your tables should be as follows (attributes are suggested but you may modify them if needed):

 

Table: Department Attribute Data Type Description dept_id (PK) INT Unique ID for department dept_name VARCHAR(50) Name of the department hod_name VARCHAR(50) Head of department name office_location VARCHAR(50) Location of the department office contact_email VARCHAR(50) Department email contact_phone VARCHAR(15) Department phone number established_year INT Year department was established

 

 

Table: Student Attribute Data Type Description student_id (PK) INT Unique ID for student first_name VARCHAR(50) Student’s first name last_name VARCHAR(50) Student’s last name gender CHAR(1) M/F dob DATE Date of birth dept_id (FK) INT References Department(dept_id) email VARCHAR(50) Student email phone VARCHAR(15) Student phone

 

Table: Faculty Attribute Data Type Description faculty_id (PK) INT Unique ID for faculty first_name VARCHAR(50) Faculty first name last_name VARCHAR(50) Faculty last name designation VARCHAR(30) Lecturer/Professor/Asst. Prof dept_id (FK) INT References Department(dept_id) email VARCHAR(50) Faculty email phone VARCHAR(15) Faculty contact number hire_date DATE Joining date

 

Table: Course Attribute Data Type Description course_id (PK) INT Unique ID for course course_name VARCHAR(50) Name of course credits INT Number of credits dept_id (FK) INT References Department(dept_id) faculty_id (FK) INT References Faculty(faculty_id) semester INT Semester offered max_enrollment INT Maximum students allowed

 

Table: Enrollment Attribute Data Type Description enroll_id (PK) INT Unique enrollment ID student_id (FK) INT References Student(student_id) course_id (FK) INT References Course(course_id) enrollment_date DATE Date of enrollment attendance_percentage DECIMAL(5,2) Attendance in % internal_marks DECIMAL(5,2) Internal assessment marks status VARCHAR(20) Active/Completed/Failed

 

Table: Exam Attribute Data Type Description exam_id (PK) INT Unique exam ID course_id (FK) INT References Course(course_id) exam_date DATE Date of exam exam_type VARCHAR(20) Mid/End/Makeup max_marks INT Maximum marks location VARCHAR(50) Exam hall invigilator VARCHAR(50) Name of invigilator

 

Table: Result Attribute Data Type Description result_id (PK) INT Unique result ID student_id (FK) INT References Student(student_id) course_id (FK) INT References Course(course_id) exam_id (FK) INT References Exam(exam_id) marks_obtained DECIMAL(5,2) Marks scored grade CHAR(2) Grade (A,B,C...) result_status VARCHAR(20) Pass/Fail

 

 

Keys and Relationships

·         Department → PK: dept_id

·         Student → FK: dept_id

·         Faculty → FK: dept_id

·         Course → FK: dept_id, faculty_id

·         Enrollment → FK: student_id, course_id

·         Exam → FK: course_id

·         Result → FK: student_id, course_id, exam_id

Write SQL queries for the following:

1.      List all students in the 'Computer Science' department.

SELECT first_name, last_name

FROM Student S

JOIN Department D ON S.dept_id = D.dept_id

WHERE D.dept_name = 'Computer Science';

2.      Find the total number of students enrolled in each course.

SELECT C.course_name, COUNT(E.student_id) AS total_students

FROM Enrollment E

JOIN Course C ON E.course_id = C.course_id

GROUP BY C.course_name;

3.      Retrieve faculty names and the courses they teach.

SELECT F.first_name, F.last_name, C.course_name

FROM Faculty F

JOIN Course C ON F.faculty_id = C.faculty_id;

4.      Get the details of students who have less than 75% attendance.

SELECT S.first_name, S.last_name, E.attendance_percentage

FROM Enrollment E

JOIN Student S ON E.student_id = S.student_id

WHERE E.attendance_percentage < 75;

5.      Find students who have failed in any course.

SELECT S.first_name, S.last_name, E.attendance_percentage

FROM Enrollment E

JOIN Student S ON E.student_id = S.student_id

WHERE E.attendance_percentage < 75;

6.      Display department-wise count of faculty members.

SELECT D.dept_name, COUNT(F.faculty_id) AS faculty_count

FROM Faculty F

JOIN Department D ON F.dept_id = D.dept_id

GROUP BY D.dept_name;

7.      List courses offered in semester 5 with more than 3 credits.

SELECT course_name, credits

FROM Course

WHERE semester = 5 AND credits > 3;

8.      Retrieve students along with the courses they are enrolled in.

SELECT S.first_name, S.last_name, C.course_name

FROM Enrollment E

JOIN Student S ON E.student_id = S.student_id

JOIN Course C ON E.course_id = C.course_id;

9.      Get the average marks of each student across all courses.

SELECT S.first_name, S.last_name, AVG(R.marks_obtained) AS avg_marks

FROM Result R

JOIN Student S ON R.student_id = S.student_id

GROUP BY S.first_name, S.last_name;

10.  Find the top 3 students with the highest average marks.

SELECT S.student_id, S.first_name, S.last_name, AVG(R.marks_obtained) AS avg_marks

FROM Result R

JOIN Student S ON R.student_id = S.student_id

GROUP BY S.student_id, S.first_name, S.last_name

ORDER BY avg_marks DESC

LIMIT 3;