Problem set 2

Problem 1 (40 points)

Part 1

• About a book: title, author, publisher, year of publication.
• About a reader: library card number, first name, last name, address, date of birth.
• About a book loan: which book has been borrowed, which reader has borrowed the book, date borrowed, date due (a book can be renewed), date returned, and a flag indicating whether the book is overdue.

Please list all relations represented by the database and what kind of relations they are (one-to-one or one-to-many).

Part 2

1. Create a table that contains information about book loans. Make sure to specify all necessary constraints. You may assume that the other tables have already been created.

   From this point on assume that all tables are created and populated with data.

2. Add the column "date acquired" to the table describing a book.
3. Add the following information to the database: John Green, born on June 15, 1973, who lives at 5 Cherry Lane, Smallville, got a membership at the library. His card number is 333444.
4. Find out the names of all readers whose books are overdue.
5. Find out how many readers have overdue books (hint: a reader who holds more than one book should be counted only once).
6. List (in alphabetical order) the authors of all books that have been borrowed in 2002.
7. Find out the names and the addresses of all readers who are currently holding books by 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.
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.
10. Update the information in the prevous record to indicate that Jane Brown has renewed the book for an extra two weeks.
11. Mark all book loans whose due date is before Feb. 18, 2002, and which have not been returned yet, as overdue.
12. Delete all the information about loans before 1995, except for those for which the books haven't been returned yet.

Problem 2 (20 points)

ID	First Name	Last Name	Date of Birth	Sex	Mother	Father
123	Mary Jane	Smith	1766-10-13	F	112	105
124	John Andrew	Smith	1764-05-25	M	107	99
125	Peter John	Smith	1790-04-04	M	123	124
126	Joan Rose	McDonald	1792-08-15	F	123	124
127	Edward John	McDonald	1789-05-17	M	116	104
128	Mary Rose	McDonald	1812-06-22	F	126	127
129	Catherine Jennifer	Smith	1790-07-27	F	87	111
130	Peter John	Smith	1815-12-12	M	129	125

Part 1.

1. What column(s) represent the primary key of the table?
2. What relations are represented in the database, if any? If there are any, what kind are they (one-to-one, one-to-many, many-to-many), what foreign key(s) are used to represent the relation(s), and what examples of the relation(s) are given in the table fragment?
3. Is it reasonable to require the columns Mother and Father to be non-null? Please explain your answer.

Part 2.

Please state all your assumptions clearly.

1. Find all daughters of Mary Jane Smith and John Edward Smith.
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.
3. Find all daughters-in-law of Mary Jane Smith and John Edward Smith.
4. Find all first cousins of Mary Rose McDonald.
5. Find out how many grandchildren of Mary Jane Smith have the last name Smith.
6. Please think carefully about this question. Find all descendants of Mary Jane Smith and John Edward Smith. The query should find all descendants based on the information in the database. Of course, there may always be cases when a family moved out of Smallville, lived elsewhere for many years, and then their grandchildren or other descendants who were not born in Smallville have moved to the town. The database contains only information about Smallville residents, so it is imposible to trace such descendants based on the database information. The query is not expected to find such descendants.

Problem 3 (10 points)

User Anna:

CREATE TABLE Anna_table (

Data1	INTEGER	PRIMARY KEY,
Data2	CHAR(20)

);

GRANT ALL ON Anna_table TO Bob;

User Bob:

INSERT INTO Anna_table VALUES (5, 'My string');

CREATE TABLE Bob_table (

Data1	INTEGER	PRIMARY KEY,
Data2	REFERENCES	Anna_table(Data 1)

);

GRANT SELECT, INSERT ON Bob_table TO Carol;

User Carol:

INSERT INTO Bob_table VALUES (7, 5);

SELECT Anna_table.data2 FROM Bob_table, Anna_table WHERE Bob_table.data2 = Anna_table.data1;

Please indicate which of the above queries will go through.