*Based on the require database and solutions answer all other questions :

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

"Please go to this link in chegg to review part 1 work by other expert and then answer rest of them."

https://www.chegg.com/homework-help/questions-and-answers/create-following-schema-maintaining-books-issued-patrons-books-bookid-int-title-varchar-20-q68244199

Write relational expressions for :

1. List all the reference books details and List last_name and first_name of all patrons having first_name starting with M
2. Display complete details of the patrons in Kamloops and list all the transactions with the corresponding patron names and book names
3. List number of transactions book wise.

Please answer 3 of them.

create table books create table books ( book_id NUMBER(5) primary key, title varchar(20), author_last_name varchar(20), author_first_name varchar(20), rating char(1)); -- Create table patrons create table patrons (patron_id NUMBER(5) primary key, last_name varchar(20), first_name varchar(20), street_address varchar(30), city varchar(10), zip varchar(7)); -- create table transactions create table transactions (transaction_id NUMBER(5) primary key, patron_id number(5) CONSTRAINT FK_PATRON_ID references patrons(patron_id), book_id number(5) CONSTRAINT FK_BOOK_ID references books(book_id), transaction_date date, transaction_type char(1)); -- describe books table desc books; -- describe patrons table desc patrons; -- describe transactions table desc transactions; -- Create sequence unique_seq_val CREATE SEQUENCE unique_seq_val START WITH 1000 INCREMENT BY 1; -- alter table patrons and add column dob ALTER TABLE patrons ADD dob DATE; -- view updated table structure desc patrons; -- alter table patrons and add column last_modified ALTER TABLE patrons ADD last_modified TIMESTAMP; -- alter table patrons and add column modified_by ALTER TABLE patrons ADD modified_by varchar(20); -- view updated table structure of patrons table desc patrons; -- insert two rows in patrons table insert into patrons values(unique_seq_val.NEXTVAL, 'Dew, Uhon, 'XYZ', 'Chicago, 411045) TO_DATE('2003/07/09', 'yyyy/mm/dd"), SYSDATE, 'SXK11"); insert into patrons values(unique_seq_val.NEXTVAL, 'Bush, 'George', 'abc, 'Chicago', '335524, TO_DATE('2001/03/25'yyyy/mm/dd'), SYSDATE, 'KK360"); -- view data in patrons table select * from patrons; Per te potros create table books create table books ( book_id NUMBER(5) primary key, title varchar(20), author_last_name varchar(20), author_first_name varchar(20), rating char(1)); -- Create table patrons create table patrons (patron_id NUMBER(5) primary key, last_name varchar(20), first_name varchar(20), street_address varchar(30), city varchar(10), zip varchar(7)); -- create table transactions create table transactions (transaction_id NUMBER(5) primary key, patron_id number(5) CONSTRAINT FK_PATRON_ID references patrons(patron_id), book_id number(5) CONSTRAINT FK_BOOK_ID references books(book_id), transaction_date date, transaction_type char(1)); -- describe books table desc books; -- describe patrons table desc patrons; -- describe transactions table desc transactions; -- Create sequence unique_seq_val CREATE SEQUENCE unique_seq_val START WITH 1000 INCREMENT BY 1; -- alter table patrons and add column dob ALTER TABLE patrons ADD dob DATE; -- view updated table structure desc patrons; -- alter table patrons and add column last_modified ALTER TABLE patrons ADD last_modified TIMESTAMP; -- alter table patrons and add column modified_by ALTER TABLE patrons ADD modified_by varchar(20); -- view updated table structure of patrons table desc patrons; -- insert two rows in patrons table insert into patrons values(unique_seq_val.NEXTVAL, 'Dew, Uhon, 'XYZ', 'Chicago, 411045) TO_DATE('2003/07/09', 'yyyy/mm/dd"), SYSDATE, 'SXK11"); insert into patrons values(unique_seq_val.NEXTVAL, 'Bush, 'George', 'abc, 'Chicago', '335524, TO_DATE('2001/03/25'yyyy/mm/dd'), SYSDATE, 'KK360"); -- view data in patrons table select * from patrons; Per te potros