Sports Tournaments And Functional Dependencies In Two Relations

Details of Sports Tournaments

Question:

Relational database implementation

You must implement the entity relationship model provided in the model solution to Assignment 1. The model solution will be released on the course website approximately TWO (2) weeks after the Assignment 1 due date.

The recommended tool for this assignment is Microsoft Access, version 2003 or later. One reason for recommending Microsoft Access is that your course lecturer will be able to provide support in the use of this tool. If you choose to use a different DBMS, then there is no guarantee that your lecturer will be able to provide support. Note: If you choose not to use Microsoft Access, you must contact your lead lecturer as soon as possible before you start work on the assignment. The remainder of this document assumes you are using Access.

In implementing the ERD provided, you must complete the following tasks:
 
•    Map the ERD into a set of relations in at least Third Normal Form (3NF). You must ensure that your relations meet 3NF. There is no need to show your working.

•    Select any two (2) of your relations from the previous step and for each of them:

•    List all the functional dependencies present in the relation

•    Demonstrate that the relation meets Third Normal Form (3NF), for example: STUDENT (StudentID, Name, Address, DateOfBirth)

The primary key is StudentID; this is the only candidate key since Name is not guaranteed to be unique (two different students may have the same name). Address is also not guaranteed to be unique. There will be one student name per Student ID, since only one name is recorded for each student. There will be one address per Student ID; even though a student may have multiple address (e.g. a home address and a work address), the case study states that there is only a need to record the home address. There will be only one date of birth per Student ID. This means that Student ID functionally determines every other attribute in the table; therefore there are no repeating groups. There is a single simple candidate key, therefore no partial dependencies are possible. Name cannot be used to functionally determine any other attribute in the table since two different students may have the same name; likewise for address and date of birth, therefore there are no transitive dependencies in the table. Therefore it meets the requirements of first, second and third normal form.

•    Create all the relations in a Microsoft Access database. Consider each attribute in every table and make appropriate choices regarding data types and sizes, indexes, required/not required, and validation rules. Your choices should be appropriate for each attribute and should support data integrity. (Note: see the Data Integrity section below for specific data integrity requirements.)

•    Create relationships as appropriate. Enforce referential integrity for all appropriate relationships in the database. Explore the possibility of cascade update and/or delete options.

•    Review the default indexes created by Access for each table. You should ensure that the indexes follow the guidelines given in the set textbook.

•    Populate the database with sample data. You must include sufficient sample data to test your queries and report(s). You should aim for at least 5 or 6 rows in every table. Please note that the expected result of query questions may depend on the actual sample data populated.

Data integrity

You are required to implement integrity constraints within the database to enforce the following requirements:

For the table Coach:

•    All fields must be entered.

•    BlueCard number must be an 6-digit number.

•    Contact phone can’t be null.

For the table PlayedIn:

•    All fields must be entered.

•    The only valid values to indicate the match outcome must be “won”, “lost”, or “draw”.

•    Score can’t be null.
 
For the table Earning:

•    All fields must be entered.

•    Prize must be not less than $100.

SQL Queries for Sports Tournaments

Information requests

Create queries to answer the following information requests. Note: Do not use the Access query builder (QBE) to create your queries – you should type the queries manually using SQL view.

Although it is optional to justify the effectiveness of your SQL statement for the request, the marking process of MS Access queries for information requests may also consider your justification of the effectiveness for your queries in the implementation report if you provide them. (Expected results using sample data for queries depend on your actual sample data populated in the database . The following results of queries are only examples. It is important that your implementation must reflect the correct business logic.)

1. How many matches did each tournament have? Show the tournament’s title and start date, as well as the number of matches of each tournament.

Title		StartDate	NumOfMatch
2011 Challenge Cup	1/03/2011	2
2012 Telstra Cup	2/01/2012	2
2013 CQU Cup	5/01/2013	11

2. Which referee has umpired more than 5 matches? Show the referee’ name, contact phone and the number of times he/she has umpired.

Q2- Query

FirstNameLastName Phone NumRef

Grace

Duke

4908798

6

3. Which team never won any matches in the tournament titled “2013 CQU Cup”? List the team name and the number of matches played.

Q3- Query 

Team Name	Num Played
Glenmore	3
Helios	4
NorthStar	2

4. John would like to know the efficiency of each stadium used in the tournament held in 2013. For each stadium, show the stadium name, address, facility description and the number of matches. Order the result in ascending.

Q4- Query

StadiumName	Street	City	State	PostCode	FacilityDes	NumMatches
Rocky Sport	17 Farm street	Rockhampton	QLD	4702	Various fields for different	2
Center					sports
Fun Sports Club	76 Lavdar ave.	Rockhampton	QLD	4700	Limited seat for viewer	2
Telstra Stadium	19 North Road	Rockhampton	QLD	4700	Rugby and basketball fields	3
CQ Basket	202 Harris road	Rockhampton	QLD	4701	Basketball field only	4
Center

5. Find out the details of the team which has won the highest prize so far. The details include the team name, founded date, the name of coach, the amount of prize and the tournament title for which the team won the highest prize.

Q5- Query

TeamName	FoundedDate	CoachName	Prize	Title
Leopard	10/11/2002	Kim Jackson	$5,000.00	2012 Telstra Cup

6. Find out the sponsor names who have sponsored the total amount more than $10,000 so far.

Q6- Query

SponsorName	Total
CQU	$11,000.00
Education Queensland	$11,000.00

