The Gateway to Computer Science Excellence
First time here? Checkout the FAQ!
x
+24 votes
2.4k views

Consider the relation Student (name, sex, marks), where the primary key is shown underlined, pertaining to students in a class that has at least one boy and one girl. What does the following relational algebra expression produce? (Note: $\rho$ is the rename operator).

$\pi_{name} \{\mathcal{r}_{sex=female} (Student) \} - \pi_{name} (Student_{(sex=female \wedge x=male \wedge marks \leq m)} \rho_{n, x, m}(Student))$

  1. names of girl students with the highest marks
  2. names of girl students with more marks than some boy student
  3. names of girl students with marks not less than some boy student
  4. names of girl students with more marks than all the boy students
asked in Databases by Veteran (59.4k points)
retagged | 2.4k views
+1
Image is not readable here !
+9

This is the correct form of this question:

+2
Not sure from where you got the image, it does not look valid RA expression !
0
image updated !
0
@akhilnadhpc - please change the rename operator sign, it looks like select, please correct it.
+1
1st Query = list the name of all female students

2nd Query= list the name of  all female students who has marks less than and equal to  at least one male student

1st Query-2nd Query= list the name of all female students with more marks than all the boy students
+15
Here is correct form of query-

4 Answers

+55 votes
Best answer

OPTION : (d)

​The following query states the following conditions:

Sex = F $\land$

x= M $\land$

Marks $\leq$ m

Let the relation Student(Name, Sex, Marks)

Name

Sex

Marks

S1

F

30

S2

F

10

S3

M

20


 

Student(Name, Sex, Marks) Relation is renamed as Student(x, x, m).

Taking cross product of the relations

No

Name

Sex

Marks

n

x

m

1

S1

F

30

S1

F

30

2

S1

F

30

S2

F

10

3

S1

F

30

S3

M

20

4

S2

F

10

S1

F

30

5

S2

F

10

S2

F

10

6

S2

F

10

S3

M

20

7

S3

M

20

S1

F

30

8

S3

M

20

S2

F

10

9

S3

M

20

S3

M

20

Selecting the tuple (row# 6 from the above table), which satisfies the condition (i.e.,)

Sex = F $\land$

x = M $\land$

Marks $\leq$ m

PROJECTING:

$\Pi_{name} = S2$

$\Pi_{name} ( \sigma_{sex=F} (student) ) = \begin{matrix} S1 \\ S2 \end{matrix}$​

Hence the query:

$\Pi_{name} \begin{bmatrix} \sigma_{sex=F} (student) \end{bmatrix} - \Pi_{name} \begin{bmatrix} student \bowtie \sigma_{x,x,m} (student) \\ sex = F \wedge \\ x= M \wedge \\ marks \leq m \end{bmatrix}$

$\begin{matrix} S1 \\ S2 \end{matrix} – S2 = S1$

Let us take another relation data of student(Name, Sex, marks)

Name

Sex

Marks

 

S1

M

100

> highest marks of M student

S2

F

50

> highest marks of F student

S3

M

40

 

S4

F

30

 


 

Taking the cross product

NO

Name

Sex

Marks

x

x

M

1

S1

M

100

S1

M

100

2

S1

M

100

S2

F

50

3

S1

M

100

S3

M

40

4

S1

M

100

S4

F

30

5

S2

F

50

S1

M

100

6

S2

F

50

S2

F

50

7

S2

F

50

S3

M

40

8

S2

F

50

S4

F

30

9

S3

M

100

S1

M

100

10

S3

M

100

S2

F

50

11

S3

M

100

S3

M

40

12

S3

M

100

S4

F

30

13

S4

F

50

S1

M

100

14

S4

F

50

S2

F

50

15

S4

F

50

S3

M

40

16

S4

F

50

S4

F

30


 

Consider the row numbers 5, 13, 15 from the above table,

$\begin{matrix} S2 \\ S4 \end{matrix}$ => F students who score less than equal to some M students.

$\Pi_{name} [ \sigma_{sex=F} (student) ] = \begin{matrix} S2 \\ S4 \end{matrix}$

Hence, the result of the query will be:

$\begin{matrix} S2 \\ S4 \end{matrix} - \begin{matrix} S2 \\ S4 \end{matrix} = empty relation

From the above relational data of table Student(Name, Sex, Marks)

(D) is the correct option

In short,

$\{ \geq \text{All} \} = \mid \text{universal}\mid - \mid < \text{some M} \mid$

$\{ > \text{All} \} = \mid \text{universal} \mid - \mid \leq \text{some M} \mid$

$\{ \geq some \} = \mid universal \mid - \mid < all M \mid$


 

Source : http://www.edugrabs.com/questions-based-on-relational-algebra/

 

answered by Boss (22k points)
edited by
0
upvote for steps
0
best explanation
0
very good explanation
0

Here answer D is not fully correct, what if no girl in the database has more marks than atleast one boy, then query return empty set , hence it need to be given in question that atleast one girl present with more marks than atleast one boy.

0
Its a theta join question .... right ??
+14 votes
Answer is D

because 1st query result = all Female candidate

2nd Query = All the Female Student Whose Marks Less than from Male Student

and Ques = 1st Query - 2nd Query = Girl Student with more marks than all the male Student
answered by Junior (963 points)
+3 votes

 

lets try to break the query into parts and then resolve

Lets take the second part :

Here, cross product of two copies of Student relation is carried out. To effectively write the selection criteria, one relation of student is renamed with new attributes as name to n,sex to x and marks to m.

Now after having taking the cross product, selection operation is performed on the result based on below criteria :

which selects all such females whose marks are less or equal to atleast one male student (atleast one male student M1 should exist whose marks must be either greater or equal to the marks of Female student F1 for the record to be selected by the selection statement) .

Now, if we look at the first part,

This selects all female students from the relation.

Now if we look the original problem, it is more or like A-B

A - B = A intersection B'

If we consider the second part of the problem as B which gives all female students which have marks either less or equal to atleast one male student

complement of this statement would be

all female students which have marks more than all the male students.

And this if taken intersection with all female students

will give all female students with marks more than all male students

So, Option (d) is the answer.

 

 

answered by Loyal (7.9k points)
0
Can you please explain the difference between Option A and Option D?
+1
I'll give you eg.

If girls have marks of these values (11,15,20)

And boys have marks of these values (4,8,12)

Then option A will return (20)

Option D will return (15,20)

See the diff. yourself.
0
Thank you :)
+3 votes

                 -----------------------------------------------       ---------------------------------------------------------------------------------

                                             A                                                                                                              B

B: The inner part finds out  "all female students who have got less or equal marks than all boys".

A: The outer part  "All female students"

Now A-B gives "All female students who have got more marks than all boys".      ans d)

In the inner part B: there is self-cross product with attribute names being renamed. So sex denotes attribute sex of the first table while x denotes sex attribute of the 2nd table. Similarly marks and m.

answered by Loyal (7.8k points)
edited by


Quick search syntax
tags tag:apple
author user:martin
title title:apple
content content:apple
exclude -tag:apple
force match +apple
views views:100
score score:10
answers answers:2
is accepted isaccepted:true
is closed isclosed:true

35,506 questions
42,827 answers
121,678 comments
42,181 users