Construct the entity-relationship diagram (ERD) for this scenario.

Outline why data validation is difficult for the Book_Title attribute.

State the result from the following query:

SELECT Book_Title
FROM BOOK
WHERE Genre = "Non-fiction"
AND ISBN = '0-98124-612-2'

Construct a query to find the titles of the books published by “Orlando Crux”.

Outline why atomicity is important within a database.

Outline how data consistency can be maintained in transactions in this database system.

Some data in the MRBS database is redundant.

Outline one problem caused by redundant data.

Award [2 max].
Award [1] for correct relationship “publishes books from”.
Award [1] for correct relationship “writes”.

Award [2 max].
As Book_Title attribute is String/Varchar;
It does not allow many validation checks (like range check etc.);

Award [1 max].
Seeking the truth;

Award [4 max].
Award [1] for selection of both Publisher_Name and Book_Title from the two respective tables.
Award [1] for making INNER JOIN of the Publisher_Name from both tables-Publisher and Author.
Award [1] for the correct test ON Author_Num from both tables-Author and Book.
Award [1] for the correct test of equity of Publisher_Name.

SELECT Publisher.Publisher_Name, Book.Book_Title FROM Author
INNER JOIN Book ON PUBLISHER. Publisher_Name = 
Author.Publisher_Name, ON Author.Author_Num = Book.Author_Num 
WHERE Publisher.Publisher_Name = "Orlando Crux";

Award [2 max].
Helps the searching of a family name for example rather than a “like” name search;
So, making data into the smallest possible unit helps quicker searches;

Award [2 max].
Each user sees a fixed view of the data;
This may include visible changes made by the user / the user's own transactions and transactions of other users;

Award [2 max].
When a field value changes multiple occurrences must be updated;
For example, if a publisher moves, we’ll need to change the values for City and Country in multiple records;
Problem occurs if we forget to change the values in any of the records;
The database would then have data inconsistency;

Most students knew how to sketch an ER diagram though some of them don’t seem to know the various relationships in it and how to apply. Some of them can draw the entities but setting the relationship was not done correctly.

Most of the students understood the validations in String attributes and its challenges.

The query was well understood.

Most students were able to get the basic structure of the query well though some of them could not set the two tables and their relationship properly while writing the query. Also many students searched for the publisher or author code not the publisher name.

Most students were able to explain atomicity though only very few students were able to refer to rollback as well.

‘Data consistency’ was understood though they could not relate to the given scenario of the bookstore database.

Most students gave generic answers and most of them incorrectly referred to the space redundancy that is not appropriate to this question clearly.