Database Systems – Exam 2
Write your name on this paper and write your answers on the answer sheets provided. You may refer to any SQL document that you have created.

  1. (3 pts each) Briefly define/explain the following terms:
    1. JDBC
    2. SQL injection

  2. (2 pts) Name one way in which defining a field of a table to be a primary key differs from defining the field to be unique.

  3. Suppose a database exists to store information about books and authors and that it has been designed as follows:
    • author=(author_id, firstname, lastname)
    • publisher=(pub_id, name, city)
    • book=(isbn, author_id, title, year, pub_id)
    1. Show the SQL statements necessary to create these tables so that the following integrity rules will be enforced.
      • (2 pts) the author's last name is required, but the first name is optional; publisher name and city are required; title is required but year is not
      • (3 pts) every book must have an valid author specified; if an author is removed from the list then any books associated with them should be removed as well
      • (2 pts) include an SQL statement that will create an entry in the publisher table with an id number of 1 and a name of “Unknown” and a city of “n/a”
      • (2 pts) if a book's publisher is not known, the publisher id number should default to the value 1
      • (4 pts) if a publisher is deleted from the list, any books so assigned should revert to “Unknown”; if a publisher's id number changes then then entries in the book table should be changed to match automatically
      • (2 pts) require that each publisher's name be unique
      • (4 pts) when a new book is being entered make sure that the year is not in the future. HINT: The expression date_part('year',current_date) will return the current year (in four digit format). If the year is from the future then deny entry.
      • (3 pts) specify a primary key for each table

    2. (4 pts) Write a series of SQL statements that will insert a new book in the database. Make these assumptions:
      • The book is entitled “Super Fun” and is authored by “Mary Jones” (you can make up the ISBN and year).
      • “Mary Jones” does not yet exist in the author table.
      • The author table type for the author_id field is SERIAL.
      • There are no other authors in the database who have the name “Mary Jones”.
      • The book is published by an existing publisher whose id is 13.

  4. Answer the following questions about transactions and locking.
    1. (3 pts) Give an example of a transaction.
    2. (4 pts) Expand the acronym ACID.
    3. (3 pts) How do failed transactions and aborted transactions differ?

  5. Suppose the following tables represent statistics for baseball players for various years.

    player
    id lastname firstname
    19 Smith Fred
    20 Jones Sam
    21 Gonzalez Eric
    22 Yakamoto Tom
    23 Ruth Babe
    24 Matthis Alan
    stats  
    id year rbi battingavg
    19 2012 57 0.312
    19 2013 60 0.320
    19 2014 47 0.297
    20 2012 null null
    20 2013 20 0.250
    20 2014 22 0.270
    21 2012 null null
    21 2013 null null
    1. (2 pts each) What would be displayed by each of the following queries:
      1. SELECT SUM(rbi) FROM stats WHERE id=20
      2. SELECT COUNT(rbi) FROM stats WHERE id=20

    2. (3 pts each) Construct a query to accomplish each task below. Do not assume that the data listed in the tables above is complete.
      1. For each year display the number of null values that exist for batting average.
      2. For each player give the average of their available batting averages (whether or not they appear in the stats table).
      3. Delete all rows in the stats table having a null rbi value.

  6. Refer to the tables from the previous problem which store baseball statistics. Then answer these questions about JDBC. Assume that the tables in question are in a PostgreSQL schema named baseballdb.
    1. (2 pts) Consider this section of JDBC code. Suppose the variables rbi and player_id are entered by a user. Do we need to be concerned about SQL injection attack in this situation? Explain. 
      Statement stmt;
int rbi,player_id;
...
stmt= con.createStatement();
stmt.executeUpdate("UPDATE baseballdb.stats SET rbi=" + rbi +
	" WHERE player_id=" + player_id + " AND year=2018");
    2. (4 pts) Rewrite the above section of code so that it uses a PreparedStatement instead of a Statement to perform the specified update.
    3. Suppose the database is being updated once per week in a 1 hour window by various teams. Further suppose that the responsibility of updating the stats falls to the home team. Since it is common for teams to play multiple games in a week there may be situations where two separate teams are updating the rbi stats for the same player at the same time.

      Write a complete Java method called addToRbis that accepts as parameters (2 pts):

      • a Connection object that has already been initialized and has established an active connection with the database,
      • a player_id whose RBI stats are to be updated, and
      • the number of RBIs earned by that player during the current week.

      Your function should perform the following actions:

      • (3 pts) Retrieve the current number of RBIs for the specified player in the current year. (See problem #3a for a command to retrieve the current year.)
      • (3 pts) If no entry exists in the database for that user then create one with the given RBI value (you can ignore batting average).
      • (3 pts) If the entry does exist then add the existing RBI value to the value given as a parameter and update the entry accordingly.

      Your solution should be written in such a way that no two updates, even if they arrive at the same time, will result in an incorrect state. (4 pts)

Database Systems

Course Resources

Grades, Etc.

Quick Links