Welcome!
Everything is fine.

DDL: Creating Tables and Managing Data

Today, we'll learn about creating tables and inserting data

Plan

  1. (10) Recap
  2. (20) quiz questions
  3. (20) Group work
  4. (10) Break (together)
  5. (25) Continue group work

Announcements

  • Tomorrow in L240!
  • Queries due tomorrow
  • Contacts and Contributions due next week
    • Contacts will require defining tables in your personal database, and
    • defining batch files running in your database that answer the queries,
    • given data I will give you.

Today's Partners

Try to find some things that you and your partner have in common!

  • 2 Ali+Hannah
  • 4 Anjali+Makana
  • 6 Claire+Kathy
  • 8 Daisy+Flora
  • 10 Bella+Fangzhong
  • 12 Ariel+Indira
  • 14 Maddie+Yuneydy
  • 16 Ashley Cong+Maya
  • 18 Jasmine+Sana
  • 20 Chloe+Malinda
  • 22 Ashley You+Sophia
  • 24 Mukhlisa+Nicole
  • 26 Diya+Linh
  • 28 Vaishu+Winnie
  • 30 solo Sophie

DDL Recap

Most people felt okay about the material, and no one felt uncomfortable; That's great!

  • Create a MySQL table by defining the table name, column names and types, and a primary key
  • primary key requires/guarantees uniqueness but ensures speed
  • Datatypes like int, char, varchar, date
  • Datatypes determine operations and storage costs
  • insert into table(col1,col2) values (val1,val2)
  • update table set col1 = val1, col2 = val2 where expression
  • delete from table where expression
  • mysqldump database > dumpfile.sql

Varchar

Some concern about varchar, so let's talk about that specifically.

  • If a database doesn't have enough room for some piece of data, say an exceptionally long name or address, there's no place to store it: part gets chopped off and lost. That's usually unacceptable, so we have to consider the worst case.
  • But the worst case is often rare
  • Back in the very, very olden days, database records always had fixed-length fields, so allowing for the worst case "wasted" precious space in very record.
  • (BTW, fixed-length fields are very efficient to access, much like arrays.)
  • So, varchar was invented. At the cost of a tiny amount of additional overhead for storage and access, data only takes as much space as it needs, so you can allow for the worst case, but not store tons of blanks in the common case.

Hence variable length strings.

  • Use fixed-length char(size) fields when appropriate (e.g. SSN, B-number)
  • Use variable-length varchar(worse_case_size) fields when the worst case is much longer than the common case.

Quiz Questions on DDL

Most felt pretty good about this, but some folks don't, which is fine.

I'll address your quiz questions

Code for Today

You can get started by copying the files for today like this:

cd ~/cs304
cp -r ~cs304flask/pub/downloads/mysql4-ddl mysql4-ddl
cd mysql4-ddl

We'll look at person-table.sql and run it. Note that it doesn't have a use statement at the top, so for each of us, it uses our default (personal) database.

MySQLdump

I'll demo by:

  • inserting some data
  • dumping
  • deleting the data
  • restoring from dump

Breakout 1

  • write a batch file called mypet.sql that creates a pet table for a vet office. I suggest you start with person-table.sql and "save as" mypet.sql and then edit.

The table should have columns like

  • name
  • dob (date of birth)
  • weight

I'm sure you can think of others. Get it working.

Insert some data into the table. Insert your own pets or fictional ones ("Crookshanks", "Snoopy", etc.)

Try UPDATE and DELETE as well.

Done

We'll gather together and ask questions. I'll demo the following:

  • pet{1,2,3}.sql different versions of a pet table
  • insert-pet{1,2,3}.sql different insertions

What key did you create for your table?

Did you use name as a key? I'll tell a story or two about my name....

Breakout #2

You can spend the rest of class continuing with this work, above, chatting among yourselves, or working on homework.