Quiz

1. Is there any drawback to just giving varchar(x) some massive number for x, such as 2**10?

   Denial of service attack.

2. In LOAD DATA local infile 'file.csv', it sounds like the keyword ""local"" sends data to the server to be read. That sounds like it is making the data reading process be an external process instead of a local one. So I am confused as to why the keyword is ""local""?

   It says the file is local (with the client) not on the server.

3. You said that when using LOAD DATA, the column names in the tsv should match the column names in the MySQL table. What if there are spaces, unusual characters, or superscripts in the column names (ex: ""Acceleration (m/s^2)"")?

   Probably you should edit your CSV file to rename the header or just remove the headers.

4. Can you explain why it’s easier for software to parse a TSV file over CSV? I thought we could specify what the delimiter is.

   The parsing is just as easy, but I think human error happens more with CSV. You might have a CSV file with a NAME column, and someone enters their name as "Anderson, Scott". Now, one of your rows has an extra column, and the "Scott" has moved over to the BIRTHDATE column, and, well, nothing good is going to happen.

5. How can we add columns to a table without wiping out the pre-existing data?

   Yes. We can use the alter table statement. We'll do that later this semester. We will not need to do it yet.

6. How can we index columns in a pre-existing table without modifying the table creation file?

   ALTER TABLE can add indexes

7. Is there a way to auto assign IDs without a matching column in a CSV file?

   Not as far as I know.

8. I'm a little confused on the difference between primary keys and indexes. Could you provide an example of what they look like on table?

   Well, I can't show you what they look like. But think about an index in the back of a book, listing the page numbers that something is on. The page number is the primary key. The index is additional.

   We might add an index for PERSON for NAME and MOVIE for TITLE, since we will often look things up by those values.

9. having some trouble understanding how index speeds up queries, can you give an example

   If you had find occurrences of "Rosalind Franklin" in a biology textbook, would you rather go page by page or look her up in the index?

10. How does an index treat rows with the same entry for the index column (ex. two people with the same name)?

    Presumably, it keeps a list of the locations of those rows, like our Rosalind Franklin example, whether they are the same ID or different.

11. I think this will be clearer if we see examples in class, but I'm a bit confused as to how using index(col_name) is different than doing select * from table where col_name = desired value. Does doing this the select way take O(n) time for some reason? Also, what information can you get using an indexed column?

    An index means we can look things up faster.

12. Can you over innoDB more in class?

    Sure. It's a data structure that has a bit more bookkeeping info in it, so it can enforce referential integrity, while MyISAM can't.

    There is probably some price to be paid in performance or storage for the extra power. Almost every choice involves tradeoffs.

13. I'm a little confused why a key would be updated and set null in the subordinate table, could you give an example of this?

    Sure. we'll work through some examples. If a DEPARTMENT in some company is abolished during a reorganization, we don't necessarily lay off the employees. We might set their department field to be NULL.

14. If a pet dies and its owner only has one pet, will both the owner and the pet be removed from the owner and the pet table?

    I don't see any reason to delete the owner. They may get another pet.

15. I'm still confused as to why the order of the drop tables commands matters. Why is it that "if we dropped the album table first, it would immediately render all the foreign keys in the track table invalid"?

    Good question. The TRACK table has a foreign key into the ALBUM department. So "Shake it Off" refers to "1989". But that album no longer exists, because none of the albums exist, because the table was dropped. So, that's not allowed.

16. Related to Q2: Is referential integrity a two-way street? Does saying an owner must have a pet mean the owner table must have a column with the primary key of the pet table?

    No. We want the foreign key to be valid. A pet *must* have an *owner*, but an owner need not have a pet (yet). An track *must* have an album, but an album need not have a track (yet).

    If it were two-way, it would be difficult to insert any data!

17. So to keep referential integrity, do we need to use the foreign key and index clauses when creating tables?

    We need the foreign key clauses. I think the INDEX is no longer required if it's a primary key.

18. Would you ever use an index on a primary key?

    We wouldn't. I think that part of the syntax is no longer required.