1

In the 3rd part of the question below:

Study the tables given below and write relational algebra expressions for the queries that follow.

STUDENT(+ROLLNO, NAME, AGE, GENDER, ADDRESS, ADVISOR)
COURSE(+COURSEID, CNAME, CREDITS)
PROFESSOR(+PROFID, PNAME, PHONE)
ENROLLMENT(+ROLLNO, +COURSEID, GRADE)

Primary keys are preceded by a +. ADVISOR is a foreign key referring to PROFESSOR table. ROLLNO and COURSEID in ENROLLMENT are also foreign keys referring to THE primary keys with the same name.

  1. Names of femal students
  2. Names of male students along with advisor name
  3. Roll Number and name of students who have not enrolled for any course.

How do I represent NULL marks in relational algebra?

I projected STUDENT NAME and ROLLNO after assigning COURSEID from ENROLLMENT table as

COURSEID = NULL

Is this valid?

  • 1
    The question does not mention Nulls. Why are you considering them? – ypercubeᵀᴹ Jun 24 '18 at 17:08
  • No it's not valid,courseid is foreign key in enrollment table referencing to primary key in course table as you know primary keys cannot be NULL. – Sam Jun 24 '18 at 17:59
  • 2
    A student has not enrolled in any course if it does not exist an enrollment for that student regardless of course. There's no need for nulls – Lennart Jun 24 '18 at 18:41
  • 2
    please dont post pictures of text, but post text. – miracle173 Jun 24 '18 at 21:02
  • 1
    @AanandhiVB Relational algebra doesn't have nulls. – ypercubeᵀᴹ Jun 24 '18 at 21:05
3

There are no NULL values in relational algebra. Here you can use the antijoin operator ▷:

πROLLNO, NAME(STUDENT ▷ENROLLMENT)

that selects all records from STUDENT where the ROLLNO is not in ENROLLMENT.

In SQL there are NULL values. You may use a left outer join and select the rows where the courseid is NULL:

select STUDENT.ROLLNO, STUDENT.NAME
from STUDENT left outer join ENROLLMENT 
    on(STUDENT.ROLLNO=ENROLLMENT.ROLLNO)
where ENROLLMENT.COURSEID is null
  • 1
    I would upvote if you didn't use nulls in the SQL code either. A NOT EXISTS subquery would be much nicer. – ypercubeᵀᴹ Jun 24 '18 at 21:05
  • @ypercubeᵀᴹ there are different methods to get the result in SQL. The NOT EXISTS is the SQL equivalent to the antijoin operation of relational algebra. But I didn't want to be nice but wanted to show why the OP is talking about NULLs. From the comments to the OP one could conclude that there is no reason that the OP thinks about NULLs. – miracle173 Jun 24 '18 at 23:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.