Database System and Design

This document provides a scenario and conceptual database design for a game database, along with logical database design and Oracle SQL implementation/querying.

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).