CS 348 Database Systems - Sample Exam 1 Solutions

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.

   Yes, notice book 2 appears twice in the publishes relation.

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.

   Yes, in this case the book would not appear anywhere in the publishes relation.

3. List three super keys of the books relation.

   {BNO, Title}, {BNO, Author}, {BNO, Date} are all superkeys, in fact there are many more. Any subset of attributes that contains BNO is a superkey.

4. List all candidate keys of the books relation.

   There is one candidate key and its {BNO}.

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.

   False, since a relation is a set and sets do not contain duplicate tuples then by definition every relation has a superkey that consists of all of the attributes in the relation.

6. List all of the candidate keys of the publishes relation.

   There is one, {PNO, BNO}.

7. List all of the superkeys of the publishes relation.

   There are four: {PNO, BNO},{PNO, BNO, Pages}, {PNO, BNO, Copyright}, {PNO, BNO, Pages, Copyright}.

8. What two meanings best describe a null web site value in the publishers table?

   Either the publisher does not have a web site, or we don't know what the web site is.

9. Is SQL's "order by" clause part of the relational algebra? Explain why or why not.

   We haven't covered relational algebra in this class. But anyway, the answer is no, by definition a relation is a set and a set is unordered, so the order by clause has no place in set operations on relations (relational algebra).

10. In terms of the JDBC what purpose does the class ResultSet serve? (Don't use the term "result set" in your answer)

    ResultSet is the JDBC class that represents the output of an SQL query. The class contains functions for iterating through the rows and also getting the values of individual attritbutes in each row.

11. In terms of the JDBC what purpose does the class ResultSetMetaData serve? (Don't use the term "meta data" in your answer.)

    ResultSetMetaData is the JDBC class that represents information about a ResultSet object. It contains data about the data including the names of the attributes and the number of attributes in each row, etc.

12. Outline the best strategy to handle publishers that have more than one web site.

    This would be an example of a multi-valued attribute. We should create another table of website wherethe URL of the web site is the primary key and the publisher number is a foreign key referring to the publisher number in the publishers relation.

13. List all of the foreign keys in the database. Be explicit by naming the referencing and referenced relations and the appropriate attributes.
    1. PNO in the publishes relation is a foreign key that references the PNO in the publishers relation.
    2. BNO in the publishes relation is a foreign key that references the BNO in the books relation.
14. What is the relational schema for this database? Use the proper notation for specifying a schema.
    • books = (BNO, Title, Author, Date, Edition)
    • publishers = (PNO, Publisher, City, Website)
    • publishes = (PNO, BNO, Pages, Copyright)
15. Are there any attributes in the database that could be considered logically non-atomic? Briefly explain why or why not.

    The author of a book might best be separated into first and last names.

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?

    It failed becuase there is no book 83. This would have violated the principle of referential integrity.

17. Does the SQL statement

      select title,author from books;

    evaluate to a relation? Briefly explain.

    The result wold have a duplicate tuple (Programming Pearls, Jon Bentley) and a relation is a set and sets do not have duplicaes so it it does not evaluate to a relation.

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

    Don't bother, we didn't cover relational algebra in any detail.

Part II - SQL Queries

1. Write an SQL expression that lists all titles by Jon Bentley.

       select title from book where autho='Jon Bentley';
     

2. Write an SQL expression that evaluates to the union of the author and publisher attributes.

   select author from books union select publisher from publishers;
     

3. Write an SQL expression that computes the set difference of the date and copyright attributes, date - copyright.

   select distinct date from books where date not in 
   (select copyright from publishes)
     

4. Write an SQL expression that computes the set intersection of the date and copyright attributes.

   
   select distinct date from books where date in 
   (select copyright from publishes)
     

5. Write an SQL expression that lists each book with its publisher. The resulting table should have attributes title, author, and publisher.

   select title, author, publisher from 
   books inner join publishers
         inner join publishes using (bno,pno) 
   
     

6. Write an SQL statement that lists all books that have the same title but different authors.

   select * from books as b1,
                 books as b2
       where b1.title=b2.title and
       b1.author != b2.author
     

7. Write an SQL statement that lists the title and author of the book that has the most number of pages.

   select title,author from 
   books inner join publishes using (bno)
   where pages = (select max(pages) from publishes);
     

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.

   select count(*) from 
    (select distinct title from books) as temp; 
     

9. Write an SQL statement that lists all of the publishers that are in a city with two or more publishers.

   select publisher from publishers
   where city 
     in (select city from publishers 
         group by city having count(city) >= 2);
     

10. Write an SQL statement that lists all of the book titles that were originally issued after every book by Jon Bentley.

    select * from books where
    date > all (select date from books 
                where author = "Jon Bentley")
      

11. Write an SQL statement that lists book titles that are currently out of print.

    select title from books 
    where bno not in
    	(select distinct bno from  publishes)