Identifying professors with highest course load in 2009 using relational database

Courses
1

I’m working with a database that has two tables:

teacher(TeacherID, FullName, Department, Pay)
classes(TeacherID, ClassCode, SectionNum, Term, AcademicYear)

I need to figure out which teachers taught the most classes during 2009. I’m having trouble coming up with the right relational algebra expression for this. Can anyone give me some pointers on how to approach this problem?

I think I need to start by filtering the classes table for 2009, then count the classes for each teacher. But I’m not sure how to get the maximum count or how to join this information with the teacher names. Any help would be really appreciated!

2

To tackle this problem, you’ll want to use a combination of selection, grouping, and joining operations. Here’s a step-by-step approach:

  1. Filter the classes table for 2009 using a selection operation.
  2. Group the filtered results by TeacherID and count the classes.
  3. Join this result with the teacher table to get the names.
  4. Order the result by the class count in descending order.
  5. Select the top result(s) to get the professor(s) with the highest load.

In SQL, this might look something like:

SELECT t.FullName, COUNT(*) as ClassCount
FROM teacher t
JOIN classes c ON t.TeacherID = c.TeacherID
WHERE c.AcademicYear = 2009
GROUP BY t.TeacherID, t.FullName
ORDER BY ClassCount DESC
LIMIT 1;

This query will give you the professor(s) who taught the most classes in 2009. You may need to adjust the LIMIT if there are ties for the top spot.

Hope this helps point you in the right direction!

3

hey surfingwave, i think you should just filter classes for 2009, group by teacherid to count classes, join with the teacher table, order by class count descending, and grab the top result. hope that helps, good luck!

4

Hey there SurfingWave! :wave:

I totally get where you’re coming from - those relational algebra expressions can be tricky! Have you considered using a subquery approach? It might make things a bit clearer.

What if you started by counting classes per teacher in 2009, then used that as a subquery to find the max count? Something like:

SELECT t.FullName, class_count.count
FROM teacher t
JOIN (
  SELECT TeacherID, COUNT(*) as count
  FROM classes
  WHERE AcademicYear = 2009
  GROUP BY TeacherID
) class_count ON t.TeacherID = class_count.TeacherID
WHERE class_count.count = (
  SELECT MAX(count)
  FROM (
    SELECT COUNT(*) as count
    FROM classes
    WHERE AcademicYear = 2009
    GROUP BY TeacherID
  ) max_count
)

This way, you’re breaking it down into smaller steps. What do you think? Does this help make the problem a bit more manageable?

I’m curious - what made you interested in tackling this particular problem? Are you working on a bigger project related to teacher workloads?