Consider the following relational database schema: $$ Author$($aID$,$ aName, age, nationality$)$ Publisher$($pID$,$ pName, address$)$ PublishesWith $($aID$,$ pID, since$)$ Book$($bID$,$ title, genre, bFormat$)$ AuthoredBy$($aID$,$ bID$)$ Sells$($pID$,$ bID, copiesAvailable, copiesSold$)$ Clarifications: $$ aID, pID and bID are the identification numbers $($unique IDs$)$ of authors, publishers, and books, respectively. $$ Underlined attributes indicate the primary keys of each relation. $$ The bFormat attribute indicates the format of the book, e$.$g$.$ hardback, softback, mass market, digital, etc. Assume each book has a single format, $($i$.$e$.$ if released as hardback it will not be released in paperback and so on$).$ The genre attribute indicate the genre of the book, e$.$g$.$: literary fiction, horror, crime, young adult, sci$-$fi$,$ fantasy, etc. $$ PublishesWith relation links authors with their publishers, the since attribute indicates the date when the authors started working for the corresponding publisher. Authors publish with publishers $($i$.$e$.$ they write books of multiple genres, which the publishers subsequently own and sell$).$ The same author could publish with multiple publishers, so do not make assumptions otherwise. $$ Authoredby relation links authors with the books they have written, as some books may have more than one author. $$ Sells relation indicate which books each publisher sells $($i$.$e$.$ publishes or releases$)$; copiesAvailable attribute indicates how many copies of a book are available for sale via the corresponding publisher; and copiesSold attribute indicates the number of copies of a book which has already been sold by the corresponding publisher. Task Create the above schemas in MySQL$,$ using the CREATE TABLE statement. Make sure that you define all possible keys, and that entity integrity and referential integrity are guaranteed in a reasonable manner. Key Points to Consider: $$ Make sure you assign appropriate domains for each attribute. $$ Make sure you assign appropriate primary keys for each table. $$ Make sure you identify, and correctly assign, foreign keys for tables which require them. Ensure you use the correct and complete syntax when doing so$.$ Do not add any additional attributes to any of the above relations when creating your tables Consider the following relational database schema:

$-$ Author$($aID$,$ aName, age, nationality$)$

$-$ Publisher$($pID$,$ pName, address$)$

$-$ PublishesWith $($aID$,$ pID, since$)$

$-$ Book$($bID$,$ title, genre, bFormat$)$

$-$ AuthoredBy$($aID$,$ bID$)$

$-$ Sells$($pID$,$ bID, copiesAvailable, copiesSold$)$

Clarifications:

$-$ aID, pID and bID are the identification numbers $($unique IDs$)$ of authors, publishers, and books, respectively.

$-$ Underlined attributes indicate the primary keys of each relation.

$-$ The bFormat attribute indicates the format of the book, e$.$g$.$ hardback, softback, mass market, digital, etc. Assume each book has a single format, $($i$.$e$.$ if released as hardback it will not be released in paperback and so on$).$

$-$ The genre attribute indicate the genre of the book, e$.$g$.$: literary fiction, horror, crime, young adult, sci$-$fi$,$ fantasy, etc.

$-$ PublishesWith relation links authors with their publishers, the since attribute indicates the date when the authors started working for the corresponding publisher. Authors publish with publishers $($i$.$e$.$ they write books of multiple genres, which the publishers subsequently own and sell$).$ The same author could publish with multiple publishers, so do not make assumptions otherwise.

$-$ Authoredby relation links authors with the books they have written, as some books may have more than one author.

$-$ Sells relation indicate which books each publisher sells $($i$.$e$.$ publishes or releases$)$; copiesAvailable attribute indicates how many copies of a book are available for sale via the corresponding publisher; and copiesSold attribute indicates the number of copies of a book which has already been sold by the corresponding publisher.

Task

Create the above schemas in MySQL$,$ using the CREATE TABLE statement. Make sure that you define all possible keys, and that entity integrity and referential integrity are guaranteed in a reasonable manner.

Key Points to Consider:

$-$ Make sure you assign appropriate domains for each attribute.

$-$ Make sure you assign appropriate primary keys for each table.

$-$ Make sure you identify, and correctly assign, foreign keys for tables which require them. Ensure you use the correct and complete syntax when doing so$.$

$-$ Do not add any additional attributes to any of the above relations when creating your tables.