Quiz

1. I would like to know more about foreign keys.

   Sure. A foreign key is not a key in the current table, but it is a key in some other table. For example, in the Movie table, we store the ID (NM) of the director of the movie. That value is a key in the Person table, but not in the Movie table.

2. In a one-to-many relationship (like director -> movies), why does the "many" side (movies) store the foreign key instead of the "one" side (person)?

   Because it's a fixed, finite amount of space. A movie (in our world) has only one director, so there's exactly one director ID (NM). But a prolific director (Alfred Hitchcock, say), would have lots of movie IDs (TT) and so the list of TT values would be awkward to store. Imagine you are writing the data on a piece of paper, and you have a small box for "movies this person directed"

3. Using the example from the reading, if we use INNER JOIN where owner.oid = pet.oid, would the resulting query include every column in the owner table, as well as every column in the pet table? Also, in this case, the owner's oid would have to be the same as its own's pet oid.

   Do you mean "row" rather than column? Columns are listed after the SELECT keyword.

   Assuming you mean row, then the query results will list every match.

   It will not list any pets without owners (there shouldn't be any)

   It will not list any owners without pets (unlikely, but possible)

   But it will have every row in both tables if every pet has an owner and every own has a pet.

4. I got a bit tripped up on Q3 and am now kinda confused, what is the exact difference between the ON and USING keyword? I'm thinking that the ON keyword along with the WHERE clause can specify a specific movie or staff member for the JOIN query, but doesnt the USING query do the same, if we specify what movie or staff member in the WHERE clause too?

   ON and USING are very similar. ON is more flexible but more verbose. USING is simpler but not as general:

   
       FROM owner INNER JOIN pet USING(oid) ...
       FROM owner INNER JOIN pet ON(owner.oid=pet.oid) ...
       FROM movie INNER JOIN person ON(movie.director=person.nm) ...
       FROM movie INNER JOIN person USING(fails: no shared column names) ...
   
   

5. How is .... "JOIN pet ON (owner.oid = pet.oid)" different from "JOIN pet USING (oid)"? In what scenario should you use ON over USING? When column names are different?

   Yes, that's right. USING is a nice short-cut: clear and concise, but can't be used if the column names differ

   ON always works, but is a little less concise.

6. I'm still confused about the difference between join ON and join USING. Could you also use join ON for two tables with the same names?

   It's not the table names; it's the column names.

   You can *always* use ON.

   You can't always use USING.

   But when you can use USING, it's nice. I always do.

7. What is the best way to generate an intermediate table for many-many relationships?

   We'll talk about defining tables in about a week. But you have to build it just like any other table.

8. When might self-crosses be useful?

   When matching things in a set with other things in the set.

   For example, you could find co-stars by joining the PERSON table with the PERSON table

   You could find double-features by joining the MOVIE table with the MOVIE table

9. If joins are only virtual and don't change the underlying tables, why do databases still need to optimize joins so heavily?

   Because databases do those joins all the time, so they have to be quick to compute.

10. Would there be any case where a natural join is used?

    When you are absolutely sure of the columns that will be used (as in USING) in the join.

11. I'm a bit confused on what the natural join does -- why is there an issue when the pet has the same name as the owner?

    Because with a natural join, you would only get owners whose names match their pets names. Barack Obama and Buddy don't get listed.

    Natural join matches (as in USING) for all columns that are shared. So, consider:

    
        SELECT * FROM owner NATURAL JOIN pet;
        SELECT * FROM owner INNER JOIN pet USING (oid);
        SELECT * FROM owner INNER JOIN pet ON (owner.oid=pet.oid AND owner.name=pet.name);
    
    

    You wrote the first, thinking it means the same as the second, but it means the same as the third.

    I think it's better to be clear.

12. I'm confused about the difference between inner joins and outer joins. / How does an outer join differ from an inner join in practice?

    We will talk about outer joins later in the course. They are used when you don't want the non-matched rows to be omitted. E.g. Owners in the VET office with their pets, but also listing owners without pets.

13. So far I'm good! / no questions! / No questions! I understand the material at this point :)

    Great!