NDS203: Networking and Database Systems

Assignment Overview

Assessment Task

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 where 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.
Display Columns: Name and Type.

Query 2:

Print all Female NPCs.
Display Columns: First Name and Last Name.

Query 3:

Print all members of the ‘Rogues Guild’ in ascending order.
Display Columns: First Name, Last Name, Gender, and Faction Code.

Query 4:

Print all Quests given from NPCs in the location ‘Arcane Capital’.
Display Columns: Quest Name, NPC First Name.

Query 5:

Print all Quests that are given out by ‘Warriors United’ in a ‘Large Scale City’.
Display Columns: Quest Name, NPC First Name.

Query 6:

Print the number of quests given in each Location.
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:

• School of Magicians: Fireball

• Warriors United: Lunging Strike

• Rogues Guild: Steal

After adding the above requirements for Query 7, print each Faction with its Favourite Skill.
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.
Display Columns: First Name, Last Name, and Gender.

Assessment Summary

The given assessment focused on demonstrating the student’s understanding of database creation, population, and query execution in MySQL Workbench using a supplied dataset. The dataset, titled “Game World Data.xlsx”, represented a small virtual game environment consisting of Non-Player Characters (NPCs), factions, skills, and quests, each with relationships to specific locations and regions.

The key tasks were divided into three main sections:

1. Database Design:
   The student had to design and create multiple interrelated tables using proper SQL syntax, ensuring correct identification of primary and foreign keys. The database had to reflect all the defined relationships between entities.

2. Data Input:
   After successfully designing the structure, the student was required to populate the database using SQL scripts derived from the provided Excel dataset. The entire data insertion process needed to execute without any errors when run sequentially after the database creation script.

3. Queries and Mutations:
   A series of eight queries were required to demonstrate retrieval and manipulation of the stored data. These included extracting NPC details, listing quests, counting data entries, modifying relationships between tables, and altering data field types.

The assessment’s overall aim was to evaluate the student’s competency in designing normalized relational databases, writing accurate SQL statements, and applying theoretical database concepts to practical scenarios.

Academic Mentor’s Step-by-Step Guidance

The academic mentor provided structured, step-by-step guidance throughout the assessment to help the student approach each section with clarity, accuracy, and efficiency.

Step 1: Understanding the Dataset and Problem Context

The mentor first guided the student to carefully review the dataset (Game World Data.xlsx) and identify key entities — NPCs, Factions, Skills, Quests, Locations, and Regions.
A conceptual Entity-Relationship (ER) diagram was recommended as a preliminary step to visualize relationships such as:

• NPCs belonging to specific factions.

• Factions providing quests and training in skills.

• Quests associated with locations and NPCs.

This initial analysis helped the student build a strong conceptual foundation before starting with the database design in MySQL.

Step 2: Designing the Database (Task 1)

Once the entities and relationships were understood, the mentor helped the student design the database schema in MySQL Workbench.

Key aspects discussed included:

• Correct SQL syntax for creating tables.

• Use of primary keys to uniquely identify each record.

• Implementation of foreign keys to maintain referential integrity.

• Ensuring normalization to avoid redundancy.

The mentor emphasized writing a single SQL script capable of running successfully in one execution without errors. This approach promoted modular and error-free database design.

Step 3: Populating the Database (Task 2)

After successfully creating the database schema, the mentor instructed the student on how to write an SQL script to populate tables using data from the provided Excel sheet.
Key learning points included:

• Formatting data correctly before insertion.

• Avoiding the use of automated Excel readers — focusing instead on SQL-based data input.

• Verifying that foreign key constraints were maintained during data import.

The student was guided to test the script incrementally to ensure that each insertion executed properly and that the final dataset matched the structure defined in Task 1.

Step 4: Writing Queries and Mutations (Task 3)

This was the most extensive part of the assessment. The mentor guided the student through eight queries, each targeting a specific aspect of database manipulation and data retrieval.

1. Query 1: Retrieve all skills that can be learned — reinforcing statements with specific columns.

2. Query 2: Display all female NPCs — introducing conditional filtering with the clause.

3. Query 3: List all members of the “Rogues Guild” in ascending order — applying for sorting results.

4. Query 4: Display quests associated with the location “Arcane Capital” — using table joins to extract related data.

5. Query 5: Retrieve quests given by “Warriors United” in a “Large Scale City” — combining multiple filters and joins.

6. Query 6: Count the number of quests per location — employing aggregate functions like 

7. Query 7: Create a new one-to-many relationship between Factions and Skills, labeled “Favourite Skill” — reinforcing the concept of table alteration and relationship creation through statements.

8. Query 8: Modify the field data type and update all entries from short codes (‘M’, ‘F’) to complete words (‘Male’, ‘Female’) — illustrating schema evolution and bulk updates using.

Throughout this section, the mentor ensured that each SQL script was tested independently and then in sequence to confirm that the database operated without errors.

Final Outcome

By following the mentor’s step-by-step guidance, the student successfully:

• Designed a normalized relational database in MySQL Workbench.

• Populated it with accurate and consistent data derived from the Excel sheet.

• Executed all eight SQL queries effectively, producing the required outputs.

• Applied relational database principles such as data integrity, relationship modeling, referential constraints, and data manipulation using SQL.

The final deliverables included:

• A fully functional database schema.

• Three SQL scripts for creation, data insertion, and queries.

• Verified query outputs matching the expected display columns.

Learning Objectives Covered

Through this assessment, the student achieved multiple core learning objectives:

1. Understanding Database Design Concepts:
   Learned how to identify entities, define attributes, and create relationships through primary and foreign keys.

2. Developing Technical SQL Skills:
   Gained proficiency in writing SQL scripts for table creation, data insertion, and complex query execution.

3. Applying Data Normalization Principles:
   Understood the importance of structuring data efficiently to avoid redundancy and maintain consistency.

4. Enhancing Problem-Solving Abilities:
   Applied theoretical knowledge to a real-world-style dataset to analyze, interpret, and manipulate data accurately.

5. Demonstrating Practical Competency in MySQL Workbench:
   Acquired hands-on experience in designing, executing, and troubleshooting database operations within an applied context.

Conclusion

The assessment not only evaluated the student’s ability to design and manage databases but also strengthened their analytical thinking and technical confidence in working with structured datasets. Under the academic mentor’s systematic guidance, the student progressed from conceptual understanding to technical execution, producing a robust, error-free database solution that aligned with all learning outcomes and industry-standard database practices.

Get Expert Guidance for Your Assignment Success

Looking for inspiration to complete your assignment? Download this sample solution to understand how to structure, format, and approach your academic task effectively. This reference file can help you learn the right writing style, research methods, and formatting techniques needed to achieve high academic standards.

Important: This sample is meant for reference and study purposes only. Submitting it directly as your own work may lead to plagiarism issues and academic penalties. Always use it as a guide to create your original content.

If you need a fresh, plagiarism-free, and custom-written assignment solution, our team of professional academic writers is here to help. Each order is written from scratch, tailored to your topic, meets your university requirements, and passes all plagiarism checks, ensuring your submission is 100% authentic and high-quality.

Why Choose a Fresh Custom Solution?

• 100% original, plagiarism-free content

• Written by qualified academic experts

• Meets your specific guidelines and deadline

• Properly referenced and formatted

• Enhances your understanding of the topic

Take the next step towards academic excellence:
Download Sample Solution                   Order Fresh Assignment