edited by
7,930 views
30 votes
30 votes

Consider the following relational database schemes:

  • COURSES (Cno, Name)
  • PRE_REQ(Cno, Pre_Cno)
  • COMPLETED (Student_no, Cno)

COURSES gives the number and name of all the available courses.

PRE_REQ gives the information about which courses are pre-requisites for a given course.

COMPLETED indicates what courses have been completed by students

Express the following using relational algebra:

List all the courses for which a student with Student_no 2310 has completed all the pre-requisites.

edited by

10 Answers

Best answer
27 votes
27 votes

$T_1$ will have all the available course numbers

$T_2$ will have all the course numbers completed by student2310

$T_3$  will have the combination of all the courses and the courses completed by student2310

$\text{PRE_REQ} - T_3$ (set minus operation) will return us all the entries of $\text{PRE_REQ}$ which are not there in $T_3,$

Suppose $\langle C_1,C_5\rangle$ is a particular tuple of $(\text{PRE-REQ} - T_3),$

Now what does it imply? $\implies$ It implies that $C_5$ is one of the prerequisite course for $C_1$ which has not been completed by $C_5$. Proof: If student2310 would have completed $C_5$ then definitely $\langle C_1,C_5 \rangle$ should have been there in  $T_3$ (remember $T_3$ is the combination of all the courses and the courses completed by student2310) and in that case $(\text{PRE_REQ} - T_3)$ can not have  $\langle C_1,C_5 \rangle$ as a tuple.

So, for any such $\langle C_1,C_5 \rangle$ tuple, $(\langle C_1,$ any  course id$\rangle)$ of $\text{PRE_REQ} - T_3, C_1$  should not be printed as output (Since there is some prerequisite course for $C_1$ which student2310 has not completed).

Now, suppose we have not got any tuple as a result of $(\text{PRE_REQ} - T_3)$ where $C_2$ is there under cno attribute $(\langle C_2,$ any course id$\rangle ),$ what does it imply?$\implies $ It implies that student2310 has completed all the prerequisite courses $C_2.$

Hence, in order to get the final result we need to project cno from $(\text{PRE_REQ} - T_3)$ and subtract it from $T_1.$

  • $T_1 \leftarrow \pi_{\text{cno}}(\text{COURSES})$
  • $T_2 \leftarrow \rho_{T_2(\text{std2310completedcourses})}(\pi_{\text{cno}}(\sigma_{\text{student_no} = 2310}(\text{COMPLETED})))$
  • $T_3 \leftarrow T_1 \times T_2$
  • $T_4 \leftarrow \rho_{T_4(\text{cno, pre_cno})}(\text{PRE_REQ}-T_3)$
  • $Result \leftarrow T_1 - \pi_{\text{cno}}(T_4)$
edited by
11 votes
11 votes

SQL query will be 

SELECT cno 
FROM Completed, Pre-Req  
WHERE student_no = '2310'  
GROUP BY cno  
HAVING pre-Cno IN (
    SELECT C.cno 
    FROM Completed AS C
    WHERE C.student_no = '2310';
    )
10 votes
10 votes
$S \leftarrow \pi_{Cno}(\sigma_{student\_no=2310}(COMPLETED))\\RESULT \leftarrow ((\rho_{(Course,Cno)}(PRE-REQ)) \div S )$

http://docdro.id/T8OxQBt
4 votes
4 votes

RA query: ΠCno.(PRE-REQ ⋈pre Cno.=Cno.   ΠCno.Sno.=2310(COMPLETED)))   

make it in two parts:

X= ΠCno.Sno.=2310(COMPLETED))      {X gives all the tuples in which 2310 is present}

YCno.(PRE-REQ pre Cno.=Cno.   X)   

I think Y is the required answer... correct me if i wrong.

Related questions

23 votes
23 votes
1 answer
1
Kathleen asked Sep 25, 2014
7,162 views
Given two union compatible relations $R_1(A, B)$ and $R_2 (C, D)$, what is the result of the operation $R_1 \Join_{ A = C \wedge B = D} R_2$?$R_1 \cup R_2$$R_1 \times R_2...
47 votes
47 votes
2 answers
4
Kathleen asked Sep 25, 2014
9,754 views
There are five records in a database.$$\begin{array}{|c|c|c|c|} \hline \textbf {Name} & \textbf {Age} & \textbf {Occupation} & \textbf{Category } \\\hline \text{Rama} & ...