NIT1201 Introduction to Database Systems: Tennis Tournament Project

This document presents a comprehensive database solution for managing a tennis tournament at VU University. It includes defining business rules, constructing an Entity-Relationship (ER) model with entities like Teams, Matches, Referees, Students, and Staff, and detailing the characteristics of these entities with attributes, primary keys, and foreign keys. The solution provides example tables and a data dictionary, followed by normalization to the 3rd Normal Form (3NF) to eliminate redundancy and improve data integrity. The project implementation includes SQL code for database creation, table definitions, and data insertion. Queries are designed to demonstrate the database's functionality. This solved assignment, available on Desklib, serves as a valuable resource for students studying database systems.

Running head: DBMS
DBMS
Name of the Student:
Name of the University:
Author Note:

1DBMS
Table of Contents
Part 1..........................................................................................................................................2
1. Rules of the business..............................................................................................................2
2. Entities and Relationships......................................................................................................2
3. Characteristics of entities.......................................................................................................3
4. Example Tables for the ER model.........................................................................................5
5. Data Dictionary.....................................................................................................................7
6. Normalization to 3 CNF.........................................................................................................9
Part 2: Project Implementation.................................................................................................12
Queries:....................................................................................................................................29

2DBMS
Part 1
1. Rules of the business
The VU University is conducting a tennis tournament. It is a knockout tournament
where total of 20 teams will participate. Every team will play against each other every round
and one winner team will proceed to the next round. There will be a total of four rounds
including the final. The team must be consisting of one team leader and rest students. The
staff member will be the team leader. A team can have minimum 4 members and maximum 8
members. The organisation needs the team leaders to submit a form two weeks before the
tournament start, to register their teams. A single student or staff can only play for one team.
The tournament will allow single and pair games. The number if single team games is 4 and
number of pair team games is 2. So, a total of 6 games is allotted per team. The total points
from all the 6 games are counted against each team and the teams are ranked as per these
points. There are a total of 15 referees to monitor the games. Each match is assigned a referee
at the start of each round. Each of these referees will be marked against their performances
and ranked. The 2nd round will have the 4 highest ranked referees and the 3rd round will have
2 of the highest ranked referees. The final match of the tournament will be refereed by the
referee with the highest points.
2. Entities and Relationships
The main entities in our relationship are the teams which comprises of the team
leaders and team members. The team members are the students and the staff are the team
leaders. The staff and the students together constitutes the entity team or in other words, a
team cannot exist without the students and the staff. The matches again is another entity
which comprises of teams. Teams play in these matches. This entity keep tracks of the points

3DBMS
scored by the teams and stores the total scores, fixture details and team names. Hence this
entity is derived from the team entity and borrows the team data to work. The Referee is
another entity which supervises the match and is connected to the MatchReferee entity which
is responsible for keeping the score of the referees. The MatchReferee is also connected to
the Matches entity and the Referee entity is responsible for storing the details of the referees.
The Student and the staff both entities are connected to the team entity. Many to one
relationship is followed in this entity. The Matches entity is totally depended on the team
entity. Here one to many relationship is followed as one team can play many matches. The
MatchReferee and the Referee entities have many to one relationship as one Referee
scorecard sheet can have scores of many referees. The match and the referee entity has one to
one relationship as each match can have one referee score for each individual referee. So its
relationship between them is on sharing of information to one another basis.
3. Characteristics of entities
The main characteristics of the given entities is their attributes, relationships and the
primary and foreign keys.
a. Student
In this entity, the main attributes are student_id, team_id, name, contact_number,
email_address and role. The primary key here is the student_id and the foreign key is the
team_id. The relationship of this entity with Team is many to one.
b. Staff
The main attributes of this entity are Staff_id, team_id, name, contact_number,
email_address and role. The primary key here is the staff_id and the foreign key here is the
team_id. The relationship between the staff and the Team is many to one.

