NIT1201 Introduction to Database Systems: VU Table Tennis Project

This assignment focuses on designing a database for a table tennis tournament organized by VU University. The project encompasses various stages of database design, starting with the identification of business rules and entities, including students, staff, referees, and game details. An ER diagram visually represents the relationships between these entities. The solution details the characteristics of the system, including the attributes for each entity. The assignment also involves ensuring data integrity through 3NF normalization. It includes a data dictionary defining data types and keys for each attribute. The solution provides SQL commands for inserting data into the tables and demonstrates database querying through SQL SELECT statements to retrieve specific information, such as team scores, referee details, and game results. A bibliography of relevant database systems resources is also included.

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

1INTRODUCTION TO DATABASE SYSTEMS
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

2INTRODUCTION TO DATABASE SYSTEMS
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

3INTRODUCTION TO DATABASE SYSTEMS
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

4INTRODUCTION TO DATABASE SYSTEMS
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

5INTRODUCTION TO DATABASE SYSTEMS
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

6INTRODUCTION TO DATABASE SYSTEMS
TeamID
Team_Score
Game details ID
TeamID
Score_Details
Round_number
Round details Round_number
Round_name
Round_time
RefereeID
3NF dependency
Student (StudentID, StudentName , StudentAddress, TeamID, LeaderID)
The table is said to be in 3NF if it has the presence in 2nf and does not contains any
transitive partial dependency. In the above selected table it can be observed that the table does
not allow duplicate data. StudentID does not allow any duplicate value and ensures that all data
ate able to maintain proper integrity throughout the system. Thus it can be stated that it is in 3
normal form.
Staff (LeaderID, StaffName, TeamID, Staff_address)
The staff table is considered to be in the 3NF as it ensures better way of maintaining the
data within the system. The table does not show any transitive dependency and hence it is

7INTRODUCTION TO DATABASE SYSTEMS
considered to be in the 3NF. The table contains a unique attribute and ensures that no duplicate
value is entered within the data.
Team details (TeamID, TeamName, LeaderID, ID, Members, Rank)
The team details table is to be in 3NF because it does not show any transitive dependency
and each data stored within the table is unique and maintains proper integrity.
Winner details (Winner_ID, Team_ID, Score, Yearofwinning)
The winner details table is said to be in 3NF because it maintain better integrity
throughout the table. This also ensures that proper transitive dependency is maintained within the
system.
Referee (RefereeID, Ref_name, Ref_contact, Ref_name, Ref_score)
The referee table is said to be in 3NF as it has the ability to maintain integrity between
the data. This also ensures that each data maintained within the system has the potential to be
unique.
Result (Rank, TeamID, Team_Score)
The result is stated to be in 3NF as it does not maintain any transitive data and ensures all
data maintains integrity.
Game details (ID, TeamID, Score_Details, Round_number)
The game details is said to be in 3NF as it has the ability to provide better integrity
towards the data. Moreover there is no transitive dependency between the entities.
Round details (Round_number, Round_name, Round_time , RefereeID)

8INTRODUCTION TO DATABASE SYSTEMS
The round details is said to be in 3NF because its maintain integrity and does not allow
any duplicate data.
Data dictionary
Entities Attributes Data type Key
Student StudentID Number Primary key
StudentName Varchar
StudentAddress Varchar
TeamID Number Foreign key
LeaderID Varchar Foreign key
Staff LeaderID Number Primary key
StaffName Varchar
TeamID Number Foreign key
Staff_address Varchar
Team details TeamID Number Primary key
TeamName Varchar
LeaderID Number Foreign key
ID Number Foreign key
Members Varchar
Rank Number
Winner details Winner_ID Number Primary key
Team_ID Number Foreign key
Score Number

9INTRODUCTION TO DATABASE SYSTEMS
Yearofwinning Date
Referee RefereeID Number Primary key
Ref_name Varchar
Ref_contact Number
Ref_name Number
Ref_score Number
Result Rank Number Primary key
TeamID Number Foreign key
Team_Score Number
Game details ID Number Primary key
TeamID Number Foreign key
Score_Details Number
Round_number Number Foreign key
Round details Round_number Number Primary key
Round_name Varchar
Round_time Time
RefereeID Number Foreign key

