Carefully study the Online Book database scenario below and complete the questions that follow:

BOOK

ISBN

Book$\_$title

Category

Price

Copyright$\_$date

Year

Page$\_$count

P$\_$ID

$10001$

Database Systems

Textbook

$320$

$2020$

$2020$

$600$

P$5001$

$10002$

Relational Algebra

Textbook

$500$

$2023$

$2023$

$440$

P$5002$

$10003$

The Beautiful Ones Are Not Yet Born

Novel

$400$

$2024$

$2024$

$320$

P$5001$

PUBLISHER

P$\_$ID

Pname

Address

State

Phone

Email$\_$id

P$5001$

Nkosi Publications

$2$ Berverly Hills

Cape Town

$075487242$

nkozi@aol.com

P$5002$

Dominic Publishing

$21$ Drive Way

Midrand

$076569222$

dominic@aol.com

AUTHOR

A$\_$ID

Aname

State

City

Zip

Phone

URL

A$10$

Steve Harvey

US

US

$2001$

$085289211$

www$.$steve.com

A$20$

Obaro Adewale

South Africa

South Africa

$1830$

$075682942$

www$.$obaro.com

Source: Obaro Adewale $(2024)$

Question $3$

$3\mathrm{.}1.$a$.$ Based on the tables presented in the Online Book database, create a logical database diagram which reflect the scenario and establish the relationship between the tables. Indicate the appropriate data$-$types in the data fields. A sample of the design is presented below.

$(5$ Marks$)$

$3\mathrm{.}1.$b$.$ Using the relational tables in the scenario above, draw an equivalent graph database model to the logical database design in $3\mathrm{.}1$a$.$

$(5$ Marks$)$

$3\mathrm{.}2$ Write queries in relational algebra for the following based on the Online Book database.

I. Retrieve the city, phone and url of the author whose name is Obaro Adewale.

II$.$ Retrieve the name, address and phone of all the publishers located in US state.

III. Retrieve the title and price of all the Textbook category with a page count greater than $400.$

IV$.$ Retrieve the ISBN, title and price of the books belonging to either Textbook or Novel book category.

V$.$ Retrieve the ID$,$ name, address and phone of publishers publishing novel.

VI$.$ Retrieve the title and price of all the books published by Nkosi Publications.

VII. Retrieve the book title, reviewers ID and rating of all the text books.

VIII. Retrieve the title, category and price of all the books written by Steve Harvey.

$(18$ Marks$)$

$3\mathrm{.}3.$a$.$ Study the two Database transactions defined by T$1$ and T$2,$ which are presented below:

i$.$ Write a serial schedule for these transactions.

$(3$ Marks$)$

ii$.$ Write an equivalent non$-$serial schedule for these transactions.

$(3$ Marks$)$

$3\mathrm{.}3.$b$.$ State and discuss the two $(2)$ ways by which the performance of the above query can be evaluated.

$(6$ Marks$)$

$3\mathrm{.}3.$c$.$ If the two transactions encounter a deadlock during processing, provide a solution on how it can be solved in this scenario.

$(2$ Marks$)$