Creating ERD and SQL tables based on the ERD

Question

I have an assignment to create an ERD from a story given and then make tables and build a db with the information given.

The story is:

There is a real estate company:

• Houses for sale are located at a specific Neighborhood.
• Each neighborhood is part of a city (as we know, in a city there are many neighborhoods)
• Each house is from a house type, such as: villa, apartment, penthouse, etc.
• Each house has an owner which is a customer of the company
• A sale (of a house) is made to a new owner (customer of the company) and in a sale a customer can buy only one house
• A sale is made by a salesman (a company worker)
• Each salesman specializes in different house types, and sometimes there are several salesmen that specialize in the same house types

The ERD I managed to come up with looks like this:

The tables I've managed to build are:

I`m pretty sure I got some of the tables wrong,starting from the ERD.

Would really appreciate some help with setting the correct tables before I create my database.

Answer 1

I carefully checked you design, and it is true that some improvements are needed to properly fit your use case.

The following assumptions are properly implemented (however field names could be refined) :

• Houses for sale are located at a specific Neighborhood
  => 1-N relationship between Neighborhood.NeighborhoodID and House.NeighborhoodID

• Each neighborhood is part of a city (as we know, in a city there are many neighborhoods)
  => 1-N relationship between City.CityID and House.CityID

• Each house is from a house type, such as: villa, apartment, penthouse, etc.
  => 1-N relationship between HouseType.HouseTypeID and House.HouseTypeID

Following assumptions are NOT properly implemented in your schema :

• Each house has an owner which is a customer of the company
  =>This is a 1-N relationship. You need to create a new field in House, called CustomerID, that refers to Customer.CustomerID. Also, you need to create a new table, Company, that stores the Company information (don't store it in the Customer table !). A field called CompanyID must be created in table Customer that references the primary key of the Company table. You should not have a HouseID column in the Customer table.

• A sale (of a house) is made to a new owner (customer of the company) and in a sale a customer can buy only one house
  => The Sale table will contain one record per sale. It references the CustomerID and the SalesManID (see further). You should not have a SalesID column in the Customer table.

• A sale is made by a salesman (a company worker)
  => the SalesMan table should have a CompanyID field that references the Company table

• Each salesman specializes in different house types, and sometimes there are several salesmen that specialize in the same house types => the SalesMan table should have a HouseTypeID field that references the HouseType table. Table SalesManToHouseType should be dropped

Another remark is that you should simplify the names of the fields. There is no need to prefix the field names with the name of the table, as fields belong to tables already... This only make sense when dealing with foreign key, where it is a good pratice to put the target table name in the referencing field name.

Example for table House :

ID              -- not HouseID !
HouseTypeID     -- foreign key to field ID in table HouseType
NeighborhoodID  -- foreign key to field ID in table Neighborhood
CustomerID      -- foreign key to field ID in table Customer
Address
NumRooms
...

Example for table HouseType :

ID               -- not HouseTypeID !
Name             -- not HouseTypeName !
Description