Consider the following schema:
Broker(Id, Name) Account(Acct#, BrokerId, Gain)
Write the following query in relational algebra and in SQL: Find the names of all brokers who have made money in all accounts assigned to them (i.e., Gain > 0).
1. Algebra: The algebraic solution has a pitfall and is harder than the SQL-based solution. Note that the following is incorrect :
?Name(Broker (?Acct#,BrokerId(?Gain>0(Account)) / ?Acct# (Account)))
because it ?nds brokers who manage al l accounts and made money for all of them. For a correct solution, ?rst ?nd all broker-account associations where the broker lost money:
LoosingAccounts =
?Acct#,BrokerId(Account) ? ?Acct#,BrokerId(?Gain>0(Account))
The requisite query is then
?Name(Broker (?Id(Broker) ? ?BrokerId(LoosingAccounts)))
2. SQL:
SELECT B.Name
FROM Broker B
WHERE NOT EXISTS (
SELECT A.Acct#
FROM Account A
WHERE A.BrokerId = B.Id AND A.Gain <= 0)
You might also like to view...
When configuring MySQL on UNIX, a script called ____________________ creates the basic file directory and grant tables for user permissions automatically.
Fill in the blank(s) with the appropriate word(s).
?A strong security program begins by assessing the backgrounds of the employees in the organization.
Answer the following statement true (T) or false (F)
Case-Based Critical Thinking QuestionsCase 12-2Mother Goose Computing, Inc. provides computational biology consulting services. They are currently updating several of their systems. For the protein folding division, Mother Goose is planning to implement the new system module by module. This is called a ____ conversion.
A. phased B. direct C. parallel D. pilot
A company has been given a Class C address to be utilized for all devices. The company has several subnets and the largest subnet has 15 hosts. Which of the following represents the MINIMUM CIDR notation of this subnet mask?
A. /26 B. /27 C. /28 D. /29