Functional Dependencies in two Relations

Two relations Tournament and Match are being described here. Following is the structure of Tournament table-

Tournament (TournamentID, Title, StartDate)

Title à TournamentID

There is possibility that two tournaments can have same title, therefore title functionally depends upon TournamentID.

StartDate à TournamentID

There is possibility that two tournaments start on same date, therefore StartDate functionally depends upon TournamentID.

Following is the structure of Match table-

Match (MatchID, StartDateTime, RefereeID, TournamentID)

StartDateTime à MatchID

There is possibility that two matches may start at same date time at different stadium, therefore StartDateTime functionally depends upon MatchID.

RefereeID à MatchID

There is possibility that the referee works for different matches, therefore RefereeID functionally depends upon MatchID.

TournamentID à MatchID

There may be more matches in one tournament; therefore TournamentID functionally depends upon MatchID.

(Janalta Interactive Inc. 2015)

Demonstration of Normalisation in two Relations

All relations in the database are in 3 NF form because all are following the rules of 3 NF form. The third normalization form is satisfied in the relations in the database by following the following rules-

1.    There is not any column which is duplicate in any table.
2.    There are different tables related to different data.
3.    There is relation in all related tables.
4.    All the columns depend upon the primary key.

The Tournament and the Match relations will demonstrate the 3 NF form –

The table Tournament has the primary key ‘TournamentID’ and all the fields depend upon the ‘TournamentID’. It uniquely identifies the record in the relation. Every field except ‘TournamentID’ cannot be unique in the Tournament table. Therefore there is only one candidate key and there is no partial dependency in the relation and the relation ‘Match’ references the ‘TournamentID’ of the relation ‘Tournament’. It enforces the referential integrity for the relation ‘Tournament’.

The relation ‘Match’ has the primary key ‘MatchID’. All the other attributes are dependent upon the ‘MatchID’. Every field except ‘MatchID’ cannot be unique in the Match table. Therefore there is only one candidate key and there is no partial dependency in the relation.

All the other relations are also satisfying the above 3 NF form rules as per the above two relations. (teach-ict.com n.d.)

(About.com 2015)

1. SELECT Tournament. Title, Tournament. Start Date, count (MatchID) AS Num Of Match
FROM Tournament INNER JOIN [Match] ON Match. Tournament ID=Tournament. Tournament ID
GROUP BY Tournament. Title, Tournament. Start Date;

2. SELECT Referee.FirstName, Referee.LastName, Referee.Phone, Count(Match.MatchID) AS NumRef
FROM Referee INNER JOIN [Match] ON Referee.RefereeID = Match.RefereeID
GROUP BY Referee.FirstName, Referee.LastName, Referee.Phone
HAVING Count(Match.MatchID)>5;

3. SELECT TeamName, count(PlayedIn.MatchID) AS NumPlayed
FROM Tournament, Team INNER JOIN PlayedIn ON Team.TeamID=PlayedIn.TeamID
GROUP BY Team.TeamName, Tournament.Title, PlayedIn.Outcome
HAVING Tournament.Title=’2013 CQU Cup’ and PlayedIn.Outcome=’lost’;

4. SELECT distinct StadiumName, Street, City, State, PCode, FacilityDescription, count(Stadium_Match.MatchID) AS NumMatches
FROM [Match], Stadium INNER JOIN Stadium_Match ON Stadium_Match.StadiumID=Stadium.StadiumID
GROUP BY StadiumName, Street, City, State, PCode, FacilityDescription, Match.StartDateTime
HAVING year(Match.StartDateTime)= 2013;

5. SELECT Team.TeamName, Team.FoundedDate, Coach.FullName, Earning.Prize, Tournament.Title
FROM (Tournament INNER JOIN (Team INNER JOIN Earning ON Team.TeamID = Earning.TeamID) ON Tournament.TournamentID = Earning.TournamentID) INNER JOIN (Coach INNER JOIN Coach_Team ON Coach.CoachID = Coach_Team.CoachID) ON Team.TeamID = Coach_Team.TeamID
WHERE Earning.Prize=(select max(Prize) from Earning);

6. SELECT SponsorName, Amount AS Total
FROM Sponsor INNER JOIN Sponsoring ON Sponsor.SponsorID=Sponsoring.SponsorID
WHERE Amount>10000;

Some points are missing in the assignment. For example –

1.    The queries of outer joins are missing in the assignment.
2.    There is lack of complex queries in the assignment.
3.    There is lack of creating the forms in the assignment.

1.    The assignment is giving the complete working knowledge about database. After completing this assignment, I learn more about the reports, queries and integrity constraints of MS Access.

2.    The queries are very useful for general database. I am feeling very confident now in database.

3.    There are hints given for the report in the assignment, I found the right way to create the report by the given hints. Therefore I did not go in the wrong direction to create the report.

References

[1] Janalta Interactive Inc. 2015, Functional Dependency, Viewed on 15^th Jan 2015

<https://www.techopedia.com/definition/19504/functional-dependency>

 [2] About.com 2015, Third Normal Form (3NF), Viewed on 15^th Jan 2015

<https://databases.about.com/od/administration/l/bldef_3nf.htm>

 [3] teach-ict.com n.d., Third Normal Form, Viewed on 15^th Jan 2015

< https://www.teach-ict.com/as_a2_ict_new/ocr/AS_G061/315_database_concepts/normalisation/miniweb/pg9.htm>