The question is for introduction to database system with sql live.

The assignmemt is due on tomorrow 12p.m.

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 Jast 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, patronidint book idiot transaction.date date, transaction type char(1)) Book Id. Patron id and Transaction id are primary keys. Patron,id and book jd 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 Dseydesolum, 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 seg START WITH 1000 INCREMENT BY 1 Use customer seg to insert data as primary key as: Insert into books values(books. seg.nextual.",".........) 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) 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. 4. 1. Write and SQL query to list the patrons (patrooid, 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. 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" book (the book with the most transactions). 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 e. List number of transactions book wise Q2. Solve the following relational expressions for above relations. a. TT title(Orating-2(Books)) b. books Patrons Transactions C. Tlast_name, first name, city(Patrons) d. Ssum(trandaction type(Transactions)) e. Orating=2/month(transaction date=02(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: 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)