Quiz

1. Why are 1:1 mappings rare? Could you give an example of one?

   Sure; I'd be glad to. Thanks to this question, I added some info to the reading: one to one mappings

   Let's consider an employee database. The columns might be things like EMPID (key), NAME, ADDRESS, PHONE, TITLE, SALARY, HIRE_DATE, ...

   We would probably keep them all in one table. But we don't have to. We could put, say, the SALARY info in another table, with columns EMPID (key), SALARY

   There would then be a 1:1 mapping between the EMPLOYEE table and the SALARY table.

   To look up an employee with NAME and SALARY would require a join. That's fine, but if we put everything in one table, the join is unnecessary. Joins are reasonably efficient, but they aren't free.

   Why might we do this? One possibility is that the privilege of viewing data in MySQL is controlled by table, rather than column. So, there might be more people allowed to VIEW the EMPLOYEE table than the SALARY table, keeping the latter information more private.

   Another case might be some data that is rarely used, so keeping it in another table means it doesn't have to get shuffled around along with the other, more frequently accessed data. Keeping tables and rows small is useful, so that data can fit in memory and so the database can avoid copying data from disk or across the network that won't be needed after all.

2. I am a little confused on question 3, could you go over it in class please.

   For sure. We just did, but I'm happy to answer any followup questions.

3. What are common mistakes people make when using joins? 

   I don't think they make a lot of mistakes. Getting the model of the data right in the first place is the biggest hurdle. Is a relationship one-to-many? Many-to-many? Is something open-ended (hobbies) or just one of a fixed set of choices (like a major)?

4. Can you go over the USING syntax in class? How would I use USING if I wanted to match rows on two columns instead of just one?

   Sure. USING is just a shorthand for a longer ON or WHERE clause, but has the advantage that the column names aren't repeated. Suppose an employee can be in only one dept, so they have a "deptid" field in the employee table. The following are all equivalent:

   
       SELECT * FROM employee INNER JOIN dept
       WHERE employee.deptid = dept.deptid
   
       SELECT * FROM employee INNER JOIN dept
       ON employee.deptid = dept.deptid
   
       SELECT * FROM employee INNER JOIN dept
       USING (deptid)
   
   
   

   As for your second question, suppose the CREDIT table is in a one-to-many (1:N) relationship with a table of role_comments. Recall that the CREDIT table has a key of (nm,tt), so we need both pieces of information to uniquely identify a role (such as Margot Robbie in Barbie).

   
   SELECT * FROM credit INNER JOIN role_comments USING (nm,tt);
   
   

5. In what case(s) would a mapping table have more columns/dimensions if any

   When you join two tables, you get all the columns from each. If table A has N columns and table B has M columns, you get N+M columns.

   If there are repeats (e.g. both have an deptid column), you get both: dept.deptid and employee.deptid, unless you use the USING syntax, in which case they are coallesced.

6. Like in basic queries, does the order of JOIN queries have a big effect on the outcome? Like would the order of the columns be switched or the result would be wrong?

   Typically not. For an INNER JOIN, the query optimizer will shuffle conditions around to try to do things in the most efficient way, and we should trust it. (Later, we may look at how to analyze queries and improve their efficiency.)

7. So if we want to use join, we use ON instead of WHERE to use conditioning?

   No. Both ON and USING are convenient shorthands, not requirements. You can do everything in the WHERE clause if you want, and pretend that ON and USING don't exist.

   But they were created for a reason, and you should probably learn to be more comfortable with them. Reading if not writing.

8. How are you able to verify that the joins operated correctly? Especially if you have a large dataset, what's the best way to sample and verify?

   That's a great question. You want to push on edge cases: what happens if the key doesn't match anything? What kind of result do I want? (Later, we'll learn about OUTER JOINS, but not yet). What if there's more than one match (if that's possible)?

   So consider zero, one and several matches.

9. Not necessary a material question but would it be possible to slow down just a bit when doing "follow along" code in class? Thank you!

   Absolutely, and thanks for reminding me.

   It occurs to me that some of the examples I did on Friday were ones I wanted you to mimic. Others were "here's a fun shell feature" that I mostly just wanted you to see.

   But please speak up if there's something that went by too quickly. Because once you are lost, it's hard to get caught up, and I really don't want that.

   I will try to make sure I don't go too quickly.