NIT1201 Introduction To Databases Systems Essay

Question:

Your task

You have been commissioned to develop a database system that is capable of keeping records for FU’s

table tennis matches from now on.

The database needs to keep a record of:

  • All team information, including players’ information
  • All games, sections, the players involved and the scores
  • The winner team of each game
  • The teams play in each round
  • The match winner team
  • The ranking of the teams of each year’s match

The referee assigned for each game and their performance score

Further, it should be possible to generate a report on:

  • Game scores after each round, including section scores
  • The total number of games that each team played in a match
  • The total scores of each team after a match
  • The ranking list of teams after a match
  • The winner list of all recorded matches

Referees ranking, referee with best performance at all matches

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.

How to cite this essay: