IMAT5103 - Database Design: Implementing a Tournament Database System

This report presents a database solution designed for the Denver Sports Club to manage their tournament, including player and team data, match results, and winning team details. The report includes an Entity Relationship Diagram (ERD) illustrating the database structure, business rules that guided the design, and SQL queries used to create and populate the database tables within Oracle DBMS. The database is structured to store information about players, teams, coaches, and match winners, facilitating efficient tracking and management of tournament data. Example SQL queries demonstrate how to retrieve specific information, such as the players on the winning team, the number of players in each position, and the coaches associated with winning teams. Desklib offers a wide array of solved assignments and study resources for students.

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

1
DATABASE
Table of Contents
Stage 1: Scenario and Conceptual Database Design..................................................................2
Task 1.1: Case Scenario.........................................................................................................2
Task 1.2: Entity Relationship Diagram:.................................................................................3
EER Diagram.....................................................................................................................3
Assumption........................................................................................................................3
Conceptual Diagram:.........................................................................................................4
Business Rules...................................................................................................................4
Task 2.1: Provides a logical database design for your scenario.............................................5
Task 2.2: Create the tables using Oracle DBMS....................................................................5
Database tables...................................................................................................................5
Data Population..................................................................................................................6
SQL Queries.......................................................................................................................8
Bibliography.............................................................................................................................13

2
DATABASE
Stage 1: Scenario and Conceptual Database Design
Task 1.1: Case Scenario
The Case study for the Denver Sports Club has been displayed in this report. The club
is looking to host a tournament along with the players from their academy. The organization
would host the tournament in their home ground. All the players would be divided into a
number of teams and a number of matches would take place in between the teams decided by
the club. All the players would be participating in the tournament and there would be a
number of coaches for each of the teams. Each of the match would have a coach monitoring
the game.
Now, the organization is need of a database that would be storing the data for the club
in their matches and the results of the matches are also required to be stored in the database.
The database should also be able to store the details of the winning teams in the match. The
development of the database would help the club in determining and keeping track of the
matches and the tournament taking place within the organization.

3
DATABASE
Task 1.2: Entity Relationship Diagram:
EER Diagram
Assumption
The diagram that is created above is based on a football match in which the record of
the players and the coaches of the teams have been recorded in the database. The names of all
the players and the winning team has also been recorded in the database. It has been assumed
that the database has been used for the details regarding a single match in the football event.

4
DATABASE
Hence the data for only two teams has been used in the database. The data about the coaches
and the countries to which they belong has also been recorded in the database.
Conceptual Diagram:
Business Rules
The business rules which were followed during the implementation of the database
are provided in the description below:
 The database should be able to store the details of all the players that are participating
in the match, the details of the players should include the player identification
number, the name of the player and the position to the player plays for.
 The database should be able to store the details of the teams and the name of the
teams.
 The database should be able to store the details of the coaches for each of the teams
and there should be a single coach for each of the team.
 The database should be able to store the record the winner of the match and there
would only be a single winner in the match.

5
DATABASE
Task 2.1: Provides a logical database design for your scenario
Task 2.2: Create the tables using Oracle DBMS
Database tables
Teams
Players

6
DATABASE
Coaches
Winners
Data Population
Teams

7
DATABASE
Players
Coaches

8
DATABASE
Winners
SQL Queries
Query 1
Select P2508365PLAYERS.PLAYERNAME,P2508365PLAYERS.POSITION
From P2508365PLAYERS
Inner Join P2508365TEAMS on P2508365TEAMS.TEAMID =
P2508365PLAYERS.TEAMID
Inner Join P2508365WINNERS on P2508365WINNERS.TEAMID =
P2508365TEAMS.TEAMID;

9
DATABASE
The list of players and positions of the winning team has been displayed in the above
table.
Query 2
Select COUNT(P2508365PLAYERS.PLAYERID)
From P2508365PLAYERS
Where P2508365PLAYERS.POSITION = 'Centre Midfield';
The number of the players playing the centre midfield of the match has been
displayed using the query designed above.
Query 3
Select P2508365COACHES.COACHNAME,P2508365COACHES.COUNTRY
From P2508365COACHES

10
DATABASE
Inner Join P2508365TEAMS on P2508365TEAMS.TEAMID =
P2508365COACHES.TEAMID
Inner Join P2508365WINNERS on P2508365WINNERS.TEAMID =
P2508365TEAMS.TEAMID;
The details of the coach of the winning team has been displayed using the sql query.
Query 4
Select P2508365TEAMS.TEAMNAME
From P2508365TEAMS
Inner join P2508365PLAYERS On P2508365TEAMS.TEAMID =
P2508365PLAYERS.TEAMID
Where P2508365PLAYERS.PLAYERNAME = 'Paul Pogba' OR
P2508365PLAYERS.PLAYERNAME = 'Jesse Lingard';
The query has been used for searching the name of the team to which a certain player
belongs.
Query 5

11
DATABASE
Select P2508365TEAMS.TEAMNAME, COUNT(P2508365PLAYERS.PLAYERID)
From P2508365PLAYERS
Inner Join P2508365TEAMS on P2508365PLAYERS.TEAMID =
P2508365TEAMS.TEAMID
Group By P2508365TEAMS.TEAMNAME;
The number of players currently playing in each team has been displayed in the table
designed above using the query designed above.
Query 6
Select P2508365PLAYERS.POSITION, COUNT(P2508365PLAYERS.PLAYERID)
From P2508365PLAYERS
Group By P2508365PLAYERS.POSITION;

12
DATABASE
The details of the number of players in a certain playing area of field has been
identified by the query and the table has been used for displaying the result.

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