CS 348 Database Systems - Sample Exam 1

Each question is worth 4 points. You may use the MySQL command line client. You may not use any other materials, files, or notes, etc. Make sure you write non-SQL answers in the correct order in your blue exam booklet.

This exam doesn't have any questions about ER diagrams or Database Design (Ch 7). That is because when this exam was given I had not covered them yet. I moved ER diagrams and database Design topics earlier in the course, so expect the exam to have some ER diagram questions and questions about Database Design (Chapter 7). Review practice problem 7.1 (answer is online at db-book.com.) Review terminology on page 315.

Consider the following relational database that represents, books, publishers, and books published by publishers.

• If a book b is published by publisher p then b is in the books relation, p is in the publishers relation, and a row is added to the publishes relation.
• The date in the books relation represents the year when the book was originally issued.
• The date in the publishes table represents the copyright date when that particular publisher issued it.
• BNO numbers are unique book identifiers (similar to an ISBN number)
• PNO (publisher numbers) are unique publisher identifiers.

Part 1

1. Does this database schema allow for a book to have more than one publisher? Explain why or why not using an example.
2. Does this database schema allow for a book to be currently out of print (not currently being published)? Explain why or why not using an example.
3. List three super keys of the books relation.
4. List all candidate keys of the books relation.
5. (True/False) Consider the statement: It is possible that a relation might not have a super key. In this case the database designer must add an attribute that gives the relation a super key. Write true or false in the blue exam book and briefly explain your answer.
6. List all of the candidate keys of the publishes relation.
7. List all of the super keys of the publishes relation.
8. What two meanings best describe a null web site value in the publishers table?
9. Is SQL's "order by" clause part of the relational algebra? Explain why or why not.
10. In terms of the JDBC what purpose does the class ResultSet serve? (Don't use the term "result set" in your answer)
11. In terms of the JDBC what purpose does the class ResultSetMetaData serve? (Don't use the term "meta data" in your answer.)
12. Outline the best strategy to handle publishers that have more than one web site.
13. List all of the foreign keys in the database. Be explicit by naming the referencing and referenced relations and the appropriate attributes.
14. What is the relational schema for this database? Use the proper notation for specifying a schema.
15. Are there any attributes in the database that could be considered logically non-atomic? Briefly explain why or why not.
16. Assuming we have the tables above, inserting the tuple (4,83,200,1990) into the publishes table failed. Why did this happen? What database principle was violated?
17. Does the SQL statement

      select title,author from books;

    evaluate to a relation? Briefly explain.
18. Write a relational algebra expression that finds all books currently being published. The resulting relation should have attributes title, author, and publisher.

Part II - SQL Queries

1. Write an SQL expression that lists all titles by Jon Bentley.
2. Write an SQL expression that evaluates to the union of the author and publisher attributes.
3. Write an SQL expression that computes the set difference of the date and copyright attributes, date - copyright.
4. Write an SQL expression that computes the set intersection of the date and copyright attributes.
5. Write an SQL expression that lists each book with its publisher. The resulting table should have attributes title, author, and publisher.
6. Write an SQL statement that lists all books that have the same title but different authors.
7. Write an SQL statement that lists the title and author of the book that has the most number of pages.
8. The SQL statement

     select count(distinct title) from books;

   lists the number of different book titles and evaluates to the scalar 3 in the example tables above. Suppose that putting the distinct keyword in the count expression was not supported, write another SQL statement that does the same thing.
9. Write an SQL statement that lists all of the publishers that are in a city with two or more publishers.
10. Write an SQL statement that lists all of the book titles that were originally issued after every book by Jon Bentley.
11. Write an SQL statement that lists book titles that are currently out of print.