Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

SCC 201 DATABASE

Coursework

2022/2023

A Database for a Multiplayer FPS game

The InfoLab21 Studios having a serious issue with their smash hit multi-player online game, Zork Nemesis 2: The revenge of fallen, which was rushed to market without adequate development time and therefore is facing several issues.

In short, a decision has been made to reimplement the database to resolve some of the issues, and a database expert has been employed to do the work – This expert is you!

Your task is to design and implement a relational database for some critical areas of the game.

To help you in your task several example data Þles are provided; these are:

 Players.csv - The main player and character data Þle, which contains player character definitions, with customer and billing data.

 Items.csv - Character inventory of weapons.

 Combat.csv - Records the activity and results of battles between players.

In the followings we will provide you some requirements, however we will expect you to exceed these expectations. You will need to demonstrate these in three milestones:

MILESTONES

1) Design and develop an Entity-Relationship Diagram (ERD) that models the following key entities:

a) Customers/Players.

b) Characters.

c) Inventory for the various weapons and armour a character may possess.

d) Combat activities (#of kills, KIA’s, victories, etc.).

Your ERD should handle the following rules as a minimum but please use your imagination to provide further rules: 

a) A player must have at least one character and can create a new character or deactivate one they no longer wish to play at any time. Once a player unsubscribed from the game, the character is also removed from the system.

b) A player will be billed at the end of a calendar month for all active characters for any part of that month.

c) A character can possess any number of inventory items (see Items.csv):

a. Weapons: have a range, damage points.

b. Armours: has a defence score and is worn on a speciÞ c body part.

c. Supplies: have a healing score, mana score and if consumed increase the character’s life score by that amount (after which time the character will possess one fewer of that type of item)

d) Characters have combat info (see Combat.csv)

2) Write relational schema and integrity constraints for your relations. Provide the relevant SQL DDL statements to implement your ERD.

a) Pay careful attention to your choice of datatypes and consistency.

b) Consider referential integrity and nullable/not nullable attributes.

c) Make comment on the Normalisation level of your schemas.

3) Milestone three has three parts:

a) Create a java library containing methods to read given .csv Þles that match the supplied example Þles and generate the relevant SQL insert statements to populate the database (your Þnal submission will be tested using data imported this way).

a. You will need to consider which columns in the csv Þles map to the relevant Entities.

b. Pay careful attention to the order in which entities need to be populated.

b) Create a set of SQL queries that implements your and the given queries (Providing relational algebra statements is a bonus):

1. List the top 5 characters with the highest number of successful combats attacks.

2. Print the name and total number of attacks per character having more than 5 attacks.

3. Order the names of characters from highest to lowest number of attacks.

4. List the name of Players with at least 5 characters.

5. List the name of weapons that is used by at least 10 Players.

c) Add methods to your java library to perform these queries using JDBC when called.

Assessment

Please have a look at your calendar, your workshop date, location, and time is provided. We will assess your project in three stages:

1. Your initial E-R diagram (30%)

You will provide two outputs: 1) An English paragraph given in a PDF Þle that describes your personal ERD and 2) your ERD (embedded in the PDF Þle). We will assess your ERD by reading your paragraph.

You will receive full mark if your paragraph matches to your ERD. Otherwise we will reduce your marks based on the following rules:

a) For each participation constraint fault (-1.5%).

b) For each multiplicity fault (-1.5%).

c) For each notation/Symbol fault (-1.5%).

2. SQL DDL ( 30%)

You will provide a PDF Þle that contains your Relational Schemas (RS), Integrity Constraints (IC), Normalisation reports for each RSs and the DDL statements written in SQL. You will receive full mark if your RS and ICs matches with your ERD. Otherwise, you we will reduce your mark based on the followings:

a) For each typo for SQL code (-0.6%).

b) Reporting wrong(or none) Normalisation level (for each table -1.5%).

c) For each IC mismatch (from ERD to ICs) and (from ICs to DDL) (-3%).

3. Java Library and SQL queries (40%)

You will provide a PDF Þle that contains the English versions of your queries. You will also provide a java library that reads CSV Þle and builds required tables and answers the queries you provided. You will receive full mark if your library successfully accomplishes these tasks. Otherwise we will reduce your mark according to the following rules:

a) Not compiles (-40%).

b) Compiles but cannot populate tables (-20%).

c) Cannot read CSV Þles but read from other Þle format and populates tables (-0%).

d) For each SQL warning, error (-3%).

e) For each failed query (-3%).

f) Providing Relational Algebra statements (for each query) (+3%)