Executive Summary

Real estate is the most globally recognized industry. With the increasing demand for professional customer service to facilitate the transactions, real estate brokerage became one of the hottest businesses projected to solid growth post-pandemic. With the increasing number of agents, listings, and clients’ information, a seamlessly integrated database management system can facilitate agents in better helping clients and optimizing stakeholders’ profit. There are some pain points to operating a real estate firm and better data management will help businesses in relife those issues. To stand out from the highly competitive real estate industry, using different data to manage information and make an efficient decision is important. Agents are the people who really keep the business going. To better keep track of the business activities from each agent and assist them in helping clients is more and more difficult for the firm with large scales in size. Besides managing the agents, facilitating agents in managing the client’s relationship is critical to increasing closing rate and generating revenue. Understanding their client’s needs and better matching the right property for them is a fundamental element to the decided closing rate. The business relies on the client relationship, using data to analyze clients’ needs and knowing the customers better would help the firm in future growth and expansion. Properties’ listing is the inventory for a company, keeping track of each listing becomes more complicated when the company has more inventories, and adjusting the price for each property will increase the turnover rate as well as bring more profits for stakeholders. An OLAP database management system designed for real estate brokerage can help with the problems mentioned above by better storing, managing, and analyzing data from different perspectives. 

Business case

Background: One of the mid-size real estate firms in New York City had unexpected growth last year because of the renounced real estate price after a year plummeting during the pandemic. They hired 40% more agents and opened 3 more franchises to help with company expansion, and their number of clients in the system doubled within the last year. The company is using a file system to manage clients’ information, each agent has their own page to keep track of their leads and the manager can review agents’ work in the same drive.

Database issues:  Management now has issues in managing their agents, and agents are having trouble serving each of their clients better with the current system because of the scale of the company. Besides managing their agents’ performance, managing their listing is another problem. They realized the inventory turnover is much longer than before because the inexperienced new agents do not have a professional pricing strategy to list the property, and the closing rate is lower than expected with the longer than before negotiation length. The company needs technical help to better process their client information and make agents work more efficiently. 

In a real estate brokerage, each agent has their own listing which can be shared within the entire company. Each transaction is associated with an agent, the company needs to keep track of agents’ performance to compensate them with different commission pay off percentages based on agents’ accumulated sales, but some of the agents said they received an incorrect amount last month. When the owner intends to sell or lease their property, the broker needs to identify a legit number which is attractive enough for owners and acceptable for buyers or renters. The company needs to assist its agents in pricing strategy to increase the inventory and decrease the turnover rate. There is always an optimized point for the property price to maximize profit and minimize the days on the market. From a customer experience perspective, the company received more feedback from clients regarding they are ignored by agents for a long time and the historical data reflect the closing rate is only 60% which is lower than other similar size brokerages in New York City. The database management system will keep track of all agents’ activity and each agent accounts to calculate commission for accounting purposes and will help agents with managing their own leads by increasing the engagement and interaction with clients. 

Once the database system management is implemented, it will automatically calculate the agent’s payroll based on their performance. The company should be able to manage all agent’s accounts accurately and compensate them without errors. The manager will identify the regional top sales, where the hottest neighborhood, easily with OLAP.  All property detailed information will be stored in the database, and the system will analyze historical data to provide an estimated price for the inexperienced agents when they list the new property at a reasonable price. For client relationship management, the system will help agents keep track of their leads and identify the most potential clients to increase the closing rate. 

Project Objectives 

The project will deliver a real estate company database management system to help manage the agents in the firm and help agents better assist their clients in increasing revenue. Since the seasonality of the real estate industry, to better test the efficiency for the new database system, the success criteria will be analyzed in one year period after the new system is implemented. The ultimate goal is to increase revenue for the business by 30% within one year by increasing the system efficiency. The system will decrease data inconsistency and redundancy to increase data entry errors and increase the efficiency of the company. The projected success details that facilitate we achieve our ultimate goal include:

