Part 1 Maintaining and redesigning an existing database (2 %)

Create the following schema for maintaining the books issued to the patrons.

books ( book_id, title, author_last_name, author_first_name, rating)

patrons (patron_id, last_name, first_name, street_address, city_state_zip, location)

transactions (transaction_id, patron_id, book_id, transaction_date, transaction_type)

Book_Id, Patron_id and Transaction_id are primary keys. Patron_id and book_id in transactions table are foreign keys.

Possible values for the transaction code are 1 = checking out , 2 = returning 3 = placing a hold.

Use the existing database to do the following:

1. Adding new columns:
   1. Write an SQL statement to add a new column DOB to the PATRONS table. Print the query and the results.
   2. Write an SQL statement to add LAST_MODIFIED and MODIFIED_BY columns to the PATRONS table. The LAST_MODIFIED column will have the server date and time and the MODIFIED_BY will have the USER name.
2. Add two patrons with their DOB, LAST_MODIFIED, and MODIFIED_BY.
   1. Write an SQL statement to add two new patrons to the table. Use the TO_DATE function for their DOBs. Use the existing sequence for the patron id. Print the query and the results.
   2. Write and SQL query to list the patrons (patron_id, last_name, first_name, DOB) whose data were added/modified today (use SYSDATE as todays date). The first name and last name should have the following format: Upper case Initial, period, comma, and Last name in a mixed case. Use the ISO standard for DOB (YYYY-MM-DD). Sort the results by last name. Print the query and the results.

The query results should have at least the two new patrons added in (a).

Part 2 SQL queries (1 %)

Use the modified database from PART 1.

1. Write SQL to list all patrons with their ids and age in years (rounded up). Age is calculated based on the server date/time. Sort the results by age in an ascending order.
2. Write SQL to list all patrons and their number of transactions for each transaction type. Include all patrons even if they do not have transactions.
3. Write SQL to list the books with the word database or data base or databases in their title. Make your search case insensitive. Print the authors last name (mixed case) and the title. Sort the results by the rating.
4. Write SQL to list all the books (book ids and first 10 characters of the title) and their total number of transactions in 2013. Include all books even if they have no transactions (count should be 0). Sort the results by the most popular book (the book with the most transactions).

Part 3 Research (1 %)

Using online documentation, find out about data types used in MS Access 2010, Oracle 11g, and MySQL 5.0. Study two data types modeling issues in these three DBMS:

1. Compare and contrast the methods for creating an automatic unique identifiers in each DBMS. Put your answer in a short point/paragraph/table format. List references (at least one source).
2. Compare and contrast the storage of date, time, and time zone. Specify the data types for storing date, time, and time zone in each DBMS. How dates are stored internally in each DBMS? What is the maximum range for dates? Prepare your answer in a table format. List references (at least one source).