qidao123.com技术社区-IT企服评测·应用市场

标题: MIS201 Database FundamentalsSQL [打印本页]

作者: 南七星之家 时间: 2024-8-4 13:20
标题: MIS201 Database FundamentalsSQL
Java Python

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 understandi MIS201 Database FundamentalsSQL ng 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

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

欢迎光临 qidao123.com技术社区-IT企服评测·应用市场 (https://dis.qidao123.com/)