Assignment on implementing part of BookClub System with MySQL

Task

In this assignment you will implement part of the BookClub system you modeled in Assignment . (For those of you who are pining to write some SQL code, this is your chance!) The implementation will consist of a number of MySQL tables, views and triggers. Once your database is created you will need to populate it with some test data.

For this assignment, we will not be implementing the entire system, only the user and book entities and the reads relationship. Here is the stripped
down version of the relational model:

User(Email, DateAdded, NickName, Profile)
Primary Key: Email
Domain: Email → A string with a unique and valid (format) email address.
Domain: DateAdded → A DateTime field. It should be automatically set to the date and time the user was added. 

Domain: NickName → An optional string with a unique nickname for the user.
Domain: Profile → An optional string.

Users can be added and deleted. The Nick Name and Profile can be modified, but Date Added is not user modifible. Email, as the primary
key, cannot be changed. The user must be deleted and re-added with the new email. When a user is deleted all of the read book records associated with the user must also be deleted. 

Book (BookID, Title, Year, NumRaters, Rating)
Primary Key: BookID
Domain: BookID → A positive integer, unique and automatically generated.
Domain: Title → A string.
Domain: Year → A four digit integer representing a valid year.
Domain: NumRaters → An integer (default 0). The total number of users who have rated this book. This is a derived field as it is the count of rows in read book for this particular book.

Domain: Rating → A decimal(3,1). The average of all the ratings on this book. This is a derived field as it is the average of all the ratings in read book for this particular book.

Books can never be deleted.

Author(AuthorID, LastName, MiddleName, FirstName, DOB)
Primary Key: AuthorID
Domain: AuthorID → A postive integer, unique and automatically generated.
Domain: LastName → An optional string.
Domain: FirstName → A string.
Domain: MiddleName → An optional string. 

Bookauthor (AuthorID, BookID)
Primary Key: AuthorID, BookID
Foreign Key: AuthorID → Author
Foreign Key: BookID → Book

Read book (BookID, Email, DateRead, Rating)
Primary Key: BookID, Email
Foreign Key: BookID → BOOK
Foreign Key: Email → USER
Domain: DateRead → A date field indicating the date the user read the book.
Domain: Rating → An integer from 1-10 rating the book. (10 is good, 1 is bad).

Instances of readbook can be inserted or deleted and the Rating and DateRead can be modified. Whenever a row is inserted, deleted or modified the corresponding row in book, Numraters and Rating must be updated. Unless noted all fields are required. 

You will need to create tables for all of the relations above. Your tables should be as constrained as possible, that is have primary and foreign keys
defined and as precise as possible attribute domains. Use triggers to implement constraints that cannot be otherwise handled. In particular it should be possible to add or delete a row from read book and have the ratings be updated in the book table. For testing, it will be very helpful to create one or more views to allow you to easily check that your code is working properly.

You will need to have data and queries to demonstrate that the following tasks efficiently:
1. (10 Marks) Attempt to add invalid users. Add a valid user. Demonstrate that the required domain checking is being performed.
2. (10 Marks) Add a book with multiple authors. Demonstrate that the defaults for the book statistics are set and that one would look up books by authors and by books. (Hint: a view!)
3. (10 Marks) Have two users ’read’ and rate a book. Show that domain constraints are being met and all statistics have been updated. (Hint: a view!)
4. (10 Marks) Delete one of the ’read’ records just entered and show that all statistics have been updated.

Your code will be assessed as follows.
1. (10 Marks) Scripts well formatted, organized and commented.
2. (10 Marks) All required tables, triggers, views, etc. are implemented.
3. (10 Marks) Sufficient quality test data has been added.
4. (10 Marks) Testing is comprehensive. All cases for the domain checks and triggers have been tested. There is good demonstration that the desired actions are being carried out.