NIT1201 Introduction to Database Systems: Table Tennis Tournament

This project presents a comprehensive database design for managing a table tennis tournament at VU University. It includes defining business rules, identifying key entities such as players, teams, matches, referees, and rounds, and establishing relationships between them. The project further develops an ER model and corresponding table structures, complete with data dictionaries and dependency analysis. The database implementation involves creating tables, defining primary and foreign keys, and populating the tables with sample data. SQL queries are provided for creating the database schema and inserting initial data, demonstrating the functionality and viability of the database design. This project offers a complete solution for organizing and managing data related to the table tennis tournament.

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

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:

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.

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)

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

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

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

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”

8
DATABASE DESIGN
Database tables
The tables created are provide below :
Database data
Games

9
DATABASE DESIGN
Group
Matches
Player

10
DATABASE DESIGN
Ratings
Referee

11
DATABASE DESIGN
Round
Section
Team

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,

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;
--

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`

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;

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`

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,

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;

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`);

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`

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`
--

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`),

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`);

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`);

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

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

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'),

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`
--

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'),

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

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

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;

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';

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`

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;

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`

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.