Facilitating management decisions: The DBMS will improve data sharing within an organization. The new agents’ information can be easily added to the database, and the better integration will give managers a better view of the whole picture of the organization. 

Maintaining the accounting accuracy by 100%. DBMS will increase the data inconsistency and help track agents’ commission in each transaction, calculate each agent's total commission, and deliver it to an accountant. 

Increasing the closing rate by 20%. The most important reason the real estate deal cannot close is the agents’ lack of experience. The system will help agents identify the most valuable customer in the leads and set up follow-up reminders. It will also analyze the historical data from the previous listing to provide pricing strategies and optimize profits.  

Increase the customer satisfaction rate by 20%. The DBMS system will transform more useful data into useful information, which helps companies know their customer better and provide better services for them. 

Project scope 

To create an all-functional information system to facilitate the real estate company in managing their employees and providing help to their employees in assisting their clients, the project is designing a database to represent the real-world business and activity. There are several steps to achieve the final objective that increase the company’s revenue. 

In the first step, our group needs to understand the business needs and requirements based on the business’s current situation and available IT infrastructures. The new design needs to be compatible with the organization’s current system and can satisfy all stakeholder’s needs. Once we understand the structure we need to identify the entities and the relationship between each entity based on the business rules. To better structure the enterprise model, we need to identify the mandatory and optional relationships and connect with business rules. After the stakeholder approves the enterprise model and all suggestions are revised, we can move to the next stage to convert the model to a real database and implement it into the system. The project is not done until a year after the new system is implemented because maintenance is one of the most important stages in the project as well. We need to make sure the system is fully functional and able to achieve the ultimate goal.  

Product scope 

             Our final product will be a database management system that helps the real estate company work more efficiently. It assists agents in understanding market trends, tracking their client’s leads. Managers can use the database to evaluate the performance of their agents by tracking the agent deals and commissions. 

            Managers of the company can access all the information about agents, such as  all agents’ contact information, total revenue generated, which region the agent operates in, when the agent joins the company. They can get up-to-date information about what deal the agent is making. They can compare an agent's performance to agents in the same region to find out who is the region's top salesperson. They will decide payroll based on their sales performance.

All agents can access the client information about the client telephone, address, accumulative revenue generated from the client, whether they have a house on sale. It can help an agent identify which clients contribute most to profit and follow up on them.All agents can access the database from the user interface and add clients and housing information. It can be whether the house is for vacation or located in the city, how many hospitals and schools are near the house, crime rate, infrastructure condition, convenience level, etc. The pricing is determined by the agent who is dealing with this case, managers, and other experienced agents.  They will give approximate pricing based on the neighborhood information and previous pricing of the house. Agents can access the database to read the approximate price to help decide the price of a house. When they actually sell the house, they add the actual price sold to the database. 

Assumptions and Constraints: 

To ensure the successful implementation of the project, there are some assumptions as a premise. Firstly, having a well-designed database is an indispensable factor, containing the latest and large enough data with clear internal structure for future prediction. In addition, the external environment should be relatively steady, which means that there is no abruptly huge change which will have a pretty great influence on our current social life, such as the financial crisis sweeping the world, large-scale natural disasters, the new virus which is highly contagious, and so forth.

