Task 1 (7 marks) Construct an ER diagram in Workbench (starting with the incomplete sample provided) for the SBH database. You may need to introduce additional entities and relationships to capture...

Task 1 (7 marks)
Construct an ER diagram in Workbench (starting with the incomplete sample
provided) for the SBH database. You may need to introduce additional entities and
relationships to capture the data requirements of SBH. You must include attributes,
their appropriate data types and define identifiers for each entity in the process. You
will also need to implement appropriate referential integrity constraints. Place the
diagram into a file called A1__< lastname="">.doc. Please make
sure that your diagram is clearly readable.
Identify any constraints that cannot be modelled in the ER diagram.
Task 2 (3 marks)
Identify all functional dependencies for each relation of the SBH database. Then
state which normal form (1NF, 2NF, 3NF or BCNF) each of the existing relation is in,
and why. Place your answer into the file A1__.doc.
Task 3 (1 mark)
Create the DDL scripts to implement the relevant tables in MySQL. Either do
this through the CASE tool (Workbench), or alternatively you may choose to create
the DDL scripts yourself using a text editor and save them in a file named
A1__.sql. Also copy and paste them into the file
A1__.doc.
Task 4 (3 marks)
Insert records into each of your tables (there should be a minimum of 3 records
per table). When inserting records into the tables, make sure that the result of each
SQL query below will return at least one record. The SQL statements for this part
should also be saved in your A1__.sql file. Also
copy and paste them into the file A1__.doc.
Task 5 (6 marks)
Provide SQL query expressions / statements for the following queries which
should also be saved in your A1__.sql file. Note
that these SQL queries must be tested, and the query statements together with their
results must also be placed in the A1__.doc file.
a) Find the book title and price of historical books.
b) Find the name and email address of all authors who wrote at least one fiction but
no historical book.
c) Find the total number of books published by each publisher.
d) Find the total number of copies in the inventory (including all branches) for
each book. Include only the top five books based on the number of copies
available in the result set.