NIT1201 Introduction To Databases Systems

Answer:

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.

The main entities and the relation of the system are displayed below:

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.

Table structures for the model

The table structures for the ER model is provided below:

Referee (RefereeID (pk), RefereeName)

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

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
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
PalyerName	Varchar2	50

Teams

Attribute	Data Type	Size	key	Null
TeamID	Int		Primary key	Not Null
Teamame
GroupID			Foreign key	Not Null
NumberOfMembers	Int

Groups

Attribute	Data Type	Size	Key	Null
GroupID	Int		Primary key	Not Null

 

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.