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.

1. Submission

   Submissions 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:

   1. 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.

   2. 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.

2. ER Solution

3. Provided Schema

   The 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.ID

   HOMECLUB.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.ID

   MATCH.(HomeYear, HomeTeamID) references Team.(Year, ID) MATCH.(AwayYear, AwayTeamID) references Team.(Year, ID) POOLGAME.ID references MATCH.ID

   ELIMINATIONGAME.ID references MATCH.ID

   GOALS.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.ID

4. Implementation - PHPMyAdmin

The following describe the scope and requirements of the implementation in PHPMyAdmin.

1. Tables and Constraints

   
   

   Create a database in PHPMyAdmin called WorldCup

   You 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.

     
     

2. Sample Data

   
   

   Populate 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.

   
   

3. Views

Write the following queries in SQL on your WorldCup database:

1. Find all the Australian Players that played in the 2018 World Cup

2. Find the players that have never scored a goal

3. Find all the pool games played by host countries

4. Find all the Australian players linked to an English club team

5. Rank the teams by the number of support staff attending the 2018 World Cup

6. Find the names & number of goals saved by all players

7. Find the game that sold the most tickets

8. Find the player who scored the most goals in elimination games

9. Find teams that have played in all World Cups

10. Find players who have played in all of Iceland’s games

Note: 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.

1. Assessment

   Assignment 2 is allocated 100 points which are converted into 10 course marks.

   
   

   Basic Implementation (Tables, Data Types, Primary Keys) 30 Marks Foreign Keys 20 Marks

   Queries 5 Marks each 50 Marks

   
   

   This assignment targets the following assessment criteria of this course:

   1. Analyse, extract and structure information system requirements from a variety of organizational contexts

   2. Reason with the foundations of the relational data model to correctly undertake relational database design

   3. Express queries using the SQL language to provide correct and secure retrieval

      of data from relational databases

   4. Construct a small-scale information system in a relational database management system

   5. Perform information systems analysis and design in a group setting

      
      

2. Late Submissions

   Late 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).

   
   

3. Collaboration

The 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!