Assessment 3 Case Scenario & High-Level Database Design Table Case Scenario You, as a group, have...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
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 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
Expert Answer:
Related Book For
Managerial accounting
ISBN: 978-0471467854
1st edition
Authors: ramji balakrishnan, k. s i varamakrishnan, Geoffrey b. sprin
Posted Date:
Students also viewed these databases questions
-
Planning is one of the most important management functions in any business. A front office managers first step in planning should involve determine the departments goals. Planning also includes...
-
Jaez Corporation is in the process of going through a reorganization. As of December 31, 2024, the companys accountant has determined the following information although the company is still several...
-
Use Tukey's procedure on the data in Example 10.3 to identify differences in true average bond strengths among the five protocols?
-
Repeat the question above using a put with the same strike price, instead of a call. Suppose that the current eur/gbp spot rate is 0.6, the effective risk-free rates of return are r = 6 percent and r...
-
Evaluate human rights from an anthropological perspective.
-
Austin Limited is trying to determine the value of its ending inventory as of February 28, 2014, the companys year-end. The following transactions occurred, and the accountant asked your help in...
-
The operating revenues of the three largest business segments for Time Warner, Inc., for a recent year follow. Each segment includes a number of businesses, examples of which are indicated in...
-
A double pipe heat exchanger is made of a 6-nom sch 40 commercial steel outer pipe and a 5-nom sch 40S stainless steel inner pipe. The fluid in the annular space is cyclohexane that has a volumetric...
-
Sketchy Trust has taxable income of $50,000. The trust had $5,000 in interest income on tax-exempt municipal bonds, and a capital gain of $3,000. Per the trust agreement, all capital gains are...
-
A thin rod has a total uniform charge of 1.90 nC and length L = 5.10 cm. How much charge does a small piece Ax=0.150 of the rod have? + + + + + + L Ax +
-
The half-life of radium-226 Is 1620 yr. Find: a) the decay rate of radium-226. b) if 10 g of radium-226 is present now, how much will be left in 125 years?
-
When your friend hits the water, is the separation between the two of you 1.8 m, less than 1.8 m, or more than 1.8 m ? IP Standing side by side, you and a friend step off a bridge at different times...
-
Sunland Company shows the following balances in selected accounts of its adjusted trial balance. Supplies $35,200 Supplies Expense 6,600 Accounts Receivable : 13,200 Dividends 24,200 Retained...
-
8% Preferred stock-$10 par value, outstanding 20,000 shares Common stock-$100 par value, outstanding 30,100 shares Retained earnings Novak Company's ledger shows the following balances on December...
-
Question No. 3: Marks [5] The following are the demand and supply functions for three competing mobile phone models of different manufacturers. Using Inverse Matrix Method determine whether there are...
-
B made an issue of 150,000 $1 ordinary shares at a premium of 20% the proceeds of which is received by cheque. What is the correct journal to record this? A. Bank Share capital Share premium B. Bank...
-
What does the CVP relation follow directly from?
-
Igloo Electronics makes cell phones under contract to a Chaebol, which is the Korean term for a conglomerate. Igloo began work on a new contract in January. During January, Igloo started 150,000...
-
The following are some of the costs incurred by a consulting firm. Salary paid to consultants Fee for attending training seminar Salary to office administrator Corporate office rent Cost of...
-
When we are given the value of the _____________________ variable, we can use the least-squares regression line to predict the value of the ______________________ variable. In Exercises 57, fill in...
-
The least-squares regression line predicts the result of changing the value of the explanatory variable. In Exercises 812, determine whether the statement is true or false. If the statement is false,...
-
For each of the following plots, interpret the y-intercept of the least-squares regression line if possible. If not possible, explain why not. a. The least-squares regression line is = 1.98 +...
Study smarter with the SolutionInn App