MIS201 Database Fundamentals

发布时间：2024-07-01

Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

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

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.