CS 582 — Database Management System II — Spring 2007

Midterm – Extra

Name:

Consider the following relation schema (the well-known student registration system)

Student(\underline{Id}, Name, DepId, Street)
Transcript(\underline{StudId, CrsCode, Semester}   , Grade)
Course(\underline{CrsCod} , DepId, Name)

As usual, key of a relation is underlined. Here, we assume that

• Grade has only four values A, B, C, and D which correspond to the grade points of 4, 3, 2, and 1.

• A student is said to take a class twice if he has enrolled in the same class more than one.

• The credit hours generated by a class is computed by the formula 3× number of students in the class.

• The credit hours generated by a department is the total number of credit hours generated by the classes offerred (or listed as the course belonging to the department) by the department.

Write relational algebra expression to answer the following:

• List the Ids of students who have taken at least one class twice;

• List the Ids of students who lives on ’Telshor’ and get only B and D in their study;

Write SQL commands to answer the following:

• Create a dean list from CS department for the dean (a student gets to list on the dean list if he/she get an GPA of 4.0)

• Create an ordered list (lowest to highest) of departments with their number of credit hours generated by the department for Spring 2007.