Microsoft Word - MIS201_Assessment 3_Brief_Database Programming Project-Final.docx
MIS201_Assessment_3_Brief_Database Programming Project_Module 6.1 Page 1 of 7
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.
) 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 fo
specialist and non‐specialist stakeholders.
Submission Due by 11:55pm AEST Sunday end of Module 6.1 (Week 11)
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 co
ect and efficient SQL statements to solve problems in a close‐to‐
eal‐life scenario.
MIS201_Assessment_3_Brief_Database Programming Project_Module 6.1 Page 2 of 7
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. (0.5 * 6 = 3 marks)
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.
Task 2. (0.5 * 5 = 2.5 marks)
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. (1.5 marks)
Write one (1) MySQL statement that changes the status of all properties in VIC to ‘under contract’.
Task 4. (2 marks)
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. (3 marks)
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‘.
MIS201_Assessment_3_Brief_Database Programming Project_Module 6.1 Page 3 of 7
Task 6. (2 marks)
The Real Estate management would like to receive a list of properties which are cu
ently ‘listed’.
Write one MySQL query statement to select property information for those that are cu
ently
‘listed’. Your query should return a table in the following format.
Property ID STREET_ADDRESS SUBURB STATE TYPE
Task 7. (2*3 = 6 marks)
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.
Gender Number
Male
Female
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.
Gender Average age
Male
Female
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.
Vendor ID Name Number of properties
MIS201_Assessment_3_Brief_Database Programming Project_Module 6.1 Page 4 of 7
Task 8. (4 + 4 = 8 marks)
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.
State Average sale price
VIC
NSW
SA
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.
State Type Average sale price
VIC land
VIC townhouse
VIC house
VIC unit
VIC apartment
NSW land
NSW townhouse
NSW house
NSW unit
NSW apartment
SA land
SA townhouse
SA house
SA unit
SA apartment
MIS201_Assessment_3_Brief_Database Programming Project_Module 6.1 Page 5 of 7
Task 9. (4 + 4 = 8 marks)
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.
Agent ID Agent Name Commission
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.
Agent ID Agent Name
Task 10. (2 + 2 = 4 marks)
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.
Property ID Street
Address
Subu
State Status Type List price
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.
Property ID Street
Address
Subu
State status type List price
MIS201_Assessment_3_Brief_Database Programming Project_Module 6.1 Page 6 of 7
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 co
ectly as
no submission may result in a zero grading for this assessment criteria.
Academic Integrity Declaration
Group assessment tasks:
We declare that except where we have referenced, the work we are submitting for this assessment
task is our own work. We have read and are aware of To
ens University Australia Academic Integrity
Policy and Procedure viewable online at http:
www.to
ens.edu.au/policies‐and‐forms
We are aware that we need to keep a copy of all submitted material and their drafts, and we will do
so accordingly.
MIS201_Assessment_3_Brief_Database Programming Project_Module 6.1 Page 7 of 7
Assessment Ru
ic
Assessment
Attributes
Fail
(Yet to achieve
minimum standard)
0‐49%
Pass
(Functional)
50‐64%
Credit
(Proficient)
65‐74%
Distinction
(Advanced)
75‐84%
High Distinction
(Exceptional)
85‐100%
SQL file
Knowledge and
understanding of the
SQL language
demonstrated through
the successful
completion of Tasks 1 –
10.
80%
Demonstrated none o
partially developed
understanding of the SQL
language with incomplete
answers to Tasks 1–10.
Demonstrates a functional
knowledge and understanding
of the SQL language.
Significant e
ors in answers
for Tasks 1–10.
Demonstrates proficient
knowledge and
understanding of the SQL
language. Minor e
ors in
answers for Tasks 1–10.
Demonstrates advanced
knowledge of the SQL
language by answering Tasks
1–10 accurately.
Demonstrates exceptional
knowledge of the SQL
language with accurate and
well‐written answers
provided for all the tasks.
Team Work
An active member of the
team. Group work is
equal amongst the
student group.
Peer review score
average gathered from
team participation
matrix.
20%
Limited or no contribution
to the team. Other team
member(s) had to
contribute more and take
on the tasks agreed to be
completed.
Places individual goals
ahead of the group
esponsibility. Hinders the
group process and upsets
the schedule.
Has fulfilled part of the agreed
tasks and overlooked others
for group tasks.
Has demonstrated some
understanding of team and
individual goals, tasks,
esponsibilities and schedules.
Contributes to select group
processes. Has not been
initiating tasks for the group.
A passive member of the
group.
Contributes to small group
discussions to reach
agreement on issues. Works
together with others
towards shared goals.
Renegotiates responsibilities
to meet needed change.
Very valuable member of
the team actively
contributing to the team
effort and the group
assessment outcomes.
Understands group
dynamics and team roles.
Facilitates team
development. Renegotiates
esponsibilities, tasks and
schedules to meet needed
change.
Is a consistent and reliable
key member of the group,
taking initiative and
extensively contributing to
the group assessment
effort. Builds team’s identity
and commitment. Leads
team. Evaluates team’s
outcomes. Implements
strategies for enhancing
team effectiveness.
Assessment 3 Case Scenario & High-Level Database Design Table
MIS201_Assessment 3_ Case Scenario & High-Level Database Design Table Page 1 of 3
Case Scenario
You, as a group, have been contracted by a national real estate agency — ABC Real Estate — to
develop the backend database for a property management system (the System). The System shall
allow the owners of the agency to track their sales performance, to calculate commissions for their
sales agents and to extrapolate business insights from their property sales history.
The party who sells a property is normally called ‘the vendor’. They are normally but not always the
owners of the property. A property may be owned jointly by multiple people. Thus, there may be
more than one vendor for a property. For the purpose of this assessment, please assume that each
of the vendors would have the attributes of an individual person. For example: name, gender and
age.
A vendor will normally engage a sales agent and authorise the agent to run a marketing campaign to
sell the property. In return, the sales agent will take a proportion from the sales proceeds as a
commission. The commission is calculated at 6% of the property sale price if the property is sold
through auction, or 4% of the property sale price if it is sold through other means, for example a
private sale. For the purpose of this assessment, please assume that a property can be sold by only
one agent.
Once the vendor contacts the agent and authorises them to market the property, the agent will then
advertise the property on popular websites such as Domain or Realestate.com.au. At this stage, the
property will be labelled as ‘listed’.
If the property is auctioned, the highest bidder and the vendor will enter into an unconditional
Contract of Sale (‘CoS’) on the auction day and the status of the property will be changed to ‘under
contract’. In this assessment, you do not need to consider any other possibilities at auction, e.g., the
property is withdrawn or passed in.
If the property is not to be auctioned, interested purchasers may approach the agent and make
offers on the property. If the vendor accepts the offer, both parties will normally proceed to a
conditional CoS, which is subject to conditions, for example, securing a home loan from the bank. At
this stage, the status of the property will be changed to ‘under offer’. Once the conditions in the CoS
are satisfied and the CoS becomes unconditional, the status of the property will be changed to
‘under contract’.
There is normally a one to six month gap between the date when a contract, conditional or
otherwise, is entered into and the date that the ownership of the property is transfe
ed from the
vendor to the purchaser. The time duration allows the vendor to move out of the house and the
purchaser to prepare finances and other paperwork required for the conveyancing of the property.
The date on which the ownership is transfe
ed is called the settlement date. Once the property is
settled, its status will be changed to ‘settled’. This is when the commission becomes payable to the
agent.
MIS201_Assessment 3_ Case Scenario & High-Level Database Design Table Page 2 of 3
High-level database design table
PROPERTY
Attribute Comment
ID Primary key, unique ID for each property, auto-increment
STREET_ADDRESS The street address for the property
SUBURB The subu
of the property
STATE The State of the property. Only the following values are valid:
VIC, NSW, SA
STATUS The status of the property. Only the following values are valid:
listed, under offer, under contract, settled
TYPE The type of the property. Only the following values are valid:
land, townhouse, house, unit, apartment
LIST_PRICE The list price for the property, i.e. the asking price that is shown in the
advertisement for the property. Assume that this is a single number,
i.e. NOT a price range
DATE_CREATED The date this entry is creased in the database
DATE_EDITED The date this entry is edited in the database
VENDOR
Attribute Comment
ID Primary key, unique ID for each vendor, auto-increment
NAME The name of the vendor
GENDER The gender of the vendor
AGE The age of the vendor
PROPERTY_VENDOR
Attribute Comment
PROPERTY_ID Foreign key. ID in PROPERTY table
VENDOR_ID Foreign key. ID in VENDOR table
MIS201_Assessment 3_ Case Scenario & High-Level Database Design Table Page 3 of 3
AGENT
Attribute Comment
AGENT_ID Primary key, unique ID for each agent, auto-incremented
NAME The name of the agent
GENDER The gender of the agent
AGE The age of the agent
PURCHASE
Attribute Comment
ID Primary key, unique ID for each sale, auto-incremented
PROPERTY_ID Foreign key. ID in the PROPERTY table
AGENT_ID Foreign key. ID in the AGENT table, assuming each property is sold by
one and only one agent.
AUCTION Binary value. Whether or not this property is sold via auction,
property can also be sold through other means, for example, private
sale or sale by set date. This attribute only indicates whether this
property is sold via auction or not.
SALE_PRICE The actual sale price for the property
CONTRACT_DATE The date that the contract of sale is entered, that is, the date that
oth parties (the vendor and the purchaser) sign the contract of sale.
Note that the ownership of the property is not transfe
ed until the
settlement date, which is normally stipulated on the contract of sale.
SETTLEMENT_DATE The date for settlement. Once a contract of sale is entered, it would
normally take one to six months for the ownership of the property to
e transfe
ed from the vendor to the purchaser. The date that the
ownership is transfe
ed is called a settlement date, which is normally
a few months after the contract date.
Case Scenario
PROPERTY