Assessment details for ALL students
Assessment item 2—Assignment 2
Friday of Week 10 (Midnight AEST time)
Typically 10-20 hours
This assessment item relates to the following course learning outcomes:
• database applications, systems, and the database environment
• database design using a specified data modelling approach
• normalisation theory
• relational database systems
• database implementation issues
• Regular access to a system running an appropriate DBMS is required for this assignment.
Details of appropriate DBMS are provided later in this document.
• This assignment is an individual piece of work. Discussing the assignment with other
students is encouraged; however, the document you submit must be an individual piece of
work, produced by you. In your submission, identify any students who have influenced your
work on this assignment.
• The assignment due date is Friday Week 10. The sample solution will be released on the
Monday following Week 12 (17 days after the due date). A penalty of 5% of total marks
PER CALENDAR DAY will apply to late submissions. Submissions made after release of
the sample solution will receive ZERO (0) marks.
• A late penalty can be avoided by applying for an extension. AIC students must submit an
application to the local campus. Other students must submit an application via the
electronic Assignment Extension System. To avoid a late penalty, the application must be
submitted before the due date.
• An electronic submission is required for this assignment. After making your submission,
download your submitted file to check it has not been corrupted. Before submission, check
you have met all criteria in the marking sheet (available from the course website).
• This assignment is marked out of 40. The mark you obtain contributes 20% towards your
Part A — SQL queries (8 marks)
This part of the assignment tests your understanding of SQL topics covered in Week 6.
1. Replace the query below with an equivalent query that is simpler, does not use UNION and does
use an OUTER JOIN: (2 marks)
SELECT Description,COUNT(NbrItemsRequested) AS TotalItems
FROM Product AS p INNER JOIN SalesOrderProduct AS sop
ON p.PID = sop.PID
GROUP BY Description
HAVING COUNT(NbrItemsRequested) >= 1
SELECT Description, 0
WHERE PID NOT IN (SELECT PID