讲解 Database Management and Security Instruction for CA2讲解 SQL语言

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)

2.  SQL script. to generate a report of the number of characters at each level range, from Level 1 to 90 (10 marks)

3.  SQL script. to generate a report of player ranking. (10 marks)

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