CS 582 — Database Management System II— Spring 2007

Homework 1 — Solution for the second question

The homework has two parts.

1. Create your own web site on CS Web Server. Read how to do it from intranet.cs.nmsu.edu. Your website should provide the following information:

• Name

• Your highest degree, university

• List of classes that you have completed at NMSU

• List of your current classes

Answer: No answer for this.

2. Write relational algebral expressions for the following queries (given the Student Registration System database as described in the hand-out, or page 43 from the textbook):

• List all courses that are taught by professors who belong to the EE or MGT departments. Answer:

\begin{matrix}R = π_{CrsCode}(σ_{DeptId=′EE′ OR DeptId=′MGT′}(T eaching⊳⊲_{P rofId=Id}P rofessor)) \\ Answer = π_{CrsName,CrsCode,Descr}(Course⊳⊲R) \end{matrix}

R contains the course code satisfying the requirement of the query. The answer lists the name and description as well.

• List the names of all students who took courses both in spring 2007 and fall 2006.

Answer:

R_{1} = π_{StudId}(σ_{Semester=′F 2006′}(T ranscript))
R_{2} = π_{StudId}(σ_{Semester=′S2007′}(T ranscript))
R_{3} = R_{1} ∩ R_{2}
Answer = π_{Id,Name}(Student⊳⊲_{Id=StudId}R_{3})

Explanation: R_{1} is the list of StudIds who takes at least one course in Fall 2006. R_{2} is the list of StudIds who takes at least one course in Spring 2007. R_{3} collects all students who took courses both in spring 2007 and fall 2006. The answer contains the name and id of those students.

• List the names of all students who took courses from at least two professors in different departments. Answer:

R_{1} = π_{StudId,P rofId,DeptId}((T ranscript⊳⊲T eaching)⊳⊲_{P rofId=Id}P rofessor)
R_{2} = R_{1}⊳⊲R_{1}[StudId, P rof1, Dep1]
R_{3} = π_{StudId}(σ_{P rofId≠P rof1 AND DeptId≠Dep1}(R_{2}))
Answer = π_{Id,Name}(Student⊳⊲_{Id=StudId}R_{3})

Explanation: R_{1} is the list of StudIds with the ProfessorID, from whom the student takes at least one class, and the Department Id of the professor.R_{3} collects all student IDs satisfying the request of the query. The answer contains the name and id of those students.

• List the names of all students who got at least one A.

Answer:

\begin{matrix}R_{1} = π_{StudId}(σ_{Grade=′A′}(T ranscript)) \\ Answer = π_{Id,Name}(Student⊳⊲_{Id=StudId}R_{1}) \end{matrix}

• List the names of all students who got at least one A and at least one F.

Answer:

R_{1} = π_{StudId}(σ_{Grade=′A′}(T ranscript))
R_{2} = π_{StudId}(σ_{Grade=′F′}(T ranscript))

R_{3} = R_{1} ∩ R_{2}

Answer = π_{Id,Name}(Student⊳⊲_{Id=StudId}R_{3})