4DBMS
c. Matches
The main attributes of this entity are match_id, first_team_id, second_team_id,
winner_team, fixture_date, round, group_name and points. The primary key here is the
match_id and the foreign keys are first_team_id, second_team_id and winner_team. The
relationship between this entity and the MatchReferee entity is one to one.
d. Team
The main attributes of this entity are team_id, team_name and team_member. The
primary key here is the team_id and there is no foreign key. The relationship between this
entity and the Matches are one to many.
e. MatchReferee
The main attributes of this entity are refree_id, match_id and score. In this entity two
attributes are given as both primary and foreign keys and hence can be attributed as two super
keys. These super keys are referee_id and match_id. The relationship between this entity and
the Referee entity is many to one.
f. Referee
The main attributes of this entity are refree_id, name, contact_number and
email_address. The primary key here is referee_id and there is no foreign key. The
relationship between this entity and the MatchReferee is one to many.

5DBMS
4. Example Tables for the ER model
a. Student
Student_id Team_id Name Contact_number Email_address Role
b. Staff
Staff_id Team_id Name Contact_numbe Email_address role

6DBMS
r
c. Team
Team_id Team_name Total_number
d. Matches
Match
_id
First_tea
m_id
Second_tea
m_id
Winner_t
eam
Fixture_
date
Rou
nd
Group_n
ame
poin
ts
e. MatchReferee
Referee_id Match_id score
f. Referee

7DBMS
Referee_id Name Contact_number Email_address
g. Referee
match_id section_id winner_team section_score
5. Data Dictionary
Table: Team
Attribute Data Type Range Primary key Foreign Key
team_id int 11 Yes
team_name varchar 200
total_member int 11
Table: Student
Attribute Data Type Range Primary key Foreign Key
student_id int 11 Yes
team_id int 11 Team table
(team_id)
name varchar 200
contact_number varchar 200

8DBMS
email_address varchar 200
role varchar 200
Table: Staff
Attribute Data Type Range Primary key Foreign Key
staff_id int 11 Yes
team_id int 11 Team table
(team_id)
name varchar 200
contact_number varchar 200
email_address varchar 200
role varchar 200
Table: Referee
Attribute Data Type Range Primary key Foreign Key
referee_id int 11 Yes
name varchar 200
contact_number varchar 200
email_address varchar 200
Table: Matches
Attribute Data Type Range Primary key Foreign Key
match_id int 11 Yes
first_team_id int 11 Team table

9DBMS
(team_id)
second_team_id int 11 Team table
(team_id)
winner_team int 11 Team table
(team_id)
fixture_date DATE
round varchar 200
group_name varchar 200
points int 11
Table: Matchreferee
Attribute Data Type Range Primary key Foreign Key
referee_id int 11 Yes Referee table
(referee_id)
team_id int 11 Match table
(match_id)
score int 11
6. Normalization to 3 CNF
First Normal Form: The normalization states that database tables must not have any
multivalued attributes in order to be in first normal form. The Staff, Student and Team related
attributes were in the same table. For each team, the staff and student attributes were
multivalued. The table example is provided below.
team_id Team_name Staff_id Staff_name Student_id Student_name

10DBMS
1 Blasters 1 Eden Mead 1 Alexandra
Santo
5 Cooper De
Loitte
5 Luke Glassey
9 Nate Heffron
If the above table is converted into first normal form then the following table is the
answer.
team_id Team_name Staff_id Staff_name Student_id Student_name
1 Blasters 1 Eden Mead 1 Alexandra
Santo
1 Blasters 5 Cooper De
Loitte
5 Luke Glassey
1 Blasters 9 Nate Heffron
Second Normal Form: The second normal form is the state where database entities
are free of partial dependencies. The staff name is dependent on the staff_id and student name
is dependent on student id. These two dependencies are partial dependencies. If partial
dependencies are resolved then the following tables are created.
team_id Team_name Staff_id Staff_name Student_id Student_name
1 Blasters 1 Eden Mead 1 Alexandra
Santo
1 Blasters 5 Cooper De 5 Luke Glassey

11DBMS
Loitte
1 Blasters 9 Nate Heffron
Staff_id Staff_name team_id
1 Eden Mead 1
5 Cooper De
Loitte
1
Student_id Student_name team_id
1 Alexandra
Santo
1
5 Luke Glassey 1
9 Nate Heffron 1
team_id Team_name
1 Blasters
Third normal form: The database entities are free of transitive dependencies to be in
the third normal form. Team_id and referee_id have transitive dependencies among them.
That is why a new table called matchreferee is implemented. The matchreferee is consisted of
both the math and referee entity primary keys as foreign keys and uses match_id and
referee_id as composite primary key.