Problem set 2

Due Tuesday, February 26 at 1:30pm in class. Total points 70.
This is a paper-and-pencil assignment, no programming is required.

Problem 1 (40 points)

Part 1

Draw out the schema of a database that describes a library. The database should contain the following information: Please show all the tables of the database, the names and the types of all columns, which column(s) form a primary key in which table, and what are foreign keys in each table. You may add other fields (in addition to those specified) if you find it necessary. Please list all the necessary assumptions (for instance, "we assume that all library card numbers are distinct").

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

Part 2

Using the database constructed in part 1, please write the following SQL queries:
  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)

The Historic Society of the Town of Smallville has created a database of all known residents of Smallville since the establishment of the town in 1750. The database has a single table Resident which describes town residents. The fragment of the table given below shows all the columns, but only some of the rows. The IDs are assigned based on the order in which records are found, not in chronological order.

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.

Please answer the following three questions about the database:
  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.

For each of the following requests please write SQL query to obtain the information from the database, or explain why such a query is not possible.

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)

3 users of a database, none of whom is a database manager, issue the following SQL commands in this order:

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.


This page has been created and is maintained by Elena Machkasova
Comments and suggestions are welcome at emachkas@wellesley.edu

Spring Semester 2002