Welcome!
Everything is fine.

Referential Integrity

Today we'll mostly conclude our work with SQL. There will be more later in the course, but we'll definitely be taking a break from SQL.

Next time, ER diagrams!

Outline

  1. Announcements
  2. Recap of Referential Integrity
  3. Questions about it
  4. Work on Referential Integrity
  5. A little on CSV

Announcements

  • About half have submitted Queries.
    • If you need help, please ask!
    • Try to get it in soon
  • Contacts and Contributions due Wednesday

Assignment 4: ER

  • This will be our first pair assignment
  • I will assign the pairs, based on your self-reported availability
  • The matching algorithm is not easy and I may make mistakes, but I'll do my best
  • You are expected to work together with your partner, rather than "divide and conquer". We'll talk more about that idea later in the semester.
  • To gather that information, I built this web application that will probably remind you of whenisgood: my app is cs304when

Let's take a few minutes to fill that out now.

Partners for Today

As before, introduce yourself, find a few non-trivial things in common, and chat pleasantly.

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

Referential Integrity Recap

A fair number of you aren't feeling very comfortable with this material, so let's take our time here.

A relationship between tables, possibly enforced by the DBMS.

  • 1:n mappings
  • The 1 is a minimum. A violation is an orphan. E.g. a pet that has no owner
  • n can be zero (An owner with no pets — yet.)

Can be annoying to have DBMS enforce this if you want to have temporary violations: an employee that isn't yet in a department.

Refinteg Enforcement

  • Use InnoDB for both tables
  • Use Primary keys (or request INDEXes) for both tables
  • Specify the foreign key and the actions to take on update and delete.
    • cascade: delete orphans
    • restrict: prevent the operation
    • set null: set the foreign key to null.
    • no action: ???

Some Examples

  • cascade: if you delete a pet owner from your practice, delete all the pets, too
  • restrict: if someone tries to "rmdir" a directory, prevent it unless it's empty
  • set null: if a director is deleted from the WMDB, set the "director" field of their movies to NULL

Your Questions on Referential Integrity

quiz

Referential Integrity Discussion

ALBUM and TRACK tables, with ALBUM having a key of AID and TRACK using that as a foreign key

Breakout

  • discuss the questions below and decide whether the stated operation is:
    • fine (will succeed)
    • depends (might not succeed). What does it depend on?
    • forbidden

Here are the operations to discuss:

  • deletes from the referring table (TRACK)

    fine

  • deletes from the referenced table (ALBUM)

    depends on whether there are any tracks

  • updates to the referenced table (ALBUM)

    depends on whether there are any tracks

  • dropping both tables

    depends on whether there are any foreign keys (very likely). Reverse order is good (like Unix directories): drop the TRACK table first, then the ALBUM, because there then can't be any references.

  • entering data

    depends. fine for ALBUM, tracks have to have an ALBUM

Code for Today

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

cd ~/cs304
cp -r ~cs304flask/pub/downloads/mysql5-refinteg mysql5-refinteg
cd mysql5-refinteg

Music Examples

With your partner, view the files in VS Code, and then do the following:

mysql < album-setup.sql
mysql < album-data.sql
mysql < album-setup-refinteg.sql
mysql < album-listing.sql

Now, try the following. Discuss the results/behavior.

  • In the album table, change the AID for 21 to 17 
    update album set aid=17 where aid=1;
  • Look at the updated tables: 
    mysql < album-listing.sql

    Changing the AID in the album table doesn't change the AID for the tracks, so they get lost.

  • Delete 21 from the album and album2 tables. 
    delete from album where title ='21';
    delete from album2 where title ='21';
  • Look at the updated tables: 
    mysql < album-listing.sql

    Deleting 21 is prevented in the refinteg tables; there are orphans in the other tables.

Try changing the referential integrity to no action and see what it does.

no action is the same as restrict

Together

We'll gather together and discuss the results above.

CSV

Note, there are example files in your mysql5-refinteg folder to set up a owner and pet pair of tables. You might run them in this order:

  • vet-setup.sql to create the two tables
  • vet-refinteg-setup.sql to drop the prior ones and re-create them with referential integrity
  • vet-data.sql to insert some data

Now we can export as CSV and load into a new table

  • owners-and-pets.sql joins two tables to list pairs of names

This produces output that you can capture in a .tsv file. I've already done that for you in owners-and-pets.tsv, but you can try to re-create it. Do you remember the commands?

  • pet-owners-create-and-load.sql creates a table in your DB and loads the owners-and-pets.tsv file.

CSV and Excel

Let's do these steps together.

Do the following steps to see how you can edit a TSV/CSV file using Excel and then re-import it:

  1. Modify the name of a pet in your pet9 table.
  2. Write a batch file to capture the data in a TSV file. (this is easy)
  3. Check the file in the VSCode editor
  4. Edit the file in VS Code and save it
  5. Write a batch file to load the TSV file into the pet9 table (model it on pet-owners-create-and-load.sql)
  6. Load the edited TSV file and see that it works

For extra skill, try the following

  1. copy the TSV file to your local machine using SCP
  2. edit it with Excel, say to add a new pet or edit the names
  3. save and export as TSV
  4. copy the TSV file to Tempest using SCP
  5. Load the TSV file and see that it works.

One or both of those might be useful in the Contacts assignment.

Line Endings

In the last version, with scp-ing the file from/to Tempest, we will probably run into an issue with line-endings. The file might look fine, but when it loads into MySQL, the last column looks weird.

To fix it, use the dos2unix command:

dos2unix filename.tsv

Sometimes, Mac line endings are used, in which case, you can fix them like this:

mac2unix filename.tsv

Summary

  • Referential Integrity is an important idea
  • The DBMS can enforce referential integrity, but that's optional
  • There are choices about the actions of the DBMS: cascade, restrict, set null
  • CSV files are really useful