Assessment 3 Case Scenario & High-Level Database Design Table 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. 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