4.6k 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} \{\sigma_{sex=female} (\text{Student}) \} - \pi_{name} (\text{Student}_{(sex=female \wedge x=male \wedge marks \leq m)} \rho_{n, x, m}(\text{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

retagged | 4.6k views
+1
Image is not readable here !
+3
Not sure from where you got the image, it does not look valid RA expression !
0
image updated !
0
+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
+16
Here is correct form of query-
+1

OPTION : (D)

​The given query states the following conditions:

\boxed{\begin{align}\text{Sex} &= F \land \\ x&= M \land \\ \text{Marks} &\leq m \end{align}} \to (1)

Let the relation be $\text{Student} (\text{Name}, \text{Sex}, \text{Marks})$

 Name Sex Marks S1 F 30 S2 F 10 S3 M 20

$\text{Student} (\text{Name}, \text{Sex}, \text{Marks})$ Relation is renamed as $\text{Student}(n, 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 $(1)$ and PROJECTING $\Pi_{name} \implies \boxed{S2}$

$\Pi_{name} ( \sigma_{sex=F} (\text{Student}) ) = \boxed{\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}$

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

Let us take another relation data of $\text{Student}(\text{Name}, \text{Sex}, \text{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 30 S1 M 100 14 S4 F 30 S2 F 50 15 S4 F 30 S3 M 40 16 S4 F 30 S4 F 30

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

$\boxed{\begin{matrix} S2 \\ S4 \end{matrix}} \implies$  Female students who scored less than equal to some Male students.

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

Hence, the result of the query will be:

$\boxed{\begin{matrix} S2 \\ S4 \end{matrix}} -\boxed{ \begin{matrix} S2 \\ S4 \end{matrix}} = \{\}$

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

(D) is the correct option

In short,

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

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

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

by Boss (21.3k points)
edited
+1
upvote for steps
+1
best explanation
0
very good explanation
+1

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 ??
0
@pc kya sir aapnay to dil hi jeet liya nice
+1
There is no cross product sign mentioned in question. There should be X before rename query? Or do we write like the way it's mentioned above?
0
great explation
0
from where does the cross product come?
+1
Take right side of the query

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

This query is giving the female student , whose marks less than atleast one male student .

Now, subtract it from female student of original table.

This will give , those female student, whose marks greater than all male students.
0
Same doubt
I think Cartesian product performing based on the given condition.

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
by Junior (983 points)
0
2nd: Female student where every female has got some male who has higher marks than her.

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.

by Boss (27.6k points)
0
Can you please explain the difference between Option A and Option D?
+2
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 :)
+1
Upvoted for explaining 2nd query beautifully. Thank you!

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

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.

by Loyal (9.7k points)
edited

1st  -  Πname(σsex=F(Student)) - Here we get all female student name.

2nd  - πname(Student(sex=female∧x=male∧marks≤m)ρn,x,m(Student)) - Here every female student is mapped to male students then from condition  (marks≤m)  we get all female student whose marks is less than male students.

1st - 2nd give name of female student whose mark is greater than male student.

ans-D

by Junior (555 points)

1
2
3