DBMS: Rules, Entities, Characteristics, Example Tables, Normalization

This document provides information about the rules of a tennis tournament, entities and relationships in a database management system, characteristics of entities, example tables for the ER model, and normalization to 3rd CNF.

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.

12DBMS
Part 2: Project Implementation
Database Create: CREATE DATABASE IF NOT EXISTS `tenis` DEFAULT
CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `tenis`;
Database Development and Data Insert:
CREATE TABLE `matches` (
`match_id` int(11) NOT NULL,
`first_team_id` int(11) NOT NULL,
`second_team_id` int(11) NOT NULL,
`winner_team` int(11) DEFAULT NULL,
`fixture_date` date NOT NULL,
`round` varchar(200) NOT NULL,
`group_name` varchar(200) NOT NULL,
`points` int(11) DEFAULT NULL
);
INSERT INTO `matches` (`match_id`, `first_team_id`, `second_team_id`,
`winner_team`, `fixture_date`, `round`, `group_name`, `points`) VALUES
(1, 1, 2, NULL, '2019-06-04', 'Round 1', 'Group A', NULL),
(2, 1, 3, NULL, '2019-06-05', 'Round 1', 'Group A', NULL),
(3, 1, 4, NULL, '2019-06-06', 'Round 1', 'Group A', NULL),
(4, 2, 1, NULL, '2019-06-01', 'Round 1', 'Group A', NULL),
(5, 2, 3, NULL, '2019-06-02', 'Round 1', 'Group A', NULL),
(6, 2, 3, NULL, '2019-06-03', 'Round 1', 'Group A', NULL),

13DBMS
(7, 3, 1, NULL, '2019-05-26', 'Round 1', 'Group A', NULL),
(8, 3, 2, NULL, '2019-05-27', 'Round 1', 'Group A', NULL),
(9, 3, 4, NULL, '2019-05-28', 'Round 1', 'Group A', NULL),
(10, 4, 1, NULL, '2019-05-29', 'Round 1', 'Group A', NULL),
(11, 4, 2, NULL, '2019-05-30', 'Round 1', 'Group A', NULL),
(12, 4, 3, NULL, '2019-05-31', 'Round 1', 'Group A', NULL);
CREATE TABLE `matchreferee` (
`referee_id` int(11) NOT NULL,
`match_id` int(11) NOT NULL,
`score` int(11) DEFAULT NULL
);
INSERT INTO `matchreferee` (`referee_id`, `match_id`, `score`) VALUES
(1, 1, NULL),
(1, 2, NULL),
(1, 3, NULL),
(2, 4, NULL),
(2, 5, NULL),

14DBMS
(2, 6, NULL),
(3, 7, NULL),
(3, 8, NULL),
(3, 9, NULL),
(4, 10, NULL),
(4, 11, NULL),
(4, 12, NULL);
CREATE TABLE `referee` (
`referee_id` int(11) NOT NULL,
`name` varchar(200) NOT NULL,
`contact_number` varchar(20) NOT NULL,
`email_address` varchar(200) NOT NULL
);
INSERT INTO `referee` (`referee_id`, `name`, `contact_number`, `email_address`)
VALUES

15DBMS
(1, 'Emma Krimper', '[phone]', '[e-mail]'),
(2, 'Sarah Syme', '[phone]', '[e-mail]'),
(3, 'Riley Groves', '[phone]', '[e-mail]'),
(4, 'Angus Flegg', '[phone]', '[e-mail]');
-- --------------------------------------------------------
CREATE TABLE `staff` (
`staff_id` int(11) NOT NULL,
`team_id` int(11) NOT NULL,
`name` varchar(200) NOT NULL,
`contact_number` varchar(20) NOT NULL,
`email_address` varchar(200) NOT NULL,
`role` varchar(200) NOT NULL
);

16DBMS
INSERT INTO `staff` (`staff_id`, `team_id`, `name`, `contact_number`,
`email_address`, `role`) VALUES
(1, 1, 'Eden Mead', '[phone]', '[e-mail]', 'Leader'),
(2, 2, 'Zane Hartog', '[phone]', '[e-mail]', 'Leader'),
(3, 3, 'Ebony Kepert', '[phone]', '[e-mail]', 'Leader'),
(4, 4, 'Benjamin Feldt', '[phone]', '[e-mail]', 'Leader');
CREATE TABLE `student` (
`student_id` int(11) NOT NULL,
`team_id` int(11) NOT NULL,
`name` varchar(200) NOT NULL,
`contact_number` varchar(20) NOT NULL,
`email_address` varchar(200) NOT NULL,
`role` varchar(200) NOT NULL
);
INSERT INTO `student` (`student_id`, `team_id`, `name`, `contact_number`,
`email_address`, `role`) VALUES

