Construct an ER diagram in Workbench

| October 14, 2019


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_<FirstName>_< 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_<FirstName>_<LastName>.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_<FirstName>_<LastName>.sql. Also copy and paste them into the file
A1_<FirstName>_<LastName>.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_<FirstName>_<LastName>.sql file. Also
copy and paste them into the file A1_<FirstName>_<LastName>.doc.
Task 5 (6 marks)
Provide SQL query expressions / statements for the following queries which
should also be saved in your A1_<FirstName>_<LastName>.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_<FirstName>_<LastName>.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.

Order your essay today and save 30% with the discount code: ESSAYHELP
Order your essay today and save 30% with the discount code: ESSAYHELPOrder Now