Question

- Create an ER diagram for a real estate firm. The firm has multiple offices, the office is identified by a unique business number, address, and description. Each office offers house listings, a house can only be listed in one office but an office can have multiple house listings. the house has an address, area, number of rooms and number of bathrooms. Each office has an agent working for it. a sales agent can work for multiple offices and an office can contain multiple sales agents. An agent can be assigned to sell multiple houses but a house can be sold by only one agent at the same time, however we need to know the agents that were assigned to the house in the past. Agents are identified by employee id and their attributes are name and salary. An agent can agree with the house owner on a commission for every house that they sell (it can be different for every house that they sell). Although an agent can work for multiple offices; yet, they have a physical desk (work from) only in one of the offices.

Answer 1

As per requirement,

Database -  Real estate firm

Entities: Office, Agent,Houses

Let us listout main points:

• Each office offers house listings, a house can only be listed in one office but an office can have multiple house listings .   (Office:Houses - 1:N relationship).
• Each office has an agent working for it. a sales agent can work for multiple offices and an office can contain multiple sales agents. (Office:Agent - N:N relationship)
• n agent can be assigned to sell multiple houses but a house can be sold by only one agent at the same time. (Agent : Houses - 1:N Relationship)