10INTRODUCTION TO DATABASE SYSTEMS
Write SQL commands
INSERT INTO `student` (`StudentID`, `StudentName`, `StudentAddress`, `TeamID`,
`LeaderID`) VALUES ('101', 'Andre Louis ', 'California east ', '001 ', '201'), ('102', 'Suzzain
Chris', 'Red cross road ', '002', '202'), ('103', 'Christiano ', '6th Rusell Colony', '002', ' 202'), ('104',
'Ritz Henry ', 'Melbourne ', '001', '201'), ('105 ', 'Sitz Louis ', '45, Suzzane Strreet ', '003', '203'),
('106', 'Sizuka Nobi', 'Rowdown street ', '003', '203'), ('107', 'Henry Louis', '21, Bens colony',
'004', '204'), ('108', 'Alias', '43, North Colony ', '004', '204'), ('109', 'Robert James', 'Northern
Avenue ', '004', '204'), ('110', 'Robert Pattinson ', 'Southern Valley street', '001', '201');
Game details table created:

11INTRODUCTION TO DATABASE SYSTEMS
INSERT INTO `game details` (`ID`, `TeamID`, `Score_details`, `Round_number`) VALUES
('1', '1', '13000', '1'), ('2', '4', '13500', '1');
INSERT INTO `game details` (`ID`, `TeamID`, `Score_details`, `Round_number`) VALUES
('3', '2', '12000', '1'), ('4', '3', '11000', '1'), ('5', '4', '10000', '2'), ('6', '4', '7000', '3'), ('7', '2', '20000',
'4'), ('8', '4', '21000', '4'); INSERT INTO `game_details` (`ID`, `TeamID`, `Score_details`,
`Round_number`) VALUES ('9', '9', '2200', '4'), ('10', '3', '2400', '6');

12INTRODUCTION TO DATABASE SYSTEMS
INSERT INTO `rounddetails` (`Round_number`, `Round_name`, `round_time`, `Referee_ID`)
VALUES
(1, 'Show Down ', '2019-09-28 04:51:00', 1001),
(2, 'Face off ', '2019-09-26 02:00:00', 1002),
(3, 'Semi final ', '2019-10-24 02:00:00', 1001),
(4, 'Final', '2019-11-28 02:00:00', 1001),
(5, 'fire', '2019-10-03 00:00:00', 1004),
(6, 'Fire2', '2019-10-03 00:00:00', 1007),
(7, 'Fire3', '2019-10-04 00:00:00', 1007),
(8, 'Fire4', '2019-10-05 00:00:00', 1010),
(9, 'Hits1', '2019-10-06 00:00:00', 1003),
(10, 'Hits2', '2019-10-06 00:00:00', 1006);

13INTRODUCTION TO DATABASE SYSTEMS
INSERT INTO `referee` (`RefereeID`, `Ref_name`, `Ref_contact`, `Ref_score`) VALUES
(1001, 'Ben Louis', 637940021, 2000),
(1002, 'Jem Jaz', 637947821, 5000),

14INTRODUCTION TO DATABASE SYSTEMS
(1003, 'Vick john ', 637901365, 5000),
(1004, 'Derreck Stevens', 637940456, 3500),
(1005, 'Neymar', 637973256, 1000),
(1006, 'Ever Banega', 637944769, 4000),
(1007, 'Kevin Friend', 637943012, 2500),
(1008, 'Daily Blind', 637947860, 4000),
(1009, 'Freddy Stevens', 637941230, 6000),
(1010, 'Deffy Son', 637941203, 4000);
INSERT INTO `result` (`Rank`, `Team_ID`, `Team_Score`) VALUES
(1, 4, 21000),

15INTRODUCTION TO DATABASE SYSTEMS
(2, 2, 20000),
(3, 3, 19000),
(4, 1, 18700),
(5, 10, 18400),
(6, 6, 18300),
(7, 5, 18100),
(8, 9, 18050),
(9, 7, 18025),
(10, 8, 18000);

16INTRODUCTION TO DATABASE SYSTEMS
INSERT INTO `staff` (`LeaderID`, `Staffname`, `TeamID`, `Staff_address`) VALUES
(201, 'Asmi Rizaw', 1, 'Mellbourne'),
(202, 'Henry Louis', 2, '34, Capetown'),
(203, 'Ritcha Mathew', 3, '33, Mellbourne Street'),
(204, 'tony mick', 4, 'New mellbourne road'),
(205, 'Gary Williams', 1, '5 bg road'),
(206, 'Denson welliems', 4, 'down street'),
(207, 'Kelly Menting', 3, '8 Turd Street'),
(208, 'Halley Williams', 2, '90 PF End'),
(209, 'Dael Stones', 6, 'NA'),
(210, 'Uher Neil', 2, 'NA');

17INTRODUCTION TO DATABASE SYSTEMS
Queries for the tables:
3. SELECT rounddetails.Round_number, game_details.ID, team_details.TeamName
FROM rounddetails
INNER JOIN game_details ON rounddetails.Round_number = game_details.Round_number
INNER JOIN winner_details ON winner_details.RoundID = rounddetails.Round_number
INNER JOIN team_details ON team_details.TeamID = winner_details.Team_ID;

18INTRODUCTION TO DATABASE SYSTEMS
4. SELECT team_details.TeamName, SUM(game_details.Score_details)
FROM game_details
INNER JOIN team_details ON team_details.TeamID = game_details.TeamID
GROUP BY team_details.TeamName
ORDER BY SUM(game_details.Score_details) DESC;
5. SELECT referee.ref_name, rounddetails.Round_name

19INTRODUCTION TO DATABASE SYSTEMS
FROM referee
INNER JOIN rounddetails ON rounddetails.Referee_ID = referee.RefereeID
where referee.ref_name = "Ben Louis";
6. Select * from team_details
WHERE TeamID NOT IN (Select TeamID FROM winner_details);
The database has been developed in such a way that each game round will have a particular
winner. Hence there is no particular table to be displayed as looser table.
7. SELECT team_details.`TeamName`, student.StudentName
FROM team_details

20INTRODUCTION TO DATABASE SYSTEMS
INNER JOIN student ON student.TeamID = team_details.TeamID;
8. SELECT rounddetails.Round_number, `winner_details`.`Score`, game_details.Score_details
FROM rounddetails
INNER JOIN winner_details ON winner_details.RoundID = rounddetails.Round_number
INNER JOIN game_details ON game_details.Round_number = rounddetails.Round_number;

21INTRODUCTION TO DATABASE SYSTEMS
9. SELECT COUNT rounddetails.Round_number, rounddetails.Round_name,
COUNT(game_details.ID)
FROM rounddetails
INNER JOIN game_details ON rounddetails.Round_number = game_details.Round_number
GROUP BY rounddetails.Round_number, rounddetails.Round_name;
10. SELECT team_details.TeamName, game_details.Score_details
FROM game_details

22INTRODUCTION TO DATABASE SYSTEMS
INNER JOIN team_details ON game_details.TeamID = team_details.TeamID;
11. SELECT * FROM winner_details;
Query 12:
SELECT * FROM `referee` ORDER BY Ref_score DESC

23INTRODUCTION TO DATABASE SYSTEMS

24INTRODUCTION TO DATABASE SYSTEMS
Bibliography
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management.
Cengage Learning.
Stonebraker, M., Deng, D. and Brodie, M.L., 2017. Application-database co-evolution: A new
design and development paradigm. New England Database Day, pp.1-3.
Hilles, M.M. and Naser, S.S.A., 2017. Knowledge-based Intelligent Tutoring System for
Teaching Mongo Database.
Parks, R.F. and Hall, C., 2016. Front-End and Back-End Database Design and Development:
Scholar’s Academy Case Study. Information Systems Education Journal, 14(2), p.58.
Ferreira, A.C.D.M., Nascimento, L.M.E.D. and Gonzalez, S.D.A., 2017. Design and
development of a geo-referenced database to radionuclides in food.
Kralj, D. and Hren, N., 2018, January. Framework project of database development: Record
keeping of protective equipment. In 7. Međunarodni stručno-znanstveni skup" Zaštita na radu i
zaštita zdravlja"/7th International Professional and Scientific Conference" Occupational safety
and health".