Question

Draw a hi-fidelity E-R diagram for SQL

Swampland RealEstate

Swampland Real Estate is a small real estate agency owned by Jimmy Goodeal. In fact, the agency is so small that Jimmy is the only agent in the company. He wants to have a database that keeps track of key information for his company. Of course, property information is very important. Jimmy wants to be able to store the address (street, city, state & zip code) for each property. He also wants to track the number of bedrooms, and bathrooms and the listing price for each property. Some properties are single family homes. For these properties, he wants to store the lot size. For condominiums he wants to know the monthly association fee. Information about the area in which each property is located is important . Jimmy wants to track the name of the area, along with the names of schools, including the high school, elementary and middle schools. He also wants to store general comments about the area. Jimmy uses a variety of advertising outlets, such as newspapers, magazines, and Web sites, to advertise the properties he is selling. He wants to track which outlets are used to advertise each property. Keep in mind that the property may be advertised several times in the same outlet. Jimmy also wants to know when each ad was placed and how much the ad cost. The database must store the name and main phone number of the outlet.

Information about each client is also stored, including first and last names, main contact phone number, and email address. Jimmy wants the database to track which client sells each property and which client buys each property. Remember that different clients sell and buy each property. (Jimmy is a seller’s agent in some cases. At other times, he is the buyer's agent.) Jimmy pays past clients for referring others to his agency. When such referrals result in a sale, he pays the referring client a small fee. Although a client may earn many fees, Jimmy will only pay once for a referred client.

Finally, sometimes Jimmy will sell properties that are listed by other agencies. For these properties he wants the database to track which agency listed the property. He also wants to store basic information about each agency, including name and phone number.

Answer 1