|
ASSESSMENT 3 BRIEF
|
|
Subject Code and Title
|
MIS201 Database Fundamentals
|
|
Assessment
|
Database Programming Project
|
|
Individual/Group
|
Group
|
|
Length
|
N/A
|
|
Learning Outcomes
|
The Subject Learning Outcomes demonstrated by successful completion of the task below include:
a) Analysing specific organisational needs that can be addressed by collection, storage and management of organisational data.
b) Applying different database techniques to collaboratively design solutions to complex organisational problems and communicating these solutions to stakeholders.
c) Using contemporary database programming techniques to implement effective solutions that address complex
information systems problems in an organisational setting.
d) d) Applying and communicating database solutions for specialist and non-specialist stakeholders.
|
|
Submission
|
12 Week Cycle: Due by 11:55pm AEST Sunday end of Module 6.1 (Week 11)
6 Week Cycle: Due by 11:55pm AEST Sunday end of Module 6.1
|
|
Weighting
|
40%
|
|
Total Marks
|
100 marks
|
Task Summary
In this group assessment, you are required to read the case scenario provided and complete a number of database programming tasks. These tasks require you to create database and tables, and to manipulate, delete and query data.
Context
This Database Programming Project focuses on assessing your SQL programming skills. By completing this assessment, you will demonstrate your understanding of the following topics:
- SQL data definition and data manipulation statements and
- Select query statements,
as well as your ability to write correct and efficient SQL statements to solve problems in a close-to- real-life scenario.
Task Instructions
1. Form. groups of 2-3 members as per instructions outlined in the MIS201_Assessment 3 Group Working Guide document attached.
2. Please read the attached MIS201_Assessment 3_ Case Study.
3. You may insert, update delete as many records as necessary to display meaningful outputs.
4. All the SQL Queries must be generated using MYSQL Workbench or Command-line Client (CLC).
5. Based on the case scenario and the database design, complete the following tasks:
Task No Description Marks
Task 1 (a) Create a database called ‘ass3db’ 1
(b) Create the database tables based on the table structure and ER model provided in the case study, including primary key (PK) and foreign key (FK) constraints. 16
(c) Insert at least 10 records into each table. 14
Task 2 -12: Generate ONE SQL query to answer each of the following questions.
Task No Description Marks
Task 2 Find all the plants containing 'sparingly' in their watering instructions in Plant table. 3
Task 3 Count the number of plants with "Low" light requirements and low humidity requirements in the Plant table. 3
Task 4 Find the total number of users whose location is ‘US’. 3
Task 5 Calculate the average rating given to plants in the "Reviews" table. 5
Task 6 List the CollectionName and the number of plants in each collection if they have more than 1. 5
Task 7 List the CommonName of plants that do not have any reviews. 5
Task 8 Calculate the average rating for each plant in the Reviews table. 5
Task 9 Retrieve the CommonName and Rating of the plant with the highest rating from the Reviews table. 5
Task 10 Find the user(s) with the highest number of collections and list their username and collection count. 5
Task 11 How many plants have been acquired each month in 2023. 5
Task 12 Retrieve the CollectionName and Username of collections created in the last 3 months. 5
Total 80
Layout
SQL file. ALL answers must be written in a single SQL file (.sql). You do not need to copy the question. Use SQL comment to indicate question number, e.g.
/* Task 1*/
Submission Instructions
There are two submission points for this assessment: Graded submission: Group submission
1. Each team will submit ONE (1) SQL file (.sql) via the Assessment 3 section found in the main
navigation menu of the subject’s Blackboard site. The Learning Facilitator will provide feedback via the Grade Centre in the LMS portal. Feedback can be viewed in My Grades.
Peer Evaluation: Individual Submission
2. Each individual student is to submit a completed team participation score matrix document in the Assessment 3 – Peer Evaluation submission link. Please submit your peer review correctly as no submission may result in a zero grading for this assessment criteria.