Demonstrate your understanding of database creation and maintenance by creating tables and
running queries using the supplied dataset.
Please refer to the Task Instructions for details on how to complete this task.
Context
In this assessment, you will be provided with a large dataset. You are required to design a
database in MySQL Workbench that conforms to the design concepts taught during the
lectures, input the data and print out multiple outputs using queries
Instructions
An excel spreadsheet named ‘Game World Data.xlsx’ is provided with this brief which
contains the dataset needed to populate your database.
The dataset defines a small game world of NPCs. These NPCs are grouped together by
different factions, can give out quests and train others in certain skills. The database also
shows the location and region the NPC can be found in this world.
Task 1: Design the database
The first task is to design the database in MySQL Workbench by creating tables to store this
data. You need to ensure that:
• Syntax for creating tables is correct.
• Multiple tables are created to show each relationship in the database.
• Correct relationships are identified.
• There is proper use of primary keys.
• There is proper use of foreign keys.
You should use a single SQL script to create the database that can be run once in its entirety
without error.
Task 2: Input all the data
Once the tables are created, you should write another SQL script to populate your database
by extracting the data from the provided excel spreadsheet (not using an excel reader).
You should ensure that the whole SQL script need only be run once,after the script from
Task 1 has been executed, in its entirety without error.
Task 3: Queries and Mutations
After the database is fully populated, you should create a new SQL script for each of the
following queries to generate the output defined as display columns.
•
Query 1: Print all skills that can be learned in this database.
o Display Columns: Name and Type.
•
Query 2: Print all Female NPCs.
o Display Columns: First Name and Last Name.
•
Query 3: Print all members of the ‘Rogues Guild’ in ascending order.
o
Display Columns: First Name, Last Name, Gender and Faction Code.
•
Query 4: Print all Quests given from NPCs in the location ‘Arcane Capital’.
o Display Columns: Quest Name, NPC First Name.
•
Query 5: Print all Quests that are given out by ‘Warriors United’ in a ‘Large Scale
City’.
o
Display Columns: Quest Name, NPC First Name.
•
Query 6: Print the number of quests given in each Location.
o Display Columns: Location Name, Number of quests.
•
Query 7: Add a one-to-many relationship between the ‘Factions’ and ‘Skills’ to the
already existing database. Call this new relationship ‘Favourite Skill’ and update each
faction with the following skills;
o School of Magicians: Fireball.
o Warriors United: Lunging Strike.
o
Rogues Guild: Steal.
• After adding the above requirements for Query 7, print each Faction with its
Favourite Skill.
o
Display Columns: Faction Name, Favourite Skill Name.
•
Query 8: Change the data type of the field ‘Gender’ to CHAR() and update every NPC
to hold the values ‘Male’ and ‘Female’ instead of ‘M’ and ‘F’ respectively using a
single update call. Then show all NPCs.
o Display Columns: First Name, Last Name and Gender