Homework #1

Notes (please read carefully):

  1. All the queries have to be formulated with respect to the bibliography database introduced in class; here is the schema again: 
      author(AID INTEGER, NAME CHAR(22), URL CHAR(42))
  wrote(AUTHOR INTEGER, PUBLICATION CHAR(8))
  publication(PUBID CHAR(8), TITLE CHAR(70))
  book(PUBID CHAR(8), PUBLISHER CHAR(50), YEAR INTEGER)
  journal(PUBID CHAR(8), VOLUME INTEGER, NO INTEGER, YEAR INTEGER)
  proceedings(PUBID CHAR(8), YEAR INTEGER)
  article(PUBID CHAR(8), CROSSREF CHAR(8), STARTPAGE INTEGER,
                                                ENDPAGE INTEGER)
  2. Write queries Q1-6 in relational calculus.
  3. Write additional integrity constraints that is reasonable to expect to hold in the above schema.
  4. Explain how the integrity constraints affect the queries.

Queries:

  • Query 1:
    List all articles that haven't appeared in a book.
    Answer: publication id and title.
  • Query 2:
    List all books that do not contain any articles.
    Answer: publication id and title.
  • Query 3:
    List all articles and the year in which they appeared.
    Answer: pubid, title, and year.
  • Query 4:
    List all articles that have appeared in conference proceedings and later in a journal.
    Answer: article title, proceedings title, journal name.
  • Query 5:
    List all authors whose publications are solely articles.
    Answer: author id and name.
  • Query 6:
    List all pairs of authors who always publish together.
    Answer: author id and name for both authors.