Database Design And Table Structures For Tennis Tournament

Primary Keys and Foreign Keys in Tables

ID –  used to identify the primary keys and the foreign keys in the tables. For instance Match_ID, Round_ID, Ref_ID and Player_ID  are both primary keys and foreign keys in different tables.

Round – is the game level or cycle. The Tournament has four levels which translate to four rounds.

Role – is found in the player table. There are only three roles a player can partake in a team; leadership, being a primary player and being a substitute player.

Score – Found in the playerscorepersection table. It represents the number of goals a player has achieved in a given section of the game.

Scores – found in both the section and match tables. In the section table it represents the scores for each team during the section whereas in the match table, it represents the goals each team has achieved in the particular match.

Scale/Ranking – is the measure of a referee’s performance in every match that the referee was administering or upholding the tennis rules. 

Section. A match has three or two sections depending on the scores. If a team wins the first two sections consecutively then the match ends up being with two sections. Otherwise the match has three sections.

A game is either a single game or a pair game.

Single game – is played by four players singularly against the rival four players singularly. Meaning, there are four games in a game of type single.

Pair game – is played by pair of  players against the rival pair of players. Meaning, there are two games in a game of type pair.

Match – consists of three sections depending on the scores. If a team wins the first two sections consecutively then the match ends up being with two sections. Otherwise the match has three sections.

1.4 Database Design and Table structures

The database design is with reference to Teorey, Lightstone, Nadeau  and Jagadish  (2011).

Table	Attributes
Player	Player_ID(Primary key, Auto_increment) PlayerName(varChar(255) ) Email address(varChar(255)) Role(varChar(255)) TeamID(int(10) Foreign key)
Team	TeamID(Primary key, Auto_increment) TeamName(varChar(255))
Referee	Ref_ID(Primary key, Auto_increment) RefereeName(varChar(255) ) MatchID(Foreign key)
RefereeRanking	Ref_ID(Primary key) Ranking(int(2) ) MatchID(int(10) Foreign key) RoundID(int(10) Foreign key)
Round	RoundID(Primary key, Auto_increment) RoundName(varChar(255) )
Match	MatchID(Primary Key, Auto_increment) MatchName(varChar(255) ) RoundID(int(10) Foreign key)
Section	SectionID(Primary key, Auto_increment) SectionName(varChar(255) ) MatchID(int(10) Foreign key)
Game	GameID(Primary key, Auto_increment) GameName(varChar(255) ) Number(int(10)) MatchID(int(10) Foreign key)
PlayerScoreperSection	Player_ID(int(10) Foreign key) Scores(int(10) ) SectionID(int(10) Foreign key)

Player table

Player_ID	Name	Email address	Role	Team
12	Jayson Stockman	[email protected]	Leader Primary Player	Team A
14	Manuel Garry	[email protected]	Primary Player	Team B

Team table

TeamID	TeamName	Number of players
112	Team A	7
113	Team B	5

Referee table

Ref_ID	RefereeName	Match
1114	Jude Lawson	Team A vs Team B
1115	Charles Barbage	Team C vs Team D

Round Referee Ranking table

Round_Name	Referee_Name	Ranking
Round 1	Jude Lawson	10
Round 2	Charles Barbage	7

Match Table

Match_ID	MatchName
11112	Team A vs Team B
11113	Team C vs Team D

Game table

GameID	GameName	Number	MatchName
1111112	Single Game	4	Team A vs Team B
1111113	Pair Game	2	Team C vs Team D

Section Table

SectionID	SectionName	MatchName
111112	Section one	Team A vs Team B
111113	Section two	Team C vs Team D

Round table

RoundID	RoundName
1	Round one
2	Round two

PlayerScoreSection

SectionName	PlayerName	Scores
Section one	Jayson Stockman	20
Section two	Manuel Garry	12

The following table is not in 1^st Normal form because one of its records contains data which is not of atomic value.

Player_ID	Name	Email address	Role	Team
12	Jayson Stockman	[email protected]	Leader Primary Player	Team A
14	Manuel Garry	[email protected]	Primary Player	Team B

In 1^st Normal form the table should be as follows

