edited by
17,660 views
65 votes
65 votes

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).

$\displaystyle \pi_{name} \{\sigma_{sex=female} (\text{Student}) \} - \pi_{name} (\text{Student}\bowtie _{(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
edited by

7 Answers

Best answer
152 votes
152 votes

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})$ $$\begin{array} {c c c} \hline \text{Name} & \text{Sex} & \text{Marks} \\\hline S1 & F & 30 \\\hline S2 & F & 10 \\\hline S3 & M & 20 \end{array}$$ $\text{Student} (\text{Name}, \text{Sex}, \text{Marks})$ Relation is renamed as $\text{Student}(n, x, m).$

Taking the cross product of the relations

$$\begin{array} {c c c} \hline \text{No.}  & \text{Name} & \text{Sex} & \text{Marks} & n & x & m  \\\hline 1 & S1 & F & 30 & S1 & F & 30 \\\hline 2 & S1 & F & 30 & S2 & F & 10 \\\hline 3 & S1 & F & 30 & S3 & M & 20 \\\hline  4 & S2 & F & 10 & S1 & F & 30 \\\hline 5 & S2 & F & 10 & S2 & F & 10 \\\hline  {\color{DarkBlue}{6}} & {\color{DarkBlue}{S2}}  & {\color{DarkBlue}{F}}  & {\color{DarkBlue}{10}} &  {\color{DarkBlue}{S3}} & {\color{DarkBlue}{M}}   & {\color{DarkBlue}{20}}  \\\hline 7 & S3 & M & 20 & S1 & F & 30 \\\hline 8 & S3 & M & 20 & S2 & M & 10 \\\hline 9 & S3 & M & 20 & S3 & M & 30  \end{array}$$

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})$ $$\begin{array} {c c c} \hline \text{Name} & \text{Sex} & \text{Marks} \\\hline S1 & M & 100 & \text{> highest marks of M student} \\\hline S2 & F & 50 & \text{> highest marks of F student} \\\hline S3 & M & 40 \\\hline S4 & F & 30 \end{array}$$

Taking the cross product $$\begin{array} {c c c} \hline \text{No.}  & \text{Name} & \text{Sex} & \text{Marks} & n & x & m  \\\hline  1 & S1 & M & 100 & S1 & M & 100  \\\hline  2 & S1 & M & 100 & S2 & F & 50  \\\hline 3 & S1 & M & 100 & S3 & M & 40   \\\hline 4 & S1 & M & 100 & S4 & F & 30  \\\hline  {\color{DarkBlue}{5}} & {\color{DarkBlue}{S2}}  & {\color{DarkBlue}{F}}  & {\color{DarkBlue}{50}} &  {\color{DarkBlue}{S1}} & {\color{DarkBlue}{M}}   & {\color{DarkBlue}{100}}  \\\hline 6 & S2 & F & 50 & S2 & F & 50  \\\hline 7 & S2 & F & 50 & S3 & F & 40 \\\hline 8 & S2 & F & 50 & S4 & F & 30  \\\hline 9 & S3 & M & 100 & S1 & M & 100   \\\hline 10 & S3 & M & 100 & S2 & F & 50  \\\hline 11 & S3 & M & 100 & S3 & M & 40 \\\hline 12 & S3 & M & 100 & S4 & F & 30  \\\hline {\color{DarkBlue}{13}} & {\color{DarkBlue}{S4}}  & {\color{DarkBlue}{ F}} & {\color{DarkBlue}{30}}  & {\color{DarkBlue}{S1}}  & {\color{DarkBlue}{M}}  & {\color{DarkBlue}{100}} \\\hline 14 &  S4 & F & 30 & S2 & F & 50 &   \\\hline  {\color{DarkBlue}{15}}  & {\color{DarkBlue}{S4}}  & {\color{DarkBlue}{ F}} & {\color{DarkBlue}{30}}  & {\color{DarkBlue}{S3}}  & {\color{DarkBlue}{M}}  & {\color{DarkBlue}{40}}   \\\hline 16 & S4 & F & 30 & S4 & F & 30   \\\hline  \end{array}$$

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$

edited by
33 votes
33 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.

22 votes
22 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
4 votes
4 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.

edited by
Answer:

Related questions

26 votes
26 votes
2 answers
2
Kathleen asked Sep 18, 2014
11,273 views
The order of an internal node in a $B+$ tree index is the maximum number of children it can have. Suppose that a child pointer takes $6$ bytes, the search field value tak...