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

We start from the entities of yesterday:

• BOOKS
• PATRONS

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.

But BORROW and RETURN need also be linked.

So we'd have 5 tables.

We will simplify this design by introducing a TRANSACTIONS table.

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

• Friedman
• Knuth
• Salomaa
• Gosling
• Ullman
• Barendregt
• Conway

• Larry Bird
• Michael Jordan
• Tony Kukoc

We said we will introduce a TRANSACTIONS table.

Here is a list of all the transactions.

1. Bird checks out Friedman on 9/10, returns it on 9/25, rating is 3.8

2. Bird checks out Salomaa on 9/10, returns it on 9/16, rating is 3.5

3. Jordan checks out Barendregt on 9/14, returns it on 9/20, rating is 4.0

4. Jordan checks out Conway on 9/14, returns it on 9/16, rating is 3.0

5. Jordan checks out Gosling on 9/14, and he still has it with him

6. Bird checks out Knuth on 9/16, returns it on 10/9, rating is 3.95

7. Kukoc checks out Conway on 9/16, returns it on 9/17, rating is 2.9

8. Bird takes out Conway on 9/17 returns it on 10/5, rating is 3.5

9. Jordan takes out Conway on 10/6, returns it on 10/8, rating 3.2

10. Kukoc takes out Barendregt on 9/22 and still has it

11. Kukoc takes out Friedman on 9/29 and still has it

We create a TRANSACTION table with this data.

We then look at the queries.

Here's one of the queries.

SELECT STUDENTS.Name, *
FROM BOOKS, TRANSACTION, STUDENTS
WHERE BOOKS.ISBN = TRANSACTION.ISBN

  AND STUDENTS.Username = TRANSACTION.Username

  AND DateIn IS NULL

  AND (DateOut + 14 < #10/10/2001#)

ORDER BY STUDENTS.Name DESC; 

Remember everything starts from the cartesian product of the sources.

Here's another query.

SELECT ISBN, Avg(Rating) AS Rank
FROM TRANSACTION
GROUP BY TRANSACTION.ISBN
ORDER BY Avg(Rating) DESC;

SELECT Name, COUNT(*)
FROM STUDENTS, TRANSACTION
WHERE Username = Student;

List all good books (average ranking greater than 3.3) that are overdue.

Last query simply checks for the occurrence of a particular string in the list of topics.

Then we ask this question: what books are not checked out?