Quiz

1. I am a little confused by syntax, could you show some examples for defining keys and foreign keys and demonstrate their utility?

   Let's look back at the music example

2. Wait how does the link to a foreign key remain even if the key changes?

   The UPDATE has to modify both tables, which it will do if you use "ON UPDATE CASCADE"

3. Can you explain again the difference between the order of dropping the album vs. the track?

   Sure. If you drop the album table first, all those tracks now become orphans. If you have "ON DELETE RESTRICT", orphans are forbidden, so MySQL will prevent you from dropping the album table.

   You'll get a "foreign key constraint" error.

   But if you drop the tracks first, that's allowed, and then you can drop the album table.

4. What is InnoDB exactly? I thought it was something with how mySQL processes the table (when you assign Engine= InnoDB) at a lower level, more like a setting that you can change, but I didn't think it was itself a table.

   InnoDB is a kind of data structure that is used to implement or represent a table. (There are alternatives, such as MyISAM.)

   Think of this as being on the other side of an abstraction barrier, like you learned about in CS 111 and CS 230, particularly CS 230. In some of your collections, insert was O(1) but delete was O(n), while in others, both operations were O(1) or O(log n).

   Similarly, in MySQL, choosing InnoDB as your table type allows MySQL to enforce referential integrity.

   MySQL cannot enforce referential integrity if you choose MyISAM. They're essentially like comments.

   You can always declare the referential integrity constraints, but with MyISAM, they have no power.

5. I have a question about efficiency, because I remember in homework 2 (queries), question 7, if the batch file was written in a certain way, it could take longer to run. Does this mean that there is a way to make things ""more efficient"" ourselves, even though the reading for today says we should not worry about efficiency and trust the execution plan of the database?

   Yes, I said efficiency should not be your major concern, and that you should generally trust the query optimizer.

   (The same is true in industry.)

   But, sometimes you notice that a query runs slowly. That happens with certain approaches in the Queries assignment: the query seems right and the output is right, but the query takes 30 seconds instead of 0.2 seconds.

   When that happens, interrupt the query and investigate. Consider alternatives. Try using "EXPLAIN". Talk to me.

6. I am still a little confused about TSVs, is the only difference from CSVs the formatting of the data?

   Yes. Using tabs as separators instead of commas.