Database Design for Tennis Tournament System

Verified

Added on  2023/06/03

|38
|4041
|355
AI Summary
This article discusses the database design for a tennis tournament system, including business rules, entity and relationship analysis, ER model, table structures, and SQL queries.

Contribute Materials

Your contribution can guide someone’s learning journey. Share your documents today.
Document Page
Running head: DATABASE DESIGN
Database Design
Name of the Student:
Name of the University:
Author Note

Secure Best Marks with AI Grader

Need help grading? Try our AI Grader for instant feedback on your assignments.
Document Page
1
DATABASE DESIGN
Answers to question number 1
Business Rules for the system
The business rules for the system are:
The system should be able to gather information about the players in the system and
the players in the system would be identified by their unique ID.
Records of all the games and the sections are to be stored in the database along with
their scores.
The winner of each game is to be stored in the system.
The ranking of the teams is to be stored in the system.
The details of the referees are also to be stored in the system.
Entity and Relationships in the system
The main entities and the relation of the system are displayed below:
Document Page
2
DATABASE DESIGN
Characteristics of the entities
The main entities which are identified for the system are:
Rounds: There are a total of four rounds in the tournament.
Games: In each of the rounds there are a number of games and each of the games.
Matches: There are a number of matches in each of the games, both singles mode and
doubles mode.
Sections: There three sections in each of the match.
Referee: The entity would be able to store the details of the referees in the system.
Ratings: The rating would be provided to the referees according to the performances in the
games.
Players: The players belong to the team and performs for the team in the matches.
Teams: The team consists of a number of players and each of the team participates in a
number of matches in order to win the tournament.
Groups: All the teams are grouped into a number of groups.
Document Page
3
DATABASE DESIGN
ER Model for the system
Table structures for the model
The table structures for the ER model is provided below:
Referee (RefereeID (pk), RefereeName)

Secure Best Marks with AI Grader

Need help grading? Try our AI Grader for instant feedback on your assignments.
Document Page
4
DATABASE DESIGN
Ratings (Rank (pk), RefereeID (fk), TotalRatings)
Section (SectionID (pk), MatchID (fk), Team1Points, Team2Points)
Matches (MatchID (pk), GameID (fk), Player1 (fk), Player2 (fk), Winner, RefereeID (fk))
Round (RoundID (pk), RoundName, NumberOfTeams, TopTeams)
Games (GameID (pk), RoundID (fk), Team1 (fk), Team2 (fk), Winner (fk), DateOfGame)
Players (PlayerID (pk), TeamID (fk), PlayerName)
Teams (TeamID (pk), TeamName, GroupID (fk), NumberOfMember)
Groups (GroupID (pk))
Dependency analysis
The functional Dependency of the database is provided below:
Attribute Dependencies
RefereeID RefereeName
Rank RefereeID, TotalRatings
SectionID MatchID, Team1
MatchID GameID, RoundID, Team1, Team2, Winner, RefereeID
RoundID RoundName, NumberOfTeams, TopTeams
GameID RoundID, Team1, Team2, Winner, DateOfGame
PlayerID TeamID, PlayerName
TeamID TeamName, GroupID, NumberOfMember
Data Dictionary
Referee
Document Page
5
DATABASE DESIGN
Attribute Data Type Size Key Null
RefereeID Int Primary key Not Null
RefereeName Varchar2 100
Ratings
Attribute Data Type Size Key Null
Rank Int Primary key Not Null
RefereeID Int Foreign key Not Null
Totalratings Int
Section
Attribute Data Type Size key Null
SectionID Int Primary key Not Null
MatchID Int Foreign key Not Null
Team1Points Int
Team2Points Int
Matches
Attribute Data Type Size key Null
MatchID Int Primary key Not Null
GameID Int Foreign key Not Null
Player1 Int Foreign key Not Null
Palyer2 Int Foreign key Not Null
Document Page
6
DATABASE DESIGN
Winner Int Foreign key Not Null
RefereeID Int Foreign key Not Null
Round
Attribute Data Type Size key Null
RoundID Int Primary key Not Null
RoundName Int Foreign key Not Null
NumberOfTeams Int Foreign key Not Null
TopTeams Int Foreign key Not Null
Games
Attribute Data Type Size key Null
GameID Int Primary key Not Null
RoundID Int Foreign key Not Null
Team1 Int Foreign key Not Null
Team2 Int Foreign key Not Null
Winner Int Foreign key Not Null
DateOfGame Date
Players
Attribute Data Type Size key Null
PalyerID Int Primary key Not Null
TeamID Int Foreign key Not Null

