Securing Higher Grades Costing Your Pocket? Book Your Coursework Help at The Lowest Price Now!

  • +1-617-874-1011 (US)
  • +44-117-230-1145 (UK)
Online Customer Service

NIT1201 The Foreign Keys

You have been commissioned to develop a database system that is capable of keeping records for FU’s table tennis matches from now on.
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

[email protected]

Leader

Primary Player

Team A

14

Manuel Garry

[email protected]

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

[email protected]

Leader

Primary Player

Team A

14

Manuel Garry

[email protected]

Primary Player

Team B

In 1st Normal form the table should be as follows

Player_ID

Name

Email address

Role

Team

12

Jayson Stockman

[email protected]

Leader

 

Team A

12

Jayson Stockman

[email protected]

Primary Player

Team A

14

Manuel Garry

[email protected]

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.

This problem has been solved.


Please send an Email to help@urgenthomework.com with the Payment ID and the link to the document to collect by email


Cite This work.

To export a reference to this article please select a referencing stye below.

Urgent Homework (2022) . Retrive from https://www.urgenthomework.com/sample-homework/nit1201-the-foreign-keys

"." Urgent Homework ,2022, https://www.urgenthomework.com/sample-homework/nit1201-the-foreign-keys

Urgent Homework (2022) . Available from: https://www.urgenthomework.com/sample-homework/nit1201-the-foreign-keys

[Accessed 03/10/2022].

Urgent Homework . ''(Urgent Homework ,2022) https://www.urgenthomework.com/sample-homework/nit1201-the-foreign-keys accessed 03/10/2022.


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.

Get Online Support for NIT1201 The Foreign Keys Assignment Help Online

Resources

    • 24 x 7 Availability.
    • Trained and Certified Experts.
    • Deadline Guaranteed.
    • Plagiarism Free.
    • Privacy Guaranteed.
    • Free download.
    • Online help for all project.
    • Homework Help Services

Tap to Chat
Get Instant Assignment Help
Tap to Chat
Get Instant Assignment Help