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

Write SQL statements for the following

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)

Use the existing database to do the following:

Adding new data (DML):

a.)Add a new book to the BOOKS table: add your favorite book (add a high rating J).

b)Borrow your favorite book. Use yourself as a patron (added in Lab1). Return your favorite book.

c)List the last transaction (all columns) for your favorite book.

Print the SQL queries for a-c and the result for c.

Adding new columns (DDL):

a)Write an SQL statement to add a new column DOB to the PATRONS table. Print the query.

b)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.

Print the DDL statements for a and b.

Changing data (DML):

a)Add DOB to an existing patron (yourself). Use the TO_DATE function for the DOB.

b)Add another patron with all data (including columns added in 2b).

c)List the patrons (Last_modified, patron_id, last_name, first_name, DOB) whose data were added/modified within last 10 days (use SYSDATE as todays date). Use ISO standard for LAST_MODIFIED including local time. Use only the year for the DOB (YYYY). Sort the results by transaction date (newest transactions first).

Print the SQL queries for a-c and the result for c.