本次数据库代写是完成数据库建模并进行相关的查询操作
CP2404 / CP5633 Database Modelling
Assignment #2
Task 1: Creating the database [61 marks]
- 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] - Create a physical database model using the forward engineering process on MySQL
Workbench. - 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) - 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] - List the name of each author whose last names start with letter “O” or end with “phen”.
- List the title of each book published by Penguin USA.
- List the title of each book that is in the type of CMP, HIS, or SCI.
- Write query to find out publisher_name and total copies of book ‘Song of Solomon’
- 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. - 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.
