I'm confused by this line in one of the examples: uid int not null auto_increment. Why do we need not null?

Most columns are allowed to store NULL to indicate that the value is unknown. Say, someone's age or address. But sometimes you want to insist that a value is not allowed to be NULL, such as the Name or B-number or SSN.

Could you go over when to use char versus varchar? What are some things that are helpful to keep in mind when using these?

CHAR always takes up exactly the same amount of space, whether you use that space or not. It's a tiny, tiny bit faster than VARCHAR.

VARCHAR saves space for shorter strings, at the price of being a tiny, tiny bit slower.

So, it's a space/speed tradeoff, where frugality usually wins.

Very much like arrays in Java, where you pre-allocate the space but if you don't use it all, it's wasted.

Just to confirm my understanding, is char() a set number of character and varchar() variable number a characters up until the number identified in the parentethese?

For both, the stated number is the upper limit. But CHAR always takes all of it, while VARCHAR might take up less space if the contents are shorter.

Is it possible to have multiple datatypes within one column?

Not as far as I know.

Can we review signed and unsigned integers?

Very briefly. If we have an N-bit number, we can either use all N bits for "positive digits", with place values from 2⁰ (= 1) to 2^N-1. So, if N=4, the bits are 1, 2, 4 and 8, and we can create numbers from 0000 (= 0) to 1111 (= 15).

Alternatively, we can designate one of the bits as a sign bit, so half of our numbers become negative. So now with 4 bits we can only go up to 0111 (= 7) and down to -8.

Can you review ENUM syntax? In the example you give with "cat" preceding "dog", would you have to specify the enum sorting in the query?

ENUM syntax, and ENUM reading.

I specify the sort order in the definition of the column, not in the query.

Could you explain more about inserting data with auto-increment?

Glad to. We omit the auto-increment column in the INSERT statement, or alternatively, leave it NULL.

The number is then generated when we insert.

I'll demo, using the staff table in the cs304_db.

can you explain a situation where it would be wise to use something like auto_increment?

Lots of times. Any time you are creating entities, you want them to have unique numbers, but you don't care what the numbers are.

ID values for Posts to Instagram, or to Stack Overflow, or, heck, even to the IMDB. Review IDs in Yelp or Amazon.com.

Does last_insert_id remember the last id that you, personally, inserted, or does it find the last id that was inserted into the table, regardless of who did the inserting? If the latter is the case, how is that different from querying "select max(UID) from staff;"?

What a brilliant, amazing question! We will talk more about this later in the course when we talk about concurrency. It remembers the last id that you personnally inserted.

(Well, to be precise, the last insert ID in your connection to the MySQL server. If you exit and start again, it'll forget.)

Can you explain more about auto increment and last_insert_id().

Did the above help?

What happens to the other values that follow when we override a certain auto_increment value? Lets say we have uid 1,2,3,4 and we overrode 2 with 250. Do 3, and 4 still remain the same or do they automatically increase to 251, and 252?

If you update one row, only that one row is affected (barring certain exotic things we are not talking about). So, 3 and 4 remain the same.

do you get notified if you overwrite a preexistent key from an auto_increment counted table?

No. Updates are normal so they aren't considered exceptional. Also, we're supposed to know what we are doing. :-)

Where is the data stored and how?

In CS 230, you learned a lot about data structures. Consider two: trees and hashtables. Most Relational Databases are stored using a kind of tree (called a B-tree). The leaves of the tree are different disk blocks, and disks allow random access. So if I'm looking up Ms Marvel in a database whose primary key is Name, I don't have to read any disk blocks other than the one with Ms Marvel on it. I don't have to read past Antman, Black Widow and Ironman...

They can also be stored as hashtables, whether the hash table tells us which disk block to read.

I'm still a little confused about mysqldump. Could you please explain it more in class? / Could you please elaborate on how dump files work?

MySQL dump turns that data structure into a text file, allowing you to reconstruct the data structure if you need to, either as a backup, or to copy to another server, or just as a souvenir.

Is it possible to make the dump automatic? Like how you can have autosave on certain programs.

It's a nice idea, but not really. Databases are intended for high-speed updates, and saving the contents of the database on every update would defeat that that purpose.

That said, there are logging facilities that can be used to reconstruct the database from the last backup

how do we track the changes we have made and undo them if needed (or would we rely on version control eg. like git pull requests)?

Again, a good idea, but undercuts the goal of high speed. For our purposes in this class, we're going to insert, update and delete without keeping a record (though we could).

Can you add a primary key later if you didn’t when creating the table?

I think this is unlikely, because the primary key is used in ordering the data.

You could create a new table with the new primary key and copy things over, dropping the old table.

So is there no way to add a new column to the table after it has already been created and had items added into it?

Yes, there is. We'll learn about alter table later in the course.

If you want to update a table to include another column and drop it, will it delete the rows previously in the table? How do you preserve the data?

If you drop a table, the data is gone. You'd have to preserve the data in another table, or use a dump file.

- is there any modes to have it be machine-checked before deleting the entire table? If not is there no way to save the data if that ever happens?

Dump files are always a good safety net.

Be careful when dropping tables. There may be an "i-am-a-dummy" mode; I'm not sure.

- also need a primary key for the table for q1 for best practice?

Most tables will have a primary key, but not always.

- in what situations would you want a table without a key column?

Consider a table of people (uid,name,address,birthdate), including the Simpson family (1,"homer","472 Evergreen Terrace"). Now consider a table of hobbies: (uid,hobby), with entries like (1,"beer"), (1,"tv"), (1,"donuts").

The primary key would be the entire row, and its only usefulness would be in ensuring that we don't have any duplicates. Which is not nothing, but maybe not essential.