Relational Algebra and mySQL You are to use the following relational database sc
ID: 3793368 • Letter: R
Question
Relational Algebra and mySQL
You are to use the following relational database schema, primary keys are indicated in bold face. The purpose of this database is to help understand how the flow of money and political alliances affect the“causes” championed by our legislature.
Senator Business RichPerson Cause Contribution Alliance
(SID, name, netWorth, state, party)
(BID, name, netWorth, ceo-ssn)
(SSN, name, netWorth, actualTaxRate,party) (cause-name, politicalAlignment, type) (SID, SSN, amount, cause-name)
(SID1, SID2, cause-name)
Each of the relational attributes is as follows:
Senator.SID: The unique ID of a Senator
Senator.name: The name of a Senator
Senator.netWorth: The total monetary assets of a Senator
Senator.state: The home state of a Senator
Senator.party: The party affiliation (democrat,republican,other) of a Senator
Business.BID: The unique ID of a business
Business.name: The name of a business
Business.netWorth: Total assets of the business
Business.ceo-ssn: The unique social security number of the company’s CEO
RichPerson.SSN: SSN of a rich person
RichPerson.name: name of a rich person
RichPerson.netWorth: netWorth of a rich person
RichPerson.actualTaxRate: The real percentage rate of taxes this person paid once
all deductions accounted for.
RichPerson.party: The party affiliation of this person, i.e. Republican or Democrat
Cause.cause-name: name of the cause (example: “farm subsidies”)
Cause.politicalAlignment: group aligned with (left, right, mixed)
Cause.type-name: one of (environment, business law, international, social)
Contribution.SID: The senator the money was give to
Contribution.SSN: The SSN of the person giving the money
Contribution.amount: The amount of the donation
Contribution.cause-name: The cause that is being supported by the money
Alliance.SID1: First senator in the alliance
Alliance.SID2: Second senator in the alliance
Alliance.cause-name: The cause the senators are working on
2
For the database schema described above create the queries described using the Rela- tional Algebra or SQL queries as specified.
For the Relational Algebra queries you may not use Cartesian products. For the SQL queries you are not allowed to create temporary relations.
2.1. Names of Senators (5 points). Using the Relational Algebra, write a query which finds the names of the republican senators who have received contributions of more than $100,000 from a rich person who is a democrat.
2.2. Contributions from CEOs (5 points). Using the Relational Algebra, write a query which finds the names of senators who have received contributions from the CEOs of every business.
2.3. Names of CEOs (5 points). Using the Relational Algebra, write a query which find the names of rich people that are the CEO of some company and have an actualTaxRate of less then 10%.
2.4. Names of Senators (5 points). Using SQL, write a query which finds the names of the republican senators who have received contributions of more than $100,000 from a rich person who is a democrat.
2.5. Supporting a Cause (5 points). Using SQL, write a query which finds the pairs of names of senators where at least one is a democrat and they have formed an alliance for a cause whose alignment is “right”.
2.6. Total Contribution to a Cause (5 points). Using SQL, write a query that for each cause that has a total of $1,000,000 or more in contributions, reports the cause name and total amount of contributions for that cause.
Explanation / Answer
I have done your SQL queries:
#2.4
select S.name
from Senator as S join Contribution as C
on S.SID = C.SID
join RichPerson as R
on C.SSN = R.SSN
where S.party = 'Republican' and C.amount > 100000 and R.party = 'democrat';
#2.5
select S1.name as senator1, S2.name as senator2
from Alliance as A join Cause as C
on A.cause-name = C.cause-name
join Senator as S1 on S1.SID = A.SID1
join Senator as S2 on S2.SID = A.SID2
where C.politicalAlignment = 'right' and (A.SID1='democrat' or A.SID2 = 'democrat');
#2.6
select cause-name, sum(amount) as TotalContribution
from Contribution
where amount >= 1000000;