Quiz

1. Does primary key(aid,tid) mean the primary key is the value of both columns together?

   Yes, that's exactly right. We did this with the CREDIT table in the WMDB, where the key was (NM,TT). That means both values are required to uniquely identify a row.

2. How are indexes different from just using a primary key? / How are indexes better or different than a shared primary key?

   The primary key is how the data is organized (say in the B-tree) and is therefore always fast. Say the NM in the Person table.

   Looking up someone by name would involve linear search, which is O(n).

   But if we build an index, (say a hashtable) in addition to the B-tree, looking up by name is now O(1).

3. Could you please show an example in class on using indices to access specific column values?

   We don't use them directly. The database does. The query optimizer will use an index if it believes it's useful.

4. What does index do exactly?

   Speeds up queries that use the column(s) that the index is based on.

5. What is the difference between using a primary key versus another column (e.g., name) in an UPDATE statement?

   speed and, possibly, uniqueness. Giving a raise to employee ID 24601 is different from giving a raise to everyone named "Jean Val Jean" (there might be more than one).

6. How do we determine which column we should use for building an index?

   You can build one on any column you think might be useful, in the sense of often using that column in a query. So an index on name makes sense, but maybe not on birthdate.

   Why not build an index on every column, just in case? Well, there is a downside: they have to be kept up to date, which slows down inserts, updates and deletes. (Think about updating the hashtable of names.) So they aren't completely free.

7. Can you build an index on multiple columns at a time or just one?

   Yes.

8. Could you explain how Foreign keys are used in Referencial integrity? Where does the foreign key come from? Still abit confused on that part.

   Referential integrity means that a foreign key is valid: it references something that exists. Take our Pet and Owner. Each Pet has an Owner, so each row in the Pet table has an OID.

   The OID is a foreign key. It's key in a different table. It's not a key in the Pet table, since an owner might have more than one pet (Homer has 3 pets).

   But OID is a key in the Owner table.

   Referential integrity means that the owner exists.

   How could it not? The owner was deleted, but not the pet.

   So, before deleting an owner, we need to check the Pet table to see if there are any pets with that OID.

   Similarly, albums and tracks

9. Can you go over another simple example of referential integrity with a foreign key being a primary key in another table?

   Does the example above help?

10. What does it mean by "allowing the integrity to be checked quickly"? / How exactly do indexes serve as a way to check referential integrity?

    Before we delete Homer or any owner, we need to check the Pet table to see if Homer has any pets. An index helps make that fast.

11. Can we talk a little more about the usage of engine = InnoDB? / Can you explain a bit more about what InnoDB is? / still a bit confused on innodb. is referential integrity one way or two way? still a bit confused on that too.

    It's a different data structure for tables, compared to MyISAM, which used to be the default default.

    The InnoDB data structure has additional bookkeeping info to do referential integrity checking.

    Referential integrity is mostly one-way: making sure that the one in a one:many relationship doesn't become zero

12. Can we go over an example of importing from CSV to MySQL where one column is the primary key?

    Importing CSV is just like inserting a row. So

    
        insert into owner(oid,name,address,phone,balance) values
        (1,'homer','742 Evergreen Terrace','555-1212',523.88),
        (2,'obama',NULL,NULL,0);
    
    

    compared to importing a CSV file like:

    
        oid,name,address,phone,balance
        1,homer,742 Evergreen Terrace,555-1212,523.88
        2,obama,NULL,NULL,0
    
    

    Primary keys would only affect the importing if your CSV file has duplicate values for the primary key column, in which case you will get an error. You can't insert (1,Homer) twice into the Pet table. It's okay to have duplicates of the name, but not the OID.

13. What happens if the number of columns and the type of a table do not match the columns of the CSV file? Would we just get an error message?

    Yes, you'll get an error.