You are asked to design a relational database for a real estate company to support its real estate services within the city of Nanaimo such as listing properties for sell and managing the marketing of the properties, etc. The italic values are example data.
Note that this problem description specifies vastly simplified business rules used in the "real" real estate services.
From the city, the company grabbed the information of a collection of properties. Each property has a unique folio number (08056.218, a unique civic address (335 Wallace Street), a set of property attributes including its zoning information (COR1) and a description (Residential Corridor), and the year it was built (1990).
Some properties may go through several major improvement projects. We can assume that each property could have at most one major improvement project in one year. For each such project, the year when it happened (2008) and the description of the project (Kitchen Renovation) should be collected and stored.
The company employs many realtors. Each realtor is assigned a unique RID (K057) , has a unique service nickname (Eric Lampton, a real name (Derrick S. Lampton), their contact information (email, personal web page, cell phone, etc.) and an experience level (6) that is roughly equal to the number of years this realtor worked as a realtor.
The realtors in the company list properties for sell in real estate listings. To make things simple, each listing is listed by one realtor. Each listing, of course, is to sell one property in the city. Each listing is given a unique listing ID (L370182) to be used internally, has a list start date (15 March 2025), a status indicating availability (Active, Pending, Sold, Withdrawn, or Expired), a description about the property listed to sell and an asking price (358000). The description of a list can include many information in various formats. To make things simple, we assume that the description is in text only. A property can be placed on the market to sell multiple times. Which means that a property can be listed multiple times.
When a listed property has been purchased, not only the status of that listing is updated to Sold, but also its closing date (10 September 2025) and purchase price (353000) should be collected and stored.
The company collected a list of potential buyers (clients) through various approaches. Each client is represented by their unique email address (buyone@shaw.ca), has a name (M O'Harry) and other information (First time home buyer) if there is any.
These potential buyers express their interests (leads) on various listings. For example, they can leave messages requesting more information about a listing, or they can visit an open house but show no interest at all. These interests are extracted from various sources and formalized as an integer number between 0 to 5 inclusive. 0 means not interested at all, and 5 means very interested. The higher the number, the more interested the client is towards the listed property.
This problem description may have ambiguities and may miss some information for the application domain knowledge. You are welcome to document your own interpretation and assumptions.
Draw an ER diagram to capture the data information described in the Problem Description.
Choose meaningful names for the entity sets, the attributes/properties, and the relationship sets. Identify appropriate constraints that should be held in the diagram (e.g., cardinality constraints on the relationship sets, the weak entity sets and aggregations if there are any, etc.). Indicate the identifiers of the entity sets, and the discriminators if there are any.
In your ER diagram, try to capture as much relevant information as possible. There may be properties or constraints that you cannot capture in your model. If you cannot model something exactly, it is preferable to be under-constrained rather than over-constrained. If there are constraints that you recognize but cannot capture in your ER model, document them clearly.