Designing a Database for VU University Table Tennis Match

Running head: INTRODUCTION TO DATABASE SYSTEMS
INTRODUCTION TO DATABASE SYSTEMS
Name of the Student
Name of the University
Author Note

INTRODUCTION TO DATABASE SYSTEMS1
Table of Contents
About the case study........................................................................................................................2
(A)Business rules for the system.....................................................................................................2
(b) Entities along with the relationship in the system......................................................................3
(C) Characteristics of the system.....................................................................................................3
(d) ER diagram for the system.........................................................................................................4
(e)ERD model table.........................................................................................................................4
3NF dependency..............................................................................................................................6
Data dictionary.................................................................................................................................8
Write SQL commands...................................................................................................................10
Queries for the tables:....................................................................................................................13
Bibliography..................................................................................................................................20

INTRODUCTION TO DATABASE SYSTEMS2
About the case study
The main aim of the assignment is to design a database that will be able to meet the needs
of the organization and will ensure that each data has been integrated successfully within the
system. The assessment will focus on VU University that aims entirely on organizing the table
tennis match within the university and offers both staffs and students to participate in the match.
The focus of this assessment is to design a small database that will be able to record all the data
for the purpose of providing a better service towards the organization. The entire system will be
designed based on the requirements of the organizer. The report will highlight the necessary
business rules and will prepare the system accordingly.
(A)Business rules for the system
Business rule is referred to the set of rules that are being used for the purpose of
identifying the activities that are going to take place within the designed system. VU University
organizes a tennis match every year. The number of teams that participate in table tennis match
every year is 20 teams. Teams are formed by including students and staffs and ensures that each
team contains one staff at least who is being presented as team leader. This members are
responsible for communicating with the organizers and ensures that each data has been circulated
effectively between all team leaders. Each team contains 4 to 8 members including the leader.
Before registering the team for a match each team has been provided with a form so that they can
provide all the necessary data within the form. This form is further submitted towards the
respective organizer by the leader of each team before two weeks. Based on the team details
matches are being arranged. However this is essential to ensure that one person is playing for a
particular team and are not allowed to play for other teams. The game has been divided into three

INTRODUCTION TO DATABASE SYSTEMS3
section and each team gains 11 points with every win. The tournament has included 15 referees
and each performance of the referees are being recorded by the organization. The best referee is
being selected for the purpose off performing the semifinals.
(b) Entities along with the relationship in the system
Student (StudentID (pk), StudentName , StudentAddress, TeamID(fk), LeaderID(fk))
Staff (LeaderID (pk), StaffName, TeamID (fk), Staff_address)
Winner details (Winner_ID (pk), Team_ID (fk), Score, Yearofwinning)
Referee (RefereeID (pk), Ref_name, Ref_contact, Ref_name, Ref_score)
Result (Rank (pk), TeamID (fk), Team_Score)
Game details (ID (Pk), TeamID (fk), Score_Details, Round_number(fk))
Round details (Round_number(pk), Round_name, Round_time , RefereeID (pk))
(C) Characteristics of the system
The system has been developed with 8 entities so that it becomes easy to manage the
attributes associated with the system. Student entity has been developed for the purpose of
storing all the details of the student that are participating within the match. Beside this the winner
details have been developed for identifying the winner in each case and for each match. Result
entity has been developed for displaying each match result and it becomes essential to have each
team data linked so that the results can be calculated based on the matches played by each team.
Game details are been used for the purpose of managing the games and rounds that are been

INTRODUCTION TO DATABASE SYSTEMS4
included within the system. each entities are linked with each other and it becomes essential to
include data in each table for calculating the data.
(d) ER diagram for the system
(e)ERD model table
Entities Attributes
Student StudentID
StudentName
StudentAddress

INTRODUCTION TO DATABASE SYSTEMS5
TeamID
LeaderID
Staff LeaderID
StaffName
TeamID
Staff_address
Team details TeamID
TeamName
LeaderID
ID
Members
Rank
Winner details Winner_ID
Team_ID
Score
Yearofwinning
Referee RefereeID
Ref_name
Ref_contact
Ref_name
Ref_score
Result Rank