Designing an Effective Database System for Tennis Matches

The assignment provides a detailed report on designing an effective database system for tennis matches. It covers the process of collecting information and data, using ER diagrams to design the database structure, normalizing tables to reduce redundancy, and implementing SQL queries to retrieve specific data. The document also discusses various queries to be executed on the database, such as showing the winner of each game in each round, sorting teams by score, and providing a report for referees.

Introduction to database management
1

Contents
INTRODUCTION...........................................................................................................................................3
Part 1.......................................................................................................................................................3
Part 2.....................................................................................................................................................10
Part 3.....................................................................................................................................................16
CONCLUSION.............................................................................................................................................26
2

INTRODUCTION
Database management system is process for collecting the information and data of
particular organization as well as institutes. Database is a technique to update, delete, insert and
retrieve data in effective manner. It also stores information with full of security and privacy. This
report will describe the database of tennis match that collect all information regarding the
tournament and players those who is participating in the match.
Part 1
Design database applications
ER Diagrams:
Figure: 1
ER diagram is consisting of relationship between entities and attributes. This diagram should be
relationship among them. Entity relationship diagram is a graphical representation that allows to
design effective and efficient database system. According to figure: 1 , they have different
entities and attributes used in database implementations.
For Example-
Entity-
Match
3

Attributes-
match_id
match_date
match_time
match_score
match_refere
Entity-
Player
Attributes-
player_id
player_name
player_email
player_contact no
Entity-
organizer
Attributes-
org_id
org_name
org_email address
org_contact no
Entity-
Leader
Attributes-
4

Leader_id
Leader_name
Leader_contact no
Leader_email address
Entity-
Student
Attributes-
Student_id
Student_name
Student_contact no
Student_email address
Entity-
Staff
Attributes-
Staff_id
Staff_name
Staff_contact no
Staff_email
Entity-
Round
Attributes-
Round_id
Round_type
5

Entity-
Team
Attributes-
Team_id
Team_name
Entity-
Winner
Attributes
Team_id
Team_name
Entity – This is a real world object that performed different operations in database system
Attributes- attributes is representing the actual properties of particular entity in database system.
Normalization: - normalization is a technique which help for designing database tables and
organize into proper manner. This technique is also useful for reduce the data dependencies and reduce
redundancy data.
1NF(Normal Form) :
 Each and Every table contain single value in database tables.
 Each record need to be unique and specific.
6

s.
This table shows 1NF normal form because this database table contain all information of
staff and they contain unique and single value
2NF(Normal Form):
 Single column has primary key in tables.
 1 NF forms.
3NF(Normal Form):
 This normal form doesn’t have any transitive functional dependencies.
7


8


Both tables contain match_id, it means that could modify to shows only match, player
information in 3rd tables.
Match_id can be shown as a functional data dependency in the database tables.
9

Part 2
Database Implementations:
Match table
Figure 2.1
Figure: 2.2
10

Figure 2.3
11

Organizer table
Figure: 2.4
Player table
Figure: 2.5
12

Round table
Figure: 2.6
13

Staff table
Figure :2.7
14

Student table
Figure: 2.8
Team table
Figure : 2.9
15

Part 3
1). To show the winner of each game in each round
16

2). To sort all teams according to their scores (ascending)
17

3). To provide a report for a referee with all the games he served
18

4). To provide a list of the loser teams
19

5). To provide a list of all players in all teams
20

6). Game scores after each round, including section scores
21

7). The total number of games that each team played in a match
22

8).The total scores of each team after a match
23

9). The winner list of all recorded matches
24

10). Referees ranking, referee with best performance at all matches
25

CONCLUSION
As above report, successfully design an effective and efficient database system of match
that store all information in proper manner.
26