Part A: Relational Database Design

$1.$ Answer questions below regarding the relational database design for the R University book loan management database.

Book $($Call$\_$No$,$ Title, AuthorID, Author$\_$Bio, Location, Copy$\_$Barcode, Status$)$

Semantics of attributes is self$-$explanatory. Some additional notes are as follows:

$-$A call number is the combination of letters and numbers that indicates where books can be found in the library.

$-$A book may have multiple book copies. Each book copy has a unique copy barcode. All copies of a book share same call number.

$-$A book may have one or more authors and an author can write more than one book. Each author has some description $($Author$\_$Bio$).$

$-$Status presents the status of a book copy e$.$g$.$ available, on loan

Answer following questions:

$1\mathrm{.}1$ List all likely functional dependencies on the above relation Book according to the business rules. If there are no functional dependencies among attributes, you must state so$.$ Do not write redundant or trivial functional dependencies $($e$.$g$.$ Call$\_$No $->$Call$\_$No$).$ Specify all candidate keys for Book relation and explain your answer.

$1\mathrm{.}2$ Please identify the highest normal form of Book relation. Explain your answer.

$1\mathrm{.}3$ Following results in Question $1\mathrm{.}2,$ if Book relation is not in $3$NF$,$ decompose it into relations in $3$NF$.$ Merge relations with a common primary key and remove any redundant subset relations. Check if the merged relations are in $3$NF$,$ and apply further decomposition if needed until all final relations are in $3$NF$.$ Write down the final relational database schema and indicate the primary key $($underlined$)$ and foreign key$($s$)($with an asterisk$*)$ in each relation.

Important:final schema in Question $1\mathrm{.}3$ show the workings of FDs $($Question $1\mathrm{.}1)$ and normal form reasoning $($Questions $1\mathrm{.}2).$

$2.$ Given the following relation for real estate sales:RealEstate $($PropertyID$,$ Address, AgentName, Price, Commission, AgentPhone, CustomerName, CustomerPhone, SalesTax$)$

and the followingFUNCTIONAL DEPENDENCIES: PropertyID $->$Address, Price

AgentName $->$AgentPhone

Price $->$SalesTax

CustomerName $->$CustomerPhone

AgentName, Price $->$Commission

Answer following questions: $2\mathrm{.}1$ Find All Candidate Keys of the RealEstate relation. Explain your answer.

$2\mathrm{.}2$ Please identify the highest normal form of RealEstate relation. Explain your answer.

$2\mathrm{.}3$ Decompose the RealEstate relation into $3$NF relations if it is not in $3$NF already. Merge relations with a common primary key and remove any redundant subset relations. Check if the merged relations are in $3$NF$,$ and apply further decomposition if needed until all final relations are in $3$NF$.$ Write down the final relational database schema and indicate the primary key $($underlined$)$ and foreign key$($s$)($with an asterisk$*)$ in each relation.

Important: SHOW final schema in Question $2\mathrm{.}3$ if you do not show the workings of FDs $($Question $2\mathrm{.}1)$ and normal form reasoning $($Questions $2\mathrm{.}2).$