Relational algebra with several joins

Question

I have to write a relational algebra expression for a query in order to build and optimize a query tree. I have trouble understanding how to write an expression for a query involving joining of several relations.

Relations are,

Supplier(snum, name, address, bdate, sex, pnum)

Plant(pnum, mgrnum, location)

Item(item#, location, pnum)

Sells(snum, item#,qty)

Mgrnum in plant is the snum of manager in supplier table (foreign key).

The query is,

for every item located in 'Denver', retrieve the item number, the controlling plant number, and the plant manager's name, address and the date of birth.

I like to write this first using Cartesian product than using joins so i can draw the initial query tree.

This is an attempt by a friend:

PROJECT item#, pnum, mgrnum, address, bdate( (SELECT location='Denver' (Plant) x Item) JOIN mgrnum=snum (Supplier))

I'm trying to understand how to arrive at this answer. Thank you.

Please show your attempt at answering the question, even if it's horribly wrong. You'll be much more likely to get good feedback. Start with the Cartesian product of the appropriate tables then apply selection and projection. — reaanb, Jan 25 '17 at 05:57
@PeeBee Your friend's attempt is missing the manager's name, isn't selecting matching records from the Cartesian join of Plant and Item, and is using a equijoin with Supplier rather than a Cartesian join. — reaanb, Jan 25 '17 at 07:48
We can't tell how to characterize what rows your friend's query returns unless you tell us what the algebra is, because when the operators are defined differently the meaning of the results are different, and it happens to matter here. — philipxy, Jan 25 '17 at 08:00
I have no idea which variant it is. I thought there was only one. Can you mention a few examples? — PeeBee, Jan 25 '17 at 08:10
How did you expect to answer your assignment if you don't know what the operators are or what a relation is? What's your textbook? The RA wikipedia page currently describes an algebra where relations have sets of attribute names. It uses natural join, so cartesian product is just join with no common attributes, and theta-join also requires no common attributes. Many algebras have relations with ordered but possibly duplicated attribute names. See the last link in my answer. — philipxy, Jan 25 '17 at 09:07

score 0 · Answer 1 · edited May 23 '17 at 12:01

for every item located in 'Denver', retrieve the item number, the controlling plant number, and the plant manager's name, address and the date of birth.

Since this is homework, here is a hint. You apparently want item#, pnum, name, bdate rows where:

(for some sex, mgrnum, location, snum,
        snum called name occupying address born bdate is sex and manages pnum
    and item# in location is controlled by pnum
    and location = 'Denver')

(Guessing at what your tables mean and what business rules apply, because you didn't explain clearly.)

Let's use an obvious shorthand:

(for some sex, mgrnum, location, snum,
        Supplier(snum, name, address, bdate, sex, pnum)
    and Item(item#, location, pnum)
    and location = 'Denver')

Recall that PROJECT keeps/drops attributes, RESTRICT/SELECT keeps/drops tuples, and if relations R & S hold tuples that make a true statement from their predicates R(...) & S(...) (respectively) then R NATURAL JOIN S returns tuples where R(...) AND S(...). (See this answer. Also my answer to Relational Algebra Cross Product and Natural Join.)

Relational algebra with several joins

1 Answers1