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