INFS1200/7900 Introduction to Information Systems
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit
INFS1200 / INFS7900 Assignment 2
Code Due: 6 October 2023 @ 4:00 PM AEST
(Oral Assessment to be held in Week 12, 17-20 October 2023)
Weighting: 25% Version 2.0
Overview
The purpose of this assignment is to test your ability to use and apply SQL concepts to complete tasks in a real-world scenario. Specifically, this assessment will examine your ability to use SQL Data Manipulation Language to return specific subsets of information which exist in a database and Data Definition Language to create new relational schema. The assignment is to be completed individually.
Submission
Assignment 2 is made up of two parts. Part 1 will be submitted through an electronic marking tool called Gradescope, which will also be used for providing feedback. Part 2 is an oral assessment that will be completed during an in-person meeting with a tutor during a tutorial or practical session in Week 12 (after your Gradescope submission). Details below:
Part 1: Answer the questions on this task sheet and submit them through an electronic marking tool called Gradescope.
For this assignment, you will need to submit two types of files to the portal:
· Query Files:
o For each question in Sections A, B and C, you are required to submit a separate .sql or .txt file which contains your SQL query solution for that question (submit only one of these files; if you submit both, the .sql file will be graded).
o Each file should only contain the SQL query(s) and no additional text.
o Each file should be named as per the Filename description in the question.
o The total number of queries allowed to be run per question is also specified in each question’s description.
o When submitting files to the autograder, select all of your .txt or .sql files as well as your .pdf file.
· Assignment PDF:
o Insert your answers for all Sections A-D into the template boxes on this assignment task sheet where appropriate, then export this document to a PDF and also upload it to the Gradescope autograder portal.
o Only Section D will be hand-marked from your PDF submission, however this is also a backup for Sections A, B and C in case of autograder failure.
o UPDATES:
u For Sections A, B and C, include a screenshot of the output of your query for each question in the space provided. Use your zones to generate the output. For queries with a returning relation of more than 10 tuples, you can use the LIMIT 10 clause to only capture the first 10 tuples of the table.
u Please name your file ‘Assignment_2.pdf’. Please do not alter the format or layout of this document in anyway and ensure the name and SID boxes are completed.
Part 2 is an oral assessment, to verify your understanding of the code you submitted in Part 1 Sections A, B and C.
· This will be an oral critique of your submitted code. In a short meeting with a tutor during Week 12, you will explain the work you have submitted in Part 1 and discuss your choices.
· All oral assessments must be given live and will be recorded by the teaching team for archiving purposes.
Marking
Assignment 2 is worth 25 course marks, and marking is made up of two parts.
First, the marks available per section of Part 1 are as follows (INFS1200 differs from INFS7900):
|
|
INFS1200 |
INFS7900 |
|
Section A – SQL DML (SELECT) |
15 marks |
14 marks |
|
Section B – SQL DML (UPDATE, INSERT, DELETE) |
4 marks |
4 marks |
|
Section C – SQL DDL |
4 marks |
4 marks |
|
Section D – Critical thinking |
2 marks |
3 marks |
Given these available marks, students must also achieve a pass (+/-) in Part 2, the oral critique, to be eligible to pass Assignment 2. Failure in Part 2 will result in your mark being capped at 12.5%.
Grading and autograder feedback
Sections A, B and C of this assignment will be graded via an autograder deployed on Gradescope; however we reserve the right to revert to hand marking using the pdf submission should the need arise. Specifically, your assignment may be graded against several data instances, which may include a simple (and small) data instance, a large data instance or an instance containing curated edge cases. The correctness of your queries will be judged by comparing your queries’ return values to those of our solutions, because there is usually more than one equivalent way to execute a given query.
Note that solutions to each question will be limited to contain a maximum of 3 queries.
When you submit your code, the autograder will provide you with two forms of immediate feedback:
· File existence and compilation tests: Your code will be checked to see if it compiles correctly. If it fails one or more compilation test, the errors returned by the autograder will help you debug. Note that code that fails to compile will receive 0 marks. No marks are given for passing the compilation tests.
· Simple instance data tests: The autograder will return your degree of success on the simple data instance, so that you can judge your progress (i.e. 90% of simple instance tests passed). Individual test results will not be revealed, and your submission’s performance on the more difficult instances will remain hidden until grades are released. Final weightings on the different test instances will also remain hidden until grades are released.
More details will be provided regarding how you can interpret the results of these tests and what it means for your assignment grade during practicals.
Note: Your queries must compile using MySQL version 8.0. This is the same DBMS software as is used on your zones. You may use any MySQL function that have been used in class in addition to those specified in the questions. You may also use other MySQL functions not covered in this course to assist with manipulating the data if needed, however please ensure you read the MySQL documentation page first to ensure the functions works as intended.
The final details of the Gradescope autograder will be released closer to the assignment deadline. Note that you will be able to resubmit to the autograder an unlimited number of times before the deadline.
Materials provided:
You will be provided with the database schema and the simple data instance. Because the autograder uses the same DBMS as your zones, you are encouraged to use your zones to develop your assignment answers.
Plagiarism
The University has strict policies regarding plagiarism. Penalties for engaging in unacceptable behaviour range from loss of grades in a course through to expulsion from UQ. 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!
In particular, you are permitted to use generative AI tools to help you complete this assessment task. However, if you do, please provide complete copies of your interactions with the AI tool in the space provided at the end of your submission. Please note that if you use generative AI but fail to acknowledge this by attaching your interaction to the end of the assignment, it will be considered misconduct as you are claiming credit for work that is not your own.
Task
For this assignment you will be presented with the simplified schema of an event management application for the Olympics. It captures essential details about countries, sports, athletes, events, and the medals won in these events.
1. Countries table lists all the participating countries in the Olympic games. Each country has a unique identifier (CountryID), a name (CountryName), and is associated with a specific geographical region (Region).
2. Sports table contains information about various sports played in the Olympics. Each sport has a unique identifier (SportID) and a name (SportName).
3. Athletes table records all athletes participating in the games. Each athlete is uniquely identified by an AthleteID, and they also have a name (AthleteName). They are associated with a specific country, represented by the CountryID, which links to the Countries table. Their age is also stored in the database.
4. Events table represents the events held in different sports. Each event is uniquely identified by an EventID and is associated with a specific sport, represented by the SportID, which refers to the Sports table and an associated date and ticket price for the event.
5. Medals table archives the allocation of medals. Each entry, denoting a medal, is uniquely associated with a particular athlete (AthleteID) and a specific event (EventID), and it documents the category of the medal (MedalType - Gold, Silver, or Bronze) won by the athlete. The AthleteID references the Athletes table, and the EventID refers to the Events table.
6. Contestants table contains all contestants in all events. Each entry is uniquely identified by an event (EventID) and an athlete (AthleteID) participating in that event. The EventID refers to the Events table, and the AthleteID references the Athletes table.
Relational Schema:
Countries [CountryID, CountryName, Region]
Sports [SportID, SportName]
Athletes [AthleteID, AthleteName, CountryID, Age]
Events [EventID, SportID, Date, TicketPrice]
Medals [AthleteID, EventID, MedalType]
Contestants [EventID, AthleteID]
Foreign Keys:
Athletes.CountryID references Countries.CountryID
Events.SportID references Sports.SportID
Medals.AthleteID references Athletes.AthleteID
Medals.EventID references Events.EventID
Contestants.EventID references Events.EventID
Contestants.AthleteID references Athletes.AthleteID
For this assignment you will be required to write SQL queries to answer to complete the following tasks. Please use the submission boxes provided to record your answers. An example is given below.
|
Example |
|
|
Task |
Return the id and name of all athletes. |
|
Explanation |
This query should return a table with two columns, one for the id and one for the name of the athletes. |
|
SQL Solution |
SELECT AthleteID, AthleteName FROM Athletes LIMIT 10;
|
Output screenshot:
Section A – SQL DML (SELECT)
|
Question 1 |
|
|
Task |
Return the names of all sports played at the Olympics (duplicates should not be included), ordered by SportName in alphabetical order.
|
|
Filename |
a1.sql or a1.txt |
|
SQL Solution |
|
Output screenshot:
|
Question 2 |
|
|
Task |
Return the number of events that occurred for each sport during the month of July 2023. |
|
Explanation |
This query should return two columns, one for the SportID and one for the number of events that occurred for each sport. |
|
Filename |
a2.sql or a2.txt |
|
SQL Solution |
|
Output screenshot:
|
Question 3 |
|
|
Task |
Return the number of medals won for each country. |
|
Explanation |
This query should return two columns, one for the CountryID, and one for the number of medals won (if the country has won 0 medals, it should still be included). |
|
Filename |
a3.sql or a3.txt |
|
SQL Solution |
|
Output screenshot:
|
Question 4 |
|
|
Task |
Return the medal tally for ‘Australia’ across all events. |
|
Explanation |
This query should return a table with two columns, one with the type of medal (Gold, Silver, or Bronze) and the other with the number of medals won for Australia. |
|
Filename |
a4.sql or a4.txt |
|
SQL Solution |
|
Output screenshot:
|
Question 5 |
|
|
Task |
Return the country names of countries who have at least one participating athlete over the age of 30. |
|
Explanation |
This query should use at least one sub-query. |
|
Filename |
a5.sql or a5.txt |
|
SQL Solution |
|
Output screenshot:
|
Question 6 |
|
|
Task |
Return the name, age of the youngest Australian athlete(s) participating in the Olympics. |
|
Explanation |
|
|
Filename |
a6.sql or a6.txt |
|
SQL Solution |
|
Output screenshot:
|
Question 7 |
|
|
Task |
Return the country names of countries that won more than one gold medal in the Olympics. |
|
Explanation |
|
|
Filename |
a7.sql or a7.txt |
|
SQL Solution |
|
Output screenshot: