Air University Student ID : __________
Final Examination: Spring 2024
Subject: Database Systems Lab Time Allowed: 1 Hrs
Class: BSDS Max Marks: 50
Section(s):4TH A FM’s Name:
Course Code: CS12301L FM’s Signature:
INSTRUCTIONS
Internet and mobile phones are not allowed.
No LATE SUBMISSON will be accepted.
Rough work or writing on question paper will be considered as use of unfair means.
Scenario: UNIVERSITY DATABASE Management System
Description
You are task is to create a university database system,with tables , add data, create ERD and perform
queries. The university database consists of several tables including Students, Courses, Enrollments,
Professors, Departments, and Grades. Each table contains critical information and must adhere to the given
constraints and relationships.
Table Structures:
1. Students:
StudentID (Primary Key)
Name
DOB (Date of Birth)
DepartmentID (Foreign Key referencing Departments)
Page 1 of 5
2. Courses:
CourseID (Primary Key)
CourseName
DepartmentID (Foreign Key referencing Departments)
3. Enrollments:
EnrollmentID (Primary Key)
StudentID (Foreign Key referencing Students)
CourseID (Foreign Key referencing Courses)
EnrollmentDate
4. Professors:
ProfessorID (Primary Key)
Name
DepartmentID (Foreign Key referencing Departments)
Page 2 of 5
5. Departments:
DepartmentID (Primary Key)
DepartmentName
6. Grades:
GradeID (Primary Key)
EnrollmentID (Foreign Key referencing Enrollments)
Grade
Page 3 of 5
Constraints:
Each student can enroll in multiple courses.
Each course can have multiple students enrolled.
Each course is taught by one professor, but a professor can teach multiple courses.
Each department can have multiple courses and students.
5. Shipping: Stores information about shipping details for orders.
ShippingID (Primary Key)
OrderID (Foreign Key)
ShippingDate
ShippingAddress
Question 3:
Perform following queries on University Database Management System. (50)
1. List all students along with their respective department names.
2. Find the total number of students enrolled in each course.
3. Retrieve the names of professors who teach more than three courses.
4. Get the average grade for each course.
5. Find all students who have not enrolled in any courses.
6. List the courses offered by each department.
7. Retrieve the names of students along with their grades for a specific course (e.g., Database Systems).
8. Find the departments with the highest number of students.
Page 4 of 5
9. Get the list of students who have achieved a grade higher than 90 in any course.
10. Find the courses that have never been enrolled in by any student.
11. List all professors along with the number of courses they teach.
12. Get the details of students who are enrolled in the most courses.
13. Retrieve the list of courses taught by a specific professor (e.g., Professor John Doe).
14. Find the total number of courses offered by each department.
15. Get the list of students who have received the highest grade in each course.
Page 5 of 5
