Please provide me complete answer for this with explanation. Show all necessary work.

Write SQL queries for the following scenario:

A library database stores information about books, authors, and borrowings. The tables

are:

$$ Books $($book$\_$id$,$ title, isbn, publication$\_$year$)$

$$ Authors $($author$\_$id$,$ name, nationality$)$

$$ Book$\_$Authors $($book$\_$id$,$ author$\_$id$,$ primary key$)$

$$ Borrowings $($borrowing$\_$id$,$ book$\_$id$,$ borrower$\_$id$,$ borrow$\_$date, return$\_$date$)$

$$ Borrower$($id$,$ name, address$)$

$1.$ List all books published after the year $2000.$

$2.$ Find the total number of books written by each author.

$3.$ Retrieve the names of borrowers who currently have overdue books $($return$\_$date

is null and borrow$\_$date is before today's date$).$

$4.$ Write a query to update the publication year of all books by a specific author

$($identified by author$\_$id$)$ to a new value.

$5.$ Write a query that find the most borrowed books over the last month.

$6.$ Write a query that returns each borrower, with the number of borrowed books,

and the percentage between the number of books she borrowed vs the highest

borrower.

$2.$ Relational Schema Design

A university wants to design a database to manage student information, courses, and

enrollments.

$$ Students have attributes like student$\_$id$,$ name, major, and email.

$$ Courses have attributes like course$\_$id$,$ title, department, and credits.

$$ Enrollments link students to courses, with attributes like enrollment$\_$id$,$

student$\_$id$,$ course$\_$id$,$ and semester.

$1.$ Draw an Entity$-$Relationship Diagram $($ERD$)$ for this scenario, identifying entities,

attributes, relationships, and cardinalities.

$2.$ Convert the ERD into a relational schema with tables, columns, data types, and

primary$/$foreign keys.

$3.$ Normalization $(25$ Marks$)$

Consider the following table storing information about a music store:

CD$\_$ID Title Artist Genre Price Stock

$1$ Dark Side of the Moon Pink Floyd Rock $$19\mathrm{.}9910$

$2$ Wish You Were Here Pink Floyd Rock $$18\mathrm{.}995$

$3$ Hotel California Eagles Rock $$17\mathrm{.}998$

$4$ Thriller Michael Jackson Pop $$22\mathrm{.}9915$

$1.$ Identify any normalization issues with this table. Explain why they are

problematic.

$2.$ Apply normalization techniques $($up to $3$rd Normal Form$)$ to decompose the table

into a set of related tables with minimal redundancy.

Serializability

Consider the following schedule of database transactions:

T$1$: R$1($A$)...$W$1($A$)......$

T$2$: $...$R$2($A$)............$W$2($B$)$

T$3$: $...............$ R$3($A$)$ R$3($B$)$

$1.$ Explain the concept of serializability in database transactions.

$2.$ Is the given schedule serializable? If yes, demonstrate how it is equivalent to a

serial execution order. If not, explain why.

$3.$ Give an example of a non$-$serializable schedule and explain the potential

problems it can cause.