Create a schema called book, and use the files below to complete the code. Create a script “update2.sql” to update the s

[answerhappygod]

Create a schema called book, and use the files below to
complete the code.
Create a script “update2.sql” to update the schema.
Define the tables edition and review. The script will also modify
the existing table book.
1. Use the ALTER TABLE command to change
the name of the book table to edition. The syntax for this is found
in 5.6.7 of the manual
2. Use DELETE or UPDATE to fix the records
inserted in hw1 in insert_bad.sql to fit the constraints in the
next item
3. Again use the ALTER TABLE command to
update edition:
1. ASIN: You now want to make this a
primary key (keep author, publisher, pubdate, pages: from the old
design)
2. pages : add a CHECK constraint to make
sure this is positive. This can actually be null for items like
audio books
3. format : (add this column) one of
several options, cannot be null
4. language : (add this column) a
string for the primary language of the book, not null
5. price : (add this column) a decimal
(use the MONEY type) that must not be negative, not
null
6. Update existing records to fill in
values where needed for the new design
4. The fields of reviewer are: (none of
these can be null)
1. id: an automatically generated, this is
the primary key. Use the bigserial type to automatically generate
values.
2. name: name of the reviewer
3. country: the name of the country where
the reviewer is from
5. The fields of review are: (none of
these can be null)
1. id: an automatically generated, this is
the primary key. Use the bigserial type to automatically generate
values.
2. comments: text for the body of the
review
3. review_date: date when the review was
posted
4. reviewer_id: foreign key referencing
reviewer. Note that the type here and for the next field is not
serial/bigserial since the database is not going to try to generate
a new id, but should be int/bigint,
5. reviewedid: a foreign key referencing
the edition.
6. rating: an int for how the reviewer
rates this edition
2. Create a script “insert2.sql” to insert
acceptable data. Use INSERT statements to insert records into the
edition, reviewer and review tables
1. Insert enough records so that any
queries will return at least one, but not all records
2. For serial/bigserial fields and default
values, see the 2nd example on PostgreSQL: Documentation: 13:
INSERT, which is for default values, but works for serial/bigserial
fields, too
The sql files you will need to have to do
this
create.sql ex
-- create schema for books to sell (like on
Amazon)
CREATE SCHEMA IF NOT EXISTS GenD;
-- the following line causes all types, tables to be
created in the GenD schema by default
-- you can edit it to put them in your schema by
default
SET search_path TO GenD, public;
-- represents some book info on Amazon.com
CREATE TABLE book (
ASIN char(10),
title varchar,
author varchar,
publisher varchar,
pubdate date,
pages int
);
insert_ok.sql
- Script to insert valid records into book
table
SET search_path TO GenD, public;
-- Looking at amazon.com, it does not list an ASIN for
everything
-- On the other hand, ISBN's are often not used for
audiobooks and ebooks like Kindle books
-- The values with repeating digits below are fake. The
other values are real
insert into book values('0465024750', 'Fluid Concepts
and Creative Analogies: Computer Models of the Fundamental
Mechanisms of Thought',
'Douglas R Hofstadter', 'Basic
Books', '1996-03-22', 528),
('0001112222', 'We Don''t Eat Our
Classmates', 'Ryan T. Higgins',
'Disney-Hyperion', '2018-06-19',
48),
('1112223333', 'The Joy Luck Club',
'Amy Tan', 'Penguin Books', '2006-09-21', 352),
('0062107321', 'The Valley of
Amazement', 'Amy Tan', 'Ecco Books', '2013-11-05',
608),
('0002224444', 'Indivisible', 'Daniel
Aleman', 'Little, Brown', 'May 04 2021',
400);
insert_bad.sql ex
-- Script to demonstrate data errors to eventually check
for in Book database
SET search_path TO GenD, public;
-- duplicate ISBN
INSERT INTO book VALUES('1112223333', 'Duplicate
ASIN',
'For error checking', 'N/A', 'July 01
2021', 100);
-- pages should be positive
INSERT INTO book VALUES('1234567890', 'Zero pages', 'For
error checking', 'N/A', 'July 01 2021', 0);
-- title should not be null
INSERT INTO book VALUES('123456NULL', NULL, 'For error
checking', 'Not a valid publisher', 'July 01 2021',
0.99);
query.sql ex
-- Script to query the books table
SET search_path TO GenD, public;
-- Find all publishers
SELECT publisher from book;
-- Find all titles by Amy Tan, plus their length in
pages
SELECT title, pages
FROM book
WHERE author = 'Amy Tan';
-- Find authors with books published in
2021
SELECT author
FROM book
WHERE extract(year from pubdate) = 2021;