17DBMS
(1, 1, 'Alexandra Santo', '[phone]', '[e-mail]',
'Primary Player'),
(2, 2, 'Jai Oberg', '[phone]', '[e-mail]', 'Primary Player'),
(3, 3, 'Tyson Colleano', '[phone]', '[e-mail]', 'Primary
Player'),
(4, 4, 'George Boniwell', '[phone]', '[e-mail]', 'Primary
Player'),
(5, 1, 'Luke Glassey', '[phone]', '[e-mail]', 'Substitute'),
(6, 2, 'Rebecca Poynton', '[phone]', '[e-mail]',
'Substitute'),
(7, 3, 'Sara Cann', '[phone]', '[e-mail]', 'Substitute'),
(8, 4, 'Riley Crist', '[phone]', '[e-mail]', 'Substitute');
CREATE TABLE `team` (
`team_id` int(11) NOT NULL,
`team_name` varchar(200) NOT NULL,
`total_member` int(11) NOT NULL DEFAULT '0'
);
INSERT INTO `team` (`team_id`, `team_name`, `total_member`) VALUES
(1, 'Blasters', 3),
(2, 'Sunrisers', 3),

18DBMS
(3, 'Top Notch', 3),
(4, 'Warriors', 3);
ALTER TABLE `matches`
ADD PRIMARY KEY (`match_id`),
ADD KEY `first_team_id` (`first_team_id`),
ADD KEY `second_team_id` (`second_team_id`),
ADD KEY `winner_team` (`winner_team`);
ALTER TABLE `matchreferee`
ADD PRIMARY KEY (`match_id`,`referee_id`),
ADD KEY `matchreferee_ibfk_2` (`referee_id`);
ALTER TABLE `referee`

19DBMS
ADD PRIMARY KEY (`referee_id`);
ALTER TABLE `staff`
ADD PRIMARY KEY (`staff_id`),
ADD KEY `team_id` (`team_id`);
ALTER TABLE `student`
ADD PRIMARY KEY (`student_id`),
ADD KEY `team_id` (`team_id`);
ALTER TABLE `team`
ADD PRIMARY KEY (`team_id`);
ALTER TABLE `matches`
ADD CONSTRAINT `matches_ibfk_1` FOREIGN KEY (`first_team_id`)
REFERENCES `team` (`team_id`),
ADD CONSTRAINT `matches_ibfk_2` FOREIGN KEY (`second_team_id`)
REFERENCES `team` (`team_id`),
ADD CONSTRAINT `matches_ibfk_3` FOREIGN KEY (`winner_team`)
REFERENCES `team` (`team_id`);

20DBMS
ALTER TABLE `matchreferee`
ADD CONSTRAINT `matchreferee_ibfk_1` FOREIGN KEY (`match_id`)
REFERENCES `matches` (`match_id`) ON DELETE NO ACTION ON UPDATE
CASCADE,
ADD CONSTRAINT `matchreferee_ibfk_2` FOREIGN KEY (`referee_id`)
REFERENCES `referee` (`referee_id`) ON DELETE NO ACTION ON UPDATE
CASCADE;
ALTER TABLE `staff`
ADD CONSTRAINT `staff_ibfk_1` FOREIGN KEY (`team_id`) REFERENCES
`team` (`team_id`);

21DBMS
ALTER TABLE `student`
ADD CONSTRAINT `student_ibfk_1` FOREIGN KEY (`team_id`) REFERENCES
`team` (`team_id`);
COMMIT;
Trigger:
DELIMITER $$
CREATE TRIGGER `reduce_member2` AFTER DELETE ON `student` FOR EACH
ROW begin
DECLARE memer_number int;
SET memer_number = (SELECT team.total_member from team WHERE
team.team_id = old.team_id);
UPDATE team set team.total_member = (memer_number-1) where team.team_id =
old.team_id;
END
$$
DELIMITER ;

22DBMS
DELIMITER $$
CREATE TRIGGER `update_team_member2` BEFORE INSERT ON `student` FOR
EACH ROW begin
DECLARE memer_number int;
SET memer_number = (SELECT team.total_member from team WHERE
team.team_id = new.team_id);
UPDATE team set team.total_member = (memer_number+1) where team.team_id =
new.team_id;
END
$$
DELIMITER ;

23DBMS
DELIMITER $$
CREATE TRIGGER `reduce_member` AFTER DELETE ON `staff` FOR EACH
ROW begin
DECLARE memer_number int;
SET memer_number = (SELECT team.total_member from team WHERE
team.team_id = old.team_id);
UPDATE team set team.total_member = (memer_number-1) where team.team_id =
old.team_id;
END
$$
DELIMITER ;

24DBMS
DELIMITER $$
CREATE TRIGGER `update_team_member` AFTER INSERT ON `staff` FOR
EACH ROW begin
DECLARE memer_number int;
SET memer_number = (SELECT team.total_member from team WHERE
team.team_id = new.team_id);
UPDATE team set team.total_member = (memer_number+1) where team.team_id =
new.team_id;
END
$$
DELIMITER ;
Stored Procedure:
DELIMITER $$

25DBMS
CREATE DEFINER=`root`@`localhost` PROCEDURE `declare_winner` (IN
`match_no` INT, IN `team_win` INT, IN `score` INT) BEGIN
UPDATE matches Set points = score, winner_team = team_win WHERE match_id
= match_no;
END$$

26DBMS
CREATE DEFINER=`root`@`localhost` PROCEDURE `referee_score` (IN
`referee_no` INT, IN `match_no` INT, IN `point` INT) NO SQL
BEGIN
UPDATE matchreferee Set score = point WHERE match_id = match_no AND
referee_id = referee_no;
END$$

27DBMS
DELIMITER ;
Calling Procedure:
CALL referee_score(1, 1, 5);
CALL referee_score(1, 2, 8);
CALL referee_score(1, 3, 3);
CALL referee_score(2, 4, 6);
CALL referee_score(2, 5, 6);
CALL referee_score(2, 6, 7);

28DBMS
CALL referee_score(3, 7, 8);
CALL referee_score(3, 8, 4);
CALL referee_score(3, 9, 5);
CALL referee_score(4, 10, 8);
CALL referee_score(4, 11, 2);
CALL referee_score(4, 12, 4);
CALL declare_winner(1, 1, 7);
CALL declare_winner(2, 3, 5);
CALL declare_winner(3, 1, 3);
CALL declare_winner(4, 1, 7);
CALL declare_winner(5, 2, 7);
CALL declare_winner(6, 2, 3);
CALL declare_winner(7, 3, 5);

29DBMS
CALL declare_winner(8, 2, 7);
CALL declare_winner(9, 3, 6);
CALL declare_winner(10, 4, 7);
CALL declare_winner(11, 2, 2);
CALL declare_winner(12, 3, 5);
Queries:
Query 1: SELECT matches.match_id, team.team_name As `Winner Team`,
matches.round from team INNER JOIN matches ON team.team_id = matches.winner_team;

30DBMS
Query 2: SELECT team.team_name, SUM(matches.points) AS TotalScore from team
INNER JOIN matches ON team.team_id = matches.winner_team GROUP BY
team.team_name ORDER BY TotalScore ASC;
Query 3: SELECT referee.name, matchreferee.match_id, matchreferee.score FROM
referee INNER JOIN matchreferee ON referee.referee_id = matchreferee.referee_id where
referee.referee_id =1;
Query 4: Select team_name, SUM(TotalScore) As Score From (
SELECT team.team_name, SUM(matches.points) AS TotalScore FROM matches
INNER JOIN team on matches.first_team_id = team.team_id GROUP BY team.team_id
UNION ALL

31DBMS
SELECT team.team_name, SUM(matches.points) AS TotalScore FROM matches
INNER JOIN team on matches.second_team_id = team.team_id GROUP BY team.team_id)a
GROUP BY team_name ORDER BY Score ASC LIMIT 2
Query 5: SELECT student.name AS `Student Name`, staff.name AS `Staff Name`,
team_name FROM team INNER JOIN student ON team.team_id = student.team_id INNER
JOIN staff ON team.team_id = staff.team_id GROUP BY team_name;