Database Design and Table structure

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
Analysis and Design........................................................................................................................2
Business Rules.............................................................................................................................2
Entities and relationship..............................................................................................................3
ER Diagram.................................................................................................................................4
Data Dictionary............................................................................................................................5
Dependencies Analysis and Normalization...............................................................................10
Dependencies.........................................................................................................................10
Normalization........................................................................................................................12
Database Design and Table structure........................................................................................14
Implementations............................................................................................................................15
Tables.........................................................................................................................................15
Structure.....................................................................................................................................15
Data............................................................................................................................................21
SQL Code......................................................................................................................................27
Create and population................................................................................................................27
Queries.......................................................................................................................................38
Bibliography..................................................................................................................................44

INTRODUCTION TO DATABASE SYSTEMS2
Analysis and Design
Business Rules
Business rules of an information system represents the process of the system in normal
language. The rules can be identified from the work flow and the information of the table tennis
match. The business rules for the VU University are described below:
1. Staff records are stored and maintained in the system identified by staff id.
2. Student’s record are stored and maintained in the system identified by student id.
3. Each team can have zero or many students.
4. Each team is consisting of at least 1 staff member who will be the leader of the team.
5. The team will store the member’s name, contact, email address, role and staff and student
ID.
6. Each match is associated with one or two teams.
7. Each match is associated with one group.
8. Each group will have four teams.
9. Each match is associated with one and many scores.
10. Each round is associated with one or many teams.
11. Each referee is associated with one or many matches.
12. Each rank is associated with one and only one team.
13. Each match has multiple games (single and pair)
14. Each games has 3 section of scoring.
15. Each game is associated with one or many game scores.

INTRODUCTION TO DATABASE SYSTEMS3
Entities and relationship
The Entities are representation of the real-world objects in the system design to
understand the functionality of the system. The Identified entities are the Students, Staffs, teams,
groups, rounds, team_members, referees, gamescores, games etc. The details of the entities are
described below:
 Students: It will store the student’s information identified by the student id. The student
consists the team with a leader from the staff.
 Staffs: All staff details are stored identified by the staff id. At least one staff is a member
who can be the leader of the team.
 Teams: Each team details will be stored. Single team will have 4 to 8 members.
 Groups: every group’s details will be stored along with the team id. Each group will
have one or many teams.
 Rounds: every round’s details will be stored along with the macthid, team id and scored.
 Teammembers: Each members’ record will be saved from the student id and staff id
along with the role.
 Referees: referee details will be stored such as name, contact and email address.
 GameScores: scores of every match is stored along with the matchid and roundid.
 Games: it stores the game details or each match and their participant teams. Each game
will have 3 sections where games can be played between teams as single or pair.
 Matches: It records the match data and games present per match. Winning the match
depends on the section wins.

INTRODUCTION TO DATABASE SYSTEMS4
ER Diagram
Figure 1: Entity Relationship Diagram of the System
Source: created by author

INTRODUCTION TO DATABASE SYSTEMS5
Data Dictionary
Games
Column Type Null Default Links to Comments MIME
Gameid (Primary) varchar(10) No
MatchId varchar(10) No matches -> MatchId
Gametype varchar(50) No
gamescore
Column Type Null Default Links to Comments MIME
Gameid (Primary) varchar(10) No games -> Gameid
teamname (Primary) varchar(10) No team -> Teamname
section (Primary) int(11) No
score int(11) No
game_has_members
Column Type Null Default Links to Comments MIME

INTRODUCTION TO DATABASE SYSTEMS6
Gameid (Primary) varchar(10) No games -> Gameid
Memberid (Primary) varchar(10) No
teammembers ->
MemberID
groups
Column Type Null Default Links to Comments MIME
groupid (Primary) int(10) No
groupname varchar(50) No
group_has_teams
Column Type Null Default Links to Comments MIME
Groupid (Primary) int(10) No groups -> groupid
teamname (Primary) varchar(50) No team -> Teamname
matches
Column Type Null Default Links to Comments MIME

INTRODUCTION TO DATABASE SYSTEMS7
MatchId (Primary) varchar(10) No
RoundID int(10) No rounds -> RoundID
RefereeId varchar(10) No referee -> Refereeid
referee
Column Type Null Default Links to Comments MIME
Refereeid (Primary) varchar(10) No
performance int(10) No
rounds
Column Type Null Default Links to Comments MIME
RoundID (Primary) int(10) No
Roundname varchar(50) No
staff
Column Type Null Default Links to Comments MIME