代做Database Management and Security调试SQL 程序

Diploma in Information Technology

Database Management and Security

Instruction for CA3 Group Assignment

April 2024 Semester

Continuous Assessment 3 - Group Assignment

100 marks (This assignment constitutes 40% of the overall grade)

Introduction:

You have been recruited by Niantic onto a super-secret team for their highly anticipated sequel, "Pokémon GO 2." With the monumental success of the original Pokémon GO game, the company has expedited development for its sequel and is eager to test its new features and online capabilities.

To that end, you have been tasked with developing a simple database for the game’s online services, so that employees in the company can start testing the online capabilities of the game (after work hours, of course).

You may find it helpful to research the game by playing it before starting this assignment:

.    Play Store:

https://play.google.com/store/apps/details?id=com.nianticlabs.pokemongo

.    App Store:

https://apps.apple.com/vc/app/pok%C3%A9mon-go/id1094591345

Part 1: Database setup (40%)

As the core developers of Pokémon GO 2 did not have formal training in database management, they stored all the user accounts and character data of their game testers in a traditional file system as a CSV file (see Pokémon GO 2 User Accounts.csv).

You have been tasked with the following:

Tasks:

.    Analyze the fields and data in the CSV file and create an appropriate data

model for storing the data in a MySQL database. You will need to ensure that the table(s) you create are all in 3NF.

.    Write an SQL script to insert the data in the CSV file into the database.

What to submit:

1.  ER diagram of the database. (15 marks)

2.  SQL script. to create the new table(s) (10 marks)

3.  SQL script. to load CSV data into the database tables (15 marks)

Part 2: Tracking Game Play Time (40%)

To enhance the gaming experience and encourage player immersion, Pokémon GO 2 will implement a system that tracks the gaming  habits of its players, enabling personalized in-game events and rewards based on their activity levels.

This tracking system will record every instance when a player logs in, as well as how long they play for in each sitting. As with the user accounts, the core developers of Pokémon GO 2 have stored the play records of all their users in a CSV file (see Pokémon GO 2 User Play Times.csv).

Task:

.    Analyze the fields and data in the CSV file and design new table(s) in the database to store the play duration data.

.    Generate an aggregated playtime report showing the total playtime of all users per month.

.    Generate usage reports detailing the total amount of time each user spends in the game.

What to submit:

1.  ER diagram of the database with the new table(s) (5 marks)

2.  SQL script. to create the new table(s) (5 marks)

3.  SQL script. to load CSV data into the database tables (10 marks)

4.  SQL script. to generate an SQL output showing the total amount of playtime, organised by month. (10 marks)

SQL output columns for report in (4): 1.  Month 2.  Total playtime (in hours, rounded to 2 decimal places)

5.  SQL script. to generate an SQL output showing the amount of time each player spent on the game in the months of February and March only. (10 marks)

SQL output columns for report in (5): 3.  Username 4.  Month 5.  Time played (in hh:mm:ss format)

Part 3: Additional requirements (20%)

Tasks:

1.  After the game has launched, the developers would like to have another report  detailing the most popular Pokemon types. Generate a report that counts the number of Pokemon of a certain type in the database.

2.  The programming team wants to add the game to track the following data as well:

.    Pokemon’s weight: Every Pokemon that is caught by the player will have a unique weight value between 0 to 255.

.    Pokemon’s height: Every Pokemon that is caught by the player will have a unique weight value between 0 to 255.

.    Pokemon’s move: Every Pokemon species will be assigned anywhere

between 1 to 3 moves. Each move will have a name, a single typing (same list of typings the Pokemon have) and attack power (an integer value between 0 to 100).

What to submit:

1.  SQL script. to generate an output showing the total number of Pokemon of each type in the database. (10 marks)

SQL output columns for report in (1): 1.  Pokemon Type 2.  Total Occurrences

2.  ER diagram of the database with the new attribute(s) and / or table(s) (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.

CA3 Individual Assignment Deadline: 13 May 2024, 11.59 am

Appendix

Part	Description	Task	Marks	Total  Marks
1	Database setup	ER Diagram	15	40
		SQL script. to create tables	10
		SQL to load data into database	15
2	Tracking game play time	Updated ER Diagram	5	40
		SQL script. to create new tables	5
		SQL to load data into database	10
		SQL script. to generate requested data	10
		SQL script. to generate requested data	10
3	Additional requirements	SQL script. to generate requested data	10	20
		Updated ER Diagram	10
TOTAL				100