Selected solutions for problem set 2

Problem 1 (the library database)

Part 1 mostly did not cause problems. I'd like to mention just a couple of things:

Primary/foreign keys, relations

Book: a combination of author and title may serve as a primary key under the assumption that there is just one copy of a book with a particular author and a title. Since this assumption is not very realistic, it's better to have a separate ID number for each book. Then the ID is the primary key.

Reader: library card number is a foreign key (under the assumption that all cards numbers are distinct).

Book loan: a combination of a book ID and the reader's library card works as a primary key under the assumption that a reader cannot borrow a book more than once (it can still be renewed once it's borrowed). A better combination is book ID + library card + date borrowed. A separate ID for book loans is also a good option for a primary key.

Foreign keys in Book_loan table: book ID (or author+title, if that's the primary key in Book), library card.

Relations:
Book to book_loan: one-to-many (one book can be borrowed multiple times, but each book loan record refers to just one book),
Reader to book loan: also one-to-many.

Queries

All queries from 4 on:

4. Find out the names of all readers whose books are overdue:

``````
SELECT FirstName, LastName FROM Readers, BookLoans
WHERE Readers.LibCard = BookLoan.LibCard AND Overdue = 'Y'
``````

Assuming that the fields FirstName and LastName appear only in the table Readers. You could write SELECT DISTINCT ... to display information for each reader only once.

5.Find out how many readers have overdue books.

``````
SELECT DISTINCT COUNT(*) FROM Readers, BookLoans
WHERE Readers.LibCard = BookLoan.LibCard AND Overdue = 'Y'
``````

6.List (in alphabetical order) the authors of all books that have been borrowed in 2002.

``````
SELECT AuthorFirstName, AuthorLastName FROM Books, BookLoans
WHERE DateBorrowed > '2001-12-31' ORDER By AuthorLastName
``````

7.Find out the names and the addresses of all readers who are currently holding books by Hemingway.

``````
AND Book.ID = BookLoan.BookID AND AuthorLastName = 'Hemingway'
``````

Alternatively you could write "AND Author LIKE '%Hemingway%'", assuming that there is no separate field for author's last name and first name. I wasn't taking points off if you wrote "AND Author = 'Hemingway'".

8.Find out the average age of all readers who have borrowed books by J.R.R. Tolkien in years 2001 and 2002. The age should be computed on the day when the book has been borrowed. If a reader has borrowed several books by Tolkien, the reader should be counted several times.

``````
SELECT AVG( (Date_borrowed -  Date_of_birth) YEAR )
AND Book.ID = BookLoan.BookID AND Author LIKE '%J.R.R. Tolkien%'
AND ( (Date_borrowed) YEAR IN (2001, 2002) )
``````

9. Add the following information to the database: Jane Brown has borrowed the book "Moby Dick" by Herman Melville on Jan. 20, 2002 for 2 weeks.

One of many ways of doing it. Here 1111 is the record ID, we are assuming that there is only one Jane Brown and only one copy of "Moby Dick", we are inserting NULL for date_returned, and 'N' for overdue.

``````
INSERT INTO BookLoans
VALUES (1111,
(SELECT LibCard FROM Readers WHERE FirstName = 'Jane' AND
LastName = 'Brown'),
(SELECT ID FROM Books WHERE AutorLastName = 'Melville' AND
AuthorFirstName = 'Herman' and Title = 'Moby Dick'),
'2002-1-20', '2002-2-3', NULL, 'N');
``````

10. Update the information in the prevous record to indicate that Jane Brown has renewed the book for an extra two weeks.

``````
UPDATE BookLoans
SET Date_due = Date_due + INTERVAL '14' DAY
WHERE ID = 1111
``````

Since we have assigned a number to this record in the previous query, we might as well use it in this one.

11.Mark all book loans whose due date is before Feb. 18, 2002, and which have not been returned yet, as overdue.

We assume (as above) that Date_returned is NULL until the book is returned. Note that in this approach you allow Date_returned to be null, i.e. you shouldn't impose a non-null constraint on it.

``````
UPDATE BookLoans
SET Overdue = 'Y'
WHERE Due_date < '2002-2-18' AND Date_returned = NULL
``````

12.Delete all the information about loans before 1995, except for those for which the books haven't been returned yet.

``````
DELETE FROM BookLoans
WHERE Date_borrowed (YEAR) < 1995 AND Date_returned IS NOT NULL
``````

Problem 2 (the Smallville database)

The table Resident is now defined in the McKoi database (the one with the username and password "cs349"). Please feel free to experiment with it! The queries below have been tested in the database.

Part 1

1. Primary key is ID.
2. Relations: mother to child (one-to-many, represented by field Mother which uses ID as the foreign key), father to child (similar).
3. Mother, Father cannot be declared as non-null, since there are residents whose parents were not residents of Smallville, and therefore are not included in the database.

Part 2

A typo in the table: John Andrew Smith should be John Edward Smith. The answers below are taking this correction into account. Alternatively you could get John Edward's ID by running a select query: ```SELECT ID FROM Resident WHERE FirstName = 'John Edward' AND LastName = 'Smith'``` or just make an assumption about his ID. Either way it wasn't considered a mistake.

1.Find all daughters of Mary Jane Smith and John Edward Smith.

``````
SELECT * FROM Resident WHERE Mother = 123 AND Father = 124 AND Sex =
'F';
``````

2. Find all brothers of Joan Rose McDonald. You may look only for full brothers, i.e. those who have the same mother and father.

``````
SELECT * FROM Resident WHERE (
Mother = (SELECT Mother FROM Resident WHERE First_Name = 'Joan Rose'
AND Last_Name = 'McDonald')
AND
Father = (SELECT Father FROM Resident WHERE First_Name = 'Joan Rose'
AND Last_Name = 'McDonald')
AND
Sex = 'M');
``````

Or you could take the query from part 1 and change the last part to "AND Sex = 'M'".

3.Find all daughters-in-law of Mary Jane Smith and John Edward Smith.

This is impossible: no information about marriage is contained in the database. You can get an estimate by finding mothers of children of Mary Jane's sons, but what if there were no children in the marriage?

4.Find all first cousins of Mary Rose McDonald.

First cousins share a set of grandparents, but they don't have the same parents! The following query works under the assumption that both sets of Mary Rose's grandparents were residents of Smallville. Otherwise some of the grandparent's fields will come out as NULL, and all those whose grandparent's fields are NULL will "share" the grandparents with Mary Rose.

``````
SELECT * FROM Resident WHERE
(Mother IN (
SELECT ID FROM Resident WHERE (Mother = 123 AND Father = 124)
OR (Mother = 116 AND Father = 105))
OR
Father IN (
SELECT ID From Resident WHERE (Mother = 123 AND Father = 124)
OR (Mother = 116 AND Father = 105)))
AND Mother <> 126 AND Father <> 127
;
``````

5.Find out how many grandchildren of Mary Jane Smith have the last name Smith.

``````
SELECT COUNT(*) FROM Resident WHERE
Last_Name = 'Smith'
AND
(Mother IN (SELECT ID FROM Resident WHERE Mother = 123)
OR
Father IN (SELECT ID FROM Resident WHERE Mother = 123)
);
``````

6. Find all descendants of Mary Jane Smith and John Edward Smith.

Given what we have learned about SQL, it's impossible: we need to know the number of generations represented by the database to be able to write this query.