Real-Estate Brokers' Office: You are keeping records for a local real-estate firm which sells only single-family detached houses. About a dozen brokers work in the office, and for each you want to store a first name, last name, middle name/initial, phone extension, e-mail address, and date hired. You have a large pool of customers, for whom you want to store first name, last name, middle name/initial, address, phone number, e- mail address, city, state, and zip (assume you do have a zip code table). Each customer is assigned to a single primary broker, and you want the database to remember this information. You also have a pool of properties currently for sale, for each of which you want to store:

• An address, city, state, and zip
• Date first offered for sale
• An asking price
• A number of bedrooms, bathrooms
• Size in square feet
• A layout (ranch, colonial, split-level, or other)
• First name, last name, middle name/initial, and phone number of current owner
• Additional notes
• Sale price and date of sale (only for properties that were sold)

Each property has a single listing broker, and your database must remember these assignments. Finally, you want to keep track of showings in which a customer visits a property. Each showing involves a single customer, a single property, and has a date/time. For each showing, you want to have a text field for special notes/comments.

If you sell a house more than once, it is treated as different "property" for each sale. Draw an entity-relationship diagram and write a database design outline.