代做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)

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