CQ High School Student Basketball Tournament

Question: Relational database implementationYou 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 can didate 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 tabl e. 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 integrityYou 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 cant 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 cant be null.For the table Earning: All fields must be entered. Prize must be not less than $100. Information requestsCreate 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 tournaments 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 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 MatchFROM Tournament INNER JOIN [Match] ON Match. Tournament ID=Tournament. Tournament IDGROUP BY Tournament. Title, Tournament. Start Date;2. SELECT Referee.FirstName, Referee.LastName, Referee.Phone, Count(Match.MatchID) AS NumRefFROM Referee INNER JOIN [Match] ON Referee.RefereeID = Match.RefereeIDGROUP BY Referee.FirstName, Referee.LastName, Referee.PhoneHAVING Count(Match.MatchID)5;3. SELECT TeamName, count(PlayedIn.MatchID) AS NumPlayedFROM Tournament, Team INNER JOIN PlayedIn ON Team.TeamID=PlayedIn.TeamIDGROUP BY Team.TeamName, Tournament.Title, PlayedIn.OutcomeHAVING Tournament.Title='2013 CQU Cup' and PlayedIn.Outcome='lost';4. SELECT distinct StadiumName, Street, City, State, PCode, FacilityDescription, count(Stadium_Match.MatchID) AS NumMatchesFROM [Match], Stadium INNER JOIN Stadium_Match ON Stadium_Match.StadiumID=Stadium.StadiumIDGROUP BY StadiumName, Street, City, State, PCode, FacilityDesc ription, Match.StartDateTimeHAVING year(Match.StartDateTime)= 2013;5. SELECT Team.TeamName, Team.FoundedDate, Coach.FullName, Earning.Prize, Tournament.TitleFROM (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.TeamIDWHERE Earning.Prize=(select max(Prize) from Earning);6. SELECT SponsorName, Amount AS TotalFROM Sponsor INNER JOIN Sponsoring ON Sponsor.SponsorID=Sponsoring.SponsorIDWHERE Amount10000; 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