these are database managements past Questions: from last year ineed help trying to solve $,$ my professor gave it to us to go try but i have issues with the er diagram part and normilisation Use the relational schema given below to answer the following questions. $(35\%)$

BOOK

PUBLISHER

AUTHOR

AUTHOR$\_$BOOK

$\begin{array}{cc}{A}_{\text{I}}D& \text{I}\text{S}\text{B}\text{N}\end{array}$

REVIEW

a$.$ Write a SQL query to return the number of books published in $2022$ along with the publisher's

name and total price of the published books.

b$.$ Suppose that book review ratings are linearly scaled from $1$ to $5.$ According to this, list the names

of all authors along with their book titles who received a $5-$point rating for the published books in

$2022.$

c$.$ Find the author$($s$)$ of the most expensive book in the database. List the author$($s$)$ ID along with

the name.

d$.$ Find the publisher and category of the books that received $1$ rating in $2022.$

Assume that we wish to construct a database from a set of data items $\{A,B,C,D,E,F,G\}$ and a set $F$ of

functional dependencies given by F$.$ Start with Relation R$,$ containing all attributes, and perform a

lossless decomposition into two relations, $R_1$ and $R_2.$ List carefully the keys for each relation $(R,R1,$ and

$R2).$ Finally, show whether $R_1$ and $R_2$ are in $BCNF$ or not. $(30\%)$

$F=(1)BCDA,,(2)BCE,(3)AF,(4)FG,(5)CD,(6)AG$

Alice has a large DVD movie collection. Her friends like to borrow her DVD$\text{'}$s$,$ and she needs a way to

keep track of who has what. She maintains a list of friends, identified by unique FID's $($friend identifiers$)$

and a list of DVD$\text{'}$s$,$ identified by DVDID's $($DVD identifiers$).$ With each friend, there are the name and

all$-$important telephone numbers that she can call to get the DVD back. Each DVD is kept with the star

actor's name and title. Whenever a friend borrows a DVD$,$ Alice will enter that fact into her database

along with the date borrowed. Whenever the DVD is returned, that fact, too, gets noted along with the

date retumed. Alice wants to keep a complete history of her friends' borrowing habits so that she can ask

favours of the heavy borrowers $($or perhaps refuse to make further loans to those who habitually don't

return them quickly$).$

Draw an ER diagram according to the given description above. Be certain to indicate identifiers a

cardinality constraints. $(20\%)$

Production tracking is important in many manufacturing environments $($e$.$g$.,$ the pharmaceutica

industry, children's toys, etc.$).$ The following ER diagram captures important information in the trackin

of production. Specifically, the ER diagram captures relationships between production lots $($or batches$),$

individual production units, and raw materials. $(15\%)$

a$.$ Please convert the ER diagram into a relational database schema. Be certain to indicate primary keys

and referential integrity constraints.

b$.$ The current ER diagram has the following relationship, "raw materials are used in $0$ to many lots."

Please explain, in the context of the manufacturing environment, how the meaning changed if the

minimal cardinality is changed to $"1"($i$.$e$.,$ the relationship becomes "raw materials are used in $1$ to

many lots."$)$