2. Which referee has umpired more than 5 matches? Show the referee’ name, contact phone and the number of times he/she has umpired.
3. Which team never won any matches in the tournament titled “2013 CQU Cup”? List the team name and the number of matches played.
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.
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.
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
|
Answer:
Report on Basketball Tournament Database
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;
![]()
Limitations in the Assignment
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.
Learning during 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 15th Jan 2015
<https://www.techopedia.com/definition/19504/functional-dependency>
[2] About.com 2015, Third Normal Form (3NF), Viewed on 15th Jan 2015
<https://databases.about.com/od/administration/l/bldef_3nf.htm>
[3] teach-ict.com n.d., Third Normal Form, Viewed on 15th Jan 2015
< https://www.teach-ict.com/as_a2_ict_new/ocr/AS_G061/315_database_concepts/normalisation/miniweb/pg9.htm>