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.
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.
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.
SELECT FirstName, LastName, Address FROM Readers, BookLoans, Books WHERE Readers.LibCard = BookLoan.LibCard 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 ) FROM Readers, BookLoans, Books WHERE Readers.LibCard = BookLoan.LibCard 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
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.
Those who pointed out that CREATE statement for Bob_table had an error in it (the type of Data2 has not been specified), and therefore none of the further queries could go through, also got full credit. :-)
If you have any questions about the solutions, please let me know!