[Monday, Feb 21, 10:50] > can you clarify the answers put on for the spring 1999 midterm. I have a > question about Question 5 , part b), I see this as similar to Question 5, > part a) , then why is it slightly different from that answer. Dont we need > to include the natural join *Supplement for part a) too then ? Also in part > b, dont we need to project into name, and producer (is this missing) ? In 5a), the name of the nutrition supplement suffices. Therefore, the join with Supplement is not needed. In 5b), you must print the name and the producer. Once you have the name, you can add the producer with a join with Supplement. ------------------------------------------------------------------------------- [Monday, Feb 21, 11:05] > I have some questions regarding Exercise 5, 6, and 7. > > 5-b: The question asks us to print name and producer. However, the answer gi ven is > (pi Name (Supplement) - pi Name (sigma Name <> 'Vitamin C' (Contents)) ) * Supplement . > > Shouldn't it be something like, > pi Producer ((pi Name (Supplement) - pi Name (sigma Name <> 'Vitamin C ' (Contents))) * Supplement) ? > > If this is correct, does it print out both name and producer, or just producer? It prints only the producer, therefore it is incorrect. If you want name and producer, you must use PROJECT[Name, Producer](...) However, in this case the argument of the projection has exactly these two columns, therefore the projection is not needed. (The set difference returns a relation with only the column Name. Then a natural join is done with Supplement, which has the columns Name and Producer. So the Join acts only as a filter here, and its output also has exactly these two columns.) > > I'd also like to confirm if <> means 'not equal to.' Yes, you can write <> or != or =/= (a crossed =) for 'not equal two. The SQL standard only contains <>, so this is the most portable. Oracle understands <>, !=, ^= and another sign which is not in the ASCII set. > 6-c: When prefixing attribute with the relation name in natural join, does it matter which one of the relation names to use? > > The answer given is > 1 Select Contents.Name, Producer, Contents.Substance > 2 from Vitamin, Contents, Supplement > 3 where Contents.Substance = Vitamin.Substance > 4 and Contents.Name = Supplement.Name > 5 and Quantity > Daily_Value . > > Could Contents.Name and Contents.Substance (in line 1) be Supplement.N ame, and Vitamin.Name, respectively? > Are other combinations such as Contents.Name and Vitamin.Substance (fo r line 1) possible? > Or, am I always supposed to use the first relation of the join for the prefix (as in the answer above)? > No, if you have e.g. Contents.Substance = Vitamin.Substance it does not matter which of the two you use in the SELECT clause (since they are equal). However, SQL requires that if the column appears in more than one table, you must make the reference unique by prefixing Contents. or Vitamin. (although both will give the same result). This is a bit stupid, but it is this way (in the Standard and in Oracle). We will talk about this later in the course in more detail. > 7 Will the reverse engineering (of the translation from the ER-model into the relational model) such as this exercise also be excluded from the mid-term e xam? You mentioned that understanding of the translation might help us in the m id-term - does it mean that the reverse engineering is subject of the mid-term? > No, this also requires that we have treated the translation from the ER-model into the relational model. Which we have not yet completed. ------------------------------------------------------------------------------- [Wednesday, Feb 23, 16:05] There is an error in the solution to Exercise 5b): The condition Name <> 'Vitamin C' must be Substance <> 'Vitamin C'