Question

Describe briefly a database application case of your choice and represent your data in terms of 3 linked (via foreign key/s) tables. List their respective primary key and foreign key/s if any. Your database application case should be different from those already covered in the lectures or practicals.

For the database case in the Additional Exercises of the previous practical, complete the proper construction of all the 5 tables in SQL, building all the pertinent primary and foreign keys there.

Draw the updated ER diagram that reflects all 5 tables and all the attributes there.

(optional) For a clean slate, it is recommended that you delete all those tables partially constructed last week for this question and start completely anew. You can do so by issuing an SQL command similar to

drop table Reviews, Writes, Reviewer, Book, Author;

or by dropping the tables one by one in the right order

drop table Reviews;
drop table Writes;
-- more to come here ...

Use SQL to create at least the 3 tables Book, Reviewer, and Reviews, specifying the respective primary key and foreign key if any. The Reviews table should also contain at least the date of the review and the resulting recommendation.

Insert some records into these tables. Use the INSERT command in SQL to insert at least one record into at least a pair of tables that are linked via a foreign key, and the value of the foreign key created this way should not be null.

In Regards To what the question says previous practical its this

Consider the database case of keeping record of which reviewer has been assigned to review which book for a book publisher, and which book has been written by which authors. Create 3 entity typesReviewer, Book and Author, and 2 relationship types Reviews and Writes.

Suppose each book considered for publication by a book publisher needs to be recorded for thebookId, book title, each author needs to be recorded for the authorId and authorName, and each reviewer needs to be recorded for the reviewerId and the reviewerName. Draw the diagrams for the entity types Book, Author, and Reviewer. What other attributes are likely to be useful in these entity types?

Assume each book considered for publication should have no more than 5 authors in total, a reviewer may review multiple books, and a book needs to be assigned at least 2 reviewers and no more than 6. Draw the ER diagram for this simple database and also specify the occurrence multiplicities for the relationship.

Similar to the steps in Visual track 1, use SQL Server's graphical user interface to design the tables Book, Author and Writes. Specify the respective primary keys, and enter 2-3 records into each of these tables.