Diploma in Information Technology
Database Management and Security
Instruction for CA2 Group Assignment
April 2025 Semester
Continuous Assessment 2 - Group Assignment
100 marks (This assignment constitutes 40% of the overall grade)
Introduction:
You have been recruited by GameForge Studios, an indie game development company, to help design and implement a player account and progression tracking system for their latest MMORPG (Massively Multiplayer Online Role-Playing Game), "Elbion Online".
With a growing player base, the developers need a robust database to handle player accounts, character progression, inventory and quests. Your mission is to design and develop a new database system to manage these aspects efficiently.
Part 1: Player Account and Character Management (40%)
GameForge Studios stores user account data in a CSV file (see Elbion Online Test Accounts.csv). You must design a database schema that prevents data integrity issues (such as data anomalies and redundancies) and ensure that all tables are in 3NF.
You have been tasked with the following:
Tasks:
● Analyze the fields in the CSV file and create an appropriate data model for a MySQL database.
● Ensure all tables are normalized to 3NF.
● Write an SQL script to create the necessary tables.
● Write an SQL script to load the CSV data into your tables.
What to submit:
1. ER diagram of the database (15 marks)
2. SQL script. to create tables (10 marks)
3. SQL script. to insert CSV data into tables (15 marks)
Part 2: Course content and announcements (30%)
In "Elbion Online” , players can complete quests and collect items. Your database must support these features.
To help clarify your doubts, they have provided the following business rules:
Business Rules:
● A PLAYER can complete multiple QUESTS, but each QUEST can only be completed once.
● Each QUEST has a title and description
● A PLAYER can collect multiple ITEMS.
● Each ITEM has a name, description, rarity, and type (weapon, armor, potion, etc.).
NOTE: Not all business rules have been listed. Based on the rules given above, you will need to create a database that best fits the requirements of your company.
Tasks:
● Update your ER diagram to include QUESTS and ITEMS.
● Write SQL scripts to create new tables and update existing ones.
What to submit:
1. Updated ER diagram (15 marks)
2. SQL script. to create new and update existing tables (15 marks)
Part 3: Player Statistics and Economy (30%)
GameForge Studios wants to analyse player performance and in-game transactions. You need to write SQL queries to help track and analyze game data.
Tasks:
1. Provide a query that counts the number of characters each user has, sorted by the character count.
2. Provide a query that counts the number of characters at each level range in the game, in increments of 30 up to Level 90 (i.e. Level 1 to 30, 31 to 60, 61 to 90). Use the following formula to determine a player’s Level from his Experience points:
where n is the character’s level
3. Provide a query that ranks every user in the game, based on the total amount of experience all their characters have.
What to submit:
1. SQL script. to generate a report of how many characters each player has (10 marks)
|
SQL output columns for report:
1. Username
2. Number of Characters
|
2. SQL script. to generate a report of the number of characters at each level range, from Level 1 to 90 (10 marks)
|
SQL output columns for report:
|
|
1. Level Range (string)
2. No. of Players
|
3. SQL script. to generate a report of player ranking. (10 marks)
|
SQL output columns for report in (3):
1. Rank S/N
2. Username
3. Experience
|
Penalty for Late Submission of Assignment
By one day: 20% to be deducted from total marks.
More than one day: submission will NOT be graded.
CA2 Individual Assignment Deadline: 8 May 2025, 11.59 am
Rubric
|
Part
|
Description
|
Task
|
Marks
|
Total
Marks
|
|
1
|
Player Account and Character Management
|
ER diagram of the database
|
15
|
40
|
|
SQL script. to create tables
|
10
|
|
SQL script. to insert CSV data into tables
|
15
|
|
2
|
Quests,
Achievements, and Inventory Management
|
Updated ER Diagram
|
15
|
30
|
|
SQL script. to create new and update existing tables
|
15
|
|
3
|
Player Statistics and Economy
|
SQL script. to generate a report of how many characters each player has
|
10
|
30
|
|
SQL script. to generate a report of players at each level range, from Level 1 to 100
|
10
|
|
SQL script. to generate a report of player ranking
|
10
|
|
TOTAL
|
100
|