关键词 > CA3/SQL代写
Database Management and Security
发布时间：2022-11-04
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit
Database Management and Security
Instruction for CA3 Individual Assignment
October 2022 Semester
Last updated: 2 November 2022
Continual Assessment 3 Individual Assignment
100 marks (This assignment constitutes 40% of the overall grade)
Introduction:
You have been recruited by Activision Blizzard onto a super-secret team for Diablo V (i.e. Diablo 5). With the release of Diablo IV looming, the company has developed an early prototype for Diablo V (these games take years to perfect, so it is good for big companies to start early), and would like to test out its 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 learn more about the video game series in their official website for Diablo IV:https://diablo4.blizzard.com/en-us/.
Part 1: Database setup (30%)
As the core developers of Diablo V 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 Diablo V User Accounts.csv). You have been tasked with the following:
Tasks:
1. Analyse 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.
2. Write an SQL script to insert the data in the CSV file into the database.
What to submit:
1. ER diagram of the database. (10 marks)
2. SQL script to create the new table(s) (10 marks)
3. SQL script to load CSV data into the database tables (10 marks)
Part 2: Billing (40%)
To encourage healthy gaming habits, Diablo V will implement a system that tracks the gaming habits of its players, so that the game can helpfully remind players if they have been playing too long.
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 Diablo V have stored the play records of all their users in a CSV file (see Diablo V User Play Times.csv).
Task:
1. Analysis the fields and data in the csv file and design new table(s) in the database to store the play duration data.
2. A usage report detailing the total amount of time each user spent on the game
What to submit:
1. ER diagram of the database with the new table(s) (10 marks)
2. SQL script to create the new table(s) (10 marks)
3. SQL script to load CSV data into the database tables (10 marks)
4. SQL script to generate an SQL output showing the amount of time each user has spent playing the game in the months of September and October only. (10 marks)
|
SQL output columns for usage report: 1. Username 2. Month 3. Time played (in hh:mm format) |
Part 3: Customer in-sight analysis (30%)
Tasks:
1. The developers would like to have another report detailing which are the most popular character classes in Diablo IV. Come up with another report that shows how much playtime each of the character classes has.
2. Suggest what other data the game should collect on players, and add table(s) as needed to your database to store the data. Justify your answer.
What to submit:
1. SQL script to generate an output showing the amount of playtime each character class receives. (15 marks)
2. Updated ER diagram with suggested additional attributes or entities to add, the SQL script for modifying the existing database schema to accommodate the addition, as well as an explanation for why these attributes or entities were added. (15 marks)
