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.
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:
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 Solution
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
Implementation - PHPMyAdmin
The following describe the scope and requirements of the implementation in PHPMyAdmin.
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.
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.
Views
Write the following queries in SQL on your WorldCup database:
Find all the Australian Players that played in the 2018 World Cup
Find the players that have never scored a goal
Find all the pool games played by host countries
Find all the Australian players linked to an English club team
Rank the teams by the number of support staff attending the 2018 World Cup
Find the names & number of goals saved by all players
Find the game that sold the most tickets
Find the player who scored the most goals in elimination games
Find teams that have played in all World Cups
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.
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:
Analyse, extract and structure information system requirements from a variety of organizational contexts
Reason with the foundations of the relational data model to correctly undertake relational database design
Express queries using the SQL language to provide correct and secure retrieval
of data from relational databases
Construct a small-scale information system in a relational database management system
Perform information systems analysis and design in a group setting
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).
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!