Paraphrase This Document

Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
Document Page
7
DATABASE DESIGN
PalyerName Varchar2 50
Teams
Attribute Data Type Size key Null
TeamID Int Primary key Not Null
Teamame
GroupID Foreign key Not Null
NumberOfMember
s
Int
Groups
Attribute Data Type Size Key Null
GroupID Int Primary key Not Null
Answers to question number 2
Database creation
The database is created with the name “tennistournament”
Document Page
8
DATABASE DESIGN
Database tables
The tables created are provide below :
Database data
Games
Document Page
9
DATABASE DESIGN
Group
Matches
Player

Secure Best Marks with AI Grader

Need help grading? Try our AI Grader for instant feedback on your assignments.
Document Page
10
DATABASE DESIGN
Ratings
Referee
Document Page
11
DATABASE DESIGN
Round
Section
Team
Document Page
12
DATABASE DESIGN
Answer to question number 3
SQL query 1
-- Database: `tennistournament`
--
-- --------------------------------------------------------
--
-- Table structure for table `games`
--
CREATE TABLE `games` (
`GameID` int(11) NOT NULL,

Paraphrase This Document

Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
Document Page
13
DATABASE DESIGN
`RoundID` int(11) NOT NULL,
`Team1` int(11) NOT NULL,
`Team2` int(11) NOT NULL,
`Winner` int(11) NOT NULL,
`Dateofgames` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- --------------------------------------------------------
--
-- Table structure for table `groups`
--
CREATE TABLE `groups` (
`GroupID` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
Document Page
14
DATABASE DESIGN
-- --------------------------------------------------------
--
-- Table structure for table `matches`
--
CREATE TABLE `matches` (
`MatchID` int(11) NOT NULL,
`GameID` int(11) NOT NULL,
`Player1` int(11) NOT NULL,
`Player2` int(11) NOT NULL,
`Winner` int(11) NOT NULL,
`RefereeID` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
--
-- Table structure for table `player`
Document Page
15
DATABASE DESIGN
--
CREATE TABLE `player` (
`PlayerID` int(11) NOT NULL,
`TeamID` int(11) NOT NULL,
`PlayerName` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
--
-- Table structure for table `ratings`
--
CREATE TABLE `ratings` (
`Rank` int(11) NOT NULL,
`RefreeID` int(11) NOT NULL,
`TotalRatings` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Secure Best Marks with AI Grader

Need help grading? Try our AI Grader for instant feedback on your assignments.
Document Page
16
DATABASE DESIGN
--
-- --------------------------------------------------------
--
-- Table structure for table `referee`
--
CREATE TABLE `referee` (
`RefereeID` int(11) NOT NULL,
`RefereeName` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- --------------------------------------------------------
--
-- Table structure for table `round`
Document Page
17
DATABASE DESIGN
--
CREATE TABLE `round` (
`RoundID` int(11) NOT NULL,
`RoundName` varchar(50) NOT NULL,
`NumberOfTeams` int(11) NOT NULL,
`TopTeams` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- --------------------------------------------------------
--
-- Table structure for table `section`
--
CREATE TABLE `section` (
`SectionID` int(11) NOT NULL,
`MatchID` int(11) NOT NULL,
Document Page
18
DATABASE DESIGN
`Team1Score` int(11) NOT NULL,
`Team2Score` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- --------------------------------------------------------
--
-- Table structure for table `team`
--
CREATE TABLE `team` (
`TeamID` int(11) NOT NULL,
`TeamName` varchar(50) NOT NULL,
`GroupID` int(11) NOT NULL,
`NumberOfMembers` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Paraphrase This Document

Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
Document Page
19
DATABASE DESIGN
--
-- Indexes for dumped tables
--
--
-- Indexes for table `games`
--
ALTER TABLE `games`
ADD PRIMARY KEY (`GameID`),
ADD KEY `RoundID` (`RoundID`),
ADD KEY `Team1` (`Team1`),
ADD KEY `Team2` (`Team2`),
ADD KEY `Winner` (`Winner`);
--
-- Indexes for table `groups`
--
ALTER TABLE `groups`
ADD PRIMARY KEY (`GroupID`);
Document Page
20
DATABASE DESIGN
--
-- Indexes for table `matches`
--
ALTER TABLE `matches`
ADD PRIMARY KEY (`MatchID`),
ADD KEY `GameID` (`GameID`),
ADD KEY `Player1` (`Player1`),
ADD KEY `Player2` (`Player2`),
ADD KEY `Winner` (`Winner`),
ADD KEY `RefereeID` (`RefereeID`);
--
-- Indexes for table `player`
--
ALTER TABLE `player`
ADD PRIMARY KEY (`PlayerID`),
ADD KEY `player_ibfk_1` (`TeamID`);
--
-- Indexes for table `ratings`
Document Page
21
DATABASE DESIGN
--
ALTER TABLE `ratings`
ADD PRIMARY KEY (`Rank`),
ADD KEY `RefreeID` (`RefreeID`);
--
-- Indexes for table `referee`
--
ALTER TABLE `referee`
ADD PRIMARY KEY (`RefereeID`);
--
-- Indexes for table `round`
--
ALTER TABLE `round`
ADD PRIMARY KEY (`RoundID`);
--
-- Indexes for table `section`
--

Secure Best Marks with AI Grader

Need help grading? Try our AI Grader for instant feedback on your assignments.
Document Page
22
DATABASE DESIGN
ALTER TABLE `section`
ADD KEY `MatchID` (`MatchID`);
--
-- Indexes for table `team`
--
ALTER TABLE `team`
ADD PRIMARY KEY (`TeamID`),
ADD KEY `GroupID` (`GroupID`);
--
-- Constraints for dumped tables
--
--
-- Constraints for table `games`
--
ALTER TABLE `games`
ADD CONSTRAINT `games_ibfk_1` FOREIGN KEY (`RoundID`) REFERENCES
`round` (`RoundID`),
Document Page
23
DATABASE DESIGN
ADD CONSTRAINT `games_ibfk_2` FOREIGN KEY (`Team1`) REFERENCES `team`
(`TeamID`),
ADD CONSTRAINT `games_ibfk_3` FOREIGN KEY (`Team2`) REFERENCES `team`
(`TeamID`),
ADD CONSTRAINT `games_ibfk_4` FOREIGN KEY (`Winner`) REFERENCES `team`
(`TeamID`);
--
-- Constraints for table `matches`
--
ALTER TABLE `matches`
ADD CONSTRAINT `matches_ibfk_1` FOREIGN KEY (`GameID`) REFERENCES
`games` (`GameID`),
ADD CONSTRAINT `matches_ibfk_2` FOREIGN KEY (`Player1`) REFERENCES
`player` (`PlayerID`),
ADD CONSTRAINT `matches_ibfk_3` FOREIGN KEY (`Player2`) REFERENCES
`player` (`PlayerID`),
ADD CONSTRAINT `matches_ibfk_4` FOREIGN KEY (`Winner`) REFERENCES
`player` (`PlayerID`),
ADD CONSTRAINT `matches_ibfk_5` FOREIGN KEY (`RefereeID`) REFERENCES
`referee` (`RefereeID`);
Document Page
24
DATABASE DESIGN
--
-- Constraints for table `player`
--
ALTER TABLE `player`
ADD CONSTRAINT `player_ibfk_1` FOREIGN KEY (`TeamID`) REFERENCES `team`
(`TeamID`);
--
-- Constraints for table `ratings`
--
ALTER TABLE `ratings`
ADD CONSTRAINT `ratings_ibfk_1` FOREIGN KEY (`RefreeID`) REFERENCES
`referee` (`RefereeID`);
--
-- Constraints for table `section`
--
ALTER TABLE `section`
ADD CONSTRAINT `section_ibfk_1` FOREIGN KEY (`MatchID`) REFERENCES
`matches` (`MatchID`);

Paraphrase This Document

Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
Document Page
25
DATABASE DESIGN
--
-- Constraints for table `team`
--
ALTER TABLE `team`
ADD CONSTRAINT `team_ibfk_1` FOREIGN KEY (`GroupID`) REFERENCES `groups`
(`GroupID`);
COMMIT;
SQL query 2
--
-- Dumping data for table `team`
--
INSERT INTO `team` (`TeamID`, `TeamName`, `GroupID`, `NumberOfMembers`)
VALUES
(1, 'Team Eagles', 1, 6),
(2, 'Team Tigers', 1, 6),
(3, 'Team Rhino', 1, 6),
(4, 'Team Elephant', 1, 6),
Document Page
26
DATABASE DESIGN
(5, 'Team Panda', 1, 2),
(6, 'Team cat', 2, 6),
(7, 'Team Apple', 2, 6),
(8, 'Team Banana', 2, 6),
(9, 'Team trainers', 2, 6),
(10, 'Team Rockstars', 2, 6);
-- Dumping data for table `section`
--
INSERT INTO `section` (`SectionID`, `MatchID`, `Team1Score`, `Team2Score`) VALUES
(1, 1, 11, 6),
(2, 1, 8, 11),
(3, 1, 11, 9),
(4, 2, 11, 8),
(5, 2, 7, 11),
(6, 2, 11, 8),
(7, 3, 1, 11),
(8, 3, 11, 10),
(9, 3, 11, 10),
Document Page
27
DATABASE DESIGN
(10, 4, 11, 9),
(11, 4, 9, 11),
(11, 4, 11, 8);
-- Dumping data for table `round`
--
INSERT INTO `round` (`RoundID`, `RoundName`, `NumberOfTeams`, `TopTeams`)
VALUES
(1, 'First Round', 10, 'Team 1, Team 2, Team 4, Team 5, Team 7, Team 8, Team 9, Team 10'),
(2, 'Second Round', 8, 'Team 1, Team 4, Team 9, Team 10'),
(3, 'Semi-Finals', 4, 'Team 1, Team 10'),
(4, 'Finals', 2, 'Team 1');
-- Dumping data for table `referee`
--
INSERT INTO `referee` (`RefereeID`, `RefereeName`) VALUES
(1, 'Mark Noble'),
(2, 'Ben Tennyson'),

Secure Best Marks with AI Grader

Need help grading? Try our AI Grader for instant feedback on your assignments.
Document Page
28
DATABASE DESIGN
(3, 'Ben Kinsley'),
(4, 'Kevin Spacey'),
(5, 'Matt Damon'),
(6, 'John Miller');
-- Dumping data for table `ratings`
--
INSERT INTO `ratings` (`Rank`, `RefreeID`, `TotalRatings`) VALUES
(1, 1, 109),
(2, 3, 100),
(3, 6, 97),
(4, 5, 89),
(5, 2, 82),
(6, 4, 80);
--
-- Dumping data for table `player`
--
Document Page
29
DATABASE DESIGN
INSERT INTO `player` (`PlayerID`, `TeamID`, `PlayerName`) VALUES
(1, 1, 'John Denver'),
(2, 2, 'John Lenon'),
(3, 1, 'Chris Martin'),
(4, 2, 'Martin Garrix'),
(5, 3, 'Elvis Presley'),
(6, 3, 'Mattew Hayden'),
(7, 4, 'Matt Taylor'),
(8, 4, 'Barry Richardson'),
(9, 5, 'Kevin Owens'),
(10, 5, 'Matthew Perry'),
(11, 6, 'Matt Le Blanc'),
(12, 6, 'Jhonny Evans'),
(13, 7, 'Fetty Wap'),
(14, 7, 'Marcos Alonso'),
(15, 8, 'Steve Austin'),
(16, 8, 'Kiren Lee'),
(17, 9, 'Tom Hardy'),
(18, 9, 'Lucas Pavon'),
(19, 10, 'John Terry'),
Document Page
30
DATABASE DESIGN
(20, 10, 'Frank Lampard');
--
-- Dumping data for table `matches`
--
INSERT INTO `matches` (`MatchID`, `GameID`, `Player1`, `Player2`, `Winner`,
`RefereeID`) VALUES
(1, 1, 1, 20, 1, 1),
(2, 1, 2, 19, 2, 2),
(3, 1, 2, 20, 2, 3),
(4, 1, 1, 19, 1, 4),
(5, 2, 1, 7, 1, 2),
(6, 2, 1, 8, 1, 1),
(7, 2, 2, 19, 2, 3),
(8, 2, 2, 20, 2, 4),
(9, 3, 17, 19, 19, 6),
(10, 3, 17, 20, 20, 5),
(11, 3, 18, 19, 19, 1),
(12, 3, 18, 20, 20, 3);

Paraphrase This Document

Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
Document Page
31
DATABASE DESIGN
--
-- Dumping data for table `matches`
--
INSERT INTO `matches` (`MatchID`, `GameID`, `Player1`, `Player2`, `Winner`,
`RefereeID`) VALUES
(1, 1, 1, 20, 1, 1),
(2, 1, 2, 19, 2, 2),
(3, 1, 2, 20, 2, 3),
(4, 1, 1, 19, 1, 4),
(5, 2, 1, 7, 1, 2),
(6, 2, 1, 8, 1, 1),
(7, 2, 2, 19, 2, 3),
(8, 2, 2, 20, 2, 4),
(9, 3, 17, 19, 19, 6),
(10, 3, 17, 20, 20, 5),
(11, 3, 18, 19, 19, 1),
(12, 3, 18, 20, 20, 3);
Document Page
32
DATABASE DESIGN
-- Dumping data for table `games`
--
INSERT INTO `games` (`GameID`, `RoundID`, `Team1`, `Team2`, `Winner`,
`Dateofgames`) VALUES
(1, 4, 1, 10, 1, '2018-09-30'),
(2, 3, 1, 4, 1, '2018-09-28'),
(3, 3, 9, 10, 10, '2018-09-27'),
(4, 2, 1, 2, 1, '2018-09-26'),
(5, 2, 4, 5, 4, '2018-09-25'),
(6, 2, 7, 9, 9, '2018-09-24'),
(7, 2, 8, 10, 10, '2018-09-23'),
(8, 1, 1, 2, 1, '2018-10-22'),
(9, 1, 1, 3, 1, '2018-09-22'),
(10, 1, 1, 4, 1, '2018-10-21');
SQL query 3
SELECT TopTeams FROM round;
Document Page
33
DATABASE DESIGN
SQL query 4
SELECT * FROM `teams`
SQL query 5
SELECT * FROM referee
INNER JOIN matches on referee.RefereeID = matches.RefereeID
WHERE referee.RefereeName = 'Mark Noble';

Secure Best Marks with AI Grader

Need help grading? Try our AI Grader for instant feedback on your assignments.
Document Page
34
DATABASE DESIGN
SQL query 6
SELECT team.TeamName FROM team INNER JOIN games ON games.Team1 =
team.TeamID WHERE team.TeamID NOT IN (Select Winner FROM games)
SQL query 7
SELECT * FROM `player`
Document Page
35
DATABASE DESIGN
SQL query 8
SELECT * FROM `section`
SQL query 9
Select COUNT (MatchID), GameID FROM matches
GROUP BY GameID;
SQL query 10
SELECT SUM(Team1Score), SUM(Team2Score), MatchID
FROM section
GROUP BY MATCHID;
Document Page
36
DATABASE DESIGN
SQL query 11
SELECT games.Winner, team.TeamName FROM games INNER JOIN team ON
games.Winner = team.TeamID
SQL query 12
SELECT * FROM `ratings`

Paraphrase This Document

Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
Document Page
37
DATABASE DESIGN
Bibliography
Elmasri, R. and Navathe, S., 2016. Fundamentals of database systems. London: Pearson.
Mitrovic, A. and Suraweera, P., 2016. Teaching database design with constraint-based tutors.
International Journal of Artificial Intelligence in Education, 26(1), pp.448-456.
1 out of 38
circle_padding
hide_on_mobile
zoom_out_icon
[object Object]

Your All-in-One AI-Powered Toolkit for Academic Success.

Available 24*7 on WhatsApp / Email

[object Object]