Part 1 Maintaining and redesigning an existing database 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 in, 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: (4 marks) 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: (3 marks) a. Write an SOL statement to add a new column DOB to the PATRONS table. Print the query and the results. b. Write an SQL statement to add LAST_MODIFIED and MODIFIED BY columns to the PATRONS table. The LAST MODIFIED column will have the ICICD PY 2. Adding new columns: (3 marks) a. Write an SQL statement to add a new column DOB to the PATRONS table. Print the query and the results. 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. 3. Add patrons (3 marks) a. Add 2 patrons with their DOB, LAST_MODIFIED, and MODIFIED_BY. b. 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. Part 2 SQL queries (35 marks) Use the modified database from PART 1. 1. Write and SQL query to list the patrons (patron_id, last_name, first_name, DOB) whose data were added/modified today (use SYSDATE as today's 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. 2. The query results should have at least the two new patrons added in (a). 3. 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. 4. 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. 5. 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 author's last name (mixed case) and the title. Sort the results by the rating. 6. Write a join command to display all the patrons with at least one book issued to them. 7. Write SQL to list all the books (book ids and first 10 characters of the title) and their total number of transactions in 2020. Include all books even if they have no transactions (count should be 0). Sort the results by the most popular hook the Part 3 Relational Algebra (10 marks) Use the modified database from PART 1. Q1. Write relational expressions for a. List all the reference books details b. List last_name and first_name of all patrons having first_name starting with M c. Display complete details of the patrons in Kamloops d. List all the transactions with the corresponding patron names and book names c. List number of transactions book wise Q2. Solve the following relational expressions for above relations. a. TT...( Ong-Books) b. books Patrons Transactions c. Tot name, frut.name. ,Patrons) d. Ssum(trandaction_type(Transactions)) c. Ongrihmonthletecton_dmoz (Books)) Part 4 Research (1 %) - 5 marks 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: 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). - 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) Submission of the Assignment Submit the assignment with screen shots of the results generated from query. Indicate the Part and Problem number for your answers. Your document should have a title page with your name, student number, course title, and date. YOU DO NOT NEED TO SUBMIT SQL FILE SEPERATELY