Database Design for Tennis Match Management System

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 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.
Entity and Relationships in the system
The diagram below provides the details of the main entities and the relation of the
system:

2
DATABASE DESIGN
Characteristics of the entities
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.

3
DATABASE DESIGN
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.
ER Model for the system

4
DATABASE DESIGN
Table structures for the model
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)
Dependency analysis
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

5
DATABASE DESIGN
TeamID TeamName, GroupID, NumberOfMember
RefereeID RefereeName
Rank RefereeID, TotalRatings
Data Dictionary
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

6
DATABASE DESIGN
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
Team1TotalScor
e
Int
Team2TotalScor
e
Int
Winner Int Foreign key Not Null
Loser Int Foreign key Not Null
DateOfGame Date
Members
Attribute Data Type Size key Null

7
DATABASE DESIGN
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
Referee
Attribute Data Type Size Key Null
RefereeID Int Primary key Not Null
RefereeName Varchar2 100
Rankings
Attribute Data Type Size Key Null