Detailed Database Design and SQL Implementation Report - IMAT5103

This report details the design and implementation of a game database system. It begins with a scenario selection, focusing on a game database to store information about games, producers, and gamers. The report presents a conceptual database design outlining business rules for data storage, including game details, gamer information, producer details, and console specifics. Following this, a logical database design is provided, defining tables such as Producer, Games, Gamers, and Console, along with their attributes and relationships. The report then proceeds with the creation of these tables using Oracle DBMS, including data population and the creation of four useful indexes. Finally, it demonstrates SQL query writing, providing six queries to retrieve specific information from the database, such as game details by producer, gamer names, and game popularity counts, followed by a bibliography of resources used.

Running head: DATABASE
Database
Name of the Student:
Name of the University:
Author Note

1
DATABASE SYSTEM AND DESIGN
Stage 1: Scenario and Conceptual Database Design Task
1.1: Selection of the case upon which the database design and implementation is to be
based
A game database has been assumed for the descriptions in the report and the detailed
information about the games and the gamers have been used in this report. The database has
been used for the description of the games, their producers and the type of platforms on
which a gamer plays that games. There are different genre of games and there is different
type of genre for the producers as well. The game database should also be able to provide
important information to the gamer about any particular game.
Task 1.2: Provide a conceptual database design for your scenario & the list of enterprise
rules being modelled.

2
DATABASE SYSTEM AND DESIGN
Business Rules
The business rules on which the development of the database is done are described
below:
 The system should be able to store the details of the games in the database with the
major information including the producers, type of game and the mode of game.
 The system should be able to store the details of the gamers who are playing the
games and on which console they would be playing the games.
 The database should be able to store the details of the producers that produce the
games.
 The database should also store the details of the consoles on which the gamers play
the games.
Stage 2: Logical Database Design and Oracle SQL Implementation/querying
Task 2.1: Provide a Logical Database Design for your scenario
Logical design for the selected scenario is provided below:
Producer (ProducerID(pk), ProducerName)
Games (GameID (pk), GameName, GType, GMode, ProducerID (fk))
Gamers (GamerID (pk), GamerName, GameID (fk), ConsoleID (fk))
Console (ConsoleID (pk), ConsoleName)
Task 2.2 Create the tables using Oracle DBMS
Database tables
Console

3
DATABASE SYSTEM AND DESIGN
Producer
Game

4
DATABASE SYSTEM AND DESIGN
Gamers
Task 2.3: Create the four most useful indexes on your tables
The list of constraints is provided below:
 constraint "P2502929CONSOLE_PK" primary key ("CONSOLEID")
 CONSTRAINT "P2502929PRODUCER_PK" PRIMARY KEY ("PRODUCERID")
 ALTER TABLE "P2502929GAMES" ADD CONSTRAINT
"P2502929GAMES_FK"
FOREIGN KEY ("PRODUCERID")
REFERENCES "P2502929PRODUCER" ("PRODUCERID")
ON DELETE SET NULL

5
DATABASE SYSTEM AND DESIGN
 ALTER TABLE "P2502929GAMERS" ADD CONSTRAINT
"P2502929GAMERS_FK"
FOREIGN KEY ("GAMEID")
REFERENCES "P2502929GAMES" ("GAMEID")
ON DELETE SET NULL
Task 2.4: Data Population
Console
Producers

6
DATABASE SYSTEM AND DESIGN
Games
Gamers

7
DATABASE SYSTEM AND DESIGN
Task 2.5: SQL Query writing
Query 1
Select P2502929PRODUCER.PRODUCERNAME, P2502929GAMES.GAMENAME
From P2502929PRODUCER
Inner Join P2502929GAMES On P2502929GAMES.PRODUCERID =
P2502929PRODUCER.PRODUCERID
Where P2502929PRODUCER.PRODUCERNAME = 'Electronic Arts';
Query 2

8
DATABASE SYSTEM AND DESIGN
Select P2502929GAMERS.GAMERNAME
From P2502929PRODUCER
Inner Join P2502929GAMES On P2502929GAMES.PRODUCERID =
P2502929PRODUCER.PRODUCERID
Inner Join P2502929GAMERS on P2502929GAMERS.GAMEID =
P2502929GAMES.GAMEID
Where P2502929PRODUCER.PRODUCERNAME = 'Electronic Arts';
Query 3
Select P2502929GAMES.GAMENAME, COUNT(P2502929GAMERS.GAMERNAME)
From P2502929GAMERS
Inner Join P2502929GAMES on P2502929GAMERS.GAMEID =
P2502929GAMES.GAMEID
Where P2502929GAMES.GAMENAME = 'FIFA 10' OR P2502929GAMES.GAMENAME
= 'FIFA 19'
Group by P2502929GAMES.GAMENAME;

9
DATABASE SYSTEM AND DESIGN
Query 4
Select P2502929GAMERS.GAMERNAME
From P2502929GAMERS
Inner Join P2502929GAMES on P2502929GAMERS.GAMEID =
P2502929GAMES.GAMEID
Where P2502929GAMES.GAMENAME = 'Tom Clancy' OR
P2502929GAMES.GAMENAME = 'Max Payne';
Query 5
Select P2502929GAMES.GAMENAME, COUNT(P2502929GAMERS.GAMERNAME)
From P2502929GAMERS
Inner Join P2502929GAMES on P2502929GAMERS.GAMEID =
P2502929GAMES.GAMEID
Group By P2502929GAMES.GAMENAME
Order by COUNT(P2502929GAMERS.GAMERNAME) DESC;

10
DATABASE SYSTEM AND DESIGN
Query 6
Select P2502929PRODUCER.PRODUCERNAME, COUNT(P2502929GAMES.GAMEID)
From P2502929PRODUCER
Inner Join P2502929GAMES on P2502929PRODUCER.PRODUCERID =
P2502929GAMES.PRODUCERID
Group By P2502929PRODUCER.PRODUCERNAME;

11
DATABASE SYSTEM AND DESIGN
Bibliography
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management.
Cengage Learning.
Hu, Y., 2016. Design and Implementation of Recruitment Management System Based on
Analysis of Advantages and Disadvantages of PHP Three-Tier. Romanian Review Precision
Mechanics, Optics & Mechatronics, (49), p.74.
Lee, S., Tewolde, G. and Kwon, J., 2014, March. Design and implementation of vehicle
tracking system using GPS/GSM/GPRS technology and smartphone application. In Internet
of Things (WF-IoT), 2014 IEEE World Forum on (pp. 353-358). IEEE.
Pfaff, B., Pettit, J., Koponen, T., Jackson, E., Zhou, A., Rajahalme, J., Gross, J., Wang, A.,
Stringer, J., Shelar, P. and Amidon, K., 2015. The design and implementation of open
vswitch. In 12th {USENIX} Symposium on Networked Systems Design and Implementation
({NSDI} 15) (pp. 117-130).