Context: You are working as a database designer for a company that wants to revise its library management system. The current database contains a Books table which records information about the books, the authors, and the borrowers. This table suffers from several design problems related to data redundancy and inefficient information management.

Existing data in the "Books" table:

BookID

Title

Authors $($several authors can be listed in the same string$)$YearOfPublication

BorrowerID

BorrowerName

BorrowDate

ReturnDate

Identified problems:

$1.$The Books table contains information about the books, the authors, and the borrowings, which leads to redundancies and update anomalies.

$2.$Authors are stored in a single string, which makes it difficult to search by a specific author.

$3.$A book can be borrowed multiple times, but the history of borrowings is not clearly managed.

Instructions:

First Normal Form $(1$NF$)$: Identify and correct the violations of the $1$NF in the Books table. Explain your approach to separate the data so that each field contains atomic and unique values.

Second Normal Form $(2$NF$)$: After applying the $1$NF$,$ identify the partial dependencies and restructure the database to ensure each table respects the $2$NF$.$ Explain how you have eliminated the partial dependencies.

Third Normal Form $(3$NF$)$: Verify that your design after the $2$NF respects the $3$NF by eliminating transitive dependencies. Describe the changes made to achieve the $3$NF$.$

Boyce$-$Codd Normal Form $($BCNF$)$: Ensure that your design respects the BCNF$,$ where each determinant is a candidate key. If necessary, adjust your schema to correct any BCNF violations.