NIT1201 The Foreign Keys
The database needs to keep a record of:
• All team information, including players’ information
• All games, sections, the players involved and the scores
• The winner team of each game
• The teams play in each round
• The match winner team
• The ranking of the teams of each year’s match
• The referee assigned for each game and their performance score
Further, it should be possible to generate a report on:
• Game scores after each round, including section scores
• The total number of games that each team played in a match
• The total scores of each team after a match
• The ranking list of teams after a match
• The winner list of all recorded matches
• Referees ranking, referee with best performance at all matches
Answer:
ID - used to identify the primary keys and the foreign keys in the tables. For instance Match_ID, Round_ID, Ref_ID and Player_ID are both primary keys and foreign keys in different tables.
Round – is the game level or cycle. The Tournament has four levels which translate to four rounds.
Role – is found in the player table. There are only three roles a player can partake in a team; leadership, being a primary player and being a substitute player.
Score – Found in the playerscorepersection table. It represents the number of goals a player has achieved in a given section of the game.
Scores – found in both the section and match tables. In the section table it represents the scores for each team during the section whereas in the match table, it represents the goals each team has achieved in the particular match.
Scale/Ranking – is the measure of a referee’s performance in every match that the referee was administering or upholding the tennis rules.
Section. A match has three or two sections depending on the scores. If a team wins the first two sections consecutively then the match ends up being with two sections. Otherwise the match has three sections.
A game is either a single game or a pair game.
Single game – is played by four players singularly against the rival four players singularly. Meaning, there are four games in a game of type single.
Pair game – is played by pair of players against the rival pair of players. Meaning, there are two games in a game of type pair.
Match – consists of three sections depending on the scores. If a team wins the first two sections consecutively then the match ends up being with two sections
. Otherwise the match has three sections.
1.4 Database Design and Table structures
The database design is with reference to Teorey, Lightstone, Nadeau and Jagadish (2011).
|
Table |
Attributes |
|
Player |
Player_ID(Primary key, Auto_increment) PlayerName(varChar(255) ) Email address(varChar(255)) Role(varChar(255)) TeamID(int(10) Foreign key) |
|
Team |
TeamID(Primary key, Auto_increment) TeamName(varChar(255)) |
|
Referee |
Ref_ID(Primary key, Auto_increment) RefereeName(varChar(255) ) MatchID(Foreign key) |
|
RefereeRanking |
Ref_ID(Primary key) Ranking(int(2) ) MatchID(int(10) Foreign key) RoundID(int(10) Foreign key) |
|
Round |
RoundID(Primary key, Auto_increment) RoundName(varChar(255) ) |
|
Match |
MatchID(Primary Key, Auto_increment) MatchName(varChar(255) ) RoundID(int(10) Foreign key) |
|
Section |
SectionID(Primary key, Auto_increment) SectionName(varChar(255) ) MatchID(int(10) Foreign key) |
|
Game |
GameID(Primary key, Auto_increment) GameName(varChar(255) ) Number(int(10)) MatchID(int(10) Foreign key) |
|
PlayerScoreperSection |
Player_ID(int(10) Foreign key) Scores(int(10) ) SectionID(int(10) Foreign key) |
Player table
|
Player_ID |
Name |
Email address |
Role |
Team |
|
12 |
Jayson Stockman |
Leader Primary Player |
Team A | |
|
14 |
Manuel Garry |
Primary Player |
Team B |
Team table
|
TeamID |
TeamName |
Number of players |
|
112 |
Team A |
7 |
|
113 |
Team B |
5 |
Referee table
|
Ref_ID |
RefereeName |
Match |
|
1114 |
Jude Lawson |
Team A vs Team B |
|
1115 |
Charles Barbage |
Team C vs Team D |
Round Referee Ranking table
|
Round_Name |
Referee_Name |
Ranking |
|
Round 1 |
Jude Lawson |
10 |
|
Round 2 |
Charles Barbage |
7 |
Match Table
|
Match_ID |
MatchName |
|
11112 |
Team A vs Team B |
|
11113 |
Team C vs Team D |
Game table
|
GameID |
GameName |
Number |
MatchName |
|
1111112 |
Single Game |
4 |
Team A vs Team B |
|
1111113 |
Pair Game |
2 |
Team C vs Team D |
Section Table
|
SectionID |
SectionName |
MatchName |
|
111112 |
Section one |
Team A vs Team B |
|
111113 |
Section two |
Team C vs Team D |
Round table
|
RoundID |
RoundName |
|
1 |
Round one |
|
2 |
Round two |
PlayerScoreSection
|
SectionName |
PlayerName |
Scores |
|
Section one |
Jayson Stockman |
20 |
|
Section two |
Manuel Garry |
12 |
The following table is not in 1st Normal form because one of its records contains data which is not of atomic value.
|
Player_ID |
Name |
Email address |
Role |
Team |
|
12 |
Jayson Stockman |
Leader Primary Player |
Team A | |
|
14 |
Manuel Garry |
Primary Player |
Team B |
In 1st Normal form the table should be as follows
|
Player_ID |
Name |
Email address |
Role |
Team |
|
12 |
Jayson Stockman |
Leader
|
Team A | |
|
12 |
Jayson Stockman |
Primary Player |
Team A | |
|
14 |
Manuel Garry |
Primary Player |
Team B |
The following tables are not in second normal form because they contain multiple functional dependencies Elmasri and Navathe (2010)For tables to be in third normal form they must first be in second normal form and do not have transitive functional dependencies in the fields Thalheim (2013).
The following tables still have transitive functional dependencies and therefore are not in third Normal Form Above tables in Third Normal Form
1 Creation of the database and tables
CREATE DATABASE TENNIS TOURNAMENT;
CREATE TABLE PLAYER(
Player_ID int(10) NOT NULL AUTO_INCREMENT,
PlayerName varChar(255) NOT NULL,
Email_Address varChar(255) NOT NULL,
Role varChar(255) NOT NULL,
Team_ID int(10) NOT NULL,
PRIMARY KEY(Player_ID),
FOREIGN KEY(Team_ID) REFERENCES TEAM(Team_ID)
CREATE TABLE TEAM(
Team_ID int(10) NOT NULL AUTO_INCREMENT,
TeamName varChar(255) NOT NULL,
PRIMARY KEY(Team_ID)
CREATE TABLE REFEREE(
Ref_ID int(10) NOT NULL AUTO_INCREMENT,
RefereeName varChar(255) NOT NULL,
MatchID int(10) NOT NULL,
PRIMARY KEY(Ref_ID),
FOREIGN KEY(MatchID) REFERENCES MATCH(MatchID)
CREATE TABLE REFEREERANKING(
Ref_ID int(10) NOT NULL,
Ranking int(2) NOT NULL,
MatchID int(10) NOT NULL,
RoundID int(10) NOT NULL,
FOREIGN KEY(Ref_ID) REFERENCES REFEREE(Ref_ID),
FOREIGN KEY(MatchID) REFERENCES MATCH(MatchID),
FOREIGN KEY(RoundID) REFERENCES ROUND(RoundID)
CREATE TABLE ROUND(
RoundID int(10) NOT NULL AUTO_INCREMENT,
RoundName varChar(255) NOT NULL,
PRIMARY KEY(RoundID)
CREATE TABLE MATCH(
MatchID int(10) NOT NULL AUTO_INCREMENT,
MatchName varChar(255) NOT NULL,
Scores varChar(255) NOT NULL,
Winner varChar(255) NOT NULL,
Loser varChar(255) NOT NULL,
RoundID int(10) NOT NULL,
PRIMARY KEY(MatchID),
FOREIGN KEY(RoundID) REFERENCES ROUND(RoundID));
CREATE TABLE SECTION(
SectionID int(10) NOT NULL AUTO_INCREMENT,
SectionName varChar(255) NOT NULL,
Scores varChar(255) NOT NUll,
MatchID int(10) NOT NULL,
PRIMARY KEY(SectionID),
FOREIGN KEY(MatchID) REFERENCES MATCH(MatchID)
CREATE TABLE GAME(
GameID int(10) NOT NULL AUTO_INCREMENT,
GameName varChar(255) NOT NULL,
MatchID int(10) NOT NULL,
PRIMARY KEY(GameID),
FOREIGN KEY(MatchID) REFERENCES MATCH(MatchID)
CREATE TABLE PLAYERSCOREPERSECTION(
Player_ID int(10) NOT NULL,
Score int(10) NOT NULL
Population of the database
INSERT INTO TEAM(Team_ID, TeamName)
VALUES ('','Team one');
INSERT INTO TEAM(Team_ID, TeamName)
VALUES ('','Team Two');
INSERT INTO TEAM(Team_ID, TeamName)
VALUES ('','Team Three');
INSERT INTO TEAM(Team_ID, TeamName)
VALUES ('','Team Four');
INSERT INTO TEAM(Team_ID, TeamName)
VALUES ('','Team Five');
INSERT INTO TEAM(Team_ID, TeamName)
VALUES ('','Team Six');
INSERT INTO TEAM(Team_ID, TeamName)
VALUES ('','Team Seven');
INSERT INTO TEAM(Team_ID, TeamName)
VALUES ('','Team Eight');
INSERT INTO TEAM(Team_ID, TeamName)
VALUES ('','Team Nine');
INSERT INTO TEAM(Team_ID, TeamName)
VALUES ('','Team Ten');
INSERT INTO PLAYER(Player_ID, PlayerName, Email_Address, Role, Team_ID)
VALUES('','Jude Lawson','[email protected]','Leader','1');
INSERT INTO PLAYER(Player_ID, PlayerName, Email_Address, Role, Team_ID)
VALUES('','Shania Twayne','[email protected]','Primary Player','1');
INSERT INTO PLAYER(Player_ID, PlayerName, Email_Address, Role, Team_ID)
VALUES('','Aborigine Stuart','[email protected]','Substitute Player','1');
INSERT INTO PLAYER(Player_ID, PlayerName, Email_Address, Role, Team_ID)
VALUES('','John Brown','[email protected]','Substitute Player','1');
INSERT INTO PLAYER(Player_ID, PlayerName, Email_Address, Role, Team_ID)
VALUES('','Alen Fred','[email protected]','Leader','2');
INSERT INTO PLAYER(Player_ID, PlayerName, Email_Address, Role, Team_ID)
VALUES('','Angeli Norman','[email protected]','Primary Player','2');
INSERT INTO PLAYER(Player_ID, PlayerName, Email_Address, Role, Team_ID)
VALUES('','Sparky Anderson','[email protected]','Substitute Player','2');
INSERT INTO PLAYER(Player_ID, PlayerName, Email_Address, Role, Team_ID)
VALUES('','Cecil Robert','[email protected]','Substitute Player','2');
INSERT INTO PLAYER(Player_ID, PlayerName, Email_Address, Role, Team_ID)
VALUES('','Charles Dickens','[email protected]','Leader','3');
INSERT INTO PLAYER(Player_ID, PlayerName, Email_Address, Role, Team_ID)
VALUES('','James Dobson','[email protected]','Primary Player','3');
INSERT INTO PLAYER(Player_ID, PlayerName, Email_Address, Role, Team_ID)
VALUES('','Eric Dolphy','[email protected]','Substitute Player','3');
INSERT INTO PLAYER(Player_ID, PlayerName, Email_Address, Role, Team_ID)
VALUES('','Ignacy Domeyko','[email protected]','Substitute Player','3');
INSERT INTO REFEREE (Ref_ID, RefereeName, Match_ID)
VALUES('','Julius Caesar','1');
INSERT INTO REFEREE (Ref_ID, RefereeName, Match_ID)
VALUES('','Michael Owen','2');
INSERT INTO REFEREE (Ref_ID, RefereeName, Match_ID)
VALUES('','Cornel Osmarn','3');
INSERT INTO REFEREE (Ref_ID, RefereeName, Match_ID)
VALUES('','Mohammed Abdulaziz','4');
INSERT INTO REFEREE (Ref_ID, RefereeName, Match_ID)
VALUES('','Smart Joker','5');
INSERT INTO REFEREE (Ref_ID, RefereeName, Match_ID)
VALUES('','Serena Williams','6');
INSERT INTO REFEREE (Ref_ID, RefereeName, Match_ID)
VALUES('','Paul Scloes','7');
INSERT INTO REFEREE (Ref_ID, RefereeName, Match_ID)
VALUES('','Thiery Henry','8');
INSERT INTO REFEREE (Ref_ID, RefereeName, Match_ID)
VALUES('','Liam Nayson','9');
INSERT INTO REFEREE (Ref_ID, RefereeName, Match_ID)
VALUES('','Dwayne Johnson','10');
INSERT INTO REFEREERANKING(Ref_ID, Ranking, Match_ID, Round_ID)
VALUES('1','5','1','1');
INSERT INTO REFEREERANKING(Ref_ID, Ranking, Match_ID, Round_ID)
VALUES('2','4','2','1');
INSERT INTO REFEREERANKING(Ref_ID, Ranking, Match_ID, Round_ID)
VALUES('3','2','3','1');
INSERT INTO REFEREERANKING(Ref_ID, Ranking, Match_ID, Round_ID)
VALUES('4','7','4','1');
INSERT INTO REFEREERANKING(Ref_ID, Ranking, Match_ID, Round_ID)
VALUES('5','6','5','1');
INSERT INTO REFEREERANKING(Ref_ID, Ranking, Match_ID, Round_ID)
VALUES('6','3','6','1');
INSERT INTO REFEREERANKING(Ref_ID, Ranking, Match_ID, Round_ID)
VALUES('7','1','7','1');
INSERT INTO REFEREERANKING(Ref_ID, Ranking, Match_ID, Round_ID)
VALUES('8','8','8','1');
INSERT INTO REFEREERANKING(Ref_ID, Ranking, Match_ID, Round_ID)
VALUES('9','10','9','1');
INSERT INTO REFEREERANKING(Ref_ID, Ranking, Match_ID, Round_ID)
VALUES('10','9','10','1');
INSERT INTO ROUND(Round_ID, RoundName)
VALUES('','Round One');
INSERT INTO ROUND(Round_ID, RoundName)
VALUES('','Round Two');
INSERT INTO ROUND(Round_ID, RoundName)
VALUES('','Round Three');
INSERT INTO ROUND(Round_ID, RoundName)
VALUES('','Round Four');
INSERT INTO MATCHT(Match_ID, MatchName, Scores, Winner, Loser, Round_ID)
VALUES('','Team one vs Team two','8-22','Team Two','Team one','1');
INSERT INTO MATCHT(Match_ID, MatchName, Scores, Winner, Loser, Round_ID)
VALUES('','Team Three vs Team Four','28-15','Team Three','Team Four','1');
INSERT INTO MATCHT(Match_ID, MatchName, Scores, Winner, Loser, Round_ID)
VALUES('','Team Five vs Team Six','15-22','Team Six','Team Five','1');
INSERT INTO MATCHT(Match_ID, MatchName, Scores, Winner, Loser, Round_ID)
VALUES('','Team Seven vs Team Eight','28-14','Team Seven','Team Eight','1');
INSERT INTO MATCHT(Match_ID, MatchName, Scores, Winner, Loser, Round_ID)
VALUES('','Team Nine vs Team Ten','13-22','Team Ten','Team Nine','1');
INSERT INTO MATCHT(Match_ID, MatchName, Scores, Winner, Loser, Round_ID)
VALUES('','Team Eleven vs Team Twelve','20-30','Team Twelve','Team Eleven','1');
INSERT INTO MATCHT(Match_ID, MatchName, Scores, Winner, Loser, Round_ID)
VALUES('','Team Thirteen vs Team Fourteen','16-32','Team Fourteen','Team Thirteen','1');
INSERT INTO MATCHT(Match_ID, MatchName, Scores, Winner, Loser, Round_ID)
VALUES('','Team Fifteen vs Team Sixteen','19-22','Team Sixteen','Team Fifteen','1');
INSERT INTO MATCHT(Match_ID, MatchName, Scores, Winner, Loser, Round_ID)
VALUES('','Team Seventeen vs Team Eighteen','30-24','Team Seventeen','Team Eighteen','1');
INSERT INTO MATCHT(Match_ID, MatchName, Scores, Winner, Loser, Round_ID)
VALUES('','Team Nineteen vs Team Twenty','25-32','Team Twenty','Team Nineteen','1');
INSERT INTO SECTION(Section_ID, SectionName, Scores, Match_ID)
VALUES('','Section One','4-11','1');
INSERT INTO SECTION(Section_ID, SectionName, Scores, Match_ID)
VALUES('','Section Two','4-11','1');
INSERT INTO SECTION(Section_ID, SectionName, Scores, Match_ID)
VALUES('','Section One','6-11','2');
INSERT INTO SECTION(Section_ID, SectionName, Scores, Match_ID)
VALUES('','Section Two','11-4','2');
INSERT INTO SECTION(Section_ID, SectionName, Scores, Match_ID)
VALUES('','Section Three','11-0','2');
INSERT INTO SECTION(Section_ID, SectionName, Scores, Match_ID)
VALUES('','Section One','10-11','3');
INSERT INTO SECTION(Section_ID, SectionName, Scores, Match_ID)
VALUES('','Section One','5-11','3');
INSERT INTO SECTION(Section_ID, SectionName, Scores, Match_ID)
VALUES('','Section One','6-11','4');
INSERT INTO SECTION(Section_ID, SectionName, Scores, Match_ID)
VALUES('','Section One','11-3','4');
INSERT INTO SECTION(Section_ID, SectionName, Scores, Match_ID)
VALUES('','Section One','11-0','4');
INSERT INTO GAME(Game_ID, GameName, Match_ID, Number)
VALUES('','Single Game','1','4');
INSERT INTO GAME(Game_ID, GameName, Match_ID, Number)
VALUES('','Pair Game','1','2');
INSERT INTO GAME(Game_ID, GameName, Match_ID, Number)
VALUES('','Single Game','2','4');
INSERT INTO GAME(Game_ID, GameName, Match_ID, Number)
VALUES('','Pair Game','2','2');
INSERT INTO GAME(Game_ID, GameName, Match_ID, Number)
VALUES('','Single Game','3','4');
INSERT INTO GAME(Game_ID, GameName, Match_ID, Number)
VALUES('','Pair Game','3','2');
INSERT INTO GAME(Game_ID, GameName, Match_ID, Number)
VALUES('','Single Game','4','4');
INSERT INTO GAME(Game_ID, GameName, Match_ID, Number)
VALUES('','Pair Game','4','2');
INSERT INTO GAME(Game_ID, GameName, Match_ID, Number)
VALUES('','Single Game','5','4');
INSERT INTO GAME(Game_ID, GameName, Match_ID, Number)
VALUES('','Pair Game','5','2');
INSERT INTO PLAYERSCOREPERSECTION(Player_ID, Score, Section_ID)
VALUES('5','10','1');
INSERT INTO PLAYERSCOREPERSECTION(Player_ID, Score, Section_ID)
VALUES('6','1','1');
INSERT INTO PLAYERSCOREPERSECTION(Player_ID, Score, Section_ID)
VALUES('2','4','1');
INSERT INTO PLAYERSCOREPERSECTION(Player_ID, Score, Section_ID)
VALUES('7','5','2');
INSERT INTO PLAYERSCOREPERSECTION(Player_ID, Score, Section_ID)
VALUES('8','6','2');
INSERT INTO PLAYERSCOREPERSECTION(Player_ID, Score, Section_ID)
VALUES('1','4','2');
INSERT INTO PLAYERSCOREPERSECTION(Player_ID, Score, Section_ID)
VALUES('13','4','3');
INSERT INTO PLAYERSCOREPERSECTION(Player_ID, Score, Section_ID)
VALUES('14','7','3');
INSERT INTO PLAYERSCOREPERSECTION(Player_ID, Score, Section_ID)
VALUES('9','6','3');
INSERT INTO PLAYERSCOREPERSECTION(Player_ID, Score, Section_ID)
VALUES('15','10','4');
1.6.2 SQL codes to create each of the reports specified
Select statements De La Riva, Suárez-Cabal and Tuya (2010)
SELECT MATCHT.MatchName, MATCHT.Winner, ROUND.RoundName
FROM MATCHT
INNER JOIN ROUND ON MATCHT.Round_ID = ROUND.Round_ID;
To sort all teams according to their scores (ascending)|
SELECT PLAYER.Team_ID, TEAM.TeamName, SUM(PLAYERSCOREPERSECTION.Score)
FROM (( PLAYER
INNER JOIN TEAM ON PLAYER.Team_ID = TEAM.Team_ID)
INNER JOIN PLAYERSCOREPERSECTION ON PLAYER.Player_ID = PLAYERSCOREPERSECTION.Player_ID)
ORDER BY SUM(PLAYERSCOREPERSECTION.Score) ASC;
To provide a report for a referee with all the games he served
SELECT REFEREE.Ref_ID, REFEREE.RefereeName, MATCHT.MatchName
FROM REFEREE
INNER JOIN MATCHT ON REFEREE.Match_ID = MATCHT.Match_ID;
To provide a list of the loser teams
SELECT Loser
FROM MATCHT;
To provide a list of all players in all teams
SELECT PlayerName
FROM PLAYER;
Game scores after each round, including section scores
SELECT MATCHT.MatchName, MATCHT.Scores, SECTION.Scores, ROUND.RoundName
FROM (( MATCHT
INNER JOIN SECTION ON MATCHT.Match_ID = SECTION.Match_ID)
INNER JOIN ROUND ON MATCHT.Round_ID = ROUND.Round_ID);
The total number of games that each team played in a match
SELECT MATCHT.MatchName, SUM(GAME.Number)
FROM MATCHT
INNER JOIN GAME ON MATCHT.Match_ID = GAME.Match_ID
WHERE MATCHT.Match_ID = '1';
The total scores of each team after a match
SELECT MatchName, Scores
FROM MATCHT;
The winner list of all recorded matches
SELECT MatchName, Winner
FROM MATCHT;
Referees ranking, referee with best performance at all matches
SELECT REFEREE.RefereeName, MAX(REFEREERANKING.Ranking)
FROM REFEREE
INNER JOIN REFEREERANKING ON REFEREE.Ref_ID = REFEREERANKING.Ref_ID;
3.0 Bibliography
Teorey, T.J., Lightstone, S.S., Nadeau, T. and Jagadish, H.V., (2011). Database modeling and design: logical design. [online]. 5th. Michigan: Elsevier.
Thalheim, B., (2013). Entity-relationship modeling: foundations of database technology. [online]. Illustrated. Bordeaux. Springer Science & Business Media.
Frantiska, J., (2018). Entity-Relationship Diagrams. In Visualization Tools for Learning Environment Development(pp. 21-30). Springer: Cham.
Elmasri, R. and Navathe, S., (2010). Fundamentals of database systems. [online]. 6th. U.S: Addison-Wesley Publishing Company.
Özsu, M.T. and Valduriez, P., (2011). Principles of distributed database systems. [online]. 3rd. Paris: Springer Science & Business Media.
De La Riva, C., Suárez-Cabal, M.J. and Tuya, J., (2010, May). Constraint-based test database generation for SQL queries. In Proceedings of the 5th Workshop on Automation of Software Test (pp. 67-74). ACM.
Buy NIT1201 The Foreign Keys Answers Online
Talk to our expert to get the help with NIT1201 The Foreign Keys Answers to complete your assessment on time and boost your grades now
The main aim/motive of the management assignment help services is to get connect with a greater number of students, and effectively help, and support them in getting completing their assignments the students also get find this a wonderful opportunity where they could effectively learn more about their topics, as the experts also have the best team members with them in which all the members effectively support each other to get complete their diploma assignments. They complete the assessments of the students in an appropriate manner and deliver them back to the students before the due date of the assignment so that the students could timely submit this, and can score higher marks. The experts of the assignment help services at urgenthomework.com are so much skilled, capable, talented, and experienced in their field of programming homework help writing assignments, so, for this, they can effectively write the best economics assignment help services.
