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

books ( book_id int, title varchar(20), author_last_name varchar(20), author_first_name varchar(20), rating char(1))

patrons (patron_id int, last_name varchar(20), first_name varchar(20), street_address varchar(30), city varchar(10), zip char(7))

transactions (transaction_id int, patron_id int, book_id int, transaction_date date, transaction_type char(1))

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_type are 1 = checking out , 2 = returning, 3 = placing a hold.

Possible values for the rating are 1 = text book, 2 = reference book, 3 = others

Use the existing database to do the following:

1. Adding values in the primary key:

CREATE SEQUENCE is a SQL statement to create a sequence, which can be used to generate unique integers. The CURRVAL pseudocolumn, returns the current value of the sequence, and the NEXTVAL pseudocolumn, increments the sequence and returns the new value. You can use sequences to automatically generate primary key values.

CREATE SEQUENCE customers_seq

START WITH 1000

INCREMENT BY 1

Use customer_seq to insert data as primary key as:

Insert into books values(books_seq.nextval, , , )

2. 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.
3. Add patrons
   1. Add 2 patrons with their DOB, LAST_MODIFIED, and MODIFIED_BY.
   2. 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.