-1

Given the database below, project the names of the students who are not enrolled in a course using relational algebra.

Students(snum, sname, major, standing, age, gpa)
Faculty(fid, fname, deptid)
Courses(cnum, cname, course_level, credits)
Offerings(onum, cnum, day, starttime, endtime, room, max_occupancy, fid)  
Enrolled(snum, onum)

I can get the snum of all students not enrolled in a course with:

π snum Students - π snum Enrolled

But how do I project the sname of the student with the snums that I find?

philipxy
  • 14,867
  • 6
  • 39
  • 83
b7889
  • 1
  • 1
  • Hi. Please give a reference to what relational algebra you are supposed to use, because there are many versions, with different operators & notions of relation. Then read about joins. Re querying see [this](https://stackoverflow.com/a/24425914/3404097) and [this](https://stackoverflow.com/a/43318188/3404097). – philipxy Feb 28 '18 at 04:21

1 Answers1

0

Every base table holds the rows that make a true proposition (statement) from some (characteristic) predicate (statement template parameterized by columns). The designer gives the predicates. The users keep the tables updated.

-- rows where student [snum] is named [sname] and has major [major] and ...
Students
-- rows where student [snum] is enrolled in offering [onum]
Enrolled

Every query result holds the rows that make a true proposition from some predicate. The predicate of a relation expression is combined from the predicates of its argument expressions depending on its predicate nonterminal. The DBMS evaluates the result.

/* rows where
    student [snum] is named [sname] and has major [major] and ...
AND student [snum] is enrolled in offering [onum]
*/
Student ⨝ Enrolled

AND gives NATURAL JOIN, ANDcondition gives RESTRICTcondition, EXISTScolumns gives PROJECTother columns. OR & AND NOT with the same columns on both sides give OR & MINUS. Etc.

/* rows where
THERE EXISTS sname, major, standing, age & gpa SUCH THAT
    student [snum] is named [sname] and has major [major] and ...
*/
π snum Students

/* rows where
THERE EXISTS onum SUCH THAT
    student [snum] is enrolled in offering [onum]
*/
π snum Enrolled

/* rows where
    (   THERE EXISTS sname, major, standing, age & gpa SUCH THAT
            student [snum] is named [sname] and has major [major] and ...
    AND NOT
        THERE EXISTS onum SUCH THAT
            student [snum] is enrolled in offering [onum]
    )
AND student [snum] is named [sname] and has major [major] and ...
*/
(π snum Students - π snum Enrolled) ⨝ Students

You can project out any columns that you don't want from that.

(Notice that we don't need to know constraints to query.)

Relational algebra for banking scenario
Forming a relational algebra query from an English description
Is there any rule of thumb to construct SQL query from a human-readable description?

philipxy
  • 14,867
  • 6
  • 39
  • 83