CP2404 / CP5633 Database Modelling
Assignment #2
Task 1: Creating the database [61 marks]

  1. Use MySQL Workbench to create a relational database model as presented in the
    following ERD. This is a simplified database model designed for a bookshop company
    named ‘JM Books’.
    PKs should be correctly specified. All necessary attributes should be specified by setting
    appropriate data types and appropriate field lengths. [Save the completed model as
    a .mwb file]
  2. Create a physical database model using the forward engineering process on MySQL
  3. Insert the data supplied into the database. You may choose to use either the SQL
    INSERT syntax or the import facility provided by MySQL Workbench. (Note: the names
    or order of attributes in the data files may not exactly match those shown in the ERD)
  4. Finally, dump the database into one integrated file on MySQL Workbench. [Save the
    file as .sql file]
    Task 2: Creating queries [40 marks]
    Using the database you constructed in Task 1, create the following SQL queries in MySQL
    Workbench. The resulting table for each query should look like the one provided for each
    question if you created the database correctly as required. [Paste your SQL query into a
    Word document to submit]
  5. List the name of each author whose last names start with letter “O” or end with “phen”.
  6. List the title of each book published by Penguin USA.
  7. List the title of each book that is in the type of CMP, HIS, or SCI.
  8. Write query to find out publisher_name and total copies of book ‘Song of Solomon’
  9. Write SQL query to find out how many book copies available at the “JM Downtown”
    branch and “JM On the Hill” having price that is greater than $10 but less than $20.
  10. For each book title (in an alphabetical order) with more than four copies, show how
    many copies in total the JM bookshop has and what the average price is.


Assignment Exmaple

Recent Case

Service Scope

oop|Processing|JS|Ruby|Scala|Rust|Data Mining|数据库|Oracle|Mysql|Sqlite|IOS|Data Mining|网络编程|多线程编程|Linux编程操作系统|计算机网络|留学生|编程|程序|代写|加急|个人代写|作业代写|Assignment