Quiz

  1. Could you explain a bit more about the integer datatypes and space (unsigned vs signed)?

    Sure. The number of bitpatterns for a given number of bits is exponential, but finite. If you have N bits, the number of bitpatterns is 2N. (Folks who have taken CS 240 know this by heart.)

    That means that if we devote 8 bits (1 byte) for representing a number, we have 28=256 possible numbers. We can assign them meanings like 0-255 (unsigned integers) or -128-127 (signed integers).

    The more bytes we devote to a number, the more numbers we can represent, and the more space they take up.

    See MySQL Integer Types

  2. How is varchar able to change the amount of storage it allocates? Is it something like Pointers?

    Nothing to do with pointers. Pointers just let you store the data someplace else in memory; it doesn't change the space necessary to store the data.

    Back in the olden days, each record was a fixed number of bytes. Think punched cards. But that led to a lot of wasted space in text fields. So, at the cost of slightly slower processing, VARCHAR allows for variable-length text fields by having variable-length records.

    So the record for "Li Na" might only have 5-6 bytes for the "name" field, while "Aryna Siarhiejeŭna Sabalenka" would take up a lot more space.

    Or, we can allow up to 256 characters for an email address, even though most email addresses are much shorter.

  3. When using enum, is this case sensitive? Will it matter whether we input ""CAT"" or ""cat"" when we insert values?

    By default, it's case insensitive. So if you define ENUM(Cat,Dog,Rat) and you store "CAT" and retrieve the data later, the database will return "Cat".

    The internal representation is just a small integer, based on the number of ENUM values, using the same 2N idea we mentioned earlier.

  4. Given that ENUM is mutually exclusive, how would we approach a situation where let's say an animal can be of two types (like a liger, it's both tiger and lion). In other words, do you still recommend using enum and call it a liger, or for things that can belong to multiple different categories, would we use SET? 

    Yeah, SET might be right in this case, or, since most combinations of animals (SET(Cat,Dog)) don't make a lot of sense, maybe just add Liger to your ENUM.

  5. I am a bit confused about why in the Key constraint part of the reading, the table with the key fails to add the content. I understand that keys are important when we are searching for values across tables, but I dont understand the essentials of why not having a primary key causes this error. Could we go over this in class? 

    If we already have (nm,name)=(123,"George Clooney") in our person table, where NM is the primary key, we can't add him again. The key (123) would not be unique.

    We get an error if we try to add him again. The error would be DUPLICATE KEY...

    Of course, we can add a different person named George Clooney. How do we know he's different? Because he has a different NM value.

  6. In the example where you insert values on the pets characteristics, you wrote 
    insert into pet1 values ... etc

    is this line both creating and initializing pet1, or would we need to do it separately before writing this line?"

    The pet1 table needs to already exist before the insert.

  7. In the integer datatypes and space section, there's a mediumint(9) example which purposefully ignores the 9. I'm curious, what does the 9 mean? Is it the same as char(9), like up to 9 digit numbers allowed?

    Good question! In the olden days, it was the display width, particularly with the ZEROFILL option, so a number like 123 would be displayed as 000000123.

    It's mostly deprecated nowadays, so you can ignore it if MySQL shows it to you. The important part is the mediumint.

  8. Can we please go over AUTO_INCREMENT? I did not understand how this would work practically. 

    Sure. The implementation is actually quite simple. Each table that has auto_increment just keeps a counter as part of its meta-data. Whenever an insert happens that needs an auto_increment value, it uses the value and increments the counter. So, if we do two consecutive inserts, they each get different ID values.

    Fun fact: if you dump out a table with auto_increment, the value is put in the dump file, so that it can be restored if the file is loaded. You could try this today.

  9. When using auto_increment, do you have to specify it when creating the table, or does every table have it automatically?

    You have to specify it when you create the table.

  10. Is there a function equivalent to auto_increment that would have a different format or would allow the format to be adjusted by the user like including letters in the beginning or within the id? For example, with Cnumbers they might as well be just an incremented number with 1 letter in front of them, so it seems redundant to have 2 columns per student: 1 with Cid and 1 with auto_increment id. However, when creating new entries for new students you wouldn't want to have people manually have to check the last id in the table to come up with the next Cid.Or like license plates that have to be unique but have both letters and numbers?

    It's a great idea, but I'm pretty sure that doesn't exist built-in to MySQL.

    So, maybe you maintain a simple integer in the database table, and just glue the 'C' onto the front when you are displaying it.

    You could create a function to do this. (MySQL allows you to create functions, though we will not be covering that).

  11. What is a good way to verify what we are deleting is actually what we want to delete? Is there like a similar ls command for SQL in this way?

    Great idea. That's what SELECT is for. So: 

    
mysql> SELECT * FROM person WHERE birthdate < '1961-12-04';
mysql> DELETE FROM person WHERE birthdate < '1961-12-04';

    I do this all the time: check what the WHERE clause matches, and then change the "SELECT *" to just "DELETE".

  12. When using mysql dump, you say that it will allow us to restore the database to an earlier point in time. How far back in time would it allow us to go back? Are we only able to restore the latest database, or could we restore one from days/weeks/months ago? 

    It allows you to go back to the state of the database when you did the dump. So, if you dump the database every night at 4am, and you put them in numbered files: wmdb.4, wmdb.3, wmdb.2, wmdb.1, you could wind back to any of those times.

    I have already implemented such a system for the CS server, and it keeps 4 versions of each database.