Subject Code and Title: MIS201 Database Fundamentals
Assessment: Database Programming Project
Individual/Group: Group
Length: N/A
Weighting: 40%
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.
MIS201 Database Fundamentals Assignment-Laureate International University Australia.
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 Scenario & High‐Level Database Design Table.
3.Based on the case scenario and the high‐level database design table, please complete the following tasks:
Task 1.
Write six (6) MySQL statements to create a database called ’ABC_REAL_ESTATE‘ and the five database tables as shown in the case study. You need to choose the most appropriate attribute type for each attribute. The database table design will also minimise the likelihood of data anomalies.
MIS201 Database Fundamentals Assignment-Laureate International University Australia.
Task 2.
Write five (5) MySQL statements to insert five (5) rows into these three tables: PROPERTY, AGENT and VENDOR and three (3) rows into these two tables: PROPERTY_VENDOR and PURCHASE. You may make up the data to be inserted into those tables; however, you must maintain the data integrity of this database.
Task 3.
Write one (1) MySQL statement that changes the status of all properties in VIC to ‘under contract’.
Task 4.
A new legislation has been passed in Victoria that all apartments in that state must be sold at auction. Write one (1) MySQL statement to update your table or tables accordingly.
Task 5:
The vendor of the property under property ID ’56’ decided not to sell it. Write as many MySQL statements as you deem necessary to delete all records pertaining to the property with a property ID ’56‘.
Task 6:
The Real Estate management would like to receive a list of properties which are currently ‘listed’.Write one MySQL query statement to select property information for those that are currently ‘listed’. Your query should return a table in the following format.
Task 7:
The Real Estate management has asked you to provide some insights into the demographic information about the vendor. Write one (1) MySQL query statement that returns the number of male vendors and female vendors. Your query should return a table in the following format.
Write one (1) MySQL query statement that returns the average age of male vendors and female vendors respectively. Your query should return a table in the following format.
Write one (1) MySQL query statement that returns the number of properties that a vendor has. Your query should return a table in the following format.
Task 8:
ABC Real Estate provides their loyal vendors with a monthly newsletter, in which they provide statistics about the property sales for the States where the agency operates. Write one (1) MySQL query statement that returns the average sale price for properties that were ‘under contract’ in the past month for each State where the agency operates. The result should be sorted from highest to lowest by average sale price. Your query should return a table in the following format.
MIS201 Database Fundamentals Assignment-Laureate International University Australia.
Write one (1) MySQL query statement that returns the average sale price for properties that were ‘under contract’ in the past month for each State where the agency operates by property type. Your query should return a table in the following format.
Task 9:
The management of ABC Real Estate pays their agents’ commission on a quarterly basis. Write one (1) MySQL statement that calculates the commission for each agent from their auction sales from July 2019 to September 2019 (inclusive). Your query should return a table in the following format.
The management decides to award an extra $500 to the top three best agents in a quarter. Agents are ranked by the commissions they received from non‐auction sales in a quarter. Write one (1) MySQL statement that lists the three eligible agents for the sale period between July 2019 and September 2019 (inclusive). Your query should return a table in the following format.
Task 10:
The System supports fuzzy search for properties. Write one (1) SQL statement that lists all properties whose street name (part of its address) starts with ‘Bay’. Your query should return a table in the following format.
Write another one (1) MySQL statement that lists all properties whose street name (part of its address) contains ‘bay’. Your query should return a table in the following format.
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.
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.
MIS201 Database Fundamentals Assignment-Laureate International University Australia.
ORDER Now This MIS201 Database Fundamentals Assignment And Get Instant Discount
