Should be in .SQL
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. 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 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 correct and efficient SQL statements to solve problems in a close‐to‐ real‐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 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. 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 Suburb 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 Suburb 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 correctly 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 Torrens University Australia Academic Integrity Policy and Procedure viewable online at http://www.torrens.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 Rubric 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 or 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 errors in answers for Tasks 1–10. Demonstrates proficient knowledge and understanding of the SQL language. Minor errors 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 responsibility. 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, responsibilities 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 responsibilities, 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 transferred 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 transferred 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 suburb 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 both parties (the vendor and the purchaser) sign the contract of sale. Note that the ownership of the property is not transferred 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 be transferred from the vendor to the purchaser. The date that the ownership is transferred is called a settlement date, which is normally a few months after the contract date. Case Scenario PROPERTY