Recently, Bob the Builder coded up a Relational Query Language Translator (RQLT). The RQLT is simple and only operates on a single schema as given below:
Tool(tid, brand, cost)
Jobsite(location, compensation, task)
Toolbox(tbid, location)
Holds(tbid, tid) 

(a) Bob sets the RQLT to translate the following SQL query to Relational Algebra:
SELECT T.tid
FROM Tool T, Holds H, Toolbox B, Jobsite J
WHERE T.tid = H.tid AND H.tbid = B.tbid
AND B.location = J.location AND J.task = `Plumbing'}
What will be the equivalent Relational Algebra query?

(b) Bob switches the RQLT to translate Relational Algebra into Tuple Relational Calculus and passes in:

What will be the equivalent Tuple Relational Calculus Query?

(c) Bob then translates the following Tuple Relational Calculus query:

What will be the equivalent SQL Query?

(a) Your cousin, who is studying at a Bay Area university often noted for its sports programs, claims to have learned "Lance's Axioms" for reasoning about Functional Dependencies. He claims that one such axiom, is
Reversitivity: if A → B then B → A
Give an example relation instance that proves that Reversitivity is invalid. (for simplicity, use integers and/or single letters as your attribute values)

(b) After viewing your counterexample for part (a), your cousin launches into a review of the final scores of the "Big Game" over the past decade. He then claims that there is another axiom:
Kindatransitivity: if A → C and AB → C then B → C
Give an example relation instance that proves that Kindatransitivity is invalid.

(c) It turns out however, that your cousin almost got it right. In fact, there is a rule as follows:
Pseudotransitivity: if A → B and BC → D then AC → D
Show that Pseudotransitivity is in fact valid. Be sure to show the steps for your derivation.

(d) Why can't you use an example relation instance to answer part (c), as you did for parts (a) and (b) of this question?

(e) Consider the relation schema R(ABCDE) and the following functional dependencies on R: A → D, BC → E, D → AB
What are the candidate keys of R and what is the highest normal form of R. Explain.

Draw an E-R diagram for the following situation:
- This is a simplified model for reserving baseball tickets.
- There are teams, which are identified by the team name. Teams are also located in a city.
- Teams play with each other in games, which occur on a particular date at a particular time. Games are identified by a game ID, and each game has exactly two teams that play in it.
- A game is played in exactly one stadium.
- A stadium is identified by its name, and is also located in a city.
- Stadiums have seats, which have a section number, a row number, and a seat number.
- Ticket holders reserve seats for a game. Ticket holders are identified by their name.
- Some ticket holders are students (students get discounts, but we are not including that in the model).
Using SQL, convert your designed ER diagram to the relational model.

Consider the following relational schemas:
employee(person-name, street, city)
works(person-name, company-name, salary)
company(company-name, city)
manages(person-name, manager-name)
Write down the relational algebra (RA) and tuple relational calculus (TRC) for the following queries.

(a) Find the names of all employees who live in the same city and on the same street as do their managers.

(b) Find the names of all employees in this database who do not work for State Bank of India. Assume that there may be information in the database about people who do not work for any company right now.

(c) Assume the companies may be located in several cities. Find all companies located in every city in which HDFC Bank is located.