CSCI A114 / INFO I111 Lecture Notes Seven First semester 2001-2002

Last time we looked at operations on sets.

The cartesian product was the only complicated operation.

Here's a way to create the result for a cartesian product of two sets.

The question now is: how can you take into account all the cases?

How can you skip none, and look at each case once?

Here's how.

Let's take A = {a, b, c } and B = {m, n, p}.

The cartesian product A x B can be constructed as follows:

a m 
a n 
a p
b m
b n
b p
c m
c n
c p

What's A x B x C?

Here's a picture:

So in producing all combinations we end up with the following triplets.

amx
amy
amz
anx
any
anz
apx
apy
apz
bmx
bmy
bmz
bnx
bny
bnz
bpx
bpy
bpz
cmx
cmy
cmz
cnx
cny
cnz
cpx
cpy
cpz

How does the picture changes if B = {m, n} only?

How does the picture changes if C = {x, y} only?

What's the picture for A x B x C where

• A = {a, b, c}
• B = {m}
• C = {x, y}

Calculate before and after you draw the picture.

Now take

• A = {(a, m), (a, n), (b, p)}
• B = {(t, x), (t, z)}

Once this is clear we will move on to more queries.

Mostly we will cover the queries from the previous lecture.

But here's also some new material about the homework that might illustrate some of the cartesian product material.

Suppose we have

• BOOKS
• PATRONS

Are these related in any way?

Do they interact in any way?

I suppose PATRONS actually BORROW from the set of BOOKS.

So now we have a link.

Three tables.

PATRONS also RETURN those BOOKS that they have borrowed, when they're due.

So now we have four tables.

Both borrowing a book and returning a book happens on a certain date.

Assume we have 7 (seven) books in the library.

(They have been listed in the assignment text).

Assume we have three patrons:

• Larry Bird
• Michael Jordan
• Tony Kukoc

• Friedman and
• Salomaa

Everything is due two weeks later so ,he'll have to return them on or before Sep 24, 2001.

Now Jordan comes in and checks out

• Barendregt
• Conway, and
• Gosling

They're due on Sep 28, 2001 (14 days later).

One table of the four we hinted above is going to be changed.

There are five new records in it.

Fine, let's ask what books are out.

Need to use BOOKS and BORROW for that.

Let's try:

SELECT *
FROM BOOKS, BORROW;

What does it have in common with the cartesian product?

Well, it is the cartesian product.

Perhaps having less columns would make it clearer.

But remember: table contains rows.

Each row is an instance of the entity the table stands for.

So how do we restrict the output?

How about matching the records?

For example BOOK.ISBN should match BORROW.Book in the output.

That's called a JOIN.

It's a subset of the cartesian product.

It's a meaningful result.

So we have the following query:

SELECT * 
FROM BOOKS, BORROW
WHERE BOOKS.ISBN = BORROW.Book;

So we project only a few columns of the output.

SELECT Author, Title, Patron
FROM BOOKS, BORROW
WHERE BOOKS.ISBN = BORROW.Book;

We need to bring the price in the picture.

SELECT Author, Title, Patron, Price
FROM BOOKS, BORROW
WHERE BOOKS.ISBN = BORROW.Book;

How does this compare to the average price of a book.

Let's keep moving though.

Now on Sep 16, 2001

• Bird returns Salomaa (marks it with a 3.5), and checks out Knuth
• Jordan returns Conway (and marks it with a 3)
• Kukoc comes and checks out Conway (after Jordan returns it)

SELECT *
FROM BOOKS
WHERE BOOKS.ISBN = BORROW.Book;

So the only way is to start from the cartesian product.

SELECT *
FROM BOOKS, BORROW
WHERE BOOKS.ISBN = BORROW.Book;

SELECT *
FROM BOOKS, BORROW
WHERE ISBN = Book;

The RETURN table must be modified when books return.

We also see there must be an extra column in it (for rating).

Now we can ask questions of the database.

1. List all books currently checked out.

This is the same as before.

We don't need to modify the query at all.

Only the data has been changed.

That means all the books that

• have never been checked out, or
• have been checked out and have been returned

But how do we know if a book is still checked out? The way we put together our design, a book can be returned many times.

An alternative will be discussed in class.

Still, how do you find the books that have not been checked out at all?

One needs to use a LEFT JOIN (also know as LEFT OUTER JOIN).

We'll discuss how that is achieved in class.