However, there are still potential constraints during the process. The information asymmetry among the increasing number of agents is a challenge. Even though hiring 40% more agents can be effective for expansion, it also increases difficulty for setting up the well-designed database systems owing to the enormous data and various sources. That is to say, it will be more challenging to identify the primary key (PK) and foreign key (FK) of each entity within the database system, which contain unique data value respectively and maintain connection with each other. What’s more, how to avoid associated duplication of the consistency between agents and their properties is also a problem. Owing to the hiring of various agents on a large scale, the company needs to clearly organize the property scope managed by each agent in order to achieve efficient association in the database. Besides, even though setting up a relatively mature database system can improve the efficiency for prediction of home value compared with comparative market analysis (CMA), which is a traditional way for estimation in the real estate industry, it is still challenging to estimate automatically because of the rapidly changing environment as well as under special situation for the epidemic. Based on database systems, the quality of a prediction is dependent on the data it relies on. The greatest source of information about home value in real estate is generated mainly from MLS and tax records, which people can look for suitable houses as well as proper various agents by searching for the information recorded, such as locations, history, community safety, prices, conditions and so forth. And the pricing of each house depending on these factors is relatively subjective because of various standards. Therefore, it is somewhat difficult for databases to make automatic estimation combining these information for analysis to boost efficiency. In addition, the movement of people between and within countries has always been an important factor in real estate and the economy. Today, the world's economies and people face unprecedented mobility challenges because of the pandemic, which will further limit the expansion of the operations of real estate companies to a certain extent.

Major Deliverable, due dates, and acceptance criteria

Dividing the large project into small pieces can help the project stay on track. Our group sets up three milestones for the project to track the key events and updates as the project progresses. The timefrace (Appendix 1) gives stakeholders a better idea of our progress. 

In the first stage of the project is conceptual design. The team needs to identify the main entities in the business operation line and determine the relationship between each entity. We need to identify the primary keys and foregin keys to differentiate the attributes, and normalize the entities to finalize the ER diagram. Then we can transfer the ER diagrams into relations model. Once the ER model is verified, we need a DBMS software to allow the information system smooth operation. In the selection process, we need to conduct the research regarding the cost and future maintenance to report to stakeholders. The model takes two week to design because we need to meet with different stakeholders who will involved in using the system and get approved by them. It is important to understand their information needs, different users, and the information constitutions (Coronel, 2019).  The success criteria is that the model can satisfy all business needs and the relationships follows business rules, and find the software which is compatible, most cost-efficient product for the project.  We estimate it should takes two weeks and the deadline is March 10th.

The next stage is logical design. The team needs to map the conceptual model to relational model and validate the logical model. The acceptance criteria include validating logical model definitions against all end-user data. The design should take two weeks, which is March 25th. 

Finally is the physical design. The group needs to define the data storage organization and integrity and security measures. Also determine the performance measurements(Coronel, 2019). Once the last stage is completed we can implement the final product to the company and test if it meets all requirements. The acceptance criteria is to meet end-user requirements, which takes 3 weeks and is due April 15th. Since the maintenance is important for the DBMS, we are going to provide more assistants after the system is implemented. 

Logical Model

Relational Model

Assumptions about Business Rules

1. Employee_Client: Each client must serve at least one employee from the brokerage, and the agent might co-broker with each other to serve the same client at the same time. But not every agent is mandatory to have the client. Clients can be potential clients who have not started business with the company. We do not have much information. Therefore, only the client’s first name and id is mandatory in the client entity.

2. Estate_Transaciton: The client might use multiple transactions to make the purchase or rent a property, so the one property can have multiple transactions.

3. Transaction_Payment: Each payment is mandatory belongs to a transaction, and each transaction must have at least one payment. This part is mandatory based on every existing purchase meaning since each successfully traded property must correspond to a transaction record; If the real estate does not have corresponding transaction records, then the transaction data related to them will not exist either.

4. Employee_Listing: Not every employee is mandatory to have a listing, but listing is mandatory to at least one agent.

5. Lisitng_Cost: The agents need to advertise the properties to sell/ rent out the property, which might generate a cost or more cost, but it is not mandatory. If there is a cost generated, it must according to one listing.

6. Employee_Account: Each agent has to have an account and each account belongs to one agent. This part is mandatory since every employee should have an account to receive a commission.

7. Account_Commission: The account can receive many commissions, but the new agent might not close any deal yet, so it is not mandatory. But each commission has to belong to an account. Plus, in this part, the system will record related information about the commission, like the date and the amount of this commission.

8. Contact_client: The system can also help agents to keep track of their leads. One agent can have multiple contacts, but even if the same client can be reached out by different agents, one contact only belongs to one agent with a different contact id.