CT042-3-1-Introduction to Databases Group Assignment

Page 1 of 4
Learning Outcomes:
 Apply redundancy control in designing a database.
 Demonstrate a database solution using an appropriate tool based on a case study.

Case Study:
BingBing Legend Online Game
A company, FierceOne Sdn. Bhd. wishes to develop a new mobile application, named
“BingBing Legend” which is a Multi-Player Online Battle Arena game. The company has
completed its game flow plan and interface designs, but as for database design, the company has
hired your team to help in designing and implementing the database. This BingBing Legend
mobile game has several requirements and features that need to be satisfied as stated in the
scenario below:

Scenario:
 In order to play this game, a player needs to first create a new account in BingBing Legend
mobile game. The player has to register by providing a unique email address and username.
A player account will be generated for the player which will consist of the player’s unique id,
email, username, current online status (either online or offline), total amount of battle points
earned, total diamonds collected, player’s global level, list of heroes (characters) owned and
each hero’s current level, and the skins of the heroes that the player owns.

 A player is able to buy multiple heroes (characters) as well as skins for the heroes they own.
The player must own at least one hero before he is able to purchase the skins for that specific
hero. Both heroes and skins of the hero can be purchased either by using battle points or
diamonds. Each hero has their own unique hero id, name, a role (such as Fighter, Tank,
Assassin, etc.), a specialty (such as Charge, Regen, Reap, etc.), and price of each hero (in
diamonds and battle points). Each skin will have a hero’s name, unique skin id, skin name,
and price of each (in diamonds and battle points). A hero may have multiple skins.

 To start playing the game, the player needs to choose their game modes. A game mode is the
arena where the game will be played. Each game needs only one arena. Each game mode has
its own unique id and name. There are three (3) types of game modes which are Brawl mode,
Human vs AI mode, and Classic Mode. After choosing a game mode, the players will decide
on their team members. A team will have exactly five (5) members, called as active-players,
for each game. A game needs to have 2 teams to start the game. Each game played may have
different active-players. The members are chosen based on the players’ online status.

 In each game, each active-player needs to choose one hero to represent him in the battle. The
choice of heroes must be unique in a team but not among other teams. For example, in a
game, Team A has chosen heroes named Dark Rose, Rafaela, Tigreal, Fanny, and Estes,
while Team B has chosen heroes named Rafaela, Fanny, Cyclops, Lolita and Clint. Each of
these heroes will have one skin selected for the game. The game will record information such
as game id, game date, game time, list of heroes, and heroes’ levels, and the game/battle’s
results. Each player’s global level will be changed accordingly based on this results.

Level 1 Asia Pacific University of Information & Technology 202010

CT042-3-1-Introduction to Databases Group Assignment
Page 2 of 4
 Once the game has ended, the records of the players’ basic information which includes the
profile details, previous games results, and most used hero for each player, and detailed
overall information about the players’ previous games will be viewed as personal statistics.
This will help the players keep track of their records.

Coursework Details:

1. In this assignment, you are required to design, implement, and document a database system
for BingBing Legend Mobile Game.

2. Create the following queries using Data Manipulation Language (DML) – Each student
must be able to explain the queries and justify the approach taken.

a) List the names and emails of all players whose names contain the strings ‘er’ or ‘on’.
Arrange the list alphabetically according to the names.
b) List the details of players who own any heroes whose levels are more than 3.
c) List the ids, names and levels of all heroes owned by the player named ‘Wade
Wilson’.
d) List, in chronological order of game date, the game ids and dates for all games played
between ‘01/02/2020’ and ‘29/02/2020’.
e) List the names and email addresses of all players who own any ‘Assassin’ role type
heroes.
f) List the ids and names of all heroes who has any skin priced higher than the average
price of all skins.
g) List the names and email addresses of all players who have collected at least 15
diamonds. Arrange the list alphabetically according to the names.
h) List the hero id, hero name and total number of skins belonging to each hero.
i) List the details of all players whose global levels exceed 5 and have played more than
3 games.
j) List the names and battle points of all players with the highest battle points. Arrange
the list alphabetically according to the names.
k) List the total number of games played in each game mode. Sort your answer in
descending order of total number of games played.
l) List the ids and names of the most used heroes for the player named ‘Steve Rogers’.

Level 1 Asia Pacific University of Information & Technology 202010

CT042-3-1-Introduction to Databases Group Assignment
Page 3 of 4
3. Deliverables:

Part Component
1 a) Database and Database Management System
 Disadvantages of file-based system
 Advantages of Database and DBMS
 Relate your discussion to the case study
1 b) Business Rules & Normalization
 Generate a list of business rules
 Provide an example of UNF and perform normalization up to 3NF
clearly showing all the steps with explanation
1 c) Entity Relationship Diagram
 Design the database using Chen’s or Crow’s foot notation
 Draw the ERD with any suitable tools such as Visio
 All entities, attributes, relationship and constraints should be clearly
shown
1 d) Workload Matrix of each team member for Part 1 of the assignment.
2 e) Database Schema
 Finalized ERD
 Map the ERD to its corresponding relational schema and normalise
all relations up 3NF
 Identify the attributes, data types and constraints of each tables and
document in data dictionary.
 Generate the database diagram from the DBMS
2 f) SQL-Data Definition Language (DDL)
 Create all tables with suitable data types
 Insert 5-10 rows of data into each table
 Screen shot all query statements
 Screen shot all tables with its data

2 g) SQL-Data Manipulation Language (DML)

 Write SQL statements to answer question (2) above
 Screen shot all query statements together with its executed result

2 h) Workload Matrix of each team member for Part 2 of the assignment.

General Requirements:

In this assignment you are required to:

 Work a group of 3-4 members.
 Design and implement a solution to a business problem.
 Implement the solution using any Enterprise DBMS.

Level 1 Asia Pacific University of Information & Technology 202010

CT042-3-1-Introduction to Databases Group Assignment
Page 4 of 4
 Document the solution as set out in the assignment requirements.
 Submit the document online according to the date and time given below.
 Submit a Workload Matrix given by lecturer.
 Each group member is required to participate in all tasks / discussions together.
 Presentation schedules will be published at a later date.

Note: It is acceptable for discrete activities of this assignment to be undertaken by individual

group members. However, it is essential that all group members understand the presentation
in its entirety. At the end of the demonstration your group will be asked a series of questions to
explore your understanding and analysis of the given problem. Responses to these questions
such as “I don’t know because I didn't work on that part of the assignment” are not
acceptable and will result in a penalty for either the entire group or specific individual(s).

Part Assessment Criteria: Marks Online

Allocation Submission
Date
1 Group Component (40%)
a) Database and Database Management System 8% Week 8/9
b) Business Rules & Normalization 12%
c) Entity Relationship Diagram 20%

2 Group Component (18%)

d) Database Schema 18% Week 13
Individual Component (42%)
e) SQL-Data Definition Language (DDL) 12%
f) SQL-Data Manipulation Language (DML) 30%

Level 1 Asia Pacific University of Information & Technology 202010