INFS1200/7900 Information Systems Assignment 2 (10 Marks)In the second assignment the provided schema will be implemented in MySQL. The implementation will include creating tables, constraints, uploading or entering sample data, and writing queries. The details are given below in the implementation section. This part is done individually by all students.SubmissionSubmissions will be done via Blackboard. Marking will be done through an electronic marking tool called Gradescope, which will also be used for providing feedback.Your submission will contain 2 parts:Use the supplied “Assignment 2 Template” to answer all questions. Convert your answer file into PDF format and submit it via the “Assignment 2 – PDF File Submission” link.Use the export function in PHPMyAdmin to create a .SQL file of your database.Submit this file via the “Assignment 2 – SQL File Submission” link.ER SolutionProvided SchemaThe schema to be implemented for this part of the assignment is given below: COUNTRY[Name, Population]TOURNAMENT[Year, Country] TEAM[Year, ID, Country]MEMBER[ID, Name]HOMECLUB[Name, Country] SUPPORTSTAFF[ID, Role]PLAYER[ID, Position, HomeClubName]TEAMMEMBER[Year, TeamID, MemberID]MATCH[ID, Date, Time, Stadium, HomeYear, HomeTeamID, AwayYear, AwayTeamID] POOLGAME[ID]ELIMINATIONGAME[ID, Stage, HomePenalties, AwayPenalties] GOALS[PlayerID, MatchID, Count]SAVES[PlayerID, MatchID, Count]CUSTOMER[ID, Name, Email, CountryName] TICKET[MatchID, Ticket#, CustomerID, Price]Foreign Keys:TOURNAMENT.Country references COUNTRY.Name TEAM.Country references COUNTRY.Name TEAM.Year references TOURNAMENT.Year SUPPORTSTAFF.ID references MEMBER.IDHOMECLUB.Country references COUNTRY.Name PLAYER.ID references MEMBER.ID PLAYER.HomeClubName references HOMECLUB.Name TEAMMEMBER.(Year, TeamID) references TEAM.(Year, ID) TEAMMEMBER.MemberID references MEMBER.IDMATCH.(HomeYear, HomeTeamID) references Team.(Year, ID) MATCH.(AwayYear, AwayTeamID) references Team.(Year, ID) POOLGAME.ID references MATCH.IDELIMINATIONGAME.ID references MATCH.IDGOALS.PlayerID references PLAYER.ID GOALS.MatchID references MATCH.ID SAVES.PlayerID references PLAYER.ID SAVES.MatchID references MATCH.ID TICKET.CustomerID references CUSTOMER.ID TICKET.MatchID references Match.IDImplementation – PHPMyAdminThe following describe the scope and requirements of the implementation in PHPMyAdmin.Tables and ConstraintsCreate a database in PHPMyAdmin called WorldCupYou need to implement the schema above by creating the tables and constraints. Your database should enforce basic constraints, such as:Referential integrity. Multiple referential integrity constraints can be extracted from the specification.Domain. Attribute values are restricted to the allowed data types.Key and Entity integrity constraints.Semantic constraints if any/as given in the Universe of Discourse.Sample DataPopulate the database with enough meaningful sample data (at least 5 tuples per table) to allow us to test the functionality offered by your information system. This data can be made up or sourced from any websites you choose.ViewsWrite the following queries in SQL on your WorldCup database:Find all the Australian Players that played in the 2018 World CupFind the players that have never scored a goalFind all the pool games played by host countriesFind all the Australian players linked to an English club teamRank the teams by the number of support staff attending the 2018 World CupFind the names & number of goals saved by all playersFind the game that sold the most ticketsFind the player who scored the most goals in elimination gamesFind teams that have played in all World CupsFind players who have played in all of Iceland’s gamesNote: Be sure to have sample data that can generate ‘some’ result for these queries. You will not be assessed on the correctness of the data, but on the correctness of your query. You can build your queries progressively by building views on views.AssessmentAssignment 2 is allocated 100 points which are converted into 10 course marks.Basic Implementation (Tables, Data Types, Primary Keys) 30 Marks Foreign Keys 20 MarksQueries 5 Marks each 50 MarksThis assignment targets the following assessment criteria of this course:Analyse, extract and structure information system requirements from a variety of organizational contextsReason with the foundations of the relational data model to correctly undertake relational database designExpress queries using the SQL language to provide correct and secure retrievalof data from relational databasesConstruct a small-scale information system in a relational database management systemPerform information systems analysis and design in a group settingLate SubmissionsLate submissions will not be accepted at all, or if accepted, will incur a penalty of 20% reduction each date late. Students who believe they have sound reasons for late submission should refer to the course profile (section 5.3 on Late Submissions).CollaborationThe University has strict policies with regard to plagiarism. Penalties for engaging in unacceptable behaviour can range from cash fines or loss of grades in a course, through to expulsion from the University. You are required to read and understand the policies on academic integrity and plagiarism in the course profile (section 6.1).If you have any questions regarding acceptable level of collaboration with your peers, please see either the lecturer or your tutor for guidance. Remember that ignorance is not a defence!
