retagged by
782 views
5 votes
5 votes

Consider the following two relational schemas:

  • MovieExec(name, address, cert, netWorth)
  • Studio(name, address, presC)

Suppose we wish to require that one must have a net worth of at least $1000000$ Rupees to be president of a movie studio. The join operation if required can be performed on the attribute cert of MovieExec and PresC of Studio on equality condition. This join combines pairs of tuples consisting of a studio and an executive, such that the executive is the president of the studio. Which of the following expressions best represent this constraint?
(P) $\sigma_{\text{netWorth}>1000000} (\text{Studio} \bowtie_{\text{presC} = \text{cert}} \text{MovieExec}) \neq \Phi$
(Q) $\sigma_{\text{netWorth}<1000000} (\text{Studio} \bowtie_{\text{presC} = \text{cert}} \text{MovieExec}) = \Phi$
(R) $\Pi_{\text{presC}} (\text{Studio}) \subseteq \Pi_{\text{cert}} (\sigma_{\text{netWorth} \geq 1000000} (\text{MovieExec}))$
(S) $\Pi_{\text{presC}} (\text{Studio}) \subseteq \Pi_{\text{cert}} (\sigma_{\text{netWorth} \leq 1000000} (\text{MovieExec}))$
 

  1. P, Q and S
  2. P, Q and R
  3. Q and R
  4. P and S
retagged by

2 Answers

5 votes
5 votes
In question they have said that

When the relation MovieExec(name,address,cert,netWorth) is joined with Studio(name,address,presC) on condition that cert=presC, then only those names should come who are president of a movie studio and it will be possible only when netWorth>=1 Million Rupees.

Now, this will happen only when

$\sigma_{netWorth \geq 1000000}$ should appear as a clause in P and not $\sigma_{netWorth \gt 1000000}$.

So, P false.
3 votes
3 votes

MovieExec(name, address, cert, netWorth)
Studio(name, address, presC)

SQL:
Select M.Name
from MovieExec M, Studio S
Where M.Cert=S.PresC
And M.NetWorth >=1000000;

Now, This query will give the name of all the executives which are president of some Studio, and the essential requirement
of an executive to be a president of a studio is that his/her networth >=1000000.

Now, coming to options:


(P) is INCORRECT, as It will select those records, where the networth > 1000000, hence, it can be eliminated, as we're looking for presidents with networth >=1000000. And, result given by P will be subset of our required result.

(Q) is CORRECT, because in the Studio, there will be the records of those people only whose networth>=100000, and if we join Studio with MovieExec with the people with networth <100000, the result set will be empty, because there is no executive who is president of a studio and has networth <1000000.

(R) is also CORRECT, because netoworth>=1000000 is an essential requirement to be a president of studio, but everyone who has networth>=100,000 doesn't need to be a president, Hence the relation Studio will be a subset of MovieExec with executives with networth>1000000.

(S) is INCORRECT, because we can't establish a relation between the Relation Studio and the people with networth<=1000000, because the executives with <=100000 can be more than/less than/equal to the executive with networth >=100000.

Hence, the correct option is (C).
 

Answer:

Related questions