0

I am trying to solve the following question.

If we look at the solution part b) from 39:

Which of the following Relational Algebra Statement produces this relation?

enter image description here

enter image description here

* represents Natural join.

I have a problem with this small part here: (σ_weekdays=’MWF’(Flight)) * Flight_leg

(σ_weekdays=’MWF’(Flight)) returns tuples DL3 and BA12 now natural join with Flight_leg, but there are two DL3 in that table.

Is the resulting tuples :

  1. DL3, Delta, MWF, 1, JFK, 6:23, LHR, 13:54
  2. DL3, Delta, MWF, 2, LHR, 15:20, TXL, 16:03
  3. BA12, British Airways, MWF, 1, LHR, 5:23, JFK, 13:24

Or,

  1. DL3, Delta, MWF, 1, JFK, 6:23, LHR, 13:54
  2. BA12, British Airways, MWF, 1, LHR, 5:23, JFK, 13:24

excluding the second tuple with duplicate name?

zcahfg2
  • 861
  • 1
  • 12
  • 27
  • 1. Please use text not images whenever possible. Like for all this question. (You can goolge unicode relational join.) Images cannot be searched for or cut & pasted. 2. Apparently "*" is natural join?. Please give a reference to or full description of the version of relational algebra you are using. 3. Your title doesn't make sense. Please make it clear & put it in your post body. 4. What is your problem and what is your question? (Read a definition of natural join.) – philipxy May 14 '17 at 03:48

2 Answers2

1

Find a defintion of natural join. It returns the set of tuple values that can be made by combining one tuple from each argument that share the same subtuple value for common attributes. The common attribute set here is {flight_number}. Each of the two DL3 tuples from the Flight restriction can be combined with the one DL3 tuple from Flight_leg, and the one BA12 tuple from the former table can be combined with the one BA12 tuple from the latter table. So the result is the first choice.

  1. DL3, Delta, MWF, 1, JFK, 6:23, LHR, 13:54
  2. DL3, Delta, MWF, 2, LHR, 15:20, TXL, 16:03
  3. BA12, British Airways, MWF, 1, LHR, 5:23, JFK, 13:24

(Why would it possibly be anything else?)

philipxy
  • 14,867
  • 6
  • 39
  • 83
-1

the answer here should be option e) Actually the '*' here as I can see represents Cartesian product and the thing about Natural join is that its explicitly been given here as ⋈ sign.

coming back to your question. In option e) Firstly it selects tuples where dep_time<11:00 from relation Flight_leg which are having arrival airport code as LHR for 5:23 and JFK for 6:23. Now the join here which you see is actually a theta-join, this gives us airport_code=arrival_airport_code as JFK and LHR from which if we project city we simply get London and NYC as result.

also sharing a link here to clear your doubt fully, check it out What does the multiply operator do relational algebra?

I would also suggest you visit wikipedia, or some standard DBMS book to know join and its types.

Shubh_das
  • 1
  • 3
  • The post is not asking for the answer to the exam question, it is asking about "this small part here"--a part of (b). PS 1. The bow ties are not natural join; they are equijoin. 2. The post says that here "\* represents Natural join". 3. There are many different versions of relational algebra, with different operators & symbols for operators & even notions of what a relation is. See the comment on the answer at your own link re \* as natural join. It is possible to make sense of these expressions using \* as product; then headings & operators must allow for duplicate attribute names. – philipxy Dec 01 '18 at 22:40
  • I agree with yousorry I missed that, its equi join, but can I also say its implicit that in equijoin, its doing natural join too? as given here https://stackoverflow.com/questions/7870155/difference-between-a-theta-join-equijoin-and-natural-join – Shubh_das Dec 03 '18 at 08:40
  • SQL NATURAL JOIN is defined as USING/equijoin over all common columns. So what? ["There are many different versions of relational algebra, with different operators & symbols for operators & even notions of what a relation is."](https://stackoverflow.com/a/52174474/3404097) In fact the book in the comment at the link in your answer has this question's airport database & it uses \* as natural join & it uses ⋈ as θ-join & it uses θ-join with = for θ for equijoin. (Fundamentals of Database Systems 6th ed 2011 Elmasri & Navathe p 164 Chapter 6 Table 6.1 Operations of Relational Algebra) – philipxy Dec 03 '18 at 10:44
  • Oh, okay. Thank you very much! – Shubh_das Dec 03 '18 at 10:57
  • Per my first comment, your answer doesn't answer the question. You could correct your answer to whatever you think is reasonable now. – philipxy Dec 03 '18 at 12:03