Hello! Help with SQL$,$ please!

We have following database:

CREATE TABLE public.product $($

productid serial$4$ NOT NULL,

"name" varchar$(50)$ NOT NULL,

productnumber varchar$(25)$ NOT NULL,

color varchar$(15)$ NULL,

listprice numeric$(29,3)$ NOT NULL,

"size" varchar$(5)$ NULL,

sizeunitmeasurecode bpchar NULL,

weightunitmeasurecode bpchar NULL,

weight numeric$(8,2)$ NULL,

productline bpchar NULL,

"class" bpchar NULL,

"style" bpchar NULL,

productsubcategoryid int$4$ NULL,

productmodelid int$4$ NULL,

sellstartdate timestamp$(6)$ NOT NULL,

sellenddate timestamp$(6)$ NULL,

discontinueddate timestamp$(6)$ NULL,

CONSTRAINT product$\_$pkey PRIMARY KEY $($productid$)$

$)$;

CREATE INDEX idx$\_$product$\_$color ON public.product USING btree $($color$)$;

CREATE TABLE public.productsubcategory $($

productsubcategoryid serial$4$ NOT NULL,

productcategoryid serial$4$ NOT NULL,

"name" varchar$(54)$ NOT NULL,

CONSTRAINT productsubcategory$\_$pkey PRIMARY KEY $($productsubcategoryid$)$

$)$;

CREATE TABLE public.productcategory $($

productcategoryid serial$4$ NOT NULL,

"name" varchar$(54)$ NOT NULL,

CONSTRAINT productcategory$\_$pkey PRIMARY KEY $($productcategoryid$)$

$)$;

CREATE TABLE plan$\_$status $($

quarterid varchar$(6)$ NOT NULL,

status varchar$(10)$ NOT NULL,

modifieddatetime timestamp NOT NULL DEFAULT CURRENT$\_$TIMESTAMP,

author varchar$(20)$ NOT NULL DEFAULT CURRENT$\_$USER,

country varchar$(5)$ NOT NULL,

CONSTRAINT plan$\_$status$\_$pk PRIMARY KEY $($quarterid$,$ country$)$

$)$;

CREATE TABLE plan$\_$data $($

versionid varchar$(1)$ NOT NULL,

country varchar$(5)$ NOT NULL,

quarterid varchar$(6)$ NOT NULL,

pcid int$4$ NOT NULL,

salesamt numeric$(18,2)$ NULL,

CONSTRAINT planapp$\_$data$\_$pkey PRIMARY KEY $($quarterid$,$ country, pcid, versionid$)$

$)$;

CREATE TABLE country$\_$managers $($

username varchar$(30)$ NOT NULL,

country varchar$(5)$ NOT NULL,

CONSTRAINT country$\_$managers$\_$pk PRIMARY KEY $($username$,$ country$)$

$)$;

CREATE TABLE company $($

id int$4$ NOT NULL GENERATED ALWAYS AS IDENTITY,

cname varchar$(200)$ NOT NULL,

countrycode varchar$(10)$ NULL,

city varchar$(30)$ NULL,

CONSTRAINT d$\_$company$\_$pk PRIMARY KEY $($id$)$

$)$;

CREATE TABLE company$\_$sales $($

cid int$4$ NOT NULL,

salesamt numeric$(18,2)$ NULL,

year int$4$ NULL,

quarter$\_$yr int$4$ NULL,

qr varchar$(6)$ NOT NULL,

categoryid int$4$ NOT NULL,

ccls varchar$(1)$ NULL,

CONSTRAINT company$\_$sales$\_$pk PRIMARY KEY $($qr$,$ cid, categoryid$)$

$)$;

CREATE TABLE company$\_$abc $($

cid int$4$ NOT NULL,

salestotal numeric NULL,

cls varchar$(1)$ NULL,

year int$4$ NOT NULL,

CONSTRAINT company$\_$abc$\_$pk PRIMARY KEY $($cid$,$ year$)$

$)$;

create view v$\_$plan$\_$edit as

select pd$.$country, pd$.$quarterid, pd$.$pcid, pd$.$salesamt, pd$.$versionid

from plan$\_$data pd

where

pd$.$versionid $=\text{'}$P$\text{'}$

and

pd$.$country in $($select country

from country$\_$managers cm

where cm$.$username $=$ current$\_$user$)$

and

pd$.$quarterid in $($select ps$.$quarterid

from plan$\_$status ps

where ps$.$author $=$ current$\_$user and ps$.$status $=\text{'}$L$\text{'})$;

create view v$\_$plan as

select pd$.$country,

pd$.$pcid,

pd$.$quarterid,

pd$.$salesamt

FROM plan$\_$data pd

WHERE pd$.$versionid $=\text{'}$A$\text{'}$

AND $($pd$.$country IN $($SELECT cm$.$country FROM country$\_$managers cm

WHERE cm$.$username $=$ CURRENT$\_$USER$)$

or

pg$\_$has$\_$role$($current$\_$user, 'planadmin', 'member'$))$

AND $($pd$.$quarterid IN $($ SELECT ps$.$quarterid

FROM plan$\_$status ps

WHERE ps$.$status $=\text{'}$A$\text{'}))$;

create role planadmin;

create role planmanager;

Set up permissions for roles as written in the table below:

User role planadmin planmanager

$----------------------------------------$

DB object

$---------$

all tables S S

plan$\_$data SUID SUID

plan$\_$status SUID SU

country$\_$managers SUID S

v$\_$plan$\_$edit S SU

v$\_$plan S S

Legend:

S $$ select

U $$ update

I $$ insert

D $-$ delete

Create three users:

$$ Administrator:

o ivan

$$ Managers:

o sophie

o kirill

Sophie has access to data related to US and CA countries. Kirill works with sales data in FR$,$ GB$,$ DE$,$

AU countries. Put this information in the $$country$\_$managers$$ table, which will associate managers with

certain countries they are responsible for.

Populate the $$country$\_$managers$$ table with the records on two managers $$ sophie and kirill

$$ having sophie responsible for US and CA$,$ and kirill $$ for FR$,$ GB$,$ DE$,$ AU$.$

Add two materialized views $$product$2,$country$2.$ Product$2$ should combine data of product and

its category. Country$2$ view should be filled with unique codes of the countries where the shops are

located $($country codes can be taken from addresses of $$Main Office$$ type$).$

Allow managers and administrators to read from these views.

Fields of $$product$2$ are shown in the table. Each field to be included in the view is described below:

Field Description Rule

$----------------------------------------------------------------------------$

pcid Product category key Load from Productcategory.productcategoryid

productid Product key Load from Product.productid

pcname Category name Load