Player_ID	Name	Email address	Role	Team
12	Jayson Stockman	[email protected]	Leader	Team A
12	Jayson Stockman	[email protected]	Primary Player	Team A
14	Manuel Garry	[email protected]	Primary Player	Team B

The following tables are not in second normal form because they contain multiple functional dependencies Elmasri and Navathe (2010)For tables to be in third normal form they must first be in second normal form and do not have transitive functional dependencies in the fields Thalheim (2013).

Round and Role in Tennis Tournament

The following tables still have transitive functional dependencies and therefore are not in third Normal Form Above tables in Third Normal Form

1 Creation of the database and tables

CREATE DATABASE TENNIS TOURNAMENT;

CREATE TABLE PLAYER(

Player_ID int(10) NOT NULL AUTO_INCREMENT,

PlayerName varChar(255) NOT NULL,

Email_Address varChar(255) NOT NULL,

Role varChar(255) NOT NULL,

Team_ID int(10) NOT NULL,

PRIMARY KEY(Player_ID),

FOREIGN KEY(Team_ID) REFERENCES TEAM(Team_ID)

CREATE TABLE TEAM(

Team_ID int(10) NOT NULL AUTO_INCREMENT,

TeamName varChar(255) NOT NULL,

PRIMARY KEY(Team_ID)

CREATE TABLE REFEREE(

Ref_ID int(10) NOT NULL AUTO_INCREMENT,

RefereeName varChar(255) NOT NULL,

MatchID int(10) NOT NULL,

PRIMARY KEY(Ref_ID),

FOREIGN KEY(MatchID) REFERENCES MATCH(MatchID)

CREATE TABLE REFEREERANKING(

Ref_ID int(10) NOT NULL,

Ranking int(2) NOT NULL,

MatchID int(10) NOT NULL,

RoundID int(10) NOT NULL,

FOREIGN KEY(Ref_ID) REFERENCES REFEREE(Ref_ID),

FOREIGN KEY(MatchID) REFERENCES MATCH(MatchID),

FOREIGN KEY(RoundID) REFERENCES ROUND(RoundID)

CREATE TABLE ROUND(

RoundID int(10) NOT NULL AUTO_INCREMENT,

RoundName varChar(255) NOT NULL,

PRIMARY KEY(RoundID)

CREATE TABLE MATCH(

MatchID int(10) NOT NULL AUTO_INCREMENT,

MatchName varChar(255) NOT NULL,

Scores varChar(255) NOT NULL,

Winner varChar(255) NOT NULL,

Loser varChar(255) NOT NULL,

RoundID int(10) NOT NULL,

PRIMARY KEY(MatchID),

FOREIGN KEY(RoundID) REFERENCES ROUND(RoundID));

CREATE TABLE SECTION(

SectionID int(10) NOT NULL AUTO_INCREMENT,

SectionName varChar(255) NOT NULL,

Scores varChar(255) NOT NUll,

MatchID int(10) NOT NULL,

PRIMARY KEY(SectionID),

FOREIGN KEY(MatchID) REFERENCES MATCH(MatchID)

CREATE TABLE GAME(

GameID int(10) NOT NULL AUTO_INCREMENT,

GameName varChar(255) NOT NULL,

MatchID int(10) NOT NULL,

PRIMARY KEY(GameID),

FOREIGN KEY(MatchID) REFERENCES MATCH(MatchID)

CREATE TABLE PLAYERSCOREPERSECTION(

Player_ID int(10) NOT NULL,

Score int(10) NOT NULL

Population of the database

INSERT INTO TEAM(Team_ID, TeamName)

VALUES (”,’Team one’);

INSERT INTO TEAM(Team_ID, TeamName)

VALUES (”,’Team Two’);

INSERT INTO TEAM(Team_ID, TeamName)

VALUES (”,’Team Three’);

INSERT INTO TEAM(Team_ID, TeamName)

VALUES (”,’Team Four’);

INSERT INTO TEAM(Team_ID, TeamName)

VALUES (”,’Team Five’);

INSERT INTO TEAM(Team_ID, TeamName)

VALUES (”,’Team Six’);

INSERT INTO TEAM(Team_ID, TeamName)

VALUES (”,’Team Seven’);

INSERT INTO TEAM(Team_ID, TeamName)

VALUES (”,’Team Eight’);

INSERT INTO TEAM(Team_ID, TeamName)

VALUES (”,’Team Nine’);

INSERT INTO TEAM(Team_ID, TeamName)

VALUES (”,’Team Ten’);

INSERT INTO PLAYER(Player_ID, PlayerName, Email_Address, Role, Team_ID)

VALUES(”,’Jude Lawson’,’[email protected]‘,’Leader’,’1′);

INSERT INTO PLAYER(Player_ID, PlayerName, Email_Address, Role, Team_ID)

VALUES(”,’Shania Twayne’,’[email protected]‘,’Primary Player’,’1′);

INSERT INTO PLAYER(Player_ID, PlayerName, Email_Address, Role, Team_ID)

VALUES(”,’Aborigine Stuart’,’[email protected]‘,’Substitute Player’,’1′);

INSERT INTO PLAYER(Player_ID, PlayerName, Email_Address, Role, Team_ID)

VALUES(”,’John Brown’,’[email protected]‘,’Substitute Player’,’1′);

INSERT INTO PLAYER(Player_ID, PlayerName, Email_Address, Role, Team_ID)

VALUES(”,’Alen Fred’,’[email protected]‘,’Leader’,’2′);

INSERT INTO PLAYER(Player_ID, PlayerName, Email_Address, Role, Team_ID)

VALUES(”,’Angeli Norman’,’[email protected]‘,’Primary Player’,’2′);

INSERT INTO PLAYER(Player_ID, PlayerName, Email_Address, Role, Team_ID)

VALUES(”,’Sparky Anderson’,’[email protected]‘,’Substitute Player’,’2′);

INSERT INTO PLAYER(Player_ID, PlayerName, Email_Address, Role, Team_ID)

VALUES(”,’Cecil Robert’,’[email protected]‘,’Substitute Player’,’2′);

INSERT INTO PLAYER(Player_ID, PlayerName, Email_Address, Role, Team_ID)

VALUES(”,’Charles Dickens’,’[email protected]‘,’Leader’,’3′);

INSERT INTO PLAYER(Player_ID, PlayerName, Email_Address, Role, Team_ID)

Scores and Score per Section

VALUES(”,’James Dobson’,’[email protected]‘,’Primary Player’,’3′);

INSERT INTO PLAYER(Player_ID, PlayerName, Email_Address, Role, Team_ID)

VALUES(”,’Eric Dolphy’,’[email protected]‘,’Substitute Player’,’3′);

INSERT INTO PLAYER(Player_ID, PlayerName, Email_Address, Role, Team_ID)

VALUES(”,’Ignacy Domeyko’,’[email protected]‘,’Substitute Player’,’3′);

INSERT INTO REFEREE (Ref_ID, RefereeName, Match_ID)

VALUES(”,’Julius Caesar’,’1′);

INSERT INTO REFEREE (Ref_ID, RefereeName, Match_ID)

VALUES(”,’Michael Owen’,’2′);

INSERT INTO REFEREE (Ref_ID, RefereeName, Match_ID)

VALUES(”,’Cornel Osmarn’,’3′);

INSERT INTO REFEREE (Ref_ID, RefereeName, Match_ID)

VALUES(”,’Mohammed Abdulaziz’,’4′);

INSERT INTO REFEREE (Ref_ID, RefereeName, Match_ID)

VALUES(”,’Smart Joker’,’5′);

INSERT INTO REFEREE (Ref_ID, RefereeName, Match_ID)

VALUES(”,’Serena Williams’,’6′);

INSERT INTO REFEREE (Ref_ID, RefereeName, Match_ID)

VALUES(”,’Paul Scloes’,’7′);

INSERT INTO REFEREE (Ref_ID, RefereeName, Match_ID)

VALUES(”,’Thiery Henry’,’8′);

INSERT INTO REFEREE (Ref_ID, RefereeName, Match_ID)

VALUES(”,’Liam Nayson’,’9′);

INSERT INTO REFEREE (Ref_ID, RefereeName, Match_ID)

VALUES(”,’Dwayne Johnson’,’10’);

INSERT INTO REFEREERANKING(Ref_ID, Ranking, Match_ID, Round_ID)

VALUES(‘1′,’5′,’1′,’1’);

INSERT INTO REFEREERANKING(Ref_ID, Ranking, Match_ID, Round_ID)

VALUES(‘2′,’4′,’2′,’1’);

INSERT INTO REFEREERANKING(Ref_ID, Ranking, Match_ID, Round_ID)

VALUES(‘3′,’2′,’3′,’1’);

INSERT INTO REFEREERANKING(Ref_ID, Ranking, Match_ID, Round_ID)

VALUES(‘4′,’7′,’4′,’1’);

INSERT INTO REFEREERANKING(Ref_ID, Ranking, Match_ID, Round_ID)

VALUES(‘5′,’6′,’5′,’1’);

INSERT INTO REFEREERANKING(Ref_ID, Ranking, Match_ID, Round_ID)

VALUES(‘6′,’3′,’6′,’1’);

INSERT INTO REFEREERANKING(Ref_ID, Ranking, Match_ID, Round_ID)

VALUES(‘7′,’1′,’7′,’1’);

INSERT INTO REFEREERANKING(Ref_ID, Ranking, Match_ID, Round_ID)

VALUES(‘8′,’8′,’8′,’1’);

INSERT INTO REFEREERANKING(Ref_ID, Ranking, Match_ID, Round_ID)

VALUES(‘9′,’10’,’9′,’1′);

INSERT INTO REFEREERANKING(Ref_ID, Ranking, Match_ID, Round_ID)

VALUES(’10’,’9′,’10’,’1′);

INSERT INTO ROUND(Round_ID, RoundName)

VALUES(”,’Round One’);

INSERT INTO ROUND(Round_ID, RoundName)

VALUES(”,’Round Two’);

INSERT INTO ROUND(Round_ID, RoundName)

VALUES(”,’Round Three’);

INSERT INTO ROUND(Round_ID, RoundName)

VALUES(”,’Round Four’);

INSERT INTO MATCHT(Match_ID, MatchName, Scores, Winner, Loser, Round_ID)

VALUES(”,’Team one vs Team two’,’8-22′,’Team Two’,’Team one’,’1′);

INSERT INTO MATCHT(Match_ID, MatchName, Scores, Winner, Loser, Round_ID)

VALUES(”,’Team Three vs Team Four’,’28-15′,’Team Three’,’Team Four’,’1′);

INSERT INTO MATCHT(Match_ID, MatchName, Scores, Winner, Loser, Round_ID)

VALUES(”,’Team Five vs Team Six’,’15-22′,’Team Six’,’Team Five’,’1′);

INSERT INTO MATCHT(Match_ID, MatchName, Scores, Winner, Loser, Round_ID)

VALUES(”,’Team Seven vs Team Eight’,’28-14′,’Team Seven’,’Team Eight’,’1′);

INSERT INTO MATCHT(Match_ID, MatchName, Scores, Winner, Loser, Round_ID)

VALUES(”,’Team Nine vs Team Ten’,’13-22′,’Team Ten’,’Team Nine’,’1′);

INSERT INTO MATCHT(Match_ID, MatchName, Scores, Winner, Loser, Round_ID)

VALUES(”,’Team Eleven vs Team Twelve’,’20-30′,’Team Twelve’,’Team Eleven’,’1′);

INSERT INTO MATCHT(Match_ID, MatchName, Scores, Winner, Loser, Round_ID)

VALUES(”,’Team Thirteen vs Team Fourteen’,’16-32′,’Team Fourteen’,’Team Thirteen’,’1′);

INSERT INTO MATCHT(Match_ID, MatchName, Scores, Winner, Loser, Round_ID)

VALUES(”,’Team Fifteen vs Team Sixteen’,’19-22′,’Team Sixteen’,’Team Fifteen’,’1′);

INSERT INTO MATCHT(Match_ID, MatchName, Scores, Winner, Loser, Round_ID)

VALUES(”,’Team Seventeen vs Team Eighteen’,’30-24′,’Team Seventeen’,’Team Eighteen’,’1′);

INSERT INTO MATCHT(Match_ID, MatchName, Scores, Winner, Loser, Round_ID)

VALUES(”,’Team Nineteen vs Team Twenty’,’25-32′,’Team Twenty’,’Team Nineteen’,’1′);

INSERT INTO SECTION(Section_ID, SectionName, Scores, Match_ID)

VALUES(”,’Section One’,’4-11′,’1′);

INSERT INTO SECTION(Section_ID, SectionName, Scores, Match_ID)

VALUES(”,’Section Two’,’4-11′,’1′);

INSERT INTO SECTION(Section_ID, SectionName, Scores, Match_ID)

VALUES(”,’Section One’,’6-11′,’2′);

INSERT INTO SECTION(Section_ID, SectionName, Scores, Match_ID)

VALUES(”,’Section Two’,’11-4′,’2′);

INSERT INTO SECTION(Section_ID, SectionName, Scores, Match_ID)

VALUES(”,’Section Three’,’11-0′,’2′);

INSERT INTO SECTION(Section_ID, SectionName, Scores, Match_ID)

VALUES(”,’Section One’,’10-11′,’3′);

INSERT INTO SECTION(Section_ID, SectionName, Scores, Match_ID)

Types of Games in Tennis Tournament

VALUES(”,’Section One’,’5-11′,’3′);

INSERT INTO SECTION(Section_ID, SectionName, Scores, Match_ID)

VALUES(”,’Section One’,’6-11′,’4′);

INSERT INTO SECTION(Section_ID, SectionName, Scores, Match_ID)

VALUES(”,’Section One’,’11-3′,’4′);

INSERT INTO SECTION(Section_ID, SectionName, Scores, Match_ID)

VALUES(”,’Section One’,’11-0′,’4′);

INSERT INTO GAME(Game_ID, GameName, Match_ID,  Number)

VALUES(”,’Single Game’,’1′,’4′);

INSERT INTO GAME(Game_ID, GameName, Match_ID,  Number)

VALUES(”,’Pair Game’,’1′,’2′);

INSERT INTO GAME(Game_ID, GameName, Match_ID,  Number)

VALUES(”,’Single Game’,’2′,’4′);

INSERT INTO GAME(Game_ID, GameName, Match_ID,  Number)

VALUES(”,’Pair Game’,’2′,’2′);

INSERT INTO GAME(Game_ID, GameName, Match_ID,  Number)

VALUES(”,’Single Game’,’3′,’4′);

INSERT INTO GAME(Game_ID, GameName, Match_ID,  Number)

VALUES(”,’Pair Game’,’3′,’2′);

INSERT INTO GAME(Game_ID, GameName, Match_ID,  Number)

VALUES(”,’Single Game’,’4′,’4′);

INSERT INTO GAME(Game_ID, GameName, Match_ID,  Number)

VALUES(”,’Pair Game’,’4′,’2′);

INSERT INTO GAME(Game_ID, GameName, Match_ID,  Number)

VALUES(”,’Single Game’,’5′,’4′);

INSERT INTO GAME(Game_ID, GameName, Match_ID,  Number)

VALUES(”,’Pair Game’,’5′,’2′);

INSERT INTO PLAYERSCOREPERSECTION(Player_ID, Score, Section_ID)

VALUES(‘5′,’10’,’1′);

INSERT INTO PLAYERSCOREPERSECTION(Player_ID, Score, Section_ID)

VALUES(‘6′,’1′,’1’);

INSERT INTO PLAYERSCOREPERSECTION(Player_ID, Score, Section_ID)

VALUES(‘2′,’4′,’1’);

INSERT INTO PLAYERSCOREPERSECTION(Player_ID, Score, Section_ID)

VALUES(‘7′,’5′,’2’);

INSERT INTO PLAYERSCOREPERSECTION(Player_ID, Score, Section_ID)

VALUES(‘8′,’6′,’2’);

INSERT INTO PLAYERSCOREPERSECTION(Player_ID, Score, Section_ID)

VALUES(‘1′,’4′,’2’);

INSERT INTO PLAYERSCOREPERSECTION(Player_ID, Score, Section_ID)

VALUES(’13’,’4′,’3′);

INSERT INTO PLAYERSCOREPERSECTION(Player_ID, Score, Section_ID)

VALUES(’14’,’7′,’3′);

INSERT INTO PLAYERSCOREPERSECTION(Player_ID, Score, Section_ID)

VALUES(‘9′,’6′,’3’);

INSERT INTO PLAYERSCOREPERSECTION(Player_ID, Score, Section_ID)

VALUES(’15’,’10’,’4′);

1.6.2 SQL codes to create each of the reports specified

Select statements De La Riva, Suárez-Cabal and Tuya (2010)

SELECT MATCHT.MatchName, MATCHT.Winner, ROUND.RoundName

FROM MATCHT

INNER JOIN ROUND ON MATCHT.Round_ID = ROUND.Round_ID;

To sort all teams according to their scores (ascending)|

SELECT PLAYER.Team_ID, TEAM.TeamName, SUM(PLAYERSCOREPERSECTION.Score)

FROM (( PLAYER

INNER JOIN TEAM ON PLAYER.Team_ID = TEAM.Team_ID)

INNER JOIN PLAYERSCOREPERSECTION ON PLAYER.Player_ID = PLAYERSCOREPERSECTION.Player_ID)

ORDER BY SUM(PLAYERSCOREPERSECTION.Score) ASC;

To provide a report for a referee with all the games he served

SELECT REFEREE.Ref_ID, REFEREE.RefereeName, MATCHT.MatchName

FROM REFEREE

INNER JOIN MATCHT ON REFEREE.Match_ID = MATCHT.Match_ID;

To provide a list of the loser teams

SELECT Loser

FROM MATCHT;

To provide a list of all players in all teams

SELECT PlayerName

FROM PLAYER;

Game scores after each round, including section scores

SELECT MATCHT.MatchName, MATCHT.Scores, SECTION.Scores, ROUND.RoundName

FROM (( MATCHT

INNER JOIN SECTION ON MATCHT.Match_ID = SECTION.Match_ID)

INNER JOIN ROUND ON MATCHT.Round_ID = ROUND.Round_ID);

The total number of games that each team played in a match

SELECT MATCHT.MatchName, SUM(GAME.Number)

FROM MATCHT

INNER JOIN GAME ON MATCHT.Match_ID = GAME.Match_ID

WHERE MATCHT.Match_ID = ‘1’;

The total scores of each team after a match

SELECT MatchName, Scores

FROM MATCHT;

The winner list of all recorded matches

SELECT MatchName, Winner

FROM MATCHT;

Referees ranking, referee with best performance at all matches

SELECT REFEREE.RefereeName, MAX(REFEREERANKING.Ranking)

FROM REFEREE

INNER JOIN REFEREERANKING ON REFEREE.Ref_ID = REFEREERANKING.Ref_ID;

Teorey, T.J., Lightstone, S.S., Nadeau, T. and Jagadish, H.V., (2011). Database modeling and design: logical design. [online]. 5^th. Michigan: Elsevier. 

Thalheim, B., (2013). Entity-relationship modeling: foundations of database technology. [online]. Illustrated. Bordeaux. Springer Science & Business Media.

Frantiska, J., (2018). Entity-Relationship Diagrams. In Visualization Tools for Learning Environment Development(pp. 21-30). Springer: Cham.

Elmasri, R. and Navathe, S., (2010). Fundamentals of database systems. [online]. 6^th. U.S: Addison-Wesley Publishing Company. 

Özsu, M.T. and Valduriez, P., (2011). Principles of distributed database systems. [online]. 3^rd. Paris: Springer Science & Business Media. 

De La Riva, C., Suárez-Cabal, M.J. and Tuya, J., (2010, May). Constraint-based test database generation for SQL queries. In Proceedings of the 5th Workshop on Automation of Software Test (pp. 67-74). ACM.