Analysis and Design
The business rules for the system are:
- The database should be able to store the details of the teams and the players who are participating in the tournament and the details for the matches and the winners of all the games and the matches are to be stored in the system.
- The system should be able to store the details of each of the teams in the competition and the members of each of the team.
- The details of the members of the teams are to be stored in the database.
- The round details and the winners of each of rounds of the competition are to be stored in the database.
- The database should also be able to store the details of the referees in the system and the they are to be ranked according to performances of the referees.
- The system should be able to store the information about the finals and the semi-finals round.
The diagram below provides the details of the main entities and the relation of the system:
The main entities which are identified for the system are:
Rounds: The tennis tournament has around 4 rounds.
Games: There are a number of games for the tournament in each of the matches.
Matches: There are a number of matches in each of the rounds.
Sections: Each game would be consisting of three sections.
Referee: The details of the referee would be stored in the system.
Rankings: The referees would be getting some ratings in the matches that they officiate and the
Members: The members belong to certain teams and participates in a certain number of matches.
Teams: There are a number of teams and each of the teams participate in a number of games in the tournament.
Groups: A whole tournament has been divided in two groups.
The table structures for the ER model is provided below:
Groups (GroupID (pk))
Teams (TeamID (pk), TeamName, GroupID (fk), NumberOfMember)
Members (MemberID (pk), TeamID (fk), MemberName)
Match (MatchID (pk), RoundID (fk), Team1 (fk), Team2 (fk), Team1TotalScore, Team2TotalScore, Winner, Loser DateOfGame)
Games (GameID (pk), MatchID (fk), Member1 (fk), Member2 (fk), Winner, Loser, RefereeID (fk))
Round (RoundID (pk), RoundName, NumberOfTeams, TopTeams)
Section (SectionID (pk), MatchID (fk), Team1Points, Team2Points)
Referee (RefereeID (pk), RefereeName)
Rankings (Rank (pk), RefereeID (fk), TotalRatings)
The functional Dependency of the database is provided below:
|
Attribute |
Dependencies |
|
SectionID |
MatchID, Team1Points, Team2Points |
|
MatchID |
MatchID, RoundID, Team1, Team2, Team1TotalScore, Team2TotalScore, Winner, Looser, RefereeID |
|
RoundID |
RoundName, NumberOfTeams, TopTeams |
|
GameID |
MatchID, Member1, Member2, Winner, Loser, RefereeID |
|
MemberID |
TeamID, MemberName |
|
TeamID |
TeamName, GroupID, NumberOfMember |
|
RefereeID |
RefereeName |
|
Rank |
RefereeID, TotalRatings |
Section
|
Attribute |
Data Type |
Size |
key |
Null |
|
SectionID |
Int |
Primary key |
Not Null |
|
|
MatchID |
Int |
Foreign key |
Not Null |
|
|
Team1Points |
Int |
|||
|
Team2Points |
Int |
Games
|
Attribute |
Data Type |
Size |
key |
Null |
|
GameID |
Int |
Primary key |
Not Null |
|
|
MatchID |
Int |
Foreign key |
Not Null |
|
|
Member1 |
Int |
Foreign key |
Not Null |
|
|
Member2 |
Int |
Foreign key |
Not Null |
|
|
Winner |
Int |
Foreign key |
Not Null |
|
|
Looser |
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 |
Match
|
Attribute |
Data Type |
Size |
key |
Null |
|
MatchID |
Int |
Primary key |
Not Null |
|
|
RoundID |
Int |
Foreign key |
Not Null |
|
|
Team1 |
Int |
Foreign key |
Not Null |
|
|
Team2 |
Int |
Foreign key |
Not Null |
|
|
Team1TotalScore |
Int |
|||
|
Team2TotalScore |
Int |
|||
|
Winner |
Int |
Foreign key |
Not Null |
|
|
Loser |
Int |
Foreign key |
Not Null |
|
|
DateOfGame |
Date |
Members
|
Attribute |
Data Type |
Size |
key |
Null |
|
MemberID |
Int |
Primary key |
Not Null |
|
|
TeamID |
Int |
Foreign key |
Not Null |
|
|
MemberName |
Varchar2 |
50 |
Teams
|
Attribute |
Data Type |
Size |
key |
Null |
|
TeamID |
Int |
Primary key |
Not Null |
|
|
TeamName |
||||
|
GroupID |
Varchar |
50 |
Foreign key |
Not Null |
|
Number OfMembers |
Int |
Groups
|
Attribute |
Data Type |
Size |
Key |
Null |
|
GroupID |
Varchar |
50 |
Primary key |
Not Null |
|
Attribute |
Data Type |
Size |
Key |
Null |
|
RefereeID |
Int |
Primary key |
Not Null |
|
|
RefereeName |
Varchar2 |
100 |
Rankings
|
Attribute |
Data Type |
Size |
Key |
Null |
|
Rank |
Int |
Primary key |
Not Null |
|
|
RefereeID |
Int |
Foreign key |
Not Null |
|
|
Totalratings |
Int |
Answers to question number 2
Database creation
The database is created with the name “tennismatch”
Database tables
The tables created are provide below:
Answer to question number 3
SQL query 1
CREATE TABLE `games` (
`GameID` int(11) NOT NULL,
`RoundID` int(11) NOT NULL,
`Member1` int(11) NOT NULL,
`Member2` int(11) NOT NULL,
`Winner` int(11) NOT NULL,
`Loser` int(11) NOT NULL,
`RefereeID` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1–
— Dumping data for table `games`
— Table structure for table `groups`
CREATE TABLE `groups` (
`GroupID` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;-
— Dumping data for table `groups`
CREATE TABLE `matches` (
`MatchID` int(11) NOT NULL,
`GameID` int(11) NOT NULL,
`Team1` int(11) NOT NULL,
`Team2` int(11) NOT NULL,
`Team1Score` int(11) NOT NULL,
Business Rules
`Team2Score` int(11) NOT NULL,
`Winner` int(11) NOT NULL,
`Loser` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;-
— Dumping data for table `matches`
— Table structure for table `member`
CREATE TABLE `member` (
`MemberID` int(11) NOT NULL,
`TeamID` int(11) NOT NULL,
`MemberName` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;-
— Dumping data for table `member`-
— Table structure for table `rankings`
CREATE TABLE `rankings` (
`Rank` int(11) NOT NULL,
`RefereeID` int(11) NOT NULL,
`TotalRatings` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;-
— Dumping data for table `rankings`-
— Table structure for table `referee`
CREATE TABLE `referee` (
`RefereeID` int(11) NOT NULL,
`RefereeName` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;–
— Dumping data for table `referee`-
— Table structure for table `round`
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;
– Dumping data for table `round`-
— Table structure for table `section`
CREATE TABLE `section` (
`SectionID` int(11) NOT NULL,
`GameID` int(11) NOT NULL,
`Team1Points` int(11) NOT NULL,
`Team2Points` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;(
`TeamID` int(11) NOT NULL,
`TeamName` varchar(50) NOT NULL,
`GroupID` varchar(50) NOT NULL,
`NumberOfMembers` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
— Dumping data for table `teams`–
— Indexes for dumped ta
Indexes for table `games`
ALTER TABLE `games`
ADD PRIMARY KEY (`GameID`),
ADD KEY `RoundID` (`RoundID`),
ADD KEY `Member1` (`Member1`),
ADD KEY `Member2` (`Member2`),
ADD KEY `Winner` (`Winner`),
ADD KEY `Loser` (`Loser`);
dexes for table `matches`–
ALTER TABLE `matches`
ADD KEY `GameID` (`GameID`),
ADD KEY `Team1` (`Team1`),
ADD KEY `Team2` (`Team2`),
ADD KEY `Winner` (`Winner`),
ADD KEY `Loser` (`Loser`)–
— Indexes for table `member`-
ALTER TABLE `member`
ADD PRIMARY KEY (`MemberID`),
ADD KEY `TeamID` (`TeamID`);-
— Indexes for table `rankings`
ALTER TABLE `rankings`
ADD PRIMARY KEY (`Rank`),
ADD KEY `RefereeID` (`RefereeID`);–
— Indexes for table `referee`-
ALTER TABLE `referee`
ADD PRIMARY KEY (`RefereeID`);-
— Indexes for table `round`–
ALTER TABLE `round`
ADD PRIMARY KEY (`RoundID`);–
ALTER TABLE `section`
ADD PRIMARY KEY (`SectionID`),
ADD KEY `MatchID` (`GameID`);-
— Indexes for table `teams–
ALTER TABLE `teams`–
ALTER TABLE `games`
ADD CONSTRAINT `games_ibfk_1` FOREIGN KEY (`RoundID`) REFERENCES `round` (`RoundID`),
ADD CONSTRAINT `games_ibfk_2` FOREIGN KEY (`Member1`) REFERENCES `member` (`MemberID`),
Entities and Relations
ADD CONSTRAINT `games_ibfk_3` FOREIGN KEY (`Member2`) REFERENCES `member` (`MemberID`),
ADD CONSTRAINT `games_ibfk_4` FOREIGN KEY (`Winner`) REFERENCES `member` (`MemberID`),
ADD CONSTRAINT `games_ibfk_5` FOREIGN KEY (`Loser`) REFERENCES `member` (`MemberID`);–
— 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 (`Team1`) REFERENCES `teams` (`TeamID`),
ADD CONSTRAINT `matches_ibfk_3` FOREIGN KEY (`Team2`) REFERENCES `teams` (`TeamID`),
ADD CONSTRAINT `matches_ibfk_4` FOREIGN KEY (`Winner`) REFERENCES `teams` (`TeamID`),
ADD CONSTRAINT `matches_ibfk_5` FOREIGN KEY (`Loser`) REFERENCES `teams` (`TeamID`);-
— Constraints for table `member–
ALTER TABLE `member`
ADD CONSTRAINT `member_ibfk_1` FOREIGN KEY (`TeamID`) REFERENCES `teams` (`TeamID`);–
— Constraints for table `rankings`–
ALTER TABLE `rankings`
ADD CONSTRAINT `rankings_ibfk_1` FOREIGN KEY (`RefereeID`) REFERENCES `referee` (`RefereeID`);–
— Constraints for table `section`–
ALTER TABLE `section`
ADD CONSTRAINT `section_ibfk_1` FOREIGN KEY (`GameID`) REFERENCES `games` (`GameID`);BLE `teams`
ADD CONSTRAINT `teams_ibfk_1` FOREIGN KEY (`GroupID`) REFERENCES `groups` (`GroupID`);
COMMIT;
/*!40101 SET [email protected]_CHARACTER_SET_CLIENT */;
/*!40101 SET [email protected]_CHARACTER_SET_RESULTS */;
/*!40101 SET [email protected]_COLLATION_CONNECTION */;
INSERT INTO `games` (`GameID`, `RoundID`, `Member1`, `Member2`, `Winner`, `Loser`, `RefereeID`) VALUES
(1, 4, 11, 5, 11, 5, 4),
(2, 3, 14, 4, 14, 4, 6),
(3, 3, 13, 18, 13, 18, 5),
(4, 3, 15, 6, 15, 6, 1),
(5, 3, 3, 17, 3, 17, 2),
(6, 2, 13, 1, 13, 1, 5),
(7, 2, 9, 7, 9, 7, 3),
(8, 1, 5, 12, 5, 12, 4),
(9, 1, 6, 15, 6, 15, 3),
(10, 1, 6, 10, 6, 10, 2);
INSERT INTO `groups` (`GroupID`) VALUES
(‘Group A’),
(‘Group B’);
INSERT INTO `matches` (`MatchID`, `GameID`, `Team1`, `Team2`, `Team1Score`, `Team2Score`, `Winner`, `Loser`) VALUES
(1, 5, 7, 5, 11, 6, 7, 5),
(2, 3, 1, 8, 11, 8, 1, 8),
(3, 4, 3, 7, 11, 10, 3, 7),
(4, 8, 4, 7, 11, 1, 4, 7),
(5, 4, 5, 3, 11, 9, 5, 3),
(6, 2, 4, 7, 11, 7, 4, 7);
INSERT INTO `member` (`MemberID`, `TeamID`, `MemberName`) VALUES
(1, 1, ‘Henry Denver’),
(2, 2, ‘Jaden Lenon’),
(3, 1, ‘Elvis Martin’),
(4, 2, ‘Peter Garrix’),
(5, 3, ‘Beck Presley’),
(6, 3, ‘Matt Hayden’),
(7, 4, ‘Mattew Taylor’),
(8, 4, ‘Harry Richardson’),
(9, 5, ‘Michael Owens’),
(10, 5, ‘Donovan Perry’),
(11, 6, ‘Ashley Le Blanc’),
(12, 6, ‘Austin Evans’),
(13, 7, ‘kevin Wap’),
(14, 7, ‘Gary Alonso’),
(15, 8, ‘Xabi Austin’),
(16, 8, ‘Zander Lee’),
(17, 9, ‘Peter Hardy’),
(18, 9, ‘Henry Pavon’),
(19, 10, ‘Frank Terry’),
(20, 10, ‘John Lampard’);
INSERT INTO `rankings` (`Rank`, `RefereeID`, `TotalRatings`) VALUES
(1, 1, 106),
(2, 3, 99),
(3, 6, 95),
(4, 5, 86),
(5, 2, 81),
(6, 4, 78);
INSERT INTO `referee` (`RefereeID`, `RefereeName`) VALUES
(1, ‘Jamie Noble’),
(2, ‘Mark Tennyson’),
(3, ‘Kevin Kinsley’),
(4, ‘Richard Spacey’),
(5, ‘Keneth Damon’),
(6, ‘David Miller’);
INSERT INTO `round` (`RoundID`, `RoundName`, `NumberOfTeams`, `TopTeams`) VALUES
(1, ‘First Round’, 10, ‘Team A, Team B, Team W, Team S, Team C, Team D, Team T, Team X’),
(2, ‘Second Round’, 8, ‘Team S, Team W, Team X, Team Y’),
(3, ‘Semi-Finals’, 4, ‘Team S, Team T’),
(4, ‘Finals’, 2, ‘Team S’);
INSERT INTO `section` (`SectionID`, `GameID`, `Team1Points`, `Team2Points`) VALUES
(1, 1, 11, 1),
(2, 1, 1, 11),
(3, 1, 1, 11);
INSERT INTO `teams` (`TeamID`, `TeamName`, `GroupID`, `NumberOfMembers`) VALUES
(1, ‘Team A’, ‘Group A’, 6),
(2, ‘Team B’, ‘Group A’, 6),
(3, ‘Team Z’, ‘Group A’, 6),
(4, ‘Team X’, ‘Group A’, 6),
(5, ‘Team S’, ‘Group A’, 6),
(6, ‘Team F’, ‘Group B’, 6),
(7, ‘Team G’, ‘Group B’, 6),
(8, ‘Team Y’, ‘Group B’, 6),
(9, ‘Team W’, ‘Group B’, 6),
(10, ‘Team T’, ‘Group B’, 6);5
SELECT * FROM referee
INNER JOIN games on referee.RefereeID = games.RefereeID
WHERE referee.RefereeName = ‘Jamie Noble’;
SELECT teams.TeamName from games
Inner join teams on games.Loser = teams.TeamID;
SELECT * FROM `member`
SELECT * FROM `section`
Select COUNT(GameID), MatchID FROM matches GROUP BY MatchID;
SELECT SUM(Team1Points), SUM(Team2Points), GameID
FROM section
GROUP BY GameID;
SELECT matches.Winner, teams.TeamName FROM matches INNER JOIN teams ON matches.Winner = teams.TeamID;
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
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
