It's the same equality that you're familiar with. If a row of the Person table has an addedby field that has the value of 17 and a row of the Staff table has a uid field that has the value 17, we can match those two rows by specifying that addedby=uid. It's just equality of the two integers.

That equality condition is used to infer that staff member 17 added that person to the Person table.

No. It usually does, but not always.

When the information to answer a query is in multiple tables. For example, "who added James Earl Jones" to the WMDB? requires both the staff and person tables.

Sure. We happen to have two people named Ashley in this class, so "first name" is not unique. It's also not uncommon to have duplicate last names. But for many classes, (first_name, last_name) would be a unique identifier, and hence a key.

More commonly, it happens with intermediate tables that are used to represent many:many mappings; stay tuned.

For now, yes. Later, we'll learn about outer joins.

When we join two tables, we get all the columns. If we join table A with 5 columns and table B with 3 columns, the join has 8 columns. So the foreign keys are still there. No data is lost.

The join just matches up rows from A with rows from B, with replacement. So if A has 20 rows and B has 10 rows, the join might have as many as 200 rows.

The join condition might discard some of those 200 rows, so it might be fewer. Maybe even only one row, such as the example in the first question.

Right; it does not save it anywhere. It "creates" the table for the purpose of the query and then discards it when it is done.

the ON keyword is just a way of stating the JOIN condition. Going back to the first example, the following are equivalent:

    
SELECT person.name,staff.name
FROM person,staff 
WHERE person.addedby=staff.uid and nm=469;

SELECT person.name,staff.name
FROM person INNER JOIN staff ON person.addedby=staff.uid
WHERE nm=469;

The mapping from owners to pets is one:many, and the mapping from pets to owners is many:one. So, we can implemented that by a foreign key indicating the (unique) OID (owner id) for a particular pet.

The mapping from an actor to their movies is one:many. For example, James Earl Jones was in Star Wars, The Lion King, Field of Dreams, and The Hunt for Red October (and many more).

The cast of a movie is similarly one:many.

The mapping from the set of actors to the set of movies is many:many, which we implement with an intermediate table storing the pairs (NM, TT)

Let's look at it together: many:many with intermediate table

Great question! Yes, James Earl Jones is a unique actor. He can be in lots of movies. That's one:many.

The set of actors and the set of movies have a many:many relationship between them.

Sure. They represent pairs of entities, such as maybe a pair of people. That's how we could represent that two people are friends, or married, or siblings or ...

A cross-product is the set of all possible pairs of elements drawn from two sets.

If I'm creating partners for class today, I'm drawing from the set of all possible pairs, so I'm drawing from the cross-product.

If we have a graph, the adjacency matrix is the cross-product of the vertices

I can't think of any good reason to use a natural join. It's at best a shorthand, and at worst a trap.

I suggest avoiding them.

But you should know that they exist.

We use aliasing when we need to refer to another occurrence of the same table in a single query.

Consider the following Python code:

def all_pairs(list1, list2):
    return [ (x,y) 
             for x in list1
             for y in list2 ]
all_pairs([1,2,3], [10,20,30])
mylist = [1,2,3]
all_pairs(mylist, mylist)

Just keep going:

SELECT person.name,staff.name,movie.title
FROM person INNER JOIN staff ON person.addedby=staff.uid
            INNER JOIN movie ON person.nm=movie.director
WHERE nm=1392;

It's a great question. For another project, I have a big Google doc describing all the tables and all the field names so that when I forget, I can look them up to determine their meaning, usage and other documentation.

Documentation is crucial.

I think you will.