Tutorial 3

Returning to the same domain once again, you have identified the following relations:

• Teachers(name, room)
• TeacherTitles(teacher, title)
• Rooms(name, nrSeats)
• Courses(name, teacher, nrStudents)
• Classes(course, day, hour)

Your task for this exercise is to write a number of queries, both in SQL and using relational algebra.

Relational Algebra

For each of the following, write a relational-algebraic expression that

• lists all teachers and their rooms.
• finds the number of students for all courses.
• finds, for each course, the room in which it is held.
• lists all teachers that have classes on Mondays.
• finds, for each hour of each day, the number of courses starting a class at that time.
• your own query.

Also do the following exercises from "Database Systems: The Complete Book, 2/E":

• 2.4.1 c,d,g
• 5.2.1 b,d,f,g,i

If you have the first edition of the textbook, the following exercises are similar:

• 5.2.1 b,c,g
• 5.4.1 b,d,f,h,i

SQL queries

For each of the following, write an SQL query that

• finds all courses that have more that 20 students.
• lists all professors at the school.
• lists all courses and their teachers.
• finds all classes that have more students than there are seats in the room.
• your own query.

For each of the following, write at least two significantly different SQL queries that

• finds all teachers giving more than one course.
• finds all rooms that are used on Mondays.
• finds the room with the highest number of seats.
• lists all teachers along with their titles. Those without a title should be listed as having NULL.
• finds the teacher(s) with the highest average number of students on his or her (their) courses.

Translate the following SQL queries to relational-algebraic expressions:

  SELECT C.name, teacher
  FROM Courses C, Teachers T
  WHERE teacher = T.name;

  SELECT day, COUNT(DISTINCT teacher)
  FROM Courses C, Classes S
  WHERE C.name = S.course
  GROUP BY day;

Also do the following exercises from "Database Systems: The Complete Book, 2/E":

• 6.1.3 a,e
• 6.2.2 a,b,d
• 6.3.1 b,c
• 6.4.6 b,e,g

If you have the first edition of the textbook, the following exercises are similar:

• 6.1.3 d,b
• 6.2.2 c,a,e
• 6.3.1 b,c
• 6.4.6 b,e,g