sql代写-Information Systems Assignment

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!

51作业君 51作业君

扫码添加客服微信

添加客服微信: IT_51zuoyejun