INFS7900 Assignment 2 – Module 3
Code Due: 9 May 2025 @ 3:00 PM AEST
Oral Assessment: Week 12, 19-23 May 2025
Weighting: 25%
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 that exist in a database and Data Definition Language to create a 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 interview with a tutor during a 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, and D where indicated, 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 file types).
o Each file should only contain the SQL query(s) and no additional text or comments.
o Each file should be named as per the Filename description in the question.
o When submitting files to the autograder, select all of your .sql or .txt files as well as your .pdf file.
o It is recommended to write queries in a SQL/text file and test them in your phpMyAdmin zones before copying them into the Word document.
o Your queries must compile using MySQL version 8.0. This is the same DBMS software as is used on your phpMyAdmin zones.
o You may use any MySQL function that have been used in class in addition to those specified in the questions.
· Assignment PDF:
o Insert your answers for all Sections A-D into the template boxes on the Microsoft Word version of this assignment task sheet where appropriate. Export this document to a PDF and also upload it to the Gradescope autograder portal.
o Only subsections of Section D will be partially hand-marked from your PDF submission, however this is also a backup for Sections A, B and C in case of autograder failure.
o 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.
o Please name your file ‘Assignment_2.pdf’. Please do not alter the format or layout of this document 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 member of the teaching staff during Week 12 practical sessions, 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 (i.e. on Zoom) 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 (note that INFS1200 differs from INFS7900):
|
|
INFS7900
|
|
Section A – SQL DML (SELECT)
|
13 marks
|
|
Section B – SQL DML (UPDATE, INSERT, DELETE)
|
3 marks
|
|
Section C – SQL DDL
|
3 marks
|
|
Section D – Critical thinking
|
6 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 marks (50% for this assignment).
Grading and Autograder feedback:
Sections A, B, C and parts of D 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.
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 tests, 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.
· Column Domain checking: The autograder will check whether the domain of the projected values for each attribute in your query results matches the expected domain. Make sure to carefully read the 'explanation' section in each question of the assignment, as it provides specific details about the expected domains for attributes in the result.
Submit your work to Gradescope early so you can use the test results to ensure your queries are compiling and you are on the right track. 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 a simple data instance that will yield a result for every correct DML (SELECT) question. Visible test results in the autograder submission portal will check that the query compiles successfully. Because the autograder uses the same DBMS as your zones, you are encouraged to use your zones to develop your assignment answers.
Late penalties: Please consult the course profile for late penalties that apply to this assessment item.
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 an acceptable level of collaboration with your peers, please see either the lecturer or your tutor for guidance. Remember that ignorance is not a defence!
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 authorisation application.
BestTechLtd have designed and developed a simplistic authorisation management system to provide secure and sufficient access control within their organisational network.
When a new employee joins an organisation using SecureAccess, they are registered and their personal details are stored in the Employee table. Administrative employees, whose additional information is stored in the AdministrativeEmployee table, can then assign appropriate roles to these employees through a role-granting process. Each role, defined in the Role table, comes with specific permissions that determine which company websites and resources the employee can access. This hierarchical permission structure ensures that employees only have access to the resources necessary for their job functions. BestTechLtd manage their authorisation by storing employee and permission data in a relational database management system (RDBMS) with the following schema:
· The Employee table records staff-specific information.
· Administration employees are a specific type of employee that have authority to grant authorisation roles to employees.
· The Role table records typical roles that might exist within a business and is associated with the necessary permissions that a user (Employee) of the role will need.
· The Permission table maintains a record of all the websites in a company’s network. A website URI and RoleID associated with the website means that the Role is granted permission to access, retrieve resources and perform. operations on the website.
ERD for the BestTechLtd schema: