Tanya Pierce, President and owner of Florida Now Real Estate is seeking your assistance in designing a database for her business
. One of her employees has experience in developing and implementing Access-based systems, but has no experience in conceptual or logical data modeling. So, at this point Tanya only wants you to develop a conceptual data model for her system. You are to use our entity-data diagramming notation - Crows foot symbols.
Tanya has some very specific needs for her system. There are several aspects of the business that need to be represented in the data model. Of central interest are properties that are listed or sold by the company. Note that a separate division of Florida Now handles raw land, so your system only has to deal with developed property. For all types of properties, Tanya wants to keep track of the owner (client), the listing and selling dates, the asking and selling prices, the address of the property, the Multiple Listing Service (MLS) number, and any general comments. In addition, the database should store the client that purchases a property. There are some specialized data that need to be stored, depending on the type of property. For single family houses she wants to store the area (for example UCF or Conway), the size of the house in square feet, the number of bedrooms and baths, the size of the garage (for example, 2 car), and the number of stories. For condominiums, the database should track the name of the complex, the unit number, the size in square feet, the number of bedrooms and baths, and the type of community (coop or condo). For commercial properties, she needs to know the zoning, size in square feet, type (industrial, retail, or office), and the general condition of the property.
Tanya also wants the database to store information about her real estate agents, such as their name, home address, home phone number, mobile phone number, email address, and their real estate license number. Also, the database should track which agent lists and sells each piece of property. Note that she has a separate system that tracks selling agents and listings from outside brokerages, so you don’t have to worry about external agents. However, sometimes a property will be listed and sold by the same Florida Now agent, but other times one Florida Now agent will list a property and another will sell it.
Of course, Tanya thinks that it would be good to have the database track information about Florida Now’s clients, such as their name, phone number, and street and email addresses. Also, she wants to be able to record comments about the client. Florida Now offers referral fees to clients who refer potential customers to the brokerage. The database should store these referral relationships between clients, including the amount and date of the referral payment. Note that only one client can be paid for a referral. In other words, it is not possible for two clients to be paid for referring the same client.
Finally, Tanya wants to be able to use the database to examine the effectiveness of various advertising outlets. For each outlet, the database should store the name of the outlet (for example, realestate.com or the Orlando Sentinel), the main contact person and their phone number. She also wants to know how much it cost to advertise each property on any outlet used, and when a property was advertised on each outlet used for that property. Keep in mind that a property may be advertised on multiple outlets, and that the cost of advertising on an outlet might vary from property to property.
Create an entity relationship diagram that captures Tanya’s database requirements. The ERD should indicate all entities, attributes, and relationships (including maximum and minimum cardinalities). Also be sure to indicate primary key attributes.
You must draw the ERD using computer software such as Visio, PowerPoint or Word. In addition, you must resolve all M:N (many- to-many) relationships and multi-valued attributes. State any assumptions you make. Remember that your assumptions must be reasonable and must not violate any stated requirements. Think through the entities, and attributes for each entity.
