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...
-
Reconsider the axial stiffness data given in Exercise 8. ANOVA output from Minitab follows: Pooled StDev = 32.39. Tukey's pair-wise comparisons Family error rate = 0.0500 Individual error rate =...
-
Discuss the reasons behind the growing convergence and interdependence of labor unions around the world.
-
On March 13, 2009, Juan Mendez Sr. was admitted to a nursing facility. On that day, a doctor employed by the facility determined the father lacked the capacity to give informed consent or make...
-
At Dot Corn, a large retailer of popular books demand is constant at 32.000 books per year. The cost of placing an order to replenish stock is $10 and the annual cost of holding is $4 per book. Stock...
-
Prove that the series (1+3i)" zn n= 4" n ln (n) converges for all z such that || 2. Here In: (0, ) R is the natural logarithm.
-
Given the following information: Gross sales $1,500,000 Items purchased from out of state vendors $20,000 Items purchased under certificate of resale but used $5,000 Food sales included in gross...
-
How do the following factors affect the external financing requirement? a. Revenue growth rate b. Capacity utilization c. Capital intensity d. Profitability e. For investor-owned firms, dividend...
-
What are the two components of interest rate risk?
-
What is the difference between using derivatives for hedging as opposed to speculation?
-
What is the difference between bidding regulation and antitrust regulation?
-
Why do businesses, mostly not-for-profit hospitals, hold long-term investment portfolios?
-
QUESTION A uniform bar of length 10 centimetres is held at 0C at x = Ocm and 100C at X = 10cm. The bar is first heated with an initial temperature distribution given by, f(x) = 100-x; 0
-
[a] Two foam blocks, each with a charge of 19 micro coulombs (1 C = 10-6 C), are both held in place 19 cm apart in the east-west direction. A foam ball with a charge 49 C is placed 55 cm north of the...
-
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...
-
To test \(H_{0}: \sigma=50\) versus \(H_{1}: \sigma <50\), a random sample of size \(n=24\) is obtained from a population that is known to be normally distributed. (a) If the sample standard...
-
In Example 2 from Section 10.3, the quality-control engineer for M\&MMars tested whether the mean weight of fun-size Snickers was 20.1 grams. Suppose that the standard deviation of the weight of the...
-
(a) Determine the critical values for a right-tailed test of a standard population deviation with 18 degrees of freedom at \(\alpha=0.1\) level of significance. (b) Determine the critical values for...
Study smarter with the SolutionInn App