1

Problem: Compute the natural join of R and S. Which of the following tuples is in the result? Assume each tuple has schema (A,B,C,D).

Relation R 

| A | C |
|---|---|

| 3 | 3 |
| 6 | 4 |
| 2 | 3 |
| 3 | 5 |
| 7 | 1 |

Relation S

| B | C | D |
|---|---|---|
| 5 | 1 | 6 |
| 1 | 5 | 8 |
| 4 | 3 | 9 |

I'm not quite sure what it means by "assume each tuple has a schema of A,B,C,D". Does this mean the R relation has a scheme of ABCD although it only lists A and C? I should assume there's also B and D but columns B and D are blank?

Operating under that assumption, I got the answer wrong. The explanation says there's no (7,5) in R which there clearly is under column A. Could someone explain to me what I'm doing wrong or if I'm missing something? Thank you!

philipxy
  • 14,867
  • 6
  • 39
  • 83
Maggie Liu
  • 344
  • 1
  • 3
  • 15
  • Hi. Please [use text, not images/links, for text (including code, tables & ERDs)](https://meta.stackoverflow.com/q/285551/3404097). Use a link/image only for convenience to supplement text and/or for what cannot be given in text. And never give a diagram without a legend/key. Use edit functions to inline, not links, if you have the rep--make your post self-contained. – philipxy Dec 03 '18 at 21:42
  • I think professors should stop using natural joins. They are quite confusing and error prone for the real-world applications. – The Impaler Dec 03 '18 at 21:45
  • @philipxy hi sorry! what do you mean by use edit function to inline? Sorry thank you! Also ah okay so avoid image/links basically? So I should my post to copy paste the table onto here instead of the image? – Maggie Liu Dec 03 '18 at 21:47
  • Find their definition of natural join & show the steps of your work following it. Otherwise we can't tell you where you went right or wrong. Also otherwise you are just asking us to either find & copy their definition when you wouldn't or would have to write yet another definition--when we don't even know & must guess which one they mean. – philipxy Dec 03 '18 at 22:01
  • Click on 'edit' & look at the menu of icons for functions. You can click on edit help then advanced help. Yes, use text formatted as code for tables. There is online OCR. [There is online formatting from CSV to tables.](https://ozh.github.io/ascii-tables) But if you are giving a [mcve] it is better to give tables as tabulated initialization code. [There is online formatting for that too.](http://sqlfiddle.com) PS Look at the formatted version of your post before you think you are finished editing. PS *Really* don't use photos of hand work. – philipxy Dec 03 '18 at 22:02
  • Okay I'll take some time to repost this in the way that you've described. Will take some time lol thanks for all the resources and got it sorry – Maggie Liu Dec 03 '18 at 22:07
  • @TheImpaler Natural join for relations that have sets for headings is the simplest operator for explaining the semantics of the relational model. [Including even SQL.](https://stackoverflow.com/a/35034568/3404097). (Also predicate logic.) (Also Prolog/Datalog.) – philipxy Dec 03 '18 at 22:12
  • @philipxy edited the post! could you give me some feedback on formatting the post and let me know what you think? ! also Natural join definition: enforces equality on all attributes with same name, eliminates one copy of duplicate attributes, also performs a cross product. i know i already deleted it, but my handwritten work was attempting to follow the definition of a natural join! – Maggie Liu Dec 03 '18 at 22:27
  • Your edit is missing content from the quiz. So we can't address your interpretation of it.(Eg see my answer.) PS Please clarify via post edits, not comments. Give the quiz wording, and its answer wording. – philipxy Dec 03 '18 at 22:32
  • @philipxy ah yes just wanted to make sure my table format was readable thank you – Maggie Liu Dec 03 '18 at 22:34
  • Tables are great... but differently formatted... typo? – philipxy Dec 03 '18 at 22:35
  • Your old post content is available by clicking on 'edited'. – philipxy Dec 03 '18 at 22:55
  • @philipxy your comment regarding the natural join definition helped me figure it out. i feel super stupid hah. but i basically ignored the "order" of the tuples and kept the sets without the common relations which confused me a lot at the end. i eliminated the ones without the common relations and managed to figure it out. i'm so dumb i saw 7,5 in relation R (7 under A and 5 under C) and was confused by the feedback. – Maggie Liu Dec 03 '18 at 23:02
  • I don't understand what you mean by "common relations". Tables are relations. They have attributes in their headings & rows in their bodies. There is no order between rows. The order of things along a heading or row matters *in a picture* so we can pair up an attribute with a value but if instead in a picture we paired attributes & values within a row then we would not need horizontal picture order & we would only need a heading for an empty relation. PS It is not accurate or helpful to call yourself stupid. We are just uninformed & unpracticed in some things. – philipxy Dec 03 '18 at 23:08
  • sorry still getting use to the syntax @philipxy, I mean the common attributes (which in this case is Column C). – Maggie Liu Dec 03 '18 at 23:10
  • @philipxy Honest, I think it's good natural joins are taught in school so developers will know what not to use in the real world (sarcasm). The thing is, I've worked will all levels of developers, from the great ones to the bottom of the barrel. It's the latter that worries me, since you never know who will working in the application three years from now. I guess too many ghosts hound me already. – The Impaler Dec 04 '18 at 01:02

2 Answers2

1

The question doesn't say R has that scheme. It says the natural join of R & S has that scheme.

(There are many variations on what a relation is, what relational operators are available, how they work & what their symbols are. They are telling you to expect that the schema for the join of those two relations has columns A, B, C & D. You should already know that from the definitions in the course, but since they give it nobody should get that part wrong.)

You seem to be saying that your choice of a row in the natural join was 2. That's correct. The explanation says that a wrong choice can't be right because tuple (7,5) is not in R. They do not mean that (7,5) is a list of values "under column A". But that feedback is for choice 3, not choice 2. So the answer checking seems to have a bug. Let them know.

philipxy
  • 14,867
  • 6
  • 39
  • 83
  • Sorry I still don't understand. So you're saying when the question says "Assume each tuple has schema (A,B,C,D)", they're saying that the result of the natural join of R & S will have that schema. Sure. Got that. But under that the explanation says "This tuple would require (7,5) to be in R, which it is not." I'm confused. 7 and 5 are in table R no? – Maggie Liu Dec 03 '18 at 21:55
  • I missed your comment on my answer. They mean (7,5) as a tuple not as a list of values. See my edited answer. PS Your question doesn't make much sense right now with neither links nor the content from the links--please read it & edit it enough for it to make sense. – philipxy Dec 03 '18 at 23:42
  • Ah okay. I saw your edit on the post. Maybe I need to really define what a tuple is. So from what you're saying, having the values 7 and 5 under Column A does not necessarily mean I have a tuple of 7 and 5 in R? – Maggie Liu Dec 04 '18 at 00:03
  • I don't know what reasoning leads to or follows from the question in your comment. See my comment on your question re relations. Read the lecture notes re what a relation is & what natural join is. (Or the textbook--you can find it free online in pdf.) I repeat: The feedback is not using "(7,5)" as a list of values in column A of R. It is saying that a relation with schema (A C) joined with S holds tuple/row (7,1,5,8) if & only if the relation holds row/tuple (7,5). R doesn't have row/tuple (7,5) so the join doesn't have tuple/row (7,1,5,8). R has (3,3) so the join has (3,4,3,9). – philipxy Dec 04 '18 at 00:49
  • Okay I think I've got it. So the relation (which is a table) R does not hold the row 7, 5 (7 in column A and 5 in column C) therefore the answer 7,1,5,8 does not work. Sorry and thanks for your patience. I saw 7 and 5 in the relation but like you said they are just values. Whereas (7,1) or like you said (3,3) would be considered a row/tuple. I'm pretty sure I understand it now thank you! – Maggie Liu Dec 04 '18 at 03:52
  • That seems right, good luck. This will all be in the free online DB course notes & also the textbook by its professor Widom (et al). – philipxy Dec 04 '18 at 04:43
  • I just started reading A First Course in Database Systems (should've done this a long time ago) thank you for the little push! Have a great day @philipxy – Maggie Liu Dec 05 '18 at 23:20
1

The answer feedback is misleading and wrong, that would be the feedback if you choose (7,1,5,8)

Your answer is right.

For thoroughness: in a natural join you connect tuples on common attributes, in this case C is the attribute in common.

Your return tuples are:

  R        S
 A,C     B,C,D     A,B,C,D
(7,1) & (5,1,6) = (7,5,1,6)
(3,5) & (1,5,8) = (3,1,5,8)
(2,3) & (4,3,9) = (2,4,3,9)
(3,3) & (4,3,9) = (3,4,3,9) --Your answer, correct

I even found a Stanford doc defining a natural join, just in case they lived in a different universe than the rest of us, but they don't. It's just a bug in the quiz.

onedaywhen
  • 55,269
  • 12
  • 100
  • 138
Aaron Dietz
  • 10,137
  • 1
  • 14
  • 26